Composites#
This section contains examples for Composites 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__'
Daily Composite Bars#
Returns pre-calculated Daily OHLCV (Open, High, Low, Close, Volume) data
for a specified symbol and date range for a Composite.
Multiple rows are returned per day: one per EXCHANGE/VENUE_ID
(US_COMP uses EXCHANGE, other composites use VENUE_ID),
plus the primary venue as 'PRIM' and the composite itself as an empty string.
US Composite Trade Daily Bars Including Volume Split by Trade Type.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
| Time | EXCHANGE | OPEN | HIGH | LOW | CLOSE | VOLUME | VWAP | PRICE_OPENING_AUCTION | VOLUME_OPENING_AUCTION | PRICE_CLOSING_AUCTION | VOLUME_CLOSING_AUCTION | VOLUME_MAIN_SESSION | VOLUME_PRE_MARKET | VOLUME_POST_MARKET | VOLUME_ODD_LOT | VOLUME_ROUND_LOT | VOLUME_OFF_EXCHANGE | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 20:15:00 | A | 50.170 | 50.67 | 50.005 | 50.545 | 19865 | 50.466779 | NaN | 0 | NaN | 0 | 19865 | 0 | 0 | 1635 | 18230 | 0 | 29978 |
| 1 | 2024-01-03 20:15:00 | B | 49.990 | 50.67 | 49.990 | 50.560 | 33528 | 50.353333 | NaN | 0 | NaN | 0 | 33528 | 0 | 0 | 7004 | 26524 | 0 | 29979 |
| 2 | 2024-01-03 20:15:00 | C | 50.005 | 50.68 | 50.005 | 50.540 | 114102 | 50.480954 | NaN | 0 | NaN | 0 | 114102 | 0 | 0 | 26914 | 87188 | 0 | 29980 |
| 3 | 2024-01-03 20:15:00 | D | 50.002 | 50.68 | 49.941 | 50.521 | 5885087 | 50.395912 | NaN | 0 | NaN | 0 | 4387424 | 19482 | 1478181 | 450582 | 5434505 | 5885087 | 29981 |
| 4 | 2024-01-03 20:15:00 | H | 50.120 | 50.67 | 49.960 | 50.550 | 237095 | 50.407378 | NaN | 0 | NaN | 0 | 237095 | 0 | 0 | 44582 | 192513 | 0 | 29982 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 13 | 2024-01-03 20:15:00 | V | 50.170 | 50.68 | 49.990 | 50.530 | 401516 | 50.408718 | NaN | 0 | NaN | 0 | 401036 | 480 | 0 | 54896 | 346620 | 0 | 29991 |
| 14 | 2024-01-03 20:15:00 | X | 50.060 | 50.67 | 50.005 | 50.560 | 45859 | 50.463649 | NaN | 0 | NaN | 0 | 45859 | 0 | 0 | 5796 | 40063 | 0 | 29992 |
| 15 | 2024-01-03 20:15:00 | Y | 50.140 | 50.68 | 50.030 | 50.540 | 155311 | 50.438789 | NaN | 0 | NaN | 0 | 154918 | 152 | 241 | 23842 | 131469 | 0 | 29993 |
| 16 | 2024-01-03 20:15:00 | Z | 50.090 | 50.68 | 49.960 | 50.530 | 983836 | 50.410505 | NaN | 0 | NaN | 0 | 981479 | 1959 | 398 | 133016 | 850820 | 0 | 29994 |
| 17 | 2024-01-03 20:15:00 | 50.170 | 50.68 | 49.941 | 50.510 | 20303875 | 50.434310 | NaN | 262623 | NaN | 3994768 | 14483100 | 67390 | 1495994 | 1569477 | 18734398 | 5885087 | 29995 |
18 rows × 19 columns
Total Volume Per Venue Across Symbols#
Calculating the Volume Traded across all symbols for each venue within the Composite. e.g. US_COMP.
The DAY summary table includes the daily Volume rollup,
making it unnecessary to calculate across the Trade table TRD.
The venue is stored in the field EXCHANGE for US_COMP, and QUOTE_VENUE for other composites.
The composite for all venues is typically added as empty quotes e.g. ''.
The primary venue is identified as PRIM.
import onetick.py as otp
# Retrieve DAY Records from US_COMP_DAILY
data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
# Limit Fields
data = data[['EXCHANGE', 'VOLUME']]
# Merge across all symbols
data = otp.merge([data], symbols=otp.Symbols(db='US_COMP_SAMPLE_DAILY'), identify_input_ts=True)
# Aggregate: count number of quotes per venue
data = data.agg({'VOLUME': otp.agg.sum('VOLUME')}, group_by=['EXCHANGE'])
# Filter out the Composite
data = data.where(data['EXCHANGE'] != '')
# Filter out the Primary
data = data.where(data['EXCHANGE'] != 'PRIM')
# Run query across the time range.
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York')
result
| Time | EXCHANGE | VOLUME | |
|---|---|---|---|
| 0 | 2024-01-04 | A | 33890564 |
| 1 | 2024-01-04 | B | 45310224 |
| 2 | 2024-01-04 | C | 47420646 |
| 3 | 2024-01-04 | D | 5101343473 |
| 4 | 2024-01-04 | H | 220129350 |
| ... | ... | ... | ... |
| 14 | 2024-01-04 | U | 366155659 |
| 15 | 2024-01-04 | V | 234670455 |
| 16 | 2024-01-04 | X | 27713620 |
| 17 | 2024-01-04 | Y | 121288137 |
| 18 | 2024-01-04 | Z | 578921698 |
19 rows × 3 columns
Trade Count Per Venue#
Calculating the trade count for each venue within the Composite. e.g. CA_COMP.
The quotes are stored in the TRD table.
The venue is stored in the field TRADE_VENUE, except for US_COMP which uses EXCHANGE.
import onetick.py as otp
# Retrieve Trades from CA_COMP
data = otp.DataSource(db='CA_COMP_SAMPLE', tick_type='TRD')
# Aggregate: count number of trades per venue
data = data.agg({'TRADE_COUNT': otp.agg.count()}, group_by=['TRADE_VENUE'])
# Define Output fields
data = data[['TRADE_VENUE', 'TRADE_COUNT']]
# Run query for selected symbol (for TD) and time range.
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/Toronto',
symbols='TD')
result
| Time | TRADE_VENUE | TRADE_COUNT | |
|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | CHIC | 8746 |
| 1 | 2024-01-03 16:00:00 | CSE2 | 18 |
| 2 | 2024-01-03 16:00:00 | LYNX | 93 |
| 3 | 2024-01-03 16:00:00 | MATN | 2879 |
| 4 | 2024-01-03 16:00:00 | NEOD | 18 |
| ... | ... | ... | ... |
| 10 | 2024-01-03 16:00:00 | XATX | 14 |
| 11 | 2024-01-03 16:00:00 | XCX2 | 1296 |
| 12 | 2024-01-03 16:00:00 | XCXD | 1749 |
| 13 | 2024-01-03 16:00:00 | XICX | 13 |
| 14 | 2024-01-03 16:00:00 | XTSE | 21023 |
15 rows × 3 columns
Quote Count Per Venue#
Calculating the quote count for each venue within the Composite. e.g. CA_COMP.
The quotes are stored in the QTE table.
The venue is stored in the field QUOTE_VENUE, except for US_COMP which uses EXCHANGE.
import onetick.py as otp
# Retrieve Quotes from CA_COMP
data = otp.DataSource(db='CA_COMP_SAMPLE', tick_type='QTE')
# Aggregate: count number of quotes per venue
data = data.agg({'QUOTE_COUNT': otp.agg.count()}, group_by=['QUOTE_VENUE'])
# Define Output fields
data = data[['QUOTE_VENUE', 'QUOTE_COUNT']]
# Run query for selected symbol (for TD) and time range.
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/Toronto',
symbols='TD')
result
| Time | QUOTE_VENUE | QUOTE_COUNT | |
|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | CHIC | 350854 |
| 1 | 2024-01-03 16:00:00 | CSE2 | 49538 |
| 2 | 2024-01-03 16:00:00 | LYNX | 59278 |
| 3 | 2024-01-03 16:00:00 | NEOE | 202624 |
| 4 | 2024-01-03 16:00:00 | NEON | 288203 |
| 5 | 2024-01-03 16:00:00 | OMGA | 168343 |
| 6 | 2024-01-03 16:00:00 | PURE | 81993 |
| 7 | 2024-01-03 16:00:00 | XATS | 144204 |
| 8 | 2024-01-03 16:00:00 | XATX | 856 |
| 9 | 2024-01-03 16:00:00 | XCX2 | 246433 |
| 10 | 2024-01-03 16:00:00 | XTSE | 317337 |
EBBO Custom Calculation#
Calculate a Custom EBBO / NBBO based on Selected Venues using the European Composite Dataset EU_COMP.
The European Composite includes quotes from all European Venues, plus a consolidated NBBO.
A custom NBBO can be calculated by filtering consolidated quotes on venue using the QUOTE_VENUE field
and currency using the CURRENCY field.
The virtual_ob() method is used in association with the
ob_snapshot_wide() method to construct the new consolidated book.
Using parameter max_levels=1 to limit the resulting book to Top of Book (ToB) to produce the NBBO.
import onetick.py as otp
# Retrieve Quotes from EU_COMP
data = otp.DataSource(db='EU_COMP_SAMPLE', tick_type='QTE')
# Filter on Continuous Trading Periods
data = data.where(data['OMD_STATUS'] == 'T')
# Filter on Required Currency
data = data.where(data['CURRENCY'] == 'GBX')
# Filter on Selected Venues
data = data.where(data['QUOTE_VENUE'].isin('AQXE', 'BATE', 'CHIX', 'EQTC', 'TRQX', 'XLON'))
# Create a order book data format based on the quotes from each venue
data = data.virtual_ob(['QUOTE_VENUE'])
# Rebuild the NBBO from the order book data
data = data.ob_snapshot_wide(running=True, max_levels=1)
# Add Spread
data['SPREAD'] = data['ASK_PRICE'] - data['BID_PRICE']
# Filter out events that occur at the same timestamp, keeping the last event per timestamp
data['NEXT_TS'] = data['TIMESTAMP'][+1]
data = data.where(data['NEXT_TS'] > data['TIMESTAMP'])
# Define Output fields
data = data[['BID_PRICE', 'BID_SIZE', 'ASK_PRICE', 'ASK_SIZE', 'SPREAD']]
# Return first 100 Rows
data = data.limit(100)
# Run query for selected ISIN (for Vodafone) and time range.
result = otp.run(data,
start=otp.dt(2024, 1, 3, 8, 0),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='Europe/London',
symbols='GB00BH4HKS39')
result
| Time | BID_PRICE | BID_SIZE | ASK_PRICE | ASK_SIZE | SPREAD | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 08:00:00.072000 | 69.34 | 21964 | 70.27 | 757 | 0.93 |
| 1 | 2024-01-03 08:00:00.076000 | 69.34 | 44497 | 70.27 | 757 | 0.93 |
| 2 | 2024-01-03 08:00:01.024000 | 69.34 | 44300 | 70.27 | 757 | 0.93 |
| 3 | 2024-01-03 08:00:01.122000 | 69.34 | 43160 | 70.68 | 49218 | 1.34 |
| 4 | 2024-01-03 08:00:01.163000 | 69.34 | 43160 | 70.68 | 50617 | 1.34 |
| ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-01-03 08:00:07.420198 | 70.06 | 3152 | 70.18 | 10252 | 0.12 |
| 96 | 2024-01-03 08:00:07.421000 | 70.07 | 2260 | 70.17 | 3461 | 0.10 |
| 97 | 2024-01-03 08:00:07.424000 | 70.08 | 100 | 70.17 | 3461 | 0.09 |
| 98 | 2024-01-03 08:00:07.427000 | 70.08 | 100 | 70.17 | 409 | 0.09 |
| 99 | 2024-01-03 08:00:07.435000 | 70.08 | 100 | 70.18 | 2300 | 0.10 |
100 rows × 6 columns
CBBO Custom Calculation#
Calculate a Custom CBBO / NBBO based on Selected Venues using the Canadian Composite Dataset CA_COMP.
The Canadian Composite includes quotes from all Canadian Venues, plus a consolidated NBBO.
A custom NBBO can be calculated by filtering on venue using the QUOTE_VENUE field.
The virtual_ob() method is used in association with the
ob_snapshot_wide() method to construct the new consolidated book.
Using parameter max_levels=1 to limit the resulting book to Top of Book (ToB) to produce the NBBO.
import onetick.py as otp
# Retrieve Quotes from CA_COMP
data = otp.DataSource(db='CA_COMP_SAMPLE', tick_type='QTE')
# Filter on Continuous Trading Periods
data = data.where(data['OMD_STATUS'] == 'T')
# Filter on Selected Venues
data = data.where(
data['QUOTE_VENUE'].isin('CHIC', 'CSE2', 'LYNX', 'NEOE', 'NEON', 'OMGA', 'PURE', 'XATS', 'XATX', 'XCX2', 'XTSE')
)
# Create a order book data format based on the quotes from each venue
data = data.virtual_ob(['QUOTE_VENUE'])
# Rebuild the NBBO from the order book data
data = data.ob_snapshot_wide(running=True, max_levels=1)
# Add Spread
data['SPREAD'] = data['ASK_PRICE'] - data['BID_PRICE']
# Filter out events that occur at the same timestamp, keeping the last event per timestamp
data['NEXT_TS'] = data['TIMESTAMP'][+1]
data = data.where(data['NEXT_TS'] > data['TIMESTAMP'])
# Define Output fields
data = data[['BID_PRICE', 'BID_SIZE', 'ASK_PRICE', 'ASK_SIZE', 'SPREAD']]
# Return first 100 Rows
data = data.limit(100)
# Run query for selected ISIN (for Vodafone) and time range.
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/Toronto',
symbols='TD')
result
| Time | BID_PRICE | BID_SIZE | ASK_PRICE | ASK_SIZE | SPREAD | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.063 | 85.19 | 100 | 86.08 | 1100 | 0.89 |
| 1 | 2024-01-03 09:30:00.067 | 85.24 | 800 | 86.08 | 1100 | 0.84 |
| 2 | 2024-01-03 09:30:00.070 | 85.22 | 400 | 86.08 | 1100 | 0.86 |
| 3 | 2024-01-03 09:30:00.072 | 84.28 | 100 | 86.08 | 1100 | 1.80 |
| 4 | 2024-01-03 09:30:00.073 | 84.28 | 100 | 86.08 | 1100 | 1.80 |
| ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-01-03 09:30:01.954 | 85.25 | 2700 | 85.31 | 1400 | 0.06 |
| 96 | 2024-01-03 09:30:01.963 | 85.25 | 2700 | 85.31 | 1300 | 0.06 |
| 97 | 2024-01-03 09:30:01.985 | 85.25 | 2700 | 85.31 | 1000 | 0.06 |
| 98 | 2024-01-03 09:30:01.986 | 85.25 | 3000 | 85.31 | 1000 | 0.06 |
| 99 | 2024-01-03 09:30:01.988 | 85.25 | 3600 | 85.31 | 1000 | 0.06 |
100 rows × 6 columns
Trade Volume to NBBO Calculation#
Calculate trade volume relative to NBBO levels for each trade.
Joins trades (TRD) with prevailing NBBO quotes and classifies each trade by NBBO level.
Classifies each trade volume by NBBO level (AT_MID, INSIDE_NBBO, AT_NBBO, OUTSIDE_NBBO).
Aggregates total volume by exchange and symbol for each NBBO classification.
Calculate trade volume relative to NBBO levels for each trade.
Step 1: For each trade, retrieve the prevailing NBBO at that moment
Step 2: Classify each trade volume by NBBO level (
AT_MID,INSIDE_NBBO,AT_NBBO,OUTSIDE_NBBO)Step 3: Aggregate total volume by exchange for each NBBO classification
import onetick.py as otp
# Retrieve trade ticks with PRICE and SIZE fields
trades = otp.DataSource(db='US_COMP', tick_type='TRD')
trades = trades[['PRICE', 'SIZE', 'EXCHANGE']]
# Retrieve prevailing NBBO quotes with BID_PRICE and ASK_PRICE fields
nbbo = otp.DataSource(db='US_COMP', tick_type='NBBO', back_to_first_tick=86400)
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE']]
# Join trades with prevailing NBBO at trade time
joined = otp.join_by_time([trades, nbbo])
# Calculate mid price
joined['MID_PRICE'] = (joined['BID_PRICE'] + joined['ASK_PRICE']) / 2
# Classify trade volume by NBBO level using conditional logic (boolean * size = size or 0)
joined['VOLUME_AT_MID'] = (joined['PRICE'] == joined['MID_PRICE']) * joined['SIZE']
joined['VOLUME_INSIDE_NBBO'] = (
(joined['PRICE'] > joined['BID_PRICE']) & (joined['PRICE'] < joined['ASK_PRICE'])
) * joined['SIZE']
joined['VOLUME_AT_NBBO'] = (
(joined['PRICE'] == joined['BID_PRICE']) | (joined['PRICE'] == joined['ASK_PRICE'])
) * joined['SIZE']
joined['VOLUME_OUTSIDE_NBBO'] = (
(joined['PRICE'] < joined['BID_PRICE']) | (joined['PRICE'] > joined['ASK_PRICE'])
) * joined['SIZE']
# Aggregate by exchange and symbol
data = joined.agg({
'VOLUME_AT_MID': otp.agg.sum('VOLUME_AT_MID'),
'VOLUME_INSIDE_NBBO': otp.agg.sum('VOLUME_INSIDE_NBBO'),
'VOLUME_AT_NBBO': otp.agg.sum('VOLUME_AT_NBBO'),
'VOLUME_OUTSIDE_NBBO': otp.agg.sum('VOLUME_OUTSIDE_NBBO'),
'TRADE_COUNT': otp.agg.count()
}, group_by=['EXCHANGE'])
result = otp.run(
data,
symbols=['CSCO'],
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York'
)
result
| Time | EXCHANGE | VOLUME_AT_MID | VOLUME_INSIDE_NBBO | VOLUME_AT_NBBO | VOLUME_OUTSIDE_NBBO | TRADE_COUNT | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-04 | A | 7541 | 5108 | 14257 | 504 | 236 |
| 1 | 2024-01-04 | B | 2629 | 2569 | 30459 | 500 | 485 |
| 2 | 2024-01-04 | C | 7102 | 5942 | 107844 | 597 | 1832 |
| 3 | 2024-01-04 | D | 1551492 | 3702729 | 1936908 | 245550 | 37598 |
| 4 | 2024-01-04 | H | 71108 | 6834 | 228942 | 1319 | 2537 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 11 | 2024-01-04 | U | 143049 | 36803 | 484555 | 7797 | 5444 |
| 12 | 2024-01-04 | V | 162738 | 139259 | 259411 | 2846 | 4408 |
| 13 | 2024-01-04 | X | 7165 | 4350 | 39389 | 2120 | 604 |
| 14 | 2024-01-04 | Y | 12848 | 8303 | 144428 | 2580 | 1519 |
| 15 | 2024-01-04 | Z | 286876 | 110179 | 859658 | 13999 | 11603 |
16 rows × 7 columns
US NBBO with Odd Lot Retrieval#
The US Composite provides data from the US Consolidated Tape, known as the SIP.
Historically Quotes and NBBO have been provided based on round lots, and Odd lot quotes have not been distributed.
Regulation has since been updated so that Odd lot quotes are additionally distributed,
adding the Best Odd Lot Order (BOLO).
OneTick combines the BOLO with the NBBO to create NBBO_COMP, which is available from June 2026 onwards.
import onetick.py as otp
data = otp.DataSource(db='US_COMP', tick_type='NBBO_COMP')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2026, 7, 23, 9, 30),
end=otp.dt(2026, 7, 23, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | BID_PRICE | BID_SIZE | BID_SIZE_TOTAL | BID_EXCHANGE | ASK_PRICE | ASK_SIZE | ASK_SIZE_TOTAL | ASK_EXCHANGE | IS_PRE_OPEN | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-07-23 09:30:00.005781958 | 111.60 | 2 | 2 | P | 111.97 | 1051 | 1051 | Q | 0 | 3 |
| 1 | 2026-07-23 09:30:00.021822151 | 111.60 | 10 | 12 | K | 111.97 | 1051 | 1051 | Q | 0 | 2 |
| 2 | 2026-07-23 09:30:00.026266020 | 111.60 | 93 | 105 | Z | 111.97 | 1051 | 1051 | Q | 0 | 2 |
| 3 | 2026-07-23 09:30:00.034203418 | 111.61 | 20 | 20 | Q | 111.97 | 1051 | 1051 | Q | 0 | 2 |
| 4 | 2026-07-23 09:30:00.039083007 | 111.63 | 1 | 1 | K | 111.97 | 1051 | 1051 | Q | 0 | 2 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-07-23 09:30:05.063335703 | 112.18 | 1 | 2 | K | 112.18 | 3947 | 3947 | Q | 0 | 27 |
| 996 | 2026-07-23 09:30:05.063342433 | 112.18 | 1 | 2 | K | 112.18 | 100 | 100 | Q | 0 | 31 |
| 997 | 2026-07-23 09:30:05.063349178 | 112.18 | 1 | 2 | K | 112.28 | 20 | 20 | P | 0 | 37 |
| 998 | 2026-07-23 09:30:05.063371778 | 112.18 | 1 | 2 | K | 112.20 | 8 | 8 | Q | 0 | 47 |
| 999 | 2026-07-23 09:30:05.063379664 | 112.18 | 1 | 2 | K | 112.20 | 375 | 375 | Q | 0 | 53 |
1000 rows × 11 columns
US Quotes with Odd Lot Retrieval#
The US Composite provides data from the US Consolidated Tape, known as the SIP.
Historically Quotes and NBBO have been provided based on round lots, and Odd lot quotes have not been distributed.
Regulation has since been updated so that Odd lot quotes are additionally distributed,
adding the Best Odd Lot Order (BOLO).
OneTick combines the BOLO with the exchange quotes to create QTE_COMP, which is available from June 2026 onwards.
import onetick.py as otp
data = otp.DataSource(db='US_COMP', tick_type='QTE_COMP')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2026, 7, 23, 9, 30),
end=otp.dt(2026, 7, 23, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | EXCH_TIME | SEQ_NUM | EXCHANGE | SOURCE | BID_PRICE | BID_SIZE | ASK_PRICE | ASK_SIZE | QUOTE_COND | RPI | RESTRICTION_IND | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-07-23 09:30:00.005781958 | 2026-07-23 09:30:00.000189608 | 5529880 | V | N | 105.97 | 100 | 117.57 | 100 | R | 1 | ||
| 1 | 2026-07-23 09:30:00.021822151 | 2026-07-23 09:30:00.021641304 | 5531561 | K | N | 111.60 | 10 | 112.07 | 100 | R | 1 | ||
| 2 | 2026-07-23 09:30:00.026266020 | 2026-07-23 09:30:00.026085204 | 5531984 | Z | N | 111.60 | 93 | 115.73 | 100 | R | 1 | ||
| 3 | 2026-07-23 09:30:00.027008979 | 2026-07-23 09:30:00.026811665 | 5532082 | K | N | 111.60 | 10 | 112.07 | 100 | R | 1 | ||
| 4 | 2026-07-23 09:30:00.033916296 | 2026-07-23 09:30:00.033740082 | 5532589 | Z | N | 111.60 | 93 | 115.73 | 100 | R | 1 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-07-23 09:30:02.918799529 | 2026-07-23 09:30:02.918625018 | 5663344 | J | N | 110.91 | 100 | 113.12 | 100 | R | 1 | ||
| 996 | 2026-07-23 09:30:02.918802170 | 2026-07-23 09:30:02.918628021 | 5663345 | Y | N | 107.03 | 100 | 116.17 | 100 | R | 3 | ||
| 997 | 2026-07-23 09:30:02.920359376 | 2026-07-23 09:30:02.920017641 | 5663360 | N | N | 110.53 | 100 | 117.56 | 200 | R | 1 | ||
| 998 | 2026-07-23 09:30:02.920741782 | 2026-07-23 09:30:02.920567335 | 5663365 | Z | N | 111.98 | 200 | 112.46 | 148 | R | 3 | ||
| 999 | 2026-07-23 09:30:02.920749521 | 1969-12-31 19:00:00.000000000 | 5663366 | K | N | 112.18 | 10 | 112.50 | 10 | R | 4 |
1000 rows × 13 columns
Retrieving the US Exchange NBBO with and without Odd Lots (BOLO)#
The NBBO with Odd Lots (NBBO_COMP) is joined to the NBBO without Odd Lots.
This returns the two sets of quotes: the wider spread without odd lots and the narrower spread with odd lots.
import onetick.py as otp
# NBBO without Odd Lots
nbbo = otp.DataSource(db='US_COMP', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE_TOTAL', 'ASK_SIZE_TOTAL']]
# NBBO with Odd Lots (BOLO) - renamed with a _COMP suffix
comp = otp.DataSource(db='US_COMP', tick_type='NBBO_COMP')
comp = comp[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE_TOTAL', 'ASK_SIZE_TOTAL']]
comp = comp.rename(columns={'BID_PRICE': 'BID_PRICE_COMP',
'ASK_PRICE': 'ASK_PRICE_COMP',
'BID_SIZE_TOTAL': 'BID_SIZE_TOTAL_COMP',
'ASK_SIZE_TOTAL': 'ASK_SIZE_TOTAL_COMP'})
# As-of join: keep every NBBO tick, attaching the prevailing NBBO_COMP quote
data = otp.join_by_time([nbbo, comp])
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2026, 7, 23, 9, 30),
end=otp.dt(2026, 7, 23, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | BID_PRICE | ASK_PRICE | BID_SIZE_TOTAL | ASK_SIZE_TOTAL | BID_PRICE_COMP | ASK_PRICE_COMP | BID_SIZE_TOTAL_COMP | ASK_SIZE_TOTAL_COMP | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-07-23 09:30:00.005781958 | 111.55 | 111.97 | 100 | 1000 | NaN | NaN | 0 | 0 |
| 1 | 2026-07-23 09:30:00.171091468 | 111.55 | 111.97 | 200 | 1000 | 111.81 | 111.97 | 20 | 1051 |
| 2 | 2026-07-23 09:30:00.189536667 | 111.55 | 111.97 | 300 | 1000 | 111.81 | 111.97 | 20 | 1051 |
| 3 | 2026-07-23 09:30:00.189544648 | 111.55 | 111.97 | 200 | 1000 | 111.81 | 111.97 | 20 | 1051 |
| 4 | 2026-07-23 09:30:00.348511535 | 111.55 | 112.00 | 200 | 100 | 111.81 | 111.97 | 20 | 1038 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-07-23 09:30:17.288391537 | 112.33 | 112.44 | 100 | 100 | 112.34 | 112.36 | 5 | 20 |
| 996 | 2026-07-23 09:30:17.288406083 | 112.33 | 112.41 | 100 | 100 | 112.34 | 112.36 | 5 | 20 |
| 997 | 2026-07-23 09:30:17.288432221 | 112.33 | 112.40 | 100 | 100 | 112.33 | 112.36 | 100 | 20 |
| 998 | 2026-07-23 09:30:17.288450719 | 112.34 | 112.40 | 100 | 100 | 112.33 | 112.36 | 100 | 20 |
| 999 | 2026-07-23 09:30:17.288464242 | 112.34 | 112.40 | 200 | 100 | 112.34 | 112.36 | 100 | 20 |
1000 rows × 9 columns
Retrieving the US Exchange NBBO with and without Odd Lots calculating Skews and Liquidity#
The NBBO with Odd Lots (NBBO_COMP) is joined to the NBBO without Odd Lots, returning the two sets of
quotes: the wider spread without odd lots and the narrower spread with odd lots.
These are compared to produce the Bid and Ask Skews (BID_SKEW, ASK_SKEW) and Percentage Skew
(PCNT_BID_SKEW, PCNT_ASK_SKEW).
Additional liquidity visible in the odd lot quote is also calculated by comparing the Bid and Ask Sizes
between the two sets of quotes.
This is not the full additional liquidity, as there may be price levels between the two sets of quotes.
import onetick.py as otp
# NBBO without Odd Lots
nbbo = otp.DataSource(db='US_COMP', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE_TOTAL', 'ASK_SIZE_TOTAL']]
# NBBO with Odd Lots (BOLO) - renamed with a _COMP suffix
comp = otp.DataSource(db='US_COMP', tick_type='NBBO_COMP')
comp = comp[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE_TOTAL', 'ASK_SIZE_TOTAL']]
comp = comp.rename(columns={'BID_PRICE': 'BID_PRICE_COMP',
'ASK_PRICE': 'ASK_PRICE_COMP',
'BID_SIZE_TOTAL': 'BID_SIZE_TOTAL_COMP',
'ASK_SIZE_TOTAL': 'ASK_SIZE_TOTAL_COMP'})
# As-of join: keep every NBBO tick, attaching the prevailing NBBO_COMP quote
data = otp.join_by_time([nbbo, comp])
# Skews - odd lot book is narrower, so compare against the round lot NBBO
data['BID_SKEW'] = data['BID_PRICE_COMP'] - data['BID_PRICE']
data['ASK_SKEW'] = data['ASK_PRICE'] - data['ASK_PRICE_COMP']
data['PCNT_BID_SKEW'] = 100 * (data['BID_PRICE_COMP'] - data['BID_PRICE']) / data['BID_PRICE']
data['PCNT_ASK_SKEW'] = 100 * (data['ASK_PRICE'] - data['ASK_PRICE_COMP']) / data['ASK_PRICE']
# Additional visible liquidity from the odd lot quote.
# If the price levels match, the addition is the size difference, otherwise it is the full odd lot size.
data['BID_SIZE_ADDITION'] = data.apply(
lambda r: r['BID_SIZE_TOTAL_COMP'] - r['BID_SIZE_TOTAL']
if r['BID_PRICE_COMP'] == r['BID_PRICE']
else r['BID_SIZE_TOTAL_COMP']
)
data['ASK_SIZE_ADDITION'] = data.apply(
lambda r: r['ASK_SIZE_TOTAL_COMP'] - r['ASK_SIZE_TOTAL']
if r['ASK_PRICE_COMP'] == r['ASK_PRICE']
else r['ASK_SIZE_TOTAL_COMP']
)
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2026, 7, 23, 9, 30),
end=otp.dt(2026, 7, 23, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | BID_PRICE | ASK_PRICE | BID_SIZE_TOTAL | ASK_SIZE_TOTAL | BID_PRICE_COMP | ASK_PRICE_COMP | BID_SIZE_TOTAL_COMP | ASK_SIZE_TOTAL_COMP | BID_SKEW | ASK_SKEW | PCNT_BID_SKEW | PCNT_ASK_SKEW | BID_SIZE_ADDITION | ASK_SIZE_ADDITION | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2026-07-23 09:30:00.005781958 | 111.55 | 111.97 | 100 | 1000 | NaN | NaN | 0 | 0 | NaN | NaN | NaN | NaN | 0 | 0 |
| 1 | 2026-07-23 09:30:00.171091468 | 111.55 | 111.97 | 200 | 1000 | 111.81 | 111.97 | 20 | 1051 | 0.26 | 0.00 | 0.233079 | 0.000000 | 20 | 51 |
| 2 | 2026-07-23 09:30:00.189536667 | 111.55 | 111.97 | 300 | 1000 | 111.81 | 111.97 | 20 | 1051 | 0.26 | 0.00 | 0.233079 | 0.000000 | 20 | 51 |
| 3 | 2026-07-23 09:30:00.189544648 | 111.55 | 111.97 | 200 | 1000 | 111.81 | 111.97 | 20 | 1051 | 0.26 | 0.00 | 0.233079 | 0.000000 | 20 | 51 |
| 4 | 2026-07-23 09:30:00.348511535 | 111.55 | 112.00 | 200 | 100 | 111.81 | 111.97 | 20 | 1038 | 0.26 | 0.03 | 0.233079 | 0.026786 | 20 | 1038 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2026-07-23 09:30:17.288391537 | 112.33 | 112.44 | 100 | 100 | 112.34 | 112.36 | 5 | 20 | 0.01 | 0.08 | 0.008902 | 0.071149 | 5 | 20 |
| 996 | 2026-07-23 09:30:17.288406083 | 112.33 | 112.41 | 100 | 100 | 112.34 | 112.36 | 5 | 20 | 0.01 | 0.05 | 0.008902 | 0.044480 | 5 | 20 |
| 997 | 2026-07-23 09:30:17.288432221 | 112.33 | 112.40 | 100 | 100 | 112.33 | 112.36 | 100 | 20 | 0.00 | 0.04 | 0.000000 | 0.035587 | 0 | 20 |
| 998 | 2026-07-23 09:30:17.288450719 | 112.34 | 112.40 | 100 | 100 | 112.33 | 112.36 | 100 | 20 | -0.01 | 0.04 | -0.008902 | 0.035587 | 100 | 20 |
| 999 | 2026-07-23 09:30:17.288464242 | 112.34 | 112.40 | 200 | 100 | 112.34 | 112.36 | 100 | 20 | 0.00 | 0.04 | 0.000000 | 0.035587 | -100 | 20 |
1000 rows × 15 columns
Calculating 1 Minute TWAP Bars for Spreads Skews and added liquidity comparing NBBOs with and without Odd Lots#
The NBBO with Odd Lots (NBBO_COMP) is joined to the NBBO without Odd Lots.
Spreads, Skews and added visible Liquidity are calculated.
These are then calculated as 1 minute Time weighted averages using tw_average() across the period.
import onetick.py as otp
# NBBO without Odd Lots
nbbo = otp.DataSource(db='US_COMP', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE_TOTAL', 'ASK_SIZE_TOTAL']]
# NBBO with Odd Lots (BOLO) - renamed with a _COMP suffix
comp = otp.DataSource(db='US_COMP', tick_type='NBBO_COMP')
comp = comp[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE_TOTAL', 'ASK_SIZE_TOTAL']]
comp = comp.rename(columns={'BID_PRICE': 'BID_PRICE_COMP',
'ASK_PRICE': 'ASK_PRICE_COMP',
'BID_SIZE_TOTAL': 'BID_SIZE_TOTAL_COMP',
'ASK_SIZE_TOTAL': 'ASK_SIZE_TOTAL_COMP'})
# As-of join: keep every NBBO tick, attaching the prevailing NBBO_COMP quote
data = otp.join_by_time([nbbo, comp])
# Spreads with and without odd lots
data['SPREAD'] = data['ASK_PRICE'] - data['BID_PRICE']
data['SPREAD_COMP'] = data['ASK_PRICE_COMP'] - data['BID_PRICE_COMP']
# Percentage skews
data['PCNT_BID_SKEW'] = 100 * (data['BID_PRICE_COMP'] - data['BID_PRICE']) / data['BID_PRICE']
data['PCNT_ASK_SKEW'] = 100 * (data['ASK_PRICE'] - data['ASK_PRICE_COMP']) / data['ASK_PRICE']
# Additional visible liquidity from the odd lot quote
data['BID_SIZE_ADDITION'] = data.apply(
lambda r: r['BID_SIZE_TOTAL_COMP'] - r['BID_SIZE_TOTAL']
if r['BID_PRICE_COMP'] == r['BID_PRICE']
else r['BID_SIZE_TOTAL_COMP']
)
data['ASK_SIZE_ADDITION'] = data.apply(
lambda r: r['ASK_SIZE_TOTAL_COMP'] - r['ASK_SIZE_TOTAL']
if r['ASK_PRICE_COMP'] == r['ASK_PRICE']
else r['ASK_SIZE_TOTAL_COMP']
)
# 1 minute time weighted averages
data = data.agg({'TWA_SPREAD': otp.agg.tw_average('SPREAD'),
'TWA_SPREAD_COMP': otp.agg.tw_average('SPREAD_COMP'),
'TWA_PCNT_BID_SKEW': otp.agg.tw_average('PCNT_BID_SKEW'),
'TWA_PCNT_ASK_SKEW': otp.agg.tw_average('PCNT_ASK_SKEW'),
'TWA_BID_SIZE_ADDITION': otp.agg.tw_average('BID_SIZE_ADDITION'),
'TWA_ASK_SIZE_ADDITION': otp.agg.tw_average('ASK_SIZE_ADDITION')},
bucket_interval=otp.Minute(1))
result = otp.run(data,
start=otp.dt(2026, 7, 23, 9, 30),
end=otp.dt(2026, 7, 23, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | TWA_SPREAD | TWA_SPREAD_COMP | TWA_PCNT_BID_SKEW | TWA_PCNT_ASK_SKEW | TWA_BID_SIZE_ADDITION | TWA_ASK_SIZE_ADDITION | |
|---|---|---|---|---|---|---|---|
| 0 | 2026-07-23 09:31:00 | 0.215450 | 0.140410 | 0.024416 | 0.041910 | 53.203005 | 19.718561 |
| 1 | 2026-07-23 09:32:00 | 0.123505 | 0.080868 | 0.015353 | 0.022436 | 28.342016 | 13.206969 |
| 2 | 2026-07-23 09:33:00 | 0.095515 | 0.060749 | 0.005024 | 0.025806 | 44.442403 | 25.211660 |
| 3 | 2026-07-23 09:34:00 | 0.103634 | 0.085108 | 0.006949 | 0.009550 | 18.700700 | 25.212788 |
| 4 | 2026-07-23 09:35:00 | 0.092061 | 0.079801 | 0.004404 | 0.006504 | -0.621712 | 70.537566 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 385 | 2026-07-23 15:56:00 | 0.019129 | 0.015048 | 0.001639 | 0.001984 | 92.985752 | 71.156811 |
| 386 | 2026-07-23 15:57:00 | 0.013511 | 0.011700 | 0.000818 | 0.000788 | 102.146870 | 96.556970 |
| 387 | 2026-07-23 15:58:00 | 0.015162 | 0.012494 | 0.001250 | 0.001115 | 118.091640 | 93.751892 |
| 388 | 2026-07-23 15:59:00 | 0.014644 | 0.012188 | 0.000438 | 0.001737 | 103.466443 | 62.913740 |
| 389 | 2026-07-23 16:00:00 | 0.012814 | 0.011879 | 0.000243 | 0.000586 | 57.165562 | 53.505681 |
390 rows × 7 columns
European Composite Bar Creation#
Returns OHLC prices for the European Composite by Venue over the query interval.
The query groups by TRADE_VENUE,
and since the European Composite includes trades in multiple currencies, also by CURRENCY.
Bars for Vodafone by Venue on EU_COMP.
import onetick.py as otp
data = otp.DataSource(db='EU_COMP_SAMPLE', tick_type='TRD')
data = data[['TRADE_VENUE', 'CURRENCY', 'PRICE', 'SIZE']]
# Notional value of each trade, summed per venue/currency below.
data['TRADE_VALUE'] = data['PRICE'] * data['SIZE']
data = data.agg({
'FIRST_PRICE': otp.agg.first('PRICE'),
'HIGH_PRICE': otp.agg.max('PRICE'),
'LOW_PRICE': otp.agg.min('PRICE'),
'LAST_PRICE': otp.agg.last('PRICE'),
'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_VALUE': otp.agg.sum('TRADE_VALUE'),
'TRADE_COUNT': otp.agg.count(),
}, group_by=['TRADE_VENUE', 'CURRENCY'])
# EU_COMP Symbols are defined as ISINs
result = otp.run(data,
start=otp.dt(2024, 1, 3, 10, 23, 1),
end=otp.dt(2024, 1, 3, 10, 27, 48),
timezone='Europe/London',
symbols='GB00BH4HKS39')
result
| Time | TRADE_VENUE | CURRENCY | FIRST_PRICE | HIGH_PRICE | LOW_PRICE | LAST_PRICE | VWAP_PRICE | VOLUME | TRADE_VALUE | TRADE_COUNT | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 10:27:48 | AQXE | GBX | 70.1600 | 70.1900 | 70.1600 | 70.1900 | 70.167688 | 7559 | 5.303976e+05 | 6 |
| 1 | 2024-01-03 10:27:48 | BATE | GBX | 70.1800 | 70.1800 | 70.1500 | 70.1700 | 70.163523 | 11909 | 8.355774e+05 | 7 |
| 2 | 2024-01-03 10:27:48 | BATF | GBX | 70.1740 | 70.1740 | 70.1740 | 70.1740 | 70.174000 | 6868 | 4.819550e+05 | 1 |
| 3 | 2024-01-03 10:27:48 | CHIX | GBX | 70.1800 | 70.1800 | 70.1500 | 70.1700 | 70.165289 | 54523 | 3.825622e+06 | 30 |
| 4 | 2024-01-03 10:27:48 | SINT | GBX | 70.1800 | 70.1900 | 70.1400 | 70.1700 | 70.175339 | 25143 | 1.764419e+06 | 16 |
| 5 | 2024-01-03 10:27:48 | TRQX | GBX | 70.1600 | 70.1800 | 70.1500 | 70.1700 | 70.168596 | 21446 | 1.504836e+06 | 13 |
| 6 | 2024-01-03 10:27:48 | XETB | EUR | 0.8155 | 0.8165 | 0.8155 | 0.8165 | 0.816270 | 131285 | 1.071640e+05 | 5 |
| 7 | 2024-01-03 10:27:48 | XGAT | EUR | 0.8165 | 0.8165 | 0.8155 | 0.8155 | 0.815992 | 110290 | 8.999578e+04 | 5 |
| 8 | 2024-01-03 10:27:48 | XLON | GBX | 70.1750 | 70.2100 | 70.1500 | 70.1700 | 70.177304 | 152608 | 1.070962e+07 | 44 |
| 9 | 2024-01-03 10:27:48 | XOFF | GBX | 70.1773 | 70.1800 | 70.1773 | 70.1800 | 70.177307 | 821 | 5.761557e+04 | 2 |
European Composite Bar Creation with Fill Forward#
Returns OHLC prices for the European Composite by Venue with the last price filled forward into 5-minute
buckets.
The query groups on bucket_interval, TRADE_VENUE and CURRENCY.
The LAST aggregate is filled forward for buckets without trades by setting time_series_type='state_ts'.
Trades are limited to those during Continuous Trading (TRADE_PERIOD = '-')
and Lit Order Book Trades (BOOK_TYPE = '0').
5 Minute Bars for Vodafone by Venue on EU_COMP with the LAST_PRICE filled Forward.
import onetick.py as otp
data = otp.DataSource(db='EU_COMP', tick_type='TRD')
data = data[['TRADE_VENUE', 'CURRENCY', 'PRICE', 'SIZE', 'TRADE_PERIOD', 'BOOK_TYPE']]
# Regular Trades, Lit Order Book Trades
data = data.where((data['TRADE_PERIOD'] == '-') & (data['BOOK_TYPE'] == '0'))
data = data.agg({
'FIRST_PRICE': otp.agg.first('PRICE'),
'HIGH_PRICE': otp.agg.max('PRICE'),
'LOW_PRICE': otp.agg.min('PRICE'),
'LAST_PRICE': otp.agg.last('PRICE', time_series_type='state_ts'),
'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count(),
}, bucket_interval=300, group_by=['TRADE_VENUE', 'CURRENCY'])
result = otp.run(data,
start=otp.dt(2024, 1, 3, 8),
end=otp.dt(2024, 1, 3, 16),
timezone='Europe/London',
symbols='GB00BH4HKS39')
result
| Time | TRADE_VENUE | CURRENCY | FIRST_PRICE | HIGH_PRICE | LOW_PRICE | LAST_PRICE | VWAP_PRICE | VOLUME | TRADE_COUNT | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 08:05:00 | AQXE | GBX | 70.1900 | 70.5300 | 70.1900 | 70.5100 | 70.396213 | 8188 | 11 |
| 1 | 2024-01-03 08:05:00 | BATE | GBX | 70.2100 | 70.5200 | 70.2100 | 70.4700 | 70.376028 | 9259 | 5 |
| 2 | 2024-01-03 08:05:00 | CHIX | GBX | 70.2000 | 70.5700 | 70.2000 | 70.4800 | 70.475176 | 83915 | 35 |
| 3 | 2024-01-03 08:05:00 | TRQX | GBX | 70.2300 | 70.5300 | 70.2300 | 70.4600 | 70.488130 | 14436 | 17 |
| 4 | 2024-01-03 08:05:00 | XLON | GBX | 70.0100 | 70.5700 | 70.0100 | 70.4600 | 70.460361 | 322654 | 76 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 733 | 2024-01-03 16:00:00 | CHIX | GBX | 69.6800 | 69.7200 | 69.6200 | 69.6700 | 69.677900 | 173679 | 76 |
| 734 | 2024-01-03 16:00:00 | EQTC | GBX | 69.6200 | 69.6200 | 69.6200 | 69.6200 | 69.620000 | 750 | 1 |
| 735 | 2024-01-03 16:00:00 | TRQX | GBX | 69.6800 | 69.7000 | 69.6400 | 69.6700 | 69.678608 | 13997 | 23 |
| 736 | 2024-01-03 16:00:00 | XETB | EUR | 0.8125 | 0.8125 | 0.8125 | 0.8125 | 0.812500 | 6891 | 1 |
| 737 | 2024-01-03 16:00:00 | XLON | GBX | 69.6800 | 69.7300 | 69.6200 | 69.6600 | 69.669443 | 201306 | 73 |
738 rows × 10 columns
European Composite Trade Date#
European Composite Trade Records Consolidating Liquidity Across European Venues.
Symbols are defined as ISINs, since the Ticker Symbol is not consistent across European venues.
Trades are recorded with their TRADE_VENUE and CURRENCY,
since an instrument may trade in multiple currencies,
along with standardized TRADE_PERIOD and BOOK_TYPE fields,
and MiFID-related fields such as MMT_MKT_MECH and MMT_TRD_MODE.
Multiple symbols are requested at once by passing a list to the symbols parameter.
Retrieving Trades for All European Venues using EU_COMP Across 4 ISINs.
import onetick.py as otp
data = otp.DataSource(db='EU_COMP_SAMPLE', tick_type='TRD')
data = otp.merge([data],
symbols=['GB00BH4HKS39', 'FR001400J770', 'DE0005140008', 'GB00BP6MXD84'],
identify_input_ts=True)
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 8),
end=otp.dt(2024, 1, 3, 16),
timezone='Europe/London')
result
| Time | CLOUD_DB | CURRENCY | DB_SYMBOL | EXCH_TIME | ISIN | MMT_ALGO_IND | MMT_BENCHMARK_IND | MMT_CROSS_IND | MMT_MKT_MECH | ... | PRICE | PUB_VENUE | SIZE | TRADE_TYPE | TRADE_VENUE | AGGRESSOR_SIDE | TRADE_PERIOD | BOOK_TYPE | SYMBOL_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 08:00:03.033 | LSE | GBX | SHEL | 2024-01-03 08:00:03.021970 | GB00BP6MXD84 | - | - | - | 1 | ... | 2572.500 | 24394 | UT | XLON | O | 0 | GB00BP6MXD84 | TRD | ||
| 1 | 2024-01-03 08:00:03.055 | LSE | GBX | SHEL | 2024-01-03 08:00:03.054526 | GB00BP6MXD84 | H | - | - | 1 | ... | 2574.500 | 147 | AT | XLON | B | - | 0 | GB00BP6MXD84 | TRD | |
| 2 | 2024-01-03 08:00:03.056 | LSE | GBX | SHEL | 2024-01-03 08:00:03.055170 | GB00BP6MXD84 | H | - | - | 1 | ... | 2574.500 | 46 | AT | XLON | B | - | 0 | GB00BP6MXD84 | TRD | |
| 3 | 2024-01-03 08:00:03.056 | LSE | GBX | SHEL | 2024-01-03 08:00:03.055335 | GB00BP6MXD84 | H | - | - | 1 | ... | 2574.500 | 44 | AT | XLON | B | - | 0 | GB00BP6MXD84 | TRD | |
| 4 | 2024-01-03 08:00:03.060 | CBOE_APA | GBX | SHELl | 2024-01-03 08:00:03.059000 | GB00BP6MXD84 | H | - | - | 4 | ... | 2573.000 | BOTC | 10 | OB | SINT | - | 9 | GB00BP6MXD84 | TRD | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 08:00:53.554 | LSE | GBX | VOD | 2024-01-03 08:00:32.861000 | GB00BH4HKS39 | - | - | - | 4 | ... | 70.450 | ECHO | 1 | OB | SINT | - | 9 | GB00BH4HKS39 | TRD | |
| 996 | 2024-01-03 08:00:53.582 | LSE | GBX | VOD | 2024-01-03 08:00:32.861000 | GB00BH4HKS39 | - | - | - | 4 | ... | 70.450 | ECHO | 14 | OB | SINT | - | 9 | GB00BH4HKS39 | TRD | |
| 997 | 2024-01-03 08:00:53.585 | TRADEGATE | EUR | DBK | 2024-01-03 08:00:53.411000 | DE0005140008 | H | - | - | 8 | ... | 12.510 | 100 | X | XGAT | U | Q | DE0005140008 | TRD | ||
| 998 | 2024-01-03 08:00:53.616 | LSE | GBX | VOD | 2024-01-03 08:00:32.861000 | GB00BH4HKS39 | - | - | - | 4 | ... | 70.250 | ECEU | 147 | OB | SINT | - | 9 | GB00BH4HKS39 | TRD | |
| 999 | 2024-01-03 08:00:53.642 | TRADEGATE | EUR | DBK | 2024-01-03 08:00:53.535000 | DE0005140008 | H | - | - | 8 | ... | 12.512 | 500 | X | XGAT | U | Q | DE0005140008 | TRD |
1000 rows × 28 columns
European Composite Daily Bars#
For the European Composite, returns pre-calculated Daily OHLCV (Open, High, Low, Close, Volume) data
for a specified symbol and date range.
Multiple rows are returned per day: one per VENUE_ID and CURRENCY.
The composite itself is represented as VENUE_ID with an empty string,
and records aggregated across currency are represented as CURRENCY with an empty string
(returning nan values for Price related fields).
European Composite Trade Daily Bars for Vodafone Including Volume Split by Trade Type, and Divided by Venue and Currency.
import onetick.py as otp
data = otp.DataSource(db='EU_COMP', tick_type='DAY')
data = data.limit(1000)
# EU_COMP Symbols are defined as ISINs
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='Europe/London',
symbols='GB00BH4HKS39')
result
| Time | VENUE_ID | CURRENCY | OPEN | HIGH | LOW | CLOSE | VOLUME | TRADE_COUNT | TURNOVER | VWAP | VOLUME_LIT | VOLUME_DARK | OFF_BOOK_VOLUME | VOLUME_RFQ | VOLUME_CONTINUOUS | VOLUME_AUCTION | VOLUME_OPENING_AUCTION | VOLUME_CLOSING_AUCTION | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 21:45:00 | NaN | NaN | NaN | NaN | 162365315 | 17950 | NaN | NaN | 51937283 | 3024392 | 107297855 | 105785 | 30911592 | 20975691 | 193032 | 16901308 | 0 | ||
| 1 | 2024-01-03 21:45:00 | AQEU | NaN | NaN | NaN | NaN | 18245 | 4 | NaN | NaN | 18245 | 0 | 0 | 0 | 18245 | 0 | 0 | 0 | 0 | |
| 2 | 2024-01-03 21:45:00 | AQXA | NaN | NaN | NaN | NaN | 147440 | 31 | NaN | NaN | 147440 | 0 | 0 | 0 | 0 | 147440 | 0 | 0 | 1 | |
| 3 | 2024-01-03 21:45:00 | AQXD | NaN | NaN | NaN | NaN | 271699 | 114 | NaN | NaN | 0 | 271699 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | |
| 4 | 2024-01-03 21:45:00 | AQXE | NaN | NaN | NaN | NaN | 2868685 | 1028 | NaN | NaN | 2868685 | 0 | 0 | 0 | 1758646 | 1110039 | 0 | 1110039 | 3 | |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 50 | 2024-01-03 21:45:00 | XLON | GBX | 70.000 | 70.750 | 69.38 | 69.51 | 35729584 | 5655 | 2.496875e+09 | 69.836950 | 31819135 | 0 | 3910449 | 0 | 15796368 | 15972767 | 184613 | 15788154 | 22 |
| 51 | 2024-01-03 21:45:00 | XOFF | EUR | NaN | NaN | NaN | NaN | 908729 | 7 | 7.326590e+05 | NaN | 0 | 0 | 908729 | 0 | 0 | 0 | 0 | 0 | 23 |
| 52 | 2024-01-03 21:45:00 | XOFF | GBX | NaN | NaN | NaN | NaN | 82569503 | 659 | 5.782179e+09 | NaN | 0 | 0 | 82569503 | 0 | 0 | 0 | 0 | 0 | 24 |
| 53 | 2024-01-03 21:45:00 | XPAL | GBX | 70.255 | 70.255 | 69.89 | 69.89 | 10585 | 3 | 7.419720e+05 | 70.096552 | 10585 | 0 | 0 | 0 | 0 | 10585 | 0 | 0 | 25 |
| 54 | 2024-01-03 21:45:00 | XPOL | GBX | 70.465 | 70.585 | 69.42 | 69.49 | 385874 | 63 | 2.702356e+07 | 70.032091 | 0 | 385874 | 0 | 0 | 0 | 0 | 0 | 0 | 26 |
55 rows × 20 columns
Quote to NBBO#
Returns aggregated Durations of Exchange Quotes relative to the NBBO.
Metrics include Durations in Seconds and Time Weighted Average Bid and Ask Sizes.
Multiple rows are returned per day: one per EXCHANGE/VENUE_ID.
US Composite Quotes Relative to NBBO Daily Metrics.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_MKT_SHARE', tick_type='QTE_NBBO_DAY')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
Time |
NBBO_DURATION |
AT_NBBO_BOTH_DURATION |
AT_NBBO_BID_DURATION |
AT_NBBO_ASK_DURATION |
… |
AT_NBBO_BOTH_SIZE |
AT_NBBO_BID_SIZE |
AT_NBBO_ASK_SIZE |
AT_NBBO_EITHER_SIZE |
EXCHANGE |
|
|---|---|---|---|---|---|---|---|---|---|---|---|
0 |
2024-01-03 20:15:00 |
23399.999042 |
3461.328841 |
12254.868121 |
11851.047136 |
… |
2.195534 |
1.071412 |
1.096154 |
1.265252 |
A |
1 |
2024-01-03 20:15:00 |
23399.999042 |
0.000000 |
91.206346 |
1049.890999 |
… |
NaN |
1.441661 |
1.301335 |
1.312551 |
B |
2 |
2024-01-03 20:15:00 |
23399.999042 |
166.372875 |
4855.599746 |
3486.597275 |
… |
3.899924 |
1.352153 |
1.692614 |
1.524859 |
C |
3 |
2024-01-03 20:15:00 |
23399.999042 |
0.000000 |
0.000000 |
0.000000 |
… |
NaN |
NaN |
NaN |
NaN |
D |
4 |
2024-01-03 20:15:00 |
23399.999042 |
0.000000 |
0.000000 |
0.000000 |
… |
NaN |
NaN |
NaN |
NaN |
F |
.. |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
18 |
2024-01-03 20:15:00 |
23399.999042 |
14193.489903 |
19143.794961 |
17967.336442 |
… |
9.484606 |
5.674532 |
5.009383 |
8.667443 |
V |
19 |
2024-01-03 20:15:00 |
23399.999042 |
0.000000 |
0.000000 |
0.000000 |
… |
NaN |
NaN |
NaN |
NaN |
W |
20 |
2024-01-03 20:15:00 |
23399.999042 |
441.048628 |
3814.788175 |
4883.906510 |
… |
4.946489 |
3.624611 |
3.199336 |
3.566680 |
X |
21 |
2024-01-03 20:15:00 |
23399.999042 |
528.385826 |
9799.012940 |
7777.112852 |
… |
4.828393 |
3.575193 |
3.450817 |
3.629264 |
Y |
22 |
2024-01-03 20:15:00 |
23399.999042 |
21819.373044 |
22418.592538 |
22727.999920 |
… |
12.584441 |
6.201227 |
6.529836 |
12.321782 |
Z |
Trade to NBBO#
Returns aggregated Daily Volume, Value and Trade Counts for a specified symbol and date range for a Composite.
Metrics include groupings by Trade Type, Block Trades, and Trades relative to the NBBO.
Multiple rows are returned per day: one per EXCHANGE/VENUE_ID.
US Composite Trade Daily Metrics including Trades Relative to the NBBO and Block trades.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_MKT_SHARE', tick_type='TRD_NBBO_DAY')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
Time |
TRADE_VALUE |
TRADE_COUNT |
VOLUME |
TRADE_VALUE_MAIN_SESSION |
… |
TRADE_VALUE_POST_MARKET |
TRADE_VALUE_BLOCK |
BLOCK_VOLUME |
TRADE_COUNT_BLOCK |
EXCHANGE |
|
|---|---|---|---|---|---|---|---|---|---|---|---|
0 |
2024-01-03 20:15:00 |
1.024018e+09 |
130038.0 |
20303875.0 |
7.301464e+08 |
… |
389.0 |
3.755104e+08 |
7438923.0 |
430.0 |
|
1 |
2024-01-03 20:15:00 |
1.002469e+06 |
236.0 |
19865.0 |
1.002469e+06 |
… |
0.0 |
0.000000e+00 |
0.0 |
0.0 |
A |
2 |
2024-01-03 20:15:00 |
1.688385e+06 |
485.0 |
33528.0 |
1.688385e+06 |
… |
0.0 |
0.000000e+00 |
0.0 |
0.0 |
B |
3 |
2024-01-03 20:15:00 |
5.759688e+06 |
1832.0 |
114102.0 |
5.759688e+06 |
… |
0.0 |
0.000000e+00 |
0.0 |
0.0 |
C |
4 |
2024-01-03 20:15:00 |
2.967372e+08 |
37598.0 |
5885087.0 |
2.210986e+08 |
… |
165.0 |
1.200495e+08 |
2378854.0 |
268.0 |
D |
.. |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
12 |
2024-01-03 20:15:00 |
2.665790e+07 |
5444.0 |
529155.0 |
2.665744e+07 |
… |
1.0 |
2.228014e+06 |
44426.0 |
6.0 |
U |
13 |
2024-01-03 20:15:00 |
2.023853e+07 |
4408.0 |
401516.0 |
2.021446e+07 |
… |
0.0 |
0.000000e+00 |
0.0 |
0.0 |
V |
14 |
2024-01-03 20:15:00 |
2.314178e+06 |
604.0 |
45859.0 |
2.314178e+06 |
… |
0.0 |
0.000000e+00 |
0.0 |
0.0 |
X |
15 |
2024-01-03 20:15:00 |
7.833038e+06 |
1519.0 |
155311.0 |
7.813260e+06 |
… |
3.0 |
6.067000e+05 |
12000.0 |
6.0 |
Y |
16 |
2024-01-03 20:15:00 |
4.958872e+07 |
11603.0 |
983836.0 |
4.947041e+07 |
… |
9.0 |
5.727366e+05 |
11342.0 |
4.0 |
Z |
Calculate a combined Point in Time Trade and NBBO Snapshot Across a Composite#
A specific point in time is selected as the query interval.
All symbols are retrieved by merging across otp.Symbols.
A lookback of up to 1 day (86400s) via back_to_first_tick returns the prevailing trade and NBBO
before the selected time, per symbol.
As a Composite is being accessed, the NBBO tick type is queried rather than QTE.
Calculate Point in Time Trade & NBBO Snapshot for US_COMP.
import onetick.py as otp
snapshot_time = otp.dt(2024, 1, 3, 12, 30)
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD', back_to_first_tick=86400)
trd = trd[['PRICE', 'SIZE']]
trd = trd.last()
nbbo = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO', back_to_first_tick=86400)
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE']]
nbbo = nbbo.last()
# Combine the prevailing trade and prevailing NBBO for each symbol into one tick. on='all' cross-joins the
# (at most one) trade tick with the (at most one) NBBO tick; unlike on='same_size', it degrades gracefully to
# no output for a symbol that lacks a prevailing trade or NBBO within the lookback window, rather than erroring.
data = otp.join(trd, nbbo, on='all')
# Merge the combined snapshot for every symbol into a single stream.
# identify_input_ts adds the SYMBOL_NAME of each tick to the output.
merged = otp.merge([data], symbols=otp.Symbols('US_COMP_SAMPLE', for_tick_type='TRD'), identify_input_ts=True)
result = otp.run(merged,
start=snapshot_time,
end=snapshot_time,
timezone='America/New_York')
result
| Time | PRICE | SIZE | BID_PRICE | ASK_PRICE | SYMBOL_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 12:30:00 | 132.8100 | 20 | 132.79 | 132.85 | A | |
| 1 | 2024-01-03 12:30:00 | 13.0250 | 200 | 13.02 | 13.03 | AAL | |
| 2 | 2024-01-03 12:30:00 | 183.9276 | 1 | 183.92 | 183.93 | AAPL | |
| 3 | 2024-01-03 12:30:00 | 160.2700 | 11 | 160.26 | 160.29 | ABBV | |
| 4 | 2024-01-03 12:30:00 | 132.7700 | 97 | 132.75 | 132.79 | ABNB | |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 495 | 2024-01-03 12:30:00 | 112.2700 | 1 | 112.26 | 112.29 | XYL | |
| 496 | 2024-01-03 12:30:00 | 129.2700 | 1 | 129.26 | 129.32 | YUM | |
| 497 | 2024-01-03 12:30:00 | 120.9700 | 100 | 120.94 | 120.97 | ZBH | |
| 498 | 2024-01-03 12:30:00 | 255.1299 | 4 | 254.65 | 255.13 | ZBRA | |
| 499 | 2024-01-03 12:30:00 | 194.5000 | 21 | 194.46 | 194.59 | ZTS |
500 rows × 7 columns
Calculate Spread and Mid from Composite Quotes Per Venue#
Composite datasets include both quotes per venue/exchange and an NBBO.
The QTE tick type includes the top of book quotes associated with each venue.
US_COMP uses the EXCHANGE field; other composites use QUOTE_VENUE.
SPREAD = ASK_PRICE - BID_PRICE
MID_PRICE = (BID_PRICE + ASK_PRICE) / 2
Calculate Spread and Mid Price by Exchange for CSCO from the QTE table.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
data = data[['EXCHANGE', 'BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
data = data.where((data['BID_SIZE'] > 0) & (data['ASK_SIZE'] > 0))
data['MID_PRICE'] = (data['BID_PRICE'] + data['ASK_PRICE']) / 2
data['SPREAD'] = data['ASK_PRICE'] - data['BID_PRICE']
data = data[['EXCHANGE', 'BID_PRICE', 'ASK_PRICE', 'MID_PRICE', 'SPREAD']]
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | EXCHANGE | BID_PRICE | ASK_PRICE | MID_PRICE | SPREAD | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.001830617 | Z | 50.00 | 50.18 | 50.090 | 0.18 |
| 1 | 2024-01-03 09:30:00.002078349 | K | 49.80 | 50.37 | 50.085 | 0.57 |
| 2 | 2024-01-03 09:30:00.002215206 | Q | 50.00 | 50.20 | 50.100 | 0.20 |
| 3 | 2024-01-03 09:30:00.002341904 | K | 49.80 | 50.37 | 50.085 | 0.57 |
| 4 | 2024-01-03 09:30:00.002524728 | Z | 50.00 | 50.18 | 50.090 | 0.18 |
| ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:01.212565762 | U | 50.08 | 50.12 | 50.100 | 0.04 |
| 996 | 2024-01-03 09:30:01.212610812 | Q | 50.07 | 50.09 | 50.080 | 0.02 |
| 997 | 2024-01-03 09:30:01.212643714 | U | 50.08 | 50.11 | 50.095 | 0.03 |
| 998 | 2024-01-03 09:30:01.212674502 | Q | 50.07 | 50.09 | 50.080 | 0.02 |
| 999 | 2024-01-03 09:30:01.212742707 | K | 50.07 | 50.11 | 50.090 | 0.04 |
1000 rows × 6 columns
Calculate Spread and Mid Statistics from a Composite Venues#
SPREAD and MID_PRICE are computed per venue quote,
then aggregated across the period using the time weighted average, high and low, grouped by EXCHANGE.
Calculate Spread and Mid Price Aggregates by Exchange for CSCO from the QTE Table.
import onetick.py as otp
qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte[['EXCHANGE', 'BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
qte = qte.where((qte['BID_SIZE'] > 0) & (qte['ASK_SIZE'] > 0))
# Calculate the Mid Price and Spread
qte['MID_PRICE'] = (qte['BID_PRICE'] + qte['ASK_PRICE']) / 2
qte['SPREAD'] = qte['ASK_PRICE'] - qte['BID_PRICE']
data = qte.agg({
'TW_AVG_MID_PRICE': otp.agg.tw_average('MID_PRICE'),
'HIGH_MID_PRICE': otp.agg.max('MID_PRICE'),
'LOW_MID_PRICE': otp.agg.min('MID_PRICE'),
'TW_AVG_SPREAD': otp.agg.tw_average('SPREAD'),
'HIGH_SPREAD': otp.agg.max('SPREAD'),
'LOW_SPREAD': otp.agg.min('SPREAD'),
'QTE_COUNT': otp.agg.count(),
}, group_by=['EXCHANGE'])
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | EXCHANGE | TW_AVG_MID_PRICE | HIGH_MID_PRICE | LOW_MID_PRICE | TW_AVG_SPREAD | HIGH_SPREAD | LOW_SPREAD | QTE_COUNT | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | A | 50.365858 | 59.665 | 49.920 | 0.050821 | 18.20 | 0.01 | 31701 |
| 1 | 2024-01-03 16:00:00 | B | 50.427252 | 51.670 | 48.920 | 0.274450 | 5.04 | 0.02 | 104046 |
| 2 | 2024-01-03 16:00:00 | C | 50.399748 | 51.685 | 48.860 | 3.504164 | 5.04 | 0.01 | 16665 |
| 3 | 2024-01-03 16:00:00 | H | 50.363778 | 59.590 | 48.155 | 0.016979 | 18.14 | 0.01 | 58283 |
| 4 | 2024-01-03 16:00:00 | J | 50.368070 | 51.315 | 49.345 | 0.048714 | 3.28 | 0.01 | 58250 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 10 | 2024-01-03 16:00:00 | U | 50.365556 | 50.685 | 49.840 | 0.011539 | 0.68 | 0.01 | 319613 |
| 11 | 2024-01-03 16:00:00 | V | 50.367538 | 51.325 | 49.945 | 0.021154 | 2.00 | 0.01 | 267781 |
| 12 | 2024-01-03 16:00:00 | X | 50.368231 | 51.675 | 48.930 | 0.078841 | 5.04 | 0.01 | 100494 |
| 13 | 2024-01-03 16:00:00 | Y | 50.365750 | 51.440 | 48.990 | 0.092152 | 4.20 | 0.01 | 52184 |
| 14 | 2024-01-03 16:00:00 | Z | 50.365487 | 50.685 | 49.940 | 0.010744 | 0.18 | 0.01 | 189278 |
15 rows × 9 columns
Calculate Spread and Mid from a Composite NBBO#
Composite datasets include both quotes per venue/exchange and an NBBO.
The NBBO can either be regulatory (as in the US) or constructed.
SPREAD = ASK_PRICE - BID_PRICE
MID_PRICE = (BID_PRICE + ASK_PRICE) / 2
Calculate Spread and Mid Price for CSCO from the NBBO.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO')
data = data[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
data = data.where((data['BID_SIZE'] > 0) & (data['ASK_SIZE'] > 0))
data['MID_PRICE'] = (data['BID_PRICE'] + data['ASK_PRICE']) / 2
data['SPREAD'] = data['ASK_PRICE'] - data['BID_PRICE']
data = data[['BID_PRICE', 'ASK_PRICE', 'MID_PRICE', 'SPREAD']]
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | BID_PRICE | ASK_PRICE | MID_PRICE | SPREAD | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.001830617 | 50.00 | 50.18 | 50.090 | 0.18 |
| 1 | 2024-01-03 09:30:00.002215206 | 50.00 | 50.18 | 50.090 | 0.18 |
| 2 | 2024-01-03 09:30:00.002524728 | 50.00 | 50.18 | 50.090 | 0.18 |
| 3 | 2024-01-03 09:30:00.003295288 | 50.00 | 50.18 | 50.090 | 0.18 |
| 4 | 2024-01-03 09:30:00.010072539 | 50.00 | 50.18 | 50.090 | 0.18 |
| ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:03.909674185 | 50.18 | 50.20 | 50.190 | 0.02 |
| 996 | 2024-01-03 09:30:03.909758379 | 50.18 | 50.19 | 50.185 | 0.01 |
| 997 | 2024-01-03 09:30:03.909883162 | 50.18 | 50.20 | 50.190 | 0.02 |
| 998 | 2024-01-03 09:30:03.910223796 | 50.18 | 50.20 | 50.190 | 0.02 |
| 999 | 2024-01-03 09:30:03.910328511 | 50.18 | 50.20 | 50.190 | 0.02 |
1000 rows × 5 columns
Calculate Spread and Mid Statistics from a Composite NBBO#
SPREAD and MID_PRICE are computed per NBBO quote,
then aggregated across the period using the time weighted average, high and low.
Calculate Spread and Mid Price Aggregates for CSCO from the NBBO.
import onetick.py as otp
nbbo = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
nbbo = nbbo.where((nbbo['BID_SIZE'] > 0) & (nbbo['ASK_SIZE'] > 0))
# Calculate the Mid Price and Spread
nbbo['MID_PRICE'] = (nbbo['BID_PRICE'] + nbbo['ASK_PRICE']) / 2
nbbo['SPREAD'] = nbbo['ASK_PRICE'] - nbbo['BID_PRICE']
data = nbbo.agg({
'TW_AVG_MID_PRICE': otp.agg.tw_average('MID_PRICE'),
'HIGH_MID_PRICE': otp.agg.max('MID_PRICE'),
'LOW_MID_PRICE': otp.agg.min('MID_PRICE'),
'TW_AVG_SPREAD': otp.agg.tw_average('SPREAD'),
'HIGH_SPREAD': otp.agg.max('SPREAD'),
'LOW_SPREAD': otp.agg.min('SPREAD'),
'QTE_COUNT': otp.agg.count(),
})
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | TW_AVG_MID_PRICE | HIGH_MID_PRICE | LOW_MID_PRICE | TW_AVG_SPREAD | HIGH_SPREAD | LOW_SPREAD | QTE_COUNT | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 50.365553 | 50.685 | 49.945 | 0.010018 | 0.18 | -0.02 | 1813049 |
Calculate Rolling Spread and Mid Statistics from a Composite NBBO#
SPREAD and MID_PRICE are computed per NBBO quote.
Rolling statistics across the period calculate the time weighted average,
high and low at every point in time, using running=True with the default bucket_interval=0
(accumulating from the query start up to each quote).
Calculate Rolling Spread and Mid Price Aggregates for CSCO from the NBBO.
import onetick.py as otp
nbbo = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
nbbo = nbbo.where((nbbo['BID_SIZE'] > 0) & (nbbo['ASK_SIZE'] > 0))
# Calculate the Mid Price and Spread
nbbo['MID_PRICE'] = (nbbo['BID_PRICE'] + nbbo['ASK_PRICE']) / 2
nbbo['SPREAD'] = nbbo['ASK_PRICE'] - nbbo['BID_PRICE']
data = nbbo.agg({
'ROLLING_TW_AVG_MID_PRICE': otp.agg.tw_average('MID_PRICE'),
'ROLLING_HIGH_MID_PRICE': otp.agg.max('MID_PRICE'),
'ROLLING_LOW_MID_PRICE': otp.agg.min('MID_PRICE'),
'ROLLING_TW_AVG_SPREAD': otp.agg.tw_average('SPREAD'),
'ROLLING_HIGH_SPREAD': otp.agg.max('SPREAD'),
'ROLLING_LOW_SPREAD': otp.agg.min('SPREAD'),
}, running=True, all_fields=True)
data = data[['BID_PRICE', 'ASK_PRICE', 'MID_PRICE', 'SPREAD',
'ROLLING_TW_AVG_MID_PRICE', 'ROLLING_HIGH_MID_PRICE', 'ROLLING_LOW_MID_PRICE',
'ROLLING_TW_AVG_SPREAD', 'ROLLING_HIGH_SPREAD', 'ROLLING_LOW_SPREAD']]
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO')
result
| Time | BID_PRICE | ASK_PRICE | MID_PRICE | SPREAD | ROLLING_TW_AVG_MID_PRICE | ROLLING_HIGH_MID_PRICE | ROLLING_LOW_MID_PRICE | ROLLING_TW_AVG_SPREAD | ROLLING_HIGH_SPREAD | ROLLING_LOW_SPREAD | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.001830617 | 50.00 | 50.18 | 50.090 | 0.18 | 50.090000 | 50.09 | 50.090 | 0.180000 | 0.18 | 0.18 |
| 1 | 2024-01-03 09:30:00.002215206 | 50.00 | 50.18 | 50.090 | 0.18 | 50.090000 | 50.09 | 50.090 | 0.180000 | 0.18 | 0.18 |
| 2 | 2024-01-03 09:30:00.002524728 | 50.00 | 50.18 | 50.090 | 0.18 | 50.090000 | 50.09 | 50.090 | 0.180000 | 0.18 | 0.18 |
| 3 | 2024-01-03 09:30:00.003295288 | 50.00 | 50.18 | 50.090 | 0.18 | 50.090000 | 50.09 | 50.090 | 0.180000 | 0.18 | 0.18 |
| 4 | 2024-01-03 09:30:00.010072539 | 50.00 | 50.18 | 50.090 | 0.18 | 50.090000 | 50.09 | 50.090 | 0.180000 | 0.18 | 0.18 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:03.909674185 | 50.18 | 50.20 | 50.190 | 0.02 | 50.120071 | 50.19 | 50.055 | 0.037096 | 0.18 | 0.00 |
| 996 | 2024-01-03 09:30:03.909758379 | 50.18 | 50.19 | 50.185 | 0.01 | 50.120072 | 50.19 | 50.055 | 0.037095 | 0.18 | 0.00 |
| 997 | 2024-01-03 09:30:03.909883162 | 50.18 | 50.20 | 50.190 | 0.02 | 50.120074 | 50.19 | 50.055 | 0.037094 | 0.18 | 0.00 |
| 998 | 2024-01-03 09:30:03.910223796 | 50.18 | 50.20 | 50.190 | 0.02 | 50.120081 | 50.19 | 50.055 | 0.037093 | 0.18 | 0.00 |
| 999 | 2024-01-03 09:30:03.910328511 | 50.18 | 50.20 | 50.190 | 0.02 | 50.120082 | 50.19 | 50.055 | 0.037093 | 0.18 | 0.00 |
1000 rows × 11 columns