How to get active Price at uniform time interval?

This one is simple with KDB+’s support with ‘xbar’ function and ‘last’ aggregation function:

e.g. Get (prior active or current) price at every second

q) select last Price by 1 xbar time.second from t

Similarily, we can take price at every hour, day, month, for example:

q)table
Date       Sym Open High Low   Close Volume
———————————————–
1984.09.07 AAPL 26.5 26.87 26.25 26.5 2981600
1984.09.10 AAPL 26.5 26.62 25.87 26.37 2346400
1984.09.11 AAPL 26.62 27.37 26.62 26.87 5444000
1984.09.12 AAPL 26.87 27    26.12 26.12 4773600
1984.09.13 AAPL 27.5 27.62 27.5 27.5 7429600
1984.09.14 AAPL 27.62 28.5 27.62 27.87 8826400
1984.09.17 AAPL 28.62 29    28.62 28.62 6886400
1984.09.18 AAPL 28.62 28.87 27.62 27.62 3495200
1984.09.19 AAPL 27.62 27.87 27    27    3816000
1984.09.20 AAPL 27.12 27.37 27.12 27.12 2387200
1984.09.21 AAPL 27.12 27.87 26.5 26.87 3591200
1984.09.24 AAPL 26.87 27    26.62 26.62 2833600
1984.09.25 AAPL 26.5 26.5 26.12 26.12 5977600
1984.09.26 AAPL 26.12 27.25 25.75 25.75 3987200
1984.09.27 AAPL 25.75 25.87 25.75 25.75 3796000
1984.09.28 AAPL 25.75 25.75 24.62 25.12 8344800

…

…

Get last open price at every month:

One problem with above solution is that there will be missing data if there is no data at all for some time period.

For example:

q)t:([]time:08:00:01 08:00:07 08:00:09 08:00:10 08:00:15; 0.2557052 0.20912 0.6004199 0.974358 0.08293808 )

q)t
time x
——————-
08:00:01 0.2557052
08:00:07 0.20912
08:00:09 0.6004199
08:00:10 0.974358
08:00:15 0.08293808
q)select x by 2 xbar time.second from t
second | x
——–| ———-
08:00:00| 0.2557052
08:00:06| 0.20912
08:00:08| 0.6004199
08:00:10| 0.974358
08:00:14| 0.08293808

The active price at time ponit 08:00:02 08:00:04 08:00:12 are missing in above result.

A complete solution on how to solve this will be posted soon in a new post.

Tags: active price, last price, prior price, uniform time interval

This entry was posted on August 13, 2008 at 12:00 pm and is filed under Time series. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Practical Advice on KDB+/Q