Sliding window computation in tick table with irregular timestamp

Community Support
1

Hi,

I have a tick data table which store the price and volume of a contract, something like this.

q)t

time                 price vol

09:00:00.000 10     10 

09:00:01.000 11     12 

09:00:05.000 10     12 

Now, I would like to compute the vwap of last minute, in a sliding window manner.

e.g.

time                 price vol vwap

09:00:00.000 10     10 (vwap of 08:59:00-09:00:00)

09:00:01.000 11     12 (vwap of 08:59:01-09:00:01)

09:00:05.000 10     12 (vwap of 09:59:05-09:00:05)

In short, that should require me to write a write a nested query like this?

But I dunno how exactly it can be done…

update vwap:(exec “vwap of data where time within last minute”) from t

Or is it no way to do it and its better to re-sample the original t table at a regular time interval?

Thanks a lot

Gary

2

N:((type 0#0)$10 xexp) 2<o:p></o:p>

time:asc N?.z.t;prx:N?100+0.01*1+N?100;qty:1+N?1000<o:p></o:p>

update vwap:(wavg)./:flip(qty;prx)@:i{x + neg til 1 + x - y binr neg[`minute$1]+last y:(x+1)#y}:time from (time;prx;qty)<o:p></o:p>

<o:p> </o:p>

HTH,<o:p></o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Gary Chow
Gesendet: Montag, 25. Januar 2016 05:11
An: Kdb+ Personal Developers
Betreff: [personal kdb+] Sliding window computation in tick table with irregular timestamp<o:p></o:p>

<o:p> </o:p>

Hi,<o:p></o:p>

<o:p> </o:p>

I have a tick data table which store the price and volume of a contract, something like this.<o:p></o:p>

<o:p> </o:p>

q)t<o:p></o:p>

time                 price vol<o:p></o:p>

----------------------<o:p></o:p>

09:00:00.000 10     10 <o:p></o:p>

09:00:01.000 11     12 <o:p></o:p>

09:00:05.000 10     12 <o:p></o:p>

<o:p> </o:p>

Now, I would like to compute the vwap of last minute, in a sliding window manner.<o:p></o:p>

<o:p> </o:p>

e.g.<o:p></o:p>

time                 price vol vwap<o:p></o:p>

----------------------<o:p></o:p>

09:00:00.000 10     10 (vwap of 08:59:00-09:00:00)<o:p></o:p>

09:00:01.000 11     12 (vwap of 08:59:01-09:00:01)<o:p></o:p>

09:00:05.000 10     12 (vwap of 09:59:05-09:00:05)<o:p></o:p>

<o:p> </o:p>

In short, that should require me to write a write a nested query like this?<o:p></o:p>

But I dunno how exactly it can be done…<o:p></o:p>

update vwap:(exec “vwap of data where time within last minute”) from t<o:p></o:p>

<o:p> </o:p>

Or is it no way to do it and its better to re-sample the original t table at a regular time interval?<o:p></o:p>

<o:p> </o:p>

Thanks a lot<o:p></o:p>

<o:p> </o:p>

Gary<o:p></o:p>

–
Submitted via Google Groups

3

Hi Gary,

Another way of dealing with this question is to use aj, which will perform faster.

Setting:

q)N:100000

q)time:asc N?.z.t;prx:N?100+0.01*1+N?100;qty:1+N?1000

q)trades:(time;prx;qty)

To get the vwap use:

q)t:update sqty:sums qty,sqtyprx:sums qty*prx from trades

q)(select time from t),'select vwap:sqtyprx%sqty from (sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]

Breaking this down 

q)t:update sqty:sums qty,sqtyprx:sums qty*prx from trades                                  / create a new table t with running sums of qty and qty*prx

q)(sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]            / use aj to create running sum values stopping 1 minute before actual time

                                        / and subtract it from the original running sums at that time

                                        / this gives the total sums for the preceeding minute

q)(select time from t),'select vwap:sqtyprx%sqty from (sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]

We can then use this to calculate the vwap for the preceeding minute at each trade record, and join back to its original time.

I hope this makes sense.

Josh

4

personally, I like a simpler approach

update w:{exec wavg[vol;price]from t where time within (x-60;x)}each time from t

probably not the most performant, but still…

5

Hmmm,<o:p></o:p>

<o:p> </o:p>

It will not work when time is not unique.<o:p></o:p>

<o:p> </o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Sean O’Hagan
Gesendet: Mittwoch, 27. Januar 2016 05:04
An: Kdb+ Personal Developers
Betreff: [personal kdb+] Re: Sliding window computation in tick table with irregular timestamp<o:p></o:p>

<o:p> </o:p>

personally, I like a simpler approach<o:p></o:p>

update w:{exec wavg[vol;price]from t where time within (x-60;x)}each time from t<o:p></o:p>

<o:p> </o:p>

probably not the most performant, but still…<o:p></o:p>

<o:p> </o:p>

On Monday, January 25, 2016 at 1:19:08 AM UTC-5, Gary Chow wrote:<o:p></o:p>

Hi,<o:p></o:p>

<o:p> </o:p>

I have a tick data table which store the price and volume of a contract, something like this.<o:p></o:p>

<o:p> </o:p>

q)t<o:p></o:p>

time                 price vol<o:p></o:p>

----------------------<o:p></o:p>

09:00:00.000 10     10 <o:p></o:p>

09:00:01.000 11     12 <o:p></o:p>

09:00:05.000 10     12 <o:p></o:p>

<o:p> </o:p>

Now, I would like to compute the vwap of last minute, in a sliding window manner.<o:p></o:p>

<o:p> </o:p>

e.g.<o:p></o:p>

time                 price vol vwap<o:p></o:p>

----------------------<o:p></o:p>

09:00:00.000 10     10 (vwap of 08:59:00-09:00:00)<o:p></o:p>

09:00:01.000 11     12 (vwap of 08:59:01-09:00:01)<o:p></o:p>

09:00:05.000 10     12 (vwap of 09:59:05-09:00:05)<o:p></o:p>

<o:p> </o:p>

In short, that should require me to write a write a nested query like this?<o:p></o:p>

But I dunno how exactly it can be done…<o:p></o:p>

update vwap:(exec “vwap of data where time within last minute”) from t<o:p></o:p>

<o:p> </o:p>

Or is it no way to do it and its better to re-sample the original t table at a regular time interval?<o:p></o:p>

<o:p> </o:p>

Thanks a lot<o:p></o:p>

<o:p> </o:p>

Gary<o:p></o:p>

–
Submitted via Google Groups

6

Point taken, the results you will get with my answer and your answer will be different if time is not unique.

I’ve seen all the aj answers and other answers for this specific problem. I can’t remember whether they match my results or yours, I just know generally the aj is the most efficient. 

I tend to look at it differently though, especially in the way Gary has worded his question.

Take the table below

t:(time:09:00:00 + 0 0 1 5;price:til 4;vol:4+til 4)

Between 08:59:00 and 09:00:00 we have two trades. I ask myself what is the vwap in that time period?

Imo it’s 0.5555556 for both trades, not 0&0.5555556 as your answer would give. You may disagree.

Now, say you were asked to chart time vs vwap.

How do you choose what point to take from your two results for 09:00:00?

That’s my two cents.

Sean 

7

Interesting to see is that the speed up is coming from the vectorization of the vwap calculation. It does not have anything to do with aj.<o:p></o:p>

<o:p> </o:p>

q)N:100000<o:p></o:p>

q)time:asc N?.z.t;prx:N?100+0.01*1+N?100;qty:1+N?1000<o:p></o:p>

q)trades:(time;prx;qty)<o:p></o:p>

<o:p> </o:p>

q)\t:10 t:update sqty:sums qty,sqtyprx:sums qty*prx from trades;  a:(select time from t),'select vwap:sqtyprx%sqty from (sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]<o:p></o:p>

466<o:p></o:p>

q)\t:10 b:update vwap:{(x - 0^x z) % y- 0^y z}[sums qty*prx;sums qty]{ x bin (x - 00:01)}time from trades<o:p></o:p>

420<o:p></o:p>

q)a[vwap]~bvwap<o:p></o:p>

1b<o:p></o:p>

<o:p> </o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Joshua Shields (AquaQ Analytics)
Gesendet: Montag, 25. Januar 2016 16:01
An: Kdb+ Personal Developers
Betreff: [personal kdb+] Re: Sliding window computation in tick table with irregular timestamp<o:p></o:p>

<o:p> </o:p>

Hi Gary,<o:p></o:p>

<o:p> </o:p>

Another way of dealing with this question is to use aj, which should perform faster.<o:p></o:p>

<o:p> </o:p>

Setting:<o:p></o:p>

q)N:100000<o:p></o:p>

q)time:asc N?.z.t;prx:N?100+0.01*1+N?100;qty:1+N?1000<o:p></o:p>

q)trades:(time;prx;qty)<o:p></o:p>

<o:p> </o:p>

To get the vwap use:<o:p></o:p>

<o:p> </o:p>

q)t:update sqty:sums qty,sqtyprx:sums qty*prx from trades; <o:p></o:p>

q)(select time from t),'select vwap:sqtyprx%sqty from (sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]<o:p></o:p>

<o:p> </o:p>

Breaking this down <o:p></o:p>

<o:p> </o:p>

t:update sqty:sums qty,sqtyprx:sums qty*prx from trades                                  / create a new table t with running sums of qty and qty*prx<o:p></o:p>

(sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]            / use aj to create running sum values stopping 1 minute before the actual time of the record<o:p></o:p>

                                                                                                                                                             / and subtract it from the original running sums at that time<o:p></o:p>

                                                                                                                                                             / this gives the total sums for the preceeding minute<o:p></o:p>

<o:p> </o:p>

(select time from t),'select vwap:sqtyprx%sqty from (sqtyprxsqty#t)-0^sqtyprxsqty#aj[`time;select time:time-00:01 from t;t]<o:p></o:p>

  <o:p></o:p>

We can then use this to calculate the vwap for the preceeding minute at each trade record, and join back to its original time.<o:p></o:p>

<o:p> </o:p>

I hope this makes sense.<o:p></o:p>

<o:p> </o:p>

Josh<o:p></o:p>

–
Submitted via Google Groups

8

Hi

   I find it hard to understand what tis function does?

lets say I need sliding average on tick data of one year of trades prices,

each row has timestamp YYYY.MM.DD"D"HH:uu:SS.ffffff where this timestamp

advance randomly (can be between 0.1 microsecond to … say 10 seconds between tick to tick)

now I wish to get sliding window of average price for last 25 days per tick.

how can I use this function to get the result in a new table?