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 |