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(2023, 5, 15, 9, 30)
e = otp.dt(2023, 5, 15, 9, 30, 1)

# we can retrieve the list of databases available
dbs = otp.databases()
list(dbs)[:5]
['ABU_DHABI', 'ABU_DHABI_BARS', 'ACTIV_ALPHA', 'ACTIV_ALPHA_X', 'ACTIV_ATHENS']
# the list of dates with data for the db
dbs['ACTIV_CANADA'].dates()[-5:]
[datetime.date(2024, 11, 25),
 datetime.date(2024, 11, 26),
 datetime.date(2024, 11, 27),
 datetime.date(2024, 11, 28),
 datetime.date(2024, 11, 29)]
# or just the last day with data
dbs['ACTIV_CANADA'].last_date
datetime.date(2024, 11, 29)
# and the list of tick types
dbs['ACTIV_CANADA'].tick_types()
['QTE', 'STAT', 'TRD']

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('ACTIV_CANADA')
otp.run(symbols, start=otp.dt(2023, 4, 11), end=otp.dt(2023, 4, 12))
Time SYMBOL_NAME
0 2023-04-11 A.H
1 2023-04-11 AAA.P
2 2023-04-11 AAAJ.P
3 2023-04-11 AAB
4 2023-04-11 AAC.P
... ... ...
5737 2023-04-11 ZYZ.SB.A
5738 2023-04-11 ZYZ.UN.U
5739 2023-04-11 ZZE.H
5740 2023-04-11 ZZZ
5741 2023-04-11 ZZZD

5742 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('NYSE_TAQ', tick_type='TRD')
otp.run(q, start=s, end=e, symbols=['SPY'])
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 2023-05-15 09:30:00.000178688 P T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39195 52983525167153 2023-05-15 09:30:00.000150016 1969-12-31 19:00:00.000000000 0
1 2023-05-15 09:30:00.000776704 Z N C 0 SPY 412.22 1969-12-31 19:00:00 0 247 0 39196 52983525035355 2023-05-15 09:30:00.000450000 1969-12-31 19:00:00.000000000 1
2 2023-05-15 09:30:00.003603456 T T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39212 62879133157950 2023-05-15 09:30:00.003165122 1969-12-31 19:00:00.000000000 0
3 2023-05-15 09:30:00.006352128 K I N C 0 SPY 412.24 1969-12-31 19:00:00 0 1 0 39227 52983525098301 2023-05-15 09:30:00.006091000 1969-12-31 19:00:00.000000000 0
4 2023-05-15 09:30:00.007128064 K I N C 0 SPY 412.24 1969-12-31 19:00:00 0 3 0 39231 52983525098302 2023-05-15 09:30:00.006873000 1969-12-31 19:00:00.000000000 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
310 2023-05-15 09:30:00.934032640 T T N C 0 SPY 412.27 1969-12-31 19:00:00 0 160 0 40517 62879133545538 2023-05-15 09:30:00.933678033 1969-12-31 19:00:00.000000000 1
311 2023-05-15 09:30:00.975609344 D I N C T 0 SPY 412.24 1969-12-31 19:00:00 0 2 0 40543 71675240595789 2023-05-15 09:30:00.661000000 2023-05-15 09:30:00.975241514 0
312 2023-05-15 09:30:00.980264448 D I N C T 0 SPY 412.27 1969-12-31 19:00:00 0 1 0 40545 71675240596294 2023-05-15 09:30:00.553501000 2023-05-15 09:30:00.979895700 0
313 2023-05-15 09:30:00.985391616 T N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40547 62879133552717 2023-05-15 09:30:00.984946104 1969-12-31 19:00:00.000000000 0
314 2023-05-15 09:30:00.985394944 T Q N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40549 62879133552719 2023-05-15 09:30:00.984958312 1969-12-31 19:00:00.000000000 1

315 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('NYSE_TAQ', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
otp.run(q, start=s, end=e, symbols=['SPY'])
Time PRICE SIZE COND EXCHANGE
0 2023-05-15 09:30:00.000178688 412.22 100 T P
1 2023-05-15 09:30:00.000776704 412.22 247 Z
2 2023-05-15 09:30:00.003603456 412.22 100 T T
3 2023-05-15 09:30:00.006352128 412.24 1 I K
4 2023-05-15 09:30:00.007128064 412.24 3 I K
... ... ... ... ... ...
310 2023-05-15 09:30:00.934032640 412.27 160 T T
311 2023-05-15 09:30:00.975609344 412.24 2 I D
312 2023-05-15 09:30:00.980264448 412.27 1 I D
313 2023-05-15 09:30:00.985391616 412.28 100 T
314 2023-05-15 09:30:00.985394944 412.28 100 Q T

315 rows × 5 columns

We can retrieve multiple time series.

q = otp.DataSource('NYSE_TAQ', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
mult = otp.run(q, start=s, end=e, symbols=['SPY', 'QQQ'])
mult
{'SPY':                              Time   PRICE  SIZE  COND EXCHANGE
 0   2023-05-15 09:30:00.000178688  412.22   100    T         P
 1   2023-05-15 09:30:00.000776704  412.22   247              Z
 2   2023-05-15 09:30:00.003603456  412.22   100    T         T
 3   2023-05-15 09:30:00.006352128  412.24     1     I        K
 4   2023-05-15 09:30:00.007128064  412.24     3     I        K
 ..                            ...     ...   ...   ...      ...
 310 2023-05-15 09:30:00.934032640  412.27   160    T         T
 311 2023-05-15 09:30:00.975609344  412.24     2     I        D
 312 2023-05-15 09:30:00.980264448  412.27     1     I        D
 313 2023-05-15 09:30:00.985391616  412.28   100              T
 314 2023-05-15 09:30:00.985394944  412.28   100     Q        T
 
 [315 rows x 5 columns],
 'QQQ':                              Time   PRICE  SIZE  COND EXCHANGE
 0   2023-05-15 09:30:00.000234302  325.51   300  @           B
 1   2023-05-15 09:30:00.001657060  325.54     2  @  I        Z
 2   2023-05-15 09:30:00.001661871  325.54   166  @           Z
 3   2023-05-15 09:30:00.001667551  325.54   167  @           Z
 4   2023-05-15 09:30:00.001671172  325.54   100  @           Z
 ..                            ...     ...   ...   ...      ...
 378 2023-05-15 09:30:00.973742814  325.52     4  @  I        P
 379 2023-05-15 09:30:00.974814807  325.53    50  @  I        Q
 380 2023-05-15 09:30:00.975917138  325.51     7  @  I        P
 381 2023-05-15 09:30:00.984404972  325.52    65  @F I        Z
 382 2023-05-15 09:30:00.999619637  325.50     6  @  I        Q
 
 [383 rows x 5 columns]}

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

mult['QQQ']
Time PRICE SIZE COND EXCHANGE
0 2023-05-15 09:30:00.000234302 325.51 300 @ B
1 2023-05-15 09:30:00.001657060 325.54 2 @ I Z
2 2023-05-15 09:30:00.001661871 325.54 166 @ Z
3 2023-05-15 09:30:00.001667551 325.54 167 @ Z
4 2023-05-15 09:30:00.001671172 325.54 100 @ Z
... ... ... ... ... ...
378 2023-05-15 09:30:00.973742814 325.52 4 @ I P
379 2023-05-15 09:30:00.974814807 325.53 50 @ I Q
380 2023-05-15 09:30:00.975917138 325.51 7 @ I P
381 2023-05-15 09:30:00.984404972 325.52 65 @F I Z
382 2023-05-15 09:30:00.999619637 325.50 6 @ I Q

383 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('NYSE_TAQ', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
mult = otp.run(q, start=s, end=e, symbols=otp.Symbols('NYSE_TAQ', pattern='AAA%'))
mult
{'AAA':                            Time  PRICE  SIZE  COND EXCHANGE
 0 2023-05-15 09:30:00.227737600  24.55    20     I        P
 1 2023-05-15 09:30:00.227740672  24.55    20     Q        P,
 'AAAU':                            Time   PRICE  SIZE  COND EXCHANGE
 0 2023-05-15 09:30:00.018481664  19.990  7985   O X        Z
 1 2023-05-15 09:30:00.033499392  19.990    50   4 I        D
 2 2023-05-15 09:30:00.034347520  19.990    61   4 I        D
 3 2023-05-15 09:30:00.034804480  19.990   250   4          D
 4 2023-05-15 09:30:00.041420288  19.990    77     I        K
 5 2023-05-15 09:30:00.042842624  19.990  6000    T         T
 6 2023-05-15 09:30:00.184702208  19.990     1     I        D
 7 2023-05-15 09:30:00.219001600  19.995     2     I        Z
 8 2023-05-15 09:30:00.817365248  19.995     1     I        D}

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('NYSE_TAQ', tick_type='TRD')
q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']]
q = otp.merge([q], symbols=['SPY', 'QQQ'], identify_input_ts=True)
single = otp.run(q, start=s, end=e)
single
Time PRICE SIZE COND EXCHANGE SYMBOL_NAME TICK_TYPE
0 2023-05-15 09:30:00.000178688 412.22 100 T P SPY TRD
1 2023-05-15 09:30:00.000234302 325.51 300 @ B QQQ TRD
2 2023-05-15 09:30:00.000776704 412.22 247 Z SPY TRD
3 2023-05-15 09:30:00.001657060 325.54 2 @ I Z QQQ TRD
4 2023-05-15 09:30:00.001661871 325.54 166 @ Z QQQ TRD
... ... ... ... ... ... ... ...
693 2023-05-15 09:30:00.980264448 412.27 1 I D SPY TRD
694 2023-05-15 09:30:00.984404972 325.52 65 @F I Z QQQ TRD
695 2023-05-15 09:30:00.985391616 412.28 100 T SPY TRD
696 2023-05-15 09:30:00.985394944 412.28 100 Q T SPY TRD
697 2023-05-15 09:30:00.999619637 325.50 6 @ I Q QQQ TRD

698 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('NYSE_TAQ', tick_type='TRD', start=s, end=e, symbols=['SPY'])
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 2023-05-15 09:30:00.000178688 P T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39195 52983525167153 2023-05-15 09:30:00.000150016 1969-12-31 19:00:00.000000000 0
1 2023-05-15 09:30:00.000776704 Z N C 0 SPY 412.22 1969-12-31 19:00:00 0 247 0 39196 52983525035355 2023-05-15 09:30:00.000450000 1969-12-31 19:00:00.000000000 1
2 2023-05-15 09:30:00.003603456 T T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39212 62879133157950 2023-05-15 09:30:00.003165122 1969-12-31 19:00:00.000000000 0
3 2023-05-15 09:30:00.006352128 K I N C 0 SPY 412.24 1969-12-31 19:00:00 0 1 0 39227 52983525098301 2023-05-15 09:30:00.006091000 1969-12-31 19:00:00.000000000 0
4 2023-05-15 09:30:00.007128064 K I N C 0 SPY 412.24 1969-12-31 19:00:00 0 3 0 39231 52983525098302 2023-05-15 09:30:00.006873000 1969-12-31 19:00:00.000000000 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
310 2023-05-15 09:30:00.934032640 T T N C 0 SPY 412.27 1969-12-31 19:00:00 0 160 0 40517 62879133545538 2023-05-15 09:30:00.933678033 1969-12-31 19:00:00.000000000 1
311 2023-05-15 09:30:00.975609344 D I N C T 0 SPY 412.24 1969-12-31 19:00:00 0 2 0 40543 71675240595789 2023-05-15 09:30:00.661000000 2023-05-15 09:30:00.975241514 0
312 2023-05-15 09:30:00.980264448 D I N C T 0 SPY 412.27 1969-12-31 19:00:00 0 1 0 40545 71675240596294 2023-05-15 09:30:00.553501000 2023-05-15 09:30:00.979895700 0
313 2023-05-15 09:30:00.985391616 T N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40547 62879133552717 2023-05-15 09:30:00.984946104 1969-12-31 19:00:00.000000000 0
314 2023-05-15 09:30:00.985394944 T Q N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40549 62879133552719 2023-05-15 09:30:00.984958312 1969-12-31 19:00:00.000000000 1

315 rows × 18 columns

For example, we can get the data from March 29 for SPY and from April 20 for QQQ.

spy = otp.DataSource('NYSE_TAQ', tick_type='TRD', start=otp.dt(2023, 3, 29, 9, 30), end=otp.dt(2023, 3, 29, 9, 30, 1), symbols=['SPY'])
qqq = otp.DataSource('NYSE_TAQ', tick_type='TRD', start=otp.dt(2023, 4, 20, 9, 30), end=otp.dt(2023, 4, 20, 9, 30, 1), symbols=['QQQ'])
merged = otp.merge([spy, qqq], identify_input_ts=True)
merged = merged[['PRICE', 'SIZE', 'SYMBOL_NAME']]
otp.run(merged)
Time PRICE SIZE SYMBOL_NAME
0 2023-03-29 09:30:00.000877568 399.920 400 SPY
1 2023-03-29 09:30:00.001151232 399.920 1000 SPY
2 2023-03-29 09:30:00.001154304 399.920 1000 SPY
3 2023-03-29 09:30:00.001921280 399.930 657 SPY
4 2023-03-29 09:30:00.010831360 399.925 100 SPY
... ... ... ... ...
749 2023-04-20 09:30:00.989278707 315.640 41 QQQ
750 2023-04-20 09:30:00.989278785 315.640 10 QQQ
751 2023-04-20 09:30:00.990793766 315.620 100 QQQ
752 2023-04-20 09:30:00.990868824 315.610 186 QQQ
753 2023-04-20 09:30:00.990872151 315.610 1314 QQQ

754 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('NYSE_TAQ', tick_type='TRD', start=s, end=e, symbols=['SPY', 'QQQ'])
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 2023-05-15 09:30:00.000178688 P T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39195 52983525167153 2023-05-15 09:30:00.000150016 1969-12-31 19:00:00.000000000 0
1 2023-05-15 09:30:00.000234302 B @ N 0 QQQ 325.51 1969-12-31 19:00:00 0 300 0 67371 44 2023-05-15 09:30:00.000211220 1969-12-31 19:00:00.000000000 0
2 2023-05-15 09:30:00.000776704 Z N C 0 SPY 412.22 1969-12-31 19:00:00 0 247 0 39196 52983525035355 2023-05-15 09:30:00.000450000 1969-12-31 19:00:00.000000000 1
3 2023-05-15 09:30:00.001657060 Z @ I N 0 QQQ 325.54 1969-12-31 19:00:00 0 2 0 67373 460 2023-05-15 09:30:00.001437000 1969-12-31 19:00:00.000000000 0
4 2023-05-15 09:30:00.001661871 Z @ N 0 QQQ 325.54 1969-12-31 19:00:00 0 166 0 67374 461 2023-05-15 09:30:00.001437000 1969-12-31 19:00:00.000000000 1
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
693 2023-05-15 09:30:00.980264448 D I N C T 0 SPY 412.27 1969-12-31 19:00:00 0 1 0 40545 71675240596294 2023-05-15 09:30:00.553501000 2023-05-15 09:30:00.979895700 0
694 2023-05-15 09:30:00.984404972 Z @F I N 1 QQQ 325.52 1969-12-31 19:00:00 0 65 0 73144 507 2023-05-15 09:30:00.984206000 1969-12-31 19:00:00.000000000 0
695 2023-05-15 09:30:00.985391616 T N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40547 62879133552717 2023-05-15 09:30:00.984946104 1969-12-31 19:00:00.000000000 0
696 2023-05-15 09:30:00.985394944 T Q N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40549 62879133552719 2023-05-15 09:30:00.984958312 1969-12-31 19:00:00.000000000 1
697 2023-05-15 09:30:00.999619637 Q @ I N 0 QQQ 325.50 1969-12-31 19:00:00 0 6 0 73175 1987 2023-05-15 09:30:00.999602699 1969-12-31 19:00:00.000000000 0

698 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=['NYSE_TAQ::SPY', 'TDI_FUT::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 2023-05-15 09:30:00.000000000 4143.00 4 TDI_FUT::ES_r_tdi TRD
1 2023-05-15 09:30:00.000178688 412.22 100 NYSE_TAQ::SPY TRD
2 2023-05-15 09:30:00.000776704 412.22 247 NYSE_TAQ::SPY TRD
3 2023-05-15 09:30:00.001000000 4143.00 4 TDI_FUT::ES_r_tdi TRD
4 2023-05-15 09:30:00.001000000 4143.25 1 TDI_FUT::ES_r_tdi TRD
... ... ... ... ... ...
547 2023-05-15 09:30:00.986000000 4143.75 9 TDI_FUT::ES_r_tdi TRD
548 2023-05-15 09:30:00.986000000 4143.75 1 TDI_FUT::ES_r_tdi TRD
549 2023-05-15 09:30:00.986000000 4143.75 2 TDI_FUT::ES_r_tdi TRD
550 2023-05-15 09:30:00.986000000 4143.75 2 TDI_FUT::ES_r_tdi TRD
551 2023-05-15 09:30:00.986000000 4143.75 2 TDI_FUT::ES_r_tdi TRD

552 rows × 5 columns

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

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

q = otp.merge([qte,nbbo], symbols=['SPY', 'QQQ'], identify_input_ts=True)
otp.run(q, start=s, end=s+otp.Milli(1))
Time BID_PRICE ASK_PRICE SYMBOL_NAME TICK_TYPE
0 2023-05-15 09:30:00.000174080 412.21 412.26 SPY QTE
1 2023-05-15 09:30:00.000174080 412.22 412.25 SPY NBBO
2 2023-05-15 09:30:00.000293590 325.51 325.55 QQQ QTE
3 2023-05-15 09:30:00.000293590 325.51 325.54 QQQ NBBO
4 2023-05-15 09:30:00.000524867 325.51 325.57 QQQ QTE
5 2023-05-15 09:30:00.000524962 325.06 325.57 QQQ QTE
6 2023-05-15 09:30:00.000524962 325.51 325.54 QQQ NBBO
7 2023-05-15 09:30:00.000580467 325.51 325.55 QQQ QTE
8 2023-05-15 09:30:00.000580467 325.51 325.54 QQQ NBBO
9 2023-05-15 09:30:00.000595200 412.21 412.24 SPY QTE
10 2023-05-15 09:30:00.000595200 412.22 412.24 SPY NBBO
11 2023-05-15 09:30:00.000610755 325.51 325.54 QQQ QTE
12 2023-05-15 09:30:00.000610755 325.51 325.54 QQQ NBBO
13 2023-05-15 09:30:00.000640528 325.51 325.54 QQQ QTE
14 2023-05-15 09:30:00.000640528 325.51 325.54 QQQ NBBO
15 2023-05-15 09:30:00.000715520 412.21 412.25 SPY QTE
16 2023-05-15 09:30:00.000715520 412.21 412.24 SPY NBBO
17 2023-05-15 09:30:00.000730138 325.51 325.54 QQQ QTE
18 2023-05-15 09:30:00.000730138 325.51 325.54 QQQ NBBO
19 2023-05-15 09:30:00.000752301 325.51 325.55 QQQ QTE
20 2023-05-15 09:30:00.000752301 325.51 325.54 QQQ NBBO
21 2023-05-15 09:30:00.000790784 412.22 412.25 SPY QTE
22 2023-05-15 09:30:00.000790784 412.22 412.24 SPY NBBO
23 2023-05-15 09:30:00.000845125 325.51 325.54 QQQ QTE
24 2023-05-15 09:30:00.000845125 325.51 325.54 QQQ NBBO
25 2023-05-15 09:30:00.000860928 412.22 412.25 SPY QTE
26 2023-05-15 09:30:00.000860928 412.22 412.24 SPY NBBO
27 2023-05-15 09:30:00.000924837 325.06 325.57 QQQ QTE
28 2023-05-15 09:30:00.000924837 325.51 325.54 QQQ NBBO
29 2023-05-15 09:30:00.000931072 412.21 412.24 SPY QTE
30 2023-05-15 09:30:00.000931072 412.21 412.24 SPY QTE
31 2023-05-15 09:30:00.000931072 412.22 412.24 SPY NBBO
32 2023-05-15 09:30:00.000931072 412.22 412.24 SPY NBBO
33 2023-05-15 09:30:00.000935936 412.21 412.26 SPY QTE
34 2023-05-15 09:30:00.000966144 412.21 412.25 SPY QTE
35 2023-05-15 09:30:00.000966144 412.22 412.25 SPY NBBO
36 2023-05-15 09:30:00.000996096 412.22 412.24 SPY QTE
37 2023-05-15 09:30:00.000996096 412.22 412.24 SPY NBBO

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 73
1 2022-01-02 99
2 2022-01-03 95
3 2022-01-04 21
4 2022-01-05 67

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