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']
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 | 84 |
1 | 2022-01-02 | 92 |
2 | 2022-01-03 | 84 |
3 | 2022-01-04 | 11 |
4 | 2022-01-05 | 66 |
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 |