cancel
Showing results for
Did you mean:

## Sliding window computation in tick table with irregular timestamp

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
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

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.

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

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
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...

New Contributor

Hmmm,

It will not work when time is not unique.

Kim

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.

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?

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
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)\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

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

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.

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?