# Markouts and Time Shifts 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__' ``` ## Prevailing quote at the time of a trade ```ipython3 import onetick.py as otp trd = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD') trd = trd[['PRICE', 'SIZE']] qte = otp.DataSource('US_COMP_SAMPLE', tick_type='NBBO', back_to_first_tick=otp.Minute(10), keep_first_tick_timestamp='NBBO_TIME') qte = qte[['ASK_PRICE', 'BID_PRICE', 'NBBO_TIME']] enriched_trades = otp.join_by_time([trd, qte]) result = otp.run(enriched_trades, symbols='AAPL', start=otp.dt(2024, 2, 1, 9, 30), end=otp.dt(2024, 2, 1, 9, 30, 1), timezone='America/New_York') result ``` ```myst-ansi Time PRICE SIZE ASK_PRICE BID_PRICE \ 0 2024-02-01 09:30:00.000961260 184.010 302 184.14 184.00 1 2024-02-01 09:30:00.000961491 184.000 100 184.14 184.00 2 2024-02-01 09:30:00.000961701 184.000 1 184.14 184.00 3 2024-02-01 09:30:00.000973163 184.000 1 184.14 183.90 4 2024-02-01 09:30:00.000973355 184.000 5 184.14 183.90 .. ... ... ... ... ... 574 2024-02-01 09:30:00.987184691 183.900 9 183.93 183.89 575 2024-02-01 09:30:00.990378350 183.920 1 183.93 183.89 576 2024-02-01 09:30:00.991941892 183.935 1 183.93 183.89 577 2024-02-01 09:30:00.993785116 183.905 300 183.93 183.89 578 2024-02-01 09:30:00.996512511 183.934 5 183.93 183.89 NBBO_TIME 0 2024-02-01 09:30:00.000860953 1 2024-02-01 09:30:00.000860953 2 2024-02-01 09:30:00.000860953 3 2024-02-01 09:30:00.000969529 4 2024-02-01 09:30:00.000969529 .. ... 574 2024-02-01 09:30:00.973387417 575 2024-02-01 09:30:00.987461418 576 2024-02-01 09:30:00.987461418 577 2024-02-01 09:30:00.987461418 578 2024-02-01 09:30:00.987461418 [579 rows x 6 columns] ``` ## Point-in-time benchmarks: BBO at different markouts Find the prevailing quote at different time intervals (markouts) before/after each trade in seconds.
\\\\ Also add the timestamp of joined quote. ```ipython3 markouts = [-1, 1] trd = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD') trd = trd[['PRICE', 'SIZE']] qte_by_markout = [] for m in markouts: mr = str(m).replace('-', 'B') if m < 0 else f'A{m}' qte = otp.DataSource('US_COMP_SAMPLE', tick_type='NBBO', back_to_first_tick=otp.Hour(24), keep_first_tick_timestamp='NBBO_TIME') qte = qte[['ASK_PRICE', 'BID_PRICE', 'NBBO_TIME']] qte = qte.add_suffix(f'_{mr}') # shift the data by m seconds qte = qte.time_interval_shift(m * 1000) qte_by_markout.append(qte) data = otp.join_by_time([trd] + qte_by_markout) result = otp.run( data, symbols='AAPL', start=otp.dt(2024, 2, 1, 9, 30), end=otp.dt(2024, 2, 1, 9, 30, 1), timezone='America/New_York', ) result ``` ```myst-ansi Time PRICE SIZE ASK_PRICE_B1 BID_PRICE_B1 \ 0 2024-02-01 09:30:00.000961260 184.010 302 184.29 184.14 1 2024-02-01 09:30:00.000961491 184.000 100 184.29 184.14 2 2024-02-01 09:30:00.000961701 184.000 1 184.29 184.14 3 2024-02-01 09:30:00.000973163 184.000 1 184.29 184.14 4 2024-02-01 09:30:00.000973355 184.000 5 184.29 184.14 .. ... ... ... ... ... 574 2024-02-01 09:30:00.987184691 183.900 9 184.14 184.00 575 2024-02-01 09:30:00.990378350 183.920 1 184.14 184.00 576 2024-02-01 09:30:00.991941892 183.935 1 184.14 184.00 577 2024-02-01 09:30:00.993785116 183.905 300 184.14 184.00 578 2024-02-01 09:30:00.996512511 183.934 5 184.14 184.00 NBBO_TIME_B1 ASK_PRICE_A1 BID_PRICE_A1 \ 0 2024-02-01 09:29:58.861113417 183.93 183.89 1 2024-02-01 09:29:58.861113417 183.93 183.89 2 2024-02-01 09:29:58.861113417 183.93 183.89 3 2024-02-01 09:29:58.861113417 183.93 183.89 4 2024-02-01 09:29:58.861113417 183.93 183.89 .. ... ... ... 574 2024-02-01 09:29:59.972347877 183.98 183.91 575 2024-02-01 09:29:59.972347877 183.98 183.91 576 2024-02-01 09:29:59.972347877 183.98 183.91 577 2024-02-01 09:29:59.972347877 183.98 183.91 578 2024-02-01 09:29:59.972347877 183.98 183.91 NBBO_TIME_A1 0 2024-02-01 09:30:00.987461418 1 2024-02-01 09:30:00.987461418 2 2024-02-01 09:30:00.987461418 3 2024-02-01 09:30:00.987461418 4 2024-02-01 09:30:00.987461418 .. ... 574 2024-02-01 09:30:01.955975928 575 2024-02-01 09:30:01.955975928 576 2024-02-01 09:30:01.955975928 577 2024-02-01 09:30:01.955975928 578 2024-02-01 09:30:01.955975928 [579 rows x 9 columns] ``` ## Time Shifts Query `VOD` (Vodafone) trade data with time-shifted versions.
\\\\ Returns original prices plus shifted prices (1s, 10s, 60s backward and forward). ```ipython3 import onetick.py as otp # Define the time range start = otp.dt(2024, 1, 3, 8) end = otp.dt(2024, 1, 4, 16) # Create base trades source trades = otp.DataSource( db='LSE_SAMPLE', tick_type='TRD', symbols='VOD' ) # Define offsets in milliseconds (backward and forward) offsets = { 'BACK_1S': -1000, 'BACK_10S': -10000, 'BACK_60S': -60000, 'FWD_1S': 1000, 'FWD_10S': 10000, 'FWD_60S': 60000 } # Generate shifted data sources using for loop shifted_sources = [trades] shifted_column_names = [] for suffix, shift_ms in offsets.items(): shifted = otp.DataSource( db='LSE_SAMPLE', tick_type='TRD', symbols='VOD' ).time_interval_shift(shift=shift_ms).add_suffix(f'_{suffix}') shifted_sources.append(shifted) shifted_column_names.append(f'PRICE_{suffix}') # Join all shifted prices by TIMESTAMP data = otp.join_by_time(shifted_sources) # Select and order columns columns = ['TIMESTAMP', 'PRICE', 'SIZE', 'TRADE_ID', 'TRADE_VENUE'] columns.extend(shifted_column_names) data = data[columns] # Return first 1000 Rows data = data.limit(1000) # Run the query result = otp.run(data, start=start, end=end, timezone='UTC') result ``` ```myst-ansi Time PRICE SIZE TRADE_ID TRADE_VENUE \ 0 2024-01-03 08:00:06.232 70.000 184613 911727684223506 XLON 1 2024-01-03 08:00:06.233 70.010 140 911727684223588 XLON 2 2024-01-03 08:00:06.287 70.010 500 911727684223589 XLON 3 2024-01-03 08:00:08.113 70.104 2800 25706436899852400 XLON 4 2024-01-03 08:00:09.380 70.137 49 892755055723892848 XLON .. ... ... ... ... ... 995 2024-01-03 08:49:23.760 70.650 711 911727684233082 XLON 996 2024-01-03 08:49:28.304 70.630 1 911727684233089 XLON 997 2024-01-03 08:49:28.304 70.630 969 911727684233090 XLON 998 2024-01-03 08:50:29.344 70.650 4716 911727684233162 XLON 999 2024-01-03 08:50:40.916 70.620 751 911727684233179 XLON PRICE_BACK_1S PRICE_BACK_10S PRICE_BACK_60S PRICE_FWD_1S \ 0 NaN NaN NaN 70.010 1 NaN NaN NaN 70.010 2 NaN NaN NaN 70.010 3 70.010 NaN NaN 70.104 4 70.104 NaN NaN 70.146 .. ... ... ... ... 995 70.650 70.65 70.6700 70.650 996 70.650 70.65 70.6549 70.630 997 70.650 70.65 70.6549 70.630 998 70.630 70.63 70.6300 70.650 999 70.650 70.65 70.6300 70.660 PRICE_FWD_10S PRICE_FWD_60S 0 70.1320 70.30 1 70.1320 70.30 2 70.1320 70.45 3 70.1320 70.08 4 70.1320 70.08 .. ... ... 995 70.6300 70.63 996 70.6300 70.63 997 70.6300 70.63 998 70.6500 70.63 999 70.6223 70.58 [1000 rows x 11 columns] ``` ## Time Shifts - Nested Query `VOD` (Vodafone) quote data with nested time-shifted mid-prices.
\\\\ Returns original mid-price plus shifted mid-prices (1s, 10s, 60s backward and forward). ```ipython3 import onetick.py as otp # Define the time range start = otp.dt(2024, 1, 3, 8) end = otp.dt(2024, 1, 4, 16) # Base quotes source quotes = otp.DataSource( db='LSE_SAMPLE', tick_type='QTE', symbols='VOD' ) quotes['MID_PRICE'] = (quotes['BID_PRICE'] + quotes['ASK_PRICE']) / 2 # Define offsets in milliseconds (backward and forward) offsets = { 'BACK_1S': -1000, 'BACK_10S': -10000, 'BACK_60S': -60000, 'FWD_1S': 1000, 'FWD_10S': 10000, 'FWD_60S': 60000 } # Generate independent time-shifted data sources shifted_sources = [quotes] for suffix, shift_ms in offsets.items(): shifted = otp.DataSource( db='LSE_SAMPLE', tick_type='QTE', symbols='VOD' ) shifted['MID_PRICE'] = (shifted['BID_PRICE'] + shifted['ASK_PRICE']) / 2 shifted = shifted.time_interval_shift(shift=shift_ms).add_suffix(f'_{suffix}') shifted_sources.append(shifted) # Join all shifted data by TIMESTAMP data = otp.join_by_time(shifted_sources) # Select final columns columns = ['TIMESTAMP', 'MID_PRICE'] columns.extend([f'MID_PRICE_{suffix}' for suffix in offsets.keys()]) data = data[columns] # Return first 1000 Rows data = data.limit(1000) # Run the query result = otp.run(data, start=start, end=end, timezone='UTC') result ``` ```myst-ansi Time MID_PRICE MID_PRICE_BACK_1S MID_PRICE_BACK_10S \ 0 2024-01-03 08:00:00.156 67.320 NaN NaN 1 2024-01-03 08:00:02.864 67.320 67.320 NaN 2 2024-01-03 08:00:02.865 66.215 67.320 NaN 3 2024-01-03 08:00:06.225 66.215 66.215 NaN 4 2024-01-03 08:00:06.225 66.215 66.215 NaN .. ... ... ... ... 995 2024-01-03 08:02:05.342 70.550 70.545 70.45 996 2024-01-03 08:02:05.342 70.550 70.545 70.45 997 2024-01-03 08:02:05.342 70.550 70.545 70.45 998 2024-01-03 08:02:05.342 70.545 70.545 70.45 999 2024-01-03 08:02:05.347 70.545 70.545 70.45 MID_PRICE_BACK_60S MID_PRICE_FWD_1S MID_PRICE_FWD_10S \ 0 NaN NaN NaN 1 NaN 66.215 70.145 2 NaN 66.215 70.145 3 NaN 70.095 70.145 4 NaN 70.095 70.145 .. ... ... ... 995 70.4 70.550 70.525 996 70.4 70.545 70.525 997 70.4 70.545 70.525 998 70.4 70.545 70.525 999 70.4 70.545 70.525 MID_PRICE_FWD_60S 0 NaN 1 70.40 2 70.40 3 70.40 4 70.40 .. ... 995 70.47 996 70.47 997 70.47 998 70.47 999 70.47 [1000 rows x 8 columns] ```