Order Book Analytics#
onetick-py offers functions for analyzing tick-by-tick order book. There are three representations of an order book. We’ll show top 3 levels only for the ease of exposition.
A book can be displayed with a tick per level per side. We refer to a level in the book as a ‘price level’ or ‘prl’.
import onetick.py as otp
s = otp.dt(2023, 3, 2, 10)
prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', max_levels=3)
# we can use the same timestamp for the start an the end times when we just need a snapshot
otp.run(prl, symbols='NQ\H23', start=s, end=s)
| Time | PRICE | UPDATE_TIME | SIZE | LEVEL | BUY_SELL_FLAG | |
|---|---|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00 | 11897.00 | 2023-03-02 09:59:59.993541965 | 1 | 1 | 1 |
| 1 | 2023-03-02 10:00:00 | 11897.25 | 2023-03-02 09:59:59.874850291 | 6 | 2 | 1 |
| 2 | 2023-03-02 10:00:00 | 11897.50 | 2023-03-02 09:59:59.859827385 | 8 | 3 | 1 |
| 3 | 2023-03-02 10:00:00 | 11896.75 | 2023-03-02 09:59:59.994670829 | 6 | 1 | 0 |
| 4 | 2023-03-02 10:00:00 | 11896.50 | 2023-03-02 09:59:59.952449109 | 10 | 2 | 0 |
| 5 | 2023-03-02 10:00:00 | 11896.25 | 2023-03-02 09:59:59.952450505 | 14 | 3 | 0 |
Alternatively, a book can show a tick per level with both ask and bid price/size info.
prl = otp.ObSnapshotWide(db='CME', tick_type='PRL_FULL', max_levels=3)
otp.run(prl, symbols='NQ\H23', start=s, end=s)
| Time | BID_PRICE | BID_UPDATE_TIME | BID_SIZE | ASK_PRICE | ASK_UPDATE_TIME | ASK_SIZE | LEVEL | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00 | 11896.75 | 2023-03-02 09:59:59.994670829 | 6 | 11897.00 | 2023-03-02 09:59:59.993541965 | 1 | 1 |
| 1 | 2023-03-02 10:00:00 | 11896.50 | 2023-03-02 09:59:59.952449109 | 10 | 11897.25 | 2023-03-02 09:59:59.874850291 | 6 | 2 |
| 2 | 2023-03-02 10:00:00 | 11896.25 | 2023-03-02 09:59:59.952450505 | 14 | 11897.50 | 2023-03-02 09:59:59.859827385 | 8 | 3 |
Finally, all levels can be displayed in one tick.
prl = otp.ObSnapshotFlat(db='CME', tick_type='PRL_FULL', max_levels=3)
print(otp.run(prl, symbols='NQ\H23', start=s, end=s))
Time BID_PRICE1 BID_UPDATE_TIME1 BID_SIZE1 ASK_PRICE1 ASK_UPDATE_TIME1 ASK_SIZE1 BID_PRICE2 BID_UPDATE_TIME2 BID_SIZE2 ASK_PRICE2 ASK_UPDATE_TIME2 ASK_SIZE2 BID_PRICE3 BID_UPDATE_TIME3 BID_SIZE3 ASK_PRICE3 ASK_UPDATE_TIME3 ASK_SIZE3
0 2023-03-02 10:00:00 11896.75 2023-03-02 09:59:59.994670829 6 11897.0 2023-03-02 09:59:59.993541965 1 11896.5 2023-03-02 09:59:59.952449109 10 11897.25 2023-03-02 09:59:59.874850291 6 11896.25 2023-03-02 09:59:59.952450505 14 11897.5 2023-03-02 09:59:59.859827385 8
We can output the book (in any of the three representation) on every change to price/size at any of the levels.
prl = otp.ObSnapshotFlat(db='CME', tick_type='PRL_FULL', max_levels=3, running=True)
prl = prl.drop(r".+TIME\d")
print(otp.run(prl, symbols='NQ\H23', start=s, end=s + otp.Milli(100)))
Time BID_PRICE1 BID_SIZE1 ASK_PRICE1 ASK_SIZE1 BID_PRICE2 BID_SIZE2 ASK_PRICE2 ASK_SIZE2 BID_PRICE3 BID_SIZE3 ASK_PRICE3 ASK_SIZE3
0 2023-03-02 10:00:00.000000000 11896.75 6 11897.00 1 11896.50 10 11897.25 6 11896.25 14 11897.50 8
1 2023-03-02 10:00:00.000348579 11896.75 6 11897.25 6 11896.50 10 11897.50 8 11896.25 14 11897.75 12
2 2023-03-02 10:00:00.000686591 11896.75 7 11897.25 6 11896.50 10 11897.50 8 11896.25 14 11897.75 12
3 2023-03-02 10:00:00.000704727 11896.75 7 11897.25 5 11896.50 10 11897.50 8 11896.25 14 11897.75 12
4 2023-03-02 10:00:00.001020191 11896.75 7 11897.00 1 11896.50 10 11897.25 5 11896.25 14 11897.50 8
.. ... ... ... ... ... ... ... ... ... ... ... ... ...
252 2023-03-02 10:00:00.096853133 11897.25 10 11897.75 4 11897.00 11 11898.00 8 11896.75 11 11898.25 10
253 2023-03-02 10:00:00.096910329 11897.25 11 11897.75 4 11897.00 11 11898.00 8 11896.75 11 11898.25 10
254 2023-03-02 10:00:00.098742231 11897.50 1 11897.75 4 11897.25 11 11898.00 8 11897.00 11 11898.25 10
255 2023-03-02 10:00:00.098763587 11897.50 1 11897.75 3 11897.25 11 11898.00 8 11897.00 11 11898.25 10
256 2023-03-02 10:00:00.098859719 11897.50 2 11897.75 3 11897.25 11 11898.00 8 11897.00 11 11898.25 10
[257 rows x 13 columns]
The otp.ObSnapshot method doesn’t require specifying max_levels. The entire book is returned when the parameter is not specified.
prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL')
otp.run(prl, symbols='NQ\H23', start=s, end=s)
| Time | PRICE | UPDATE_TIME | SIZE | LEVEL | BUY_SELL_FLAG | |
|---|---|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00 | 11897.00 | 2023-03-02 09:59:59.993541965 | 1 | 1 | 1 |
| 1 | 2023-03-02 10:00:00 | 11897.25 | 2023-03-02 09:59:59.874850291 | 6 | 2 | 1 |
| 2 | 2023-03-02 10:00:00 | 11897.50 | 2023-03-02 09:59:59.859827385 | 8 | 3 | 1 |
| 3 | 2023-03-02 10:00:00 | 11897.75 | 2023-03-02 09:59:59.854529351 | 12 | 4 | 1 |
| 4 | 2023-03-02 10:00:00 | 11898.00 | 2023-03-02 09:59:59.871411363 | 11 | 5 | 1 |
| ... | ... | ... | ... | ... | ... | ... |
| 1868 | 2023-03-02 10:00:00 | 977.75 | 2023-03-01 17:59:59.997000000 | 1 | 1019 | 0 |
| 1869 | 2023-03-02 10:00:00 | 643.75 | 2023-03-01 17:59:59.997000000 | 1 | 1020 | 0 |
| 1870 | 2023-03-02 10:00:00 | 200.00 | 2023-03-01 17:59:59.997000000 | 1 | 1021 | 0 |
| 1871 | 2023-03-02 10:00:00 | 111.00 | 2023-03-01 17:59:59.997000000 | 1 | 1022 | 0 |
| 1872 | 2023-03-02 10:00:00 | 1.00 | 2023-03-01 17:59:59.997000000 | 1 | 1023 | 0 |
1873 rows × 6 columns
Book Imbalance#
Let’s find the time weighted book imbalance. The imbalance at a given time is defined as the sum of the bid sizes at the top x levels minus the sum of the ask sizes at the top x levels divided by the sum of these two terms: the values close to 1 mean the book is much heavier on the bid side, close to -1 – on the ask side, equal to zero means the sizes are the same.
We display top 3 levels of the book first on every update at any of these levels. There are three ticks (one per level) to represent the book after each update.
x = 3
prl = otp.ObSnapshotWide(db='CME', tick_type='PRL_FULL', max_levels=x, running=True)
otp.run(prl, symbols='NQ\H23', start=s, end=s + otp.Milli(100))
| Time | BID_PRICE | BID_UPDATE_TIME | BID_SIZE | ASK_PRICE | ASK_UPDATE_TIME | ASK_SIZE | LEVEL | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00.000000000 | 11896.75 | 2023-03-02 09:59:59.994670829 | 6 | 11897.00 | 2023-03-02 09:59:59.993541965 | 1 | 1 |
| 1 | 2023-03-02 10:00:00.000000000 | 11896.50 | 2023-03-02 09:59:59.952449109 | 10 | 11897.25 | 2023-03-02 09:59:59.874850291 | 6 | 2 |
| 2 | 2023-03-02 10:00:00.000000000 | 11896.25 | 2023-03-02 09:59:59.952450505 | 14 | 11897.50 | 2023-03-02 09:59:59.859827385 | 8 | 3 |
| 3 | 2023-03-02 10:00:00.000348579 | 11896.75 | 2023-03-02 09:59:59.994670829 | 6 | 11897.25 | 2023-03-02 09:59:59.874850291 | 6 | 1 |
| 4 | 2023-03-02 10:00:00.000348579 | 11896.50 | 2023-03-02 09:59:59.952449109 | 10 | 11897.50 | 2023-03-02 09:59:59.859827385 | 8 | 2 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 766 | 2023-03-02 10:00:00.098763587 | 11897.25 | 2023-03-02 10:00:00.096910329 | 11 | 11898.00 | 2023-03-02 10:00:00.084378527 | 8 | 2 |
| 767 | 2023-03-02 10:00:00.098763587 | 11897.00 | 2023-03-02 10:00:00.007823277 | 11 | 11898.25 | 2023-03-02 10:00:00.063950379 | 10 | 3 |
| 768 | 2023-03-02 10:00:00.098859719 | 11897.50 | 2023-03-02 10:00:00.098859719 | 2 | 11897.75 | 2023-03-02 10:00:00.098763587 | 3 | 1 |
| 769 | 2023-03-02 10:00:00.098859719 | 11897.25 | 2023-03-02 10:00:00.096910329 | 11 | 11898.00 | 2023-03-02 10:00:00.084378527 | 8 | 2 |
| 770 | 2023-03-02 10:00:00.098859719 | 11897.00 | 2023-03-02 10:00:00.007823277 | 11 | 11898.25 | 2023-03-02 10:00:00.063950379 | 10 | 3 |
771 rows × 8 columns
Let’s compute the total ask and bid volumes and the corresponding imbalance.
prl = otp.ObSnapshotWide(db='CME', tick_type='PRL_FULL', max_levels=x, running=True)
prl = prl.agg({'ask_vol': otp.agg.sum('ASK_SIZE'), 'bid_vol': otp.agg.sum('BID_SIZE')}, bucket_units='ticks', bucket_interval=x)
prl['imb'] = (prl['bid_vol'] - prl['ask_vol']) / (prl['bid_vol'] + prl['ask_vol'])
otp.run(prl, symbols='NQ\H23', start=s, end=s + otp.Milli(100))
| Time | ask_vol | bid_vol | imb | |
|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00.000000000 | 15 | 30 | 0.333333 |
| 1 | 2023-03-02 10:00:00.000348579 | 26 | 30 | 0.071429 |
| 2 | 2023-03-02 10:00:00.000686591 | 26 | 31 | 0.087719 |
| 3 | 2023-03-02 10:00:00.000704727 | 25 | 31 | 0.107143 |
| 4 | 2023-03-02 10:00:00.001020191 | 14 | 31 | 0.377778 |
| ... | ... | ... | ... | ... |
| 252 | 2023-03-02 10:00:00.096853133 | 22 | 32 | 0.185185 |
| 253 | 2023-03-02 10:00:00.096910329 | 22 | 33 | 0.200000 |
| 254 | 2023-03-02 10:00:00.098742231 | 22 | 23 | 0.022222 |
| 255 | 2023-03-02 10:00:00.098763587 | 21 | 23 | 0.045455 |
| 256 | 2023-03-02 10:00:00.098859719 | 21 | 24 | 0.066667 |
257 rows × 4 columns
We can also compute that stats for the imbalance over time.
imb_stats = prl.agg({
'tw_imb': otp.agg.tw_average('imb'),
'mean': otp.agg.average('imb'),
'stdev': otp.agg.stddev('imb'),
})
otp.run(imb_stats, symbols='NQ\H23', start=s, end=s + otp.Milli(100))
| Time | tw_imb | mean | stdev | |
|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00.100 | 0.079144 | 0.000367 | 0.1479 |
Book sweep#
There are two version of book sweep: by price and by quantity. Book sweep by price, take a price as an input and returns the total quantity available at that price or better. Book sweep by quantity, takes a quantity as an input and returns the VWAP if the quantity were executed immediately.
prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', max_levels=10)
otp.run(prl, symbols='NQ\H23', start=s, end=s)
| Time | PRICE | UPDATE_TIME | SIZE | LEVEL | BUY_SELL_FLAG | |
|---|---|---|---|---|---|---|
| 0 | 2023-03-02 10:00:00 | 11897.00 | 2023-03-02 09:59:59.993541965 | 1 | 1 | 1 |
| 1 | 2023-03-02 10:00:00 | 11897.25 | 2023-03-02 09:59:59.874850291 | 6 | 2 | 1 |
| 2 | 2023-03-02 10:00:00 | 11897.50 | 2023-03-02 09:59:59.859827385 | 8 | 3 | 1 |
| 3 | 2023-03-02 10:00:00 | 11897.75 | 2023-03-02 09:59:59.854529351 | 12 | 4 | 1 |
| 4 | 2023-03-02 10:00:00 | 11898.00 | 2023-03-02 09:59:59.871411363 | 11 | 5 | 1 |
| 5 | 2023-03-02 10:00:00 | 11898.25 | 2023-03-02 09:59:59.865033659 | 14 | 6 | 1 |
| 6 | 2023-03-02 10:00:00 | 11898.50 | 2023-03-02 09:59:59.852086241 | 13 | 7 | 1 |
| 7 | 2023-03-02 10:00:00 | 11898.75 | 2023-03-02 09:59:59.853624043 | 18 | 8 | 1 |
| 8 | 2023-03-02 10:00:00 | 11899.00 | 2023-03-02 09:59:59.850077075 | 16 | 9 | 1 |
| 9 | 2023-03-02 10:00:00 | 11899.25 | 2023-03-02 09:59:59.963769599 | 14 | 10 | 1 |
| 10 | 2023-03-02 10:00:00 | 11896.75 | 2023-03-02 09:59:59.994670829 | 6 | 1 | 0 |
| 11 | 2023-03-02 10:00:00 | 11896.50 | 2023-03-02 09:59:59.952449109 | 10 | 2 | 0 |
| 12 | 2023-03-02 10:00:00 | 11896.25 | 2023-03-02 09:59:59.952450505 | 14 | 3 | 0 |
| 13 | 2023-03-02 10:00:00 | 11896.00 | 2023-03-02 09:59:59.952451831 | 12 | 4 | 0 |
| 14 | 2023-03-02 10:00:00 | 11895.75 | 2023-03-02 09:59:59.854319345 | 12 | 5 | 0 |
| 15 | 2023-03-02 10:00:00 | 11895.50 | 2023-03-02 09:59:59.851075041 | 11 | 6 | 0 |
| 16 | 2023-03-02 10:00:00 | 11895.25 | 2023-03-02 09:59:59.850647593 | 13 | 7 | 0 |
| 17 | 2023-03-02 10:00:00 | 11895.00 | 2023-03-02 09:59:59.851678883 | 18 | 8 | 0 |
| 18 | 2023-03-02 10:00:00 | 11894.75 | 2023-03-02 09:59:59.876564849 | 16 | 9 | 0 |
| 19 | 2023-03-02 10:00:00 | 11894.50 | 2023-03-02 09:59:59.867135353 | 16 | 10 | 0 |
def side_to_direction(side):
return 1 if side == 'ASK' else -1
def sweep_by_price(side, price):
prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', side=side)
direction = side_to_direction(side)
prl, _ = prl[direction * prl['PRICE'] <= direction * price]
prl = prl.agg({'total_qty': otp.agg.sum('SIZE')})
return otp.run(prl, symbols='NQ\H23', start=s, end=s)
print(sweep_by_price('BID', 11896))
print(sweep_by_price('ASK', 11898))
Time total_qty
0 2023-03-02 10:00:00 42
Time total_qty
0 2023-03-02 10:00:00 38
def sweep_by_qty(side, qty):
prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', side=side)
prl = prl.agg({'total_qty': otp.agg.sum('SIZE')}, running=True, all_fields=True)
direction = side_to_direction(side)
prl, _ = prl[prl['total_qty'] - prl['SIZE'] < qty]
# update the SIZE in the last tick only so that total_qty is exactly qty
prl['SIZE'] = prl.apply(lambda row: row['SIZE'] - (row['total_qty'] - qty) if row['total_qty'] > qty else row['SIZE'])
prl = prl.agg({'VWAP': otp.agg.vwap('PRICE', 'SIZE')})
return otp.run(prl, symbols='NQ\H23', start=s, end=s)
print(sweep_by_qty('BID', 10))
print(sweep_by_qty('ASK', 10))
Time VWAP
0 2023-03-02 10:00:00 11896.65
Time VWAP
0 2023-03-02 10:00:00 11897.3
Market By Order#
Order Book data may be annotated with ‘key’ fields lets us break down the book by each value of the ‘key’ fields. For example, a book could by keyed by market participant ID, allowing us to see the book with the orders of a given market participant only. Some exchanges provide ‘market-by-order’ data where the book is keyed by order id. Set show_full_detail to True to see the book broken down to the most granular level. The example below is a market-by-order book.
prl = otp.ObSnapshot('CME', tick_type='PRL_FULL', side='BID', show_full_detail=True)
prl = prl.first(5)
print(otp.run(prl, symbols='NQ\H23', start=s, end=s))
Time UPDATE_TYPE ORDER_ID BUY_SELL_FLAG ORDER_TYPE PRICE SIZE TIME_PRIORITY TRADE_ID FILL_SIZE RECORD_TYPE DELETED_TIME TICK_STATUS OMDSEQ LEVEL UPDATE_TIME
0 2023-03-02 10:00:00 A 6842044209509 0 L 11896.75 1 57348279199 0 R 1969-12-31 19:00:00 0 3 1 2023-03-02 09:59:59.872343629
1 2023-03-02 10:00:00 A 6842044209501 0 L 11896.75 2 57348279189 0 R 1969-12-31 19:00:00 0 2 1 2023-03-02 09:59:59.871648081
2 2023-03-02 10:00:00 A 6842044209397 0 L 11896.75 1 57348279042 0 R 1969-12-31 19:00:00 0 16 1 2023-03-02 09:59:59.859362705
3 2023-03-02 10:00:00 A 6842044209391 0 L 11896.75 1 57348279036 0 R 1969-12-31 19:00:00 0 2 1 2023-03-02 09:59:59.859081005
4 2023-03-02 10:00:00 M 6842044103605 0 L 11896.75 1 57348279337 0 R 1969-12-31 19:00:00 0 2 1 2023-03-02 09:59:59.952448809
Market-by-order data can be used to analyze/validate the priority mechanism used by the exchange.``
prl = otp.ObSnapshot('CME', tick_type='PRL_FULL', side='BID', show_full_detail=True)
"""
ORDER_TYPE:
L = Limit order
I = Implied order
Implied liquidity doesn’t have priority as it's always last to execute at any price level.
It also doesn’t have an order ID, so the IDs that we see in the db are synthetic
(consisting of 1 or 2 for the 1st/2nd implied level, and E/F for the buy/sell side respectively).
In order to rank the orders within a given price point by priority, we need to sort first by ORDER_TYPE (“L” comes before “I”),
then by TIME_PRIORITY (lowest value comes first).
"""
prl = prl.sort(['LEVEL', 'ORDER_TYPE', 'TIME_PRIORITY'], ascending=[True, False, True])
orders = otp.run(prl, symbols='NQ\H23', start=s, end=s)
orders = orders[['ORDER_ID', 'PRICE', 'LEVEL', 'TIME_PRIORITY','SIZE', 'BUY_SELL_FLAG', 'ORDER_TYPE']]
orders.head()
| ORDER_ID | PRICE | LEVEL | TIME_PRIORITY | SIZE | BUY_SELL_FLAG | ORDER_TYPE | |
|---|---|---|---|---|---|---|---|
| 0 | 6842044209391 | 11896.75 | 1 | 57348279036 | 1 | 0 | L |
| 1 | 6842044209397 | 11896.75 | 1 | 57348279042 | 1 | 0 | L |
| 2 | 6842044209501 | 11896.75 | 1 | 57348279189 | 2 | 0 | L |
| 3 | 6842044209509 | 11896.75 | 1 | 57348279199 | 1 | 0 | L |
| 4 | 6842044103605 | 11896.75 | 1 | 57348279337 | 1 | 0 | L |