Rolling Difference Based on Time

Community Support
1

I have table similar to

trade1:time xasc(time:50?24:00:00.000;stock:50?AAA; price:50?100.0)`

and I want to have the rolling difference in price by sym in x time bars.

So for example add a col to the table that shows the price difference from 1 hour before.

I know theres probably an easy way to do this, but not sure.  

Thanks!

2

> I want to have the rolling difference in price by sym in x time bars.

That can be as simple as
select deltas price by stock,oneHourBucket:(60*60*1000) xbar time from trade1

but this is simply grouping by stock by hour. Therefore you may get multiple trade prices in each group and thus multiple deltas. I dont think this is what you want?(going by your next comment too)

>So for example add a col to the table that shows the price difference from 1 hour before.

For that, aj can become very useful and pretty neat there, imo.

If you manipulate the time column of your trades, you can find the trade price asof x minutes ago for a certain sym. And then add on that column. I think thats what you want?

.e.g.

q)trade1:time xasc([]time:50?24:00:00.000;stock:50?AAA; price:50?100.0) q)stocktime xasc trade1
q)f:{[x;y;z] aj[stocktime;x;(timestock,z) xcol update time+y from trade1]}
q)trade2:f/[trade1;00:01:00 00:10:00 01:00:00;{`$“pr”,string,“MinPrice”}each 1 10 60]

In my example I have this data
q)select from trade1 where stock=`A,time within 11:00 14:00
time         stock price  

11:55:56.570 A     96.75763
13:39:34.991 A     90.82711
13:46:21.117 A     81.87707 <–

Looking at the last trade, the price 1min ago was last traded at 90.82, then 10 mins ago, it last traded at 96.75, then 1 hour ago(12:46) the last trace price reported was still 96.75(at 11:55).

This is what trade2 can now neatly show you:

q)select from trade2 where stock=`A,time=13:46:21.117
time         stock price    pr1MinPrice pr10MinPrice pr60MinPrice

13:46:21.117 A     81.87707 90.82711    96.75763     96.75763   

You now can very easily use this table to find the deltas for what you need

select delta60Min:price-pr60MinPrice from trade2…etc

HTH,
Sean

3

I agree with one of the other replies, simply using xbar will not work for scenerios where trades expand bucket window, so using aj is a good approach

You can define a function like this

findPriceDiffByStock:{[trades;window]

   select time,stock,priceDiff:0^price-prevPrice from 

     trade ,’ select prevPrice:price from aj[`stock`time;update ?[0>time-window;time-1;time-window] from trade;`time xasc trade]

 }

q)findPriceDiffByStock[trade1;01:00:00]

time         stock priceDiff

00:15:40.175 A     0

00:24:10.757 A     30.2496

02:04:40.558 AA    0

02:29:04.401 A     -7.965152

03:15:14.605 A     -26.93427

03:51:04.905 AA    44.19762

04:02:44.053 A     31.46872

04:22:47.790 A     2.619632

q)findPriceDiffByStock[trade1;00:30:00]

time         stock priceDiff

00:15:40.175 A     0

00:24:10.757 A     30.2496

02:04:40.558 AA    0

02:29:04.401 A     -7.965152

03:15:14.605 A     -18.96912

03:51:04.905 AA    44.19762

04:02:44.053 A     50.43784

4

Thats perfect I like that thanks.

5

This works well, thanks!