Rolling Bars

Community Support
1

Hello, wondering if there is a simple way to have rolling bars and xbar wont work because its rounded to nearest multiple.  So would like to have something like 30minute bars updated every 5seconds.  Not sure if I should be using aj or xprev, or if theres a quick way to do this.

Thanks!

2

Hi Roni,

can you supply a reproducible example of what your table looks like and what you expect the output to be? 

Terry

3

Ahh yea my mistake

So would have some table of prices

t:time xasc (time:20?.z.T;sym:20?enlist a;price:20?100)

time               symprice

01:03:02.668a38

01:09:50.253a88

01:30:50.872a90

01:32:21.596a97

01:33:14.916a54

…

And then what I would like is to have some x duration bars every y period, so for example 30sec bars every 5sec period

If I use xbar then bars are rounded to 30s

©¬ secondtimesymprice

01:03: 00 01:03:02.668a38

01:09: 30 01:09:50.253a88

01:30: 30 01:30:50.872a90

01:32: 00 01:32:21.596a97

would like to have something like 

©¬ secondtimesymprice

01:03: 05  03:02.668a38

01:03: 10 01:09:50.253a88

01:03: 15 01:30:50.872a90

01:03: 20 01:32:21.596a97

 with each bar as the last 30sec of data.  I would like to pass in start and end time and have the bars reversed from the end time, so pass in say 01:04:00 and then get back 30sec bars at 01:03:55, 01:03:50, 01:03:45…  I’ve got it to work by getting a time seq from end time down to start time, and then iterating each of those, doing a select, and then using aj but there must be a better way

getRollingBars:{[startTime;endTime;duration;period] //get raw data t:select from trade where ... //gen time seq timeseq:{y-z*til 1+long$(y-x)%z}[startTime;endTime;period] //iterate seq and select last values and time at each interval t1:raze { } each timeseq; //use aj to get values at last interval time}`

hope that makes sense, thanks!

4

Hi Roni, 

I feel it should be easier to have cumulative sums with different lags rather than iterating over every time bucket. 

trades:time xasc (sym: 10000?(),a;time:10000?12:00:00;size:10000?100);getRollingBars:{[startTime;endTime;duration;period] /duration and periods in seconds raw:select sum size by period xbar second$time from trades where time within (startTime - duration;endTime); / raw:update 0^size from (time:{x+z* til 1 + long$(y-x)%z}[startTime-duration;endTime;period]) lj raw; / include this if you want values for every bucket csums: update cumsum:sums size from raw; ans:update res:cumsum-0^cumLag from aj[time;csums;select time + duration, cumLag: cumsum from csums]; :select from ans where time within (startTime;endTime); / deleting the entries before startTime };getRollingBars[03:50:00;05:00:00;30;5]`

If you are enforcing all buckets, might as well replace aj with lj based on which is faster. 

Hope that helps. 

Regards,

Balaji

5

Thanks for this Balaji, this works much appreciated

6

You could also achieve this with a window join

getBar:{[t;s;e;p;w]b:reverse e-p*til"i"$(e-s)div p;wj1[(neg[w];0)+\:b;symtime;([]time:b)cross distinct(1#sym)#t;(t;(sum;size))]};getBar[trades;03:50:00;04:58:00;00:00:05;00:00:27]

Customize the window join parameters (sum;`size) as necessary.

Terry

7

Thanks Terry, will try