Hey Everyone,
I’m trying to achieve a rolling delta so that I can see how much movement has occurred after a timestamp. In other words, for each trade in my table I want to know where the market was x minutes after the event.
I have a pretty basic table…
e.g.
table: sym, date, time, price
I’ve tried xbar, but obviously that just gives me a time bucket of the action.
I’m led to believe that this is a job for asof join but have not yet been able to figure out how to make it work.
Thanks!
Hi Greg,
If you want to use an aj for this, given a table t with sym, time and price columns you can offset you time to do the aj and then reset back.
offset:0D00:05:00;
time_type:`time
update time_type$time-offset from
aj[symtime;
update time_type$time+offset from t;
select sym, time, off_p:price from t]
There are better functional ways to write this where you call roll out over a few different offset and get those columns in your table too if that helps but thought this might be a clearer way to demonstrate the idea.
Thanks,
Rebecca
Rebecca Kelly | Kx Technical Evangelist | ML COE US | Kx | (646) 630-4381 | rebecca@kx.com
Hello,
The following provides an alternative function and example output over several different offsets, again, using an asof join. A sample trade table, t, and quote table, q, are defined with random time,sym and price columns generated:
q)n:100;s:(abcd);t:time xasc ([]time:n?.z.T;sym:n?s;price:n?10.); n:n*10; q:update g#sym from `time xasc (time:n?.z.T;sym:n?s;price:n?10.)
The following function then uses an asof join between t and an offsetted q to create a table displaying the original price of each record followed by the price price_x at each offset time ot_x.
q){[q;t;offset]aj[symtime;t;(timesym,($string[price],““,string[offset]),($string[ot],””,string[offset])) xcol update time:time+offset,otime:time from q]}[q]/[t;10 20 30]
time sym price price_10 ot_10 price_20 ot_20 price_30 ot_30
-------------------------------------------------------------------------------------------
00:09:18.738 a 4.041054 2.968421 00:07:48.897 2.968421 00:07:48.897 2.968421 00:07:48.897
00:09:43.119 b 9.340615 4.793682 00:07:47.124 4.793682 00:07:47.124 4.793682 00:07:47.124
00:25:31.716 c 8.360091 1.102165 00:20:27.655 1.102165 00:20:27.655 1.102165 00:20:27.655
00:41:12.135 b 7.060537 5.494765 00:40:50.102 5.494765 00:40:50.102 5.494765 00:40:50.102
00:45:52.815 a 3.802801 6.090168 00:45:46.468 6.090168 00:45:46.468 6.090168 00:45:46.468
01:11:02.464 b 4.429798 7.082719 01:10:22.765 7.082719 01:10:22.765 7.082719 01:10:22.765
01:15:15.002 a 4.378904 4.249544 01:01:59.277 4.249544 01:01:59.277 4.249544 01:01:59.277
01:26:38.948 b 8.44846 2.609256 01:23:27.476 2.609256 01:23:27.476 2.609256 01:23:27.476
01:27:36.694 d 6.558275 2.558815 01:22:23.671 2.558815 01:22:23.671 2.558815 01:22:23.671
01:30:13.931 a 6.808597 7.414788 01:30:12.227 7.414788 01:30:12.227 7.414788 01:30:12.227
01:30:42.933 b 4.295886 8.941862 01:30:37.033 8.941862 01:30:37.033 8.941862 01:30:37.033
01:41:12.267 d 7.658848 2.230976 01:33:19.334 2.230976 01:33:19.334 2.230976 01:33:19.334
01:43:25.069 d 1.763493 1.707678 01:42:42.586 1.707678 01:42:42.586 1.707678 01:42:42.586
01:51:22.574 b 9.493308 7.882397 01:42:37.855 7.882397 01:42:37.855 7.882397 01:42:37.855
02:12:12.972 b 4.285788 8.034015 02:08:50.226 8.034015 02:08:50.226 8.034015 02:08:50.226
..
At the minute, the offsets are set to 10, 20 and 30 ms but you can cast offset to your desired format.
Hope this helps.
Regards,
Craig
Thanks!
Hey Rebecca,
You mentioned that there was a more functional way to do this over a few different offsets: is this thread similar to what you were speaking of?
Hi Greg,
From the functional sense I was thinking more along the lines of what Craig suggested - using an over to enable you to easily loop over a few offsets. Personally rather than the column renaming I would probably just have done the selects and updates functionally though, so something like the below.
_ //Mock table _
t:(time: asc n?.z.t; sym: n?AB`C;price:n?100.)
_ //setting variables _
offset: 5; //assuming offset in minutes, but up to you if you want something more granular, I’m being lazy for my column names
time_type:time; col:price;
//write a function that does this in the generic case
offset_func:{[time_type;col;t;offset]
offset_span:timespan$minute$offset;_ //again, assuming offset is in minutes _
update time_type$time-offset_span from
aj[`sym`time;
![t;();0b;enlist[`time]!enlist($;enlist time_type;(+;offset_span;`time))]; // functional - update time_type$time+offset from t
?[t;();0b;(`sym`time,`$string[col],"",string offset)!(`sym`time,col)] **// functional - select sym, time, price_5:price from t
_** ]
}
//this will give you back the price offset for each time difference of 2 3 4 minutes
offset_func[time_type;col;;]/[t;2 3 4]
But also what you linked would make sense for what you’re doing, if your primary interest is in the difference in prices, rather than the price itself.
Really it’s 6 of one/half a dozen and more down to which suits your needs and your own code style :) I’m sure there are still much better ways of doing this!
Hope this makes sense and happy coding,
Rebecca
Rebecca Kelly | Kx Technical Evangelist | ML COE US | Kx | (646) 630-4381 | rebecca@kx.com
update futureprice:price@time bin time + 0D00:00:05 by sym from from table