cancel
Showing results for 
Search instead for 
Did you mean: 

Sliding window computation in tick table with irregular timestamp

reactgary
New Contributor
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
7 REPLIES 7

kuentang
New Contributor

N:((type 0#0)$10 xexp) 2

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

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)

 

HTH,

Kim

 

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

 

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

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

Joshua_Shields_
New Contributor

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 (`sqtyprx`sqty#t)-0^`sqtyprx`sqty#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)(`sqtyprx`sqty#t)-0^`sqtyprx`sqty#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 (`sqtyprx`sqty#t)-0^`sqtyprx`sqty#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

sohagan
New Contributor
personally, I like a simpler approach
update w:{[x]exec wavg[vol;price]from t where time within (x-60;x)}each time from t

probably not the most performant, but still...

Hmmm,

 

It will not work when time is not unique.

 

Kim

 

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

 

personally, I like a simpler approach

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

 

probably not the most performant, but still...

 


On Monday, January 25, 2016 at 1:19:08 AM UTC-5, Gary Chow wrote:

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

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

sohagan
New Contributor
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 

kuentang
New Contributor

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.

 

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)

 

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

466

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

420

q)a[`vwap]~b`vwap

1b

 

Kim

 

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

 

Hi Gary,

 

Another way of dealing with this question is to use aj, which should 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 (`sqtyprx`sqty#t)-0^`sqtyprx`sqty#aj[`time;select time:time-00:01 from t;t]

 

Breaking this down 

 

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

(`sqtyprx`sqty#t)-0^`sqtyprx`sqty#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

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

                                                                                                                                                             / this gives the total sums for the preceeding minute

 

(select time from t),'select vwap:sqtyprx%sqty from (`sqtyprx`sqty#t)-0^`sqtyprx`sqty#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

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

Kovacsi
New Contributor III

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?