# 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. ```ipython3 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')) ``` ```myst-ansi ['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'] ``` ```ipython3 # the list of dates with data for the db dbs['US_COMP_SAMPLE'].dates()[-5:] ``` ```myst-ansi [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)] ``` ```ipython3 # or just the last day with data dbs['US_COMP_SAMPLE'].last_date ``` ```myst-ansi datetime.date(2024, 3, 28) ``` ```ipython3 # and the list of tick types dbs['US_COMP_SAMPLE'].tick_types() ``` ```myst-ansi ['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). ```ipython3 symbols = otp.Symbols('US_COMP_SAMPLE') otp.run(symbols, start=otp.dt(2024, 2, 1), end=otp.dt(2024, 2, 2)) ``` ```myst-ansi 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 x 2 columns] ``` We used the [`otp.run`](../../api/run.md#onetick.py.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`](../../api/datetime/dt.md#onetick.py.datetime). Now that we have database names, tick types, and symbols, we are ready to query a time series. ```ipython3 q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD') otp.run(q, start=s, end=e, symbols=['AAPL']) ``` ```myst-ansi Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE \ 0 2024-02-01 09:30:00.000961260 P @FT N 1 1 2024-02-01 09:30:00.000961491 P @FT N 1 2 2024-02-01 09:30:00.000961701 P @FTI N 1 3 2024-02-01 09:30:00.000973163 P @FTI N 1 4 2024-02-01 09:30:00.000973355 P @FTI N 1 .. ... ... ... ... ... .. .. 574 2024-02-01 09:30:00.987184691 K @F I N 1 575 2024-02-01 09:30:00.990378350 D @ I N N 0 576 2024-02-01 09:30:00.991941892 D @ I N Q 0 577 2024-02-01 09:30:00.993785116 D @ N Q 0 578 2024-02-01 09:30:00.996512511 D @ I N Q 0 TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM \ 0 AAPL 184.010 1969-12-31 19:00:00 0 302 0 140448 1 AAPL 184.000 1969-12-31 19:00:00 0 100 0 140449 2 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140450 3 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140451 4 AAPL 184.000 1969-12-31 19:00:00 0 5 0 140452 .. ... ... ... ... ... ... ... 574 AAPL 183.900 1969-12-31 19:00:00 0 9 0 146846 575 AAPL 183.920 1969-12-31 19:00:00 0 1 0 146905 576 AAPL 183.935 1969-12-31 19:00:00 0 1 0 146927 577 AAPL 183.905 1969-12-31 19:00:00 0 300 0 146944 578 AAPL 183.934 1969-12-31 19:00:00 0 5 0 146970 TRADE_ID PARTICIPANT_TIME TRF_TIME \ 0 5239 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 1 5240 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 2 5241 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 3 5242 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 4 5243 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 .. ... ... ... 574 3099 2024-02-01 09:30:00.987000000 1969-12-31 19:00:00.000000000 575 1106 2024-02-01 09:30:00.975000000 2024-02-01 09:30:00.990038585 576 2606 2024-02-01 09:30:00.520147000 2024-02-01 09:30:00.991906728 577 2607 2024-02-01 09:30:00.970910000 2024-02-01 09:30:00.993753186 578 2608 2024-02-01 09:30:00.686000000 2024-02-01 09:30:00.996478568 OMDSEQ 0 0 1 1 2 2 3 3 4 4 .. ... 574 0 575 0 576 0 577 0 578 0 [579 rows x 18 columns] ``` Note that we specified the start and end of the time series to retrieve the corresponding interval. #### WARNING In [`otp.DataSource`](../../api/sources/data_source.md#onetick.py.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](../concepts/schema.md#schema-deduction-mechanism). Let's just keep the columns we're interested in to make it more digestible. ```ipython3 q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD') q = q[['PRICE', 'SIZE', 'COND', 'EXCHANGE']] otp.run(q, start=s, end=e, symbols=['AAPL']) ``` ```myst-ansi 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 retrieve multiple time series. ```ipython3 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 ``` ```myst-ansi {'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. ```ipython3 mult['MSFT'] ``` ```myst-ansi 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] ``` We can also retrieve all of the symbols from the database or all of the symbols matching a pattern. ```ipython3 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 ``` ```myst-ansi {'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`](../../api/functions/merge.md#onetick.py.merge) function. Parameter `identify_input_ts` here automatically adds `SYMBOL_NAME` and `TICK_TYPE` columns to the output, so each tick can be identified. ```ipython3 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 ``` ```myst-ansi Time PRICE SIZE COND EXCHANGE SYMBOL_NAME \ 0 2024-02-01 09:30:00.000961260 184.010 302 @FT P AAPL 1 2024-02-01 09:30:00.000961491 184.000 100 @FT P AAPL 2 2024-02-01 09:30:00.000961701 184.000 1 @FTI P AAPL 3 2024-02-01 09:30:00.000973163 184.000 1 @FTI P AAPL 4 2024-02-01 09:30:00.000973355 184.000 5 @FTI P AAPL ... ... ... ... ... ... ... 1199 2024-02-01 09:30:00.991941892 183.935 1 @ I D AAPL 1200 2024-02-01 09:30:00.993785116 183.905 300 @ D AAPL 1201 2024-02-01 09:30:00.996512511 183.934 5 @ I D AAPL 1202 2024-02-01 09:30:00.997095792 401.995 1 @ I D MSFT 1203 2024-02-01 09:30:00.999830840 401.995 1 @ I D MSFT TICK_TYPE 0 TRD 1 TRD 2 TRD 3 TRD 4 TRD ... ... 1199 TRD 1200 TRD 1201 TRD 1202 TRD 1203 TRD [1204 rows x 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. ```ipython3 q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD', start=s, end=e, symbols=['AAPL']) otp.run(q) ``` ```myst-ansi Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE \ 0 2024-02-01 09:30:00.000961260 P @FT N 1 1 2024-02-01 09:30:00.000961491 P @FT N 1 2 2024-02-01 09:30:00.000961701 P @FTI N 1 3 2024-02-01 09:30:00.000973163 P @FTI N 1 4 2024-02-01 09:30:00.000973355 P @FTI N 1 .. ... ... ... ... ... .. .. 574 2024-02-01 09:30:00.987184691 K @F I N 1 575 2024-02-01 09:30:00.990378350 D @ I N N 0 576 2024-02-01 09:30:00.991941892 D @ I N Q 0 577 2024-02-01 09:30:00.993785116 D @ N Q 0 578 2024-02-01 09:30:00.996512511 D @ I N Q 0 TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM \ 0 AAPL 184.010 1969-12-31 19:00:00 0 302 0 140448 1 AAPL 184.000 1969-12-31 19:00:00 0 100 0 140449 2 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140450 3 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140451 4 AAPL 184.000 1969-12-31 19:00:00 0 5 0 140452 .. ... ... ... ... ... ... ... 574 AAPL 183.900 1969-12-31 19:00:00 0 9 0 146846 575 AAPL 183.920 1969-12-31 19:00:00 0 1 0 146905 576 AAPL 183.935 1969-12-31 19:00:00 0 1 0 146927 577 AAPL 183.905 1969-12-31 19:00:00 0 300 0 146944 578 AAPL 183.934 1969-12-31 19:00:00 0 5 0 146970 TRADE_ID PARTICIPANT_TIME TRF_TIME \ 0 5239 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 1 5240 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 2 5241 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 3 5242 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 4 5243 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 .. ... ... ... 574 3099 2024-02-01 09:30:00.987000000 1969-12-31 19:00:00.000000000 575 1106 2024-02-01 09:30:00.975000000 2024-02-01 09:30:00.990038585 576 2606 2024-02-01 09:30:00.520147000 2024-02-01 09:30:00.991906728 577 2607 2024-02-01 09:30:00.970910000 2024-02-01 09:30:00.993753186 578 2608 2024-02-01 09:30:00.686000000 2024-02-01 09:30:00.996478568 OMDSEQ 0 0 1 1 2 2 3 3 4 4 .. ... 574 0 575 0 576 0 577 0 578 0 [579 rows x 18 columns] ``` For example, we can get the data from February 24 for AAPL and from March 20 for MSFT. ```ipython3 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) ``` ```myst-ansi 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 x 4 columns] ``` We can also specify multiple symbols in the data source in which case they will be merged by time. ```ipython3 q = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD', start=s, end=e, symbols=['AAPL', 'MSFT']) otp.run(q) ``` ```myst-ansi Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE \ 0 2024-02-01 09:30:00.000961260 P @FT N 1 1 2024-02-01 09:30:00.000961491 P @FT N 1 2 2024-02-01 09:30:00.000961701 P @FTI N 1 3 2024-02-01 09:30:00.000973163 P @FTI N 1 4 2024-02-01 09:30:00.000973355 P @FTI N 1 ... ... ... ... ... ... .. .. 1199 2024-02-01 09:30:00.991941892 D @ I N Q 0 1200 2024-02-01 09:30:00.993785116 D @ N Q 0 1201 2024-02-01 09:30:00.996512511 D @ I N Q 0 1202 2024-02-01 09:30:00.997095792 D @ I N Q 0 1203 2024-02-01 09:30:00.999830840 D @ I N Q 0 TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM \ 0 AAPL 184.010 1969-12-31 19:00:00 0 302 0 140448 1 AAPL 184.000 1969-12-31 19:00:00 0 100 0 140449 2 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140450 3 AAPL 184.000 1969-12-31 19:00:00 0 1 0 140451 4 AAPL 184.000 1969-12-31 19:00:00 0 5 0 140452 ... ... ... ... ... ... ... ... 1199 AAPL 183.935 1969-12-31 19:00:00 0 1 0 146927 1200 AAPL 183.905 1969-12-31 19:00:00 0 300 0 146944 1201 AAPL 183.934 1969-12-31 19:00:00 0 5 0 146970 1202 MSFT 401.995 1969-12-31 19:00:00 0 1 0 245899 1203 MSFT 401.995 1969-12-31 19:00:00 0 1 0 245911 TRADE_ID PARTICIPANT_TIME TRF_TIME \ 0 5239 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 1 5240 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 2 5241 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 3 5242 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 4 5243 2024-02-01 09:30:00.000585731 1969-12-31 19:00:00.000000000 ... ... ... ... 1199 2606 2024-02-01 09:30:00.520147000 2024-02-01 09:30:00.991906728 1200 2607 2024-02-01 09:30:00.970910000 2024-02-01 09:30:00.993753186 1201 2608 2024-02-01 09:30:00.686000000 2024-02-01 09:30:00.996478568 1202 2619 2024-02-01 09:30:00.497061000 2024-02-01 09:30:00.997066904 1203 2620 2024-02-01 09:30:00.311890000 2024-02-01 09:30:00.999804780 OMDSEQ 0 0 1 1 2 2 3 3 4 4 ... ... 1199 0 1200 0 1201 0 1202 0 1203 0 [1204 rows x 18 columns] ``` We can look up symbols in multiple databases. ```ipython3 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) ``` ```myst-ansi Time PRICE SIZE SYMBOL_NAME \ 0 2024-02-01 09:30:00.000961260 184.010 302 US_COMP_SAMPLE::AAPL 1 2024-02-01 09:30:00.000961491 184.000 100 US_COMP_SAMPLE::AAPL 2 2024-02-01 09:30:00.000961701 184.000 1 US_COMP_SAMPLE::AAPL 3 2024-02-01 09:30:00.000973163 184.000 1 US_COMP_SAMPLE::AAPL 4 2024-02-01 09:30:00.000973355 184.000 5 US_COMP_SAMPLE::AAPL ... ... ... ... ... 1065 2024-02-01 09:30:00.987184691 183.900 9 US_COMP_SAMPLE::AAPL 1066 2024-02-01 09:30:00.990378350 183.920 1 US_COMP_SAMPLE::AAPL 1067 2024-02-01 09:30:00.991941892 183.935 1 US_COMP_SAMPLE::AAPL 1068 2024-02-01 09:30:00.993785116 183.905 300 US_COMP_SAMPLE::AAPL 1069 2024-02-01 09:30:00.996512511 183.934 5 US_COMP_SAMPLE::AAPL TICK_TYPE 0 TRD 1 TRD 2 TRD 3 TRD 4 TRD ... ... 1065 TRD 1066 TRD 1067 TRD 1068 TRD 1069 TRD [1070 rows x 5 columns] ``` We can also look up the same symbols in different databases (even if they have different tick types). ```ipython3 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)) ``` ```myst-ansi 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`](../../api/sources/tick.md#onetick.py.Tick) and [`otp.Ticks`](../../api/sources/ticks.md#onetick.py.Ticks) can be used for this purpose. [`otp.Tick`](../../api/sources/tick.md#onetick.py.Tick) can be used to generate a single tick for the whole time range: ```ipython3 data = otp.Tick(A=1) data['B'] = 2 otp.run(data) ``` ```myst-ansi Time A B 0 2024-02-01 1 2 ``` Or to generate ticks with some interval, for example every day: ```ipython3 data = otp.Tick(A=otp.math.rand(1, 100), bucket_interval=1, bucket_units='days') otp.run(data, start=otp.dt(2024, 2, 1), end=otp.dt(2024, 2, 6)) ``` ```myst-ansi Time A 0 2024-02-01 41 1 2024-02-02 3 2 2024-02-03 30 3 2024-02-04 11 4 2024-02-05 2 ``` And [`otp.Ticks`](../../api/sources/ticks.md#onetick.py.Ticks) can be used to generate many ticks with different fixed values: ```ipython3 data = otp.Ticks({'A': list(range(5)), 'B': ['c', 'd', 'e', 'f', 'g']}) otp.run(data) ``` ```myst-ansi Time A B 0 2024-02-01 00:00:00.000 0 c 1 2024-02-01 00:00:00.001 1 d 2 2024-02-01 00:00:00.002 2 e 3 2024-02-01 00:00:00.003 3 f 4 2024-02-01 00:00:00.004 4 g ``` [`otp.Ticks`](../../api/sources/ticks.md#onetick.py.Ticks) can also be used to generate ticks from `pandas.DataFrame`: ```ipython3 import pandas as pd df = pd.DataFrame({'Time': [pd.Timestamp(2024, 2, 1, 1, 1, 1), pd.Timestamp(2024, 2, 2, 2, 2, 2)], 'A': [1, 2]}) data = otp.Ticks(df) otp.run(data, start=otp.dt(2024, 2, 1), end=otp.dt(2024, 2, 6)) ``` ```myst-ansi /onetick-py/src/onetick/py/sources/ticks.py:493: FutureWarning: Using pandas DataFrame as `data` parameter is deprecated, use `otp.LoadTicksFromDataFrame` source instead. warnings.warn( ``` ```myst-ansi Time A 0 2024-02-01 01:01:01 1 1 2024-02-02 02:02:02 2 ```