CODEWITHSUNDEEP
kdb+
tenticon
tenticon

Reputation: 2913

kdb - get column values n days ago

If I have a table of prices

t:([]date:2018.01.01+til 30;px:100+sums 30?(-1;1))

date    px
2018.01.01  101
2018.01.02  102
2018.01.03  103
2018.01.04  102
2018.01.05  103
2018.01.06  102
2018.01.07  103
...

how do I compute the returns over n days? I am interested in both computing
(px[i] - px[i-n])/px[i-n] and (px[date] - px[date-n])/px[date-n], i.e. one where the column px is shifted n slots by index and one where the previous price is the price at date-n

Thanks for the help

Upvotes: 1

Views: 597

Answers (1)

Alex R.
Alex R.

Reputation: 721

Well you've pretty much got it right with the first one. To get the returns you can use this lambda: 

{update return1:(px-px[i-x])%px[i-x] from t}[5]

For the date shift you can use an aj like this: 

select date,return2:(px-pr)%pr from aj[`date;t;select date,pr:px from update date:date+5 from t]

Basically what you are trying to do here is to shift the date by the number of days you want to and then extract the price. You use an aj to create your table which will look something like this:

q)aj[`date;t;select date,pr:px from update date:date+5 from t]
date       px pr
----------------
2018.01.01 99 98
2018.01.02 98 97
2018.01.03 97 98

Where px is your price now and pr is your price 5 days from now.

Then the return is calculated just the normal way.

Hope this helps!

Upvotes: 2

Related Questions