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