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, 12, 16),
datetime.date(2024, 12, 17),
datetime.date(2024, 12, 18),
datetime.date(2024, 12, 19),
datetime.date(2024, 12, 20)]
# or just the last day with data
dbs['ACTIV_CANADA'].last_date
datetime.date(2024, 12, 20)
# and the list of tick types
dbs['ACTIV_CANADA'].tick_types()
['DAY', '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 | 76 |
| 1 | 2022-01-02 | 57 |
| 2 | 2022-01-03 | 48 |
| 3 | 2022-01-04 | 53 |
| 4 | 2022-01-05 | 8 |
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 |