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 SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2023-03-02 10:00:00 11897.00 1 1 2023-03-02 09:59:59.993541965 1
1 2023-03-02 10:00:00 11897.25 6 2 2023-03-02 09:59:59.874850291 1
2 2023-03-02 10:00:00 11897.50 8 3 2023-03-02 09:59:59.859827385 1
3 2023-03-02 10:00:00 11896.75 6 1 2023-03-02 09:59:59.994670829 0
4 2023-03-02 10:00:00 11896.50 10 2 2023-03-02 09:59:59.952449109 0
5 2023-03-02 10:00:00 11896.25 14 3 2023-03-02 09:59:59.952450505 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_SIZE BID_UPDATE_TIME ASK_PRICE ASK_SIZE ASK_UPDATE_TIME LEVEL
0 2023-03-02 10:00:00 11896.75 6 2023-03-02 09:59:59.994670829 11897.00 1 2023-03-02 09:59:59.993541965 1
1 2023-03-02 10:00:00 11896.50 10 2023-03-02 09:59:59.952449109 11897.25 6 2023-03-02 09:59:59.874850291 2
2 2023-03-02 10:00:00 11896.25 14 2023-03-02 09:59:59.952450505 11897.50 8 2023-03-02 09:59:59.859827385 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_SIZE1              BID_UPDATE_TIME1  ASK_PRICE1  ASK_SIZE1              ASK_UPDATE_TIME1  BID_PRICE2  BID_SIZE2              BID_UPDATE_TIME2  ASK_PRICE2  ASK_SIZE2              ASK_UPDATE_TIME2  BID_PRICE3  BID_SIZE3              BID_UPDATE_TIME3  ASK_PRICE3  ASK_SIZE3              ASK_UPDATE_TIME3
0 2023-03-02 10:00:00    11896.75          6 2023-03-02 09:59:59.994670829     11897.0          1 2023-03-02 09:59:59.993541965     11896.5         10 2023-03-02 09:59:59.952449109    11897.25          6 2023-03-02 09:59:59.874850291    11896.25         14 2023-03-02 09:59:59.952450505     11897.5          8 2023-03-02 09:59:59.859827385

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 SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2023-03-02 10:00:00 11897.00 1 1 2023-03-02 09:59:59.993541965 1
1 2023-03-02 10:00:00 11897.25 6 2 2023-03-02 09:59:59.874850291 1
2 2023-03-02 10:00:00 11897.50 8 3 2023-03-02 09:59:59.859827385 1
3 2023-03-02 10:00:00 11897.75 12 4 2023-03-02 09:59:59.854529351 1
4 2023-03-02 10:00:00 11898.00 11 5 2023-03-02 09:59:59.871411363 1
... ... ... ... ... ... ...
1868 2023-03-02 10:00:00 977.75 1 1019 2023-03-01 17:59:59.997000000 0
1869 2023-03-02 10:00:00 643.75 1 1020 2023-03-01 17:59:59.997000000 0
1870 2023-03-02 10:00:00 200.00 1 1021 2023-03-01 17:59:59.997000000 0
1871 2023-03-02 10:00:00 111.00 1 1022 2023-03-01 17:59:59.997000000 0
1872 2023-03-02 10:00:00 1.00 1 1023 2023-03-01 17:59:59.997000000 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_SIZE BID_UPDATE_TIME ASK_PRICE ASK_SIZE ASK_UPDATE_TIME LEVEL
0 2023-03-02 10:00:00.000000000 11896.75 6 2023-03-02 09:59:59.994670829 11897.00 1 2023-03-02 09:59:59.993541965 1
1 2023-03-02 10:00:00.000000000 11896.50 10 2023-03-02 09:59:59.952449109 11897.25 6 2023-03-02 09:59:59.874850291 2
2 2023-03-02 10:00:00.000000000 11896.25 14 2023-03-02 09:59:59.952450505 11897.50 8 2023-03-02 09:59:59.859827385 3
3 2023-03-02 10:00:00.000348579 11896.75 6 2023-03-02 09:59:59.994670829 11897.25 6 2023-03-02 09:59:59.874850291 1
4 2023-03-02 10:00:00.000348579 11896.50 10 2023-03-02 09:59:59.952449109 11897.50 8 2023-03-02 09:59:59.859827385 2
... ... ... ... ... ... ... ... ...
766 2023-03-02 10:00:00.098763587 11897.25 11 2023-03-02 10:00:00.096910329 11898.00 8 2023-03-02 10:00:00.084378527 2
767 2023-03-02 10:00:00.098763587 11897.00 11 2023-03-02 10:00:00.007823277 11898.25 10 2023-03-02 10:00:00.063950379 3
768 2023-03-02 10:00:00.098859719 11897.50 2 2023-03-02 10:00:00.098859719 11897.75 3 2023-03-02 10:00:00.098763587 1
769 2023-03-02 10:00:00.098859719 11897.25 11 2023-03-02 10:00:00.096910329 11898.00 8 2023-03-02 10:00:00.084378527 2
770 2023-03-02 10:00:00.098859719 11897.00 11 2023-03-02 10:00:00.007823277 11898.25 10 2023-03-02 10:00:00.063950379 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 versions of book sweep: by price and by quantity. Book sweep by price, takes 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 SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2023-03-02 10:00:00 11897.00 1 1 2023-03-02 09:59:59.993541965 1
1 2023-03-02 10:00:00 11897.25 6 2 2023-03-02 09:59:59.874850291 1
2 2023-03-02 10:00:00 11897.50 8 3 2023-03-02 09:59:59.859827385 1
3 2023-03-02 10:00:00 11897.75 12 4 2023-03-02 09:59:59.854529351 1
4 2023-03-02 10:00:00 11898.00 11 5 2023-03-02 09:59:59.871411363 1
5 2023-03-02 10:00:00 11898.25 14 6 2023-03-02 09:59:59.865033659 1
6 2023-03-02 10:00:00 11898.50 13 7 2023-03-02 09:59:59.852086241 1
7 2023-03-02 10:00:00 11898.75 18 8 2023-03-02 09:59:59.853624043 1
8 2023-03-02 10:00:00 11899.00 16 9 2023-03-02 09:59:59.850077075 1
9 2023-03-02 10:00:00 11899.25 14 10 2023-03-02 09:59:59.963769599 1
10 2023-03-02 10:00:00 11896.75 6 1 2023-03-02 09:59:59.994670829 0
11 2023-03-02 10:00:00 11896.50 10 2 2023-03-02 09:59:59.952449109 0
12 2023-03-02 10:00:00 11896.25 14 3 2023-03-02 09:59:59.952450505 0
13 2023-03-02 10:00:00 11896.00 12 4 2023-03-02 09:59:59.952451831 0
14 2023-03-02 10:00:00 11895.75 12 5 2023-03-02 09:59:59.854319345 0
15 2023-03-02 10:00:00 11895.50 11 6 2023-03-02 09:59:59.851075041 0
16 2023-03-02 10:00:00 11895.25 13 7 2023-03-02 09:59:59.850647593 0
17 2023-03-02 10:00:00 11895.00 18 8 2023-03-02 09:59:59.851678883 0
18 2023-03-02 10:00:00 11894.75 16 9 2023-03-02 09:59:59.876564849 0
19 2023-03-02 10:00:00 11894.50 16 10 2023-03-02 09:59:59.867135353 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’ field that lets us break down the book by each value of the ‘key’ field. 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)
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 6842044209509 11896.75 1 57348279199 1 0 L
1 6842044209501 11896.75 1 57348279189 2 0 L
2 6842044209397 11896.75 1 57348279042 1 0 L
3 6842044209391 11896.75 1 57348279036 1 0 L
4 6842044103605 11896.75 1 57348279337 1 0 L

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