I have a problem with a select statement in KDB giving strange results. The data is OPRA data. i.e. listed options exchange data. The object of the query is to give the last ASK_PRICE for a specific EXERCISE_PRICE and EXERCISE_DATE in 10 minute intervals. The table only has 1 day in it. So the statement looks like (and gives empty results):
select
last ASK_PRICE by 10 xbar TRADE_TIME.minute
from
trade
where
EXERCISE_PRICE=12 and EXPIRATION_DATE=2014.01.18
If I alter the statement to look like to see if there is data that might fit the query, I tried:
select
last ASK_PRICE,EXERCISE_PRICE by 10 xbar TRADE_TIME.minute
from
trade
where
EXERCISE_PRICE>12 and EXPIRATION_DATE=2014.01.18
I get the following results with an EXERCISE_PRICE of 12 showing up in the results, but also with values of less than 12 as well. Results are truncated:
minute ASK_PRICE EXERCISE_PRICE (the table header)
09:50 6.2 8 8 9 9 9 9 9 9 9 9 9 9 9 9 9 9 11 11 11 12 12 12 12 13 14 14 14 14 14 24 24 24 24 24 24 24 24 17 17 17 10 10
Please note in the result the ASK_PRICE is 6.2 and the rest of the values are the EXERCISE_PRICE. That is, all the EXERCISE_PRICE values for the time period 09:50.
So it's really not clear why when I specify >12 that I get values below 12. When I specify = 12, I get nothing.
The Meta information on the table gives the column EXERCISE_PRICE as type f, the column EXERCISE_DATE as d, the column TRADE_TIME as t and the ASK_PRICE as f.
There looks to be a hidden error in your query. When you use and in a where clause it is the equivalent to running:
"where EXERCISE_PRICE>(12 and EXPIRATION_DATE=2014.01.18)"
This is because kdb+ evaluates right to left - try using , (comma) to separate your where conditions.
q)select last ASK_PRICE,EXERCISE_PRICE by 10 xbar TRADE_TIME.minute from trade where EXERCISE_PRICE>12 , EXPIRATION_DATE=2014.01.18