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