Joins#

This section contains examples for Joins 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__'

Calculates Period Statistics and Join them to Trading Activity#

Period statistics calculate the Volume Weighted Average Price (VWAP), High, Low and total Volume across the whole period as a single tick (bucket_interval=0), which is then joined to every trade with otp.join(on='all').

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE', 'EXCHANGE', 'COND']]

# Compute Aggregates across the whole Period as a single tick.
stats_src = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
stats = stats_src.agg({
    'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
    'FIRST_PRICE': otp.agg.first('PRICE'),
    'HIGH_PRICE': otp.agg.max('PRICE'),
    'LOW_PRICE': otp.agg.min('PRICE'),
    'LAST_PRICE': otp.agg.last('PRICE'),
    'VOLUME': otp.agg.sum('SIZE'),
})

# Join every trade to the single period-statistics record.
data = otp.join(trd, stats, on='all')

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 PRICE SIZE EXCHANGE COND VWAP_PRICE FIRST_PRICE HIGH_PRICE LOW_PRICE LAST_PRICE VOLUME
0 2024-01-03 09:30:00.065443591 50.02 2 Z @ I 50.402342 50.02 50.805 49.94 50.52 15008890
1 2024-01-03 09:30:00.111130049 50.16 3 Z @ I 50.402342 50.02 50.805 49.94 50.52 15008890
2 2024-01-03 09:30:00.127459523 50.17 100 V @ 50.402342 50.02 50.805 49.94 50.52 15008890
3 2024-01-03 09:30:00.128498068 50.17 5 Z @F I 50.402342 50.02 50.805 49.94 50.52 15008890
4 2024-01-03 09:30:00.135190071 50.13 46 Q @FTI 50.402342 50.02 50.805 49.94 50.52 15008890
... ... ... ... ... ... ... ... ... ... ... ...
995 2024-01-03 09:30:07.378682043 50.12 2 Q @ I 50.402342 50.02 50.805 49.94 50.52 15008890
996 2024-01-03 09:30:07.378683771 50.12 50 Q @ I 50.402342 50.02 50.805 49.94 50.52 15008890
997 2024-01-03 09:30:07.379088757 50.12 100 Z @F 50.402342 50.02 50.805 49.94 50.52 15008890
998 2024-01-03 09:30:07.390014965 50.12 100 P @ 50.402342 50.02 50.805 49.94 50.52 15008890
999 2024-01-03 09:30:07.396341167 50.09 1 D @4 I 50.402342 50.02 50.805 49.94 50.52 15008890

1000 rows × 11 columns

Join Trades to Multiple Tables#

Returns trade price/size with prevailing bid/ask prices from both the NBBO and NBBO_COMP tables.
Joins three tables together: TRD, NBBO and NBBO_COMP.
NBBO provides the standard NBBO; NBBO_COMP provides an improved NBBO that includes the Best Odd Lot order (BOLO).
Each side’s fields are renamed before the join, since NBBO and NBBO_COMP share column names.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]

nbbo = otp.DataSource(db='US_COMP', tick_type='NBBO')
nbbo['NBBO_TIME'] = nbbo['TIMESTAMP']
nbbo = nbbo[['NBBO_TIME', 'BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]

nbbo_comp = otp.DataSource(db='US_COMP', tick_type='NBBO_COMP')
nbbo_comp = nbbo_comp.add_suffix('_COMP')
nbbo_comp['NBBO_COMP_TIME'] = nbbo_comp['TIMESTAMP']

data = otp.join_by_time([trd, nbbo, nbbo_comp])
data = data.limit(10)

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 PRICE SIZE NBBO_TIME BID_PRICE ASK_PRICE BID_SIZE ASK_SIZE ASK_EXCHANGE_COMP ASK_PRICE_COMP ASK_SIZE_COMP ASK_SIZE_TOTAL_COMP BID_EXCHANGE_COMP BID_PRICE_COMP BID_SIZE_COMP BID_SIZE_TOTAL_COMP IS_PRE_OPEN_COMP OMDSEQ_COMP NBBO_COMP_TIME
0 2026-07-23 09:30:00.015928092 111.76 45 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 P 111.60 2 2 0 3 2026-07-23 09:30:00.005781958
1 2026-07-23 09:30:00.025618222 111.97 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 K 111.60 10 12 0 2 2026-07-23 09:30:00.021822151
2 2026-07-23 09:30:00.033583537 111.96 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 Z 111.60 93 105 0 2 2026-07-23 09:30:00.026266020
3 2026-07-23 09:30:00.033671013 111.96 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 Z 111.60 93 105 0 2 2026-07-23 09:30:00.026266020
4 2026-07-23 09:30:00.054061753 111.96 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 Z 111.63 49 60 0 5 2026-07-23 09:30:00.043221664
5 2026-07-23 09:30:00.055756073 111.96 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 Z 111.63 49 60 0 5 2026-07-23 09:30:00.043221664
6 2026-07-23 09:30:00.055851011 111.96 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 Z 111.63 49 60 0 5 2026-07-23 09:30:00.043221664
7 2026-07-23 09:30:00.163603286 111.76 1 2026-07-23 09:30:00.005781958 111.55 111.97 100 1000 Q 111.97 1051 1051 Q 111.81 20 20 0 5 2026-07-23 09:30:00.140853617
8 2026-07-23 09:30:00.190544330 111.97 2 2026-07-23 09:30:00.189544648 111.55 111.97 100 1000 Q 111.97 1049 1049 Q 111.81 20 20 0 2 2026-07-23 09:30:00.190543288
9 2026-07-23 09:30:00.199389623 111.97 7 2026-07-23 09:30:00.189544648 111.55 111.97 100 1000 Q 111.97 1042 1042 Q 111.81 20 20 0 2 2026-07-23 09:30:00.199388052

Join Trades to Prevailing Quotes#

Returns trade price/size with prevailing bid/ask prices at time of trade.
Joins trades and quotes by symbol using otp.join_by_time.
As the US Composite includes trades and quotes across multiple exchanges, the NBBO tick type is used.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]

nbbo = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE']]

data = otp.join_by_time([trd, nbbo])
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 1, 4),
                 timezone='UTC',
                 symbols='CSCO')
result
Time PRICE SIZE BID_PRICE ASK_PRICE
0 2024-01-03 00:00:40.413537416 50.56 17 50.51 50.65
1 2024-01-03 00:00:40.413540299 50.57 33 50.51 50.65
2 2024-01-03 00:03:34.026455739 50.52 26 50.51 50.65
3 2024-01-03 00:03:34.026458843 50.51 123 50.47 50.49
4 2024-01-03 00:03:34.026459421 50.49 3 50.47 50.49
... ... ... ... ... ...
95 2024-01-03 10:15:34.061235730 50.15 8 50.01 50.22
96 2024-01-03 10:15:34.061560226 50.20 10 50.01 50.22
97 2024-01-03 10:15:34.061575831 50.20 13 50.01 50.22
98 2024-01-03 10:17:30.746036211 50.19 10 50.01 50.22
99 2024-01-03 10:17:30.746039819 50.21 12 50.01 50.22

100 rows × 5 columns

Join Trades to Prevailing Exchange Quotes#

Returns trade price/size with prevailing bid/ask prices at time of trade, matched to the quote from the same exchange as the trade.
The on parameter of otp.join_by_time adds this extra match key alongside the time-based “as of” join, so the trade is joined to the prevailing quote for its own exchange.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE', 'EXCHANGE']]

qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte[['EXCHANGE', 'BID_PRICE', 'ASK_PRICE']]

# Match on EXCHANGE in addition to the time-based "as of" join.
data = otp.join_by_time([trd, qte], on=['EXCHANGE'])
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 4, 16, 0),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time PRICE SIZE EXCHANGE BID_PRICE ASK_PRICE
0 2024-01-03 09:30:00.065443591 50.0200 2 Z 50.00 50.17
1 2024-01-03 09:30:00.111130049 50.1600 3 Z 50.00 50.17
2 2024-01-03 09:30:00.127459523 50.1700 100 V NaN NaN
3 2024-01-03 09:30:00.128498068 50.1700 5 Z 50.00 50.17
4 2024-01-03 09:30:00.135190071 50.1300 46 Q 50.00 50.20
... ... ... ... ... ... ...
95 2024-01-03 09:30:00.897416820 50.0800 51 Q 50.05 50.10
96 2024-01-03 09:30:00.897444128 50.0800 29 Q 50.05 50.10
97 2024-01-03 09:30:00.897474806 50.0800 12 Q 50.05 50.10
98 2024-01-03 09:30:00.897476583 50.0800 2 Q 50.05 50.10
99 2024-01-03 09:30:00.897670983 50.0797 100 D NaN NaN

100 rows × 6 columns

Join Trades to Static to Calc Futures Notional Value#

Notional Traded Value is calculated from both the Trade and Static (reference) record.
The static record for the futures contract is fetched once (back_to_first_tick plus last()), then joined to every trade with otp.join(on='all'), since the static side is a single constant tick.

import onetick.py as otp

trd = otp.DataSource(db='CME_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]

stat = otp.DataSource(db='CME_SAMPLE', tick_type='STAT', back_to_first_tick=86400)
stat = stat[['TICK_VALUE', 'TICK_SIZE', 'NAME']]
# Ensure exactly one static record, the prevailing one for the contract.
stat = stat.last()

# Join every trade to the single static record.
data = otp.join(trd, stat, on='all')
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE'] * data['TICK_VALUE'] / data['TICK_SIZE'].astype(float)
data = data[['PRICE', 'SIZE', 'TICK_VALUE', 'TICK_SIZE', 'TRADED_VALUE', 'NAME']]

data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 7, 17),
                 end=otp.dt(2024, 1, 8, 17),
                 timezone='America/New_York',
                 symbols=r'ES\H24')
result
Time PRICE SIZE TICK_VALUE TICK_SIZE TRADED_VALUE NAME
0 2024-01-07 18:00:00.000000000 4735.75 145 12.5 0.25 34334187.5 E-mini S&P 500 Mar24
1 2024-01-07 18:00:00.035634379 4736.00 1 12.5 0.25 236800.0 E-mini S&P 500 Mar24
2 2024-01-07 18:00:00.041642883 4736.00 1 12.5 0.25 236800.0 E-mini S&P 500 Mar24
3 2024-01-07 18:00:00.044776817 4735.50 1 12.5 0.25 236775.0 E-mini S&P 500 Mar24
4 2024-01-07 18:00:00.046079459 4735.50 3 12.5 0.25 710325.0 E-mini S&P 500 Mar24
... ... ... ... ... ... ... ...
995 2024-01-07 18:02:05.246920871 4736.00 1 12.5 0.25 236800.0 E-mini S&P 500 Mar24
996 2024-01-07 18:02:05.246920871 4736.25 4 12.5 0.25 947250.0 E-mini S&P 500 Mar24
997 2024-01-07 18:02:05.246920871 4736.25 2 12.5 0.25 473625.0 E-mini S&P 500 Mar24
998 2024-01-07 18:02:07.807154961 4736.00 2 12.5 0.25 473600.0 E-mini S&P 500 Mar24
999 2024-01-07 18:02:07.807154961 4736.00 1 12.5 0.25 236800.0 E-mini S&P 500 Mar24

1000 rows × 7 columns

Join Trades to Sub Query of Prevailing Mid#

Returns trade price/size with prevailing mid-price at time of trade.
Joins trades and quotes with valid size using otp.join_by_time.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd.where(trd['SIZE'] > 0)
trd = trd[['PRICE', 'SIZE']]

qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte.where((qte['BID_SIZE'] > 0) & (qte['ASK_SIZE'] > 0))
qte['MID'] = (qte['ASK_PRICE'] + qte['BID_PRICE']) / 2
qte = qte[['MID']]

data = otp.join_by_time([trd, qte])
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 PRICE SIZE MID
0 2024-01-03 04:00:00.019128245 50.46 1 NaN
1 2024-01-03 04:00:00.905488831 50.05 5 50.210
2 2024-01-03 04:00:01.278537460 50.29 13 50.210
3 2024-01-03 04:00:01.278746794 50.29 13 50.210
4 2024-01-03 04:00:03.090763650 50.29 1 50.210
... ... ... ... ...
995 2024-01-03 09:27:26.473753349 50.00 20 50.125
996 2024-01-03 09:27:26.474854815 50.00 1 50.145
997 2024-01-03 09:27:26.476174053 50.00 1 50.145
998 2024-01-03 09:27:26.477614441 50.00 1 50.145
999 2024-01-03 09:27:26.479953219 50.00 2 50.145

1000 rows × 4 columns

Join Trades to US Composite Day Volume#

Returns individual trade price/size with the daily total volume (all exchanges composite).
The day’s volume is fetched once (back_to_first_tick plus last(), filtered to the composite EXCHANGE), then joined to every trade with otp.join(on='all'), since the daily side is a single constant tick for the query day.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]

day = otp.DataSource(db='US_COMP_SAMPLE', tick_type='DAY', back_to_first_tick=86400)
day = day.where(day['EXCHANGE'] == '')
day['VOL_TIME'] = day['TIMESTAMP']
day = day[['VOLUME', 'VOL_TIME']]
day = day.last()

# Join every trade to the single daily volume record.
data = otp.join(trd, day, on='all')
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 PRICE SIZE VOLUME VOL_TIME
0 2024-01-03 04:00:00.019128245 50.46 1 20303875 2024-01-03 20:15:00
1 2024-01-03 04:00:00.905488831 50.05 5 20303875 2024-01-03 20:15:00
2 2024-01-03 04:00:01.278537460 50.29 13 20303875 2024-01-03 20:15:00
3 2024-01-03 04:00:01.278746794 50.29 13 20303875 2024-01-03 20:15:00
4 2024-01-03 04:00:03.090763650 50.29 1 20303875 2024-01-03 20:15:00
... ... ... ... ... ...
995 2024-01-03 09:27:26.473753349 50.00 20 20303875 2024-01-03 20:15:00
996 2024-01-03 09:27:26.474854815 50.00 1 20303875 2024-01-03 20:15:00
997 2024-01-03 09:27:26.476174053 50.00 1 20303875 2024-01-03 20:15:00
998 2024-01-03 09:27:26.477614441 50.00 1 20303875 2024-01-03 20:15:00
999 2024-01-03 09:27:26.479953219 50.00 2 20303875 2024-01-03 20:15:00

1000 rows × 5 columns