# 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'. ```ipython3 import onetick.py as otp s = otp.dt(2024, 2, 1, 10) prl = otp.ObSnapshot(db='CME_SAMPLE', 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=r'NQ\H24', start=s, end=s) ``` ```myst-ansi Time PRICE SIZE LEVEL UPDATE_TIME \ 0 2024-02-01 10:00:00 17303.50 3 1 2024-02-01 09:59:59.737771351 1 2024-02-01 10:00:00 17303.75 1 2 2024-02-01 09:59:59.968007113 2 2024-02-01 10:00:00 17304.00 3 3 2024-02-01 09:59:59.823591575 3 2024-02-01 10:00:00 17300.25 4 1 2024-02-01 09:59:59.682656319 4 2024-02-01 10:00:00 17299.50 1 2 2024-02-01 09:59:59.798148709 5 2024-02-01 10:00:00 17299.25 1 3 2024-02-01 09:59:59.881654499 BUY_SELL_FLAG 0 1 1 1 2 1 3 0 4 0 5 0 ``` Alternatively, a book can show a tick per level with both ask and bid price/size info. ```ipython3 prl = otp.ObSnapshotWide(db='CME_SAMPLE', tick_type='PRL_FULL', max_levels=3) otp.run(prl, symbols=r'NQ\H24', start=s, end=s) ``` ```myst-ansi Time BID_PRICE BID_SIZE BID_UPDATE_TIME \ 0 2024-02-01 10:00:00 17300.25 4 2024-02-01 09:59:59.682656319 1 2024-02-01 10:00:00 17299.50 1 2024-02-01 09:59:59.798148709 2 2024-02-01 10:00:00 17299.25 1 2024-02-01 09:59:59.881654499 ASK_PRICE ASK_SIZE ASK_UPDATE_TIME LEVEL 0 17303.50 3 2024-02-01 09:59:59.737771351 1 1 17303.75 1 2024-02-01 09:59:59.968007113 2 2 17304.00 3 2024-02-01 09:59:59.823591575 3 ``` Finally, all levels can be displayed in one tick. ```ipython3 prl = otp.ObSnapshotFlat(db='CME_SAMPLE', tick_type='PRL_FULL', max_levels=3) print(otp.run(prl, symbols=r'NQ\H24', start=s, end=s)) ``` ```myst-ansi Time BID_PRICE1 BID_SIZE1 BID_UPDATE_TIME1 \ 0 2024-02-01 10:00:00 17300.25 4 2024-02-01 09:59:59.682656319 ASK_PRICE1 ASK_SIZE1 ASK_UPDATE_TIME1 BID_PRICE2 BID_SIZE2 \ 0 17303.5 3 2024-02-01 09:59:59.737771351 17299.5 1 BID_UPDATE_TIME2 ASK_PRICE2 ASK_SIZE2 \ 0 2024-02-01 09:59:59.798148709 17303.75 1 ASK_UPDATE_TIME2 BID_PRICE3 BID_SIZE3 \ 0 2024-02-01 09:59:59.968007113 17299.25 1 BID_UPDATE_TIME3 ASK_PRICE3 ASK_SIZE3 \ 0 2024-02-01 09:59:59.881654499 17304.0 3 ASK_UPDATE_TIME3 0 2024-02-01 09:59:59.823591575 ``` We can output the book (in any of the three representation) on every change to price/size at any of the levels. ```ipython3 prl = otp.ObSnapshotFlat(db='CME_SAMPLE', tick_type='PRL_FULL', max_levels=3, running=True) prl = prl.drop(r".+TIME\d") print(otp.run(prl, symbols=r'NQ\H24', start=s, end=s + otp.Milli(100))) ``` ```myst-ansi Time BID_PRICE1 BID_SIZE1 ASK_PRICE1 \ 0 2024-02-01 10:00:00.000000000 17300.25 4 17303.50 1 2024-02-01 10:00:00.000005635 17281.75 1 17284.75 2 2024-02-01 10:00:00.000012009 17281.75 1 17284.75 3 2024-02-01 10:00:00.000035337 17276.00 4 17264.50 4 2024-02-01 10:00:00.002605599 17296.75 2 17264.50 5 2024-02-01 10:00:00.002615525 17296.75 2 17264.50 6 2024-02-01 10:00:00.003657751 17296.75 2 17264.50 7 2024-02-01 10:00:00.004015603 17300.25 1 17264.50 8 2024-02-01 10:00:00.006940703 17300.25 1 17264.50 9 2024-02-01 10:00:00.022899799 17300.25 1 17264.50 10 2024-02-01 10:00:00.032787693 17300.25 2 17264.50 11 2024-02-01 10:00:00.043854201 17300.25 2 17264.50 12 2024-02-01 10:00:00.054669411 17300.25 1 17264.50 13 2024-02-01 10:00:00.074736715 17300.25 1 17254.00 14 2024-02-01 10:00:00.083903067 17300.25 1 17253.00 ASK_SIZE1 BID_PRICE2 BID_SIZE2 ASK_PRICE2 ASK_SIZE2 BID_PRICE3 \ 0 3 17299.50 1 17303.75 1 17299.25 1 1 17281.50 2 17292.75 30 17281.25 2 1 17281.50 2 17303.50 3 17281.25 3 2 17275.75 1 17264.75 1 17275.50 4 2 17276.00 4 17264.75 1 17275.75 5 2 17295.50 1 17264.75 1 17276.00 6 2 17295.50 1 17264.75 1 17285.25 7 2 17296.75 2 17264.75 1 17295.50 8 2 17297.00 1 17264.75 1 17296.75 9 2 17299.25 15 17264.75 1 17297.00 10 2 17299.25 15 17264.75 1 17297.00 11 2 17297.00 1 17264.75 1 17296.75 12 2 17297.00 1 17264.75 1 17296.75 13 9 17297.00 1 17264.50 2 17296.75 14 3 17297.00 1 17254.00 9 17296.75 BID_SIZE3 ASK_PRICE3 ASK_SIZE3 0 1 17304.00 3 1 1 17303.50 3 2 1 17303.75 1 3 7 17265.00 1 4 1 17265.00 1 5 4 17265.00 1 6 1 17265.00 1 7 1 17265.00 1 8 2 17265.00 1 9 1 17265.00 1 10 1 17265.00 1 11 2 17265.00 1 12 2 17265.00 1 13 2 17264.75 1 14 2 17264.50 2 ``` The [`otp.ObSnapshot`](../../api/sources/order_book/ob_snapshot.md#onetick.py.ObSnapshot) method doesn't require specifying `max_levels`. The entire book is returned when the parameter is not specified. ```ipython3 prl = otp.ObSnapshot(db='CME_SAMPLE', tick_type='PRL_FULL') otp.run(prl, symbols=r'NQ\H24', start=s, end=s) ``` ```myst-ansi Time PRICE SIZE LEVEL UPDATE_TIME \ 0 2024-02-01 10:00:00 17303.50 3 1 2024-02-01 09:59:59.737771351 1 2024-02-01 10:00:00 17303.75 1 2 2024-02-01 09:59:59.968007113 2 2024-02-01 10:00:00 17304.00 3 3 2024-02-01 09:59:59.823591575 3 2024-02-01 10:00:00 17304.25 3 4 2024-02-01 09:59:59.668640001 4 2024-02-01 10:00:00 17304.50 4 5 2024-02-01 09:59:59.767992495 ... ... ... ... ... ... 1570 2024-02-01 10:00:00 11111.00 1 782 2024-01-31 17:59:59.998000000 1571 2024-02-01 10:00:00 10000.00 1 783 2024-01-31 17:59:59.998000000 1572 2024-02-01 10:00:00 9600.00 1 784 2024-01-31 17:59:59.998000000 1573 2024-02-01 10:00:00 622.00 1 785 2024-01-31 17:59:59.998000000 1574 2024-02-01 10:00:00 200.00 1 786 2024-01-31 17:59:59.998000000 BUY_SELL_FLAG 0 1 1 1 2 1 3 1 4 1 ... ... 1570 0 1571 0 1572 0 1573 0 1574 0 [1575 rows x 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. ```ipython3 x = 3 prl = otp.ObSnapshotWide(db='CME_SAMPLE', tick_type='PRL_FULL', max_levels=x, running=True) otp.run(prl, symbols=r'NQ\H24', start=s, end=s + otp.Milli(100)) ``` ```myst-ansi Time BID_PRICE BID_SIZE \ 0 2024-02-01 10:00:00.000000000 17300.25 4 1 2024-02-01 10:00:00.000000000 17299.50 1 2 2024-02-01 10:00:00.000000000 17299.25 1 3 2024-02-01 10:00:00.000005635 17281.75 1 4 2024-02-01 10:00:00.000005635 17281.50 2 5 2024-02-01 10:00:00.000005635 17281.25 1 6 2024-02-01 10:00:00.000012009 17281.75 1 7 2024-02-01 10:00:00.000012009 17281.50 2 8 2024-02-01 10:00:00.000012009 17281.25 1 9 2024-02-01 10:00:00.000035337 17276.00 4 10 2024-02-01 10:00:00.000035337 17275.75 1 11 2024-02-01 10:00:00.000035337 17275.50 7 12 2024-02-01 10:00:00.002605599 17296.75 2 13 2024-02-01 10:00:00.002605599 17276.00 4 14 2024-02-01 10:00:00.002605599 17275.75 1 15 2024-02-01 10:00:00.002615525 17296.75 2 16 2024-02-01 10:00:00.002615525 17295.50 1 17 2024-02-01 10:00:00.002615525 17276.00 4 18 2024-02-01 10:00:00.003657751 17296.75 2 19 2024-02-01 10:00:00.003657751 17295.50 1 20 2024-02-01 10:00:00.003657751 17285.25 1 21 2024-02-01 10:00:00.004015603 17300.25 1 22 2024-02-01 10:00:00.004015603 17296.75 2 23 2024-02-01 10:00:00.004015603 17295.50 1 24 2024-02-01 10:00:00.006940703 17300.25 1 25 2024-02-01 10:00:00.006940703 17297.00 1 26 2024-02-01 10:00:00.006940703 17296.75 2 27 2024-02-01 10:00:00.022899799 17300.25 1 28 2024-02-01 10:00:00.022899799 17299.25 15 29 2024-02-01 10:00:00.022899799 17297.00 1 30 2024-02-01 10:00:00.032787693 17300.25 2 31 2024-02-01 10:00:00.032787693 17299.25 15 32 2024-02-01 10:00:00.032787693 17297.00 1 33 2024-02-01 10:00:00.043854201 17300.25 2 34 2024-02-01 10:00:00.043854201 17297.00 1 35 2024-02-01 10:00:00.043854201 17296.75 2 36 2024-02-01 10:00:00.054669411 17300.25 1 37 2024-02-01 10:00:00.054669411 17297.00 1 38 2024-02-01 10:00:00.054669411 17296.75 2 39 2024-02-01 10:00:00.074736715 17300.25 1 40 2024-02-01 10:00:00.074736715 17297.00 1 41 2024-02-01 10:00:00.074736715 17296.75 2 42 2024-02-01 10:00:00.083903067 17300.25 1 43 2024-02-01 10:00:00.083903067 17297.00 1 44 2024-02-01 10:00:00.083903067 17296.75 2 BID_UPDATE_TIME ASK_PRICE ASK_SIZE \ 0 2024-02-01 09:59:59.682656319 17303.50 3 1 2024-02-01 09:59:59.798148709 17303.75 1 2 2024-02-01 09:59:59.881654499 17304.00 3 3 2024-02-01 09:59:50.090523363 17284.75 1 4 2024-02-01 09:59:57.426381413 17292.75 30 5 2024-02-01 09:59:50.088516037 17303.50 3 6 2024-02-01 09:59:50.090523363 17284.75 1 7 2024-02-01 09:59:57.426381413 17303.50 3 8 2024-02-01 09:59:50.088516037 17303.75 1 9 2024-02-01 09:59:50.078467295 17264.50 2 10 2024-02-01 09:59:50.077464561 17264.75 1 11 2024-02-01 09:59:50.076460353 17265.00 1 12 2024-02-01 10:00:00.002605599 17264.50 2 13 2024-02-01 09:59:50.078467295 17264.75 1 14 2024-02-01 09:59:50.077464561 17265.00 1 15 2024-02-01 10:00:00.002605599 17264.50 2 16 2024-02-01 10:00:00.002615525 17264.75 1 17 2024-02-01 09:59:50.078467295 17265.00 1 18 2024-02-01 10:00:00.002605599 17264.50 2 19 2024-02-01 10:00:00.002615525 17264.75 1 20 2024-02-01 10:00:00.003657751 17265.00 1 21 2024-02-01 10:00:00.004015603 17264.50 2 22 2024-02-01 10:00:00.002605599 17264.75 1 23 2024-02-01 10:00:00.002615525 17265.00 1 24 2024-02-01 10:00:00.004015603 17264.50 2 25 2024-02-01 10:00:00.006940703 17264.75 1 26 2024-02-01 10:00:00.002605599 17265.00 1 27 2024-02-01 10:00:00.004015603 17264.50 2 28 2024-02-01 10:00:00.022899799 17264.75 1 29 2024-02-01 10:00:00.006940703 17265.00 1 30 2024-02-01 10:00:00.032787693 17264.50 2 31 2024-02-01 10:00:00.022899799 17264.75 1 32 2024-02-01 10:00:00.006940703 17265.00 1 33 2024-02-01 10:00:00.032787693 17264.50 2 34 2024-02-01 10:00:00.006940703 17264.75 1 35 2024-02-01 10:00:00.002605599 17265.00 1 36 2024-02-01 10:00:00.054669411 17264.50 2 37 2024-02-01 10:00:00.006940703 17264.75 1 38 2024-02-01 10:00:00.002605599 17265.00 1 39 2024-02-01 10:00:00.054669411 17254.00 9 40 2024-02-01 10:00:00.006940703 17264.50 2 41 2024-02-01 10:00:00.002605599 17264.75 1 42 2024-02-01 10:00:00.054669411 17253.00 3 43 2024-02-01 10:00:00.006940703 17254.00 9 44 2024-02-01 10:00:00.002605599 17264.50 2 ASK_UPDATE_TIME LEVEL 0 2024-02-01 09:59:59.737771351 1 1 2024-02-01 09:59:59.968007113 2 2 2024-02-01 09:59:59.823591575 3 3 2024-02-01 10:00:00.000005635 1 4 2024-02-01 10:00:00.000005635 2 5 2024-02-01 09:59:59.737771351 3 6 2024-02-01 10:00:00.000005635 1 7 2024-02-01 09:59:59.737771351 2 8 2024-02-01 09:59:59.968007113 3 9 2024-02-01 10:00:00.000035337 1 10 2024-02-01 10:00:00.000035337 2 11 2024-02-01 10:00:00.000035337 3 12 2024-02-01 10:00:00.000035337 1 13 2024-02-01 10:00:00.000035337 2 14 2024-02-01 10:00:00.000035337 3 15 2024-02-01 10:00:00.000035337 1 16 2024-02-01 10:00:00.000035337 2 17 2024-02-01 10:00:00.000035337 3 18 2024-02-01 10:00:00.000035337 1 19 2024-02-01 10:00:00.000035337 2 20 2024-02-01 10:00:00.000035337 3 21 2024-02-01 10:00:00.000035337 1 22 2024-02-01 10:00:00.000035337 2 23 2024-02-01 10:00:00.000035337 3 24 2024-02-01 10:00:00.000035337 1 25 2024-02-01 10:00:00.000035337 2 26 2024-02-01 10:00:00.000035337 3 27 2024-02-01 10:00:00.000035337 1 28 2024-02-01 10:00:00.000035337 2 29 2024-02-01 10:00:00.000035337 3 30 2024-02-01 10:00:00.000035337 1 31 2024-02-01 10:00:00.000035337 2 32 2024-02-01 10:00:00.000035337 3 33 2024-02-01 10:00:00.000035337 1 34 2024-02-01 10:00:00.000035337 2 35 2024-02-01 10:00:00.000035337 3 36 2024-02-01 10:00:00.000035337 1 37 2024-02-01 10:00:00.000035337 2 38 2024-02-01 10:00:00.000035337 3 39 2024-02-01 10:00:00.074736715 1 40 2024-02-01 10:00:00.000035337 2 41 2024-02-01 10:00:00.000035337 3 42 2024-02-01 10:00:00.083903067 1 43 2024-02-01 10:00:00.074736715 2 44 2024-02-01 10:00:00.000035337 3 ``` Let's compute the total ask and bid volumes and the corresponding imbalance. ```ipython3 prl = otp.ObSnapshotWide(db='CME_SAMPLE', 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=r'NQ\H24', start=s, end=s + otp.Milli(100)) ``` ```myst-ansi Time ask_vol bid_vol imb 0 2024-02-01 10:00:00.000000000 7 6 -0.076923 1 2024-02-01 10:00:00.000005635 34 4 -0.789474 2 2024-02-01 10:00:00.000012009 5 4 -0.111111 3 2024-02-01 10:00:00.000035337 4 12 0.500000 4 2024-02-01 10:00:00.002605599 4 7 0.272727 5 2024-02-01 10:00:00.002615525 4 7 0.272727 6 2024-02-01 10:00:00.003657751 4 4 0.000000 7 2024-02-01 10:00:00.004015603 4 4 0.000000 8 2024-02-01 10:00:00.006940703 4 4 0.000000 9 2024-02-01 10:00:00.022899799 4 17 0.619048 10 2024-02-01 10:00:00.032787693 4 18 0.636364 11 2024-02-01 10:00:00.043854201 4 5 0.111111 12 2024-02-01 10:00:00.054669411 4 4 0.000000 13 2024-02-01 10:00:00.074736715 12 4 -0.500000 14 2024-02-01 10:00:00.083903067 14 4 -0.555556 ``` We can also compute that stats for the imbalance over time. ```ipython3 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=r'NQ\H24', start=s, end=s + otp.Milli(100)) ``` ```myst-ansi Time tw_imb mean stdev 0 2024-02-01 10:00:00.100 0.024032 0.025261 0.399156 ``` ## 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. ```ipython3 prl = otp.ObSnapshot(db='CME_SAMPLE', tick_type='PRL_FULL', max_levels=10) otp.run(prl, symbols=r'NQ\H24', start=s, end=s) ``` ```myst-ansi Time PRICE SIZE LEVEL UPDATE_TIME \ 0 2024-02-01 10:00:00 17303.50 3 1 2024-02-01 09:59:59.737771351 1 2024-02-01 10:00:00 17303.75 1 2 2024-02-01 09:59:59.968007113 2 2024-02-01 10:00:00 17304.00 3 3 2024-02-01 09:59:59.823591575 3 2024-02-01 10:00:00 17304.25 3 4 2024-02-01 09:59:59.668640001 4 2024-02-01 10:00:00 17304.50 4 5 2024-02-01 09:59:59.767992495 5 2024-02-01 10:00:00 17304.75 2 6 2024-02-01 09:59:59.968007113 6 2024-02-01 10:00:00 17305.00 8 7 2024-02-01 09:59:59.553379749 7 2024-02-01 10:00:00 17305.25 6 8 2024-02-01 09:59:59.553386813 8 2024-02-01 10:00:00 17305.50 1 9 2024-02-01 09:59:59.553375027 9 2024-02-01 10:00:00 17305.75 2 10 2024-02-01 09:59:59.553387949 10 2024-02-01 10:00:00 17300.25 4 1 2024-02-01 09:59:59.682656319 11 2024-02-01 10:00:00 17299.50 1 2 2024-02-01 09:59:59.798148709 12 2024-02-01 10:00:00 17299.25 1 3 2024-02-01 09:59:59.881654499 13 2024-02-01 10:00:00 17299.00 1 4 2024-02-01 09:59:59.882207547 14 2024-02-01 10:00:00 17298.75 1 5 2024-02-01 09:59:59.882657863 15 2024-02-01 10:00:00 17298.25 8 6 2024-02-01 09:59:59.681536321 16 2024-02-01 10:00:00 17298.00 3 7 2024-02-01 09:59:59.500380959 17 2024-02-01 10:00:00 17297.75 1 8 2024-02-01 09:59:59.251562445 18 2024-02-01 10:00:00 17297.50 2 9 2024-02-01 09:59:59.111633741 19 2024-02-01 10:00:00 17297.25 2 10 2024-02-01 09:59:59.883993699 BUY_SELL_FLAG 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 0 11 0 12 0 13 0 14 0 15 0 16 0 17 0 18 0 19 0 ``` ```ipython3 def side_to_direction(side): return 1 if side == 'ASK' else -1 def sweep_by_price(side, price): prl = otp.ObSnapshot(db='CME_SAMPLE', tick_type='PRL_FULL', side=side) direction = side_to_direction(side) prl = prl.where(direction * prl['PRICE'] <= direction * price) prl = prl.agg({'total_qty': otp.agg.sum('SIZE')}) return otp.run(prl, symbols=r'NQ\H24', start=s, end=s) print(sweep_by_price('BID', 11896)) print(sweep_by_price('ASK', 11898)) ``` ```myst-ansi Time total_qty 0 2024-02-01 10:00:00 2981 Time total_qty 0 2024-02-01 10:00:00 0 ``` ```ipython3 def sweep_by_qty(side, qty): prl = otp.ObSnapshot(db='CME_SAMPLE', tick_type='PRL_FULL', side=side) prl = prl.agg({'total_qty': otp.agg.sum('SIZE')}, running=True, all_fields=True) prl = prl.where(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=r'NQ\H24', start=s, end=s) print(sweep_by_qty('BID', 10)) print(sweep_by_qty('ASK', 10)) ``` ```myst-ansi Time VWAP 0 2024-02-01 10:00:00 17299.4 Time VWAP 0 2024-02-01 10:00:00 17303.9 ``` ## 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. ```ipython3 prl = otp.ObSnapshot('CME_SAMPLE', tick_type='PRL_FULL', side='BID', show_full_detail=True) orders = otp.run(prl, symbols=r'NQ\H24', start=s, end=s) orders = orders[['ORDER_ID', 'PRICE', 'LEVEL', 'TIME_PRIORITY', 'SIZE', 'BUY_SELL_FLAG', 'ORDER_TYPE']] orders.head() ``` ```myst-ansi ORDER_ID PRICE LEVEL TIME_PRIORITY SIZE BUY_SELL_FLAG \ 0 6849720601921 17300.25 1 67070105795 1 0 1 6849720601880 17300.25 1 67070105719 1 0 2 6849720601879 17300.25 1 67070105718 2 0 3 6849720600537 17299.50 2 67070105850 1 0 4 6849719227337 17299.25 3 67070105870 1 0 ORDER_TYPE 0 L 1 L 2 L 3 L 4 L ``` Market-by-order data can be used to analyze/validate the priority mechanism used by the exchange. ```ipython3 prl = otp.ObSnapshot('CME_SAMPLE', 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=r'NQ\H24', start=s, end=s) orders = orders[['ORDER_ID', 'PRICE', 'LEVEL', 'TIME_PRIORITY', 'SIZE', 'BUY_SELL_FLAG', 'ORDER_TYPE']] orders.head() ``` ```myst-ansi ORDER_ID PRICE LEVEL TIME_PRIORITY SIZE BUY_SELL_FLAG \ 0 6849720601879 17300.25 1 67070105718 2 0 1 6849720601880 17300.25 1 67070105719 1 0 2 6849720601921 17300.25 1 67070105795 1 0 3 6849720600537 17299.50 2 67070105850 1 0 4 6849719227337 17299.25 3 67070105870 1 0 ORDER_TYPE 0 L 1 L 2 L 3 L 4 L ```