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'])
| 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