Technical Analysis#
This section contains examples for Technical Analysis 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__'
Aggressor Volume Imbalance#
Aggressor Volume Imbalance from Trade Data.
Calculated for venues that publish AGGRESSOR_SIDE.
BUY_VOLUME is the SIZE where AGGRESSOR_SIDE = ‘B’, SELL_VOLUME where AGGRESSOR_SIDE = ‘S’.
Volume is aggregated into 1-minute buckets.
import onetick.py as otp
# Retrieve trade data for VOD (LSE publishes AGGRESSOR_SIDE)
data = otp.DataSource(db='LSE', tick_type='TRD')
data = data[['SIZE', 'AGGRESSOR_SIDE']]
# Split each trade's size into buy and sell volume based on the aggressor side
data['BUY_VOLUME'] = data.apply(lambda r: r['SIZE'] if r['AGGRESSOR_SIDE'] == 'B' else 0)
data['SELL_VOLUME'] = data.apply(lambda r: r['SIZE'] if r['AGGRESSOR_SIDE'] == 'S' else 0)
# Sum buy, sell and total volume per 1-minute bucket
data = data.agg(
{
'VOLUME': otp.agg.sum('SIZE'),
'BUY_VOLUME': otp.agg.sum('BUY_VOLUME'),
'SELL_VOLUME': otp.agg.sum('SELL_VOLUME'),
},
bucket_interval=otp.Minute(1)
)
# Absolute and percentage imbalance
data['IMBALANCE_VOLUME'] = data['BUY_VOLUME'] - data['SELL_VOLUME']
data['PCNT_IMBALANCE_VOLUME'] = (
100 * (data['BUY_VOLUME'] - data['SELL_VOLUME']) /
(data['BUY_VOLUME'] + data['SELL_VOLUME'])
)
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 8),
end=otp.dt(2024, 1, 3, 16),
timezone='Europe/London',
symbols='VOD'
)
result
| Time | VOLUME | BUY_VOLUME | SELL_VOLUME | IMBALANCE_VOLUME | PCNT_IMBALANCE_VOLUME | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 08:01:00 | 319591 | 24426 | 29489 | -5063 | -9.390708 |
| 1 | 2024-01-03 08:02:00 | 170283 | 125338 | 24915 | 100423 | 66.835937 |
| 2 | 2024-01-03 08:03:00 | 40964 | 5459 | 26581 | -21122 | -65.923845 |
| 3 | 2024-01-03 08:04:00 | 59600 | 0 | 24974 | -24974 | -100.000000 |
| 4 | 2024-01-03 08:05:00 | 120771 | 40161 | 21311 | 18850 | 30.664368 |
| ... | ... | ... | ... | ... | ... | ... |
| 475 | 2024-01-03 15:56:00 | 72860 | 39746 | 14682 | 25064 | 46.049827 |
| 476 | 2024-01-03 15:57:00 | 45149 | 16032 | 26057 | -10025 | -23.818575 |
| 477 | 2024-01-03 15:58:00 | 75597 | 11840 | 59452 | -47612 | -66.784492 |
| 478 | 2024-01-03 15:59:00 | 27675 | 7752 | 4716 | 3036 | 24.350337 |
| 479 | 2024-01-03 16:00:00 | 26166 | 0 | 21029 | -21029 | -100.000000 |
480 rows × 6 columns
Average True Range (ATR)#
Average True Range (ATR) Indicator from Trade Data.
The three candidate ranges use 1-minute HIGH, LOW and the prevailing price at the start of the window (PRICE_N_BACK):
HL = HIGH - LOW
H_PC = abs(HIGH - PRICE_N_BACK)
L_PC = abs(LOW - PRICE_N_BACK)
The True Range (TR) is the maximum of the three, and ATR is the 14-minute moving average of TR.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Rolling 1-minute high, low and prevailing price at the start of the window
data = data.agg(
{
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'PRICE_N_BACK': otp.agg.first('PRICE'),
},
running=True,
bucket_interval=otp.Minute(1),
all_fields=True
)
# The three candidate ranges (H_PC and L_PC are absolute values)
data['HL'] = data['HIGH'] - data['LOW']
data['H_PC'] = data.apply(lambda r: r['HIGH'] - r['PRICE_N_BACK']
if r['HIGH'] - r['PRICE_N_BACK'] >= 0
else r['PRICE_N_BACK'] - r['HIGH'])
data['L_PC'] = data.apply(lambda r: r['LOW'] - r['PRICE_N_BACK']
if r['LOW'] - r['PRICE_N_BACK'] >= 0
else r['PRICE_N_BACK'] - r['LOW'])
# True Range is the maximum of the three candidate ranges
def true_range(r):
if r['HL'] >= r['H_PC'] and r['HL'] >= r['L_PC']:
return r['HL']
if r['H_PC'] >= r['L_PC']:
return r['H_PC']
return r['L_PC']
data['TR'] = data.apply(true_range)
# ATR: 14-minute moving average of the True Range
data = data.agg(
{'ATR': otp.agg.average('TR')},
running=True,
bucket_interval=otp.Minute(14),
all_fields=True
)
data = data[['PRICE', 'HIGH', 'LOW', 'PRICE_N_BACK', 'TR', 'ATR']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | HIGH | LOW | PRICE_N_BACK | TR | ATR | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 50.02 | 50.020 | 50.02 | 0.000 | 0.000000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 50.16 | 50.020 | 50.02 | 0.140 | 0.070000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 50.17 | 50.020 | 50.02 | 0.150 | 0.096667 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 50.17 | 50.020 | 50.02 | 0.150 | 0.110000 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 50.17 | 50.020 | 50.02 | 0.150 | 0.118000 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 50.23 | 50.002 | 50.02 | 0.228 | 0.195606 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50.23 | 50.002 | 50.02 | 0.228 | 0.195639 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 50.23 | 50.002 | 50.02 | 0.228 | 0.195671 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 50.23 | 50.002 | 50.02 | 0.228 | 0.195704 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 50.23 | 50.002 | 50.02 | 0.228 | 0.195736 |
1000 rows × 7 columns
Average True Range (ATR) on 1 Min Bars#
Average True Range (ATR) Indicator from 1 Minute Trade Bars.
Returns the Average True Range (ATR) indicator computed on pre-built 1-minute bars.
The three candidate ranges use the bar HIGH, LOW and the previous bar’s LAST (PRIOR_LAST):
HL = HIGH - LOW
H_PC = abs(HIGH - PRIOR_LAST)
L_PC = abs(LOW - PRIOR_LAST)
The True Range (TR) is the maximum of the three, and ATR is the 14-minute moving average of TR.
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['HIGH', 'LOW', 'LAST']]
# Previous bar's last price (LAST[-1] is the previous bar's LAST)
data['PRIOR_LAST'] = data['LAST'][-1]
# The three candidate ranges (H_PC and L_PC are absolute values)
data['HL'] = data['HIGH'] - data['LOW']
data['H_PC'] = data.apply(lambda r: r['HIGH'] - r['PRIOR_LAST']
if r['HIGH'] - r['PRIOR_LAST'] >= 0
else r['PRIOR_LAST'] - r['HIGH'])
data['L_PC'] = data.apply(lambda r: r['LOW'] - r['PRIOR_LAST']
if r['LOW'] - r['PRIOR_LAST'] >= 0
else r['PRIOR_LAST'] - r['LOW'])
# True Range is the maximum of the three candidate ranges
def true_range(r):
if r['HL'] >= r['H_PC'] and r['HL'] >= r['L_PC']:
return r['HL']
if r['H_PC'] >= r['L_PC']:
return r['H_PC']
return r['L_PC']
data['TR'] = data.apply(true_range)
# ATR: 14-minute moving average of the True Range
data = data.agg(
{'ATR': otp.agg.average('TR')},
running=True,
bucket_interval=otp.Minute(14),
all_fields=True
)
data = data[['LAST', 'HIGH', 'LOW', 'PRIOR_LAST', 'TR', 'ATR']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | HIGH | LOW | PRIOR_LAST | TR | ATR | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 50.2200 | 50.0020 | NaN | 0.2180 | 0.218000 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 50.1750 | 50.0800 | 50.160 | 0.0950 | 0.156500 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 50.0900 | 50.0301 | 50.090 | 0.0599 | 0.124300 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 50.0600 | 50.0000 | 50.040 | 0.0600 | 0.108225 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 50.0400 | 50.0000 | 50.025 | 0.0400 | 0.094580 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 50.5100 | 50.4700 | 50.495 | 0.0400 | 0.043114 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 50.5199 | 50.4700 | 50.475 | 0.0499 | 0.045607 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 50.5300 | 50.4850 | 50.505 | 0.0450 | 0.048107 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 50.5450 | 50.5100 | 50.515 | 0.0350 | 0.045964 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 50.5450 | 50.5100 | 50.520 | 0.0350 | 0.046321 |
389 rows × 7 columns
Bollinger Bands#
Bollinger Bands from Trade Data.
A rolling average and rolling standard deviation are calculated across trade PRICE.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Rolling 5-minute moving average and standard deviation of PRICE
data = data.agg(
{
'MVG_AVG_PRICE': otp.agg.average('PRICE'),
'MVG_STDDEV_PRICE': otp.agg.stddev('PRICE'),
},
running=True,
bucket_interval=otp.Minute(5),
all_fields=True
)
# Upper and Lower Bollinger Bands (2 standard deviations from the moving average)
data['BOLLINGER_UPPER_BAND'] = data['MVG_AVG_PRICE'] + 2 * data['MVG_STDDEV_PRICE']
data['BOLLINGER_LOWER_BAND'] = data['MVG_AVG_PRICE'] - 2 * data['MVG_STDDEV_PRICE']
data = data[['PRICE', 'SIZE', 'MVG_AVG_PRICE', 'BOLLINGER_UPPER_BAND', 'BOLLINGER_LOWER_BAND']]
# 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, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | SIZE | MVG_AVG_PRICE | BOLLINGER_UPPER_BAND | BOLLINGER_LOWER_BAND | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 50.020000 | 50.020000 | 50.020000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 50.090000 | 50.230000 | 49.950000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 50.116667 | 50.253618 | 49.979716 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 50.130000 | 50.257279 | 50.002721 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | 50.130000 | 50.243842 | 50.016158 |
| ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | 50.132580 | 50.231373 | 50.033787 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 50.132567 | 50.231314 | 50.033821 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 50.132555 | 50.231255 | 50.033854 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 50.132542 | 50.231196 | 50.033888 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | 50.132499 | 50.231141 | 50.033858 |
1000 rows × 6 columns
Bollinger Bandwidth#
Bollinger Bandwidth from Trade Data.
A rolling average and rolling standard deviation are calculated across trade PRICE.
The moving window is defined as 5 minutes.
The Upper and Lower Bollinger Bands are calculated at 2 standard deviations from the average.
BOLLINGER_BANDWIDTH is 4 times the MVG_STDDEV_PRICE (the full band width).
PCNT_BOLLINGER_BANDWIDTH expresses the bandwidth as a percentage of the moving average.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Rolling 5-minute moving average and standard deviation of PRICE
data = data.agg(
{
'MVG_AVG_PRICE': otp.agg.average('PRICE'),
'MVG_STDDEV_PRICE': otp.agg.stddev('PRICE'),
},
running=True,
bucket_interval=otp.Minute(5),
all_fields=True
)
# Bollinger Bands, Bandwidth, and Bandwidth as a percentage of the moving average
data['BOLLINGER_UPPER_BAND'] = data['MVG_AVG_PRICE'] + 2 * data['MVG_STDDEV_PRICE']
data['BOLLINGER_LOWER_BAND'] = data['MVG_AVG_PRICE'] - 2 * data['MVG_STDDEV_PRICE']
data['BOLLINGER_BANDWIDTH'] = 4 * data['MVG_STDDEV_PRICE']
data['PCNT_BOLLINGER_BANDWIDTH'] = 100 * (4 * data['MVG_STDDEV_PRICE'] / data['MVG_AVG_PRICE'])
data = data[['PRICE', 'SIZE', 'MVG_AVG_PRICE', 'BOLLINGER_UPPER_BAND', 'BOLLINGER_LOWER_BAND',
'BOLLINGER_BANDWIDTH', 'PCNT_BOLLINGER_BANDWIDTH']]
# 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, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | SIZE | MVG_AVG_PRICE | BOLLINGER_UPPER_BAND | BOLLINGER_LOWER_BAND | BOLLINGER_BANDWIDTH | PCNT_BOLLINGER_BANDWIDTH | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 50.020000 | 50.020000 | 50.020000 | 0.000000 | 0.000000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 50.090000 | 50.230000 | 49.950000 | 0.280000 | 0.558994 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 50.116667 | 50.253618 | 49.979716 | 0.273902 | 0.546528 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 50.130000 | 50.257279 | 50.002721 | 0.254558 | 0.507797 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | 50.130000 | 50.243842 | 50.016158 | 0.227684 | 0.454187 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | 50.132580 | 50.231373 | 50.033787 | 0.197586 | 0.394126 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 50.132567 | 50.231314 | 50.033821 | 0.197493 | 0.393942 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 50.132555 | 50.231255 | 50.033854 | 0.197400 | 0.393757 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 50.132542 | 50.231196 | 50.033888 | 0.197308 | 0.393573 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | 50.132499 | 50.231141 | 50.033858 | 0.197283 | 0.393523 |
1000 rows × 8 columns
Donchian Channels#
Donchian Channels from Trade Data.
The most common period is 20 (here, 20 minutes).
UPPER_CHANNEL is the rolling maximum price over the last 20 minutes.
LOWER_CHANNEL is the rolling minimum price over the last 20 minutes.
MID_CHANNEL is the midpoint between the upper and lower channels.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Rolling 20-minute maximum (upper) and minimum (lower) channels
data = data.agg(
{
'UPPER_CHANNEL': otp.agg.max('PRICE'),
'LOWER_CHANNEL': otp.agg.min('PRICE'),
},
running=True,
bucket_interval=otp.Minute(20),
all_fields=True
)
# Middle channel: midpoint of the upper and lower channels
data['MID_CHANNEL'] = (data['UPPER_CHANNEL'] + data['LOWER_CHANNEL']) / 2
data = data[['PRICE', 'UPPER_CHANNEL', 'LOWER_CHANNEL', 'MID_CHANNEL']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | UPPER_CHANNEL | LOWER_CHANNEL | MID_CHANNEL | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 50.02 | 50.020 | 50.020 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 50.16 | 50.020 | 50.090 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 50.17 | 50.020 | 50.095 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 50.17 | 50.020 | 50.095 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 50.17 | 50.020 | 50.095 |
| ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 50.23 | 50.002 | 50.116 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50.23 | 50.002 | 50.116 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 50.23 | 50.002 | 50.116 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 50.23 | 50.002 | 50.116 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 50.23 | 50.002 | 50.116 |
1000 rows × 5 columns
Donchian Channels on 1 Min Bars#
Donchian Channels from 1 Minute Trade Bars.
The most common period is 20 (here, 20 one-minute bars).
UPPER_CHANNEL is the rolling maximum of the bar HIGH over the last 20 bars.
LOWER_CHANNEL is the rolling minimum of the bar LOW over the last 20 bars.
MID_CHANNEL is the midpoint between the upper and lower channels.
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['HIGH', 'LOW', 'LAST']]
# Rolling 20-bar maximum HIGH (upper) and minimum LOW (lower) channels
data = data.agg(
{
'UPPER_CHANNEL': otp.agg.max('HIGH'),
'LOWER_CHANNEL': otp.agg.min('LOW'),
},
running=True,
bucket_interval=20,
bucket_units='ticks',
all_fields=True
)
# Middle channel: midpoint of the upper and lower channels
data['MID_CHANNEL'] = (data['UPPER_CHANNEL'] + data['LOWER_CHANNEL']) / 2
data = data[['LAST', 'UPPER_CHANNEL', 'LOWER_CHANNEL', 'MID_CHANNEL']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | UPPER_CHANNEL | LOWER_CHANNEL | MID_CHANNEL | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 50.22 | 50.002 | 50.111 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 50.22 | 50.002 | 50.111 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 50.22 | 50.002 | 50.111 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 50.22 | 50.000 | 50.110 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 50.22 | 50.000 | 50.110 |
| ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 50.68 | 50.470 | 50.575 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 50.68 | 50.470 | 50.575 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 50.68 | 50.470 | 50.575 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 50.68 | 50.470 | 50.575 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 50.68 | 50.470 | 50.575 |
389 rows × 5 columns
Liquidity Comparison#
Liquidity Comparison - Bid-Ask Spread Analysis with Order Book Summary.
This example analyzes bid-ask VWAP spreads from order book summary.
Calculates spread in absolute terms and in basis points (bps) relative to mid-price.
import onetick.py as otp
# Get order book summary data from OTQ_CHAIN
# OB_SUMMARY provides best bid/ask prices from order book snapshots
data = otp.DataSource(db='LSE_SAMPLE', tick_type='PRL_FULL')
data = data.ob_summary(bucket_interval=60, max_depth_shares=1000)
data = data[['BID_VWAP', 'ASK_VWAP']]
# Calculate spread metrics
data['SPREAD'] = data['ASK_VWAP'] - data['BID_VWAP']
data['MID'] = (data['ASK_VWAP'] + data['BID_VWAP']) / 2
data['SPREAD_BPS'] = 10000 * data['SPREAD'] / data['MID']
result = otp.run(
data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='UTC',
symbols='HSBA'
)
result
| Time | BID_VWAP | ASK_VWAP | SPREAD | MID | SPREAD_BPS | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 00:01:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 1 | 2024-01-03 00:02:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 2 | 2024-01-03 00:03:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 3 | 2024-01-03 00:04:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 4 | 2024-01-03 00:05:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| ... | ... | ... | ... | ... | ... | ... |
| 1435 | 2024-01-03 23:56:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 1436 | 2024-01-03 23:57:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 1437 | 2024-01-03 23:58:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 1438 | 2024-01-03 23:59:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
| 1439 | 2024-01-04 00:00:00 | 590.55 | 640.0 | 49.45 | 615.275 | 803.70566 |
1440 rows × 6 columns
Market Breadth TRIN Snapshot#
Market Breadth - TRIN Snapshot (Daily).
TRIN < 1.0 = volume favoring advances (bullish).
TRIN > 1.0 = volume favoring declines (bearish).
TRIN = 1.0 = neutral.
This snapshot calculates TRIN for the entire trading day by comparing current price to day’s open price for each symbol, weighted by total volume.
import onetick.py as otp
# Get trade data for multiple symbols
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Aggregate per symbol: get open, close, and total volume for the day
daily_stats = data.agg({
'OPEN_PRICE': otp.agg.first('PRICE'),
'CURRENT_PRICE': otp.agg.last('PRICE'),
'TOTAL_VOLUME': otp.agg.sum('SIZE')
})
# Classify each symbol as advancing or declining
daily_stats['IS_ADVANCING'] = (daily_stats['CURRENT_PRICE'] > daily_stats['OPEN_PRICE']).astype(int)
daily_stats['IS_DECLINING'] = (daily_stats['CURRENT_PRICE'] < daily_stats['OPEN_PRICE']).astype(int)
daily_stats['ADVANCING_VOLUME'] = daily_stats['TOTAL_VOLUME'] * (daily_stats['CURRENT_PRICE'] > daily_stats['OPEN_PRICE']).astype(int)
daily_stats['DECLINING_VOLUME'] = daily_stats['TOTAL_VOLUME'] * (daily_stats['CURRENT_PRICE'] < daily_stats['OPEN_PRICE']).astype(int)
# Merge per-symbol stats across all symbols for TRIN calculation
daily_stats = otp.merge([daily_stats], separate_db_name=True, identify_input_ts=True,
symbols=['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN', 'NVDA', 'META', 'JPM'])
# Aggregate across all symbols to calculate TRIN
trin_result = daily_stats.agg({
'ADVANCING_SYMBOLS': otp.agg.sum('IS_ADVANCING'),
'declining_symbols': otp.agg.sum('IS_DECLINING'),
'ADVANCING_VOLUME': otp.agg.sum('ADVANCING_VOLUME'),
'DECLINING_VOLUME': otp.agg.sum('DECLINING_VOLUME')
})
# Calculate TRIN: (advancing symbols / declining symbols) / (advancing volume / declining volume)
trin_result['trin'] = (
(trin_result['ADVANCING_SYMBOLS'] / trin_result['declining_symbols']) /
(trin_result['ADVANCING_VOLUME'] / trin_result['DECLINING_VOLUME'])
)
result = otp.run(
trin_result,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 30),
timezone='America/New_York'
)
result
| Time | ADVANCING_SYMBOLS | declining_symbols | ADVANCING_VOLUME | DECLINING_VOLUME | trin | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:30:00 | 3 | 5 | 86246054 | 266398168 | 1.853289 |
Market Breadth TRIN Time-Series#
Market Breadth - TRIN Time-Series (5-Minute Candles).
TRIN < 1.0 = volume favoring advances (bullish).
TRIN > 1.0 = volume favoring declines (bearish).
This time-series tracks how market breadth evolves throughout the day for each 5-minute candle, symbols are classified as advancing or declining based on comparison of their close to the previous candle’s close.
import onetick.py as otp
# Get trade data for multiple symbols
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Create 5-minute candles: get close and volume per symbol per candle
candles = data.agg({
'CLOSE_PRICE': otp.agg.last('PRICE'),
'TOTAL_VOLUME': otp.agg.sum('SIZE')
}, bucket_interval=300)
# Calculate previous candle's close for each symbol
candles['LAST_CLOSE'] = candles['CLOSE_PRICE'][-1]
# Filter to only candles with a previous close
candles = candles.dropna()
# Classify each symbol as advancing or declining
candles['IS_ADVANCING'] = (candles['CLOSE_PRICE'] > candles['LAST_CLOSE']).astype(int)
candles['IS_DECLINING'] = (candles['CLOSE_PRICE'] < candles['LAST_CLOSE']).astype(int)
candles['ADVANCING_VOLUME'] = candles['TOTAL_VOLUME'] * (candles['CLOSE_PRICE'] > candles['LAST_CLOSE']).astype(int)
candles['DECLINING_VOLUME'] = candles['TOTAL_VOLUME'] * (candles['CLOSE_PRICE'] < candles['LAST_CLOSE']).astype(int)
# Merge candles across all symbols for TRIN calculation
candles = otp.merge([candles], separate_db_name=True, identify_input_ts=True,
symbols=['AAPL', 'MSFT', 'GOOGL', 'TSLA', 'AMZN', 'NVDA', 'META', 'JPM'])
# Aggregate across all symbols per timestamp to calculate TRIN time-series
trin_timeseries = candles.agg({
'ADVANCING_SYMBOLS': otp.agg.sum('IS_ADVANCING'),
'DECLINING_SYMBOLS': otp.agg.sum('IS_DECLINING'),
'ADVANCING_VOLUME': otp.agg.sum('ADVANCING_VOLUME'),
'DECLINING_VOLUME': otp.agg.sum('DECLINING_VOLUME')
}, bucket_interval=300)
# Calculate TRIN: (advancing symbols / declining symbols) / (advancing volume / declining volume)
trin_timeseries['trin'] = (trin_timeseries['ADVANCING_SYMBOLS'] / trin_timeseries['DECLINING_SYMBOLS']) / \
(trin_timeseries['ADVANCING_VOLUME'] / trin_timeseries['DECLINING_VOLUME'])
result = otp.run(
trin_timeseries,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 30),
timezone='America/New_York'
)
result
| Time | ADVANCING_SYMBOLS | DECLINING_SYMBOLS | ADVANCING_VOLUME | DECLINING_VOLUME | trin | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:35:00 | 0 | 0 | 0 | 0 | NaN |
| 1 | 2024-01-03 09:40:00 | 3 | 5 | 5234260 | 4223485 | 0.484135 |
| 2 | 2024-01-03 09:45:00 | 6 | 2 | 3250791 | 4637772 | 4.279979 |
| 3 | 2024-01-03 09:50:00 | 3 | 5 | 2505300 | 4913899 | 1.176841 |
| 4 | 2024-01-03 09:55:00 | 1 | 7 | 312205 | 5940112 | 2.718046 |
| ... | ... | ... | ... | ... | ... | ... |
| 79 | 2024-01-03 16:10:00 | 4 | 4 | 1107741 | 505165 | 0.456032 |
| 80 | 2024-01-03 16:15:00 | 4 | 2 | 1535165 | 62848 | 0.081878 |
| 81 | 2024-01-03 16:20:00 | 3 | 5 | 16311 | 33108 | 1.217878 |
| 82 | 2024-01-03 16:25:00 | 5 | 3 | 105467 | 6075 | 0.096002 |
| 83 | 2024-01-03 16:30:00 | 3 | 5 | 1243 | 29102 | 14.047627 |
84 rows × 6 columns
Maximum Drawdown (MDD)#
Maximum Drawdown (MDD %) from Trade Data.
The running high price is tracked from the start of the period.
PCNT_DRAWDOWN is the percentage difference between the current price and the running high:
PCNT_DRAWDOWN = 100 * (PRICE - RUNNING_HIGH_PRICE) / RUNNING_HIGH_PRICE
MAX_PCNT_DRAWDOWN is the running minimum of PCNT_DRAWDOWN (the largest drawdown so far).
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Running high price from the start of the period (cumulative running max)
data = data.agg(
{'RUNNING_HIGH_PRICE': otp.agg.max('PRICE')},
running=True,
all_fields=True
)
# Percentage drawdown from the running high
data['PCNT_DRAWDOWN'] = 100 * (data['PRICE'] - data['RUNNING_HIGH_PRICE']) / data['RUNNING_HIGH_PRICE']
# Maximum drawdown: running minimum of the percentage drawdown
data = data.agg(
{'MAX_PCNT_DRAWDOWN': otp.agg.min('PCNT_DRAWDOWN')},
running=True,
all_fields=True
)
data = data[['PRICE', 'RUNNING_HIGH_PRICE', 'PCNT_DRAWDOWN', 'MAX_PCNT_DRAWDOWN']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | RUNNING_HIGH_PRICE | PCNT_DRAWDOWN | MAX_PCNT_DRAWDOWN | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 50.02 | 0.000000 | 0.000000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 50.16 | 0.000000 | 0.000000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 50.17 | 0.000000 | 0.000000 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 50.17 | 0.000000 | 0.000000 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 50.17 | -0.079729 | -0.079729 |
| ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 50.23 | -0.218993 | -0.334861 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50.23 | -0.218993 | -0.334861 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 50.23 | -0.218993 | -0.334861 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 50.23 | -0.218993 | -0.334861 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 50.23 | -0.278718 | -0.334861 |
1000 rows × 5 columns
Maximum Drawdown (MDD) on 1 Min Bars#
Maximum Drawdown (MDD %) from 1 Minute Trade Bars.
The running high price is tracked from the start of the period using the bar HIGH.
PCNT_DRAWDOWN is the percentage difference between the current bar LAST and the running high:
PCNT_DRAWDOWN = 100 * (LAST - RUNNING_HIGH_PRICE) / RUNNING_HIGH_PRICE
MAX_PCNT_DRAWDOWN is the running minimum of PCNT_DRAWDOWN (the largest drawdown so far).
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['HIGH', 'LAST']]
# Running high price from the start of the period (cumulative running max of the bar HIGH)
data = data.agg(
{'RUNNING_HIGH_PRICE': otp.agg.max('HIGH')},
running=True,
all_fields=True
)
# Percentage drawdown from the running high
data['PCNT_DRAWDOWN'] = 100 * (data['LAST'] - data['RUNNING_HIGH_PRICE']) / data['RUNNING_HIGH_PRICE']
# Maximum drawdown: running minimum of the percentage drawdown
data = data.agg(
{'MAX_PCNT_DRAWDOWN': otp.agg.min('PCNT_DRAWDOWN')},
running=True,
all_fields=True
)
data = data[['LAST', 'RUNNING_HIGH_PRICE', 'PCNT_DRAWDOWN', 'MAX_PCNT_DRAWDOWN']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | RUNNING_HIGH_PRICE | PCNT_DRAWDOWN | MAX_PCNT_DRAWDOWN | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 50.22 | -0.119474 | -0.119474 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 50.22 | -0.258861 | -0.258861 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 50.22 | -0.358423 | -0.358423 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 50.22 | -0.388292 | -0.388292 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 50.22 | -0.398248 | -0.398248 |
| ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 50.68 | -0.404499 | -0.556439 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 50.68 | -0.345304 | -0.556439 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 50.68 | -0.325572 | -0.556439 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 50.68 | -0.315706 | -0.556439 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 50.68 | -0.295975 | -0.556439 |
389 rows × 5 columns
On-Balance Volume (OBV)#
On-Balance Volume (OBV) from Trade Data.
Retrieves the PRICE, SIZE and PRIOR_PRICE (the previous tick’s price, PRICE[-1]).
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Previous price (PRICE[-1] is the previous tick's price)
data['PRIOR_PRICE'] = data['PRICE'][-1]
# Signed volume based on price direction
def signed_size(r):
if r['PRICE'] > r['PRIOR_PRICE']:
return r['SIZE']
if r['PRICE'] < r['PRIOR_PRICE']:
return -r['SIZE']
return 0
data['SIGNED_SIZE'] = data.apply(signed_size)
# On-Balance Volume: cumulative sum of signed volume
# running=True with no bucket_interval accumulates from query start to each tick
data = data.agg(
{'OBV': otp.agg.sum('SIGNED_SIZE')},
running=True,
all_fields=True
)
data = data[['PRICE', 'SIZE', 'SIGNED_SIZE', 'OBV']]
# 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, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | SIZE | SIGNED_SIZE | OBV | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 2 | 2 | 2 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 3 | 3 | 5 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 100 | 100 | 105 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 5 | 0 | 105 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 46 | -46 | 59 |
| ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 2 | -2 | 261522 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50 | 0 | 261522 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 100 | 0 | 261522 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 100 | 0 | 261522 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1 | -1 | 261521 |
1000 rows × 5 columns
On-Balance Volume (OBV) on 1 Min Bars#
On-Balance Volume (OBV) from 1 Minute Trade Bars.
Retrieves the bar LAST, VOLUME and the previous bar’s LAST (PRIOR_LAST, LAST[-1]).
SIGNED_VOLUME is +VOLUME when LAST > PRIOR_LAST, -VOLUME when LAST < PRIOR_LAST, else 0.
OBV is the cumulative sum of SIGNED_VOLUME across the bars.
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST', 'VOLUME']]
# Previous bar's last price (LAST[-1] is the previous bar's LAST)
data['PRIOR_LAST'] = data['LAST'][-1]
# Signed volume based on the bar-over-bar price direction
def signed_volume(r):
if r['LAST'] > r['PRIOR_LAST']:
return r['VOLUME']
if r['LAST'] < r['PRIOR_LAST']:
return -r['VOLUME']
return 0
data['SIGNED_VOLUME'] = data.apply(signed_volume)
# On-Balance Volume: cumulative sum of signed volume
# running=True with no bucket_interval accumulates from query start to each bar
data = data.agg(
{'OBV': otp.agg.sum('SIGNED_VOLUME')},
running=True,
all_fields=True
)
data = data[['LAST', 'VOLUME', 'SIGNED_VOLUME', 'OBV']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | VOLUME | SIGNED_VOLUME | OBV | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 69657 | 69657 | 69657 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 39792 | -39792 | 29865 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 26904 | -26904 | 2961 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 32673 | -32673 | -29712 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 32783 | -32783 | -62495 |
| ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 169383 | -169383 | 872242 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 185335 | 185335 | 1057577 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 166268 | 166268 | 1223845 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 346983 | 346983 | 1570828 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 166368 | 166368 | 1737196 |
389 rows × 5 columns
Order Flow Imbalance (OFI)#
Order Flow Imbalance (OFI) from Quote Data.
Order Flow Imbalance (OFI) measures the net change in supply and demand at the
best bid and ask across successive quotes (Cont, Kukanov & Stoikov, 2014).
As US_COMP is a composite, the NBBO tick type is used rather than QTE.
import onetick.py as otp
# Retrieve NBBO quote data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='NBBO')
data = data[['BID_PRICE', 'BID_SIZE', 'ASK_PRICE', 'ASK_SIZE']]
# Prior bid and ask prices / sizes (col[-1] is the previous tick's value)
data['PRIOR_BID_PRICE'] = data['BID_PRICE'][-1]
data['PRIOR_BID_SIZE'] = data['BID_SIZE'][-1]
data['PRIOR_ASK_PRICE'] = data['ASK_PRICE'][-1]
data['PRIOR_ASK_SIZE'] = data['ASK_SIZE'][-1]
# Bid flow
def bid_flow(r):
if r['BID_PRICE'] > r['PRIOR_BID_PRICE']:
return r['BID_SIZE']
if r['BID_PRICE'] < r['PRIOR_BID_PRICE']:
return r['PRIOR_BID_SIZE']
return r['BID_SIZE'] - r['PRIOR_BID_SIZE']
data['BID_FLOW'] = data.apply(bid_flow)
# Ask flow
def ask_flow(r):
if r['ASK_PRICE'] > r['PRIOR_ASK_PRICE']:
return r['ASK_SIZE']
if r['ASK_PRICE'] < r['PRIOR_ASK_PRICE']:
return r['PRIOR_ASK_SIZE']
return r['ASK_SIZE'] - r['PRIOR_ASK_SIZE']
data['ASK_FLOW'] = data.apply(ask_flow)
# Order Flow Imbalance
data['OFI'] = data['BID_FLOW'] - data['ASK_FLOW']
data = data[['BID_PRICE', 'ASK_PRICE', 'BID_FLOW', 'ASK_FLOW', 'OFI']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | BID_PRICE | ASK_PRICE | BID_FLOW | ASK_FLOW | OFI | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.001830617 | 50.00 | 50.18 | 9 | 2 | 7 |
| 1 | 2024-01-03 09:30:00.002215206 | 50.00 | 50.18 | 0 | 0 | 0 |
| 2 | 2024-01-03 09:30:00.002524728 | 50.00 | 50.18 | 0 | 1 | -1 |
| 3 | 2024-01-03 09:30:00.003295288 | 50.00 | 50.18 | 1 | 0 | 1 |
| 4 | 2024-01-03 09:30:00.010072539 | 50.00 | 50.18 | 0 | 1 | -1 |
| ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:03.909674185 | 50.18 | 50.20 | 0 | 0 | 0 |
| 996 | 2024-01-03 09:30:03.909758379 | 50.18 | 50.19 | 0 | 21 | -21 |
| 997 | 2024-01-03 09:30:03.909883162 | 50.18 | 50.20 | 0 | 21 | -21 |
| 998 | 2024-01-03 09:30:03.910223796 | 50.18 | 50.20 | 0 | 0 | 0 |
| 999 | 2024-01-03 09:30:03.910328511 | 50.18 | 50.20 | 0 | 0 | 0 |
1000 rows × 6 columns
Realized Volatility#
Realized Volatility (RV) from Trade Data.
Trades are first bucketed into 1-minute periods, keeping the last price of each period.
Log returns are calculated as LOG(LAST_PRICE / previous LAST_PRICE).
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Divide into 1-minute periods, keeping the last price of each minute
data = data.agg({'LAST_PRICE': otp.agg.last('PRICE')}, bucket_interval=otp.Minute(1))
# Previous minute's last price and the log return
# (LAST_PRICE[-1] is the previous minute's last price)
data['PRIOR_LAST_PRICE'] = data['LAST_PRICE'][-1]
data['LOG_RETURN'] = otp.math.log(data['LAST_PRICE'] / data['PRIOR_LAST_PRICE'])
# Rolling 30-minute standard deviation of the log returns
data = data.agg(
{'ROLLING_STDDEV_LOG_RETURN': otp.agg.stddev('LOG_RETURN')},
running=True,
bucket_interval=otp.Minute(30),
all_fields=True
)
# Annualize: 252 trading days * 13 thirty-minute periods per day
data['ANNUALIZED_RV'] = data['ROLLING_STDDEV_LOG_RETURN'] * 252 * 13
data = data[['LOG_RETURN', 'ROLLING_STDDEV_LOG_RETURN', 'ANNUALIZED_RV']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LOG_RETURN | ROLLING_STDDEV_LOG_RETURN | ANNUALIZED_RV | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | NaN | NaN | NaN |
| 1 | 2024-01-03 09:32:00 | -0.001397 | 0.000000 | 0.000000 |
| 2 | 2024-01-03 09:33:00 | -0.000999 | 0.000199 | 0.651608 |
| 3 | 2024-01-03 09:34:00 | -0.000300 | 0.000453 | 1.485066 |
| 4 | 2024-01-03 09:35:00 | -0.000100 | 0.000523 | 1.713689 |
| ... | ... | ... | ... | ... |
| 385 | 2024-01-03 15:56:00 | 0.000691 | 0.000535 | 1.752873 |
| 386 | 2024-01-03 15:57:00 | 0.000198 | 0.000534 | 1.748094 |
| 387 | 2024-01-03 15:58:00 | 0.000099 | 0.000525 | 1.718966 |
| 388 | 2024-01-03 15:59:00 | 0.000198 | 0.000525 | 1.721012 |
| 389 | 2024-01-03 16:00:00 | -0.000198 | 0.000507 | 1.662270 |
390 rows × 4 columns
Realized Volatility on 1 Min Bars#
Realized Volatility (RV) from 1 Minute Trade Bars.
Log returns are calculated on the bar LAST as LOG(LAST / previous LAST).
A 30-minute rolling standard deviation of the log returns is calculated.
The result is annualized by multiplying by the number of trading days (252)
and the number of 30-minute periods in a trading day (13).
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST']]
# Previous bar's last price and the log return (LAST[-1] is the previous bar's LAST)
data['PRIOR_LAST'] = data['LAST'][-1]
data['LOG_RETURN'] = otp.math.log(data['LAST'] / data['PRIOR_LAST'])
# Rolling 30-minute standard deviation of the log returns
data = data.agg(
{'ROLLING_STDDEV_LOG_RETURN': otp.agg.stddev('LOG_RETURN')},
running=True,
bucket_interval=otp.Minute(30),
all_fields=True
)
# Annualize: 252 trading days * 13 thirty-minute periods per day
data['ANNUALIZED_RV'] = data['ROLLING_STDDEV_LOG_RETURN'] * 252 * 13
data = data[['LOG_RETURN', 'ROLLING_STDDEV_LOG_RETURN', 'ANNUALIZED_RV']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LOG_RETURN | ROLLING_STDDEV_LOG_RETURN | ANNUALIZED_RV | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | NaN | NaN | NaN |
| 1 | 2024-01-03 09:32:00 | -0.001397 | 0.000000 | 0.000000 |
| 2 | 2024-01-03 09:33:00 | -0.000999 | 0.000199 | 0.651608 |
| 3 | 2024-01-03 09:34:00 | -0.000300 | 0.000453 | 1.485066 |
| 4 | 2024-01-03 09:35:00 | -0.000100 | 0.000523 | 1.713689 |
| ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | -0.000396 | 0.000485 | 1.588239 |
| 385 | 2024-01-03 15:56:00 | 0.000594 | 0.000495 | 1.623197 |
| 386 | 2024-01-03 15:57:00 | 0.000198 | 0.000494 | 1.618035 |
| 387 | 2024-01-03 15:58:00 | 0.000099 | 0.000485 | 1.589154 |
| 388 | 2024-01-03 15:59:00 | 0.000198 | 0.000486 | 1.592090 |
389 rows × 4 columns
Rate of Change (ROC)#
Rate of Change (ROC) Indicator from Trade Data.
Returns the Rate of Change (ROC) indicator over a lookback period of 7 seconds.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Prevailing price at the start of the 7-second window
data = data.agg(
{'PRICE_N_BACK': otp.agg.first('PRICE')},
running=True,
bucket_interval=otp.Second(7),
all_fields=True
)
# Rate of Change
data['ROC'] = 100 * (data['PRICE'] - data['PRICE_N_BACK']) / data['PRICE_N_BACK']
data = data[['PRICE', 'PRICE_N_BACK', 'ROC']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | PRICE_N_BACK | ROC | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 50.02 | 0.000000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 50.02 | 0.279888 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 50.02 | 0.299880 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 50.02 | 0.299880 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 50.02 | 0.219912 |
| ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 50.01 | 0.219956 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50.01 | 0.219956 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 50.01 | 0.219956 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 50.01 | 0.219956 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 50.01 | 0.159968 |
1000 rows × 4 columns
Rate of Change (ROC) on 1 Min Bars#
Rate of Change (ROC) Indicator from 1 Minute Trade Bars.
Returns the Rate of Change (ROC) indicator over a lookback of 7 one-minute bars.
LAST_N_BACK is the bar LAST 7 bars earlier.
ROC = 100 * (LAST - LAST_N_BACK) / LAST_N_BACK
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST']]
# Bar LAST 7 bars earlier (the first LAST in a running 7-bar window)
data = data.agg(
{'LAST_N_BACK': otp.agg.first('LAST')},
running=True,
bucket_interval=7,
bucket_units='ticks',
all_fields=True
)
# Rate of Change
data['ROC'] = 100 * (data['LAST'] - data['LAST_N_BACK']) / data['LAST_N_BACK']
data = data[['LAST', 'LAST_N_BACK', 'ROC']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | LAST_N_BACK | ROC | |
|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 50.160 | 0.000000 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 50.160 | -0.139553 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 50.160 | -0.239234 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 50.160 | -0.269139 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 50.160 | -0.279107 |
| ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 50.655 | -0.355345 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 50.600 | -0.187747 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 50.525 | -0.019792 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 50.510 | 0.019798 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 50.540 | -0.019786 |
389 rows × 4 columns
Rolling Stddev#
Rolling Standard Deviation from Trade Data.
Returns the Rolling Standard Deviation of trade PRICE.
The period is defined as 5 minutes.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Rolling 5-minute standard deviation of PRICE
data = data.agg(
{'ROLLING_STDDEV_PRICE': otp.agg.stddev('PRICE')},
running=True,
bucket_interval=otp.Minute(5),
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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | ROLLING_STDDEV_PRICE | |
|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 0.000000 |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 0.070000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 0.068475 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 0.063640 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 0.056921 |
| ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 0.049396 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 0.049373 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 0.049350 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 0.049327 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 0.049321 |
1000 rows × 3 columns
Rolling Stddev on 1 Min Bars#
Rolling Standard Deviation from 1 Minute Trade Bars.
Returns the rolling standard deviation of the bar LAST price.
The period is defined as 5 one-minute bars.
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST']]
# Rolling 5-bar standard deviation of the bar LAST price
data = data.agg(
{'ROLLING_STDDEV_PRICE': otp.agg.stddev('LAST')},
running=True,
bucket_interval=5,
bucket_units='ticks',
all_fields=True
)
data = data[['LAST', 'ROLLING_STDDEV_PRICE']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | ROLLING_STDDEV_PRICE | |
|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 0.000000 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 0.035000 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 0.049216 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 0.052723 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 0.052688 |
| ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 0.022672 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 0.021213 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 0.021541 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 0.016000 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 0.018815 |
389 rows × 3 columns
RSI#
RSI Indicator from Trade Data.
Returns the RSI together with the RS (Average Gain over Average Loss).
The previous tick’s PRICE (PRICE[-1]) is used to calculate the change in price.
The change is separated into a GAIN (positive changes) and a LOSS (absolute of negative changes).
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Previous price and change in price (PRICE[-1] is the previous tick's price)
data['PRIOR_PRICE'] = data['PRICE'][-1]
data['CHANGE_PRICE'] = data['PRICE'] - data['PRIOR_PRICE']
# Separate the change into gains and losses
data['GAIN'] = data.apply(lambda r: r['CHANGE_PRICE'] if r['CHANGE_PRICE'] > 0 else 0)
data['LOSS'] = data.apply(lambda r: -r['CHANGE_PRICE'] if r['CHANGE_PRICE'] < 0 else 0)
# Rolling 14-minute average gain and loss
data = data.agg(
{
'MVG_AVG_GAIN': otp.agg.average('GAIN'),
'MVG_AVG_LOSS': otp.agg.average('LOSS'),
},
running=True,
bucket_interval=otp.Minute(14),
all_fields=True
)
# RS and RSI
data['RS'] = data['MVG_AVG_GAIN'] / data['MVG_AVG_LOSS']
data['RSI'] = 100 - (100 / (1 + data['RS']))
data = data[['PRICE', 'RS', 'RSI', 'MVG_AVG_GAIN', 'MVG_AVG_LOSS']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | RS | RSI | MVG_AVG_GAIN | MVG_AVG_LOSS | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | NaN | NaN | 0.000000 | NaN |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | inf | 100.000000 | 0.070000 | 0.000000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | inf | 100.000000 | 0.050000 | 0.000000 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | inf | 100.000000 | 0.037500 | 0.000000 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 3.000000 | 75.000000 | 0.030000 | 0.010000 |
| ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 1.012996 | 50.322792 | 0.007265 | 0.007172 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 1.012997 | 50.322817 | 0.007258 | 0.007165 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 1.012998 | 50.322842 | 0.007250 | 0.007157 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 1.012999 | 50.322867 | 0.007243 | 0.007150 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 1.008759 | 50.218013 | 0.007236 | 0.007173 |
1000 rows × 6 columns
RSI on 1 Min Bars#
RSI Indicator from 1 Minute Trade Bars.
Returns the RSI together with the RS (Average Gain over Average Loss).
The previous bar’s LAST (LAST[-1]) is used to calculate the change in price.
The change is separated into a GAIN (positive changes) and a LOSS (absolute of negative changes).
The gains and losses are averaged across a rolling 14-minute period.
RS = MVG_AVG_GAIN / MVG_AVG_LOSS
RSI = 100 - (100 / (1 + RS))
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST']]
# Previous bar's last price and the change in price (LAST[-1] is the previous bar's LAST)
data['PRIOR_LAST'] = data['LAST'][-1]
data['CHANGE_LAST'] = data['LAST'] - data['PRIOR_LAST']
# Separate the change into gains and losses
data['GAIN'] = data.apply(lambda r: r['CHANGE_LAST'] if r['CHANGE_LAST'] > 0 else 0)
data['LOSS'] = data.apply(lambda r: -r['CHANGE_LAST'] if r['CHANGE_LAST'] < 0 else 0)
# Rolling 14-minute average gain and loss
data = data.agg(
{
'MVG_AVG_GAIN': otp.agg.average('GAIN'),
'MVG_AVG_LOSS': otp.agg.average('LOSS'),
},
running=True,
bucket_interval=otp.Minute(14),
all_fields=True
)
# RS and RSI
data['RS'] = data['MVG_AVG_GAIN'] / data['MVG_AVG_LOSS']
data['RSI'] = 100 - (100 / (1 + data['RS']))
data = data[['LAST', 'RS', 'RSI', 'MVG_AVG_GAIN', 'MVG_AVG_LOSS']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | RS | RSI | MVG_AVG_GAIN | MVG_AVG_LOSS | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | NaN | NaN | 0.000000 | NaN |
| 1 | 2024-01-03 09:32:00 | 50.090 | 0.000000 | 0.000000 | 0.000000 | 0.070000 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 0.000000 | 0.000000 | 0.000000 | 0.060000 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 0.000000 | 0.000000 | 0.000000 | 0.045000 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 0.000000 | 0.000000 | 0.000000 | 0.035000 |
| ... | ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 0.373379 | 27.186910 | 0.006171 | 0.016529 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 0.487900 | 32.791170 | 0.008064 | 0.016529 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 0.539272 | 35.034208 | 0.008779 | 0.016279 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 0.573800 | 36.459521 | 0.009136 | 0.015921 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 0.641395 | 39.076226 | 0.009850 | 0.015357 |
389 rows × 6 columns
Stochastic Oscillator#
Stochastic Oscillator from Trade Data.
Uses a 14-minute rolling Minimum to calculate MLOW (lowest price traded in the period).
Uses a 14-minute rolling Maximum to calculate MHIGH (highest price traded in the period).
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE']]
# Rolling 14-minute minimum (MLOW) and maximum (MHIGH) prices
data = data.agg(
{
'MLOW': otp.agg.min('PRICE'),
'MHIGH': otp.agg.max('PRICE'),
},
running=True,
bucket_interval=otp.Minute(14),
all_fields=True
)
# %K: position of the current price within the 14-minute high/low range
data['PCNT_K'] = 100 * (data['PRICE'] - data['MLOW']) / (data['MHIGH'] - data['MLOW'])
# %D: 3-minute moving average of %K
data = data.agg(
{'PCNT_D': otp.agg.average('PCNT_K')},
running=True,
bucket_interval=otp.Minute(3),
all_fields=True
)
data = data[['PRICE', 'MLOW', 'MHIGH', 'PCNT_K', 'PCNT_D']]
# 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, 16, 30),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | MLOW | MHIGH | PCNT_K | PCNT_D | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.065443591 | 50.02 | 50.020 | 50.02 | NaN | NaN |
| 1 | 2024-01-03 09:30:00.111130049 | 50.16 | 50.020 | 50.16 | 100.000000 | 100.000000 |
| 2 | 2024-01-03 09:30:00.127459523 | 50.17 | 50.020 | 50.17 | 100.000000 | 100.000000 |
| 3 | 2024-01-03 09:30:00.128498068 | 50.17 | 50.020 | 50.17 | 100.000000 | 100.000000 |
| 4 | 2024-01-03 09:30:00.135190071 | 50.13 | 50.020 | 50.17 | 73.333333 | 93.333333 |
| ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:30:07.378682043 | 50.12 | 50.002 | 50.23 | 51.754386 | 66.419446 |
| 996 | 2024-01-03 09:30:07.378683771 | 50.12 | 50.002 | 50.23 | 51.754386 | 66.404722 |
| 997 | 2024-01-03 09:30:07.379088757 | 50.12 | 50.002 | 50.23 | 51.754386 | 66.390028 |
| 998 | 2024-01-03 09:30:07.390014965 | 50.12 | 50.002 | 50.23 | 51.754386 | 66.375363 |
| 999 | 2024-01-03 09:30:07.396341167 | 50.09 | 50.002 | 50.23 | 38.596491 | 66.347556 |
1000 rows × 6 columns
Stochastic Oscillator on 1 Min Bars#
Stochastic Oscillator from 1 Minute Trade Bars.
Uses a 14-bar rolling minimum of the bar LOW to calculate MLOW (lowest price in the period).
Uses a 14-bar rolling maximum of the bar HIGH to calculate MHIGH (highest price in the period).
%K = 100 * (LAST - MLOW) / (MHIGH - MLOW)
%D = 3-minute moving average of %K
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['HIGH', 'LOW', 'LAST']]
# Rolling 14-bar minimum LOW (MLOW) and maximum HIGH (MHIGH)
data = data.agg(
{
'MLOW': otp.agg.min('LOW'),
'MHIGH': otp.agg.max('HIGH'),
},
running=True,
bucket_interval=14,
bucket_units='ticks',
all_fields=True
)
# %K: position of the current bar LAST within the 14-bar high/low range
data['PCNT_K'] = 100 * (data['LAST'] - data['MLOW']) / (data['MHIGH'] - data['MLOW'])
# %D: 3-minute moving average of %K
data = data.agg(
{'PCNT_D': otp.agg.average('PCNT_K')},
running=True,
bucket_interval=otp.Minute(3),
all_fields=True
)
data = data[['LAST', 'MLOW', 'MHIGH', 'PCNT_K', 'PCNT_D']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | MLOW | MHIGH | PCNT_K | PCNT_D | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 50.002 | 50.22 | 72.477064 | 72.477064 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 50.002 | 50.22 | 40.366972 | 56.422018 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 50.002 | 50.22 | 17.431193 | 43.425076 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 50.000 | 50.22 | 11.363636 | 23.053934 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 50.000 | 50.22 | 9.090909 | 12.628579 |
| ... | ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 50.470 | 50.68 | 2.380952 | 9.078251 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 50.470 | 50.68 | 16.666667 | 7.226399 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 50.470 | 50.68 | 21.428571 | 13.492063 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 50.470 | 50.68 | 23.809524 | 20.634921 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 50.470 | 50.68 | 28.571429 | 24.603175 |
389 rows × 6 columns
Volume Bars#
Volume Bars (Fixed Volume Bins) from Trade Data.
The cumulative volume is calculated across the period and floored into fixed-size bins:
VOL_BIN = FLOOR(cumulative volume / 100000) (a new bar every 100,000 shares)
The day is then aggregated grouped by VOL_BIN, producing OHLC, count and volume per bar.
Because bars span different time ranges, the start and end time of each bar are also returned.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Cumulative volume, then floor into fixed 100,000-share volume bins
data = data.agg({'ACC_VOLUME': otp.agg.sum('SIZE')}, running=True, all_fields=True)
data['VOL_BIN'] = otp.math.floor(data['ACC_VOLUME'] / 100000)
# Aggregate each volume bin into an OHLC bar
data = data.agg(
{
'BIN_START': otp.agg.first('Time'),
'BIN_END': otp.agg.last('Time'),
'FIRST': otp.agg.first('PRICE'),
'HIGH': otp.agg.max('PRICE'),
'LOW': otp.agg.min('PRICE'),
'LAST': otp.agg.last('PRICE'),
'TRADE_COUNT': otp.agg.count(),
'VOLUME': otp.agg.sum('SIZE'),
},
group_by=['VOL_BIN']
)
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | VOL_BIN | FIRST | HIGH | LOW | LAST | TRADE_COUNT | VOLUME | BIN_START | BIN_END | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 0.0 | 50.020 | 50.170 | 50.002 | 50.0850 | 75 | 1785 | 2024-01-03 09:30:00.065443591 | 2024-01-03 09:30:00.801003556 |
| 1 | 2024-01-03 16:00:00 | 2.0 | 50.090 | 50.090 | 50.090 | 50.0900 | 1 | 262623 | 2024-01-03 09:30:00.887879969 | 2024-01-03 09:30:00.887879969 |
| 2 | 2024-01-03 16:00:00 | 5.0 | 50.090 | 50.230 | 50.050 | 50.1500 | 1654 | 335376 | 2024-01-03 09:30:00.888066374 | 2024-01-03 09:30:29.305168688 |
| 3 | 2024-01-03 16:00:00 | 6.0 | 50.160 | 50.190 | 50.050 | 50.0500 | 1162 | 100177 | 2024-01-03 09:30:29.305775276 | 2024-01-03 09:32:55.319732421 |
| 4 | 2024-01-03 16:00:00 | 7.0 | 50.050 | 50.100 | 49.990 | 49.9900 | 1084 | 99354 | 2024-01-03 09:32:55.319733020 | 2024-01-03 09:35:19.495008777 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 143 | 2024-01-03 16:00:00 | 146.0 | 50.550 | 50.555 | 50.540 | 50.5500 | 431 | 99006 | 2024-01-03 15:59:28.015701322 | 2024-01-03 15:59:33.265693472 |
| 144 | 2024-01-03 16:00:00 | 147.0 | 50.550 | 50.560 | 50.545 | 50.5550 | 389 | 102028 | 2024-01-03 15:59:33.265693538 | 2024-01-03 15:59:40.294178279 |
| 145 | 2024-01-03 16:00:00 | 148.0 | 50.560 | 50.560 | 50.530 | 50.5301 | 649 | 97956 | 2024-01-03 15:59:40.294179336 | 2024-01-03 15:59:53.856498573 |
| 146 | 2024-01-03 16:00:00 | 149.0 | 50.535 | 50.540 | 50.520 | 50.5200 | 275 | 101461 | 2024-01-03 15:59:53.857395780 | 2024-01-03 15:59:59.970955843 |
| 147 | 2024-01-03 16:00:00 | 150.0 | 50.521 | 50.521 | 50.520 | 50.5200 | 3 | 9620 | 2024-01-03 15:59:59.971309605 | 2024-01-03 15:59:59.994802751 |
148 rows × 10 columns
Volume Profile#
Volume Profile (Volume Histogram) from Trade Data.
Retrieves the VOLUME and TRADE_COUNT grouped by PRICE.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Total volume and trade count for each distinct price level
data = data.agg(
{
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count(),
},
group_by=['PRICE']
)
# 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, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE | VOLUME | TRADE_COUNT | |
|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 49.9400 | 16 | 5 |
| 1 | 2024-01-03 16:00:00 | 49.9401 | 30 | 2 |
| 2 | 2024-01-03 16:00:00 | 49.9410 | 100 | 1 |
| 3 | 2024-01-03 16:00:00 | 49.9420 | 100 | 1 |
| 4 | 2024-01-03 16:00:00 | 49.9426 | 1 | 1 |
| ... | ... | ... | ... | ... |
| 995 | 2024-01-03 16:00:00 | 50.1867 | 3 | 2 |
| 996 | 2024-01-03 16:00:00 | 50.1868 | 35 | 1 |
| 997 | 2024-01-03 16:00:00 | 50.1869 | 110 | 4 |
| 998 | 2024-01-03 16:00:00 | 50.1870 | 349 | 3 |
| 999 | 2024-01-03 16:00:00 | 50.1871 | 103 | 2 |
1000 rows × 4 columns
Volume Profile by Sample Bins#
Volume Profile by Sample from Trade Data.
The price range across the trading day is divided into a fixed number of samples (here 100).
TICK_SIZE = (max price - min price) / (samples - 1) = range / 99.
Each trade’s price is floored into a PRICE_BIN by dividing by TICK_SIZE.
Retrieves the VOLUME and TRADE_COUNT grouped by PRICE_BIN.
import onetick.py as otp
SAMPLES = 100
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Compute the day's price range with a running (cumulative) min/max so the
# TICK_SIZE is available on every tick without a separate join.
data = data.agg(
{
'MIN_PRICE': otp.agg.min('PRICE'),
'MAX_PRICE': otp.agg.max('PRICE'),
},
running=True,
all_fields=True
)
data['TICK_SIZE'] = (data['MAX_PRICE'] - data['MIN_PRICE']) / (SAMPLES - 1)
# Floor each price into its sample bin
data['PRICE_BIN'] = otp.math.floor(data['PRICE'] / data['TICK_SIZE'])
# Total volume and trade count for each price bin
data = data.agg(
{
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count(),
},
group_by=['PRICE_BIN']
)
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE_BIN | VOLUME | TRADE_COUNT | |
|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 5767.0 | 7857 | 87 |
| 1 | 2024-01-03 16:00:00 | 5768.0 | 83268 | 348 |
| 2 | 2024-01-03 16:00:00 | 5769.0 | 36802 | 346 |
| 3 | 2024-01-03 16:00:00 | 5770.0 | 73483 | 568 |
| 4 | 2024-01-03 16:00:00 | 5771.0 | 68848 | 785 |
| ... | ... | ... | ... | ... |
| 523 | 2024-01-03 16:00:00 | 33033.0 | 83 | 1 |
| 524 | 2024-01-03 16:00:00 | 33085.0 | 163 | 2 |
| 525 | 2024-01-03 16:00:00 | 33112.0 | 105 | 2 |
| 526 | 2024-01-03 16:00:00 | 35470.0 | 3 | 1 |
| 527 | 2024-01-03 16:00:00 | inf | 2 | 1 |
528 rows × 4 columns
Volume Profile by Tick Size Bins#
Volume Profile by Tick Size from Trade Data.
A fixed tick size is specified (here 1 cent, 0.01).
Each trade’s price is floored to that tick size to form a PRICE_BIN.
Retrieves the VOLUME and TRADE_COUNT grouped by PRICE_BIN.
import onetick.py as otp
TICK_SIZE = 0.01
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Floor each price to the nearest tick-size bin
data['PRICE_BIN'] = otp.math.floor(data['PRICE'] / TICK_SIZE) * TICK_SIZE
# Total volume and trade count for each price bin
data = data.agg(
{
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count(),
},
group_by=['PRICE_BIN']
)
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | PRICE_BIN | VOLUME | TRADE_COUNT | |
|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 49.94 | 766 | 19 |
| 1 | 2024-01-03 16:00:00 | 49.95 | 1262 | 27 |
| 2 | 2024-01-03 16:00:00 | 49.96 | 3982 | 55 |
| 3 | 2024-01-03 16:00:00 | 49.97 | 2728 | 49 |
| 4 | 2024-01-03 16:00:00 | 49.98 | 7869 | 116 |
| ... | ... | ... | ... | ... |
| 72 | 2024-01-03 16:00:00 | 50.66 | 162270 | 1430 |
| 73 | 2024-01-03 16:00:00 | 50.67 | 81742 | 774 |
| 74 | 2024-01-03 16:00:00 | 50.68 | 16980 | 144 |
| 75 | 2024-01-03 16:00:00 | 50.74 | 19 | 1 |
| 76 | 2024-01-03 16:00:00 | 50.80 | 81 | 1 |
77 rows × 4 columns
Volume Spike Detection#
Volume Spike Detection from Trade Data.
Volume is aggregated into 1-minute buckets.
MAVG_VOLUME is the average volume across the last 5 buckets (current + 4 preceding).
A spike is flagged (SPIKES = 1) when the current bucket volume exceeds twice MAVG_VOLUME.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Bucket volume, last price and trade count per 1-minute interval
data = data.agg(
{
'LAST_PRICE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count(),
},
bucket_interval=otp.Minute(1)
)
# Moving average volume over the last 5 buckets (current + 4 preceding)
data = data.agg(
{'MAVG_VOLUME': otp.agg.average('VOLUME')},
running=True,
bucket_interval=5,
bucket_units='ticks',
all_fields=True
)
# Flag spikes where volume is more than twice the moving-average volume
data['SPIKES'] = data.apply(lambda r: 1 if r['VOLUME'] > 2 * r['MAVG_VOLUME'] else 0)
data = data[['LAST_PRICE', 'VOLUME', 'TRADE_COUNT', 'MAVG_VOLUME', 'SPIKES']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST_PRICE | VOLUME | TRADE_COUNT | MAVG_VOLUME | SPIKES | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 624483 | 1988 | 624483.000000 | 0 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 45575 | 529 | 335029.000000 | 0 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 34353 | 410 | 234803.666667 | 0 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 37577 | 383 | 185497.000000 | 0 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 37479 | 402 | 155893.400000 | 0 |
| ... | ... | ... | ... | ... | ... | ... |
| 385 | 2024-01-03 15:56:00 | 50.505 | 202820 | 1538 | 214195.600000 | 0 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 183148 | 1429 | 216602.000000 | 0 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 364420 | 1878 | 249589.200000 | 0 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 178199 | 1095 | 223255.000000 | 0 |
| 389 | 2024-01-03 16:00:00 | 50.520 | 658118 | 3149 | 317341.000000 | 1 |
390 rows × 6 columns
Volume Spike Detection on 1 Min Bars#
Volume Spike Detection from 1 Minute Trade Bars.
MAVG_VOLUME is the average of the bar VOLUME across the last 5 bars (current + 4 preceding).
A spike is flagged (SPIKES = 1) when the current bar VOLUME exceeds twice MAVG_VOLUME.
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST', 'VOLUME']]
# Moving average volume over the last 5 bars (current + 4 preceding)
data = data.agg(
{'MAVG_VOLUME': otp.agg.average('VOLUME')},
running=True,
bucket_interval=5,
bucket_units='ticks',
all_fields=True
)
# Flag spikes where volume is more than twice the moving-average volume
data['SPIKES'] = data.apply(lambda r: 1 if r['VOLUME'] > 2 * r['MAVG_VOLUME'] else 0)
data = data[['LAST', 'VOLUME', 'MAVG_VOLUME', 'SPIKES']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | VOLUME | MAVG_VOLUME | SPIKES | |
|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 69657 | 69657.0 | 0 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 39792 | 54724.5 | 0 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 26904 | 45451.0 | 0 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 32673 | 42256.5 | 0 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 32783 | 40361.8 | 0 |
| ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 169383 | 210648.8 | 0 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 185335 | 198229.0 | 0 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 166268 | 200247.0 | 0 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 346983 | 233206.0 | 0 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 166368 | 206867.4 | 0 |
389 rows × 5 columns
Volume Surge Indicator#
Volume Surge Indicator from Trade Data.
Volume is aggregated into 1-minute buckets.
MAVG_VOLUME is the average volume across the last 100 buckets (current + 99 preceding).
VOLUME_SURGE = 100 * VOLUME / MAVG_VOLUME expresses the current volume relative to the recent average.
import onetick.py as otp
# Retrieve trade data for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
# Bucket volume, last price and trade count per 1-minute interval
data = data.agg(
{
'LAST_PRICE': otp.agg.last('PRICE'),
'VOLUME': otp.agg.sum('SIZE'),
'TRADE_COUNT': otp.agg.count(),
},
bucket_interval=otp.Minute(1)
)
# Moving average volume over the last 100 buckets (current + 99 preceding)
data = data.agg(
{'MAVG_VOLUME': otp.agg.average('VOLUME')},
running=True,
bucket_interval=100,
bucket_units='ticks',
all_fields=True
)
# Volume surge: current volume as a percentage of the moving-average volume
data['VOLUME_SURGE'] = 100 * data['VOLUME'] / data['MAVG_VOLUME']
data = data[['LAST_PRICE', 'VOLUME', 'TRADE_COUNT', 'MAVG_VOLUME', 'VOLUME_SURGE']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST_PRICE | VOLUME | TRADE_COUNT | MAVG_VOLUME | VOLUME_SURGE | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 624483 | 1988 | 624483.000000 | 100.000000 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 45575 | 529 | 335029.000000 | 13.603300 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 34353 | 410 | 234803.666667 | 14.630521 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 37577 | 383 | 185497.000000 | 20.257470 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 37479 | 402 | 155893.400000 | 24.041428 |
| ... | ... | ... | ... | ... | ... | ... |
| 385 | 2024-01-03 15:56:00 | 50.505 | 202820 | 1538 | 52276.230000 | 387.977480 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 183148 | 1429 | 53690.820000 | 341.116042 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 364420 | 1878 | 57021.490000 | 639.092384 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 178199 | 1095 | 58651.630000 | 303.826168 |
| 389 | 2024-01-03 16:00:00 | 50.520 | 658118 | 3149 | 64882.060000 | 1014.329693 |
390 rows × 6 columns
Volume Surge Indicator on 1 Min Bars#
Volume Surge Indicator from 1 Minute Trade Bars.
MAVG_VOLUME is the average of the bar VOLUME across the last 100 bars (current + 99 preceding).
VOLUME_SURGE = 100 * VOLUME / MAVG_VOLUME expresses the current bar volume relative to the recent average.
import onetick.py as otp
# Retrieve 1-minute trade bars for CSCO
data = otp.DataSource(db='US_COMP_SAMPLE_BARS', tick_type='TRD_1M')
data = data[['LAST', 'VOLUME', 'TRADE_TICK_COUNT']]
# Moving average volume over the last 100 bars (current + 99 preceding)
data = data.agg(
{'MAVG_VOLUME': otp.agg.average('VOLUME')},
running=True,
bucket_interval=100,
bucket_units='ticks',
all_fields=True
)
# Volume surge: current bar volume as a percentage of the moving-average volume
data['VOLUME_SURGE'] = 100 * data['VOLUME'] / data['MAVG_VOLUME']
data = data[['LAST', 'VOLUME', 'TRADE_TICK_COUNT', 'MAVG_VOLUME', 'VOLUME_SURGE']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 16, 0),
timezone='America/New_York',
symbols='CSCO'
)
result
| Time | LAST | VOLUME | TRADE_TICK_COUNT | MAVG_VOLUME | VOLUME_SURGE | |
|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:31:00 | 50.160 | 69657 | 418 | 69657.00 | 100.000000 |
| 1 | 2024-01-03 09:32:00 | 50.090 | 39792 | 244 | 54724.50 | 72.713319 |
| 2 | 2024-01-03 09:33:00 | 50.040 | 26904 | 187 | 45451.00 | 59.193417 |
| 3 | 2024-01-03 09:34:00 | 50.025 | 32673 | 149 | 42256.50 | 77.320649 |
| 4 | 2024-01-03 09:35:00 | 50.020 | 32783 | 173 | 40361.80 | 81.222839 |
| ... | ... | ... | ... | ... | ... | ... |
| 384 | 2024-01-03 15:55:00 | 50.475 | 169383 | 787 | 44883.59 | 377.382914 |
| 385 | 2024-01-03 15:56:00 | 50.505 | 185335 | 925 | 46416.08 | 399.290504 |
| 386 | 2024-01-03 15:57:00 | 50.515 | 166268 | 850 | 47726.45 | 348.377053 |
| 387 | 2024-01-03 15:58:00 | 50.520 | 346983 | 1277 | 50928.83 | 681.309584 |
| 388 | 2024-01-03 15:59:00 | 50.530 | 166368 | 669 | 52473.92 | 317.048926 |
389 rows × 6 columns
VPIN#
Volume Synchronized Probability of Informed Trading (VPIN) from Trade Data.
Volume bins are formed every 100 000 shares.
BUY_VOLUME is SIZE where AGGRESSOR_SIDE = 'B', SELL_VOLUME where AGGRESSOR_SIDE = 'S'.
VPIN is the rolling average of BIN_IMBALANCE over the last 50 bins.
import onetick.py as otp
# Retrieve trade data for VOD (LSE publishes AGGRESSOR_SIDE)
data = otp.DataSource(db='LSE', tick_type='TRD')
data = data[['SIZE', 'AGGRESSOR_SIDE']]
# Split size into buy/sell volume by aggressor side
data['BUY_VOLUME'] = data.apply(lambda r: r['SIZE'] if r['AGGRESSOR_SIDE'] == 'B' else 0)
data['SELL_VOLUME'] = data.apply(lambda r: r['SIZE'] if r['AGGRESSOR_SIDE'] == 'S' else 0)
# Cumulative volume, then floor into fixed 100,000-share volume bins
data = data.agg({'ACC_VOLUME': otp.agg.sum('SIZE')}, running=True, all_fields=True)
data['VOL_BIN'] = otp.math.floor(data['ACC_VOLUME'] / 100000)
# Aggregate each volume bin
data = data.agg(
{
'BIN_START': otp.agg.first_time(),
'BIN_END': otp.agg.last_time(),
'TRADE_COUNT': otp.agg.count(),
'VOLUME': otp.agg.sum('SIZE'),
'BUY_VOLUME': otp.agg.sum('BUY_VOLUME'),
'SELL_VOLUME': otp.agg.sum('SELL_VOLUME'),
},
group_by=['VOL_BIN']
)
# Order imbalance within each bin (absolute net volume over total volume)
data['DIFF'] = data['BUY_VOLUME'] - data['SELL_VOLUME']
data['ABS_DIFF'] = data.apply(lambda r: r['DIFF'] if r['DIFF'] >= 0 else -r['DIFF'])
data['BIN_IMBALANCE'] = data['ABS_DIFF'] / data['VOLUME']
# VPIN: rolling average of the bin imbalance over the last 50 bins
data = data.agg(
{'VPIN': otp.agg.average('BIN_IMBALANCE')},
running=True,
bucket_interval=50,
bucket_units='ticks',
all_fields=True,
)
data = data[['VOL_BIN', 'BIN_START', 'BIN_END', 'TRADE_COUNT', 'VOLUME',
'BUY_VOLUME', 'SELL_VOLUME', 'BIN_IMBALANCE', 'VPIN']]
result = otp.run(
data,
start=otp.dt(2024, 1, 3, 8),
end=otp.dt(2024, 1, 3, 16),
timezone='Europe/London',
symbols='VOD'
)
result
| Time | VOL_BIN | BIN_START | BIN_END | TRADE_COUNT | VOLUME | BUY_VOLUME | SELL_VOLUME | BIN_IMBALANCE | VPIN | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00 | 1.0 | 2024-01-03 08:00:06.232 | 2024-01-03 08:00:09.380 | 5 | 188102 | 0 | 640 | 0.003402 | 0.003402 |
| 1 | 2024-01-03 16:00:00 | 2.0 | 2024-01-03 08:00:09.943 | 2024-01-03 08:00:39.920 | 22 | 108351 | 18726 | 19626 | 0.008306 | 0.005854 |
| 2 | 2024-01-03 16:00:00 | 3.0 | 2024-01-03 08:00:39.921 | 2024-01-03 08:01:54.173 | 163 | 47376 | 11357 | 9223 | 0.045044 | 0.018918 |
| 3 | 2024-01-03 16:00:00 | 4.0 | 2024-01-03 08:01:55.438 | 2024-01-03 08:02:34.502 | 53 | 155799 | 119681 | 26964 | 0.595107 | 0.162965 |
| 4 | 2024-01-03 16:00:00 | 5.0 | 2024-01-03 08:02:34.619 | 2024-01-03 08:04:24.229 | 58 | 95773 | 5459 | 49506 | 0.459910 | 0.222354 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 196 | 2024-01-03 16:00:00 | 674.0 | 2024-01-03 15:47:27.146 | 2024-01-03 15:48:14.860 | 22 | 96760 | 711 | 58735 | 0.599669 | 0.299909 |
| 197 | 2024-01-03 16:00:00 | 675.0 | 2024-01-03 15:48:14.860 | 2024-01-03 15:52:36.288 | 47 | 101722 | 15632 | 70823 | 0.542567 | 0.309792 |
| 198 | 2024-01-03 16:00:00 | 676.0 | 2024-01-03 15:52:45.542 | 2024-01-03 15:55:34.314 | 48 | 102397 | 55608 | 19929 | 0.348438 | 0.299804 |
| 199 | 2024-01-03 16:00:00 | 677.0 | 2024-01-03 15:55:40.243 | 2024-01-03 15:57:04.639 | 37 | 100372 | 25748 | 71550 | 0.456322 | 0.294721 |
| 200 | 2024-01-03 16:00:00 | 678.0 | 2024-01-03 15:57:04.639 | 2024-01-03 15:59:58.296 | 31 | 93870 | 19592 | 49629 | 0.319985 | 0.297549 |
201 rows × 10 columns
Death & Golden Cross Events Identification#
Identifying the Golden and Death Crosses for a specified Symbol across a 2 Year Time Period from Daily Data.
Retrieves Daily Data for the specified Symbol HD (Home Depot).
Calculates 50 and 200 period (day) moving averages as running aggregates with bucket_units='seconds'
(bucket_interval given as a multiple of 86400,
since bucket_units='days'/'months' are not supported for sliding-window aggregation).
Calculates the prior values for the moving averages using column[-1] (record-based lag).
Uses apply() to return the price
when the Golden Cross and Death Cross occur, otherwise otp.nan.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')
data = data[['CLOSE', 'VOLUME']]
# Calculate the short and long moving averages
data = data.agg({'SMA50_CLOSE': otp.agg.average('CLOSE')},
running=True, bucket_interval=50 * 86400, bucket_units='seconds', all_fields=True)
data = data.agg({'SMA200_CLOSE': otp.agg.average('CLOSE')},
running=True, bucket_interval=200 * 86400, bucket_units='seconds', all_fields=True)
# Calculate the prior values for the moving averages
data['PRIOR_SMA50_CLOSE'] = data['SMA50_CLOSE'][-1]
data['PRIOR_SMA200_CLOSE'] = data['SMA200_CLOSE'][-1]
# Identify the Golden and Death Crosses, returning the price when they occur, otherwise otp.nan
def golden_cross(row):
if row['SMA50_CLOSE'] > row['SMA200_CLOSE'] and row['PRIOR_SMA50_CLOSE'] < row['PRIOR_SMA200_CLOSE']:
return row['CLOSE']
return otp.nan
def death_cross(row):
if row['SMA50_CLOSE'] < row['SMA200_CLOSE'] and row['PRIOR_SMA50_CLOSE'] > row['PRIOR_SMA200_CLOSE']:
return row['CLOSE']
return otp.nan
data['GOLDEN_CROSS_PRICE'] = data.apply(golden_cross)
data['DEATH_CROSS_PRICE'] = data.apply(death_cross)
data = data[['CLOSE', 'VOLUME', 'SMA50_CLOSE', 'SMA200_CLOSE', 'PRIOR_SMA50_CLOSE', 'PRIOR_SMA200_CLOSE',
'GOLDEN_CROSS_PRICE', 'DEATH_CROSS_PRICE']]
result = otp.run(data,
start=otp.dt(2024, 1, 1),
end=otp.dt(2026, 1, 1),
timezone='America/New_York',
symbols='HD')
result
| Time | CLOSE | VOLUME | SMA50_CLOSE | SMA200_CLOSE | PRIOR_SMA50_CLOSE | PRIOR_SMA200_CLOSE | GOLDEN_CROSS_PRICE | DEATH_CROSS_PRICE | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-02 20:15:00 | 345.08 | 2833794 | 345.080000 | 345.080000 | NaN | NaN | NaN | NaN |
| 1 | 2024-01-03 20:15:00 | 338.26 | 3309589 | 341.670000 | 341.670000 | 345.080000 | 345.080000 | NaN | NaN |
| 2 | 2024-01-04 20:15:00 | 338.59 | 3652356 | 340.643333 | 340.643333 | 341.670000 | 341.670000 | NaN | NaN |
| 3 | 2024-01-05 20:15:00 | 342.94 | 2664279 | 341.217500 | 341.217500 | 340.643333 | 340.643333 | NaN | NaN |
| 4 | 2024-01-08 20:15:00 | 347.93 | 2736244 | 342.560000 | 342.560000 | 341.217500 | 341.217500 | NaN | NaN |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 497 | 2025-12-24 20:15:00 | 347.34 | 1368553 | 354.530857 | 378.468417 | 355.552000 | 378.693986 | NaN | NaN |
| 498 | 2025-12-26 20:15:00 | 349.78 | 1786872 | 353.395588 | 378.357482 | 354.530857 | 378.468417 | NaN | NaN |
| 499 | 2025-12-29 20:15:00 | 347.45 | 3790318 | 352.699706 | 378.449708 | 353.395588 | 378.357482 | NaN | NaN |
| 500 | 2025-12-30 20:15:00 | 346.35 | 2393017 | 351.991471 | 378.389051 | 352.699706 | 378.449708 | NaN | NaN |
| 501 | 2025-12-31 20:15:00 | 344.10 | 2317891 | 351.105000 | 378.140580 | 351.991471 | 378.389051 | NaN | NaN |
502 rows × 9 columns
Death Cross Events for the Mag7#
Retrieves Daily Data for AAPL, MSFT, AMZN, GOOG, META, NVDA and TSLA.
Calculates 50 and 200 period (day) moving averages as running aggregates with bucket_units='seconds'
(bucket_interval given as a multiple of 86400,
since bucket_units='days'/'months' are not supported for sliding-window aggregation),
and the prior values using column[-1] (record-based lag).
The per-symbol computation is built once, then merged across the symbol list with otp.merge,
so each symbol’s moving averages run independently before being combined into one result.
Filters to bars where the short moving average crosses below the long moving average.
import onetick.py as otp
MAG7 = ['AAPL', 'MSFT', 'AMZN', 'GOOG', 'META', 'NVDA', 'TSLA']
data = otp.DataSource(db='US_COMP_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')
data = data[['CLOSE', 'VOLUME']]
# Calculate the short and long moving averages
data = data.agg({'SMA50_CLOSE': otp.agg.average('CLOSE')},
running=True, bucket_interval=50 * 86400, bucket_units='seconds', all_fields=True)
data = data.agg({'SMA200_CLOSE': otp.agg.average('CLOSE')},
running=True, bucket_interval=200 * 86400, bucket_units='seconds', all_fields=True)
# Calculate the prior values for the moving averages
data['PRIOR_SMA50_CLOSE'] = data['SMA50_CLOSE'][-1]
data['PRIOR_SMA200_CLOSE'] = data['SMA200_CLOSE'][-1]
# Merge every Mag7 symbol's moving average stream into a single result.
merged = otp.merge([data], symbols=MAG7, identify_input_ts=True)
# Filter on when the moving averages cross (Death Cross)
merged = merged.where((merged['SMA50_CLOSE'] < merged['SMA200_CLOSE']) &
(merged['PRIOR_SMA50_CLOSE'] > merged['PRIOR_SMA200_CLOSE']))
result = otp.run(merged,
start=otp.dt(2024, 1, 1),
end=otp.dt(2026, 1, 1),
timezone='America/New_York')
result
| Time | CLOSE | VOLUME | SMA50_CLOSE | SMA200_CLOSE | PRIOR_SMA50_CLOSE | PRIOR_SMA200_CLOSE | SYMBOL_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-03-08 20:15:00 | 170.73 | 76267041 | 184.432286 | 184.498936 | 184.943714 | 184.798261 | AAPL | DAY |
| 1 | 2024-03-15 20:15:00 | 142.17 | 41039494 | 143.179722 | 143.294038 | 143.406944 | 143.316078 | GOOG | DAY |
| 2 | 2024-06-24 20:15:00 | 118.11 | 477031066 | 759.441471 | 762.211375 | 782.082059 | 767.623992 | NVDA | DAY |
| 3 | 2024-08-26 20:15:00 | 175.50 | 22368036 | 180.295278 | 180.972065 | 180.975833 | 180.775964 | AMZN | DAY |
| 4 | 2024-08-30 20:15:00 | 417.14 | 24310524 | 421.724167 | 422.286357 | 422.767500 | 422.323381 | MSFT | DAY |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 11 | 2025-03-26 20:15:00 | 167.14 | 22554236 | 176.994167 | 177.859635 | 177.980278 | 177.938456 | GOOG | DAY |
| 12 | 2025-03-31 20:15:00 | 190.26 | 63551458 | 207.891143 | 208.717279 | 209.830833 | 208.306957 | AMZN | DAY |
| 13 | 2025-04-11 20:15:00 | 543.57 | 17657177 | 605.534324 | 608.873406 | 609.864054 | 609.024420 | META | DAY |
| 14 | 2025-11-12 20:15:00 | 609.01 | 24597922 | 702.549028 | 704.710683 | 706.615417 | 705.404167 | META | DAY |
| 15 | 2025-12-09 20:15:00 | 492.02 | 14696078 | 502.336286 | 502.809270 | 503.044000 | 502.503869 | MSFT | DAY |
16 rows × 9 columns
Golden Cross Events for the Mag7#
Retrieves Daily Data for AAPL, MSFT, AMZN, GOOG, META, NVDA and TSLA.
Calculates 50 and 200 period (day) moving averages as running aggregates with bucket_units='seconds'
(bucket_interval given as a multiple of 86400,
since bucket_units='days'/'months' are not supported for sliding-window aggregation),
and the prior values using column[-1] (record-based lag).
The per-symbol computation is built once, then merged across the symbol list with otp.merge,
so each symbol’s moving averages run independently before being combined into one result.
Filters to bars where the short moving average crosses above the long moving average.
import onetick.py as otp
MAG7 = ['AAPL', 'MSFT', 'AMZN', 'GOOG', 'META', 'NVDA', 'TSLA']
data = otp.DataSource(db='US_COMP_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')
data = data[['CLOSE', 'VOLUME']]
# Calculate the short and long moving averages
data = data.agg({'SMA50_CLOSE': otp.agg.average('CLOSE')},
running=True, bucket_interval=50 * 86400, bucket_units='seconds', all_fields=True)
data = data.agg({'SMA200_CLOSE': otp.agg.average('CLOSE')},
running=True, bucket_interval=200 * 86400, bucket_units='seconds', all_fields=True)
# Calculate the prior values for the moving averages
data['PRIOR_SMA50_CLOSE'] = data['SMA50_CLOSE'][-1]
data['PRIOR_SMA200_CLOSE'] = data['SMA200_CLOSE'][-1]
# Merge every Mag7 symbol's moving average stream into a single result.
merged = otp.merge([data], symbols=MAG7, identify_input_ts=True)
# Filter on when the moving averages cross (Golden Cross)
merged = merged.where((merged['SMA50_CLOSE'] > merged['SMA200_CLOSE']) &
(merged['PRIOR_SMA50_CLOSE'] < merged['PRIOR_SMA200_CLOSE']))
result = otp.run(merged,
start=otp.dt(2024, 1, 1),
end=otp.dt(2026, 1, 1),
timezone='America/New_York')
result
| Time | CLOSE | VOLUME | SMA50_CLOSE | SMA200_CLOSE | PRIOR_SMA50_CLOSE | PRIOR_SMA200_CLOSE | SYMBOL_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-04-10 20:15:00 | 157.66 | 16341374 | 145.802778 | 145.673768 | 145.464000 | 145.497500 | GOOG | DAY |
| 1 | 2024-06-03 20:15:00 | 194.03 | 50082639 | 180.548857 | 180.332736 | 180.049429 | 180.202286 | AAPL | DAY |
| 2 | 2024-07-05 20:15:00 | 251.52 | 154633652 | 187.367273 | 186.463750 | 184.718529 | 185.951496 | TSLA | DAY |
| 3 | 2024-10-14 20:15:00 | 187.54 | 22620007 | 183.354857 | 183.216277 | 183.054857 | 183.105036 | AMZN | DAY |
| 4 | 2024-10-30 20:15:00 | 432.53 | 29758749 | 424.564444 | 424.303022 | 424.055278 | 424.243406 | MSFT | DAY |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 10 | 2025-06-11 20:15:00 | 142.83 | 167701644 | 127.200571 | 126.317556 | 125.945143 | 126.311037 | NVDA | DAY |
| 11 | 2025-06-26 20:15:00 | 325.78 | 80443067 | 330.533529 | 330.468015 | 329.075882 | 330.502741 | TSLA | DAY |
| 12 | 2025-06-30 20:15:00 | 219.39 | 58887780 | 209.949412 | 209.067704 | 209.175000 | 209.387883 | AMZN | DAY |
| 13 | 2025-07-14 20:15:00 | 182.81 | 21001352 | 175.069091 | 174.945630 | 174.668485 | 175.208897 | GOOG | DAY |
| 14 | 2025-08-18 20:15:00 | 230.89 | 37849788 | 214.572857 | 213.825217 | 213.721143 | 214.174786 | AAPL | DAY |
15 rows × 9 columns