I have a table of trades Trades
:
TradeID Stock Timestamp
1 GOOG 2014.03.03 09:13:59.054
2 GOOG 2014.03.03 09:28:12.003
3 GOOG 2014.03.03 09:28:18.199
4 GOOG 2014.03.03 09:52:03.628
5 GOOG 2014.03.03 11:18:52.629
...
and also a table of price tick data Prices
:
Stock Timestamp Bid Ask
GOOG 2014.03.03 08:02:34.297 102.21 102.41
GOOG 2014.03.03 08:02:40.118 102.32 102.42
GOOG 2014.03.03 08:02:44.090 102.33 102.43
GOOG 2014.03.03 08:03:20.197 102.34 102.44
GOOG 2014.03.03 08:05:09.325 102.35 102.45
...
When I do an asof join...
aj[
`Stock`Timestamp;
Trades;
update TimestampPrice:Timestamp from Prices / let's me track which price gets joined
]
...I get the wrong result:
TradeID Stock Timestamp TimestampPrice Bid Ask
1 GOOG 2014.03.03 09:13:59.054 2014.03.03 08:05:09.325 102.35 102.45
2 GOOG 2014.03.03 09:28:12.003 2014.03.03 08:05:09.325 102.35 102.45
3 GOOG 2014.03.03 09:28:18.199 2014.03.03 08:05:09.325 102.35 102.45
4 GOOG 2014.03.03 09:52:03.628 2014.03.03 08:05:09.325 102.35 102.45
5 GOOG 2014.03.03 11:18:52.629 2014.03.03 10:31:45.043 102.24 102.35
6 GOOG 2014.03.03 11:33:52.021 2014.03.03 10:31:45.043 102.24 102.35
Timestamp
shows the time of trade, while TimestampPrice
shows the timestamp of the price data that was joined. Despite having price data ticking roughly every 30 secs, the result of the aj
joins on prices that are several hours away from the actual trade! E.g., TradeID=1
was traded at 09:13:59
but was joined on prices from 08:05:09
.
Also, I find it strange that the joined TimestampPrice
suddenly jumps from 08:05:09
to 10:31:45
.
I have manually checked that there are no gaps in the data that could account for this.
What might be going wrong?
In addition, also make sure the Timestamp column is sorted as aj assumes it is
– user2393012 Apr 9 at 8:47