Effective spread#
Each example is a self-contained script that can be run against the OneTick Cloud sample databases.
# onetick-py WebAPI configuration for OneTick Cloud
import os
os.environ['OTP_WEBAPI'] = '1'
os.environ['OTP_HTTP_ADDRESS'] = 'https://rest.cloud.onetick.com'
os.environ['OTP_ACCESS_TOKEN_URL'] = 'https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token'
os.environ['OTP_CLIENT_ID'] = '__FILL_IN__'
os.environ['OTP_CLIENT_SECRET'] = '__FILL_IN__'
To calculate the effective spread of your orders,
you’ll need to first calculate the Volume Weighted Average Price (VWAP) for each order
and determine the prevailing mid-price (average of bid and ask prices) at the time of each order.
Then, you can use the formula for the effective spread:
EFFECTIVE_SPREAD = 2 * DIRECTION * EXEC_QTY * (VWAP - ARRIVAL_MID)
Joining Orders recorded in the sample Orders database to LSE Quotes.
As the dataset refers to a single venue, the QTE table is used for benchmarking performance.
The prevailing quote is associated with each order message
through an “as of” join with otp.join_by_time,
then the fill VWAP, ARRIVAL_MID and DIRECTION are aggregated per order ID.
import onetick.py as otp
order = otp.DataSource(db='S_ORDERS_LSE_SAMPLE', tick_type='ORDER')
order = order[['ID', 'PRICE_FILLED', 'QTY_FILLED', 'SIDE']]
order['DIRECTION'] = order.if_else(order['SIDE'] == 'BUY', 1, -1)
qte = otp.DataSource(db='LSE_SAMPLE', tick_type='QTE')
qte['MID_PRICE'] = (qte['ASK_PRICE'] + qte['BID_PRICE']) / 2
qte = qte[['MID_PRICE']]
joined = otp.join_by_time([order, qte])
data = joined.agg({
'VWAP_PRICE': otp.agg.vwap('PRICE_FILLED', 'QTY_FILLED'),
'EXEC_COUNT': otp.agg.count(),
'EXEC_QTY': otp.agg.sum('QTY_FILLED'),
'ARRIVAL_MID': otp.agg.first('MID_PRICE'),
'DIRECTION': otp.agg.first('DIRECTION'),
}, group_by=['ID'])
data = data.where(data['EXEC_QTY'] > 0)
data['EFFECTIVE_SPREAD'] = 2 * data['DIRECTION'] * data['EXEC_QTY'] * (data['VWAP_PRICE'] - data['ARRIVAL_MID'])
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='Europe/London',
symbols='VOD')
result
Time |
ID |
VWAP_PRICE |
EXEC_COUNT |
EXEC_QTY |
ARRIVAL_MID |
DIRECTION |
EFFECTIVE_SPREAD |
|
|---|---|---|---|---|---|---|---|---|
0 |
2024-01-04 |
233402424600089290 |
70.37 |
5 |
1987 |
70.390 |
1 |
-79.48 |
1 |
2024-01-04 |
233402424600163510 |
70.28 |
3 |
1214 |
70.290 |
1 |
-24.28 |
2 |
2024-01-04 |
233402424600242327 |
70.70 |
3 |
750 |
70.710 |
1 |
-15.00 |
3 |
2024-01-04 |
233402424600242348 |
70.73 |
2 |
750 |
70.710 |
-1 |
-30.00 |
4 |
2024-01-04 |
233402424600242354 |
70.73 |
2 |
750 |
70.710 |
-1 |
-30.00 |
.. |
… |
… |
… |
… |
… |
… |
… |
… |
25 |
2024-01-04 |
233402424601044819 |
69.97 |
1 |
1423 |
69.975 |
1 |
-14.23 |
26 |
2024-01-04 |
233402424601076779 |
69.89 |
5 |
75 |
69.895 |
-1 |
0.75 |
27 |
2024-01-04 |
233402424601108653 |
69.95 |
3 |
999 |
69.955 |
1 |
-9.99 |
28 |
2024-01-04 |
233402424601108753 |
69.95 |
3 |
5 |
69.955 |
-1 |
0.05 |
29 |
2024-01-04 |
233402424601134016 |
69.86 |
3 |
700 |
69.860 |
1 |
0.00 |