Helpful Tools for Quant丨Efficiently Calculate Transaction Costs from Tick Data

The calculation of transaction costs from tick data often involves two tables: trade and nbbo. As the timestamps of both tables are at nanosecond level, there are virtually no exact match between the timestamps of the two tables.

Therefore, in order to calculate transaction costs, we need to locate the most recent quote before each trade (of the same stock). We may also need to calculate the average quotes within a specific window relative to each trade. These non-exact joins are frequently used in quant finance, but they are not supported in most databases.

This time, DolphinDB provides you with asof join and window join for these scenarios.

Take a look at the following case!

The data used in this example is the high-frequency data from the New York Stock Exchange, consisting of two tables: trade and nbbo, respectively containing 27 million and 78 million records. The DolphinDB script is as follows:

trade = loadTable("dfs://EQY", "trade")
select count(*) from trade

nbbo = loadTable("dfs://EQY", "nbbo")
select count(*) from nbbo

// asof join
timer TC1 = select sum(Trade_Volume*abs(Trade_Price-(Bid_Price+Offer_Price)/2))/sum(Trade_Volume*Trade_Price)*10000 as TC_aj from aj(trade,nbbo,`Symbol`Time) where Time between 09:30:00.000000000 : 15:59:59.999999999 group by symbol

// window join
timer TC2 = select sum(Trade_Volume*abs(Trade_Price-(Bid_Price+Offer_Price)/2))/sum(Trade_Volume*Trade_Price)*10000 as TC_wj from pwj(trade,nbbo,-100000000:0,<[avg(Offer_Price) as Offer_Price, avg(Bid_Price) as Bid_Price]>,`Symbol`Time) where Time between 09:30:00.000000000 : 15:59:59.999999999 group by symbol

select * from ej(TC1,TC2,`symbol) where symbol in `AAPL`MS`EBAY

Just one line of script can implement complex calculation logic.

// asof join
timer TC1 = select sum(Trade_Volume*abs(Trade_Price-(Bid_Price+Offer_Price)/2))/sum(Trade_Volume*Trade_Price)*10000 as TC_aj from aj(trade,nbbo,`Symbol`Time) where Time between 09:30:00.000000000 : 15:59:59.999999999 group by symbol

It takes 339 milliseconds to complete the calculation of transaction costs with asof join, which is more than 100 times faster than the equivalent calculation in Python pandas, and the script to calculate with window join takes 402 milliseconds.

While calculating transaction costs, DolphinDB shows an excellent performance with concise code. For a visual representation of the operation covered in this article, you can take one minute watching this demo!