# 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.
```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__'
```
## Accumulative Sum
Calculate the running accumulative aggregation of Trade Size across the defined time period.
```ipython3
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
```
```myst-ansi
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 x 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).
```ipython3
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
```
```myst-ansi
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 x 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.
```ipython3
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
```
```myst-ansi
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 x 5 columns]
```
## Rolling Sum
Calculate the running aggregation of Trade Size across a rolling 60 second time window.
```ipython3
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
```
```myst-ansi
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 x 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.
```ipython3
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
```
```myst-ansi
Time FIRST_BID_PRICE FIRST_BID_SIZE FIRST_ASK_PRICE \
0 2024-01-03 09:35:00 50.00 9 50.18
1 2024-01-03 09:40:00 50.01 10 50.02
2 2024-01-03 09:45:00 50.08 3 50.09
3 2024-01-03 09:50:00 50.06 8 50.07
4 2024-01-03 09:55:00 50.10 10 50.11
.. ... ... ... ...
73 2024-01-03 15:40:00 50.56 22 50.57
74 2024-01-03 15:45:00 50.61 14 50.62
75 2024-01-03 15:50:00 50.60 201 50.61
76 2024-01-03 15:55:00 50.60 7 50.61
77 2024-01-03 16:00:00 50.47 29 50.48
FIRST_ASK_SIZE HIGH_BID LOW_ASK LAST_BID_PRICE LAST_BID_SIZE \
0 2 50.22 50.01 50.01 10
1 10 50.10 49.95 50.08 3
2 6 50.13 50.04 50.06 8
3 5 50.14 50.07 50.10 10
4 4 50.18 50.11 50.15 15
.. ... ... ... ... ...
73 14 50.63 50.57 50.61 14
74 34 50.62 50.57 50.60 201
75 28 50.67 50.59 50.60 7
76 27 50.61 50.48 50.47 29
77 24 50.55 50.48 50.52 12
LAST_ASK_PRICE ... SPREAD_MIN SPREAD_MAX SPREAD_TWAP SPREAD_LAST \
0 50.02 ... 0.00 0.18 0.014241 0.01
1 50.09 ... 0.00 0.02 0.009874 0.01
2 50.07 ... 0.00 0.02 0.009954 0.01
3 50.11 ... 0.00 0.02 0.009997 0.01
4 50.16 ... 0.00 0.02 0.009982 0.01
.. ... ... ... ... ... ...
73 50.62 ... 0.00 0.02 0.009954 0.01
74 50.61 ... 0.00 0.02 0.009964 0.01
75 50.61 ... 0.00 0.02 0.010000 0.01
76 50.48 ... -0.02 0.02 0.009987 0.01
77 50.53 ... 0.00 0.02 0.009986 0.01
QUOTE_TICK_COUNT CLOUD_DB FIRST_BID_TIME \
0 27777 NYSE_TAQ 2024-01-03 09:30:00.001830617
1 33004 NYSE_TAQ 2024-01-03 09:35:00.017051433
2 30841 NYSE_TAQ 2024-01-03 09:40:00.010846436
3 25767 NYSE_TAQ 2024-01-03 09:45:00.005034758
4 27302 NYSE_TAQ 2024-01-03 09:50:00.002211689
.. ... ... ...
73 25145 NYSE_TAQ 2024-01-03 15:35:00.001295259
74 35456 NYSE_TAQ 2024-01-03 15:40:00.037708852
75 29970 NYSE_TAQ 2024-01-03 15:45:00.005060393
76 57574 NYSE_TAQ 2024-01-03 15:50:00.003122479
77 53948 NYSE_TAQ 2024-01-03 15:55:00.000474694
FIRST_ASK_TIME LAST_BID_TIME \
0 2024-01-03 09:30:00.001830617 2024-01-03 09:34:59.646788708
1 2024-01-03 09:35:00.017051433 2024-01-03 09:39:59.798415631
2 2024-01-03 09:40:00.010846436 2024-01-03 09:44:59.913091069
3 2024-01-03 09:45:00.005034758 2024-01-03 09:49:59.913721766
4 2024-01-03 09:50:00.002211689 2024-01-03 09:54:59.962710362
.. ... ...
73 2024-01-03 15:35:00.001295259 2024-01-03 15:39:59.517222261
74 2024-01-03 15:40:00.037708852 2024-01-03 15:44:59.900149085
75 2024-01-03 15:45:00.005060393 2024-01-03 15:49:59.990600181
76 2024-01-03 15:50:00.003122479 2024-01-03 15:54:59.805799225
77 2024-01-03 15:55:00.000474694 2024-01-03 15:59:59.994124940
LAST_ASK_TIME
0 2024-01-03 09:34:59.646788708
1 2024-01-03 09:39:59.798415631
2 2024-01-03 09:44:59.913091069
3 2024-01-03 09:49:59.913721766
4 2024-01-03 09:54:59.962710362
.. ...
73 2024-01-03 15:39:59.517222261
74 2024-01-03 15:44:59.900149085
75 2024-01-03 15:49:59.990600181
76 2024-01-03 15:54:59.805799225
77 2024-01-03 15:59:59.994124940
[78 rows x 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.
```ipython3
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
```
```myst-ansi
Time FIRST FIRST_SIZE HIGH LOW LAST \
0 2024-01-03 09:35:00 50.1700 100 50.22 50.000 50.0200
1 2024-01-03 09:40:00 50.0100 100 50.11 49.941 50.0800
2 2024-01-03 09:45:00 50.0850 100 50.14 50.040 50.0700
3 2024-01-03 09:50:00 50.0700 100 50.15 50.060 50.1100
4 2024-01-03 09:55:00 50.1100 100 50.19 50.110 50.1600
.. ... ... ... ... ... ...
73 2024-01-03 15:40:00 50.5700 100 50.64 50.570 50.6150
74 2024-01-03 15:45:00 50.6150 100 50.63 50.565 50.6071
75 2024-01-03 15:50:00 50.6019 104 50.68 50.580 50.6000
76 2024-01-03 15:55:00 50.6100 200 50.61 50.470 50.4750
77 2024-01-03 16:00:00 50.4800 100 50.56 50.470 50.5200
LAST_SIZE VWAP TWAP VOLUME TRADE_TICK_COUNT CLOUD_DB \
0 100 50.091271 50.076608 201809 1171 NYSE_TAQ
1 100 50.039389 50.044006 118204 720 NYSE_TAQ
2 100 50.083021 50.079562 108834 634 NYSE_TAQ
3 100 50.103509 50.104881 120809 744 NYSE_TAQ
4 200 50.152210 50.150055 139186 813 NYSE_TAQ
.. ... ... ... ... ... ...
73 100 50.599631 50.600055 271385 1425 NYSE_TAQ
74 200 50.608675 50.613853 417947 1845 NYSE_TAQ
75 100 50.637210 50.642675 532673 1921 NYSE_TAQ
76 200 50.528558 50.519795 1053244 4186 NYSE_TAQ
77 4300 50.527444 50.520318 1492305 5779 NYSE_TAQ
FIRST_TIME LAST_TIME
0 2024-01-03 09:30:00.127459523 2024-01-03 09:34:59.152722398
1 2024-01-03 09:35:00.095924736 2024-01-03 09:39:59.079500891
2 2024-01-03 09:40:00.015000664 2024-01-03 09:44:59.912387013
3 2024-01-03 09:45:00.013469654 2024-01-03 09:49:59.040007649
4 2024-01-03 09:50:00.389976008 2024-01-03 09:54:59.858046121
.. ... ...
73 2024-01-03 15:35:00.831037944 2024-01-03 15:39:59.026229145
74 2024-01-03 15:40:05.502129340 2024-01-03 15:44:59.564126074
75 2024-01-03 15:45:01.282125833 2024-01-03 15:49:59.475127758
76 2024-01-03 15:50:00.006851958 2024-01-03 15:54:59.401937848
77 2024-01-03 15:55:00.024403059 2024-01-03 15:59:59.990217289
[78 rows x 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'`.
```ipython3
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
```
```myst-ansi
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 x 4 columns]
```
## Simple Moving Averages in Time
Calculate a 1 and 5 minute moving average for Price.
```ipython3
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
```
```myst-ansi
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 x 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.
```ipython3
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
```
```myst-ansi
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.
```ipython3
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
```
```myst-ansi
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.
```ipython3
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
```
```myst-ansi
Time PRICE SIZE COND EXCHANGE STOP_STOCK \
0 2024-01-03 09:30:00.004113329 184.29 300 @ T Q
1 2024-01-03 09:30:00.004115356 184.29 100 @ T Q
2 2024-01-03 09:30:00.004631270 184.22 100 @ K
3 2024-01-03 09:30:00.004634829 184.21 10 @ I K
4 2024-01-03 09:30:00.004637339 184.19 25 @ I K
.. ... ... ... ... ... ...
995 2024-01-03 09:30:01.298814506 184.32 100 @ P
996 2024-01-03 09:30:01.308859960 184.31 20 @ I P
997 2024-01-03 09:30:01.332097998 184.31 13 @ I D
998 2024-01-03 09:30:01.344762538 184.33 2 @F I J
999 2024-01-03 09:30:01.401645194 184.30 9 @ I U
SOURCE TRF TTE TICKER DELETED_TIME TICK_STATUS CORR SEQ_NUM \
0 N 0 AAPL 1969-12-31 19:00:00 0 0 337117
1 N 0 AAPL 1969-12-31 19:00:00 0 0 337118
2 N 0 AAPL 1969-12-31 19:00:00 0 0 337139
3 N 0 AAPL 1969-12-31 19:00:00 0 0 337140
4 N 0 AAPL 1969-12-31 19:00:00 0 0 337141
.. ... .. .. ... ... ... ... ...
995 N 0 AAPL 1969-12-31 19:00:00 0 0 346827
996 N 0 AAPL 1969-12-31 19:00:00 0 0 346967
997 N Q 0 AAPL 1969-12-31 19:00:00 0 0 347134
998 N 1 AAPL 1969-12-31 19:00:00 0 0 347213
999 N 0 AAPL 1969-12-31 19:00:00 0 0 347841
TRADE_ID PARTICIPANT_TIME TRF_TIME \
0 4535 2024-01-03 09:30:00.004088724 1969-12-31 19:00:00.000000000
1 4536 2024-01-03 09:30:00.004088724 1969-12-31 19:00:00.000000000
2 2758 2024-01-03 09:30:00.004373000 1969-12-31 19:00:00.000000000
3 2759 2024-01-03 09:30:00.004373000 1969-12-31 19:00:00.000000000
4 2760 2024-01-03 09:30:00.004373000 1969-12-31 19:00:00.000000000
.. ... ... ...
995 8403 2024-01-03 09:30:01.298472697 1969-12-31 19:00:00.000000000
996 8404 2024-01-03 09:30:01.308517681 1969-12-31 19:00:00.000000000
997 3446 2024-01-03 09:30:01.331386885 2024-01-03 09:30:01.332073963
998 55 2024-01-03 09:30:01.344572000 1969-12-31 19:00:00.000000000
999 329 2024-01-03 09:30:01.401438624 1969-12-31 19:00:00.000000000
OMDSEQ
0 0
1 1
2 2
3 3
4 4
.. ...
995 0
996 0
997 0
998 0
999 0
[1000 rows x 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.
```ipython3
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
```
```myst-ansi
Time MKT_ACTIVITY FIRST_PRICE HIGH_PRICE LOW_PRICE LAST_PRICE \
0 2024-01-04 Ra 69.8800 70.6620 69.4700 69.7020
1 2024-01-04 Rb 69.7600 69.7600 69.7600 69.7600
2 2024-01-04 Rr 70.0000 71.0166 0.8135 69.4800
3 2024-01-04 Rr 13.4000 13.4440 12.8620 12.9080
4 2024-01-04 Ra 12.2500 12.2500 12.2500 12.2500
.. ... ... ... ... ... ...
8 2024-01-04 Rb 292.9667 292.9667 292.9667 292.9667
9 2024-01-04 Rr 293.6000 298.4019 291.4000 297.7000
10 2024-01-04 Ra 2599.5000 2600.0000 29.9927 2580.0080
11 2024-01-04 Rb 2592.6667 2592.6667 30.2825 30.2825
12 2024-01-04 Rr 2572.5000 2600.5000 29.9300 2599.5000
VWAP_PRICE SUM_SIZE TRADE_COUNT SYMBOL_NAME DB_NAME TICK_TYPE
0 69.562057 20056126 62 VOD LSE TRD
1 69.760000 40000 1 VOD LSE TRD
2 70.129743 70105531 6888 VOD LSE TRD
3 13.040510 1771782 5194 AF EURONEXT TRD
4 12.250000 7000 1 DBK XETRA TRD
.. ... ... ... ... ... ...
8 292.966700 6000 1 TSCO LSE TRD
9 296.535342 8377291 5985 TSCO LSE TRD
10 2165.772030 7938453 140 SHEL LSE TRD
11 1128.447157 700 2 SHEL LSE TRD
12 2139.709673 5710843 14101 SHEL LSE TRD
[13 rows x 12 columns]
```