I want to calculate effective spread rate in bond market.And have two data frame first trade data that is executed price and second is quote that contains buy and sell quotes for bonds. Effective Spread Rate is calculated like below:
Effective spread: Buy market order: 2×(pt−mt) Sell market order: 2×(mt−pt) where pt is the price for the transaction at time t, and mt is the mid quote at time t−ε (just before the trade).
Mid quote is :
quote['MidQuote'] = (quote['BuyPrice'] + quote['SellPrice'])/2.0
quote data is like this:
Id BuyTime BuyPrice Date SellTime SellPrice MidQuote
1 09:00:20 1005 2010-01-01 09:00:10 1001 1003
1 09:10:21 1020 2010-01-01 09:10:25 1012 1016
2 09:11:22 1032 2010-01-02 09:11:19 1020 1026
trade data is like this:
Id Time Date Price
1 09:00:21 2010-01-01 1004
1 09:10:26 2010-01-01 1017
2 09:11:23 2010-01-02 2028
For instance Effective Spread rate for first row is like this:
2*(1004-1003) = 2
I should merge the data first. I have searched internet and find this but i can not understand how he is done that Here is the web address:
http://www.vincentgregoire.com/introduction-to-empirical-market-microstructure-in-python/
My Question is how could i merge this data and calculate this data by time. time is important because i should calculate difference between time t and t−ε (right before execution time).
