Symbol Selection#

This section contains examples for Symbol Selection using the onetick-py.
Each example is a self-contained script that can be run against the OneTick Cloud sample databases.

# onetick-py WebAPI configuration for OneTick Cloud
import os
os.environ['OTP_WEBAPI'] = '1'
os.environ['OTP_HTTP_ADDRESS'] = 'https://rest.cloud.onetick.com'
os.environ['OTP_ACCESS_TOKEN_URL'] = 'https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token'
os.environ['OTP_CLIENT_ID'] = '__FILL_IN__'
os.environ['OTP_CLIENT_SECRET'] = '__FILL_IN__'

Data Retrieval with Single Symbol#

Retrieve Data for a single symbol, by passing a single symbol string into otp.run.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
# Return first 100 Rows
data = data.limit(100)
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
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-01-03 09:30:00.065443591 Z @ I N 0 CSCO 50.0200 1969-12-31 19:00:00 0 2 0 169103 42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00.000000000 0
1 2024-01-03 09:30:00.111130049 Z @ I N 0 CSCO 50.1600 1969-12-31 19:00:00 0 3 0 169148 43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00.000000000 0
2 2024-01-03 09:30:00.127459523 V @ N 0 CSCO 50.1700 1969-12-31 19:00:00 0 100 0 169181 13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00.000000000 0
3 2024-01-03 09:30:00.128498068 Z @F I N 1 CSCO 50.1700 1969-12-31 19:00:00 0 5 0 169182 44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00.000000000 0
4 2024-01-03 09:30:00.135190071 Q @FTI N 1 CSCO 50.1300 1969-12-31 19:00:00 0 46 0 169193 349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00.000000000 0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
95 2024-01-03 09:30:00.897416820 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 51 0 171857 365 2024-01-03 09:30:00.897398456 1969-12-31 19:00:00.000000000 3
96 2024-01-03 09:30:00.897444128 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 29 0 171858 366 2024-01-03 09:30:00.897428791 1969-12-31 19:00:00.000000000 4
97 2024-01-03 09:30:00.897474806 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 12 0 171859 367 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000 5
98 2024-01-03 09:30:00.897476583 Q @F I N 1 CSCO 50.0800 1969-12-31 19:00:00 0 2 0 171860 368 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000 6
99 2024-01-03 09:30:00.897670983 D @ N Q 0 CSCO 50.0797 1969-12-31 19:00:00 0 100 0 171862 100 2024-01-03 09:30:00.896374950 2024-01-03 09:30:00.897637536 7

100 rows × 18 columns

Data Retrieval with List of Symbols#

Retrieve Data for a set of symbols, by passing a List of Symbols in otp.run.

import onetick.py as otp

# Define the DataSource
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Limit to 1000 rows
data = data.limit(1000)

# Specify the Symbol List and Time Range
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols=['CSCO', 'MSFT'])
result
{'CSCO':                              Time EXCHANGE  COND STOP_STOCK SOURCE TRF TTE  \
 0   2024-01-03 09:30:00.065443591        Z  @  I                 N       0   
 1   2024-01-03 09:30:00.111130049        Z  @  I                 N       0   
 2   2024-01-03 09:30:00.127459523        V  @                    N       0   
 3   2024-01-03 09:30:00.128498068        Z  @F I                 N       1   
 4   2024-01-03 09:30:00.135190071        Q  @FTI                 N       1   
 ..                            ...      ...   ...        ...    ...  ..  ..   
 995 2024-01-03 09:30:07.378682043        Q  @  I                 N       0   
 996 2024-01-03 09:30:07.378683771        Q  @  I                 N       0   
 997 2024-01-03 09:30:07.379088757        Z  @F                   N       1   
 998 2024-01-03 09:30:07.390014965        P  @                    N       0   
 999 2024-01-03 09:30:07.396341167        D  @4 I                 N   Q   1   
 
     TICKER  PRICE        DELETED_TIME  TICK_STATUS  SIZE  CORR  SEQ_NUM  \
 0     CSCO  50.02 1969-12-31 19:00:00            0     2     0   169103   
 1     CSCO  50.16 1969-12-31 19:00:00            0     3     0   169148   
 2     CSCO  50.17 1969-12-31 19:00:00            0   100     0   169181   
 3     CSCO  50.17 1969-12-31 19:00:00            0     5     0   169182   
 4     CSCO  50.13 1969-12-31 19:00:00            0    46     0   169193   
 ..     ...    ...                 ...          ...   ...   ...      ...   
 995   CSCO  50.12 1969-12-31 19:00:00            0     2     0   192393   
 996   CSCO  50.12 1969-12-31 19:00:00            0    50     0   192394   
 997   CSCO  50.12 1969-12-31 19:00:00            0   100     0   192396   
 998   CSCO  50.12 1969-12-31 19:00:00            0   100     0   192406   
 999   CSCO  50.09 1969-12-31 19:00:00            0     1     0   192414   
 
     TRADE_ID              PARTICIPANT_TIME                      TRF_TIME  \
 0         42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00.000000000   
 1         43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00.000000000   
 2         13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00.000000000   
 3         44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00.000000000   
 4        349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00.000000000   
 ..       ...                           ...                           ...   
 995      636 2024-01-03 09:30:07.378665797 1969-12-31 19:00:00.000000000   
 996      637 2024-01-03 09:30:07.378665797 1969-12-31 19:00:00.000000000   
 997      138 2024-01-03 09:30:07.378898000 1969-12-31 19:00:00.000000000   
 998      337 2024-01-03 09:30:07.389672607 1969-12-31 19:00:00.000000000   
 999      453 2024-01-03 09:30:02.589000000 2024-01-03 09:30:07.396318790   
 
      OMDSEQ  
 0         0  
 1         0  
 2         0  
 3         0  
 4         0  
 ..      ...  
 995       2  
 996       3  
 997       0  
 998       0  
 999       0  
 
 [1000 rows x 18 columns],
 'MSFT':                              Time EXCHANGE  COND STOP_STOCK SOURCE TRF TTE  \
 0   2024-01-03 09:30:00.001141139        Q  @FTI                 N       1   
 1   2024-01-03 09:30:00.001232095        Q  @FTI                 N       1   
 2   2024-01-03 09:30:00.001348140        Q  @ TI                 N       0   
 3   2024-01-03 09:30:00.001835253        Z  @  I                 N       0   
 4   2024-01-03 09:30:00.002218671        P  @ T                  N       0   
 ..                            ...      ...   ...        ...    ...  ..  ..   
 995 2024-01-03 09:30:01.980424744        Z  @F I                 N       1   
 996 2024-01-03 09:30:01.980430260        Z  @F I                 N       1   
 997 2024-01-03 09:30:01.980438693        Z  @F I                 N       1   
 998 2024-01-03 09:30:01.980454705        Z  @F I                 N       1   
 999 2024-01-03 09:30:01.980463011        Z  @F I                 N       1   
 
     TICKER    PRICE        DELETED_TIME  TICK_STATUS  SIZE  CORR  SEQ_NUM  \
 0     MSFT  368.990 1969-12-31 19:00:00            0     9     0   251795   
 1     MSFT  369.000 1969-12-31 19:00:00            0    10     0   251798   
 2     MSFT  369.000 1969-12-31 19:00:00            0    90     0   251800   
 3     MSFT  368.995 1969-12-31 19:00:00            0     1     0   251803   
 4     MSFT  368.990 1969-12-31 19:00:00            0   100     0   251808   
 ..     ...      ...                 ...          ...   ...   ...      ...   
 995   MSFT  369.100 1969-12-31 19:00:00            0     1     0   262423   
 996   MSFT  369.100 1969-12-31 19:00:00            0     1     0   262424   
 997   MSFT  369.100 1969-12-31 19:00:00            0     1     0   262425   
 998   MSFT  369.100 1969-12-31 19:00:00            0     1     0   262426   
 999   MSFT  369.100 1969-12-31 19:00:00            0     1     0   262427   
 
     TRADE_ID              PARTICIPANT_TIME            TRF_TIME  OMDSEQ  
 0       2626 2024-01-03 09:30:00.001115161 1969-12-31 19:00:00       0  
 1       2627 2024-01-03 09:30:00.001215210 1969-12-31 19:00:00       1  
 2       2628 2024-01-03 09:30:00.001330517 1969-12-31 19:00:00       2  
 3        340 2024-01-03 09:30:00.001612000 1969-12-31 19:00:00       3  
 4       3095 2024-01-03 09:30:00.001875282 1969-12-31 19:00:00       0  
 ..       ...                           ...                 ...     ...  
 995      501 2024-01-03 09:30:01.980246000 1969-12-31 19:00:00      36  
 996      502 2024-01-03 09:30:01.980252000 1969-12-31 19:00:00      37  
 997      503 2024-01-03 09:30:01.980258000 1969-12-31 19:00:00      38  
 998      504 2024-01-03 09:30:01.980269000 1969-12-31 19:00:00      39  
 999      505 2024-01-03 09:30:01.980279000 1969-12-31 19:00:00      40  
 
 [1000 rows x 18 columns]}

Data Retrieval for All Db symbols#

If no symbol is specified, data for all symbols in a database can be retrieved by merging across every symbol with otp.Symbols.
The below returns data on the first 1000 symbols.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')

# Merge the daily record of every symbol in the database into a single stream.
data = otp.merge([data],
                 symbols=otp.Symbols(db='US_COMP_SAMPLE_DAILY', for_tick_type='DAY'),
                 identify_input_ts=True)
data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 1, 4),
                 timezone='America/New_York')
result
Time CLOSE EXCHANGE HIGH LOW OMDSEQ OPEN PRICE_CLOSING_AUCTION PRICE_OPENING_AUCTION VOLUME ... VOLUME_MAIN_SESSION VOLUME_ODD_LOT VOLUME_OFF_EXCHANGE VOLUME_OPENING_AUCTION VOLUME_POST_MARKET VOLUME_PRE_MARKET VOLUME_ROUND_LOT VWAP SYMBOL_NAME TICK_TYPE
0 2024-01-03 20:15:00 131.160 138.0000 131.0650 17 138.000 NaN NaN 2074480 ... 1652200 647218 769914 9301 127905 144 1427262 132.476380 A DAY
1 2024-01-03 20:15:00 31.490 32.2700 31.0450 36 32.120 NaN NaN 8245658 ... 7043801 835482 4077035 70320 726723 39655 7410176 31.686287 AA DAY
2 2024-01-03 20:15:00 25.025 25.0800 24.9835 44 25.020 NaN NaN 7543 ... 7218 934 1339 325 0 0 6609 25.040404 AAA DAY
3 2024-01-03 20:15:00 20.215 20.2900 20.1140 61 20.175 NaN NaN 2110058 ... 2094872 16180 965993 11921 39 1946 2093878 20.170415 AAAU DAY
4 2024-01-03 20:15:00 1.040 1.2342 1.0400 68 1.090 NaN NaN 2567 ... 1937 1355 1348 164 119 199 1212 1.089405 AACG DAY
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
995 2024-01-03 20:15:00 NaN NaN NaN 12722 NaN NaN NaN 0 ... 0 0 0 0 0 0 0 NaN BARKWS DAY
996 2024-01-03 20:15:00 20.410 21.3600 20.3100 12740 20.930 NaN NaN 371543 ... 294670 90716 98811 3654 8428 201 280827 20.618582 BASE DAY
997 2024-01-03 20:15:00 9.590 9.5900 9.5700 12756 9.570 NaN NaN 16149 ... 15865 2567 3240 0 0 0 13582 9.584204 BATL DAY
998 2024-01-03 20:15:00 41.730 42.3500 41.6500 12772 42.240 NaN NaN 41428 ... 33097 14042 8413 664 1860 105 27386 41.936823 BATRA DAY
999 2024-01-03 20:15:00 38.500 39.0400 38.4650 12789 39.040 NaN NaN 158261 ... 72497 41267 73798 2355 51131 313 116994 38.585355 BATRK DAY

1000 rows × 21 columns

Data Retrieval with Symbol Mask#

Retrieve Data for symbols matched by pattern.

import onetick.py as otp

# Define your symbol mask, e.g., all symbols starting with 'AA'
symbol_mask = 'AA%'

# Get all symbols matching the mask
symbols = otp.Symbols(db='US_COMP_SAMPLE_DAILY', pattern=symbol_mask)

# Define Data Source, in this case for DAY records
data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')

# Merge data into a single result across symbols
merged_data = otp.merge(data, symbols=symbols, identify_input_ts=True)

# Run query, with defined time range and time zone
result = otp.run(merged_data,
                 start=otp.dt(2024, 1, 2),
                 end=otp.dt(2024, 1, 3),
                 timezone='America/New_York')
result
Time CLOSE EXCHANGE HIGH LOW OMDSEQ OPEN PRICE_CLOSING_AUCTION PRICE_OPENING_AUCTION VOLUME ... VOLUME_MAIN_SESSION VOLUME_ODD_LOT VOLUME_OFF_EXCHANGE VOLUME_OPENING_AUCTION VOLUME_POST_MARKET VOLUME_PRE_MARKET VOLUME_ROUND_LOT VWAP SYMBOL_NAME TICK_TYPE
0 2024-01-02 20:15:00 33.280 A 33.89 33.0800 18 33.890 NaN NaN 3744 ... 3744 920 0 0 0 0 2824 33.226834 AA DAY
1 2024-01-02 20:15:00 33.290 B 33.94 33.0500 19 33.420 NaN NaN 16712 ... 16712 5119 0 0 0 0 11593 33.411275 AA DAY
2 2024-01-02 20:15:00 33.300 C 33.94 33.0500 20 33.455 NaN NaN 12495 ... 12495 5195 0 0 0 0 7300 33.217808 AA DAY
3 2024-01-02 20:15:00 33.285 D 33.97 33.0428 21 33.435 NaN NaN 1503127 ... 1361152 123934 1503127 0 138988 2987 1379193 33.474980 AA DAY
4 2024-01-02 20:15:00 33.310 H 33.95 33.0600 22 33.620 NaN NaN 49660 ... 49660 11096 0 0 0 0 38564 33.526722 AA DAY
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
362 2024-01-02 20:15:00 65.470 V 65.75 65.3300 380 65.610 NaN NaN 14151 ... 14151 1021 0 0 0 0 13130 65.445476 AAXJ DAY
363 2024-01-02 20:15:00 65.435 X 65.62 65.3600 381 65.620 NaN NaN 12857 ... 12857 172 0 0 0 0 12685 65.493074 AAXJ DAY
364 2024-01-02 20:15:00 65.465 Y 65.73 65.3200 382 65.615 NaN NaN 15640 ... 15640 610 0 0 0 0 15030 65.519747 AAXJ DAY
365 2024-01-02 20:15:00 65.440 Z 65.73 65.3100 383 65.620 NaN NaN 48594 ... 48594 4091 0 0 0 0 44503 65.490934 AAXJ DAY
366 2024-01-02 20:15:00 65.440 65.76 65.3050 384 65.470 NaN NaN 641995 ... 622992 36082 159440 2733 0 40 605913 65.487922 AAXJ DAY

367 rows × 21 columns

Data Retrieval Across Databases#

Retrieve Trades for Symbols across Databases for the specified time range.
The initial otp.DataSource is defined without specifying the Database or symbol.
The schema of the Data Source is specified manually.
Symbols are specified including the Database name, with format [Database]::[Symbol] e.g. LSE::VOD.

import onetick.py as otp

# Define the Symbol List
sym_list = ['LSE::VOD', 'EURONEXT::AF', 'XETRA::DBK', 'LSE::TSCO',
            'LSE::SHEL', 'EURONEXT::AF', 'LSE::VOD', 'XETRA::DBK']

# Define Data Source, in this case without specifying the Database or symbol name.
# As the schema is not yet known, set the schema policy to manual
trd = otp.DataSource(tick_type='TRD', schema_policy='manual')
# Define the output schema
trd.schema.set(
    PRICE=float,
    SIZE=int,
    TRADE_VENUE=str,
    BOOK_TYPE=str,
    TRADE_PERIOD=str
)
# Specify Output Fields
trd = trd[['PRICE', 'SIZE', 'TRADE_VENUE', 'BOOK_TYPE', 'TRADE_PERIOD']]

# Filter on Lit Order Book
trd = trd.where(trd['BOOK_TYPE'] == '0')

# Filter on Continuous Trading
trd = trd.where(trd['TRADE_PERIOD'] == '-')

# Create a single output, merging all the inputs into a single resultset.
merged = otp.merge([trd], symbols=sym_list, identify_input_ts=True, separate_db_name=True)

# Return first 1000 Rows
merged = merged.limit(1000)

# Run the query returning the data in the selected timezone
result = otp.run(merged,
                 start=otp.datetime(2024, 1, 3, 8),
                 end=otp.datetime(2024, 1, 4, 16),
                 timezone='Europe/London')
result
Time PRICE SIZE TRADE_VENUE BOOK_TYPE TRADE_PERIOD SYMBOL_NAME DB_NAME TICK_TYPE
0 2024-01-03 08:00:03.055 2574.500 147 XLON 0 - SHEL LSE TRD
1 2024-01-03 08:00:03.056 2574.500 46 XLON 0 - SHEL LSE TRD
2 2024-01-03 08:00:03.056 2574.500 44 XLON 0 - SHEL LSE TRD
3 2024-01-03 08:00:04.494 2574.500 207 XLON 0 - SHEL LSE TRD
4 2024-01-03 08:00:05.880 2575.000 24 XLON 0 - SHEL LSE TRD
... ... ... ... ... ... ... ... ... ...
995 2024-01-03 08:06:57.076 13.302 23 XPAR 0 - AF EURONEXT TRD
996 2024-01-03 08:06:57.151 295.200 2200 XLON 0 - TSCO LSE TRD
997 2024-01-03 08:06:57.151 295.200 22742 XLON 0 - TSCO LSE TRD
998 2024-01-03 08:06:57.151 295.200 760 XLON 0 - TSCO LSE TRD
999 2024-01-03 08:06:57.151 295.300 992 XLON 0 - TSCO LSE TRD

1000 rows × 9 columns

Data Retrieval across Symbol Changes#

Ceridian HCM Holding rebranded as Dayforce, Inc on 1st Feb 2024, changing its symbol from CDAY to DAY.

Full History can be retrieved by querying for both symbols.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data[['EXCHANGE', 'OPEN', 'HIGH', 'LOW', 'CLOSE']]
result = otp.run(data,
                 start=otp.dt(2024, 1, 1),
                 end=otp.dt(2024, 4, 1),
                 timezone='America/New_York',
                 symbols=['CDAY', 'DAY'])
result
{'CDAY':                    Time EXCHANGE   OPEN   HIGH    LOW  CLOSE
 0   2024-01-02 20:15:00        A  65.80  66.16  65.74  66.04
 1   2024-01-02 20:15:00        B  65.74  66.13  65.74  66.12
 2   2024-01-02 20:15:00        C  66.18  66.18  65.63  66.12
 3   2024-01-02 20:15:00        D  66.10  66.80  65.25  66.02
 4   2024-01-02 20:15:00        H  66.69  66.69  65.71  66.08
 ..                  ...      ...    ...    ...    ...    ...
 355 2024-01-30 20:15:00        V  68.75  69.89  68.58  69.47
 356 2024-01-30 20:15:00        X  68.70  69.53  68.70  69.42
 357 2024-01-30 20:15:00        Y  69.11  69.70  69.10  69.44
 358 2024-01-30 20:15:00        Z  68.74  69.91  68.66  69.47
 359 2024-01-30 20:15:00           68.80  69.96  68.46  69.45
 
 [360 rows x 6 columns],
 'DAY':                    Time EXCHANGE    OPEN    HIGH     LOW   CLOSE
 0   2024-02-01 20:15:00        A  70.990  70.990  68.840  69.960
 1   2024-02-01 20:15:00        B  69.400  70.575  69.400  69.930
 2   2024-02-01 20:15:00        C  69.720  70.310  68.750  69.910
 3   2024-02-01 20:15:00        D  68.755  71.760  68.065  69.990
 4   2024-02-01 20:15:00        H  68.940  70.235  68.280  70.235
 ..                  ...      ...     ...     ...     ...     ...
 692 2024-03-28 20:15:00        V  66.150  66.375  65.695  66.235
 693 2024-03-28 20:15:00        X  66.070  66.165  65.710  66.165
 694 2024-03-28 20:15:00        Y  66.100  66.330  66.010  66.330
 695 2024-03-28 20:15:00        Z  66.310  66.375  65.690  66.360
 696 2024-03-28 20:15:00           66.450  66.450  65.650  66.210
 
 [697 rows x 6 columns]}

Data Retrieval with New Symbol#

Ceridian HCM Holding rerbanded as Dayforce, Inc on 1st Feb 2024, changing its symbol from CDAY to DAY.
Full History can be retrieved by specifying a single symbol and selecting the SYMBOL_DATE to when it is active.
For example DAY after Feb 2024.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
result = otp.run(data,
                 start=otp.dt(2024, 1, 1),
                 end=otp.dt(2024, 4, 1),
                 timezone='America/New_York',
                 symbols='DAY',
                 symbol_date=otp.dt(2024, 4, 1))
result
Time EXCHANGE OPEN HIGH LOW CLOSE VOLUME VWAP PRICE_OPENING_AUCTION VOLUME_OPENING_AUCTION PRICE_CLOSING_AUCTION VOLUME_CLOSING_AUCTION VOLUME_MAIN_SESSION VOLUME_PRE_MARKET VOLUME_POST_MARKET VOLUME_ODD_LOT VOLUME_ROUND_LOT VOLUME_OFF_EXCHANGE OMDSEQ
0 2024-01-02 20:15:00 A 65.80 66.160 65.740 66.040 1963 65.988380 NaN 0 NaN 0 1963 0 0 827 1136 0 22931
1 2024-01-02 20:15:00 B 65.74 66.130 65.740 66.120 3272 66.040385 NaN 0 NaN 0 3272 0 0 2440 832 0 22932
2 2024-01-02 20:15:00 C 66.18 66.180 65.630 66.120 3161 65.969379 NaN 0 NaN 0 3160 0 1 1695 1466 0 22933
3 2024-01-02 20:15:00 D 66.10 66.800 65.250 66.020 366173 65.943110 NaN 0 NaN 0 313057 97 53019 119016 247157 366173 22934
4 2024-01-02 20:15:00 H 66.69 66.690 65.710 66.080 4597 66.119785 NaN 0 NaN 0 4597 0 0 3156 1441 0 22935
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1052 2024-03-28 20:15:00 V 66.15 66.375 65.695 66.235 78488 66.125359 NaN 0 NaN 0 78488 0 0 11964 66524 0 32708
1053 2024-03-28 20:15:00 X 66.07 66.165 65.710 66.165 992 65.991250 NaN 0 NaN 0 992 0 0 592 400 0 32709
1054 2024-03-28 20:15:00 Y 66.10 66.330 66.010 66.330 2127 66.174067 NaN 0 NaN 0 2127 0 0 701 1426 0 32710
1055 2024-03-28 20:15:00 Z 66.31 66.375 65.690 66.360 42920 66.149626 NaN 0 NaN 0 42920 0 0 20470 22450 0 32711
1056 2024-03-28 20:15:00 66.45 66.450 65.650 66.210 1617713 66.163421 NaN 6550 NaN 491722 1028853 107 90481 222184 1395529 570844 32712

1057 rows × 19 columns

Data Retrieval with Old Symbol#

Ceridian HCM Holding rebanded as Dayforce, Inc on 1st Feb 2024, changing its symbol from CDAY to DAY.
Full History can be retrieved by specifying a single symbol and selecting the SYMBOL_DATE to when it is active.
For example CDAY before Feb 2024.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
result = otp.run(data,
                 start=otp.dt(2024, 1, 1),
                 end=otp.dt(2024, 4, 1),
                 timezone='America/New_York',
                 symbols='CDAY',
                 symbol_date=otp.dt(2024, 1, 1))
result
Time EXCHANGE OPEN HIGH LOW CLOSE VOLUME VWAP PRICE_OPENING_AUCTION VOLUME_OPENING_AUCTION PRICE_CLOSING_AUCTION VOLUME_CLOSING_AUCTION VOLUME_MAIN_SESSION VOLUME_PRE_MARKET VOLUME_POST_MARKET VOLUME_ODD_LOT VOLUME_ROUND_LOT VOLUME_OFF_EXCHANGE OMDSEQ
0 2024-01-02 20:15:00 A 65.80 66.160 65.740 66.040 1963 65.988380 NaN 0 NaN 0 1963 0 0 827 1136 0 22931
1 2024-01-02 20:15:00 B 65.74 66.130 65.740 66.120 3272 66.040385 NaN 0 NaN 0 3272 0 0 2440 832 0 22932
2 2024-01-02 20:15:00 C 66.18 66.180 65.630 66.120 3161 65.969379 NaN 0 NaN 0 3160 0 1 1695 1466 0 22933
3 2024-01-02 20:15:00 D 66.10 66.800 65.250 66.020 366173 65.943110 NaN 0 NaN 0 313057 97 53019 119016 247157 366173 22934
4 2024-01-02 20:15:00 H 66.69 66.690 65.710 66.080 4597 66.119785 NaN 0 NaN 0 4597 0 0 3156 1441 0 22935
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1052 2024-03-28 20:15:00 V 66.15 66.375 65.695 66.235 78488 66.125359 NaN 0 NaN 0 78488 0 0 11964 66524 0 32708
1053 2024-03-28 20:15:00 X 66.07 66.165 65.710 66.165 992 65.991250 NaN 0 NaN 0 992 0 0 592 400 0 32709
1054 2024-03-28 20:15:00 Y 66.10 66.330 66.010 66.330 2127 66.174067 NaN 0 NaN 0 2127 0 0 701 1426 0 32710
1055 2024-03-28 20:15:00 Z 66.31 66.375 65.690 66.360 42920 66.149626 NaN 0 NaN 0 42920 0 0 20470 22450 0 32711
1056 2024-03-28 20:15:00 66.45 66.450 65.650 66.210 1617713 66.163421 NaN 6550 NaN 491722 1028853 107 90481 222184 1395529 570844 32712

1057 rows × 19 columns

Data Retrieval across Instruments with the same Reallocated Symbol#

Symbols get reallocated across a relatively small time period.
SPCX has represented two instruments in 2026:

  • January 2026 to mid June 2026 - The SPAC and New Issue ETF

  • Mid June 2026 onwards - SpaceX

A simple retrieval by symbol will combine the history across both.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')
data = data.limit(1000)
result = otp.run(data,
                 start=otp.dt(2026, 1, 1),
                 end=otp.dt(2026, 7, 1),
                 timezone='UTC',
                 symbols='SPCX')
result
Time EXCHANGE OPEN HIGH LOW CLOSE VOLUME TRADE_COUNT TRADE_VALUE VWAP ... VOLUME_OPENING_AUCTION PRICE_CLOSING_AUCTION VOLUME_CLOSING_AUCTION VOLUME_MAIN_SESSION VOLUME_PRE_MARKET VOLUME_POST_MARKET VOLUME_ODD_LOT VOLUME_ROUND_LOT VOLUME_OFF_EXCHANGE OMDSEQ
0 2026-01-01 01:15:00 21.3200 21.7950 21.3200 21.7851 1205 44 2.615400e+04 21.640144 ... 156 NaN 8 1027 0 14 649 556 692 122452
1 2026-01-03 01:15:00 21.8099 21.8099 21.8099 21.8099 213 28 4.634000e+03 NaN ... 14 NaN 10 180 0 9 213 0 131 122559
2 2026-01-06 01:15:00 21.7200 21.8258 21.7200 21.8258 718 54 1.562400e+04 21.729461 ... 127 NaN 10 574 0 7 477 241 395 124920
3 2026-01-07 01:15:00 21.7950 21.8250 21.7701 21.8250 861 49 1.876400e+04 21.778400 ... 32 NaN 6 801 0 22 561 300 692 122812
4 2026-01-08 01:15:00 21.7500 21.8300 21.7500 21.8300 499 50 1.089500e+04 21.750000 ... 24 NaN 21 430 0 24 399 100 45 121830
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
71 2026-06-24 00:15:00 151.0600 165.5000 147.1100 156.1100 155848109 2889483 2.444034e+10 157.352254 ... 1944527 NaN 3198958 130153793 17942809 2608021 37707464 118140644 71432885 127774
72 2026-06-25 00:15:00 154.1954 159.8600 150.7200 154.5400 76101541 1544812 1.186473e+10 156.038204 ... 824393 NaN 2581871 64231328 4681603 3782345 21050283 55051258 34058243 129158
73 2026-06-26 00:15:00 156.6250 160.6500 150.0000 153.0000 62220685 1311456 9.543992e+09 152.972776 ... 445943 NaN 2978240 51961442 4449410 2385649 16181874 46038811 27696604 130020
74 2026-06-27 00:15:00 150.6200 158.4000 148.5100 153.2300 126932861 1212535 1.946794e+10 153.404677 ... 558362 NaN 39557281 66015705 4144214 16657298 16185487 110747373 49204705 129520
75 2026-06-30 00:15:00 157.3550 166.1700 151.7356 164.1900 81443101 1387104 1.298796e+10 159.667292 ... 1076511 NaN 8634633 66776217 2669334 2286406 19146920 62296180 33652558 131436

76 rows × 21 columns

Data Retrieval across Instruments with the same Reallocated Symbol, specifying the ETF#

Symbols get reallocated across a relatively small time period.
SPCX has represented two instruments in 2026:

  • January 2026 to mid June 2026 - The SPAC and New Issue ETF

  • Mid June 2026 onwards - SpaceX

Specifying the symbol date as January, when the ETF was active, ensures just the ETF history is retrieved.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')
data = data.limit(1000)
result = otp.run(data,
                 start=otp.dt(2026, 1, 1),
                 end=otp.dt(2026, 7, 1),
                 timezone='UTC',
                 symbols='SPCX',
                 symbol_date=otp.dt(2026, 1, 1))
result
Time EXCHANGE OPEN HIGH LOW CLOSE VOLUME TRADE_COUNT TRADE_VALUE VWAP ... VOLUME_OPENING_AUCTION PRICE_CLOSING_AUCTION VOLUME_CLOSING_AUCTION VOLUME_MAIN_SESSION VOLUME_PRE_MARKET VOLUME_POST_MARKET VOLUME_ODD_LOT VOLUME_ROUND_LOT VOLUME_OFF_EXCHANGE OMDSEQ
0 2026-01-01 01:15:00 21.3200 21.7950 21.3200 21.7851 1205 44 26154.0 21.640144 ... 156 NaN 8 1027 0 14 649 556 692 122452
1 2026-01-03 01:15:00 21.8099 21.8099 21.8099 21.8099 213 28 4634.0 NaN ... 14 NaN 10 180 0 9 213 0 131 122559
2 2026-01-06 01:15:00 21.7200 21.8258 21.7200 21.8258 718 54 15624.0 21.729461 ... 127 NaN 10 574 0 7 477 241 395 124920
3 2026-01-07 01:15:00 21.7950 21.8250 21.7701 21.8250 861 49 18764.0 21.778400 ... 32 NaN 6 801 0 22 561 300 692 122812
4 2026-01-08 01:15:00 21.7500 21.8300 21.7500 21.8300 499 50 10895.0 21.750000 ... 24 NaN 21 430 0 24 399 100 45 121830
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
118 2026-06-24 00:15:00 21.7600 22.3500 21.7600 21.9750 2755 78 60746.0 22.054597 ... 748 NaN 0 1931 70 6 607 2148 1101 19932
119 2026-06-25 00:15:00 22.2250 22.2250 21.8900 22.1265 3078 78 67808.0 21.988986 ... 79 NaN 0 2768 203 27 1016 2062 1955 20130
120 2026-06-26 00:15:00 21.6850 21.9900 21.6850 21.9878 3581 86 78669.0 21.961619 ... 5 NaN 4 3530 36 5 623 2958 1483 20302
121 2026-06-27 00:15:00 21.8000 22.1850 21.8000 22.1850 2016 59 44260.0 21.939553 ... 5 NaN 0 2004 1 6 473 1543 852 20081
122 2026-06-30 00:15:00 21.8800 22.1800 21.8800 22.1602 2429 58 53646.0 22.081632 ... 474 NaN 0 1899 0 55 370 2059 1671 20372

123 rows × 21 columns

Data Retrieval across Instruments with the same Reallocated Symbol, specifying the Latest Instrument#

Symbols get reallocated across a relatively small time period.
SPCX has represented two instruments in 2026:

  • January 2026 to mid June 2026 - The SPAC and New Issue ETF

  • Mid June 2026 onwards - SpaceX

Specifying the symbol date as July, when SpaceX is active, ensures just the SpaceX history is retrieved.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')
data = data.limit(1000)
result = otp.run(data,
                 start=otp.dt(2026, 1, 1),
                 end=otp.dt(2026, 7, 1),
                 timezone='UTC',
                 symbols='SPCX',
                 symbol_date=otp.dt(2026, 7, 1))
result
/home/kkashirin/onetick-py/src/onetick/py/run.py:1007: UserWarning: Symbol error: [987] Reverse symbology mapping is missing for symbol 747922 from symbology OID to symbology TDEQ for interval [20260101000000,20260612040000].
  warnings.warn(f"Symbol error: [{err_code}] {err_msg}")
Time EXCHANGE OPEN HIGH LOW CLOSE VOLUME TRADE_COUNT TRADE_VALUE VWAP ... VOLUME_OPENING_AUCTION PRICE_CLOSING_AUCTION VOLUME_CLOSING_AUCTION VOLUME_MAIN_SESSION VOLUME_PRE_MARKET VOLUME_POST_MARKET VOLUME_ODD_LOT VOLUME_ROUND_LOT VOLUME_OFF_EXCHANGE OMDSEQ
0 2026-06-13 00:15:00 150.0000 176.5200 135.0000 160.95 522132290 8568758 8.531658e+10 162.910927 ... 58206084 NaN 7846944 438094699 0 17984563 99709206 422423084 143233820 124790
1 2026-06-16 00:15:00 171.7400 193.0000 168.3500 192.50 256226632 4834739 4.596025e+10 179.195857 ... 5840110 NaN 3990567 213827171 17695304 14873479 57006811 199219820 106763446 127750
2 2026-06-17 00:15:00 200.5100 225.6400 195.1300 201.80 322149250 6503350 6.760143e+10 210.381888 ... 4745058 NaN 4979927 269839948 31834855 10749462 77010660 245138589 140622462 126301
3 2026-06-18 00:15:00 209.8400 213.7999 187.0100 191.82 201724493 3906416 3.983098e+10 196.971073 ... 1998448 NaN 3687906 177360563 13347906 5329669 44544556 157179936 89639350 128825
4 2026-06-19 00:15:00 188.3900 190.0000 172.1100 185.00 272126781 4054030 4.940615e+10 181.396476 ... 1194698 NaN 52281992 188108040 13310422 17231628 50071101 222055679 102890995 126704
5 2026-06-23 00:15:00 176.0420 176.7500 154.0000 154.60 169183799 3643580 2.784597e+10 164.212722 ... 1160439 NaN 3968720 149506735 6997311 7550593 38874846 130308953 81804912 128019
6 2026-06-24 00:15:00 151.0600 165.5000 147.1100 156.11 155848109 2889483 2.444034e+10 157.352254 ... 1944527 NaN 3198958 130153793 17942809 2608021 37707464 118140644 71432885 127774
7 2026-06-25 00:15:00 154.1954 159.8600 150.7200 154.54 76101541 1544812 1.186473e+10 156.038204 ... 824393 NaN 2581871 64231328 4681603 3782345 21050283 55051258 34058243 129158
8 2026-06-26 00:15:00 156.6250 160.6500 150.0000 153.00 62220685 1311456 9.543992e+09 152.972776 ... 445943 NaN 2978240 51961442 4449410 2385649 16181874 46038811 27696604 130020
9 2026-06-27 00:15:00 150.6200 158.4000 148.5100 153.23 126932861 1212535 1.946794e+10 153.404677 ... 558362 NaN 39557281 66015705 4144214 16657298 16185487 110747373 49204705 129520
10 2026-06-30 00:15:00 157.3550 166.1700 151.7356 164.19 81443101 1387104 1.298796e+10 159.667292 ... 1076511 NaN 8634633 66776217 2669334 2286406 19146920 62296180 33652558 131436

11 rows × 21 columns

Data Retrieval with Symbol LIKE#

Returns daily OHLCV data for US equities matching a symbol prefix pattern.
otp.Symbols(pattern=...) selects every symbol whose name matches the pattern, the equivalent of SQL’s LIKE.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')

# Merge every symbol starting with AA into a single stream.
data = otp.merge([data],
                 symbols=otp.Symbols('US_COMP_SAMPLE_DAILY', pattern='AA%', for_tick_type='DAY'),
                 identify_input_ts=True)
data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 2, 1),
                 timezone='America/New_York')
result
Time CLOSE EXCHANGE HIGH LOW OMDSEQ OPEN PRICE_CLOSING_AUCTION PRICE_OPENING_AUCTION VOLUME ... VOLUME_MAIN_SESSION VOLUME_ODD_LOT VOLUME_OFF_EXCHANGE VOLUME_OPENING_AUCTION VOLUME_POST_MARKET VOLUME_PRE_MARKET VOLUME_ROUND_LOT VWAP SYMBOL_NAME TICK_TYPE
0 2024-01-03 20:15:00 31.4900 32.2700 31.0450 36 32.1200 NaN NaN 8245658 ... 7043801 835482 4077035 70320 726723 39655 7410176 31.686287 AA DAY
1 2024-01-03 20:15:00 25.0250 25.0800 24.9835 44 25.0200 NaN NaN 7543 ... 7218 934 1339 325 0 0 6609 25.040404 AAA DAY
2 2024-01-03 20:15:00 20.2150 20.2900 20.1140 61 20.1750 NaN NaN 2110058 ... 2094872 16180 965993 11921 39 1946 2093878 20.170415 AAAU DAY
3 2024-01-03 20:15:00 1.0400 1.2342 1.0400 68 1.0900 NaN NaN 2567 ... 1937 1355 1348 164 119 199 1212 1.089405 AACG DAY
4 2024-01-03 20:15:00 10.8300 10.8400 10.8300 76 10.8400 NaN NaN 13059 ... 13038 154 1 0 0 1 12905 10.839609 AACI DAY
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
562 2024-01-30 20:15:00 25.3005 26.1410 25.2724 316 26.1410 NaN NaN 12303 ... 12100 552 6074 0 1 202 11751 25.881866 AAPX DAY
563 2024-01-30 20:15:00 25.9373 25.9373 25.9373 320 25.9373 NaN NaN 55 ... 55 55 54 0 0 0 0 NaN AAPY DAY
564 2024-01-30 20:15:00 23.2400 23.7800 23.1800 338 23.6500 NaN NaN 329629 ... 243308 57648 137565 776 31180 2 271981 23.418681 AAT DAY
565 2024-01-30 20:15:00 0.1351 0.1354 0.1320 352 0.1351 NaN NaN 238539 ... 230926 2657 99964 4880 2090 643 235882 0.133478 AAU DAY
566 2024-01-30 20:15:00 63.4700 63.5200 63.2200 369 63.3600 NaN NaN 1290331 ... 1259942 45019 708163 5314 0 64 1245312 63.362919 AAXJ DAY

567 rows × 21 columns

Data Retrieval with Symbol ILIKE (Case Insensitive)#

Returns daily OHLCV data for US equities using case-insensitive symbol pattern matching, the equivalent of SQL’s ILIKE.
The .str.ilike accessor matches a value against a SQL-style pattern while ignoring case, so the lowercase pattern aa% still matches the upper case symbol names stored in the database.
.str.like performs the same match case-sensitively, and would return nothing for this pattern.

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data.where(data['EXCHANGE'] == '')

# Select the symbols case-insensitively: 'aa%' matches AA, AAA, AAAU and so on.
symbols = otp.Symbols('US_COMP_SAMPLE_DAILY', for_tick_type='DAY')
symbols = symbols.where(symbols['SYMBOL_NAME'].str.ilike('aa%'))

# Merge every matching symbol into a single stream.
data = otp.merge([data], symbols=symbols, identify_input_ts=True)
data = data.limit(1000)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3),
                 end=otp.dt(2024, 2, 1),
                 timezone='America/New_York')
result
Time CLOSE EXCHANGE HIGH LOW OMDSEQ OPEN PRICE_CLOSING_AUCTION PRICE_OPENING_AUCTION VOLUME ... VOLUME_MAIN_SESSION VOLUME_ODD_LOT VOLUME_OFF_EXCHANGE VOLUME_OPENING_AUCTION VOLUME_POST_MARKET VOLUME_PRE_MARKET VOLUME_ROUND_LOT VWAP SYMBOL_NAME TICK_TYPE
0 2024-01-03 20:15:00 31.4900 32.2700 31.0450 36 32.1200 NaN NaN 8245658 ... 7043801 835482 4077035 70320 726723 39655 7410176 31.686287 AA DAY
1 2024-01-03 20:15:00 25.0250 25.0800 24.9835 44 25.0200 NaN NaN 7543 ... 7218 934 1339 325 0 0 6609 25.040404 AAA DAY
2 2024-01-03 20:15:00 20.2150 20.2900 20.1140 61 20.1750 NaN NaN 2110058 ... 2094872 16180 965993 11921 39 1946 2093878 20.170415 AAAU DAY
3 2024-01-03 20:15:00 1.0400 1.2342 1.0400 68 1.0900 NaN NaN 2567 ... 1937 1355 1348 164 119 199 1212 1.089405 AACG DAY
4 2024-01-03 20:15:00 10.8300 10.8400 10.8300 76 10.8400 NaN NaN 13059 ... 13038 154 1 0 0 1 12905 10.839609 AACI DAY
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
562 2024-01-30 20:15:00 25.3005 26.1410 25.2724 316 26.1410 NaN NaN 12303 ... 12100 552 6074 0 1 202 11751 25.881866 AAPX DAY
563 2024-01-30 20:15:00 25.9373 25.9373 25.9373 320 25.9373 NaN NaN 55 ... 55 55 54 0 0 0 0 NaN AAPY DAY
564 2024-01-30 20:15:00 23.2400 23.7800 23.1800 338 23.6500 NaN NaN 329629 ... 243308 57648 137565 776 31180 2 271981 23.418681 AAT DAY
565 2024-01-30 20:15:00 0.1351 0.1354 0.1320 352 0.1351 NaN NaN 238539 ... 230926 2657 99964 4880 2090 643 235882 0.133478 AAU DAY
566 2024-01-30 20:15:00 63.4700 63.5200 63.2200 369 63.3600 NaN NaN 1290331 ... 1259942 45019 708163 5314 0 64 1245312 63.362919 AAXJ DAY

567 rows × 21 columns