Composites#

This section contains 6 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