Example 4. Bi-temporal join (asof join)




The bi-temporal join (asof join) is an extremely convenient feature for time series data analysis. Suppose we need to join 2 tables: a stock trades table with the execution prices, and a stock quotes table with the quoted bid and ask prices. For each stock trade, we would like to get the prevailing quotes. In order words, if a timestamp in the stock trades table doesn't appear in the stock quotes table, we would like to get the most recent quotes for this stock. Asof join in DolphinDB is designed to efficiently process these bi-temporal join tasks.


The data in the following example are taken from NYSE website. Please visit ftp://ftp.nyxdata.com/Historical%20Data%20Samples/Daily%20TAQ%20Sample/ and download the following 2 files: EQY_US_ALL_TRADE_20161024.gz and EQY_US_ALL_NBBO_20161024.gz. Unzip them, save them as csv files under the folder C:/DolphinDB/Data/, and run the following script to save the data as partitioned tables in DolphinDB.  



PTNDB_DIR = "C:/DolphinDB/Data"

dbName = database(PTNDB_DIR + "/NYSE", RANGE, string('A'..'Z') join `ZZZZ)

Trades = loadTextEx(dbName, `Trades, `Symbol, PTNDB_DIR + "/EQY_US_ALL_TRADE_20161024.csv",'|')

Quotes = loadTextEx(dbName, `Nbbo, `Symbol, PTNDB_DIR + "/EQY_US_ALL_NBBO_20161024.csv",'|')



The table Trades contains all the stock trades of US stocks on 10/24/2016. The table Quotes has the National Best Bid and Offer (NBBO) on 10/24/2016. We will do a left join of Trades and Quotes first.



t1=select Time, Symbol, Trade_Volume, Trade_Price, Bid_Price, Offer_Price, Quotes.Time as QuoteTime from lj(Trades, Quotes, `Symbol`Time)




In table t1, most of the stock trade records do not have matching stock quotes.  


Now let's do an asof join of Trades and Quotes.



t2=select Time, Symbol, Trade_Volume, Trade_Price, Bid_Price, Offer_Price, Quotes.Time as QuoteTime from aj(Trades, Quotes, `Symbol`Time)




In table t2, all of the records are matched with stock quotes, either at the same time of the trade or the last available quote if there are no quotes at the same time of the trade.

Download source code here.