{ "cells": [ { "attachments": {}, "cell_type": "markdown", "id": "c4ce8bd1-58dc-46f5-b290-20d07185e2f0", "metadata": {}, "source": [ "# Order Book Analytics" ] }, { "attachments": {}, "cell_type": "markdown", "id": "61ba94e2-c191-459c-a5ca-7413cd01ec36", "metadata": { "tags": [] }, "source": [ "`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.\n", "\n", "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'." ] }, { "cell_type": "code", "execution_count": 7, "id": "0ebfd093-d330-4244-a383-1e1f42f30547", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimePRICEUPDATE_TIMESIZELEVELBUY_SELL_FLAG
02023-03-02 10:00:0011897.002023-03-02 09:59:59.993541965111
12023-03-02 10:00:0011897.252023-03-02 09:59:59.874850291621
22023-03-02 10:00:0011897.502023-03-02 09:59:59.859827385831
32023-03-02 10:00:0011896.752023-03-02 09:59:59.994670829610
42023-03-02 10:00:0011896.502023-03-02 09:59:59.9524491091020
52023-03-02 10:00:0011896.252023-03-02 09:59:59.9524505051430
\n", "
" ], "text/plain": [ " Time PRICE UPDATE_TIME SIZE LEVEL BUY_SELL_FLAG\n", "0 2023-03-02 10:00:00 11897.00 2023-03-02 09:59:59.993541965 1 1 1\n", "1 2023-03-02 10:00:00 11897.25 2023-03-02 09:59:59.874850291 6 2 1\n", "2 2023-03-02 10:00:00 11897.50 2023-03-02 09:59:59.859827385 8 3 1\n", "3 2023-03-02 10:00:00 11896.75 2023-03-02 09:59:59.994670829 6 1 0\n", "4 2023-03-02 10:00:00 11896.50 2023-03-02 09:59:59.952449109 10 2 0\n", "5 2023-03-02 10:00:00 11896.25 2023-03-02 09:59:59.952450505 14 3 0" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import onetick.py as otp\n", "\n", "s = otp.dt(2023, 3, 2, 10)\n", "\n", "prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', max_levels=3)\n", "# we can use the same timestamp for the start an the end times when we just need a snapshot\n", "otp.run(prl, symbols='NQ\\H23', start=s, end=s) " ] }, { "attachments": {}, "cell_type": "markdown", "id": "0dc1c79a-86ba-4409-a335-ec3d300017f2", "metadata": {}, "source": [ "Alternatively, a book can show a tick per level with both ask and bid price/size info." ] }, { "cell_type": "code", "execution_count": 8, "id": "23c0f013-63d4-4365-a78c-930e3a84399d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimeBID_PRICEBID_UPDATE_TIMEBID_SIZEASK_PRICEASK_UPDATE_TIMEASK_SIZELEVEL
02023-03-02 10:00:0011896.752023-03-02 09:59:59.994670829611897.002023-03-02 09:59:59.99354196511
12023-03-02 10:00:0011896.502023-03-02 09:59:59.9524491091011897.252023-03-02 09:59:59.87485029162
22023-03-02 10:00:0011896.252023-03-02 09:59:59.9524505051411897.502023-03-02 09:59:59.85982738583
\n", "
" ], "text/plain": [ " Time BID_PRICE BID_UPDATE_TIME BID_SIZE ASK_PRICE ASK_UPDATE_TIME ASK_SIZE LEVEL\n", "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\n", "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\n", "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" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "prl = otp.ObSnapshotWide(db='CME', tick_type='PRL_FULL', max_levels=3)\n", "otp.run(prl, symbols='NQ\\H23', start=s, end=s)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "4c013e5c-2958-4b08-86e3-18fb07df015e", "metadata": {}, "source": [ "Finally, all levels can be displayed in one tick." ] }, { "cell_type": "code", "execution_count": 9, "id": "92e19c89-78f4-4f9b-a8ba-20080e62f367", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 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\n", "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\n" ] } ], "source": [ "prl = otp.ObSnapshotFlat(db='CME', tick_type='PRL_FULL', max_levels=3)\n", "print(otp.run(prl, symbols='NQ\\H23', start=s, end=s))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "621e5e61-c69c-4bad-9b95-81aef68fb6df", "metadata": {}, "source": [ "We can output the book (in any of the three representation) on every change to price/size at any of the levels." ] }, { "cell_type": "code", "execution_count": 10, "id": "ccf59231-77dc-43b6-935a-f87fd0ef1456", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 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\n", "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\n", "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\n", "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\n", "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\n", "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\n", ".. ... ... ... ... ... ... ... ... ... ... ... ... ...\n", "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\n", "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\n", "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\n", "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\n", "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\n", "\n", "[257 rows x 13 columns]\n" ] } ], "source": [ "prl = otp.ObSnapshotFlat(db='CME', tick_type='PRL_FULL', max_levels=3, running=True)\n", "prl = prl.drop(r\".+TIME\\d\")\n", "print(otp.run(prl, symbols='NQ\\H23', start=s, end=s + otp.Milli(100)))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "0543a589-233a-40c3-a232-039f6e4f266c", "metadata": {}, "source": [ "The {func}`otp.ObSnapshot ` method doesn't require specifying `max_levels`. The entire book is returned when the parameter is not specified." ] }, { "cell_type": "code", "execution_count": 11, "id": "bc86f6d1-a58f-49e8-9498-b274d2537815", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimePRICEUPDATE_TIMESIZELEVELBUY_SELL_FLAG
02023-03-02 10:00:0011897.002023-03-02 09:59:59.993541965111
12023-03-02 10:00:0011897.252023-03-02 09:59:59.874850291621
22023-03-02 10:00:0011897.502023-03-02 09:59:59.859827385831
32023-03-02 10:00:0011897.752023-03-02 09:59:59.8545293511241
42023-03-02 10:00:0011898.002023-03-02 09:59:59.8714113631151
.....................
18682023-03-02 10:00:00977.752023-03-01 17:59:59.997000000110190
18692023-03-02 10:00:00643.752023-03-01 17:59:59.997000000110200
18702023-03-02 10:00:00200.002023-03-01 17:59:59.997000000110210
18712023-03-02 10:00:00111.002023-03-01 17:59:59.997000000110220
18722023-03-02 10:00:001.002023-03-01 17:59:59.997000000110230
\n", "

1873 rows × 6 columns

\n", "
" ], "text/plain": [ " Time PRICE UPDATE_TIME SIZE LEVEL BUY_SELL_FLAG\n", "0 2023-03-02 10:00:00 11897.00 2023-03-02 09:59:59.993541965 1 1 1\n", "1 2023-03-02 10:00:00 11897.25 2023-03-02 09:59:59.874850291 6 2 1\n", "2 2023-03-02 10:00:00 11897.50 2023-03-02 09:59:59.859827385 8 3 1\n", "3 2023-03-02 10:00:00 11897.75 2023-03-02 09:59:59.854529351 12 4 1\n", "4 2023-03-02 10:00:00 11898.00 2023-03-02 09:59:59.871411363 11 5 1\n", "... ... ... ... ... ... ...\n", "1868 2023-03-02 10:00:00 977.75 2023-03-01 17:59:59.997000000 1 1019 0\n", "1869 2023-03-02 10:00:00 643.75 2023-03-01 17:59:59.997000000 1 1020 0\n", "1870 2023-03-02 10:00:00 200.00 2023-03-01 17:59:59.997000000 1 1021 0\n", "1871 2023-03-02 10:00:00 111.00 2023-03-01 17:59:59.997000000 1 1022 0\n", "1872 2023-03-02 10:00:00 1.00 2023-03-01 17:59:59.997000000 1 1023 0\n", "\n", "[1873 rows x 6 columns]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL') \n", "otp.run(prl, symbols='NQ\\H23', start=s, end=s)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "d020114d-c9fa-47da-be2d-2802b2695423", "metadata": {}, "source": [ "## Book Imbalance\n", "\n", "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.\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 13, "id": "91da6ba2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimeBID_PRICEBID_UPDATE_TIMEBID_SIZEASK_PRICEASK_UPDATE_TIMEASK_SIZELEVEL
02023-03-02 10:00:00.00000000011896.752023-03-02 09:59:59.994670829611897.002023-03-02 09:59:59.99354196511
12023-03-02 10:00:00.00000000011896.502023-03-02 09:59:59.9524491091011897.252023-03-02 09:59:59.87485029162
22023-03-02 10:00:00.00000000011896.252023-03-02 09:59:59.9524505051411897.502023-03-02 09:59:59.85982738583
32023-03-02 10:00:00.00034857911896.752023-03-02 09:59:59.994670829611897.252023-03-02 09:59:59.87485029161
42023-03-02 10:00:00.00034857911896.502023-03-02 09:59:59.9524491091011897.502023-03-02 09:59:59.85982738582
...........................
7662023-03-02 10:00:00.09876358711897.252023-03-02 10:00:00.0969103291111898.002023-03-02 10:00:00.08437852782
7672023-03-02 10:00:00.09876358711897.002023-03-02 10:00:00.0078232771111898.252023-03-02 10:00:00.063950379103
7682023-03-02 10:00:00.09885971911897.502023-03-02 10:00:00.098859719211897.752023-03-02 10:00:00.09876358731
7692023-03-02 10:00:00.09885971911897.252023-03-02 10:00:00.0969103291111898.002023-03-02 10:00:00.08437852782
7702023-03-02 10:00:00.09885971911897.002023-03-02 10:00:00.0078232771111898.252023-03-02 10:00:00.063950379103
\n", "

771 rows × 8 columns

\n", "
" ], "text/plain": [ " Time BID_PRICE BID_UPDATE_TIME BID_SIZE ASK_PRICE ASK_UPDATE_TIME ASK_SIZE LEVEL\n", "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\n", "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\n", "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\n", "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\n", "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\n", ".. ... ... ... ... ... ... ... ...\n", "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\n", "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\n", "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\n", "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\n", "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\n", "\n", "[771 rows x 8 columns]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "x = 3\n", "prl = otp.ObSnapshotWide(db='CME', tick_type='PRL_FULL', max_levels=x, running=True)\n", "otp.run(prl, symbols='NQ\\H23', start=s, end=s + otp.Milli(100))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "3ce5e5aa", "metadata": {}, "source": [ "Let's compute the total ask and bid volumes and the corresponding imbalance." ] }, { "cell_type": "code", "execution_count": 16, "id": "7b03f68f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Timeask_volbid_volimb
02023-03-02 10:00:00.00000000015300.333333
12023-03-02 10:00:00.00034857926300.071429
22023-03-02 10:00:00.00068659126310.087719
32023-03-02 10:00:00.00070472725310.107143
42023-03-02 10:00:00.00102019114310.377778
...............
2522023-03-02 10:00:00.09685313322320.185185
2532023-03-02 10:00:00.09691032922330.200000
2542023-03-02 10:00:00.09874223122230.022222
2552023-03-02 10:00:00.09876358721230.045455
2562023-03-02 10:00:00.09885971921240.066667
\n", "

257 rows × 4 columns

\n", "
" ], "text/plain": [ " Time ask_vol bid_vol imb\n", "0 2023-03-02 10:00:00.000000000 15 30 0.333333\n", "1 2023-03-02 10:00:00.000348579 26 30 0.071429\n", "2 2023-03-02 10:00:00.000686591 26 31 0.087719\n", "3 2023-03-02 10:00:00.000704727 25 31 0.107143\n", "4 2023-03-02 10:00:00.001020191 14 31 0.377778\n", ".. ... ... ... ...\n", "252 2023-03-02 10:00:00.096853133 22 32 0.185185\n", "253 2023-03-02 10:00:00.096910329 22 33 0.200000\n", "254 2023-03-02 10:00:00.098742231 22 23 0.022222\n", "255 2023-03-02 10:00:00.098763587 21 23 0.045455\n", "256 2023-03-02 10:00:00.098859719 21 24 0.066667\n", "\n", "[257 rows x 4 columns]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "prl = otp.ObSnapshotWide(db='CME', tick_type='PRL_FULL', max_levels=x, running=True)\n", "prl = prl.agg({'ask_vol': otp.agg.sum('ASK_SIZE'), 'bid_vol': otp.agg.sum('BID_SIZE')}, bucket_units='ticks', bucket_interval=x)\n", "prl['imb'] = (prl['bid_vol'] - prl['ask_vol']) / (prl['bid_vol'] + prl['ask_vol'])\n", "otp.run(prl, symbols='NQ\\H23', start=s, end=s + otp.Milli(100))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "2ff66bf7", "metadata": {}, "source": [ "We can also compute that stats for the imbalance over time." ] }, { "cell_type": "code", "execution_count": 18, "id": "313a302c-e485-4f47-82cb-9baeb8c6bc8b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Timetw_imbmeanstdev
02023-03-02 10:00:00.1000.0791440.0003670.1479
\n", "
" ], "text/plain": [ " Time tw_imb mean stdev\n", "0 2023-03-02 10:00:00.100 0.079144 0.000367 0.1479" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "imb_stats = prl.agg({\n", " 'tw_imb': otp.agg.tw_average('imb'),\n", " 'mean': otp.agg.average('imb'),\n", " 'stdev': otp.agg.stddev('imb'),\n", "})\n", "otp.run(imb_stats, symbols='NQ\\H23', start=s, end=s + otp.Milli(100))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "7f607216-7bce-4dcf-a84b-34540a668535", "metadata": {}, "source": [ "## Book sweep\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 21, "id": "f20b35a1", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimePRICEUPDATE_TIMESIZELEVELBUY_SELL_FLAG
02023-03-02 10:00:0011897.002023-03-02 09:59:59.993541965111
12023-03-02 10:00:0011897.252023-03-02 09:59:59.874850291621
22023-03-02 10:00:0011897.502023-03-02 09:59:59.859827385831
32023-03-02 10:00:0011897.752023-03-02 09:59:59.8545293511241
42023-03-02 10:00:0011898.002023-03-02 09:59:59.8714113631151
52023-03-02 10:00:0011898.252023-03-02 09:59:59.8650336591461
62023-03-02 10:00:0011898.502023-03-02 09:59:59.8520862411371
72023-03-02 10:00:0011898.752023-03-02 09:59:59.8536240431881
82023-03-02 10:00:0011899.002023-03-02 09:59:59.8500770751691
92023-03-02 10:00:0011899.252023-03-02 09:59:59.96376959914101
102023-03-02 10:00:0011896.752023-03-02 09:59:59.994670829610
112023-03-02 10:00:0011896.502023-03-02 09:59:59.9524491091020
122023-03-02 10:00:0011896.252023-03-02 09:59:59.9524505051430
132023-03-02 10:00:0011896.002023-03-02 09:59:59.9524518311240
142023-03-02 10:00:0011895.752023-03-02 09:59:59.8543193451250
152023-03-02 10:00:0011895.502023-03-02 09:59:59.8510750411160
162023-03-02 10:00:0011895.252023-03-02 09:59:59.8506475931370
172023-03-02 10:00:0011895.002023-03-02 09:59:59.8516788831880
182023-03-02 10:00:0011894.752023-03-02 09:59:59.8765648491690
192023-03-02 10:00:0011894.502023-03-02 09:59:59.86713535316100
\n", "
" ], "text/plain": [ " Time PRICE UPDATE_TIME SIZE LEVEL BUY_SELL_FLAG\n", "0 2023-03-02 10:00:00 11897.00 2023-03-02 09:59:59.993541965 1 1 1\n", "1 2023-03-02 10:00:00 11897.25 2023-03-02 09:59:59.874850291 6 2 1\n", "2 2023-03-02 10:00:00 11897.50 2023-03-02 09:59:59.859827385 8 3 1\n", "3 2023-03-02 10:00:00 11897.75 2023-03-02 09:59:59.854529351 12 4 1\n", "4 2023-03-02 10:00:00 11898.00 2023-03-02 09:59:59.871411363 11 5 1\n", "5 2023-03-02 10:00:00 11898.25 2023-03-02 09:59:59.865033659 14 6 1\n", "6 2023-03-02 10:00:00 11898.50 2023-03-02 09:59:59.852086241 13 7 1\n", "7 2023-03-02 10:00:00 11898.75 2023-03-02 09:59:59.853624043 18 8 1\n", "8 2023-03-02 10:00:00 11899.00 2023-03-02 09:59:59.850077075 16 9 1\n", "9 2023-03-02 10:00:00 11899.25 2023-03-02 09:59:59.963769599 14 10 1\n", "10 2023-03-02 10:00:00 11896.75 2023-03-02 09:59:59.994670829 6 1 0\n", "11 2023-03-02 10:00:00 11896.50 2023-03-02 09:59:59.952449109 10 2 0\n", "12 2023-03-02 10:00:00 11896.25 2023-03-02 09:59:59.952450505 14 3 0\n", "13 2023-03-02 10:00:00 11896.00 2023-03-02 09:59:59.952451831 12 4 0\n", "14 2023-03-02 10:00:00 11895.75 2023-03-02 09:59:59.854319345 12 5 0\n", "15 2023-03-02 10:00:00 11895.50 2023-03-02 09:59:59.851075041 11 6 0\n", "16 2023-03-02 10:00:00 11895.25 2023-03-02 09:59:59.850647593 13 7 0\n", "17 2023-03-02 10:00:00 11895.00 2023-03-02 09:59:59.851678883 18 8 0\n", "18 2023-03-02 10:00:00 11894.75 2023-03-02 09:59:59.876564849 16 9 0\n", "19 2023-03-02 10:00:00 11894.50 2023-03-02 09:59:59.867135353 16 10 0" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', max_levels=10)\n", "otp.run(prl, symbols='NQ\\H23', start=s, end=s)" ] }, { "cell_type": "code", "execution_count": 25, "id": "97d3429b-4c5f-4ebd-a155-8593301a557a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Time total_qty\n", "0 2023-03-02 10:00:00 42\n", " Time total_qty\n", "0 2023-03-02 10:00:00 38\n" ] } ], "source": [ "def side_to_direction(side):\n", " return 1 if side == 'ASK' else -1\n", "\n", "def sweep_by_price(side, price):\n", " prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', side=side)\n", " direction = side_to_direction(side)\n", " prl, _ = prl[direction * prl['PRICE'] <= direction * price]\n", " prl = prl.agg({'total_qty': otp.agg.sum('SIZE')})\n", " return otp.run(prl, symbols='NQ\\H23', start=s, end=s)\n", "\n", "print(sweep_by_price('BID', 11896))\n", "print(sweep_by_price('ASK', 11898))" ] }, { "cell_type": "code", "execution_count": 26, "id": "c40a0eb2-aa7b-4800-b28b-a69d4c719097", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Time VWAP\n", "0 2023-03-02 10:00:00 11896.65\n", " Time VWAP\n", "0 2023-03-02 10:00:00 11897.3\n" ] } ], "source": [ "def sweep_by_qty(side, qty):\n", " prl = otp.ObSnapshot(db='CME', tick_type='PRL_FULL', side=side)\n", " prl = prl.agg({'total_qty': otp.agg.sum('SIZE')}, running=True, all_fields=True)\n", " direction = side_to_direction(side)\n", " prl, _ = prl[prl['total_qty'] - prl['SIZE'] < qty]\n", " # update the SIZE in the last tick only so that total_qty is exactly qty\n", " prl['SIZE'] = prl.apply(lambda row: row['SIZE'] - (row['total_qty'] - qty) if row['total_qty'] > qty else row['SIZE'])\n", " prl = prl.agg({'VWAP': otp.agg.vwap('PRICE', 'SIZE')})\n", " return otp.run(prl, symbols='NQ\\H23', start=s, end=s)\n", "print(sweep_by_qty('BID', 10))\n", "print(sweep_by_qty('ASK', 10))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "0aa8f44c-073a-4078-b5dd-7664c26fc3fe", "metadata": {}, "source": [ "## Market By Order\n", "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." ] }, { "cell_type": "code", "execution_count": 28, "id": "77e07c18-6e71-4b05-825b-bb5bdb54518d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Time ORDER_ID BUY_SELL_FLAG ORDER_TYPE PRICE SIZE TIME_PRIORITY RECORD_TYPE DELETED_TIME TICK_STATUS OMDSEQ LEVEL UPDATE_TIME\n", "0 2023-03-02 10:00:00 6842044209509 0 L 11896.75 1 57348279199 R 1969-12-31 19:00:00 0 3 1 2023-03-02 09:59:59.872343629\n", "1 2023-03-02 10:00:00 6842044209501 0 L 11896.75 2 57348279189 R 1969-12-31 19:00:00 0 2 1 2023-03-02 09:59:59.871648081\n", "2 2023-03-02 10:00:00 6842044209397 0 L 11896.75 1 57348279042 R 1969-12-31 19:00:00 0 16 1 2023-03-02 09:59:59.859362705\n", "3 2023-03-02 10:00:00 6842044209391 0 L 11896.75 1 57348279036 R 1969-12-31 19:00:00 0 2 1 2023-03-02 09:59:59.859081005\n", "4 2023-03-02 10:00:00 6842044103605 0 L 11896.75 1 57348279337 R 1969-12-31 19:00:00 0 2 1 2023-03-02 09:59:59.952448809\n" ] } ], "source": [ "prl = otp.ObSnapshot('CME', tick_type='PRL_FULL', side='BID', show_full_detail=True)\n", "prl = prl.first(5)\n", "print(otp.run(prl, symbols='NQ\\H23', start=s, end=s))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "cf1d6f07-69d8-4017-a690-f4ff914cb027", "metadata": {}, "source": [ "Market-by-order data can be used to analyze/validate the priority mechanism used by the exchange.``" ] }, { "cell_type": "code", "execution_count": 29, "id": "05546b5b-f5b9-44e2-a722-acefb2c1662f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ORDER_IDPRICELEVELTIME_PRIORITYSIZEBUY_SELL_FLAGORDER_TYPE
0684204420939111896.7515734827903610L
1684204420939711896.7515734827904210L
2684204420950111896.7515734827918920L
3684204420950911896.7515734827919910L
4684204410360511896.7515734827933710L
\n", "
" ], "text/plain": [ " ORDER_ID PRICE LEVEL TIME_PRIORITY SIZE BUY_SELL_FLAG ORDER_TYPE\n", "0 6842044209391 11896.75 1 57348279036 1 0 L\n", "1 6842044209397 11896.75 1 57348279042 1 0 L\n", "2 6842044209501 11896.75 1 57348279189 2 0 L\n", "3 6842044209509 11896.75 1 57348279199 1 0 L\n", "4 6842044103605 11896.75 1 57348279337 1 0 L" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "prl = otp.ObSnapshot('CME', tick_type='PRL_FULL', side='BID', show_full_detail=True)\n", "\n", "\"\"\"\n", "ORDER_TYPE:\n", "L = Limit order\n", "I = Implied order\n", "\n", "Implied liquidity doesn’t have priority as it's always last to execute at any price level. \n", "It also doesn’t have an order ID, so the IDs that we see in the db are synthetic \n", "(consisting of 1 or 2 for the 1st/2nd implied level, and E/F for the buy/sell side respectively).\n", "\n", "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”),\n", "then by TIME_PRIORITY (lowest value comes first).\n", "\"\"\"\n", "prl = prl.sort(['LEVEL', 'ORDER_TYPE', 'TIME_PRIORITY'], ascending=[True, False, True])\n", "orders = otp.run(prl, symbols='NQ\\H23', start=s, end=s)\n", "orders = orders[['ORDER_ID', 'PRICE', 'LEVEL', 'TIME_PRIORITY','SIZE', 'BUY_SELL_FLAG', 'ORDER_TYPE']]\n", "orders.head()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.6" } }, "nbformat": 4, "nbformat_minor": 5 }