Symbologies#
We can query data using different symbologies.
In this case parameter symbol_date of otp.run function should be specified.
This parameter specifies the date when queried securities had specified names
and it is used to determine the names that the security has between the query start and end times.
import onetick.py as otp
s = otp.dt(2024, 2, 1, 9, 30)
e = otp.dt(2024, 2, 1, 9, 30, 1)
trd = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE', 'EXCHANGE', 'COND']]
otp.run(trd, start=s, end=e, symbol_date=otp.dt(2022, 12, 1), symbols=['AAPL'])
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50018.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50019.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50020.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50016.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50017.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50018.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50019.
20251201120024 WARNING: WARN_02203365UWTWO: Unable to connect to server process on host development-queryhost.preprod-solutions.parent.onetick.com:50020.
| Time | PRICE | SIZE | EXCHANGE | COND | |
|---|---|---|---|---|---|
| 0 | 2024-02-01 09:30:00.000961260 | 184.010 | 302 | P | @FT |
| 1 | 2024-02-01 09:30:00.000961491 | 184.000 | 100 | P | @FT |
| 2 | 2024-02-01 09:30:00.000961701 | 184.000 | 1 | P | @FTI |
| 3 | 2024-02-01 09:30:00.000973163 | 184.000 | 1 | P | @FTI |
| 4 | 2024-02-01 09:30:00.000973355 | 184.000 | 5 | P | @FTI |
| ... | ... | ... | ... | ... | ... |
| 574 | 2024-02-01 09:30:00.987184691 | 183.900 | 9 | K | @F I |
| 575 | 2024-02-01 09:30:00.990378350 | 183.920 | 1 | D | @ I |
| 576 | 2024-02-01 09:30:00.991941892 | 183.935 | 1 | D | @ I |
| 577 | 2024-02-01 09:30:00.993785116 | 183.905 | 300 | D | @ |
| 578 | 2024-02-01 09:30:00.996512511 | 183.934 | 5 | D | @ I |
579 rows × 5 columns
otp.run(trd, start=s, end=e,
symbol_date=otp.dt(2022, 12, 1), symbols=['BSYM::::AAPL US Equity'])
| Time | PRICE | SIZE | EXCHANGE | COND | |
|---|---|---|---|---|---|
| 0 | 2024-02-01 09:30:00.000961260 | 184.010 | 302 | P | @FT |
| 1 | 2024-02-01 09:30:00.000961491 | 184.000 | 100 | P | @FT |
| 2 | 2024-02-01 09:30:00.000961701 | 184.000 | 1 | P | @FTI |
| 3 | 2024-02-01 09:30:00.000973163 | 184.000 | 1 | P | @FTI |
| 4 | 2024-02-01 09:30:00.000973355 | 184.000 | 5 | P | @FTI |
| ... | ... | ... | ... | ... | ... |
| 574 | 2024-02-01 09:30:00.987184691 | 183.900 | 9 | K | @F I |
| 575 | 2024-02-01 09:30:00.990378350 | 183.920 | 1 | D | @ I |
| 576 | 2024-02-01 09:30:00.991941892 | 183.935 | 1 | D | @ I |
| 577 | 2024-02-01 09:30:00.993785116 | 183.905 | 300 | D | @ |
| 578 | 2024-02-01 09:30:00.996512511 | 183.934 | 5 | D | @ I |
579 rows × 5 columns
Examples of supported symbologies include:
BSYM::AAPL US Equity(Bloomberg Symbol)FGC::BBG000B9XRY4(Composite FIGI)CUS::037833100(CUSIP)ISN::GB00BH4HKS39(ISIN)SED::BH4HKS3(SEDOL)
We can create a mapping between symbologies.
figi = otp.Symbols('US_COMP_SAMPLE', symbology='FGV', show_original_symbols=True, for_tick_type='TRD')
figi = figi.where(figi['SYMBOL_NAME'] != '')
otp.run(figi, start=s, end=e)
| Time | SYMBOL_NAME | ORIGINAL_SYMBOL_NAME | |
|---|---|---|---|
| 0 | 2024-02-01 09:30:00 | BBG000C2V3D6 | US_COMP_SAMPLE::A |
| 1 | 2024-02-01 09:30:00 | BBG005P7Q881 | US_COMP_SAMPLE::AAL |
| 2 | 2024-02-01 09:30:00 | BBG000B9XRY4 | US_COMP_SAMPLE::AAPL |
| 3 | 2024-02-01 09:30:00 | BBG0025Y4RY4 | US_COMP_SAMPLE::ABBV |
| 4 | 2024-02-01 09:30:00 | BBG001Y2XS07 | US_COMP_SAMPLE::ABNB |
| ... | ... | ... | ... |
| 496 | 2024-02-01 09:30:00 | BBG001D8R5D0 | US_COMP_SAMPLE::XYL |
| 497 | 2024-02-01 09:30:00 | BBG000BH3GZ2 | US_COMP_SAMPLE::YUM |
| 498 | 2024-02-01 09:30:00 | BBG000BKPL53 | US_COMP_SAMPLE::ZBH |
| 499 | 2024-02-01 09:30:00 | BBG000CC7LQ7 | US_COMP_SAMPLE::ZBRA |
| 500 | 2024-02-01 09:30:00 | BBG0039320N9 | US_COMP_SAMPLE::ZTS |
501 rows × 3 columns
btkr = otp.Symbols('US_COMP_SAMPLE', symbology='BTKR', show_original_symbols=True, for_tick_type='TRD')
btkr = btkr.where(btkr['SYMBOL_NAME'] != '')
otp.run(btkr, start=s, end=e)
| Time | SYMBOL_NAME | ORIGINAL_SYMBOL_NAME | |
|---|---|---|---|
| 0 | 2024-02-01 09:30:00 | A US | US_COMP_SAMPLE::A |
| 1 | 2024-02-01 09:30:00 | AAL US | US_COMP_SAMPLE::AAL |
| 2 | 2024-02-01 09:30:00 | AAPL US | US_COMP_SAMPLE::AAPL |
| 3 | 2024-02-01 09:30:00 | ABBV US | US_COMP_SAMPLE::ABBV |
| 4 | 2024-02-01 09:30:00 | ABNB US | US_COMP_SAMPLE::ABNB |
| ... | ... | ... | ... |
| 496 | 2024-02-01 09:30:00 | XYL US | US_COMP_SAMPLE::XYL |
| 497 | 2024-02-01 09:30:00 | YUM US | US_COMP_SAMPLE::YUM |
| 498 | 2024-02-01 09:30:00 | ZBH US | US_COMP_SAMPLE::ZBH |
| 499 | 2024-02-01 09:30:00 | ZBRA US | US_COMP_SAMPLE::ZBRA |
| 500 | 2024-02-01 09:30:00 | ZTS US | US_COMP_SAMPLE::ZTS |
501 rows × 3 columns
figi = otp.Symbols('US_COMP_SAMPLE', symbology='FGV', show_original_symbols=True, for_tick_type='TRD')
btkr = otp.Symbols('US_COMP_SAMPLE', symbology='BTKR', show_original_symbols=True, for_tick_type='TRD')
btkr = btkr.where(btkr['SYMBOL_NAME'] != '')
figi = figi.where(figi['SYMBOL_NAME'] != '')
mapping = otp.functions.join(figi, btkr, on=btkr['ORIGINAL_SYMBOL_NAME']==figi['ORIGINAL_SYMBOL_NAME'], how="inner")
mapping = mapping.rename({'RIGHT_SYMBOL_NAME' : 'BTKR',
'SYMBOL_NAME' : 'FIGI',
'ORIGINAL_SYMBOL_NAME': 'DB_SYMBOL'})
mapping = mapping[['FIGI', 'BTKR', 'DB_SYMBOL']]
otp.run(mapping, start=s, end=e)
| Time | FIGI | BTKR | DB_SYMBOL | |
|---|---|---|---|---|
| 0 | 2024-02-01 09:30:00 | BBG000C2V3D6 | A US | US_COMP_SAMPLE::A |
| 1 | 2024-02-01 09:30:00 | BBG005P7Q881 | AAL US | US_COMP_SAMPLE::AAL |
| 2 | 2024-02-01 09:30:00 | BBG000B9XRY4 | AAPL US | US_COMP_SAMPLE::AAPL |
| 3 | 2024-02-01 09:30:00 | BBG0025Y4RY4 | ABBV US | US_COMP_SAMPLE::ABBV |
| 4 | 2024-02-01 09:30:00 | BBG001Y2XS07 | ABNB US | US_COMP_SAMPLE::ABNB |
| ... | ... | ... | ... | ... |
| 496 | 2024-02-01 09:30:00 | BBG001D8R5D0 | XYL US | US_COMP_SAMPLE::XYL |
| 497 | 2024-02-01 09:30:00 | BBG000BH3GZ2 | YUM US | US_COMP_SAMPLE::YUM |
| 498 | 2024-02-01 09:30:00 | BBG000BKPL53 | ZBH US | US_COMP_SAMPLE::ZBH |
| 499 | 2024-02-01 09:30:00 | BBG000CC7LQ7 | ZBRA US | US_COMP_SAMPLE::ZBRA |
| 500 | 2024-02-01 09:30:00 | BBG0039320N9 | ZTS US | US_COMP_SAMPLE::ZTS |
501 rows × 4 columns
Symbol name changes#
Symbol name changes are easy to handle with the symbol_date parameter
of otp.run which provides the “as of” date for the symbols.
For example, we can pass META while setting symbol_date to any date after 2022-06-09 or FB and symbol_date before 2022-06-09.
trd = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
otp.run(trd, start=s, end=e, symbols='META', symbol_date=otp.dt(2023, 2, 2))
| 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.007987039 | Q | @ TI | N | 0 | META | 393.930 | 1969-12-31 19:00:00 | 0 | 50 | 0 | 239953 | 1951 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 1 | 2024-02-01 09:30:00.007990023 | Q | @ TI | N | 0 | META | 393.940 | 1969-12-31 19:00:00 | 0 | 34 | 0 | 239954 | 1952 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 2 | 2024-02-01 09:30:00.007991601 | Q | @ T | N | 0 | META | 393.960 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 239955 | 1953 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 2 | ||
| 3 | 2024-02-01 09:30:00.007993291 | Q | @ T | N | 0 | META | 393.960 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 239956 | 1954 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 3 | ||
| 4 | 2024-02-01 09:30:00.021757737 | P | @ | N | 0 | META | 393.940 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 240041 | 1555 | 2024-02-01 09:30:00.021408980 | 1969-12-31 19:00:00.000000000 | 0 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 307 | 2024-02-01 09:30:00.975760894 | D | @ I | N | Q | 0 | META | 393.845 | 1969-12-31 19:00:00 | 0 | 1 | 0 | 245808 | 1308 | 2024-02-01 09:30:00.397844000 | 2024-02-01 09:30:00.975736933 | 0 | |
| 308 | 2024-02-01 09:30:00.987474931 | D | @ | N | Q | 0 | META | 393.810 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 245840 | 1309 | 2024-02-01 09:30:00.986778211 | 2024-02-01 09:30:00.987441735 | 0 | |
| 309 | 2024-02-01 09:30:00.988587317 | D | @ I | N | Q | 0 | META | 393.930 | 1969-12-31 19:00:00 | 0 | 41 | 0 | 245848 | 1310 | 2024-02-01 09:30:00.987319939 | 2024-02-01 09:30:00.988558053 | 0 | |
| 310 | 2024-02-01 09:30:00.990261060 | V | @ | N | 0 | META | 393.810 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 245861 | 152 | 2024-02-01 09:30:00.989946291 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 311 | 2024-02-01 09:30:00.990282792 | V | @ | N | 0 | META | 393.810 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 245862 | 153 | 2024-02-01 09:30:00.989946291 | 1969-12-31 19:00:00.000000000 | 1 |
312 rows × 18 columns
trd = otp.DataSource('US_COMP_SAMPLE', tick_type='TRD')
otp.run(trd, start=s, end=e, symbols='FB', symbol_date=otp.dt(2021, 2, 2))
| 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.007987039 | Q | @ TI | N | 0 | META | 393.930 | 1969-12-31 19:00:00 | 0 | 50 | 0 | 239953 | 1951 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 1 | 2024-02-01 09:30:00.007990023 | Q | @ TI | N | 0 | META | 393.940 | 1969-12-31 19:00:00 | 0 | 34 | 0 | 239954 | 1952 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 1 | ||
| 2 | 2024-02-01 09:30:00.007991601 | Q | @ T | N | 0 | META | 393.960 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 239955 | 1953 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 2 | ||
| 3 | 2024-02-01 09:30:00.007993291 | Q | @ T | N | 0 | META | 393.960 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 239956 | 1954 | 2024-02-01 09:30:00.007968809 | 1969-12-31 19:00:00.000000000 | 3 | ||
| 4 | 2024-02-01 09:30:00.021757737 | P | @ | N | 0 | META | 393.940 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 240041 | 1555 | 2024-02-01 09:30:00.021408980 | 1969-12-31 19:00:00.000000000 | 0 | ||
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 307 | 2024-02-01 09:30:00.975760894 | D | @ I | N | Q | 0 | META | 393.845 | 1969-12-31 19:00:00 | 0 | 1 | 0 | 245808 | 1308 | 2024-02-01 09:30:00.397844000 | 2024-02-01 09:30:00.975736933 | 0 | |
| 308 | 2024-02-01 09:30:00.987474931 | D | @ | N | Q | 0 | META | 393.810 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 245840 | 1309 | 2024-02-01 09:30:00.986778211 | 2024-02-01 09:30:00.987441735 | 0 | |
| 309 | 2024-02-01 09:30:00.988587317 | D | @ I | N | Q | 0 | META | 393.930 | 1969-12-31 19:00:00 | 0 | 41 | 0 | 245848 | 1310 | 2024-02-01 09:30:00.987319939 | 2024-02-01 09:30:00.988558053 | 0 | |
| 310 | 2024-02-01 09:30:00.990261060 | V | @ | N | 0 | META | 393.810 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 245861 | 152 | 2024-02-01 09:30:00.989946291 | 1969-12-31 19:00:00.000000000 | 0 | ||
| 311 | 2024-02-01 09:30:00.990282792 | V | @ | N | 0 | META | 393.810 | 1969-12-31 19:00:00 | 0 | 100 | 0 | 245862 | 153 | 2024-02-01 09:30:00.989946291 | 1969-12-31 19:00:00.000000000 | 1 |
312 rows × 18 columns