Databases, symbols, and tick types#

The data is organized by database (e.g., NYSE_TAQ or CME), symbol (e.g., AAPL), and tick type (e.g., TRD or QTE). A data source must specify these (possibly providing multiple values for each) to retrieve data.

A tick type can be set explicitly via the tick_type parameter of a data source.

The database name can be specified via the db parameter or as part of a symbol using the :: separator. For example

>>> trades = otp.DataSource(db='NYSE_TAQ', symbol='AAPL', tick_type='TRD', date=otp.dt(2023, 3, 1))
>>> volume = trades.agg({'VOLUME': otp.agg.sum(trades['SIZE'])})
>>> otp.run(volume)
        Time    VOLUME
0 2023-03-02  62351689

is equivalent to

>>> trades = otp.DataSource(symbol='NYSE_TAQ::AAPL', tick_type='TRD', date=otp.dt(2023, 3, 1))

The same technique applies to the database and tick type:

>>> trades = otp.DataSource(symbol='AAPL', tick_type='NYSE_TAQ::TRD', date=otp.dt(2023, 3, 1))

Note that symbol name and tick type can be accessed as pseudo-fields in OneTick. Those pseudo-fields are always present and you can access them like that:

>>> trades = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', date=otp.dt(2023, 3, 1))
>>> trades['SYMBOL_NAME'] = trades['_SYMBOL_NAME']
>>> trades['TICK_TYPE'] = trades['_TICK_TYPE']
>>> trades = trades.first()
>>> trades = trades[['PRICE', 'SIZE', 'SYMBOL_NAME', 'TICK_TYPE']]
>>> otp.run(trades, symbols='AAPL')
                           Time   PRICE  SIZE SYMBOL_NAME TICK_TYPE
0 2023-03-01 00:00:00.005165425  147.66     4        AAPL       TRD

Parameter identify_input_ts in otp.DataSource and otp.merge can also be used to add them as new columns.

Symbols: bound and unbound#

There are two ways of setting symbols: bound and unbound.

Bound symbols are specified when defining a source or with otp.merge. Unbound symbols are set when the query is executed and apply to all sources that do not specify bound symbols. Here is an simple example of an unbound symbol:

>>> trades = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', date=otp.dt(2023, 3, 1))
>>> volume = trades.agg({'VOLUME': otp.agg.sum(trades['SIZE'])})
>>> otp.run(volume, symbols=['AAPL'])  # unbound symbol is set here
        Time    VOLUME
0 2023-03-02  62351689

There is no difference between bound and unbound when we talk about a single symbol. To appreciate the difference, let’s look at two symbols.

>>> trades = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', date=otp.dt(2023, 3, 1))
>>> volume = trades.agg({'VOLUME': otp.agg.sum(trades['SIZE'])})
>>> dict(sorted(otp.run(volume, symbols=['AAPL', 'MSFT']).items()))
 {'AAPL':         Time    VOLUME
  0 2023-03-02  62351689,
  'MSFT':         Time    VOLUME
  0 2023-03-02  31619403}

The results for each unbound symbol are processed separately and returned in a separate pandas DataFrame. The result of the run method above is a dict with symbols as keys and pandas DataFrames as values.

Contrast this with bound symbols where the ticks are merged into a single flow:

>>> trades = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', date=otp.dt(2023, 3, 1), symbols=['AAPL', 'MSFT'])
>>> volume = trades.agg({'VOLUME': otp.agg.sum(trades['SIZE'])})
>>> otp.run(volume)
        Time    VOLUME
0 2023-03-02  93971092

Specifying bound symbols on a source is just a shorthand for the otp.merge method added right after the source definition.

>>> trades = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', date=otp.dt(2023, 3, 1))
>>> cross_symbol_trades = otp.merge([trades], symbols=['AAPL', 'MSFT'])
>>> volume = cross_symbol_trades.agg({'VOLUME': otp.agg.sum('SIZE')})
>>> otp.run(volume)
        Time    VOLUME
0 2023-03-02  93971092

Concurrency#

Unbound symbols can be processed in parallel by specifying the concurrency parameter of otp.run. You have to explicitly use otp.run to execute the query if you want to specify concurrency.

otp.run(volume, symbols=['AAPL', 'MSFT', 'AMZN', 'META'], concurrency=8)

For bound symbols, all calculations passed into otp.merge (trades in example above) run in parallel. Calculations can be made faster by computing as much as possible per symbol before merging them. The following example has the same result as the previous one but it finds the total volume faster as it calculates the volume for every symbol independently which can be done in parallel and then adds up the total.

>>> trades = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', date=otp.dt(2023, 3, 1))
>>> volume = trades.agg({'VOLUME': otp.agg.sum('SIZE')})
>>> cross_symbol_volumes = otp.merge([volume], symbols=['AAPL', 'MSFT'], presort=True, identify_input_ts=True)
>>> otp.run(cross_symbol_volumes, concurrency=8)
        Time    VOLUME SYMBOL_NAME TICK_TYPE
0 2023-03-02  62351689        AAPL       TRD
1 2023-03-02  31619403        MSFT       TRD

>>> total_volume = cross_symbol_volumes.agg({'TOTAL_VOLUME': otp.agg.sum('VOLUME')})
>>> otp.run(total_volume, concurrency=8)
        Time  TOTAL_VOLUME
0 2023-03-02      93971092

Note

All sources passed into merge are considered bound symbol sources.

Specifying symbols dynamically#

In many cases it is necessary to select symbols from a databases according to some logic. The otp.Symbols source makes this easy.

>>> symbols = otp.Symbols(db='NYSE_TAQ', date=otp.dt(2023, 3, 1), pattern='AAA%')
>>> otp.run(symbols)
        Time SYMBOL_NAME
0 2023-03-01         AAA
1 2023-03-01        AAAU

The result of otp.Symbols can be used as bound/unbound symbols.

>>> data = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', start=otp.dt(2023, 3, 1), end=otp.dt(2023, 3, 2))
>>> data = data.first(3)
>>> data = data[['PRICE','SIZE']]
>>> dict(sorted(otp.run(data, symbols=otp.Symbols(db='NYSE_TAQ', pattern='AAA%')).items()))
{'AAA':                      Time    PRICE  SIZE
 0 2023-03-01 00:00:00.441  24.56    10
 1 2023-03-01 00:00:00.441  24.56    10
 2 2023-03-01 00:00:00.735  24.53     1,
 'AAAU':                     Time  PRICE  SIZE
 0 2023-03-01 00:00:00.017  18.17    40
 1 2023-03-01 00:00:00.071  18.17   100
 2 2023-03-01 00:00:00.802  18.22   549}

Note that the interval of the main query is implicitly used in otp.Symbols(db='NYSE_TAQ', pattern='AAA%').

The symbols parameter can be any type of calculation that contains the SYMBOL_NAME field in the resulting ticks. Every tick provides a separate symbol name in the SYMBOL_NAME field while the other fields are passed as ‘symbol parameters’.

Symbol parameters#

Symbol parameters are any fields other than SYMBOL_NAME that are constant for an instrument. We illustrate this below. First, consider a query that finds 2 most traded symbols among symbols starting with the letter ‘A’. We’ll later use the output of this query to specify symbols and symbol params.

>>> trd = otp.DataSource(db='NYSE_TAQ', tick_type='TRD', start=otp.dt(2023, 3, 1), end=otp.dt(2023, 3, 2))
>>> trd = trd.agg({'VOLUME': otp.agg.sum('SIZE')})
>>> count = otp.merge([trd], symbols=otp.Symbols(db='NYSE_TAQ', pattern='A%'), presort=True, identify_input_ts=True)
>>> most_traded = count.high('VOLUME', n=2)
>>> otp.run(most_traded)
         Time    VOLUME  SYMBOL_NAME  TICK_TYPE
 0 2023-03-02  62351689         AAPL        TRD
 1 2023-03-02  56099688         AMZN        TRD

The output of most_traded provides symbols in the SYMBOL_NAME column while the other columns (VOLUME and TICK_TYPE) provide symbol parameters. The following code retrieves the value of VOLUME for use in tick-by-tick analytics.

>>> trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')
>>> trd = trd.agg({'VOLUME': otp.agg.sum('SIZE')})
>>> count = otp.merge([trd], symbols=otp.Symbols('NYSE_TAQ', pattern='A%'), identify_input_ts=True)
>>> most_traded = count.high('VOLUME', n=2)
>>>
>>> data = otp.DataSource('NYSE_TAQ', tick_type='TRD')
>>> data = data.first(3)
>>> data = data[['PRICE','SIZE']]
>>> data['VOLUME'] = data.Symbol['VOLUME', int]
>>> dict(sorted(otp.run(data, symbols=most_traded, start=otp.dt(2023, 3, 1), end=otp.dt(2023, 3, 2)).items()))
{'AAPL':                            Time   PRICE  SIZE    VOLUME
 0 2023-03-01 00:00:00.005165425  147.66     4  62351689
 1 2023-03-01 00:00:00.005173198  147.66     1  62351689
 2 2023-03-01 00:00:00.014521032  147.66     4  62351689,
 'AMZN':                            Time  PRICE  SIZE    VOLUME
 0 2023-03-01 00:00:00.062175376  94.31    10  56099688
 1 2023-03-01 00:00:00.063719476  94.31     3  56099688
 2 2023-03-01 00:00:00.064597172  94.31     4  56099688}

Equivalently, symbol parameters can be accessed by wrapping a function around the query as illustrated below.

>>> trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')
>>> trd = trd.agg({'VOLUME': otp.agg.sum('SIZE')})
>>> count = otp.merge([trd], symbols=otp.Symbols('NYSE_TAQ', pattern='A%'), identify_input_ts=True)
>>> most_traded = count.high('VOLUME', n=2)
>>>
>>> def query(sym):
...     data = otp.DataSource('NYSE_TAQ', tick_type='TRD')
...     data = data.first(3)
...     data = data[['PRICE','SIZE']]
...     data['VOLUME'] = sym['VOLUME']
...     return data
>>>
>>> dict(sorted(otp.run(query, symbols=most_traded, start=otp.dt(2023, 3, 1), end=otp.dt(2023, 3, 2)).items()))
{'AAPL':                            Time   PRICE  SIZE    VOLUME
 0 2023-03-01 00:00:00.005165425  147.66     4  62351689
 1 2023-03-01 00:00:00.005173198  147.66     1  62351689
 2 2023-03-01 00:00:00.014521032  147.66     4  62351689,
 'AMZN':                            Time  PRICE  SIZE    VOLUME
 0 2023-03-01 00:00:00.062175376  94.31    10  56099688
 1 2023-03-01 00:00:00.063719476  94.31     3  56099688
 2 2023-03-01 00:00:00.064597172  94.31     4  56099688}

Time interval per symbol#

It is allowed to specify query interval per symbol using special fields _PARAM_START_TIME_NANOS and _PARAM_START_TIME_NANOS

>>> custom_symbols = otp.Ticks(SYMBOL_NAME=['AAPL', 'MSFT'],
...                            _PARAM_START_TIME_NANOS=[otp.dt(2023, 3, 2, 10, 30), otp.dt(2023, 3, 2, 11)],
...                            _PARAM_END_TIME_NANOS=[otp.dt(2023, 3, 2, 11), otp.dt(2023, 3, 2, 11, 30)])
>>> data = otp.DataSource(db='NYSE_TAQ', tick_type='TRD')
>>> data = data.agg({'VOLUME': otp.agg.sum('SIZE')})
>>> data['SYMBOL'] = data.Symbol.name
>>> dict(sorted(otp.run(data, start=otp.dt(2023, 3, 2), end=otp.dt(2023, 3, 3), symbols=custom_symbols).items()))
 {'AAPL':                  Time   VOLUME SYMBOL
 0 2023-03-02 11:00:00  3539370   AAPL,
 'MSFT':                  Time   VOLUME SYMBOL
 0 2023-03-02 11:30:00  1679716   MSFT}

Note that per symbol intervals should be inside the query interval.

Associated symbols#

Associated symbols is a technique when unbound symbols are used to define bound symbols or symbols in related queries. It is expressed using the otp.eval and a symbol parameter. More details can be found in the API doc for the otp.eval