CODEWITHSUNDEEP
kdb+
Niels
Niels

Reputation: 635

KDB - Compare value to value x rows back (deltas but going back x rows)

I have a table with Instrument, Time, Price

Instrument   Time                            Price
Stock x      2019.05.17D10:01:00.000000000   1.2
Stock x      2019.05.17D10:02:00.000000000   1.4
Stock x      2019.05.17D10:03:00.000000000   1.3
Stock x      2019.05.17D10:04:00.000000000   1.2
Stock x      2019.05.17D10:05:00.000000000   1.4
Stock x      2019.05.17D10:06:00.000000000   1.6
Stock x      2019.05.17D10:07:00.000000000   2.3
Stock x      2019.05.17D10:08:00.000000000   2.4
Stock x      2019.05.17D10:09:00.000000000   2.2
Stock x      2019.05.17D10:10:00.000000000   2.5

Then i want to compare the deltas price development over a running window, going x minutes back.

If i do something like

     newdata:({0,1_deltas x}(close))%(close) by Instrument from Table

I get the comparison going 1 row back. (1.2/1.4 // 1.4/1.3 // 1.3/1.2)

A: I would like to do the deltas - but going back several rows. Meaning with x=5 rows 1.2/1.6 // 1.4/2.3 // etc..

B: If I lack minute data at some point - Deltas will not take into account the minutes difference (and price development would not be as easily comparable) - So i would like to know, how i can make a solution, that is not only comparing with x rows prior, but also specificly focused on the time difference, when going the comparison in hindsight.

Upvotes: 1

Views: 781

Answers (1)

DanDan4561
DanDan4561

Reputation: 393

If you want to purely base the look back on x rows, then you can use xprev. 

tab:([]sym:10#`MSFT;time:(2019.05.01D10:00:00.000000+/:00:01+til 10);price:(1.2;1.4;1.3;1.2;1.4;1.6;2.3;2.4;2.2;2.5))

//Can adjust the row lookback value
update diff:(lookback-price) from update lookback:{y xprev x}[;5] price by sym from tab

This should also account for Scenario B, as it will return a null which can be discarded from any price development calculations. However, if your data wasn't bucketed into 1-minute slots and you wanted to do a look back on time you will need to create some kind of window-join, a good example can be found here Rolling window in multi groups

Upvotes: 2

Related Questions