Data Retrieval#

OneTick is a time series database meaning that each record has a timestamp and timestamps of consecutive records are non-decreasing. Multiple time series are stored in OneTick. An individual time series is identified by the symbol (aka ticker, financial instrument, security), tick type (i.e., the type of data such as trades or quotes), and the name of the database where the time series is stored.

import onetick.py as otp

s = otp.dt(2024, 2, 1, 9, 30)
e = otp.dt(2024, 2, 1, 9, 30, 1)

# we can retrieve the list of databases available on the server
# (note that not all databases may be readable by your user,
#  but the _SAMPLE databases should be)
dbs = otp.databases()
list(db for db in dbs if db.endswith('_SAMPLE'))

['CA_COMP_SAMPLE',
 'CME_SAMPLE',
 'EUREX_SAMPLE',
 'EU_COMP_SAMPLE',
 'ICE_EU_COM_SAMPLE',
 'ICE_US_SAMPLE',
 'LSE_SAMPLE',
 'TDI_FUT_SAMPLE',
 'US_COMP_SAMPLE']

# the list of dates with data for the db
dbs['US_COMP_SAMPLE'].dates()[-5:]

[datetime.date(2024, 3, 22),
 datetime.date(2024, 3, 25),
 datetime.date(2024, 3, 26),
 datetime.date(2024, 3, 27),
 datetime.date(2024, 3, 28)]

# or just the last day with data
dbs['US_COMP_SAMPLE'].last_date

datetime.date(2024, 3, 28)

# and the list of tick types
dbs['US_COMP_SAMPLE'].tick_types()

['DAY', 'IND', 'LULD', 'MKT', 'NBBO', 'QTE', 'STAT', 'TRD']

OneTick Cloud Standard Tables:

Table

Description

DAY

End of Day Record typically covering Closing Price plus Open Interest for Derivatives Markets

IND

Indicative Prices occurring during Auction phases

QTE

Quote Events

STAT

Static Reference Data for the Instrument

TRD

Trade Events

NBBO

National Best Bid & Offer Quotes

PRL

Book Depth - Market By Level

PRL_FULL

Book Depth - Market by Order

MKTCAL

Market Holiday & Trading Hours

TRD_1M

1 Minute Trade Bar

QTE_1M

1 Minute Quote Bar

We can now retrieve symbols traded in a given time range. (In many financial markets, there are properties that remain constant throughout the trading day. Examples include the name of a financial instrument and the set of instruments traded).

symbols = otp.Symbols('US_COMP_SAMPLE')
otp.run(symbols, start=otp.dt(2024, 2, 1), end=otp.dt(2024, 2, 2))
Time SYMBOL_NAME
0 2024-02-01 A
1 2024-02-01 AAL
2 2024-02-01 AAPL
3 2024-02-01 ABBV
4 2024-02-01 ABNB
... ... ...
496 2024-02-01 XYL
497 2024-02-01 YUM
498 2024-02-01 ZBH
499 2024-02-01 ZBRA
500 2024-02-01 ZTS

501 rows × 2 columns

We used the otp.run method above, which executed a query that retrieved the list of symbols.

The start and end timestamps were specified with onetick-py’s datetime class otp.dt.

Now that we have database names, tick types, and symbols, we are ready to query a time series.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
otp.run(q, start=s, end=e, symbols=['AAPL'])
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-02-01 09:30:00.000961260 P @FT N 1 AAPL 184.010 1969-12-31 19:00:00 0 302 0 140448 5239 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 0
1 2024-02-01 09:30:00.000961491 P @FT N 1 AAPL 184.000 1969-12-31 19:00:00 0 100 0 140449 5240 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 1
2 2024-02-01 09:30:00.000961701 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140450 5241 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 2
3 2024-02-01 09:30:00.000973163 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140451 5242 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 3
4 2024-02-01 09:30:00.000973355 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 5 0 140452 5243 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 4
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
574 2024-02-01 09:30:00.987184691 K @F I N 1 AAPL 183.900 1969-12-31 19:00:00 0 9 0 146846 3099 2024-02-01 09:30:00.987000000 1969-12-31 19:00:00.000000000 0
575 2024-02-01 09:30:00.990378350 D @ I N N 0 AAPL 183.920 1969-12-31 19:00:00 0 1 0 146905 1106 2024-02-01 09:30:00.975000000 2024-02-01 09:30:00.990038585 0
576 2024-02-01 09:30:00.991941892 D @ I N Q 0 AAPL 183.935 1969-12-31 19:00:00 0 1 0 146927 2606 2024-02-01 09:30:00.520147000 2024-02-01 09:30:00.991906728 0
577 2024-02-01 09:30:00.993785116 D @ N Q 0 AAPL 183.905 1969-12-31 19:00:00 0 300 0 146944 2607 2024-02-01 09:30:00.970910000 2024-02-01 09:30:00.993753186 0
578 2024-02-01 09:30:00.996512511 D @ I N Q 0 AAPL 183.934 1969-12-31 19:00:00 0 5 0 146970 2608 2024-02-01 09:30:00.686000000 2024-02-01 09:30:00.996478568 0

579 rows × 18 columns

Note that we specified the start and end of the time series to retrieve the corresponding interval.

Warning

In otp.DataSource default value of the parameter schema_policy enables automatic deduction of the data schema.

It works fine for simple cases like using onetick-py in Jupyter notebooks, but it is highly not recommended for production code.

For details see Schema deduction mechanism.

Let’s just keep the columns we’re interested in to make it more digestible.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
otp.run(q, start=s, end=e, symbols=['AAPL'])
Time PRICE SIZE COND EXCHANGE
0 2024-02-01 09:30:00.000961260 184.010 302 @FT P
1 2024-02-01 09:30:00.000961491 184.000 100 @FT P
2 2024-02-01 09:30:00.000961701 184.000 1 @FTI P
3 2024-02-01 09:30:00.000973163 184.000 1 @FTI P
4 2024-02-01 09:30:00.000973355 184.000 5 @FTI P
... ... ... ... ... ...
574 2024-02-01 09:30:00.987184691 183.900 9 @F I K
575 2024-02-01 09:30:00.990378350 183.920 1 @ I D
576 2024-02-01 09:30:00.991941892 183.935 1 @ I D
577 2024-02-01 09:30:00.993785116 183.905 300 @ D
578 2024-02-01 09:30:00.996512511 183.934 5 @ I D

579 rows × 5 columns

We can retrieve multiple time series.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
mult = otp.run(q, start=s, end=e, symbols=['AAPL', 'MSFT'])
mult

{'AAPL':                              Time    PRICE  SIZE  COND EXCHANGE
 0   2024-02-01 09:30:00.000961260  184.010   302  @FT         P
 1   2024-02-01 09:30:00.000961491  184.000   100  @FT         P
 2   2024-02-01 09:30:00.000961701  184.000     1  @FTI        P
 3   2024-02-01 09:30:00.000973163  184.000     1  @FTI        P
 4   2024-02-01 09:30:00.000973355  184.000     5  @FTI        P
 ..                            ...      ...   ...   ...      ...
 574 2024-02-01 09:30:00.987184691  183.900     9  @F I        K
 575 2024-02-01 09:30:00.990378350  183.920     1  @  I        D
 576 2024-02-01 09:30:00.991941892  183.935     1  @  I        D
 577 2024-02-01 09:30:00.993785116  183.905   300  @           D
 578 2024-02-01 09:30:00.996512511  183.934     5  @  I        D
 
 [579 rows x 5 columns],
 'MSFT':                              Time     PRICE  SIZE  COND EXCHANGE
 0   2024-02-01 09:30:00.001821667  401.9000    25  @F I        K
 1   2024-02-01 09:30:00.001825700  401.8900    50  @F I        K
 2   2024-02-01 09:30:00.001849062  401.8800    25  @F I        K
 3   2024-02-01 09:30:00.002566542  401.9800     7  @  I        K
 4   2024-02-01 09:30:00.002826318  401.8800     1  @F I        K
 ..                            ...       ...   ...   ...      ...
 620 2024-02-01 09:30:00.981073979  402.2950     1  @  I        D
 621 2024-02-01 09:30:00.981340636  402.2950   100  @           D
 622 2024-02-01 09:30:00.990762807  402.2949    12  @  I        D
 623 2024-02-01 09:30:00.997095792  401.9950     1  @  I        D
 624 2024-02-01 09:30:00.999830840  401.9950     1  @  I        D
 
 [625 rows x 5 columns]}

Each time series is returned as the value of a dict keyed by the corresponding symbol.

mult['MSFT']
Time PRICE SIZE COND EXCHANGE
0 2024-02-01 09:30:00.001821667 401.9000 25 @F I K
1 2024-02-01 09:30:00.001825700 401.8900 50 @F I K
2 2024-02-01 09:30:00.001849062 401.8800 25 @F I K
3 2024-02-01 09:30:00.002566542 401.9800 7 @ I K
4 2024-02-01 09:30:00.002826318 401.8800 1 @F I K
... ... ... ... ... ...
620 2024-02-01 09:30:00.981073979 402.2950 1 @ I D
621 2024-02-01 09:30:00.981340636 402.2950 100 @ D
622 2024-02-01 09:30:00.990762807 402.2949 12 @ I D
623 2024-02-01 09:30:00.997095792 401.9950 1 @ I D
624 2024-02-01 09:30:00.999830840 401.9950 1 @ I D

625 rows × 5 columns

We can also retrieve all of the symbols from the database or all of the symbols matching a pattern.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
mult = otp.run(q, start=s, end=e, symbols=otp.Symbols('US_COMP_SAMPLE', pattern='AA%'))
mult

{'AAL':                             Time   PRICE  SIZE  COND EXCHANGE
 0  2024-02-01 09:30:00.022410753  14.350    20  @  I        P
 1  2024-02-01 09:30:00.022414090  14.350    20  @  Q        P
 2  2024-02-01 09:30:00.022414192  14.350   100  @           P
 3  2024-02-01 09:30:00.022542837  14.350   100  @F          Z
 4  2024-02-01 09:30:00.022547319  14.350   100  @F          Z
 5  2024-02-01 09:30:00.022549481  14.350    71  @F I        Z
 6  2024-02-01 09:30:00.068446479  14.350    29  @  I        Z
 7  2024-02-01 09:30:00.068451239  14.350    71  @  I        Z
 8  2024-02-01 09:30:00.069556541  14.330  3360  @ T         Q
 9  2024-02-01 09:30:00.299697394  14.330     6  @ TI        Q
 10 2024-02-01 09:30:00.363469842  14.330    94  @ TI        Q
 11 2024-02-01 09:30:00.363472230  14.330   100  @ T         Q
 12 2024-02-01 09:30:00.555168072  14.330  3166  @ T         Q
 13 2024-02-01 09:30:00.555169797  14.325    21  @ TI        Q
 14 2024-02-01 09:30:00.555172368  14.320   100  @ T         Q
 15 2024-02-01 09:30:00.555174750  14.320    50  @ TI        Q
 16 2024-02-01 09:30:00.555178849  14.325    21  @  I        Z
 17 2024-02-01 09:30:00.555182916  14.320   100  @           Z
 18 2024-02-01 09:30:00.655538989  14.325     1  @  I        D
 19 2024-02-01 09:30:00.667824511  14.330     5  @F I        U
 20 2024-02-01 09:30:00.667830587  14.320    50  @ TI        Q
 21 2024-02-01 09:30:00.667832498  14.330    57  @F I        Z
 22 2024-02-01 09:30:00.667832957  14.330    20  @F I        K
 23 2024-02-01 09:30:00.668164211  14.325    50  @  I        K
 24 2024-02-01 09:30:00.668496453  14.320    50  @  I        P
 25 2024-02-01 09:30:00.676296563  14.325     1  @  I        D
 26 2024-02-01 09:30:00.875259611  14.325     1  @  I        D
 27 2024-02-01 09:30:00.886033898  14.320    50  @ TI        Q
 28 2024-02-01 09:30:00.886525738  14.320   100  @           Z
 29 2024-02-01 09:30:00.886798897  14.320   100  @           P
 30 2024-02-01 09:30:00.886835029  14.320   100  @           Z
 31 2024-02-01 09:30:00.886974451  14.320    50  @  I        P
 32 2024-02-01 09:30:00.887036917  14.320    50  @  I        P
 33 2024-02-01 09:30:00.887048606  14.320   100  @           Z
 34 2024-02-01 09:30:00.887198745  14.320   100  @           P
 35 2024-02-01 09:30:00.887198852  14.320   100  @           P
 36 2024-02-01 09:30:00.887223181  14.320   100  @           Z
 37 2024-02-01 09:30:00.887402314  14.320   100  @           P
 38 2024-02-01 09:30:00.887585774  14.320   100  @           P
 39 2024-02-01 09:30:00.898392581  14.320    50  @  I        P
 40 2024-02-01 09:30:00.910688528  14.320    50  @  I        P
 41 2024-02-01 09:30:00.920862674  14.320    50  @  I        P
 42 2024-02-01 09:30:00.940726857  14.320    50  @  I        P,
 'AAPL':                              Time    PRICE  SIZE  COND EXCHANGE
 0   2024-02-01 09:30:00.000961260  184.010   302  @FT         P
 1   2024-02-01 09:30:00.000961491  184.000   100  @FT         P
 2   2024-02-01 09:30:00.000961701  184.000     1  @FTI        P
 3   2024-02-01 09:30:00.000973163  184.000     1  @FTI        P
 4   2024-02-01 09:30:00.000973355  184.000     5  @FTI        P
 ..                            ...      ...   ...   ...      ...
 574 2024-02-01 09:30:00.987184691  183.900     9  @F I        K
 575 2024-02-01 09:30:00.990378350  183.920     1  @  I        D
 576 2024-02-01 09:30:00.991941892  183.935     1  @  I        D
 577 2024-02-01 09:30:00.993785116  183.905   300  @           D
 578 2024-02-01 09:30:00.996512511  183.934     5  @  I        D
 
 [579 rows x 5 columns]}

We can merge all of the time series by time by using otp.merge function.

Parameter identify_input_ts here automatically adds SYMBOL_NAME and TICK_TYPE columns to the output, so each tick can be identified.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
q = otp.merge([q], symbols=['AAPL', 'MSFT'], identify_input_ts=True)
single = otp.run(q, start=s, end=e)
single
Time PRICE SIZE COND EXCHANGE SYMBOL_NAME TICK_TYPE
0 2024-02-01 09:30:00.000961260 184.010 302 @FT P AAPL TRD
1 2024-02-01 09:30:00.000961491 184.000 100 @FT P AAPL TRD
2 2024-02-01 09:30:00.000961701 184.000 1 @FTI P AAPL TRD
3 2024-02-01 09:30:00.000973163 184.000 1 @FTI P AAPL TRD
4 2024-02-01 09:30:00.000973355 184.000 5 @FTI P AAPL TRD
... ... ... ... ... ... ... ...
1199 2024-02-01 09:30:00.991941892 183.935 1 @ I D AAPL TRD
1200 2024-02-01 09:30:00.993785116 183.905 300 @ D AAPL TRD
1201 2024-02-01 09:30:00.996512511 183.934 5 @ I D AAPL TRD
1202 2024-02-01 09:30:00.997095792 401.995 1 @ I D MSFT TRD
1203 2024-02-01 09:30:00.999830840 401.995 1 @ I D MSFT TRD

1204 rows × 7 columns

The time range and symbols can be specified directly on the data source. This way we can have different times ranges for difference sources that we can later merge.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD', start=s, end=e, symbols=['AAPL'])
otp.run(q)
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-02-01 09:30:00.000961260 P @FT N 1 AAPL 184.010 1969-12-31 19:00:00 0 302 0 140448 5239 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 0
1 2024-02-01 09:30:00.000961491 P @FT N 1 AAPL 184.000 1969-12-31 19:00:00 0 100 0 140449 5240 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 1
2 2024-02-01 09:30:00.000961701 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140450 5241 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 2
3 2024-02-01 09:30:00.000973163 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140451 5242 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 3
4 2024-02-01 09:30:00.000973355 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 5 0 140452 5243 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 4
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
574 2024-02-01 09:30:00.987184691 K @F I N 1 AAPL 183.900 1969-12-31 19:00:00 0 9 0 146846 3099 2024-02-01 09:30:00.987000000 1969-12-31 19:00:00.000000000 0
575 2024-02-01 09:30:00.990378350 D @ I N N 0 AAPL 183.920 1969-12-31 19:00:00 0 1 0 146905 1106 2024-02-01 09:30:00.975000000 2024-02-01 09:30:00.990038585 0
576 2024-02-01 09:30:00.991941892 D @ I N Q 0 AAPL 183.935 1969-12-31 19:00:00 0 1 0 146927 2606 2024-02-01 09:30:00.520147000 2024-02-01 09:30:00.991906728 0
577 2024-02-01 09:30:00.993785116 D @ N Q 0 AAPL 183.905 1969-12-31 19:00:00 0 300 0 146944 2607 2024-02-01 09:30:00.970910000 2024-02-01 09:30:00.993753186 0
578 2024-02-01 09:30:00.996512511 D @ I N Q 0 AAPL 183.934 1969-12-31 19:00:00 0 5 0 146970 2608 2024-02-01 09:30:00.686000000 2024-02-01 09:30:00.996478568 0

579 rows × 18 columns

For example, we can get the data from February 24 for AAPL and from March 20 for MSFT.

aapl = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD', start=otp.dt(2024, 2, 24, 9, 30), end=otp.dt(2024, 2, 24, 9, 30, 1), symbols=['AAPL'])
msft = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD', start=otp.dt(2024, 3, 20, 9, 30), end=otp.dt(2024, 3, 20, 9, 30, 1), symbols=['MSFT'])
merged = otp.merge([aapl, msft], identify_input_ts=True)
merged = merged[['PRICE', 'SIZE', 'SYMBOL_NAME']]
otp.run(merged)
Time PRICE SIZE SYMBOL_NAME
0 2024-03-20 09:30:00.004280560 422.06 1 MSFT
1 2024-03-20 09:30:00.005194296 422.08 1 MSFT
2 2024-03-20 09:30:00.007885118 422.09 1 MSFT
3 2024-03-20 09:30:00.007888920 422.23 25 MSFT
4 2024-03-20 09:30:00.007889056 422.24 10 MSFT
... ... ... ... ...
330 2024-03-20 09:30:00.953965323 422.51 6 MSFT
331 2024-03-20 09:30:00.966819818 422.43 100 MSFT
332 2024-03-20 09:30:00.966821672 422.51 19 MSFT
333 2024-03-20 09:30:00.966826014 422.51 50 MSFT
334 2024-03-20 09:30:00.975839203 421.99 1 MSFT

335 rows × 4 columns

We can also specify multiple symbols in the data source in which case they will be merged by time.

q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD', start=s, end=e, symbols=['AAPL', 'MSFT'])
otp.run(q)
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-02-01 09:30:00.000961260 P @FT N 1 AAPL 184.010 1969-12-31 19:00:00 0 302 0 140448 5239 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 0
1 2024-02-01 09:30:00.000961491 P @FT N 1 AAPL 184.000 1969-12-31 19:00:00 0 100 0 140449 5240 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 1
2 2024-02-01 09:30:00.000961701 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140450 5241 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 2
3 2024-02-01 09:30:00.000973163 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140451 5242 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 3
4 2024-02-01 09:30:00.000973355 P @FTI N 1 AAPL 184.000 1969-12-31 19:00:00 0 5 0 140452 5243 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 4
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1199 2024-02-01 09:30:00.991941892 D @ I N Q 0 AAPL 183.935 1969-12-31 19:00:00 0 1 0 146927 2606 2024-02-01 09:30:00.520147000 2024-02-01 09:30:00.991906728 0
1200 2024-02-01 09:30:00.993785116 D @ N Q 0 AAPL 183.905 1969-12-31 19:00:00 0 300 0 146944 2607 2024-02-01 09:30:00.970910000 2024-02-01 09:30:00.993753186 0
1201 2024-02-01 09:30:00.996512511 D @ I N Q 0 AAPL 183.934 1969-12-31 19:00:00 0 5 0 146970 2608 2024-02-01 09:30:00.686000000 2024-02-01 09:30:00.996478568 0
1202 2024-02-01 09:30:00.997095792 D @ I N Q 0 MSFT 401.995 1969-12-31 19:00:00 0 1 0 245899 2619 2024-02-01 09:30:00.497061000 2024-02-01 09:30:00.997066904 0
1203 2024-02-01 09:30:00.999830840 D @ I N Q 0 MSFT 401.995 1969-12-31 19:00:00 0 1 0 245911 2620 2024-02-01 09:30:00.311890000 2024-02-01 09:30:00.999804780 0

1204 rows × 18 columns

We can look up symbols in multiple databases.

q = otp.DataSource(tick_type='TRD')
q = q.table(PRICE=float, SIZE=int)
q = otp.merge(q, symbols=['US_COMP_SAMPLE::AAPL', 'TDI_FUT_SAMPLE::ES_r_tdi'], identify_input_ts=True)
otp.run(q, start=s, end=e, symbol_date=s)
Time PRICE SIZE SYMBOL_NAME TICK_TYPE
0 2024-02-01 09:30:00.000961260 184.010 302 US_COMP_SAMPLE::AAPL TRD
1 2024-02-01 09:30:00.000961491 184.000 100 US_COMP_SAMPLE::AAPL TRD
2 2024-02-01 09:30:00.000961701 184.000 1 US_COMP_SAMPLE::AAPL TRD
3 2024-02-01 09:30:00.000973163 184.000 1 US_COMP_SAMPLE::AAPL TRD
4 2024-02-01 09:30:00.000973355 184.000 5 US_COMP_SAMPLE::AAPL TRD
... ... ... ... ... ...
1065 2024-02-01 09:30:00.987184691 183.900 9 US_COMP_SAMPLE::AAPL TRD
1066 2024-02-01 09:30:00.990378350 183.920 1 US_COMP_SAMPLE::AAPL TRD
1067 2024-02-01 09:30:00.991941892 183.935 1 US_COMP_SAMPLE::AAPL TRD
1068 2024-02-01 09:30:00.993785116 183.905 300 US_COMP_SAMPLE::AAPL TRD
1069 2024-02-01 09:30:00.996512511 183.934 5 US_COMP_SAMPLE::AAPL TRD

1070 rows × 5 columns

We can also look up the same symbols in different databases (even if they have different tick types).

qte = otp.DataSource('US_COMP_SAMPLE', tick_type='QTE')
qte = qte[['BID_PRICE', 'ASK_PRICE']]
nbbo = otp.DataSource('US_COMP_SAMPLE', tick_type='NBBO')
nbbo = nbbo[['BID_PRICE', 'ASK_PRICE']]

q = otp.merge([qte, nbbo], symbols=['AAPL', 'MSFT'], identify_input_ts=True)
otp.run(q, start=s, end=s + otp.Milli(1))
Time BID_PRICE ASK_PRICE SYMBOL_NAME TICK_TYPE
0 2024-02-01 09:30:00.000860953 184.0 184.14 AAPL QTE
1 2024-02-01 09:30:00.000860953 184.0 184.14 AAPL NBBO
2 2024-02-01 09:30:00.000969529 183.9 184.14 AAPL QTE
3 2024-02-01 09:30:00.000969529 183.9 184.14 AAPL NBBO
4 2024-02-01 09:30:00.000998923 183.7 184.35 AAPL QTE

Generating ticks#

There are several ways to generate ticks without accessing the database. It’s very useful in case you want to check some algorithm fast or to create a test-case.

Classes otp.Tick and otp.Ticks can be used for this purpose.

otp.Tick can be used to generate a single tick for the whole time range:

data = otp.Tick(A=1)
data['B'] = 2
otp.run(data)
Time A B
0 2003-12-01 1 2

Or to generate ticks with some interval, for example every day:

data = otp.Tick(A=otp.math.rand(1, 100), bucket_interval=1, bucket_units='days')
otp.run(data, start=otp.dt(2022, 1, 1), end=otp.dt(2022, 1, 6))
Time A
0 2022-01-01 30
1 2022-01-02 58
2 2022-01-03 47
3 2022-01-04 9
4 2022-01-05 32

And otp.Ticks can be used to generate many ticks with different fixed values:

data = otp.Ticks({'A': list(range(5)), 'B': ['c', 'd', 'e', 'f', 'g']})
otp.run(data)
Time A B
0 2003-12-01 00:00:00.000 0 c
1 2003-12-01 00:00:00.001 1 d
2 2003-12-01 00:00:00.002 2 e
3 2003-12-01 00:00:00.003 3 f
4 2003-12-01 00:00:00.004 4 g

otp.Ticks can also be used to generate ticks from pandas.DataFrame:

import pandas as pd

df = pd.DataFrame({'Time': [pd.Timestamp(2022, 1, 1, 1, 1, 1), pd.Timestamp(2022, 1, 2, 2, 2, 2)], 'A': [1, 2]})
data = otp.Ticks(df)
otp.run(data, start=otp.dt(2022, 1, 1), end=otp.dt(2022, 1, 6))
Time A
0 2022-01-01 01:01:01 1
1 2022-01-02 02:02:02 2