Daily OHLCV (with closing prices)#

We can retrieve daily OHLCV data for specific tickers using various symbologies, by querying for the DAY Table.

For consolidated datasets such as for the US, each exchange’s data and a composite are represented.

The _DAILY databases such as the sample US_COMP_SAMPLE_DAILY are optimized for querying long time periods, while the Tick and Bar databases are optimized for querying an intraday time range.

Without Symbology:

import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
df = otp.run(data,
             start=otp.dt(2024, 1, 1),
             end=otp.dt(2024, 4, 1),
             timezone='America/New_York',
             symbols='CSCO')

With Symbology:

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
df = otp.run(data,
             start=otp.dt(2024, 1, 1),
             end=otp.dt(2024, 4, 1),
             timezone='America/New_York',
             symbols='BSYM::::CSCO US Equity',
             symbol_date=otp.dt(2024, 1, 3))
df.head(5)
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 50.460 50.83 50.27 50.55 62688 50.420217 NaN 0 NaN 0 61348 1340 0 4804 57884 0 30021
1 2024-01-02 20:15:00 B 50.420 50.83 50.29 50.53 40416 50.508402 NaN 0 NaN 0 40416 0 0 8160 32256 0 30022
2 2024-01-02 20:15:00 C 50.505 50.80 50.26 50.52 82490 50.477827 NaN 0 NaN 0 82486 4 0 17930 64560 0 30023
3 2024-01-02 20:15:00 D 50.470 50.86 50.25 50.53 7404940 50.551240 NaN 0 NaN 0 6164089 4769 1236082 478272 6926668 7404940 30024
4 2024-01-02 20:15:00 H 50.490 50.83 50.26 50.53 136771 50.464547 NaN 0 NaN 0 136771 0 0 23687 113084 0 30025

For US equities, USPRIM stands for the primary exchange, empty string for the composite, and N for the New York Stock Exchange.

Most of the time you’ll be looking for composite, which you can specify through a filter:

data = otp.DataSource(db='US_COMP_SAMPLE_DAILY', tick_type='DAY')
data = data[['CLOSE', 'VOLUME', 'EXCHANGE']]
data = data.where(data['EXCHANGE'] == '')
otp.run(data,
        start=otp.dt(2024, 1, 1),
        end=otp.dt(2024, 4, 1),
        timezone='America/New_York',
        symbols='BSYM::::CSCO US Equity',
        symbol_date=otp.dt(2024, 1, 3))
Time CLOSE VOLUME EXCHANGE
0 2024-01-02 20:15:00 50.51 20242939
1 2024-01-03 20:15:00 50.51 20303875
2 2024-01-04 20:15:00 50.08 18134121
3 2024-01-05 20:15:00 50.09 13989287
4 2024-01-08 20:15:00 50.46 18070293
... ... ... ... ...
56 2024-03-22 20:15:00 49.78 15022861
57 2024-03-25 20:15:00 49.68 16191164
58 2024-03-26 20:15:00 49.55 13842923
59 2024-03-27 20:15:00 49.77 17230958
60 2024-03-28 20:15:00 49.91 18139735

61 rows × 4 columns