Basics

This section contains 14 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

Retrieve Exchange trades from the US_COMP_SAMPLE database for CSCO, across the specified time range.

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 the first 100 trades 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, count) for LSE trades.

import onetick.py as otp

# Create the DataSource for LSE_SAMPLE.TRD trades
data = otp.DataSource(
    db='LSE_SAMPLE',
    tick_type='TRD',
    # Define the schema for trades
    schema_policy='manual',
    schema={'PRICE': float, 'SIZE': int},
)

# Aggregate statistics over the interval
agg = data.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'),
    'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
    'COUNT_PRICE': otp.agg.count(),
})

# Run the query
result = otp.run(
    agg,
    symbols='VOD',
    start=otp.dt(2024, 1, 3, 8),
    end=otp.dt(2024, 1, 4, 16),
    timezone='UTC',
)
result

	Time	MEAN_PRICE	STDDEV_PRICE	MEDIAN_PRICE	MAX_PRICE	MIN_PRICE	VWAP_PRICE	COUNT_PRICE
0	2024-01-04 16:00:00	69.770939	4.877331	70.16	71.0166	0.812	70.020224	11925

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]}

Identify Trading Days Half Day

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

# Define the data source for daily bars
data = otp.DataSource(
    db='US_COMP_SAMPLE_DAILY',
    tick_type='DAY',
    schema_policy='manual',
    schema={'CLOSE': float, 'EXCHANGE': str}
)

# Market Activity column returns R [Regular], L [Half Day], H [Holiday], W [Weekend]
data = data.mkt_activity('CLOUD_DB_US_COMP')

# Filter for EXCHANGE == ''
data = data.where(data['EXCHANGE'] == '')

# Select only the CLOSE and ACTIVITY_CODE columns
data = data[['CLOSE', 'MKT_ACTIVITY']]

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

# Run the query for symbol 'HD' and the specified time range
result = otp.run(
    data,
    symbols=['HD'],
    start=otp.dt(2024, 1, 1),
    end=otp.dt(2024, 2, 1),
    timezone='UTC'
)
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

Join Trades to Prevailing Quotes

Retrieve trades joined to prevailing quotes based on an asof 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

Retrieve prevailing trade price for CSCO on database US_COMP_SAMPLE at a specified timestamp.
Looking back 1 Day (86400 seconds) in cases where the trade does not occur at the exact timestamp.

import onetick.py as otp

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

# 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.000000000	Q	@FT		N		1	CSCO	50.16	1969-12-31 19:00:00	0	471	0	168866	348	2024-01-03 09:29:59.990923814	1969-12-31 19:00:00	1
1	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
2	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
3	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
4	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
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
95	2024-01-03 09:30:00.897235112	U	@F		N		1	CSCO	50.07	1969-12-31 19:00:00	0	200	0	171855	22	2024-01-03 09:30:00.897017316	1969-12-31 19:00:00	2
96	2024-01-03 09:30:00.897416820	Q	@F I		N		1	CSCO	50.08	1969-12-31 19:00:00	0	51	0	171857	365	2024-01-03 09:30:00.897398456	1969-12-31 19:00:00	3
97	2024-01-03 09:30:00.897444128	Q	@F I		N		1	CSCO	50.08	1969-12-31 19:00:00	0	29	0	171858	366	2024-01-03 09:30:00.897428791	1969-12-31 19:00:00	4
98	2024-01-03 09:30:00.897474806	Q	@F I		N		1	CSCO	50.08	1969-12-31 19:00:00	0	12	0	171859	367	2024-01-03 09:30:00.897456284	1969-12-31 19:00:00	5
99	2024-01-03 09:30:00.897476583	Q	@F I		N		1	CSCO	50.08	1969-12-31 19:00:00	0	2	0	171860	368	2024-01-03 09:30:00.897456284	1969-12-31 19:00:00	6

100 rows × 18 columns

Query Data Availability Status

Retrieve Completed Data Load Events for US_COMP Database.
Returns load completion events from the last 7 days.

import onetick.py as otp

# Calculate time window: last 7 days to now
now = otp.now()
seven_days_ago = now - otp.Day(7)

# Create data source for DB_INFO.PROC_EVENTS
proc_events = otp.DataSource(
    db='DB_INFO',
    tick_type='PROC_EVENTS'
)

# Filter for US_COMP database, successful load events, and events from the last 7 days
proc_events = proc_events.where(
    (proc_events['DB_NAME'] == 'US_COMP') &
    (proc_events['EVENT_NAME'] == 'Load finished')
)

# Run the query for the specified time window and output as a dataframe
result = otp.run(
    proc_events,
    symbols='US_COMP',
    start=seven_days_ago,
    end=now,
    timezone='UTC',
)
result

	Time	DB_NAME	DB_DESCRIPTION	TIMEZONE	DATA_DATE	EVENT_TIME	EVENT_NAME	SOURCE_FILE_NAME	OMDSEQ
0	2026-08-04	US_COMP	US Consolidated Equities (exc. OTC)	America/New_York	20260804	2026-08-05 02:15:14	Load finished		5006
1	2026-08-05	US_COMP	US Consolidated Equities (exc. OTC)	America/New_York	20260805	2026-08-06 02:38:26	Load finished		4991
2	2026-08-06	US_COMP	US Consolidated Equities (exc. OTC)	America/New_York	20260806	2026-08-07 02:43:32	Load finished		4956
3	2026-08-07	US_COMP	US Consolidated Equities (exc. OTC)	America/New_York	20260807	2026-08-08 02:38:16	Load finished		4887

Ranking

Retrieve trades ranked by price 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')
data = data[['PRICE', 'SIZE']]
data = data.ranking({'SIZE': 'desc'})

# 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	RANKING
0	2024-01-03 09:30:00.065443591	50.0200	2	3867
1	2024-01-03 09:30:00.111130049	50.1600	3	3817
2	2024-01-03 09:30:00.127459523	50.1700	100	684
3	2024-01-03 09:30:00.128498068	50.1700	5	3664
4	2024-01-03 09:30:00.135190071	50.1300	46	2580
...	...	...	...	...
95	2024-01-03 09:30:00.897416820	50.0800	51	2401
96	2024-01-03 09:30:00.897444128	50.0800	29	2818
97	2024-01-03 09:30:00.897474806	50.0800	12	3270
98	2024-01-03 09:30:00.897476583	50.0800	2	3867
99	2024-01-03 09:30:00.897670983	50.0797	100	684

100 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