Data Retrieval - Book Depth#

This section contains 11 examples for Data Retrieval - Book Depth using the onetick-py.
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__'

Book Depth at Quantity#

Calculate Book Depth Metrics such as Bid and Ask VWAP to trade 1000 shares every 60 seconds.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_summary(bucket_interval=60, max_depth_shares=1000)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 3, 16),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time BID_SIZE BID_VWAP BEST_BID_PRICE WORST_BID_PRICE NUM_BID_LEVELS ASK_SIZE ASK_VWAP BEST_ASK_PRICE WORST_ASK_PRICE NUM_ASK_LEVELS
0 2024-01-03 08:01:00 1000 70.34594 70.36 70.34 3 1000 70.45 70.45 70.45 1
1 2024-01-03 08:02:00 1000 70.50100 70.51 70.50 2 1000 70.60 70.60 70.60 1
2 2024-01-03 08:03:00 1000 70.41000 70.41 70.41 1 1000 70.51 70.51 70.51 1
3 2024-01-03 08:04:00 1000 70.39000 70.39 70.39 1 1000 70.49 70.49 70.49 1
4 2024-01-03 08:05:00 1000 70.43000 70.43 70.43 1 1000 70.50 70.50 70.50 1
... ... ... ... ... ... ... ... ... ... ... ...
475 2024-01-03 15:56:00 1000 69.70000 69.70 69.70 1 1000 69.72 69.72 69.72 1
476 2024-01-03 15:57:00 1000 69.66000 69.66 69.66 1 1000 69.68 69.68 69.68 1
477 2024-01-03 15:58:00 1000 69.65000 69.65 69.65 1 1000 69.67 69.67 69.67 1
478 2024-01-03 15:59:00 1000 69.65000 69.65 69.65 1 1000 69.67 69.67 69.67 1
479 2024-01-03 16:00:00 1000 69.65000 69.65 69.65 1 1000 69.68 69.68 69.68 1

480 rows × 11 columns

Book Depth at Time#

Retrieving the Book to MBL (Market by Level) at a specified time.
PRL_FULL indicates that the table is actually a MBO (Market by Order) data set.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot()
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 12),
                 end=otp.dt(2024, 1, 3, 12),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 12:00:00 70.19 1974 1 2024-01-03 11:59:59.366 1
1 2024-01-03 12:00:00 70.20 9912 2 2024-01-03 11:59:59.427 1
2 2024-01-03 12:00:00 70.21 27733 3 2024-01-03 11:59:59.629 1
3 2024-01-03 12:00:00 70.22 24690 4 2024-01-03 11:59:54.521 1
4 2024-01-03 12:00:00 70.23 36108 5 2024-01-03 11:59:59.415 1
... ... ... ... ... ... ...
309 2024-01-03 12:00:00 58.85 100 138 2024-01-03 07:50:00.074 0
310 2024-01-03 12:00:00 57.50 8638 139 2024-01-03 07:50:02.453 0
311 2024-01-03 12:00:00 55.00 7500 140 2024-01-03 07:50:03.117 0
312 2024-01-03 12:00:00 50.00 1007 141 2024-01-03 07:50:02.007 0
313 2024-01-03 12:00:00 40.00 25000 142 2024-01-03 05:00:07.876 0

314 rows × 6 columns

Book Depth at Time to Max Levels#

Retrieving the Book to MBO (Market by Order) at a specified time.
The returned book is limited to a maximum number of price levels.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(max_levels=5)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 12),
                 end=otp.dt(2024, 1, 3, 12),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 12:00:00 70.19 1974 1 2024-01-03 11:59:59.366 1
1 2024-01-03 12:00:00 70.20 9912 2 2024-01-03 11:59:59.427 1
2 2024-01-03 12:00:00 70.21 27733 3 2024-01-03 11:59:59.629 1
3 2024-01-03 12:00:00 70.22 24690 4 2024-01-03 11:59:54.521 1
4 2024-01-03 12:00:00 70.23 36108 5 2024-01-03 11:59:59.415 1
5 2024-01-03 12:00:00 70.16 10967 1 2024-01-03 11:59:59.696 0
6 2024-01-03 12:00:00 70.15 24145 2 2024-01-03 11:59:59.367 0
7 2024-01-03 12:00:00 70.14 17779 3 2024-01-03 11:59:53.976 0
8 2024-01-03 12:00:00 70.13 27285 4 2024-01-03 11:59:59.461 0
9 2024-01-03 12:00:00 70.12 28100 5 2024-01-03 11:59:59.603 0

Book Depth at Time to Max Price Skew#

Retrieving the Book to MBO (Market by Order) at a specified time.
The returned book is limited to a maximum price skew 0.005 = 0.5%.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(max_depth_for_price=0.005)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 12),
                 end=otp.dt(2024, 1, 3, 12),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 12:00:00 70.19 1974 1 2024-01-03 11:59:59.366 1
1 2024-01-03 12:00:00 70.20 9912 2 2024-01-03 11:59:59.427 1
2 2024-01-03 12:00:00 70.21 27733 3 2024-01-03 11:59:59.629 1
3 2024-01-03 12:00:00 70.22 24690 4 2024-01-03 11:59:54.521 1
4 2024-01-03 12:00:00 70.23 36108 5 2024-01-03 11:59:59.415 1
... ... ... ... ... ... ...
67 2024-01-03 12:00:00 69.85 8089 32 2024-01-03 11:25:07.933 0
68 2024-01-03 12:00:00 69.84 4348 33 2024-01-03 11:59:14.405 0
69 2024-01-03 12:00:00 69.83 4120 34 2024-01-03 11:26:59.933 0
70 2024-01-03 12:00:00 69.82 9189 35 2024-01-03 11:32:05.842 0
71 2024-01-03 12:00:00 69.81 4245 36 2024-01-03 11:36:00.933 0

72 rows × 6 columns

Book Depth at Time to Max Shares#

Retrieving the Book to MBO (Market by Order) at a specified time.
The returned book is limited to a maximum amount of accumulated size.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(max_depth_shares=10000)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 12),
                 end=otp.dt(2024, 1, 3, 12),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 12:00:00 70.19 1974 1 2024-01-03 11:59:59.366 1
1 2024-01-03 12:00:00 70.20 9912 2 2024-01-03 11:59:59.427 1
2 2024-01-03 12:00:00 70.16 10967 1 2024-01-03 11:59:59.696 0

Book Depth at Time to Max Spread#

Retrieving the Book to MBO (Market by Order) at a specified time.
The returned book is limited to a maximum absolute spread.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(max_spread=1)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 12),
                 end=otp.dt(2024, 1, 3, 12),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 12:00:00 70.19 1974 1 2024-01-03 11:59:59.366 1
1 2024-01-03 12:00:00 70.20 9912 2 2024-01-03 11:59:59.427 1
2 2024-01-03 12:00:00 70.21 27733 3 2024-01-03 11:59:59.629 1
3 2024-01-03 12:00:00 70.22 24690 4 2024-01-03 11:59:54.521 1
4 2024-01-03 12:00:00 70.23 36108 5 2024-01-03 11:59:59.415 1
... ... ... ... ... ... ...
83 2024-01-03 12:00:00 69.82 9189 35 2024-01-03 11:32:05.842 0
84 2024-01-03 12:00:00 69.81 4245 36 2024-01-03 11:36:00.933 0
85 2024-01-03 12:00:00 69.80 4244 37 2024-01-03 11:55:31.933 0
86 2024-01-03 12:00:00 69.76 915 38 2024-01-03 07:50:02.578 0
87 2024-01-03 12:00:00 69.68 1000 39 2024-01-03 08:12:55.650 0

88 rows × 6 columns

Book Depth to MBO at Time#

Retrieving the Book to MBO (Market by Order) at a specified time.
Using parameter show_full_detail=True.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(show_full_detail=True)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 12),
                 end=otp.dt(2024, 1, 3, 12),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time BUY_SELL_FLAG DELETED_TIME OMDSEQ ORDER_ID ORDER_TYPE PART_ID PRICE RECORD_TYPE SIZE TICK_STATUS UPDATE_TYPE LEVEL UPDATE_TIME SOURCE
0 2024-01-03 12:00:00 1 1970-01-01 01:00:00.000 13 233402424600626977 L 70.19 R 1974 0 A 1 2024-01-03 11:59:53.991 VOD
1 2024-01-03 12:00:00 1 1970-01-01 01:00:00.000 2 233402424600623482 L 70.20 R 1052 0 M 2 2024-01-03 11:59:54.328 VOD
2 2024-01-03 12:00:00 1 1970-01-01 01:00:00.000 49 233402424600626943 L 70.20 R 4716 0 A 2 2024-01-03 11:59:53.921 VOD
3 2024-01-03 12:00:00 1 1970-01-01 01:00:00.000 98 233402424600627607 L 70.20 R 4144 0 A 2 2024-01-03 11:59:59.427 VOD
4 2024-01-03 12:00:00 1 1970-01-01 01:00:00.000 10 233402424600625943 L 70.21 R 11824 0 M 3 2024-01-03 11:59:54.522 VOD
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
664 2024-01-03 12:00:00 0 2024-01-03 07:50:02.453 6 233402424600036160 L 57.50 R 8638 16 A 139 2024-01-03 07:50:02.453 VOD
665 2024-01-03 12:00:00 0 2024-01-03 07:50:03.117 6 233402424600036385 L 55.00 R 3500 16 A 140 2024-01-03 07:50:03.117 VOD
666 2024-01-03 12:00:00 0 2024-01-03 07:50:00.073 6 233402424600035618 L 55.00 R 4000 16 A 140 2024-01-03 07:50:00.073 VOD
667 2024-01-03 12:00:00 0 2024-01-03 07:50:02.007 6 233402424600035890 L 50.00 R 1007 16 A 141 2024-01-03 07:50:02.007 VOD
668 2024-01-03 12:00:00 0 2024-01-03 05:00:07.876 6 233244094925636438 L 40.00 R 25000 16 A 142 2024-01-03 05:00:07.876 VOD

669 rows × 15 columns

Order Book Bars#

Calculate 1 minute bars for book depth down to 5 levels from the LSE_SAMPLE database, and PRL_FULL table.
Each output row showing a Book Level, Side and Time (Bid and Ask on different rows).

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(bucket_interval=60, max_levels=5)
# Return first 100 Rows
data = data.limit(100)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 3, 16),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 08:01:00 70.45 5657 1 2024-01-03 08:00:59.243 1
1 2024-01-03 08:01:00 70.47 12161 2 2024-01-03 08:00:59.792 1
2 2024-01-03 08:01:00 70.48 108029 3 2024-01-03 08:00:58.503 1
3 2024-01-03 08:01:00 70.49 27858 4 2024-01-03 08:00:59.262 1
4 2024-01-03 08:01:00 70.50 20344 5 2024-01-03 08:00:59.056 1
... ... ... ... ... ... ...
95 2024-01-03 08:10:00 70.48 4251 1 2024-01-03 08:09:55.303 0
96 2024-01-03 08:10:00 70.47 100 2 2024-01-03 08:09:59.798 0
97 2024-01-03 08:10:00 70.46 16130 3 2024-01-03 08:09:59.041 0
98 2024-01-03 08:10:00 70.45 24564 4 2024-01-03 08:09:59.801 0
99 2024-01-03 08:10:00 70.44 20566 5 2024-01-03 08:09:30.629 0

100 rows × 6 columns

Order Book Flat Bars#

Calculate 1 minute bars for book depth down to 5 levels from the LSE_SAMPLE database, and PRL_FULL table.
Each output row showing the book at a specific time (many output columns for bids and asks at selected levels).

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot_flat(bucket_interval=60, max_levels=5)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 3, 16),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time BID_PRICE1 BID_SIZE1 BID_UPDATE_TIME1 ASK_PRICE1 ASK_SIZE1 ASK_UPDATE_TIME1 BID_PRICE2 BID_SIZE2 BID_UPDATE_TIME2 ... BID_UPDATE_TIME4 ASK_PRICE4 ASK_SIZE4 ASK_UPDATE_TIME4 BID_PRICE5 BID_SIZE5 BID_UPDATE_TIME5 ASK_PRICE5 ASK_SIZE5 ASK_UPDATE_TIME5
0 2024-01-03 08:01:00 70.36 247 2024-01-03 08:00:59.796 70.45 5657 2024-01-03 08:00:59.243 70.35 100 2024-01-03 08:00:59.243 ... 2024-01-03 08:00:59.796 70.49 27858 2024-01-03 08:00:59.262 70.32 6398 2024-01-03 08:00:58.187 70.50 20344 2024-01-03 08:00:59.056
1 2024-01-03 08:02:00 70.51 100 2024-01-03 08:01:59.461 70.60 2236 2024-01-03 08:01:58.413 70.50 1528 2024-01-03 08:01:59.461 ... 2024-01-03 08:01:57.911 70.63 27494 2024-01-03 08:01:58.613 70.47 9783 2024-01-03 08:01:57.702 70.64 26717 2024-01-03 08:01:59.013
2 2024-01-03 08:03:00 70.41 6814 2024-01-03 08:02:47.187 70.51 5093 2024-01-03 08:02:47.190 70.40 12411 2024-01-03 08:02:58.047 ... 2024-01-03 08:02:53.664 70.55 24048 2024-01-03 08:02:46.535 70.37 33771 2024-01-03 08:02:53.228 70.56 29900 2024-01-03 08:02:50.395
3 2024-01-03 08:04:00 70.39 1381 2024-01-03 08:03:57.934 70.49 7154 2024-01-03 08:03:57.933 70.38 7012 2024-01-03 08:03:57.945 ... 2024-01-03 08:03:59.065 70.52 41389 2024-01-03 08:03:58.233 70.35 8462 2024-01-03 08:03:55.984 70.53 16198 2024-01-03 08:03:57.934
4 2024-01-03 08:05:00 70.43 2682 2024-01-03 08:04:59.431 70.50 4716 2024-01-03 08:04:54.898 70.42 7867 2024-01-03 08:04:54.544 ... 2024-01-03 08:04:54.638 70.53 28692 2024-01-03 08:04:57.025 70.39 23024 2024-01-03 08:04:55.976 70.54 19374 2024-01-03 08:04:54.870
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
475 2024-01-03 15:56:00 69.70 12253 2024-01-03 15:55:57.273 69.72 41965 2024-01-03 15:55:57.311 69.69 34671 2024-01-03 15:55:57.873 ... 2024-01-03 15:55:58.067 69.75 41210 2024-01-03 15:55:57.559 69.66 39432 2024-01-03 15:55:57.873 69.76 35930 2024-01-03 15:55:57.272
476 2024-01-03 15:57:00 69.66 9432 2024-01-03 15:56:55.839 69.68 1355 2024-01-03 15:56:58.513 69.65 55792 2024-01-03 15:56:58.513 ... 2024-01-03 15:56:55.937 69.71 27985 2024-01-03 15:56:37.844 69.62 48358 2024-01-03 15:56:52.680 69.72 54166 2024-01-03 15:56:58.513
477 2024-01-03 15:58:00 69.65 4716 2024-01-03 15:57:59.779 69.67 6000 2024-01-03 15:57:59.832 69.64 24235 2024-01-03 15:57:59.780 ... 2024-01-03 15:57:59.779 69.70 29837 2024-01-03 15:57:59.819 69.61 47887 2024-01-03 15:57:59.781 69.71 48349 2024-01-03 15:57:58.759
478 2024-01-03 15:59:00 69.65 21004 2024-01-03 15:58:56.984 69.67 4716 2024-01-03 15:58:59.940 69.64 45649 2024-01-03 15:58:52.189 ... 2024-01-03 15:58:57.085 69.70 59837 2024-01-03 15:58:48.805 69.61 37037 2024-01-03 15:58:56.984 69.71 30449 2024-01-03 15:58:59.998
479 2024-01-03 16:00:00 69.65 17723 2024-01-03 15:59:59.915 69.68 10616 2024-01-03 15:59:59.918 69.64 42898 2024-01-03 15:59:53.614 ... 2024-01-03 15:59:58.397 69.71 65337 2024-01-03 15:59:59.915 69.61 25517 2024-01-03 15:59:59.915 69.72 34346 2024-01-03 15:59:41.322

480 rows × 31 columns

Order Book Updates#

Retrieve the changes in the Book across time.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot(running=True, show_only_changes=True)
# Return first 100 Rows
data = data.limit(100)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 3, 9),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time PRICE SIZE LEVEL UPDATE_TIME BUY_SELL_FLAG
0 2024-01-03 08:00:00 59.30 17048 1 2024-01-03 07:58:05.142 1
1 2024-01-03 08:00:00 66.49 24478 2 2024-01-03 07:50:48.472 1
2 2024-01-03 08:00:00 68.00 6000 3 2024-01-03 07:50:00.075 1
3 2024-01-03 08:00:00 68.45 2642 4 2024-01-03 07:59:58.750 1
4 2024-01-03 08:00:00 68.57 2642 5 2024-01-03 07:59:59.968 1
... ... ... ... ... ... ...
95 2024-01-03 08:00:00 82.00 29602 96 2024-01-03 07:50:03.129 1
96 2024-01-03 08:00:00 82.50 1544 97 2024-01-03 07:50:00.073 1
97 2024-01-03 08:00:00 82.53 570 98 2024-01-03 07:51:00.488 1
98 2024-01-03 08:00:00 82.75 1200 99 2024-01-03 07:50:00.075 1
99 2024-01-03 08:00:00 83.00 3234 100 2024-01-03 07:50:02.404 1

100 rows × 6 columns

Order Book Wide Bars#

Calculate 1 minute bars for book depth down to 5 levels from the LSE_SAMPLE database, and PRL_FULL table.
Each output row showing a Book Level and Time (Bid and Ask on same row).

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_snapshot_wide(bucket_interval=60, max_levels=5)
# Return first 100 Rows
data = data.limit(100)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 3, 16),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time BID_PRICE BID_SIZE BID_UPDATE_TIME ASK_PRICE ASK_SIZE ASK_UPDATE_TIME LEVEL
0 2024-01-03 08:01:00 70.36 247 2024-01-03 08:00:59.796 70.45 5657 2024-01-03 08:00:59.243 1
1 2024-01-03 08:01:00 70.35 100 2024-01-03 08:00:59.243 70.47 12161 2024-01-03 08:00:59.792 2
2 2024-01-03 08:01:00 70.34 6657 2024-01-03 08:00:59.792 70.48 108029 2024-01-03 08:00:58.503 3
3 2024-01-03 08:01:00 70.33 6377 2024-01-03 08:00:59.796 70.49 27858 2024-01-03 08:00:59.262 4
4 2024-01-03 08:01:00 70.32 6398 2024-01-03 08:00:58.187 70.50 20344 2024-01-03 08:00:59.056 5
... ... ... ... ... ... ... ... ...
95 2024-01-03 08:20:00 70.33 450 2024-01-03 08:19:53.329 70.37 10316 2024-01-03 08:19:53.330 1
96 2024-01-03 08:20:00 70.32 9074 2024-01-03 08:19:55.363 70.38 4716 2024-01-03 08:19:46.498 2
97 2024-01-03 08:20:00 70.31 8815 2024-01-03 08:19:53.330 70.39 25851 2024-01-03 08:19:46.504 3
98 2024-01-03 08:20:00 70.30 19903 2024-01-03 08:19:46.504 70.40 25838 2024-01-03 08:19:27.607 4
99 2024-01-03 08:20:00 70.29 22943 2024-01-03 08:19:53.329 70.41 37204 2024-01-03 08:19:53.329 5

100 rows × 8 columns