Composites#

This section contains 11 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__'

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 Odd Lot NBBO Retrieval#

Retrieving the US NBBO including Odd Lots.
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 Odd Lot Quote Retrieval#

Retrieving the US Exchange Quotes including Odd Lots.
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

Joining NBBOs with and without Odd Lots#

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

Joining NBBOs with and without Odd Lots and Calculating Skews and Liquidity#

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

TWAP Bars for Spreads, Skews and Added Liquidity Comparing NBBOs with and without Odd Lots#

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