# 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. ```default # 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`. ```ipython3 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 ``` ```myst-ansi 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 x 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`. ```ipython3 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 ``` ```myst-ansi 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 x 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`. ```ipython3 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 ``` ```myst-ansi 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()`](https://docs.pip.distribution.sol.onetick.com/api/source/virtual_ob.html.md#onetick.py.Source.virtual_ob) method is used in association with the [`ob_snapshot_wide()`](https://docs.pip.distribution.sol.onetick.com/api/source/ob_snapshot_wide.html.md#onetick.py.Source.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. ```ipython3 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 ``` ```myst-ansi Time BID_PRICE BID_SIZE ASK_PRICE ASK_SIZE \ 0 2024-01-03 08:00:00.072000 69.34 21964 70.27 757 1 2024-01-03 08:00:00.076000 69.34 44497 70.27 757 2 2024-01-03 08:00:01.024000 69.34 44300 70.27 757 3 2024-01-03 08:00:01.122000 69.34 43160 70.68 49218 4 2024-01-03 08:00:01.163000 69.34 43160 70.68 50617 .. ... ... ... ... ... 95 2024-01-03 08:00:07.420198 70.06 3152 70.18 10252 96 2024-01-03 08:00:07.421000 70.07 2260 70.17 3461 97 2024-01-03 08:00:07.424000 70.08 100 70.17 3461 98 2024-01-03 08:00:07.427000 70.08 100 70.17 409 99 2024-01-03 08:00:07.435000 70.08 100 70.18 2300 SPREAD 0 0.93 1 0.93 2 0.93 3 1.34 4 1.34 .. ... 95 0.12 96 0.10 97 0.09 98 0.09 99 0.10 [100 rows x 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()`](https://docs.pip.distribution.sol.onetick.com/api/source/virtual_ob.html.md#onetick.py.Source.virtual_ob) method is used in association with the [`ob_snapshot_wide()`](https://docs.pip.distribution.sol.onetick.com/api/source/ob_snapshot_wide.html.md#onetick.py.Source.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. ```ipython3 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 ``` ```myst-ansi 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 x 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 ```ipython3 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 ``` ```myst-ansi Time EXCHANGE VOLUME_AT_MID VOLUME_INSIDE_NBBO VOLUME_AT_NBBO \ 0 2024-01-04 A 7541 5108 14257 1 2024-01-04 B 2629 2569 30459 2 2024-01-04 C 7102 5942 107844 3 2024-01-04 D 1551492 3702729 1936908 4 2024-01-04 H 71108 6834 228942 .. ... ... ... ... ... 11 2024-01-04 U 143049 36803 484555 12 2024-01-04 V 162738 139259 259411 13 2024-01-04 X 7165 4350 39389 14 2024-01-04 Y 12848 8303 144428 15 2024-01-04 Z 286876 110179 859658 VOLUME_OUTSIDE_NBBO TRADE_COUNT 0 504 236 1 500 485 2 597 1832 3 245550 37598 4 1319 2537 .. ... ... 11 7797 5444 12 2846 4408 13 2120 604 14 2580 1519 15 13999 11603 [16 rows x 7 columns] ```