cancel
Showing results for 
Search instead for 
Did you mean: 

Rolling Bars

hoffmanroni
New Contributor III
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!
6 REPLIES 6

TerryLynch
New Contributor II
Hi Roni,

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

Terry

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                sym price
01:03:02.668 a 38
01:09:50.253 a 88
01:30:50.872 a 90
01:32:21.596 a 97
01:33:14.916 a 54
...

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

©¬ second time sym price
01:03:00 01:03:02.668 a 38
01:09:30 01:09:50.253 a 88
01:30:30 01:30:50.872 a 90
01:32:00 01:32:21.596 a 97

would like to have something like 

©¬ second time sym price
01:03:05  03:02.668 a 38
01:03:10 01:09:50.253 a 88
01:03:15 01:30:50.872 a 90
01:03:20 01:32:21.596 a 97

 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!

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

hoffmanroni
New Contributor III
Thanks for this Balaji, this works much appreciated

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;`sym`time;([]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

Thanks Terry, will try