Market trades analytics with Jupyter/Pandas

Import data from csv files with market trade data and execution time. Parse data and analyze market trades quality based on a price after 5 seconds. Calculate correlation between trade quality estimation and trade execution time delta.

Python source code: github.com

import pandas as pd

import re
import numpy as np
from matplotlib import pyplot as plt

1. Market trades log "market_logs":

trade timestamp in nanoseconds

side:

-1 sell

+1 buy

price volume

book state: volume_bid@bid_price x ask_price@volume_ask

In [2]:

market_df = pd.read_csv("market_logs.log", header=None)
market_df.shape

Out[2]:

(397802, 3)

In [3]:

print("\n".join(market_df.loc[0,:]))

mLog: market trd at 1622505601191796236 with side -1
 price 1108.9
 traded volume 36 then book became 6@1108.7x1108.8@104

In [4]:

def tm(src: str) -> int:
    '''parse timestamp'''
    res = re.findall("at (\d+) with", src)[0]
    return int(res)

In [5]:

def side(src: str) -> int:
    res = re.findall("side (.+)$", src)[0]
    return int(res)

In [6]:

def price(src: str) -> float:
    res = re.findall("price (.+)$", src)[0]
    return float(res)

In [7]:

market_df.columns = ["time_src", "price_src", "volume_src"]
market_df["ts"] = market_df["time_src"].apply(tm)
market_df["side"] = market_df["time_src"].apply(side)
market_df.drop("time_src", axis=1, inplace=True)

In [8]:

market_df["price"] = market_df["price_src"].apply(price)
market_df.drop("price_src", axis=1, inplace=True)

In [9]:

def bid_price(src: str) -> float:
    res = re.findall("@(.+)x", src)[0]
    return float(res)

In [10]:

def ask_price(src: str) -> float:
    res = re.findall("x(.+)@", src)[0]
    return float(res)

In [11]:

market_df["bid_price"] = market_df["volume_src"].apply(bid_price)
market_df["ask_price"] = market_df["volume_src"].apply(ask_price)
market_df.drop("volume_src", axis=1, inplace=True)

In [12]:

# middle price
market_df["mid_price"] = (market_df["ask_price"] + market_df["bid_price"]) / 2

In [13]:

market_df.set_index("ts", inplace=True)
market_df

Out[13]:

	side	price	bid_price	ask_price	mid_price
ts
1622505601191796236	-1	1108.9	1108.7	1108.8	1108.75
1622505601222248272	-1	1108.7	1108.5	1108.6	1108.55
1622505601364445668	-1	1108.5	1108.3	1108.6	1108.45
1622505601405325968	1	1108.6	1108.3	1108.6	1108.45
1622505601406994624	-1	1108.3	1108.2	1108.3	1108.25
...	...	...	...	...	...
1627626542759155628	1	1150.5	1150.4	1150.5	1150.45
1627626543983252140	1	1150.5	1150.4	1150.5	1150.45
1627626549091604988	1	1150.5	1150.5	1150.6	1150.55
1627626549092524892	-1	1150.5	1150.4	1150.6	1150.50
1627626549492660420	-1	1150.5	1150.4	1150.6	1150.50

397802 rows × 5 columns

1. Trade execution log: "exec_logs"

side - {+1 - order executer on bid (our buy trade), -1 - order executed on ask (our sell trade)}

price

traded volume

volume_left

delta_execsend - time between sending limit order and its executin

In [14]:

exec_df = pd.read_csv("exec_logs.log", header=None)
exec_df.columns = ["time_src", "side_src", "price_src", "trade_vol_src", "left_vol_src", "delta_src"]

In [15]:

exec_df["ts"] = exec_df["time_src"].apply(tm)
exec_df["side"] = exec_df["side_src"].apply(lambda st: int(st.strip().split(" ")[1]))
exec_df["price"] = exec_df["price_src"].apply(price)
exec_df["delta"] = exec_df["delta_src"].apply(lambda st: int(st.strip().split(" ")[1]))
exec_df.drop(["time_src", "side_src", "price_src", "delta_src"], axis=1, inplace=True)
exec_df.drop(["trade_vol_src", "left_vol_src"], axis=1, inplace=True)
#exec_df.set_index("ts", inplace=True)
exec_df

Out[15]:

	ts	side	price	delta
0	1622505601222248272	1	1108.6	30451968
1	1622505601424113404	-1	1108.4	17118720
2	1622505601604667896	1	1108.3	153866496
3	1622505602982200992	-1	1108.4	153371136
4	1622505603600579508	1	1108.3	618378496
...	...	...	...	...
8677	1627626326117581668	1	1149.9	2381312
8678	1627626397873668620	-1	1150.2	1296281088
8679	1627626412584847188	1	1150.1	14703859968
8680	1627626473991536064	-1	1150.4	12134184704
8681	1627626512859773452	1	1150.6	14734981632

8682 rows × 4 columns

In [16]:

# adding 5 seconds to timestamp
exec_df["plus_5s"] = exec_df["ts"] + 5_000_000_000  # nanoseconds

In [17]:

indx_ts = market_df.index.to_list()

def find_ts(exec_ts: int) -> int:
    '''return market price timestamp at least 5 sec after trade execution'''
    global indx_ts
    for indx in indx_ts:
        if indx >= exec_ts:
            #print(indx)
            return indx

In [18]:

exec_df["market5s"] = exec_df["plus_5s"].apply(find_ts)

In [19]:

# get mid_price after 5 sec
exec_df["mid_price"] = exec_df["market5s"].apply(lambda x: market_df.loc[x, "mid_price"])

Trade quality estimation: dmid5 = ( mid_price_after_5_seconds - our_trade_price ) * trade_direction_side

In [20]:

# trade estimation after 5 sec
exec_df["dmid5"] = (exec_df["mid_price"] - exec_df["price"]) * exec_df["side"]
exec_df

Out[20]:

	ts	side	price	delta	plus_5s	market5s	mid_price	dmid5
0	1622505601222248272	1	1108.6	30451968	1622505606222248272	1622505606366905152	1108.65	0.05
1	1622505601424113404	-1	1108.4	17118720	1622505606424113404	1622505606673089976	1108.45	-0.05
2	1622505601604667896	1	1108.3	153866496	1622505606604667896	1622505606673089976	1108.45	0.15
3	1622505602982200992	-1	1108.4	153371136	1622505607982200992	1622505608138958112	1108.55	-0.15
4	1622505603600579508	1	1108.3	618378496	1622505608600579508	1622505608664219320	1108.35	0.05
...	...	...	...	...	...	...	...	...
8677	1627626326117581668	1	1149.9	2381312	1627626331117581668	1627626333578804336	1149.85	-0.05
8678	1627626397873668620	-1	1150.2	1296281088	1627626402873668620	1627626406112129348	1150.15	0.05
8679	1627626412584847188	1	1150.1	14703859968	1627626417584847188	1627626419313019568	1150.15	0.05
8680	1627626473991536064	-1	1150.4	12134184704	1627626478991536064	1627626480273596688	1150.45	-0.05
8681	1627626512859773452	1	1150.6	14734981632	1627626517859773452	1627626518063036448	1150.55	-0.05

8682 rows × 8 columns

In [21]:

# total result
exec_df["dmid5"].sum()

Out[21]:

-70.54999999999313

In [22]:

exec_df["dmid5"].describe()

Out[22]:

count    8682.000000
mean       -0.008126
std         0.265246
min       -15.650000
25%        -0.050000
50%         0.000000
75%         0.050000
max         3.750000
Name: dmid5, dtype: float64

In [23]:

plt.plot(exec_df["dmid5"])

Out[23]:

[<matplotlib.lines.Line2D at 0x1fd02101c10>]

In [24]:

exec_df["delta"].describe()

Out[24]:

count    8.682000e+03
mean     2.954101e+10
std      5.537083e+10
min      6.451200e+04
25%      2.091328e+06
50%      7.287985e+09
75%      3.679348e+10
max      9.150647e+11
Name: delta, dtype: float64

In [25]:

plt.plot(exec_df["delta"] / 1_000_000_000)  # seconds

Out[25]:

[<matplotlib.lines.Line2D at 0x1fd02531b50>]

What is the correlation between dmid5 and delta_execsend?

In [26]:

# correlation between trade estimation and time delta
plt.scatter(exec_df["dmid5"], exec_df["delta"])

Out[26]:

<matplotlib.collections.PathCollection at 0x1fd0291f520>

In [27]:

corr = np.corrcoef(exec_df["dmid5"], exec_df["delta"])
print("Correlation: ", corr[0,1])

Correlation:  0.022883958081705544

=> No correlation between trade quality estimation dmid5 and execution send time delta