Basics#

This section contains examples for Basics 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__'

Data Retrieval#

By default all rows and columns are retrieved across the specified time range.
Specified time zone applies to both the query interval and the results.
Retrieve Exchange trades from the US_COMP_SAMPLE database for CSCO.

import onetick.py as otp

# Define Data Source, with Database and Table
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Specify Symbol, Time Range, and Time Zone
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 30, 1),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM TRADE_ID PARTICIPANT_TIME TRF_TIME OMDSEQ
0 2024-01-03 09:30:00.065443591 Z @ I N 0 CSCO 50.02 1969-12-31 19:00:00 0 2 0 169103 42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00 0
1 2024-01-03 09:30:00.111130049 Z @ I N 0 CSCO 50.16 1969-12-31 19:00:00 0 3 0 169148 43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00 0
2 2024-01-03 09:30:00.127459523 V @ N 0 CSCO 50.17 1969-12-31 19:00:00 0 100 0 169181 13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00 0
3 2024-01-03 09:30:00.128498068 Z @F I N 1 CSCO 50.17 1969-12-31 19:00:00 0 5 0 169182 44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00 0
4 2024-01-03 09:30:00.135190071 Q @FTI N 1 CSCO 50.13 1969-12-31 19:00:00 0 46 0 169193 349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
145 2024-01-03 09:30:00.999507511 Q @ I N 0 CSCO 50.09 1969-12-31 19:00:00 0 2 0 172569 396 2024-01-03 09:30:00.999433768 1969-12-31 19:00:00 2
146 2024-01-03 09:30:00.999511419 Q @ N 0 CSCO 50.09 1969-12-31 19:00:00 0 200 0 172570 397 2024-01-03 09:30:00.999433768 1969-12-31 19:00:00 3
147 2024-01-03 09:30:00.999512020 Q @ N 0 CSCO 50.09 1969-12-31 19:00:00 0 300 0 172571 398 2024-01-03 09:30:00.999433768 1969-12-31 19:00:00 4
148 2024-01-03 09:30:00.999553801 P @ I N 0 CSCO 50.09 1969-12-31 19:00:00 0 87 0 172574 312 2024-01-03 09:30:00.999210245 1969-12-31 19:00:00 5
149 2024-01-03 09:30:00.999590600 Q @F N 1 CSCO 50.09 1969-12-31 19:00:00 0 500 0 172575 399 2024-01-03 09:30:00.999572740 1969-12-31 19:00:00 6

150 rows × 18 columns

First N Rows#

Retrieve only the first 100 trades using limit() for CSCO from the US_COMP_SAMPLE database, across the specified time range.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM TRADE_ID PARTICIPANT_TIME TRF_TIME OMDSEQ
0 2024-01-03 09:30:00.065443591 Z @ I N 0 CSCO 50.0200 1969-12-31 19:00:00 0 2 0 169103 42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00.000000000 0
1 2024-01-03 09:30:00.111130049 Z @ I N 0 CSCO 50.1600 1969-12-31 19:00:00 0 3 0 169148 43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00.000000000 0
2 2024-01-03 09:30:00.127459523 V @ N 0 CSCO 50.1700 1969-12-31 19:00:00 0 100 0 169181 13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00.000000000 0
3 2024-01-03 09:30:00.128498068 Z @F I N 1 CSCO 50.1700 1969-12-31 19:00:00 0 5 0 169182 44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00.000000000 0
4 2024-01-03 09:30:00.135190071 Q @FTI N 1 CSCO 50.1300 1969-12-31 19:00:00 0 46 0 169193 349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00.000000000 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
95 2024-01-03 09:30:00.897416820 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 51 0 171857 365 2024-01-03 09:30:00.897398456 1969-12-31 19:00:00.000000000 3
96 2024-01-03 09:30:00.897444128 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 29 0 171858 366 2024-01-03 09:30:00.897428791 1969-12-31 19:00:00.000000000 4
97 2024-01-03 09:30:00.897474806 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 12 0 171859 367 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000 5
98 2024-01-03 09:30:00.897476583 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 2 0 171860 368 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000 6
99 2024-01-03 09:30:00.897670983 D @ N Q 0 CSCO 50.0797 1969-12-31 19:00:00 0 100 0 171862 100 2024-01-03 09:30:00.896374950 2024-01-03 09:30:00.897637536 7

100 rows × 18 columns

Selecting Fields#

Retrieve Trades specifying selected fields as an array of fields from the returned data source.

import onetick.py as otp

# Define the Data Source specifying the Database and Table
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Specify the fields to return
data = data[['PRICE', 'SIZE']]

# Return first 100 Rows
data = data.limit(100)

# Run the Query for the defined Symbol and Time Window
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time PRICE SIZE
0 2024-01-03 09:30:00.065443591 50.0200 2
1 2024-01-03 09:30:00.111130049 50.1600 3
2 2024-01-03 09:30:00.127459523 50.1700 100
3 2024-01-03 09:30:00.128498068 50.1700 5
4 2024-01-03 09:30:00.135190071 50.1300 46
... ... ... ...
95 2024-01-03 09:30:00.897416820 50.0800 51
96 2024-01-03 09:30:00.897444128 50.0800 29
97 2024-01-03 09:30:00.897474806 50.0800 12
98 2024-01-03 09:30:00.897476583 50.0800 2
99 2024-01-03 09:30:00.897670983 50.0797 100

100 rows × 3 columns

Adding Calculated Fields#

Retrieve Trades adding a calculated field to the data source.

import onetick.py as otp

# Define Data Source
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Limit Schema
data = data[['PRICE', 'SIZE']]

# Add Calculated Field
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE']

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time PRICE SIZE TRADED_VALUE
0 2024-01-03 09:30:00.065443591 50.0200 2 100.04
1 2024-01-03 09:30:00.111130049 50.1600 3 150.48
2 2024-01-03 09:30:00.127459523 50.1700 100 5017.00
3 2024-01-03 09:30:00.128498068 50.1700 5 250.85
4 2024-01-03 09:30:00.135190071 50.1300 46 2305.98
... ... ... ... ...
95 2024-01-03 09:30:00.897416820 50.0800 51 2554.08
96 2024-01-03 09:30:00.897444128 50.0800 29 1452.32
97 2024-01-03 09:30:00.897474806 50.0800 12 600.96
98 2024-01-03 09:30:00.897476583 50.0800 2 100.16
99 2024-01-03 09:30:00.897670983 50.0797 100 5007.97

100 rows × 4 columns

Adding Filters#

Retrieve Trades, filtered by exchange and trade size.

import onetick.py as otp

# Define Data Source
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Limit Schema
data = data[['PRICE', 'SIZE', 'EXCHANGE']]

# Add Calculated Fields
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE']

# Filter Fields
data = data.where((data['EXCHANGE'] == 'N') & (data['SIZE'] > 100))

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time PRICE SIZE EXCHANGE TRADED_VALUE
0 2024-01-03 09:30:02.371760827 50.13 127 N 6366.51
1 2024-01-03 09:30:19.325065423 50.13 337 N 16893.81
2 2024-01-03 09:30:19.701084742 50.15 400 N 20060.00
3 2024-01-03 09:30:20.787037891 50.14 107 N 5364.98
4 2024-01-03 09:30:23.436860708 50.14 600 N 30084.00
... ... ... ... ... ...
54 2024-01-03 09:38:28.029695573 50.08 200 N 10016.00
55 2024-01-03 09:39:01.147191601 50.09 312 N 15628.08
56 2024-01-03 09:39:04.654739241 50.08 200 N 10016.00
57 2024-01-03 09:39:17.427491120 50.08 211 N 10566.88
58 2024-01-03 09:39:22.549900523 50.10 200 N 10020.00

59 rows × 5 columns

Adjusting for Corporate Actions#

Retrieve trades with original and corporate action adjusted prices for splits.

import onetick.py as otp

# Defining Data Source
data = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')

# Restrict Returned Fields
data = data[['PRICE', 'SIZE']]

# Add Fields with Original Price and Size
data['ORIG_PRICE'] = data['PRICE']
data['ORIG_SIZE'] = data['SIZE']

# Adjust PRICE field for historic Corporate Actions
data = data.corp_actions(fields='PRICE',
                         adjustment_date=otp.date(2024, 1, 17),
                         adjust_rule='PRICE',
                         apply_split=True)

# Adjust SIZE field for historic Corporate Actions
data = data.corp_actions(fields='SIZE',
                         adjustment_date=otp.date(2024, 1, 17),
                         adjust_rule='SIZE',
                         apply_split=True)

result = otp.run(data,
                 start=otp.dt(2024, 1, 10),
                 end=otp.dt(2024, 1, 17),
                 timezone='Europe/London',
                 symbols='DKE',
                 symbol_date=otp.dt(2024, 1, 17))
result
Time PRICE SIZE ORIG_PRICE ORIG_SIZE
0 2024-01-10 10:17:28.066 0.4010 127668.4 0.04010 1276684
1 2024-01-10 10:17:54.299 0.4000 100000.0 0.04000 1000000
2 2024-01-10 10:18:02.437 0.4120 111349.5 0.04120 1113495
3 2024-01-10 10:18:34.771 0.3566 600739.4 0.03566 6007394
4 2024-01-10 10:18:42.555 0.3500 55.0 0.03500 550
... ... ... ... ... ...
102 2024-01-16 11:00:11.634 0.4000 520794.0 0.40000 520794
103 2024-01-16 11:49:51.584 0.3750 475182.0 0.37500 475182
104 2024-01-16 11:58:51.631 0.3500 51661.0 0.35000 51661
105 2024-01-16 14:55:22.581 0.3500 18.0 0.35000 18
106 2024-01-16 15:42:57.681 0.3875 24136.0 0.38750 24136

107 rows × 5 columns

Aggregation Statistics#

Trade Price Statistics Analysis.
Computes aggregate price statistics (mean, standard deviation, median, min, max, VWAP, TWAP, count) for LSE trades.

import onetick.py as otp

trd = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')

data = trd.agg({
    'MEAN_PRICE': otp.agg.average('PRICE'),
    'STDDEV_PRICE': otp.agg.stddev('PRICE'),
    'MEDIAN_PRICE': otp.agg.median('PRICE'),
    'MAX_PRICE': otp.agg.max('PRICE'),
    'MIN_PRICE': otp.agg.min('PRICE'),
    'TWAP_PRICE': otp.agg.tw_average('PRICE'),
    'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
    'COUNT_PRICE': otp.agg.count(),
})

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 4, 16),
                 timezone='UTC',
                 symbols='VOD')
result
Time MEAN_PRICE STDDEV_PRICE MEDIAN_PRICE MAX_PRICE MIN_PRICE TWAP_PRICE VWAP_PRICE COUNT_PRICE
0 2024-01-04 16:00:00 69.770939 4.877331 70.16 71.0166 0.812 69.628948 70.020224 11925

Aggregation Statistics with Grouping#

Results can be aggregated by specifying an aggregation for each field, grouped with group_by.
Computes aggregate price statistics for LSE trades grouped by TRADE_CURRENCY.

import onetick.py as otp

trd = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')

data = trd.agg({
    'MEAN_PRICE': otp.agg.average('PRICE'),
    'STDDEV_PRICE': otp.agg.stddev('PRICE'),
    'MEDIAN_PRICE': otp.agg.median('PRICE'),
    'MAX_PRICE': otp.agg.max('PRICE'),
    'MIN_PRICE': otp.agg.min('PRICE'),
    'TWA_PRICE': otp.agg.tw_average('PRICE'),
    'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
    'COUNT_PRICE': otp.agg.count(),
}, group_by=['TRADE_CURRENCY'])

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 4, 16),
                 timezone='UTC',
                 symbols='VOD')
result
Time TRADE_CURRENCY MEAN_PRICE STDDEV_PRICE MEDIAN_PRICE MAX_PRICE MIN_PRICE TWA_PRICE VWAP_PRICE COUNT_PRICE
0 2024-01-04 16:00:00 EUR 0.817847 0.002494 0.818 0.8250 0.812 0.815294 0.817445 59
1 2024-01-04 16:00:00 GBX 70.113787 0.385586 70.160 71.0166 68.050 69.894661 70.022669 11866

Dynamic Bar Retrieval#

Bars can be generated on the fly by aggregating trades into time buckets, with bucket_interval given in seconds. Additionally a filter is applied using character_present() method with discard_on_match=True, to ensure certain trade conditions are ignored, and do not contribute to the bar generation.

Common Aggregations for CSCO grouped by 5 Minute bar retrieval.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE', 'COND']]

# Exclude the trade conditions that should not contribute to bar generation
trd = trd.character_present(field=trd['COND'], characters='IBCGHLMNPQRVWZ479', discard_on_match=True)

data = trd.agg({
    'FIRST_PRICE': otp.agg.first('PRICE'),
    'HIGH_PRICE': otp.agg.max('PRICE'),
    'LOW_PRICE': otp.agg.min('PRICE'),
    'LAST_PRICE': otp.agg.last('PRICE'),
    'SUM_SIZE': otp.agg.sum('SIZE'),
    'TRADE_COUNT': otp.agg.count(),
}, bucket_interval=300)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 4, 16),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time FIRST_PRICE HIGH_PRICE LOW_PRICE LAST_PRICE SUM_SIZE TRADE_COUNT
0 2024-01-03 08:05:00 50.180 50.180 50.0200 50.100 16979 26
1 2024-01-03 08:10:00 50.100 50.120 50.1000 50.120 1010 3
2 2024-01-03 08:15:00 50.120 50.120 50.1200 50.120 1000 1
3 2024-01-03 08:20:00 50.120 50.200 50.1200 50.190 2877 11
4 2024-01-03 08:25:00 50.130 50.200 50.1300 50.200 916 3
... ... ... ... ... ... ... ...
379 2024-01-04 15:40:00 50.340 50.355 50.3119 50.315 108918 542
380 2024-01-04 15:45:00 50.315 50.320 50.2650 50.290 143726 740
381 2024-01-04 15:50:00 50.290 50.310 50.2350 50.240 193367 1062
382 2024-01-04 15:55:00 50.250 50.250 50.1000 50.110 796990 3821
383 2024-01-04 16:00:00 50.110 50.140 50.0350 50.090 2290395 8271

384 rows × 7 columns

Multiple Symbol Retrieval#

Retrieve Multiple Symbols by submitting an array of symbols into otp.run.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Specify the fields to return
data = data[['PRICE', 'SIZE']]

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols=['CSCO', 'MSFT'])
result
{'CSCO':                             Time    PRICE  SIZE
 0  2024-01-03 09:30:00.065443591  50.0200     2
 1  2024-01-03 09:30:00.111130049  50.1600     3
 2  2024-01-03 09:30:00.127459523  50.1700   100
 3  2024-01-03 09:30:00.128498068  50.1700     5
 4  2024-01-03 09:30:00.135190071  50.1300    46
 ..                           ...      ...   ...
 95 2024-01-03 09:30:00.897416820  50.0800    51
 96 2024-01-03 09:30:00.897444128  50.0800    29
 97 2024-01-03 09:30:00.897474806  50.0800    12
 98 2024-01-03 09:30:00.897476583  50.0800     2
 99 2024-01-03 09:30:00.897670983  50.0797   100
 
 [100 rows x 3 columns],
 'MSFT':                             Time    PRICE  SIZE
 0  2024-01-03 09:30:00.001141139  368.990     9
 1  2024-01-03 09:30:00.001232095  369.000    10
 2  2024-01-03 09:30:00.001348140  369.000    90
 3  2024-01-03 09:30:00.001835253  368.995     1
 4  2024-01-03 09:30:00.002218671  368.990   100
 ..                           ...      ...   ...
 95 2024-01-03 09:30:00.382076572  369.000     1
 96 2024-01-03 09:30:00.396730897  369.095     3
 97 2024-01-03 09:30:00.396780282  369.095     3
 98 2024-01-03 09:30:00.405154725  369.120     4
 99 2024-01-03 09:30:00.427825042  369.050    20
 
 [100 rows x 3 columns]}

Wild Card Symbol Selection#

Results for a wildcarded list of symbols can be retrieved by passing a pattern to otp.Symbols and merging across the matching symbols, which is the equivalent of SQL’s SYMBOL_NAME LIKE 'BA%'.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')

# Merge every symbol starting with BA into a single stream.
data = otp.merge([data],
                 symbols=otp.Symbols('LSE_SAMPLE', pattern='BA%', for_tick_type='TRD'),
                 identify_input_ts=True)
data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 1, 4),
                 timezone='UTC')
result
Time DELETED_TIME EXCH_TIME MMT_ALGO_IND MMT_BENCHMARK_IND MMT_CROSS_IND MMT_DEFERRAL_TYPE MMT_DIVIDEND_IND MMT_DUP_IND MMT_MKT_MECH ... PRICE PUB_VENUE SIZE TICK_STATUS TRADE_CURRENCY TRADE_ID TRADE_TYPE TRADE_VENUE SYMBOL_NAME TICK_TYPE
0 2024-01-03 07:15:10.163 1970-01-01 2024-01-02 17:55:38.774000 - - - - - - 4 ... 2330.5000 500 0 GBX 172885159248560192 OB XLON BATS TRD
1 2024-01-03 07:15:10.164 1970-01-01 2024-01-02 17:55:40.715000 - - - - - - 4 ... 406.5599 12701 0 GBX 186395958130671680 OB XLON BAB TRD
2 2024-01-03 07:15:10.168 1970-01-01 2024-01-02 17:55:53.683000 - - - - - - 4 ... 155.0000 400 0 GBX 233679812912959600 OB XLON BARC TRD
3 2024-01-03 07:47:09.677 1970-01-01 2024-01-03 07:47:09.540000 - - - - - - 4 ... 547.3800 ECEU 1 0 ZAR 146592540646342720 OB XOFF BATS TRD
4 2024-01-03 07:47:09.677 1970-01-01 2024-01-03 07:47:09.540000 - - - - - - 4 ... 547.3800 ECEU 63 0 ZAR 151096140273713216 OB XOFF BATS TRD
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
995 2024-01-03 08:02:15.271 1970-01-01 2024-01-03 08:02:15.269447 H - - - - - 1 ... 155.9600 946 0 GBX 911727751333461 AT XLON BARC TRD
996 2024-01-03 08:02:15.271 1970-01-01 2024-01-03 08:02:15.269447 H - - - - - 1 ... 155.9600 2780 0 GBX 911727751333462 AT XLON BARC TRD
997 2024-01-03 08:02:15.398 1970-01-01 2024-01-03 08:00:12.553000 - - - - - - 4 ... 2317.5000 ECEU 0 0 GBX 535897036620464192 OB XOFF BATS TRD
998 2024-01-03 08:02:15.696 1970-01-01 2024-01-03 08:00:12.680000 - - - - - - 4 ... 2317.5000 ECEU 0 0 GBX 634976228422615104 OB XOFF BATS TRD
999 2024-01-03 08:02:17.857 1970-01-01 2024-01-03 08:02:17.856120 H - - - - - 1 ... 2350.0000 125 0 GBX 911727671641834 AT XLON BATS TRD

1000 rows × 28 columns

Identify Trading Days, Half Days & Holidays#

The mkt_activity() method can be used to identify both trading days and market holidays.
It adds a MKT_ACTIVITY field with a value for the specified calendar.
The first character represents the day type: R (Regular), L (Half Day), H (Holiday), W (Weekend); the second represents the general trading period.

Fetch daily bar data for HD stock, filtered by market activity and exchange, then retrieve the closing price and activity code for January 2024.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data.mkt_activity(calendar_name='CLOUD_DB_US_COMP')
data = data.where(data['EXCHANGE'] == '')
data = data[['CLOSE', 'MKT_ACTIVITY']]

result = otp.run(data,
                 start=otp.dt(2024, 1, 1),
                 end=otp.dt(2024, 2, 1),
                 timezone='UTC',
                 symbols='HD')
result
Time CLOSE MKT_ACTIVITY
0 2024-01-03 01:15:00 345.08 R
1 2024-01-04 01:15:00 338.26 R
2 2024-01-05 01:15:00 338.59 R
3 2024-01-06 01:15:00 342.94 R
4 2024-01-09 01:15:00 347.93 R
... ... ... ...
15 2024-01-25 01:15:00 347.27 R
16 2024-01-26 01:15:00 350.97 R
17 2024-01-27 01:15:00 355.30 R
18 2024-01-30 01:15:00 355.70 R
19 2024-01-31 01:15:00 357.10 R

20 rows × 3 columns

Identifying General Trading Sessions#

The mkt_activity() method can be used to identify general trading sessions intraday.
It returns Rb (Pre Market), Rr (Trading), Ra (Post Market), R1 (Morning), Rx (Lunch), R2 (Afternoon).

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.mkt_activity(calendar_name='CLOUD_DB_US_COMP')
data = data[['PRICE', 'SIZE', 'COND', 'EXCHANGE', 'MKT_ACTIVITY']]

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 29, 30),
                 end=otp.dt(2024, 1, 3, 9, 30, 0, 200000),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time PRICE SIZE COND EXCHANGE MKT_ACTIVITY
0 2024-01-03 09:29:38.014214683 50.04 10 @ TI P Rb
1 2024-01-03 09:29:38.570056786 50.04 89 @FTI P Rb
2 2024-01-03 09:29:50.768847900 50.06 2 @FTI P Rb
3 2024-01-03 09:29:59.990898650 50.16 1995 @ T Q Rb
4 2024-01-03 09:29:59.990941169 50.16 471 @FT Q Rb
5 2024-01-03 09:30:00.065443591 50.02 2 @ I Z Rr
6 2024-01-03 09:30:00.111130049 50.16 3 @ I Z Rr
7 2024-01-03 09:30:00.127459523 50.17 100 @ V Rr
8 2024-01-03 09:30:00.128498068 50.17 5 @F I Z Rr
9 2024-01-03 09:30:00.135190071 50.13 46 @FTI Q Rr

Join Trades to Prevailing Quotes#

Retrieve trades joined to prevailing quotes based on an “as of” join.
Both Trade and Quote Data sources are defined, and then joined by time using otp.join_by_time.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte[['BID_PRICE', 'ASK_PRICE']]
data = otp.join_by_time([trd, qte])

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time PRICE SIZE BID_PRICE ASK_PRICE
0 2024-01-03 09:30:00.065443591 50.0200 2 49.50 50.18
1 2024-01-03 09:30:00.111130049 50.1600 3 49.98 50.19
2 2024-01-03 09:30:00.127459523 50.1700 100 49.98 50.19
3 2024-01-03 09:30:00.128498068 50.1700 5 50.00 50.17
4 2024-01-03 09:30:00.135190071 50.1300 46 50.00 50.17
... ... ... ... ... ...
95 2024-01-03 09:30:00.897416820 50.0800 51 50.05 50.12
96 2024-01-03 09:30:00.897444128 50.0800 29 50.05 50.12
97 2024-01-03 09:30:00.897474806 50.0800 12 50.05 50.10
98 2024-01-03 09:30:00.897476583 50.0800 2 50.05 50.10
99 2024-01-03 09:30:00.897670983 50.0797 100 50.06 50.12

100 rows × 5 columns

Prevailing Price Retrieval#

To return the most recent / prevailing trade price as of a specific time, set parameter otp.DataSource(back_to_first_tick=...) to the lookback period in seconds (in case where the trade does not occur at the exact timestamp).
Then take the last tick and query a zero-length window ending at that time.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD',
                      back_to_first_tick=86400,
                      keep_first_tick_timestamp='ORIGINAL_TIMESTAMP')
data = data.limit(2)

as_of = otp.dt(2024, 1, 3, 8, 22, 51, 848000)
result = otp.run(data,
                 start=as_of,
                 end=as_of,
                 timezone='UTC',
                 symbols='VOD')
result
Time ORIGINAL_TIMESTAMP EXCH_TIME TRADE_ID PRICE SIZE TRADE_TYPE TRADE_VENUE PUB_VENUE TRADE_CURRENCY ... MMT_DIVIDEND_IND MMT_OFF_BOOK_AUTO_IND MMT_PRICE_FORMING_IND MMT_ALGO_IND MMT_PUB_MODE MMT_DEFERRAL_TYPE MMT_DUP_IND DELETED_TIME TICK_STATUS OMDSEQ
0 2024-01-03 08:22:51.848 2024-01-03 08:22:50.847 2024-01-03 08:22:50.763 427637175188410480 0.818 200 OB SINT ECEU EUR ... - P - - - - 1970-01-01 0 1

1 rows × 27 columns

Point in Time Trade Snapshot Across Venue#

A specific point in time is selected as the query interval.
All symbols are retrieved by merging across otp.Symbols.
A lookback of up to 1 day (86400s) via back_to_first_tick returns the prevailing trade before the selected time, per symbol.

import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD', back_to_first_tick=86400)
trd = trd[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]

# Keep only the last (prevailing) tick up to the snapshot time, per symbol.
trd = trd.last()

# Merge the prevailing trade for every symbol into a single stream.
# identify_input_ts adds the SYMBOL_NAME of each tick to the output.
merged = otp.merge([trd],
                   symbols=otp.Symbols('US_COMP_SAMPLE', for_tick_type='TRD'),
                   identify_input_ts=True)

snapshot_time = otp.dt(2024, 1, 3, 12, 30)
result = otp.run(merged,
                 start=snapshot_time,
                 end=snapshot_time,
                 timezone='America/New_York')
result
Time PRICE SIZE COND EXCHANGE SYMBOL_NAME TICK_TYPE
0 2024-01-03 12:30:00 132.8100 20 I N A TRD
1 2024-01-03 12:30:00 13.0250 200 @ D AAL TRD
2 2024-01-03 12:30:00 183.9276 1 @ I D AAPL TRD
3 2024-01-03 12:30:00 160.2700 11 I D ABBV TRD
4 2024-01-03 12:30:00 132.7700 97 @F I Q ABNB TRD
... ... ... ... ... ... ... ...
495 2024-01-03 12:30:00 112.2700 1 I T XYL TRD
496 2024-01-03 12:30:00 129.2700 1 F I N YUM TRD
497 2024-01-03 12:30:00 120.9700 100 U ZBH TRD
498 2024-01-03 12:30:00 255.1299 4 @ I D ZBRA TRD
499 2024-01-03 12:30:00 194.5000 21 I T ZTS TRD

500 rows × 7 columns

Combined Point in Time Trade and Quote Snapshot Across a Venue#

A specific point in time is selected as the query interval.
All symbols are retrieved by merging across otp.Symbols.
A lookback of up to 1 day (86400s) via back_to_first_tick returns the prevailing trade and quote before the selected time, per symbol.

import onetick.py as otp

trd = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD', back_to_first_tick=86400)
trd = trd[['PRICE', 'SIZE']]
trd = trd.last()

qte = otp.DataSource(db='LSE_SAMPLE', tick_type='QTE', back_to_first_tick=86400)
qte = qte[['BID_PRICE', 'ASK_PRICE']]
qte = qte.last()

# Combine the prevailing trade and prevailing quote for each symbol into one tick.
# on='all' cross-joins the (at most one) trade tick with the (at most one) quote tick;
# unlike on='same_size', it degrades gracefully to no output for a symbol
# that lacks a prevailing trade or quote within the lookback window, rather than erroring.
data = otp.join(trd, qte, on='all')

# Merge the combined snapshot for every symbol into a single stream.
# identify_input_ts adds the SYMBOL_NAME of each tick to the output.
data = otp.merge([data],
                 symbols=otp.Symbols('LSE_SAMPLE', for_tick_type='QTE'),
                 identify_input_ts=True)

data = data.limit(1000)

snapshot_time = otp.dt(2024, 1, 3, 12, 30)
result = otp.run(data,
                 start=snapshot_time,
                 end=snapshot_time,
                 timezone='Europe/London')
result
Time PRICE SIZE BID_PRICE ASK_PRICE SYMBOL_NAME TICK_TYPE
0 2024-01-03 12:30:00 57.9000 5 55.90 59.200 0A1U
1 2024-01-03 12:30:00 13.8588 18 13.20 14.040 0A2O
2 2024-01-03 12:30:00 1.0985 200 0.61 NaN 0AB4
3 2024-01-03 12:30:00 68.0000 31 72.20 76.200 0HCI
4 2024-01-03 12:30:00 126.9600 0 134.80 142.200 0HD6
... ... ... ... ... ... ... ...
995 2024-01-03 12:30:00 29898.8000 3 29865.00 29888.000 DJEL
996 2024-01-03 12:30:00 377.9700 16 377.07 377.380 DJEU
997 2024-01-03 12:30:00 5488.6499 1 5456.00 5464.000 DJMC
998 2024-01-03 12:30:00 3742.0000 1 3723.50 3727.500 DJSC
999 2024-01-03 12:30:00 0.0415 12062531 0.04 0.045 DKE

1000 rows × 7 columns

Query Historical Database Availability Status#

Retrieve Recent Processing Events for the US_COMP Database.
Database load status is recorded in the PROC_EVENTS tick type of the DB_INFO database, one record per processing event.
The database being queried is passed as the symbol, and filtering on DB_NAME restricts the events to that database.

import onetick.py as otp

data = otp.DataSource(db='DB_INFO', tick_type='PROC_EVENTS')
data = data.where(data['DB_NAME'] == 'US_COMP')

result = otp.run(data,
                 # Include events from the last 7 days up to now
                 start=otp.now() - otp.Day(7),
                 end=otp.now(),
                 timezone='UTC',
                 symbols='US_COMP')
result
Time DB_NAME DB_DESCRIPTION TIMEZONE DATA_DATE EVENT_TIME EVENT_NAME SOURCE_FILE_NAME OMDSEQ
0 2026-10-22 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261022 2026-10-23 02:07:56 Load started 6472
1 2026-10-22 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261022 2026-10-23 03:07:53 Load finished 6504
2 2026-10-23 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261023 2026-10-24 01:51:45 Load started 6153
3 2026-10-23 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261023 2026-10-24 02:42:23 Load finished 6182
4 2026-10-26 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261026 2026-10-27 01:51:40 Load started 6134
5 2026-10-26 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261026 2026-10-27 02:43:18 Load finished 6160
6 2026-10-27 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261027 2026-10-28 01:36:19 Load started 6591
7 2026-10-27 US_COMP US Consolidated Equities (exc. OTC) America/New_York 20261027 2026-10-28 02:21:00 Load finished 6617

Ranking#

The ranking() method can be used to rank results.
It adds a RANKING field with the position of each tick in the sort order, sorted by given field(s).
Rows with the same value still get different ranks, matching SQL’s ROW_NUMBER().

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]

data = data.ranking({'SIZE': 'desc'})

data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 4, 16),
                 timezone='UTC',
                 symbols='VOD')
result
Time PRICE SIZE RANKING
0 2024-01-03 08:00:06.232 70.000 184613 22
1 2024-01-03 08:00:06.233 70.010 140 9310
2 2024-01-03 08:00:06.287 70.010 500 8145
3 2024-01-03 08:00:08.113 70.104 2800 3992
4 2024-01-03 08:00:09.380 70.137 49 9974
... ... ... ... ...
995 2024-01-03 08:49:23.760 70.650 711 7343
996 2024-01-03 08:49:28.304 70.630 1 11639
997 2024-01-03 08:49:28.304 70.630 969 6951
998 2024-01-03 08:50:29.344 70.650 4716 1909
999 2024-01-03 08:50:40.916 70.620 751 7197

1000 rows × 4 columns

Run SQL#

Execute a SQL statement using otp.SqlQuery.

import onetick.py as otp

sql_statement = """
    SELECT * FROM US_COMP_SAMPLE.TRD
    WHERE SYMBOL_NAME='CSCO'
    and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
    and TIMESTAMP < '2024-01-03 09:40:00 America/New_York'
    LIMIT 100
"""

sql_query = otp.SqlQuery(sql_statement)
result = otp.run(sql_query)
result
Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM TRADE_ID PARTICIPANT_TIME TRF_TIME OMDSEQ SYMBOL_NAME TICK_TYPE
0 2024-01-03 09:30:00.065443591 Z @ I N 0 CSCO 50.0200 1969-12-31 19:00:00 0 2 0 169103 42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00.000000000 0 CSCO TRD
1 2024-01-03 09:30:00.111130049 Z @ I N 0 CSCO 50.1600 1969-12-31 19:00:00 0 3 0 169148 43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00.000000000 0 CSCO TRD
2 2024-01-03 09:30:00.127459523 V @ N 0 CSCO 50.1700 1969-12-31 19:00:00 0 100 0 169181 13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00.000000000 0 CSCO TRD
3 2024-01-03 09:30:00.128498068 Z @F I N 1 CSCO 50.1700 1969-12-31 19:00:00 0 5 0 169182 44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00.000000000 0 CSCO TRD
4 2024-01-03 09:30:00.135190071 Q @FTI N 1 CSCO 50.1300 1969-12-31 19:00:00 0 46 0 169193 349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00.000000000 0 CSCO TRD
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
95 2024-01-03 09:30:00.897416820 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 51 0 171857 365 2024-01-03 09:30:00.897398456 1969-12-31 19:00:00.000000000 3 CSCO TRD
96 2024-01-03 09:30:00.897444128 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 29 0 171858 366 2024-01-03 09:30:00.897428791 1969-12-31 19:00:00.000000000 4 CSCO TRD
97 2024-01-03 09:30:00.897474806 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 12 0 171859 367 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000 5 CSCO TRD
98 2024-01-03 09:30:00.897476583 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 2 0 171860 368 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000 6 CSCO TRD
99 2024-01-03 09:30:00.897670983 D @ N Q 0 CSCO 50.0797 1969-12-31 19:00:00 0 100 0 171862 100 2024-01-03 09:30:00.896374950 2024-01-03 09:30:00.897637536 7 CSCO TRD

100 rows × 20 columns

Conditional Output with CASE#

Conditional fields can be added through apply() method, where the python function’s if/return statements are translated to a OneTick CASE expression.
Conditions are tested in order and the first match wins; if no condition matches, the final return provides the equivalent of SQL’s “else” clause.

Adding Conditional Field for Trade Class.

import onetick.py as otp

def trade_class(row):
    if row['SIZE'] > 1000:
        return 'LARGE TRADE'
    if row['SIZE'] > 100:
        return 'MEDIUM TRADE'
    return 'SMALL TRADE'

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
data['TRADE_CLASS'] = data.apply(trade_class)

data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 13),
                 end=otp.dt(2024, 1, 4, 14),
                 timezone='UTC',
                 symbols='CSCO')
result
Time PRICE SIZE TRADE_CLASS
0 2024-01-03 13:00:00.123988388 50.210 1 SMALL TRADE
1 2024-01-03 13:00:00.215279605 50.200 1 SMALL TRADE
2 2024-01-03 13:00:00.416529139 50.180 100 SMALL TRADE
3 2024-01-03 13:00:00.417204341 50.080 50 SMALL TRADE
4 2024-01-03 13:00:00.461716397 50.150 661 MEDIUM TRADE
... ... ... ... ...
995 2024-01-03 14:30:02.803714549 50.150 198 MEDIUM TRADE
996 2024-01-03 14:30:02.812945805 50.150 1 SMALL TRADE
997 2024-01-03 14:30:02.979190657 50.150 1 SMALL TRADE
998 2024-01-03 14:30:03.009190965 50.155 300 MEDIUM TRADE
999 2024-01-03 14:30:03.021746503 50.150 1 SMALL TRADE

1000 rows × 4 columns

Limiting Returned Rows With Offset#

The set of returned rows can be limited and offset using python slice syntax on the Source.
data[5:15] skips the first 5 ticks and returns the next 10, the equivalent of LIMIT 10 OFFSET 5.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE', 'EXCHANGE']]
data = data[5:15]

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 1, 4),
                 timezone='UTC',
                 symbols='CSCO')
result
Time PRICE SIZE EXCHANGE
0 2024-01-03 00:05:06.042217019 50.4896 1 D
1 2024-01-03 00:07:11.737983252 50.4897 1 D
2 2024-01-03 00:12:17.787582635 50.4900 1 D
3 2024-01-03 00:13:16.630750984 50.4900 3 P
4 2024-01-03 00:14:14.187418230 50.4900 100 P
5 2024-01-03 00:20:42.328826087 50.4900 45 P
6 2024-01-03 00:20:42.328826226 50.4900 25 P
7 2024-01-03 00:27:42.682762486 50.5100 40 D
8 2024-01-03 00:28:30.372465445 50.6500 2 D
9 2024-01-03 00:33:22.502436480 50.5000 3 P

Prevailing Quotes for Trades#

To retrieve trades joined to prevailing quotes, an “as of” join is specified using otp.join_by_time, with the trade source listed first so the trade timestamp leads the join.

import onetick.py as otp

trd = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]

qte = otp.DataSource(db='LSE_SAMPLE', tick_type='QTE')
qte['QTE_TIME'] = qte['TIMESTAMP']
qte = qte[['QTE_TIME', 'BID_PRICE', 'ASK_PRICE']]

data = otp.join_by_time([trd, qte])
data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 1, 4),
                 timezone='UTC',
                 symbols='VOD')
result
Time PRICE SIZE QTE_TIME BID_PRICE ASK_PRICE
0 2024-01-03 07:15:10.133 69.760 40000 2024-01-03 05:00:07.876 69.00 71.82
1 2024-01-03 08:00:06.232 70.000 184613 2024-01-03 08:00:06.226 70.01 70.19
2 2024-01-03 08:00:06.233 70.010 140 2024-01-03 08:00:06.226 70.01 70.19
3 2024-01-03 08:00:06.287 70.010 500 2024-01-03 08:00:06.287 70.01 70.19
4 2024-01-03 08:00:08.113 70.104 2800 2024-01-03 08:00:07.747 70.08 70.18
... ... ... ... ... ... ...
995 2024-01-03 08:49:23.060 70.630 711 2024-01-03 08:49:23.037 70.64 70.66
996 2024-01-03 08:49:23.760 70.650 711 2024-01-03 08:49:23.758 70.63 70.66
997 2024-01-03 08:49:28.304 70.630 1 2024-01-03 08:49:28.304 70.63 70.66
998 2024-01-03 08:49:28.304 70.630 969 2024-01-03 08:49:28.304 70.63 70.66
999 2024-01-03 08:50:29.344 70.650 4716 2024-01-03 08:50:29.343 70.63 70.66

1000 rows × 6 columns

Quotes for Specific Trading Sessions#

Retrieving Valid Quotes for the specified Market Phase or Trading Session.

Quotes are “as of” joined to the prevailing Market Phase from the MKT tick type using otp.join_by_time, then filtered to Continuous Trading (OMD_STATUS == 'T').
Quotes are additionally filtered to a valid size before the join.

Valid Quotes for LSE VOD during the Specified Market Phase Trading Session.

import onetick.py as otp

qte = otp.DataSource(db='LSE_SAMPLE', tick_type='QTE')
qte = qte[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
# Filtering for Valid Quotes
qte = qte.where((qte['BID_SIZE'] > 0) & (qte['ASK_SIZE'] > 0))

mkt = otp.DataSource(db='LSE_SAMPLE', tick_type='MKT')
mkt = mkt[['OMD_STATUS']]

# "as of" join, returning the prevailing market phase for each quote.
data = otp.join_by_time([qte, mkt])

# After the join, filter to T - Continuous Trading.
data = data.where(data['OMD_STATUS'] == 'T')
data = data[['BID_PRICE', 'ASK_PRICE', 'OMD_STATUS']]

data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 1, 4),
                 timezone='Europe/London',
                 symbols='VOD')
result
Time BID_PRICE ASK_PRICE OMD_STATUS
0 2024-01-03 08:00:06.226 70.00 70.19 T
1 2024-01-03 08:00:06.226 70.01 70.19 T
2 2024-01-03 08:00:06.226 70.01 70.19 T
3 2024-01-03 08:00:06.271 70.01 70.19 T
4 2024-01-03 08:00:06.287 70.01 70.19 T
... ... ... ... ...
995 2024-01-03 08:02:13.086 70.48 70.57 T
996 2024-01-03 08:02:13.092 70.49 70.57 T
997 2024-01-03 08:02:13.096 70.47 70.57 T
998 2024-01-03 08:02:13.096 70.48 70.57 T
999 2024-01-03 08:02:16.186 70.48 70.57 T

1000 rows × 4 columns

Top 10 Per Group#

The ranking() method can be used to rank results, grouped by a field with group_by, which is the equivalent of SQL’s PARTITION BY.
Filtering the RANKING field afterwards returns the top N per group.

import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE', 'TRADE_VENUE']]

# Sort direction is descending, matching "order by SIZE desc".
data = data.ranking({'SIZE': 'desc'}, show_rank_as='order', group_by=['TRADE_VENUE'])
# get top 10 values
data = data.where(data['RANKING'] <= 10)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 8),
                 end=otp.dt(2024, 1, 4, 16),
                 timezone='UTC',
                 symbols='VOD')
result
Time PRICE SIZE TRADE_VENUE RANKING
0 2024-01-03 08:00:06.232 70.0000 184613 XLON 6
1 2024-01-03 08:42:45.718 70.6700 191501 XLON 5
2 2024-01-03 08:43:02.860 70.6500 216821 XLON 4
3 2024-01-03 08:52:45.440 70.6000 331071 XLON 3
4 2024-01-03 08:55:18.838 70.6600 37989 SINT 8
... ... ... ... ... ...
25 2024-01-04 08:59:17.062 69.7460 131216 XLON 8
26 2024-01-04 09:05:41.159 69.6495 120000 XLON 9
27 2024-01-04 12:33:31.456 70.1220 100557 XLON 10
28 2024-01-04 14:20:35.607 70.4500 35000 SINT 9
29 2024-01-04 14:26:11.598 70.5000 174573 SINT 1

30 rows × 5 columns

Top 25 Per Venue by Daily Traded Value#

Query total traded value by venue for a single trading day.
Returns the top 25 venues ranked by total trading value (close * volume).

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='DAY')
data = data[['EXCHANGE', 'CLOSE', 'VOLUME']]

# Notional value of each venue's daily bar, summed per venue below.
data['TRADED_VALUE'] = data['CLOSE'] * data['VOLUME']

# Aggregate trades by exchange.
data = data.agg({'TRADED_VALUE': otp.agg.sum('TRADED_VALUE')}, group_by=['EXCHANGE'])

# Sort by highest traded value first and return only the top 25 venues.
data = data.sort(['TRADED_VALUE'], ascending=False)
data = data.limit(25)

result = otp.run(data,
                 start=otp.dt(2024, 2, 6),
                 end=otp.dt(2024, 2, 7),
                 timezone='America/New_York',
                 symbols='CSCO')
result
Time EXCHANGE TRADED_VALUE
0 2024-02-07 9.629045e+08
1 2024-02-07 D 3.656758e+08
2 2024-02-07 PRIM 3.229528e+08
3 2024-02-07 Q 3.229528e+08
4 2024-02-07 P 6.488997e+07
... ... ... ...
13 2024-02-07 B 5.257048e+06
14 2024-02-07 H 3.076777e+06
15 2024-02-07 X 2.988004e+06
16 2024-02-07 A 1.465464e+06
17 2024-02-07 M 1.095861e+06

18 rows × 3 columns

Calculate Spread and Mid from a Venue#

Venue datasets include top-of-book quotes in the QTE tick type.

SPREAD = ASK_PRICE - BID_PRICE
MID_PRICE = (BID_PRICE + ASK_PRICE) / 2
import onetick.py as otp

data = otp.DataSource(db='LSE_SAMPLE', tick_type='QTE')
data = data.where((data['BID_SIZE'] > 0) & (data['ASK_SIZE'] > 0))

data['MID_PRICE'] = (data['BID_PRICE'] + data['ASK_PRICE']) / 2
data['SPREAD'] = data['ASK_PRICE'] - data['BID_PRICE']
data = data[['BID_PRICE', 'ASK_PRICE', 'MID_PRICE', 'SPREAD']]

data = data.limit(1000)

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 BID_PRICE ASK_PRICE MID_PRICE SPREAD
0 2024-01-03 08:00:00.156 75.34 59.30 67.320 -16.04
1 2024-01-03 08:00:02.864 75.34 59.30 67.320 -16.04
2 2024-01-03 08:00:02.865 73.13 59.30 66.215 -13.83
3 2024-01-03 08:00:06.225 73.13 59.30 66.215 -13.83
4 2024-01-03 08:00:06.225 73.13 59.30 66.215 -13.83
... ... ... ... ... ...
995 2024-01-03 08:02:05.342 70.50 70.60 70.550 0.10
996 2024-01-03 08:02:05.342 70.50 70.60 70.550 0.10
997 2024-01-03 08:02:05.342 70.50 70.60 70.550 0.10
998 2024-01-03 08:02:05.342 70.50 70.59 70.545 0.09
999 2024-01-03 08:02:05.347 70.50 70.59 70.545 0.09

1000 rows × 5 columns

Calculate Spread and Mid Statistics from a Venue#

Venue datasets include top-of-book quotes in the QTE tick type.
SPREAD and MID_PRICE are computed per quote, then aggregated across the period using the time weighted average, high and low.

import onetick.py as otp

qte = otp.DataSource(db='LSE_SAMPLE', tick_type='QTE')
qte = qte[['BID_PRICE', 'ASK_PRICE', 'BID_SIZE', 'ASK_SIZE']]
qte = qte.where((qte['BID_SIZE'] > 0) & (qte['ASK_SIZE'] > 0))

# Calculate the Mid Price and Spread
qte['MID_PRICE'] = (qte['BID_PRICE'] + qte['ASK_PRICE']) / 2
qte['SPREAD'] = qte['ASK_PRICE'] - qte['BID_PRICE']

data = qte.agg({
    'TW_AVG_MID_PRICE': otp.agg.tw_average('MID_PRICE'),
    'HIGH_MID_PRICE': otp.agg.max('MID_PRICE'),
    'LOW_MID_PRICE': otp.agg.min('MID_PRICE'),
    'TW_AVG_SPREAD': otp.agg.tw_average('SPREAD'),
    'HIGH_SPREAD': otp.agg.max('SPREAD'),
    'LOW_SPREAD': otp.agg.min('SPREAD'),
    'QTE_COUNT': otp.agg.count(),
})

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 TW_AVG_MID_PRICE HIGH_MID_PRICE LOW_MID_PRICE TW_AVG_SPREAD HIGH_SPREAD LOW_SPREAD QTE_COUNT
0 2024-01-03 16:00:00 70.251292 70.755 64.9 0.024653 0.41 -16.04 78313