Markouts and Time Shifts#

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__'

Prevailing quote at the time of a trade#

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

	Time	PRICE	SIZE	ASK_PRICE	BID_PRICE	NBBO_TIME
0	2024-02-01 09:30:00.000961260	184.010	302	184.14	184.00	2024-02-01 09:30:00.000860953
1	2024-02-01 09:30:00.000961491	184.000	100	184.14	184.00	2024-02-01 09:30:00.000860953
2	2024-02-01 09:30:00.000961701	184.000	1	184.14	184.00	2024-02-01 09:30:00.000860953
3	2024-02-01 09:30:00.000973163	184.000	1	184.14	183.90	2024-02-01 09:30:00.000969529
4	2024-02-01 09:30:00.000973355	184.000	5	184.14	183.90	2024-02-01 09:30:00.000969529
...	...	...	...	...	...	...
574	2024-02-01 09:30:00.987184691	183.900	9	183.93	183.89	2024-02-01 09:30:00.973387417
575	2024-02-01 09:30:00.990378350	183.920	1	183.93	183.89	2024-02-01 09:30:00.987461418
576	2024-02-01 09:30:00.991941892	183.935	1	183.93	183.89	2024-02-01 09:30:00.987461418
577	2024-02-01 09:30:00.993785116	183.905	300	183.93	183.89	2024-02-01 09:30:00.987461418
578	2024-02-01 09:30:00.996512511	183.934	5	183.93	183.89	2024-02-01 09:30:00.987461418

579 rows × 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.

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

	Time	PRICE	SIZE	ASK_PRICE_B1	BID_PRICE_B1	NBBO_TIME_B1	ASK_PRICE_A1	BID_PRICE_A1	NBBO_TIME_A1
0	2024-02-01 09:30:00.000961260	184.010	302	184.29	184.14	2024-02-01 09:29:58.861113417	183.93	183.89	2024-02-01 09:30:00.987461418
1	2024-02-01 09:30:00.000961491	184.000	100	184.29	184.14	2024-02-01 09:29:58.861113417	183.93	183.89	2024-02-01 09:30:00.987461418
2	2024-02-01 09:30:00.000961701	184.000	1	184.29	184.14	2024-02-01 09:29:58.861113417	183.93	183.89	2024-02-01 09:30:00.987461418
3	2024-02-01 09:30:00.000973163	184.000	1	184.29	184.14	2024-02-01 09:29:58.861113417	183.93	183.89	2024-02-01 09:30:00.987461418
4	2024-02-01 09:30:00.000973355	184.000	5	184.29	184.14	2024-02-01 09:29:58.861113417	183.93	183.89	2024-02-01 09:30:00.987461418
...	...	...	...	...	...	...	...	...	...
574	2024-02-01 09:30:00.987184691	183.900	9	184.14	184.00	2024-02-01 09:29:59.972347877	183.98	183.91	2024-02-01 09:30:01.955975928
575	2024-02-01 09:30:00.990378350	183.920	1	184.14	184.00	2024-02-01 09:29:59.972347877	183.98	183.91	2024-02-01 09:30:01.955975928
576	2024-02-01 09:30:00.991941892	183.935	1	184.14	184.00	2024-02-01 09:29:59.972347877	183.98	183.91	2024-02-01 09:30:01.955975928
577	2024-02-01 09:30:00.993785116	183.905	300	184.14	184.00	2024-02-01 09:29:59.972347877	183.98	183.91	2024-02-01 09:30:01.955975928
578	2024-02-01 09:30:00.996512511	183.934	5	184.14	184.00	2024-02-01 09:29:59.972347877	183.98	183.91	2024-02-01 09:30:01.955975928

579 rows × 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).

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

	Time	PRICE	SIZE	TRADE_ID	TRADE_VENUE	PRICE_BACK_1S	PRICE_BACK_10S	PRICE_BACK_60S	PRICE_FWD_1S	PRICE_FWD_10S	PRICE_FWD_60S
0	2024-01-03 08:00:06.232	70.000	184613	911727684223506	XLON	NaN	NaN	NaN	70.010	70.1320	70.30
1	2024-01-03 08:00:06.233	70.010	140	911727684223588	XLON	NaN	NaN	NaN	70.010	70.1320	70.30
2	2024-01-03 08:00:06.287	70.010	500	911727684223589	XLON	NaN	NaN	NaN	70.010	70.1320	70.45
3	2024-01-03 08:00:08.113	70.104	2800	25706436899852400	XLON	70.010	NaN	NaN	70.104	70.1320	70.08
4	2024-01-03 08:00:09.380	70.137	49	892755055723892848	XLON	70.104	NaN	NaN	70.146	70.1320	70.08
...	...	...	...	...	...	...	...	...	...	...	...
995	2024-01-03 08:49:23.760	70.650	711	911727684233082	XLON	70.650	70.65	70.6700	70.650	70.6300	70.63
996	2024-01-03 08:49:28.304	70.630	1	911727684233089	XLON	70.650	70.65	70.6549	70.630	70.6300	70.63
997	2024-01-03 08:49:28.304	70.630	969	911727684233090	XLON	70.650	70.65	70.6549	70.630	70.6300	70.63
998	2024-01-03 08:50:29.344	70.650	4716	911727684233162	XLON	70.630	70.63	70.6300	70.650	70.6500	70.63
999	2024-01-03 08:50:40.916	70.620	751	911727684233179	XLON	70.650	70.65	70.6300	70.660	70.6223	70.58

1000 rows × 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).

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

	Time	MID_PRICE	MID_PRICE_BACK_1S	MID_PRICE_BACK_10S	MID_PRICE_BACK_60S	MID_PRICE_FWD_1S	MID_PRICE_FWD_10S	MID_PRICE_FWD_60S
0	2024-01-03 08:00:00.156	67.320	NaN	NaN	NaN	NaN	NaN	NaN
1	2024-01-03 08:00:02.864	67.320	67.320	NaN	NaN	66.215	70.145	70.40
2	2024-01-03 08:00:02.865	66.215	67.320	NaN	NaN	66.215	70.145	70.40
3	2024-01-03 08:00:06.225	66.215	66.215	NaN	NaN	70.095	70.145	70.40
4	2024-01-03 08:00:06.225	66.215	66.215	NaN	NaN	70.095	70.145	70.40
...	...	...	...	...	...	...	...	...
995	2024-01-03 08:02:05.342	70.550	70.545	70.45	70.4	70.550	70.525	70.47
996	2024-01-03 08:02:05.342	70.550	70.545	70.45	70.4	70.545	70.525	70.47
997	2024-01-03 08:02:05.342	70.550	70.545	70.45	70.4	70.545	70.525	70.47
998	2024-01-03 08:02:05.342	70.545	70.545	70.45	70.4	70.545	70.525	70.47
999	2024-01-03 08:02:05.347	70.545	70.545	70.45	70.4	70.545	70.525	70.47

1000 rows × 8 columns