Filtering#
This section contains 13 examples for Filtering 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__'
Adding Multiple Filters#
Applying multiple filters to the dataset as two separate operations.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE', 'EXCHANGE']]
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE']
data = data.where(data['EXCHANGE'] == 'N')
data = data.where(data['SIZE'] > 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
Filtering on a Single Trade Condition#
Filtering that a trade condition is present in the COND field of the US_COMP_SAMPLE database.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data, _= data[data['COND'].str.contains('I')]
data = data[:100]
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
| Time | COND | CORR | DELETED_TIME | EXCHANGE | OMDSEQ | PARTICIPANT_TIME | PRICE | SEQ_NUM | SIZE | SOURCE | STOP_STOCK | TICKER | TICK_STATUS | TRADE_ID | TRF | TRF_TIME | TTE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 04:00:00.019128245 | @ TI | 0 | 1969-12-31 19:00:00 | K | 0 | 2024-01-03 04:00:00.000052000 | 50.46 | 1496 | 1 | N | CSCO | 0 | 1 | 1969-12-31 19:00:00 | 0 | ||
| 1 | 2024-01-03 04:00:00.905488831 | @ TI | 0 | 1969-12-31 19:00:00 | P | 0 | 2024-01-03 04:00:00.905146127 | 50.05 | 1531 | 5 | N | CSCO | 0 | 1 | 1969-12-31 19:00:00 | 0 | ||
| 2 | 2024-01-03 04:00:01.278537460 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 04:00:01.278518875 | 50.29 | 1540 | 13 | N | CSCO | 0 | 1 | 1969-12-31 19:00:00 | 1 | ||
| 3 | 2024-01-03 04:00:01.278746794 | @FTI | 0 | 1969-12-31 19:00:00 | P | 1 | 2024-01-03 04:00:01.278404559 | 50.29 | 1541 | 13 | N | CSCO | 0 | 2 | 1969-12-31 19:00:00 | 1 | ||
| 4 | 2024-01-03 04:00:03.090763650 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 04:00:03.090746743 | 50.29 | 1565 | 1 | N | CSCO | 0 | 2 | 1969-12-31 19:00:00 | 1 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-01-03 06:42:37.790073044 | @FTI | 0 | 1969-12-31 19:00:00 | P | 0 | 2024-01-03 06:42:37.789729308 | 50.12 | 43556 | 1 | N | CSCO | 0 | 39 | 1969-12-31 19:00:00 | 1 | ||
| 96 | 2024-01-03 06:42:37.804968776 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 06:42:37.804953171 | 50.17 | 43557 | 10 | N | CSCO | 0 | 54 | 1969-12-31 19:00:00 | 1 | ||
| 97 | 2024-01-03 06:42:37.805831496 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 06:42:37.805816609 | 50.17 | 43558 | 5 | N | CSCO | 0 | 55 | 1969-12-31 19:00:00 | 1 | ||
| 98 | 2024-01-03 06:42:37.816687662 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 06:42:37.816672176 | 50.17 | 43559 | 5 | N | CSCO | 0 | 56 | 1969-12-31 19:00:00 | 1 | ||
| 99 | 2024-01-03 06:42:38.014732702 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 06:42:38.014717674 | 50.19 | 43562 | 5 | N | CSCO | 0 | 57 | 1969-12-31 19:00:00 | 1 |
100 rows × 18 columns
Filtering on Multiple Trade Conditions#
Filtering that any of the specified trade conditions are present
in the COND field of the US_COMP database, by using character_present().
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.character_present(data['COND'], 'O6TUHILNRWZ47QMBCGPV')
data = data[:100]
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
| Time | COND | CORR | DELETED_TIME | EXCHANGE | OMDSEQ | PARTICIPANT_TIME | PRICE | SEQ_NUM | SIZE | SOURCE | STOP_STOCK | TICKER | TICK_STATUS | TRADE_ID | TRF | TRF_TIME | TTE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 04:00:00.019128245 | @ TI | 0 | 1969-12-31 19:00:00 | K | 0 | 2024-01-03 04:00:00.000052000 | 50.46 | 1496 | 1 | N | CSCO | 0 | 1 | 1969-12-31 19:00:00 | 0 | ||
| 1 | 2024-01-03 04:00:00.905488831 | @ TI | 0 | 1969-12-31 19:00:00 | P | 0 | 2024-01-03 04:00:00.905146127 | 50.05 | 1531 | 5 | N | CSCO | 0 | 1 | 1969-12-31 19:00:00 | 0 | ||
| 2 | 2024-01-03 04:00:01.278537460 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 04:00:01.278518875 | 50.29 | 1540 | 13 | N | CSCO | 0 | 1 | 1969-12-31 19:00:00 | 1 | ||
| 3 | 2024-01-03 04:00:01.278746794 | @FTI | 0 | 1969-12-31 19:00:00 | P | 1 | 2024-01-03 04:00:01.278404559 | 50.29 | 1541 | 13 | N | CSCO | 0 | 2 | 1969-12-31 19:00:00 | 1 | ||
| 4 | 2024-01-03 04:00:03.090763650 | @FTI | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 04:00:03.090746743 | 50.29 | 1565 | 1 | N | CSCO | 0 | 2 | 1969-12-31 19:00:00 | 1 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-01-03 06:10:45.228483994 | @ T | 0 | 1969-12-31 19:00:00 | P | 3 | 2024-01-03 06:10:45.228132116 | 50.20 | 36285 | 788 | N | CSCO | 0 | 35 | 1969-12-31 19:00:00 | 0 | ||
| 96 | 2024-01-03 06:10:52.079452806 | @ T | 0 | 1969-12-31 19:00:00 | P | 0 | 2024-01-03 06:10:52.079105701 | 50.20 | 36303 | 100 | N | CSCO | 0 | 36 | 1969-12-31 19:00:00 | 0 | ||
| 97 | 2024-01-03 06:10:52.079454393 | @ T | 0 | 1969-12-31 19:00:00 | P | 1 | 2024-01-03 06:10:52.079105701 | 50.20 | 36304 | 900 | N | CSCO | 0 | 37 | 1969-12-31 19:00:00 | 0 | ||
| 98 | 2024-01-03 06:31:08.521306739 | @FTI | 0 | 1969-12-31 19:00:00 | P | 0 | 2024-01-03 06:31:08.520961545 | 50.15 | 39749 | 1 | N | CSCO | 0 | 38 | 1969-12-31 19:00:00 | 1 | ||
| 99 | 2024-01-03 06:42:03.828154778 | @ TI | 0 | 1969-12-31 19:00:00 | K | 0 | 2024-01-03 06:42:03.827956000 | 50.17 | 43183 | 1 | N | CSCO | 0 | 10 | 1969-12-31 19:00:00 | 0 |
100 rows × 18 columns
Filtering on Excluding Multiple Trade Conditions#
Filtering that any of the specified trade conditions are not present
in the COND field of the US_COMP_SAMPLE database.
Using character_present() with parameter discard_on_match=True.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.character_present(data['COND'], 'O6TUHILNRWZ47QMBCGPV', discard_on_match=True)
data = data[:100]
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/New_York',
symbols='CSCO')
result
| Time | COND | CORR | DELETED_TIME | EXCHANGE | OMDSEQ | PARTICIPANT_TIME | PRICE | SEQ_NUM | SIZE | SOURCE | STOP_STOCK | TICKER | TICK_STATUS | TRADE_ID | TRF | TRF_TIME | TTE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.127459523 | @ | 0 | 1969-12-31 19:00:00 | V | 0 | 2024-01-03 09:30:00.065044730 | 50.170 | 169181 | 100 | N | CSCO | 0 | 13 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 1 | 2024-01-03 09:30:00.280877749 | @ | 0 | 1969-12-31 19:00:00 | D | 0 | 2024-01-03 09:30:00.280542269 | 50.002 | 169295 | 200 | N | CSCO | 0 | 94 | Q | 2024-01-03 09:30:00.280841375 | 0 | |
| 2 | 2024-01-03 09:30:00.607073913 | @F | 0 | 1969-12-31 19:00:00 | Z | 4 | 2024-01-03 09:30:00.606878000 | 50.090 | 170006 | 124 | N | CSCO | 0 | 63 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 3 | 2024-01-03 09:30:00.888302226 | @ | 0 | 1969-12-31 19:00:00 | Q | 4 | 2024-01-03 09:30:00.888283518 | 50.090 | 171782 | 109 | N | CSCO | 0 | 360 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 4 | 2024-01-03 09:30:00.889018624 | @F | 0 | 1969-12-31 19:00:00 | U | 0 | 2024-01-03 09:30:00.888812693 | 50.090 | 171787 | 100 | N | CSCO | 0 | 17 | 1969-12-31 19:00:00.000000000 | 1 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-01-03 09:30:04.203519590 | @F | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 09:30:04.203504691 | 50.220 | 185111 | 100 | N | CSCO | 0 | 537 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 96 | 2024-01-03 09:30:04.203522132 | @F | 0 | 1969-12-31 19:00:00 | Q | 1 | 2024-01-03 09:30:04.203504691 | 50.220 | 185112 | 138 | N | CSCO | 0 | 538 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 97 | 2024-01-03 09:30:04.203653496 | @ | 0 | 1969-12-31 19:00:00 | Q | 2 | 2024-01-03 09:30:04.203638794 | 50.220 | 185114 | 100 | N | CSCO | 0 | 539 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 98 | 2024-01-03 09:30:04.203816597 | @F | 0 | 1969-12-31 19:00:00 | Z | 4 | 2024-01-03 09:30:04.203620000 | 50.220 | 185121 | 296 | N | CSCO | 0 | 109 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 99 | 2024-01-03 09:30:04.203826441 | @ | 0 | 1969-12-31 19:00:00 | Q | 5 | 2024-01-03 09:30:04.203810447 | 50.220 | 185122 | 100 | N | CSCO | 0 | 540 | 1969-12-31 19:00:00.000000000 | 0 |
100 rows × 18 columns
Filtering on Specific Time Ranges#
Applying a filter on the Data Source based on specific time periods per day,
using time_filter().
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.time_filter(start_time='09:33:00', end_time='09:35:00')
data = data[:1000]
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 9, 40),
timezone='America/New_York',
symbols='CSCO')
result
| Time | COND | CORR | DELETED_TIME | EXCHANGE | OMDSEQ | PARTICIPANT_TIME | PRICE | SEQ_NUM | SIZE | SOURCE | STOP_STOCK | TICKER | TICK_STATUS | TRADE_ID | TRF | TRF_TIME | TTE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:33:00.084705868 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.084511000 | 50.05 | 262862 | 1 | N | CSCO | 0 | 305 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 1 | 2024-01-03 09:33:00.230210508 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.230021000 | 50.05 | 262931 | 2 | N | CSCO | 0 | 306 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 2 | 2024-01-03 09:33:00.320146067 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.319957000 | 50.05 | 262947 | 1 | N | CSCO | 0 | 307 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 3 | 2024-01-03 09:33:00.441710628 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.441521000 | 50.05 | 262984 | 1 | N | CSCO | 0 | 308 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 4 | 2024-01-03 09:33:00.444156451 | @F I | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 09:33:00.444135893 | 50.05 | 262986 | 2 | N | CSCO | 0 | 1142 | 1969-12-31 19:00:00.000000000 | 1 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 780 | 2024-01-03 09:34:59.152722398 | @F | 0 | 1969-12-31 19:00:00 | P | 4 | 2024-01-03 09:34:59.152378746 | 50.02 | 300893 | 100 | N | CSCO | 0 | 512 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 781 | 2024-01-03 09:34:59.263442478 | @ I | 0 | 1969-12-31 19:00:00 | D | 0 | 2024-01-03 09:34:59.263054575 | 50.01 | 300948 | 21 | N | CSCO | 0 | 1372 | Q | 2024-01-03 09:34:59.263418818 | 0 | |
| 782 | 2024-01-03 09:34:59.263934938 | @F I | 0 | 1969-12-31 19:00:00 | K | 1 | 2024-01-03 09:34:59.263744000 | 50.01 | 300949 | 1 | N | CSCO | 0 | 367 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 783 | 2024-01-03 09:34:59.263938903 | @F I | 0 | 1969-12-31 19:00:00 | K | 2 | 2024-01-03 09:34:59.263744000 | 50.01 | 300950 | 99 | N | CSCO | 0 | 368 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 784 | 2024-01-03 09:34:59.547741776 | @ I | 0 | 1969-12-31 19:00:00 | D | 0 | 2024-01-03 09:34:59.524000000 | 50.02 | 300986 | 1 | N | CSCO | 0 | 1373 | Q | 2024-01-03 09:34:59.547719733 | 0 |
785 rows × 18 columns
Filtering on Excluding Specific Time Ranges#
Applying a filter on the Data Source based on excluding specific time periods per day.
Using time_filter() with parameter discard_on_match=True.
import onetick.py as otp
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data.time_filter(start_time='09:30:00', end_time='09:33:00', discard_on_match=True)
data = data[:1000]
result = otp.run(data,
start=otp.dt(2024, 1, 3, 9, 30),
end=otp.dt(2024, 1, 3, 9, 40),
timezone='America/New_York',
symbols='CSCO')
result
| Time | COND | CORR | DELETED_TIME | EXCHANGE | OMDSEQ | PARTICIPANT_TIME | PRICE | SEQ_NUM | SIZE | SOURCE | STOP_STOCK | TICKER | TICK_STATUS | TRADE_ID | TRF | TRF_TIME | TTE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:33:00.084705868 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.084511000 | 50.05 | 262862 | 1 | N | CSCO | 0 | 305 | 1969-12-31 19:00:00 | 0 | ||
| 1 | 2024-01-03 09:33:00.230210508 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.230021000 | 50.05 | 262931 | 2 | N | CSCO | 0 | 306 | 1969-12-31 19:00:00 | 0 | ||
| 2 | 2024-01-03 09:33:00.320146067 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.319957000 | 50.05 | 262947 | 1 | N | CSCO | 0 | 307 | 1969-12-31 19:00:00 | 0 | ||
| 3 | 2024-01-03 09:33:00.441710628 | @ I | 0 | 1969-12-31 19:00:00 | Z | 0 | 2024-01-03 09:33:00.441521000 | 50.05 | 262984 | 1 | N | CSCO | 0 | 308 | 1969-12-31 19:00:00 | 0 | ||
| 4 | 2024-01-03 09:33:00.444156451 | @F I | 0 | 1969-12-31 19:00:00 | Q | 0 | 2024-01-03 09:33:00.444135893 | 50.05 | 262986 | 2 | N | CSCO | 0 | 1142 | 1969-12-31 19:00:00 | 1 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:35:19.333430053 | @F I | 0 | 1969-12-31 19:00:00 | K | 57 | 2024-01-03 09:35:19.333225000 | 50.00 | 309118 | 50 | N | CSCO | 0 | 378 | 1969-12-31 19:00:00 | 1 | ||
| 996 | 2024-01-03 09:35:19.333435686 | @F I | 0 | 1969-12-31 19:00:00 | K | 58 | 2024-01-03 09:35:19.333225000 | 50.00 | 309119 | 25 | N | CSCO | 0 | 379 | 1969-12-31 19:00:00 | 1 | ||
| 997 | 2024-01-03 09:35:19.333440538 | @ I | 0 | 1969-12-31 19:00:00 | Z | 59 | 2024-01-03 09:35:19.333213000 | 50.00 | 309120 | 16 | N | CSCO | 0 | 427 | 1969-12-31 19:00:00 | 0 | ||
| 998 | 2024-01-03 09:35:19.333444799 | @ I | 0 | 1969-12-31 19:00:00 | Z | 60 | 2024-01-03 09:35:19.333250000 | 50.00 | 309121 | 9 | N | CSCO | 0 | 428 | 1969-12-31 19:00:00 | 0 | ||
| 999 | 2024-01-03 09:35:19.333448520 | @ | 0 | 1969-12-31 19:00:00 | U | 61 | 2024-01-03 09:35:19.333227544 | 50.00 | 309122 | 398 | N | CSCO | 0 | 319 | 1969-12-31 19:00:00 | 0 |
1000 rows × 18 columns
Relative Time Filtering on the last 5 minutes Trades#
Retrieving Trades for the last 5 minutes, using relative syntax for start and end in otp.run.
import onetick.py as otp
data = otp.DataSource(db='US_COMP', tick_type='TRD')
data = data[['PRICE', 'SIZE', 'COND']]
data = data.limit(1000)
result = otp.run(data,
start=otp.now() - otp.Minute(5),
end=otp.now(),
timezone='America/New_York',
symbols='CSCO')
result
Time |
PRICE |
SIZE |
COND |
|
|---|---|---|---|---|
0 |
2026-08-04 09:38:09.337531279 |
120.1068 |
1 |
@ I |
1 |
2026-08-04 09:38:09.509870550 |
120.1200 |
14 |
@ I |
2 |
2026-08-04 09:38:09.572110788 |
120.1500 |
10 |
@ I |
3 |
2026-08-04 09:38:09.572151798 |
120.1500 |
398 |
@F |
4 |
2026-08-04 09:38:09.572172621 |
120.1500 |
1 |
@ I |
… |
… |
… |
… |
… |
995 |
2026-08-04 09:39:06.025000447 |
120.3100 |
20 |
@F I |
996 |
2026-08-04 09:39:06.281626529 |
120.2871 |
1 |
@ I |
997 |
2026-08-04 09:39:06.311070704 |
120.3138 |
1 |
@ I |
998 |
2026-08-04 09:39:06.475337961 |
120.3050 |
100 |
@ |
999 |
2026-08-04 09:39:06.475912328 |
120.3050 |
17 |
@ I |
1000 rows x 4 columns
Relative Time Filtering on Trades from Today#
Retrieving Trades from Today until Now, using relative syntax for start and end in otp.run.
import onetick.py as otp
data = otp.DataSource(db='US_COMP', tick_type='TRD')
data = data[['PRICE', 'SIZE', 'COND']]
data = data.limit(1000)
result = otp.run(data,
# get the current date == start of day
start=otp.now().dt.date(),
end=otp.now(),
timezone='America/New_York',
symbols='CSCO')
result
Time |
PRICE |
SIZE |
COND |
|
|---|---|---|---|---|
0 |
2026-08-04 04:00:00.062628594 |
115.8700 |
10 |
@ TI |
1 |
2026-08-04 04:00:00.517801239 |
115.8700 |
28 |
@ TI |
2 |
2026-08-04 04:00:01.572798398 |
116.2988 |
1 |
@ TI |
3 |
2026-08-04 04:00:01.961360151 |
115.4412 |
1 |
@ TI |
4 |
2026-08-04 04:00:02.804289238 |
115.8300 |
5 |
@ TI |
… |
… |
… |
… |
… |
995 |
2026-08-04 07:34:13.936435065 |
117.7400 |
18 |
@FTI |
996 |
2026-08-04 07:34:13.936615045 |
117.7400 |
2 |
@FTI |
997 |
2026-08-04 07:34:13.941759948 |
117.8000 |
37 |
@FTI |
998 |
2026-08-04 07:34:13.941765195 |
117.8000 |
31 |
@FTI |
999 |
2026-08-04 07:34:13.952417322 |
117.7800 |
34 |
@ TI |
1000 rows x 4 columns
Filtering Trades on Continuous Trading#
Filtering trades to the Continuous Trading session using the TRADE_PERIOD field of the CA_COMP_SAMPLE database.
The TRADE_PERIOD field identifies the session each trade belongs to:
O- Opening Auction-- Continuous TradingC- Closing AuctionL- Late Session
Continuous Trading is filtered with TRADE_PERIOD = '-'.
import onetick.py as otp
data = otp.DataSource(db='CA_COMP_SAMPLE', tick_type='TRD')
# Filter to Continuous Trading trades only.
data = data.where(data['TRADE_PERIOD'] == '-')
data = data.limit(1000)
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/Toronto',
symbols='TD')
result
| Time | EXCH_TIME | PRICE | SIZE | TRADE_VENUE | BUYER | SELLER | TRADE_TYPE | ODD_LOT | BYPASS | SPECIAL_TERMS | TRADE_PERIOD | BOOK_TYPE | TRADE_ID | CLOUD_DB | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.179 | 2024-01-03 09:30:00.002 | 85.270 | 11 | XTSE | 56 | 5 | Y | - | 3 | 115 | TSX | 0 | |||
| 1 | 2024-01-03 09:30:00.179 | 2024-01-03 09:30:00.002 | 85.270 | 11 | XTSE | 56 | 80 | Y | - | 3 | 116 | TSX | 1 | |||
| 2 | 2024-01-03 09:30:00.179 | 2024-01-03 09:30:00.002 | 85.270 | 25 | XTSE | 56 | 5 | Y | - | 3 | 117 | TSX | 2 | |||
| 3 | 2024-01-03 09:30:00.179 | 2024-01-03 09:30:00.002 | 85.270 | 11 | XTSE | 56 | 80 | Y | - | 3 | 118 | TSX | 3 | |||
| 4 | 2024-01-03 09:30:00.179 | 2024-01-03 09:30:00.002 | 85.270 | 1 | XTSE | 80 | 80 | Y | - | 3 | 119 | TSX | 4 | |||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 995 | 2024-01-03 09:32:27.801 | 2024-01-03 09:32:27.799 | 85.070 | 100 | XTSE | 1 | 2 | - | 0 | 632 | TSX | 4 | ||||
| 996 | 2024-01-03 09:32:27.801 | 2024-01-03 09:32:27.799 | 85.070 | 100 | XTSE | 1 | 2 | - | 0 | 633 | TSX | 5 | ||||
| 997 | 2024-01-03 09:32:27.802 | 2024-01-03 09:32:27.800 | 85.055 | 100 | MATN | 79 | 2 | - | 2 | 313456662384 | CANADA | 0 | ||||
| 998 | 2024-01-03 09:32:27.804 | 2024-01-03 09:32:27.800 | 85.070 | 100 | XTSE | 79 | 2 | - | 0 | 634 | TSX | 0 | ||||
| 999 | 2024-01-03 09:32:27.804 | 2024-01-03 09:32:27.800 | 85.070 | 100 | XTSE | 1 | 2 | - | 0 | 635 | TSX | 1 |
1000 rows × 16 columns
Filtering Trades on Opening Auction#
Filtering trades to the Opening Auction using the TRADE_PERIOD field of the CA_COMP_SAMPLE database.
The TRADE_PERIOD field identifies the session each trade belongs to:
O- Opening Auction-- Continuous TradingC- Closing AuctionL- Late Session
The Opening Auction is filtered with TRADE_PERIOD = 'O'.
import onetick.py as otp
data = otp.DataSource(db='CA_COMP_SAMPLE', tick_type='TRD')
# Filter to Opening Auction trades only.
data = data.where(data['TRADE_PERIOD'] == 'O')
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/Toronto',
symbols='TD')
result
| Time | EXCH_TIME | PRICE | SIZE | TRADE_VENUE | BUYER | SELLER | TRADE_TYPE | ODD_LOT | BYPASS | SPECIAL_TERMS | TRADE_PERIOD | BOOK_TYPE | TRADE_ID | CLOUD_DB | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 09:30:00.165 | 2024-01-03 09:30:00.002 | 85.25 | 300 | XTSE | 85 | 85 | O | 0 | 1 | TSX | 0 | ||||
| 1 | 2024-01-03 09:30:00.165 | 2024-01-03 09:30:00.002 | 85.25 | 200 | XTSE | 85 | 85 | O | 0 | 2 | TSX | 1 | ||||
| 2 | 2024-01-03 09:30:00.165 | 2024-01-03 09:30:00.002 | 85.25 | 1200 | XTSE | 85 | 85 | O | 0 | 3 | TSX | 2 | ||||
| 3 | 2024-01-03 09:30:00.165 | 2024-01-03 09:30:00.002 | 85.25 | 100 | XTSE | 7 | 7 | O | 0 | 4 | TSX | 3 | ||||
| 4 | 2024-01-03 09:30:00.165 | 2024-01-03 09:30:00.002 | 85.25 | 100 | XTSE | 7 | 7 | O | 0 | 5 | TSX | 4 | ||||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 109 | 2024-01-03 09:30:00.175 | 2024-01-03 09:30:00.002 | 85.25 | 100 | XTSE | 7 | 79 | O | 0 | 110 | TSX | 41 | ||||
| 110 | 2024-01-03 09:30:00.175 | 2024-01-03 09:30:00.002 | 85.25 | 100 | XTSE | 7 | 79 | O | 0 | 111 | TSX | 42 | ||||
| 111 | 2024-01-03 09:30:00.176 | 2024-01-03 09:30:00.002 | 85.25 | 100 | XTSE | 7 | 79 | O | 0 | 112 | TSX | 0 | ||||
| 112 | 2024-01-03 09:30:00.176 | 2024-01-03 09:30:00.002 | 85.25 | 1000 | XTSE | 2 | 79 | O | 0 | 113 | TSX | 1 | ||||
| 113 | 2024-01-03 09:30:00.176 | 2024-01-03 09:30:00.002 | 85.25 | 200 | XTSE | 80 | 1 | O | 0 | 114 | TSX | 2 |
114 rows × 16 columns
Filtering Trades on Closing Auction#
Filtering trades to the Closing Auction using the TRADE_PERIOD field of the CA_COMP_SAMPLE database.
The TRADE_PERIOD field identifies the session each trade belongs to:
O- Opening Auction-- Continuous TradingC- Closing AuctionL- Late Session
The Closing Auction is filtered with TRADE_PERIOD = 'C'.
import onetick.py as otp
data = otp.DataSource(db='CA_COMP_SAMPLE', tick_type='TRD')
# Filter to Closing Auction trades only.
data = data.where(data['TRADE_PERIOD'] == 'C')
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='America/Toronto',
symbols='TD')
result
| Time | EXCH_TIME | PRICE | SIZE | TRADE_VENUE | BUYER | SELLER | TRADE_TYPE | ODD_LOT | BYPASS | SPECIAL_TERMS | TRADE_PERIOD | BOOK_TYPE | TRADE_ID | CLOUD_DB | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:00:00.138 | 2024-01-03 16:00:00.007 | 84.97 | 800 | XTSE | 222 | 222 | C | 0 | 21081 | TSX | 0 | ||||
| 1 | 2024-01-03 16:00:00.138 | 2024-01-03 16:00:00.007 | 84.97 | 1400 | XTSE | 222 | 222 | C | 0 | 21082 | TSX | 1 | ||||
| 2 | 2024-01-03 16:00:00.138 | 2024-01-03 16:00:00.007 | 84.97 | 300 | XTSE | 222 | 222 | C | 0 | 21083 | TSX | 2 | ||||
| 3 | 2024-01-03 16:00:00.138 | 2024-01-03 16:00:00.007 | 84.97 | 200 | XTSE | 222 | 222 | C | 0 | 21084 | TSX | 3 | ||||
| 4 | 2024-01-03 16:00:00.138 | 2024-01-03 16:00:00.007 | 84.97 | 300 | XTSE | 222 | 222 | C | 0 | 21085 | TSX | 4 | ||||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 287 | 2024-01-03 16:00:00.146 | 2024-01-03 16:00:00.007 | 84.97 | 4 | XTSE | 5 | 1 | Y | C | 3 | 21382 | TSX | 18 | |||
| 288 | 2024-01-03 16:00:00.146 | 2024-01-03 16:00:00.007 | 84.97 | 14 | XTSE | 5 | 79 | Y | C | 3 | 21383 | TSX | 19 | |||
| 289 | 2024-01-03 16:00:00.146 | 2024-01-03 16:00:00.007 | 84.97 | 48 | XTSE | 5 | 79 | Y | C | 3 | 21384 | TSX | 20 | |||
| 290 | 2024-01-03 16:00:00.146 | 2024-01-03 16:00:00.007 | 84.97 | 52 | XTSE | 5 | 79 | Y | C | 3 | 21385 | TSX | 21 | |||
| 291 | 2024-01-03 16:00:00.146 | 2024-01-03 16:00:00.007 | 84.97 | 29 | XTSE | 5 | 1 | Y | C | 3 | 21386 | TSX | 22 |
292 rows × 16 columns
Filtering Indicative Prices on Opening Auction#
Filtering the indicative (theoretical) auction prices to the Opening Auction using the AUCTION_TYPE
field of the IND table in the LSE_SAMPLE database.
The IND table holds the Auction Values, including the Indicative Price and Indicative Size across the
Opening and Closing Auctions.
The AUCTION_TYPE field identifies the auction:
O- Opening AuctionC- Closing Auction
The Opening Auction is filtered with AUCTION_TYPE = 'O'.
import onetick.py as otp
data = otp.DataSource(db='LSE_SAMPLE', tick_type='IND')
# Filter to the Opening Auction.
data = data.where(data['AUCTION_TYPE'] == 'O')
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='Europe/London',
symbols='VOD')
result
| Time | EXCH_TIME | PRICE | SIZE | IMB_SIDE | IMB_VOLUME | AUCTION_TYPE | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 07:50:00.075 | 2024-01-03 07:50:00.074938266 | 69.00 | 6000 | B | 2900 | O | 28 |
| 1 | 2024-01-03 07:50:00.076 | 2024-01-03 07:50:00.075324726 | 69.00 | 6880 | B | 2020 | O | 5 |
| 2 | 2024-01-03 07:50:00.085 | 2024-01-03 07:50:00.085221086 | 69.00 | 7370 | B | 1530 | O | 8 |
| 3 | 2024-01-03 07:50:00.088 | 2024-01-03 07:50:00.087546226 | 69.00 | 8870 | B | 30 | O | 1 |
| 4 | 2024-01-03 07:50:00.090 | 2024-01-03 07:50:00.089537586 | 68.28 | 9505 | B | 1945 | O | 8 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 254 | 2024-01-03 08:00:06.074 | 2024-01-03 08:00:06.073875086 | 70.00 | 183836 | B | 89 | O | 4 |
| 255 | 2024-01-03 08:00:06.076 | 2024-01-03 08:00:06.075733666 | 70.00 | 183836 | B | 4937 | O | 2 |
| 256 | 2024-01-03 08:00:06.105 | 2024-01-03 08:00:06.104726346 | 70.00 | 184613 | B | 4160 | O | 1 |
| 257 | 2024-01-03 08:00:06.153 | 2024-01-03 08:00:06.153005826 | 70.01 | 184613 | B | 2817 | O | 2 |
| 258 | 2024-01-03 08:00:06.176 | 2024-01-03 08:00:06.175662586 | 70.00 | 184613 | B | 4937 | O | 2 |
259 rows × 8 columns
Filtering Indicative Prices on Closing Auction#
Filtering the indicative (theoretical) auction prices to the Closing Auction using the AUCTION_TYPE
field of the IND table in the LSE_SAMPLE database.
The IND table holds the Auction Values, including the Indicative Price and Indicative Size across the
Opening and Closing Auctions.
The AUCTION_TYPE field identifies the auction:
O- Opening AuctionC- Closing Auction
The Closing Auction is filtered with AUCTION_TYPE = 'C'.
import onetick.py as otp
data = otp.DataSource(db='LSE_SAMPLE', tick_type='IND')
# Filter to the Closing Auction.
data = data.where(data['AUCTION_TYPE'] == 'C')
result = otp.run(data,
start=otp.dt(2024, 1, 3),
end=otp.dt(2024, 1, 4),
timezone='Europe/London',
symbols='VOD')
result
| Time | EXCH_TIME | PRICE | SIZE | IMB_SIDE | IMB_VOLUME | AUCTION_TYPE | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2024-01-03 16:30:00.107 | 2024-01-03 16:30:00.106699390 | 69.37 | 335707 | B | 4657 | C | 93 |
| 1 | 2024-01-03 16:30:00.112 | 2024-01-03 16:30:00.109997870 | 69.36 | 335707 | B | 4498 | C | 204 |
| 2 | 2024-01-03 16:30:00.112 | 2024-01-03 16:30:00.110009330 | 69.35 | 335707 | B | 27965 | C | 209 |
| 3 | 2024-01-03 16:30:00.112 | 2024-01-03 16:30:00.111052950 | 69.36 | 335707 | B | 295 | C | 350 |
| 4 | 2024-01-03 16:30:00.113 | 2024-01-03 16:30:00.111072590 | 69.36 | 335707 | B | 2656 | C | 5 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 661 | 2024-01-03 16:35:03.043 | 2024-01-03 16:35:03.043015978 | 69.51 | 15768552 | B | 27216 | C | 13 |
| 662 | 2024-01-03 16:35:03.544 | 2024-01-03 16:35:03.543883188 | 69.51 | 15788154 | B | 7614 | C | 1 |
| 663 | 2024-01-03 16:35:04.171 | 2024-01-03 16:35:04.171314166 | 69.51 | 15795768 | S | 448489 | C | 0 |
| 664 | 2024-01-03 16:35:05.818 | 2024-01-03 16:35:05.818078954 | 69.51 | 15788154 | B | 7614 | C | 2 |
| 665 | 2024-01-03 16:35:06.086 | 2024-01-03 16:35:06.086019927 | 69.51 | 15788154 | B | 10915 | C | 0 |
666 rows × 8 columns