Time Series#
This section contains 14 examples for Time Series 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__'
Accumulative Sum#
Calculate the running accumulative aggregation of Trade Size across the defined time period.
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
data = trd.agg({'ACC_SUM': otp.agg.sum('SIZE')}, running=True, all_fields=True)
# Return first 1000 Rows
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 9, 40),
timezone='America/New_York',
symbols='CSCO')
result
| Time | PRICE | SIZE | ACC_SUM | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 2 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 5 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 105 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 110 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | 156 |
| ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | 577230 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 577280 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 577380 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 577480 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | 577481 |
1000 rows × 4 columns
Exponential Moving Average#
Query VOD (Vodafone) trade data and compute exponential moving averages.
Returns running exponential weighted average (EWA) and exponential time-weighted average (ETWA).
import onetick.py as otp
# Define the time interval
start = otp.dt(2024, 1, 3, 8) # Start: January 3, 2024 at 8:00 AM
end = otp.dt(2024, 1, 4, 16) # End: January 4, 2024 at 4:00 PM
# Create the data source for VOD trades
trades = otp.DataSource(
db='LSE_SAMPLE', # London Stock Exchange sample database
tick_type='TRD', # Trade tick type
schema_policy='manual', # Manually specify schema
schema={'PRICE': float}, # Define the schema for trades: only trade PRICE is needed
)
# Compute running exponential weighted averages
trades_agg = trades.agg({
'EWA_PRICE': otp.agg.exp_w_average('PRICE', decay=0.01), # Exponential weighted average with decay 0.01
'ETWA_PRICE': otp.agg.exp_tw_average('PRICE', decay=300) # Exponential time-weighted average with decay 300 seconds
}, running=True, all_fields=True) # Calculate running aggregates and keep all fields
# Return first 1000 Rows
trades_agg = trades_agg.limit(1000)
# Select the required columns and run the query
result = otp.run(
trades_agg[['PRICE', 'EWA_PRICE', 'ETWA_PRICE']], # Select price and both averages
symbols=['VOD'], # Vodafone stock symbol
start=start, # Query start time
end=end, # Query end time
timezone='UTC', # Use UTC timezone
)
result # Display results
| Time | PRICE | EWA_PRICE | ETWA_PRICE | |
|---|---|---|---|---|
| 0 | 2024-01-03 08:00:06.232 | 70.000 | 70.000000 | 70.000000 |
| 1 | 2024-01-03 08:00:06.233 | 70.010 | 70.005025 | 70.000000 |
| 2 | 2024-01-03 08:00:06.287 | 70.010 | 70.006700 | 70.009818 |
| 3 | 2024-01-03 08:00:08.113 | 70.104 | 70.031391 | 70.009995 |
| 4 | 2024-01-03 08:00:09.380 | 70.137 | 70.052937 | 70.047912 |
| ... | ... | ... | ... | ... |
| 995 | 2024-01-03 08:49:23.760 | 70.650 | 69.757585 | 69.257167 |
| 996 | 2024-01-03 08:49:28.304 | 70.630 | 69.766266 | 69.271729 |
| 997 | 2024-01-03 08:49:28.304 | 70.630 | 69.774861 | 69.271729 |
| 998 | 2024-01-03 08:50:29.344 | 70.650 | 69.783569 | 69.450560 |
| 999 | 2024-01-03 08:50:40.916 | 70.620 | 69.791892 | 69.482233 |
1000 rows × 4 columns
Multi-day Avg Minute Statistics#
Calculate the volume profile across a specified month as average trade size.
Filtering trades based on trade condition, time range and day of week.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE', 'EXCHANGE', 'COND']]
data = data.where((data['PRICE'] != otp.nan) & (data['SIZE'] != 0))
data = data.character_present(field=data['COND'], characters='O6TU', discard_on_match=True)
data = data.character_present(field=data['COND'], characters='IBCGHLMNPQRVWZ479', discard_on_match=True)
data = data.agg({'SIZE':otp.agg.sum('SIZE')}, bucket_interval=60, bucket_time='start')
data['DAY_NAME'] = data['TIMESTAMP'].dt.day_name('America/New_York')
data = data.where((data['DAY_NAME'] != 'Saturday') & (data['DAY_NAME'] != 'Sunday'))
data['MIN_BAR'] = data['TIMESTAMP'].dt.strftime('%H:%M:%S', 'America/New_York')
data = data.where((data['MIN_BAR'] >= '09:30:00') & (data['MIN_BAR'] < '16:00:00'))
data = data.agg({
'AVG_SIZE':otp.agg.average('SIZE'),
'STDDEV_SIZE':otp.agg.stddev('SIZE'),
'DAY_COUNT':otp.agg.count()
}, group_by='MIN_BAR', bucket_time='start')
result = otp.run(data,
start=otp.dt(2024, 2, 6),
end=otp.dt(2024, 3, 7),
timezone='America/New_York',
symbols='CSCO')
result
| Time | MIN_BAR | AVG_SIZE | STDDEV_SIZE | DAY_COUNT | |
|---|---|---|---|---|---|
| 0 | 2024-02-06 | 09:30:00 | 129808.590909 | 148460.572913 | 22 |
| 1 | 2024-02-06 | 09:31:00 | 73484.227273 | 109267.206402 | 22 |
| 2 | 2024-02-06 | 09:32:00 | 61435.545455 | 89421.688922 | 22 |
| 3 | 2024-02-06 | 09:33:00 | 51309.363636 | 57197.988607 | 22 |
| 4 | 2024-02-06 | 09:34:00 | 42841.272727 | 43743.937939 | 22 |
| ... | ... | ... | ... | ... | ... |
| 385 | 2024-02-06 | 15:55:00 | 184664.000000 | 99353.911424 | 22 |
| 386 | 2024-02-06 | 15:56:00 | 142176.363636 | 96546.592916 | 22 |
| 387 | 2024-02-06 | 15:57:00 | 159019.227273 | 88196.354600 | 22 |
| 388 | 2024-02-06 | 15:58:00 | 228636.954545 | 112845.546386 | 22 |
| 389 | 2024-02-06 | 15:59:00 | 451829.500000 | 234156.444085 | 22 |
390 rows × 5 columns
Rolling Sum#
Calculate the running aggregation of Trade Size across a rolling 60 second time window.
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
data = trd.agg({'ROLLING_SUM': otp.agg.sum('SIZE')},
bucket_interval=60, running=True, all_fields=True)
# Return first 1000 Rows
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 9, 40),
timezone='America/New_York',
symbols='CSCO')
result
| Time | PRICE | SIZE | ROLLING_SUM | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 2 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 5 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 105 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 110 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | 156 |
| ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | 577230 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 577280 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 577380 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 577480 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | 577481 |
1000 rows × 4 columns
Rollup of Pre-calculated Quote Bars (QTE_1M)#
Rollup of pre-calculated 1 minute quote bars,
using the QTE_1M tick type and BARS database US_COMP_SAMPLE_BARS.
The aggregation is more complex as input 1 minute quote bars may not include quotes.
skip_tick_if=0 is used to skip rows where the value is 0, and skip_tick_if=otp.nan, where the value is nan.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='QTE_1M')
# modify timestamp to start, rather than end of bar (TRD_1M is saved with timestamps for the end of bar)
data['NEW_TS'] = data['TIMESTAMP']
data = data.update(if_set={'NEW_TS': data['TIMESTAMP'] - otp.Minute(1)},
where=data['TIMESTAMP'] > data['_START_TIME'] - otp.Minute(1))
data = data.update_timestamp('NEW_TS', max_delay_of_new_timestamp=otp.Minute(1))
# Define set of aggregations, as certain records may not values, may need to skip for First and Last aggregates
rollup = data.agg(
{
'FIRST_BID_PRICE': otp.agg.first('FIRST_BID_PRICE', skip_tick_if=otp.nan),
'FIRST_BID_SIZE': otp.agg.first('FIRST_BID_SIZE', skip_tick_if=0),
'FIRST_BID_TIME': otp.agg.first('FIRST_BID_TIME', large_ints=True, skip_tick_if=0),
'FIRST_ASK_PRICE': otp.agg.first('FIRST_ASK_PRICE', skip_tick_if=otp.nan),
'FIRST_ASK_SIZE': otp.agg.first('FIRST_ASK_SIZE', skip_tick_if=0),
'FIRST_ASK_TIME': otp.agg.first('FIRST_ASK_TIME', large_ints=True, skip_tick_if=0),
'HIGH_BID': otp.agg.max('HIGH_BID'),
'LOW_ASK': otp.agg.min('LOW_ASK'),
'LAST_BID_PRICE': otp.agg.last('LAST_BID_PRICE', skip_tick_if=otp.nan),
'LAST_BID_SIZE': otp.agg.last('LAST_BID_SIZE', skip_tick_if=0),
'LAST_BID_TIME': otp.agg.last('LAST_BID_TIME', large_ints=True, skip_tick_if=0),
'LAST_ASK_PRICE': otp.agg.last('LAST_ASK_PRICE', skip_tick_if=otp.nan),
'LAST_ASK_SIZE': otp.agg.last('LAST_ASK_SIZE', skip_tick_if=0),
'LAST_ASK_TIME': otp.agg.last('LAST_ASK_TIME', large_ints=True, skip_tick_if=0),
'MID_TWAP': otp.agg.average('MID_TWAP'),
'MID_LAST': otp.agg.last('MID_LAST', skip_tick_if=otp.nan),
'SPREAD_MIN': otp.agg.min('SPREAD_MIN'),
'SPREAD_MAX': otp.agg.max('SPREAD_MAX'),
'SPREAD_TWAP': otp.agg.average('SPREAD_TWAP'),
'SPREAD_LAST': otp.agg.last('SPREAD_LAST', skip_tick_if=otp.nan),
'QUOTE_TICK_COUNT': otp.agg.sum('QUOTE_TICK_COUNT'),
'CLOUD_DB': otp.agg.last('CLOUD_DB')
},
# Apply Aggregations across 5 minute buckets
bucket_interval=otp.Minute(5)
)
result = otp.run(rollup,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16),
timezone='America/New_York',
symbols='CSCO')
result
| Time | FIRST_BID_PRICE | FIRST_BID_SIZE | FIRST_ASK_PRICE | FIRST_ASK_SIZE | HIGH_BID | LOW_ASK | LAST_BID_PRICE | LAST_BID_SIZE | LAST_ASK_PRICE | ... | SPREAD_MIN | SPREAD_MAX | SPREAD_TWAP | SPREAD_LAST | QUOTE_TICK_COUNT | CLOUD_DB | FIRST_BID_TIME | FIRST_ASK_TIME | LAST_BID_TIME | LAST_ASK_TIME | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:35:00 | 50.00 | 9 | 50.18 | 2 | 50.22 | 50.01 | 50.01 | 10 | 50.02 | ... | 0.00 | 0.18 | 0.014241 | 0.01 | 27777 | NYSE_TAQ | 2024-01-03 09:30:00.001830617 | 2024-01-03 09:30:00.001830617 | 2024-01-03 09:34:59.646788708 | 2024-01-03 09:34:59.646788708 |
| 1 | 2024-01-03 09:40:00 | 50.01 | 10 | 50.02 | 10 | 50.10 | 49.95 | 50.08 | 3 | 50.09 | ... | 0.00 | 0.02 | 0.009874 | 0.01 | 33004 | NYSE_TAQ | 2024-01-03 09:35:00.017051433 | 2024-01-03 09:35:00.017051433 | 2024-01-03 09:39:59.798415631 | 2024-01-03 09:39:59.798415631 |
| 2 | 2024-01-03 09:45:00 | 50.08 | 3 | 50.09 | 6 | 50.13 | 50.04 | 50.06 | 8 | 50.07 | ... | 0.00 | 0.02 | 0.009954 | 0.01 | 30841 | NYSE_TAQ | 2024-01-03 09:40:00.010846436 | 2024-01-03 09:40:00.010846436 | 2024-01-03 09:44:59.913091069 | 2024-01-03 09:44:59.913091069 |
| 3 | 2024-01-03 09:50:00 | 50.06 | 8 | 50.07 | 5 | 50.14 | 50.07 | 50.10 | 10 | 50.11 | ... | 0.00 | 0.02 | 0.009997 | 0.01 | 25767 | NYSE_TAQ | 2024-01-03 09:45:00.005034758 | 2024-01-03 09:45:00.005034758 | 2024-01-03 09:49:59.913721766 | 2024-01-03 09:49:59.913721766 |
| 4 | 2024-01-03 09:55:00 | 50.10 | 10 | 50.11 | 4 | 50.18 | 50.11 | 50.15 | 15 | 50.16 | ... | 0.00 | 0.02 | 0.009982 | 0.01 | 27302 | NYSE_TAQ | 2024-01-03 09:50:00.002211689 | 2024-01-03 09:50:00.002211689 | 2024-01-03 09:54:59.962710362 | 2024-01-03 09:54:59.962710362 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 73 | 2024-01-03 15:40:00 | 50.56 | 22 | 50.57 | 14 | 50.63 | 50.57 | 50.61 | 14 | 50.62 | ... | 0.00 | 0.02 | 0.009954 | 0.01 | 25145 | NYSE_TAQ | 2024-01-03 15:35:00.001295259 | 2024-01-03 15:35:00.001295259 | 2024-01-03 15:39:59.517222261 | 2024-01-03 15:39:59.517222261 |
| 74 | 2024-01-03 15:45:00 | 50.61 | 14 | 50.62 | 34 | 50.62 | 50.57 | 50.60 | 201 | 50.61 | ... | 0.00 | 0.02 | 0.009964 | 0.01 | 35456 | NYSE_TAQ | 2024-01-03 15:40:00.037708852 | 2024-01-03 15:40:00.037708852 | 2024-01-03 15:44:59.900149085 | 2024-01-03 15:44:59.900149085 |
| 75 | 2024-01-03 15:50:00 | 50.60 | 201 | 50.61 | 28 | 50.67 | 50.59 | 50.60 | 7 | 50.61 | ... | 0.00 | 0.02 | 0.010000 | 0.01 | 29970 | NYSE_TAQ | 2024-01-03 15:45:00.005060393 | 2024-01-03 15:45:00.005060393 | 2024-01-03 15:49:59.990600181 | 2024-01-03 15:49:59.990600181 |
| 76 | 2024-01-03 15:55:00 | 50.60 | 7 | 50.61 | 27 | 50.61 | 50.48 | 50.47 | 29 | 50.48 | ... | -0.02 | 0.02 | 0.009987 | 0.01 | 57574 | NYSE_TAQ | 2024-01-03 15:50:00.003122479 | 2024-01-03 15:50:00.003122479 | 2024-01-03 15:54:59.805799225 | 2024-01-03 15:54:59.805799225 |
| 77 | 2024-01-03 16:00:00 | 50.47 | 29 | 50.48 | 24 | 50.55 | 50.48 | 50.52 | 12 | 50.53 | ... | 0.00 | 0.02 | 0.009986 | 0.01 | 53948 | NYSE_TAQ | 2024-01-03 15:55:00.000474694 | 2024-01-03 15:55:00.000474694 | 2024-01-03 15:59:59.994124940 | 2024-01-03 15:59:59.994124940 |
78 rows × 23 columns
Rollup of Pre-calculated Trade Bars (TRD_1M)#
Rollup of pre-calculated 1 minute trade bars,
using the TRD_1M tick type and BARS database US_COMP_SAMPLE_BARS.
The aggregation is more complex as input 1 minute trade bars may not include trades.
skip_tick_if=0 is used to skip rows where the value is 0, and skip_tick_if=otp.nan, where the value is nan.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
# modify timestamp to start of minute bar, rather than end of bar (TRD_1M is saved with timestamps for the end of bar)
data['NEW_TS'] = data['TIMESTAMP']
data = data.update(if_set={'NEW_TS': data['TIMESTAMP'] - otp.Minute(1)},
where=data['TIMESTAMP'] > data['_START_TIME'] - otp.Minute(1))
data = data.update_timestamp('NEW_TS', max_delay_of_new_timestamp=otp.Minute(1))
# Define set of aggregations, as certain records may not values, may need to skip for First and Last aggregates
rollup = data.agg(
{
'FIRST': otp.agg.first('FIRST', skip_tick_if=otp.nan),
'FIRST_SIZE': otp.agg.first('FIRST_SIZE', skip_tick_if=0),
'FIRST_TIME': otp.agg.first('FIRST_TIME', large_ints=True, skip_tick_if=0),
'HIGH': otp.agg.max('HIGH'),
'LOW': otp.agg.min('LOW'),
'LAST': otp.agg.last('LAST', skip_tick_if=otp.nan),
'LAST_SIZE': otp.agg.last('LAST_SIZE', skip_tick_if=0),
'LAST_TIME': otp.agg.last('LAST_TIME', large_ints=True, skip_tick_if=0),
'VWAP': otp.agg.vwap(price_column='VWAP',size_column='VOLUME'),
'TWAP': otp.agg.average('TWAP'),
'VOLUME': otp.agg.sum('VOLUME'),
'TRADE_TICK_COUNT': otp.agg.sum('TRADE_TICK_COUNT'),
'CLOUD_DB': otp.agg.last('CLOUD_DB')
},
# Apply Aggregations across 5 minute buckets
bucket_interval=otp.Minute(5)
)
result = otp.run(rollup,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16),
timezone='America/New_York',
symbols='CSCO')
result
| Time | FIRST | FIRST_SIZE | HIGH | LOW | LAST | LAST_SIZE | VWAP | TWAP | VOLUME | TRADE_TICK_COUNT | CLOUD_DB | FIRST_TIME | LAST_TIME | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:35:00 | 50.1700 | 100 | 50.22 | 50.000 | 50.0200 | 100 | 50.091271 | 50.076608 | 201809 | 1171 | NYSE_TAQ | 2024-01-03 09:30:00.127459523 | 2024-01-03 09:34:59.152722398 |
| 1 | 2024-01-03 09:40:00 | 50.0100 | 100 | 50.11 | 49.941 | 50.0800 | 100 | 50.039389 | 50.044006 | 118204 | 720 | NYSE_TAQ | 2024-01-03 09:35:00.095924736 | 2024-01-03 09:39:59.079500891 |
| 2 | 2024-01-03 09:45:00 | 50.0850 | 100 | 50.14 | 50.040 | 50.0700 | 100 | 50.083021 | 50.079562 | 108834 | 634 | NYSE_TAQ | 2024-01-03 09:40:00.015000664 | 2024-01-03 09:44:59.912387013 |
| 3 | 2024-01-03 09:50:00 | 50.0700 | 100 | 50.15 | 50.060 | 50.1100 | 100 | 50.103509 | 50.104881 | 120809 | 744 | NYSE_TAQ | 2024-01-03 09:45:00.013469654 | 2024-01-03 09:49:59.040007649 |
| 4 | 2024-01-03 09:55:00 | 50.1100 | 100 | 50.19 | 50.110 | 50.1600 | 200 | 50.152210 | 50.150055 | 139186 | 813 | NYSE_TAQ | 2024-01-03 09:50:00.389976008 | 2024-01-03 09:54:59.858046121 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 73 | 2024-01-03 15:40:00 | 50.5700 | 100 | 50.64 | 50.570 | 50.6150 | 100 | 50.599631 | 50.600055 | 271385 | 1425 | NYSE_TAQ | 2024-01-03 15:35:00.831037944 | 2024-01-03 15:39:59.026229145 |
| 74 | 2024-01-03 15:45:00 | 50.6150 | 100 | 50.63 | 50.565 | 50.6071 | 200 | 50.608675 | 50.613853 | 417947 | 1845 | NYSE_TAQ | 2024-01-03 15:40:05.502129340 | 2024-01-03 15:44:59.564126074 |
| 75 | 2024-01-03 15:50:00 | 50.6019 | 104 | 50.68 | 50.580 | 50.6000 | 100 | 50.637210 | 50.642675 | 532673 | 1921 | NYSE_TAQ | 2024-01-03 15:45:01.282125833 | 2024-01-03 15:49:59.475127758 |
| 76 | 2024-01-03 15:55:00 | 50.6100 | 200 | 50.61 | 50.470 | 50.4750 | 200 | 50.528558 | 50.519795 | 1053244 | 4186 | NYSE_TAQ | 2024-01-03 15:50:00.006851958 | 2024-01-03 15:54:59.401937848 |
| 77 | 2024-01-03 16:00:00 | 50.4800 | 100 | 50.56 | 50.470 | 50.5200 | 4300 | 50.527444 | 50.520318 | 1492305 | 5779 | NYSE_TAQ | 2024-01-03 15:55:00.024403059 | 2024-01-03 15:59:59.990217289 |
78 rows × 14 columns
Simple Moving Averages in Ticks#
Calculate a moving average for price based on the a rolling 60 trade count by setting bucket_units='ticks'.
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
data = trd.agg({'MAVG_PRICE': otp.agg.mean('PRICE')},
bucket_interval=60, bucket_units='ticks', running=True, all_fields=True)
# Return first 1000 Rows
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 9, 40),
timezone='America/New_York',
symbols='CSCO')
result
| Time | PRICE | SIZE | MAVG_PRICE | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 50.020000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 50.090000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 50.116667 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 50.130000 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | 50.130000 |
| ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | 50.122515 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 50.122515 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 50.122515 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 50.122432 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | 50.121182 |
1000 rows × 4 columns
Simple Moving Averages in Time#
Calculate a 1 and 5 minute moving average for Price.
import onetick.py as otp
trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
data = trd.agg({'SMA_1M': otp.agg.mean('PRICE')},
bucket_interval=otp.Minute(1), running=True, all_fields=True)
data = data.agg({'SMA_5M': otp.agg.mean('PRICE')},
bucket_interval=otp.Minute(5), running=True, all_fields=True)
# Return first 1000 rows
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 9, 40),
timezone='America/New_York',
symbols='CSCO')
result
| Time | PRICE | SIZE | SMA_1M | SMA_5M | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 50.020000 | 50.020000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 50.090000 | 50.090000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 50.116667 | 50.116667 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 50.130000 | 50.130000 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | 50.130000 | 50.130000 |
| ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | 50.132580 | 50.132580 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 50.132567 | 50.132567 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 50.132555 | 50.132555 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 50.132542 | 50.132542 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | 50.132499 | 50.132499 |
1000 rows × 5 columns
Slope & Intercept#
Query CSCO (Cisco) trade data and compute linear regression.
Returns slope and intercept of PRICE vs SIZE relationship over entire interval.
import onetick.py as otp
# Define the data source
data = otp.DataSource(
db='US_COMP_SAMPLE', # US composite trades database
tick_type='TRD', # Trade tick type
symbols='CSCO', # Cisco stock symbol
)
# Compute linear regression over the entire interval
regression = data.agg({
'reg': otp.agg.linear_regression('PRICE', 'SIZE') # Linear regression: PRICE (Y) vs SIZE (X)
})
# Run the query
result = otp.run(
regression, # Linear regression results
start=otp.dt(2024, 1, 3), # Start: January 3, 2024 at midnight
end=otp.dt(2024, 1, 4), # End: January 4, 2024 at midnight
timezone='UTC', # Use UTC timezone
)
result # Display results
| Time | reg.SLOPE | reg.INTERCEPT | |
|---|---|---|---|
| 0 | 2024-01-04 | 3.986273e-08 | 50.390932 |
Slope & Intercept between Symbols#
Query 1-minute bars for CSCO and SPY, then compute linear regression.
Returns slope and intercept of CSCO price vs SPY price relationship.
import onetick.py as otp
# Define the time interval
start = otp.dt(2024, 1, 3) # Start: January 3, 2024
end = otp.dt(2024, 1, 4) # End: January 4, 2024
# Load 1-minute bars for CSCO and SPY from US_COMP_BARS
csco = otp.DataSource(
db='US_COMP_BARS', # US composite bars database
tick_type='TRD_1M', # 1-minute trade bars
symbol='CSCO', # Cisco stock
)
spy = otp.DataSource(
db='US_COMP_BARS', # US composite bars database
tick_type='TRD_1M', # 1-minute trade bars
symbol='SPY', # S&P 500 ETF
).add_suffix('_SPY') # Add suffix to SPY columns to avoid naming conflicts
# Join on timestamp using join_by_time (robust to missing/misaligned bars)
joined = otp.join_by_time([csco, spy]) # Time-based join of CSCO and SPY bars
# Compute linear regression: SLOPE and INTERCEPT of CSCO.LAST vs SPY.LAST_SPY
regression = joined.linear_regression('LAST', 'LAST_SPY') # Linear regression: CSCO price (Y) vs SPY price (X)
# Run the query
result = otp.run(regression, start=start, end=end) # Execute query for specified time interval
result[['Time', 'SLOPE', 'INTERCEPT']] # Display Time, Slope, and Intercept columns
| Time | SLOPE | INTERCEPT | |
|---|---|---|---|
| 0 | 2024-01-04 | -0.087152 | 91.298085 |
Time Zone Spec#
Query AAPL trade data with Eastern timezone specification.
Returns first 1000 trades for specified time interval in America/New_York timezone.
import onetick.py as otp
# Define the schema for trade ticks (manual schema policy)
trade_schema = {
'PRICE': float, # Trade price
'SIZE': int, # Trade size/volume
'COND': str, # Trade condition
'EXCHANGE': str, # Exchange identifier
}
# Define the time interval
start = otp.dt(2024, 1, 3, 9, 30) # Start: January 3, 2024 at 9:30 AM
end = otp.dt(2024, 1, 3, 9, 40) # End: January 3, 2024 at 9:40 AM
# Create the DataSource for AAPL trades
trades = otp.DataSource(
db='US_COMP_SAMPLE', # US composite trades database
tick_type='TRD', # Trade tick type
schema_policy='manual', # Manually specify schema
schema=trade_schema, # Use defined schema above
)
# Limit to 1000 rows
trades = trades.limit(1000)
# Run the query for AAPL in the specified interval and timezone
result = otp.run(
trades, # Trade data source
symbols=['AAPL'], # Apple stock symbol
start=start, # Query start time
end=end, # Query end time
timezone='America/New_York', # Eastern timezone (IANA format)
)
result # Display results
| Time | PRICE | SIZE | COND | EXCHANGE | STOP_STOCK | SOURCE | TRF | TTE | TICKER | DELETED_TIME | TICK_STATUS | CORR | SEQ_NUM | TRADE_ID | PARTICIPANT_TIME | TRF_TIME | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.004113329 | 184.29 | 300 | @ T | Q | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 337117 | 4535 | 2024-01-03 09:30:00.004088724 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 1 | 2024-01-03 09:30:00.004115356 | 184.29 | 100 | @ T | Q | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 337118 | 4536 | 2024-01-03 09:30:00.004088724 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 2 | 2024-01-03 09:30:00.004631270 | 184.22 | 100 | @ | K | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 337139 | 2758 | 2024-01-03 09:30:00.004373000 | 1969-12-31 19:00:00.000000000 | 2 | ||
| 3 | 2024-01-03 09:30:00.004634829 | 184.21 | 10 | @ I | K | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 337140 | 2759 | 2024-01-03 09:30:00.004373000 | 1969-12-31 19:00:00.000000000 | 3 | ||
| 4 | 2024-01-03 09:30:00.004637339 | 184.19 | 25 | @ I | K | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 337141 | 2760 | 2024-01-03 09:30:00.004373000 | 1969-12-31 19:00:00.000000000 | 4 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:01.298814506 | 184.32 | 100 | @ | P | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 346827 | 8403 | 2024-01-03 09:30:01.298472697 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 996 | 2024-01-03 09:30:01.308859960 | 184.31 | 20 | @ I | P | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 346967 | 8404 | 2024-01-03 09:30:01.308517681 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 997 | 2024-01-03 09:30:01.332097998 | 184.31 | 13 | @ I | D | N | Q | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 347134 | 3446 | 2024-01-03 09:30:01.331386885 | 2024-01-03 09:30:01.332073963 | 0 | |
| 998 | 2024-01-03 09:30:01.344762538 | 184.33 | 2 | @F I | J | N | 1 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 347213 | 55 | 2024-01-03 09:30:01.344572000 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 999 | 2024-01-03 09:30:01.401645194 | 184.30 | 9 | @ I | U | N | 0 | AAPL | 1969-12-31 19:00:00 | 0 | 0 | 347841 | 329 | 2024-01-03 09:30:01.401438624 | 1969-12-31 19:00:00.000000000 | 0 |
1000 rows × 18 columns
Trade Statistics for Symbol List Grouped By Calendar Market Activity#
Aggregating Trades for a list of Symbols across Venues, including Calendar Information using mkt_activity().
MktActivity returns:
Rb [Pre Market], Rr [Trading], Ra [Post Market], R1 [Morning], Rx [Lunch], R2 [Afternoon].
Combining Output into a single result set.
import onetick.py as otp
# Define Symbol List with Symbols including the Database using syntax [Db Name]::[Ticker Symbol]
sym_list = [
'LSE::VOD',
'EURONEXT::AF',
'XETRA::DBK',
'LSE::TSCO',
'LSE::SHEL'
]
# Define Data Source, in this case without specifying the symbol name.
# As the schema is not yet known, set the schema policy to manual
trd = otp.DataSource(tick_type='TRD', schema_policy='manual')
# Define the output schema
trd.schema.set(
PRICE=float,
SIZE=int,
TRADE_VENUE=str,
BOOK_TYPE=str,
TRADE_PERIOD=str
)
trd = trd[['PRICE', 'SIZE', 'TRADE_VENUE', 'BOOK_TYPE', 'TRADE_PERIOD']]
# Add the Symbol to the DataSource
trd['SYMBOL_NAME'] = trd['_SYMBOL_NAME']
# Extract The Calendar Name from the Database component of the Symbol
trd['CALENDAR_NAME'] = 'CLOUD_DB_' + trd['SYMBOL_NAME'].str.extract(r'([^:]+)', rewrite=r'\1')
# Add the Market Activity field, based on the selected Calendar
trd = trd.mkt_activity(calendar_name=trd['CALENDAR_NAME'])
# Aggregates All Trades, grouped by MKT_ACTIVITY value
data = trd.agg({
'FIRST_PRICE': otp.agg.first('PRICE'),
'HIGH_PRICE': otp.agg.max('PRICE'),
'LOW_PRICE': otp.agg.min('PRICE'),
'LAST_PRICE': otp.agg.last('PRICE'),
'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
'SUM_SIZE': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count()
}, group_by=trd['MKT_ACTIVITY'])
# Create a single output, merging all the inputs into a single resultset.
merged = otp.merge([data], symbols=sym_list, identify_input_ts=True, separate_db_name=True)
# Run the query returning the data in the selected timezone
result = otp.run(merged,
start=otp.datetime(2024, 1, 3),
end=otp.datetime(2024, 1, 4),
timezone='Europe/London')
result
| Time | MKT_ACTIVITY | FIRST_PRICE | HIGH_PRICE | LOW_PRICE | LAST_PRICE | VWAP_PRICE | SUM_SIZE | TRADE_COUNT | SYMBOL_NAME | DB_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-04 | Ra | 69.8800 | 70.6620 | 69.4700 | 69.7020 | 69.562057 | 20056126 | 62 | VOD | LSE | TRD |
| 1 | 2024-01-04 | Rb | 69.7600 | 69.7600 | 69.7600 | 69.7600 | 69.760000 | 40000 | 1 | VOD | LSE | TRD |
| 2 | 2024-01-04 | Rr | 70.0000 | 71.0166 | 0.8135 | 69.4800 | 70.129743 | 70105531 | 6888 | VOD | LSE | TRD |
| 3 | 2024-01-04 | Rr | 13.4000 | 13.4440 | 12.8620 | 12.9080 | 13.040510 | 1771782 | 5194 | AF | EURONEXT | TRD |
| 4 | 2024-01-04 | Ra | 12.2500 | 12.2500 | 12.2500 | 12.2500 | 12.250000 | 7000 | 1 | DBK | XETRA | TRD |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 8 | 2024-01-04 | Rb | 292.9667 | 292.9667 | 292.9667 | 292.9667 | 292.966700 | 6000 | 1 | TSCO | LSE | TRD |
| 9 | 2024-01-04 | Rr | 293.6000 | 298.4019 | 291.4000 | 297.7000 | 296.535342 | 8377291 | 5985 | TSCO | LSE | TRD |
| 10 | 2024-01-04 | Ra | 2599.5000 | 2600.0000 | 29.9927 | 2580.0080 | 2165.772030 | 7938453 | 140 | SHEL | LSE | TRD |
| 11 | 2024-01-04 | Rb | 2592.6667 | 2592.6667 | 30.2825 | 30.2825 | 1128.447157 | 700 | 2 | SHEL | LSE | TRD |
| 12 | 2024-01-04 | Rr | 2572.5000 | 2600.5000 | 29.9300 | 2599.5000 | 2139.709673 | 5710843 | 14101 | SHEL | LSE | TRD |
13 rows × 12 columns