Daily OHLCV (with closing prices)#
This is part of the OQD offering. Available in OneTick Cloud. Can be configured on prem.
We can retrieve daily OHLCV data for specific tickers using various symbologies. It’s best to use the GMT time zone when working with OQD data.
import onetick.py as otp
src = otp.oqd.sources.OHLCV()
otp.run(src, symbols=['BTKR::::AAPL US'], start=otp.dt(2022, 10, 1), end=otp.dt(2022, 10, 7),
symbol_date=otp.dt(2022, 10, 1), timezone='GMT')
| Time | OID | EXCH | CURRENCY | OPEN | HIGH | LOW | CLOSE | VOLUME | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2022-10-03 | 9706 | USCOMP | USD | 138.210 | 143.07 | 137.685 | 142.45 | 114311700.0 |
| 1 | 2022-10-03 | 9706 | USPRIM | USD | 138.250 | 143.07 | 137.690 | 142.45 | 27094230.0 |
| 2 | 2022-10-03 | 9706 | USXNMS | USD | 138.250 | 143.07 | 137.690 | 142.45 | 27094230.0 |
| 3 | 2022-10-04 | 9706 | USCOMP | USD | 145.030 | 146.22 | 144.260 | 146.10 | 87830060.0 |
| 4 | 2022-10-04 | 9706 | USPRIM | USD | 145.000 | 146.22 | 144.270 | 146.10 | 22959160.0 |
| 5 | 2022-10-04 | 9706 | USXNMS | USD | 145.000 | 146.22 | 144.270 | 146.10 | 22959160.0 |
| 6 | 2022-10-05 | 9706 | USCOMP | USD | 144.075 | 147.38 | 143.010 | 146.40 | 79470970.0 |
| 7 | 2022-10-05 | 9706 | USPRIM | USD | 143.980 | 147.38 | 143.010 | 146.40 | 16841510.0 |
| 8 | 2022-10-05 | 9706 | USXNMS | USD | 143.980 | 147.38 | 143.010 | 146.40 | 16841510.0 |
| 9 | 2022-10-06 | 9706 | USCOMP | USD | 145.810 | 147.54 | 145.220 | 145.43 | 68402170.0 |
| 10 | 2022-10-06 | 9706 | USPRIM | USD | 145.820 | 147.54 | 145.230 | 145.43 | 15827940.0 |
| 11 | 2022-10-06 | 9706 | USXNMS | USD | 145.820 | 147.54 | 145.230 | 145.43 | 15827940.0 |
| 12 | 2022-10-07 | 9706 | USCOMP | USD | 142.540 | 143.10 | 139.445 | 140.09 | 85925560.0 |
| 13 | 2022-10-07 | 9706 | USPRIM | USD | 142.580 | 143.10 | 139.450 | 140.09 | 24384550.0 |
| 14 | 2022-10-07 | 9706 | USXNMS | USD | 142.580 | 143.10 | 139.450 | 140.09 | 24384550.0 |
For US equities, ‘USPRIM’ stands for the primary exchange, ‘USCOMP’ for the composite, and ‘USXNMS’ for US Nasdaq. Most of the time you’ll be looking for composite, which you can specify at the top:
src = otp.oqd.sources.OHLCV(exch='USCOMP')
otp.run(src, symbols=['BTKR::::AAPL US'], start=otp.dt(2022, 10, 1), end=otp.dt(2022, 10, 7),
symbol_date=otp.dt(2022, 10, 1), timezone='GMT')
| Time | OID | EXCH | CURRENCY | OPEN | HIGH | LOW | CLOSE | VOLUME | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2022-10-03 | 9706 | USCOMP | USD | 138.210 | 143.07 | 137.685 | 142.45 | 114311700.0 |
| 1 | 2022-10-04 | 9706 | USCOMP | USD | 145.030 | 146.22 | 144.260 | 146.10 | 87830060.0 |
| 2 | 2022-10-05 | 9706 | USCOMP | USD | 144.075 | 147.38 | 143.010 | 146.40 | 79470970.0 |
| 3 | 2022-10-06 | 9706 | USCOMP | USD | 145.810 | 147.54 | 145.220 | 145.43 | 68402170.0 |
| 4 | 2022-10-07 | 9706 | USCOMP | USD | 142.540 | 143.10 | 139.445 | 140.09 | 85925560.0 |
See examples of other symbologies in Symbologies. Note that unlike regular tick data, OHLCV requires ISINs to have a country + exchange suffix (e.g., ISN::::US0378331005.USCOMP or ISN::::GB0000055888.GBXLON).
The most efficient way of retrieving data for a large number of symbols is via OneTick’s native OID symbology. We can create a map from the databases’s symbology to the OID using the code below or between any two symbologies using the method described in the Symbologies guide.
oid = otp.Symbols('NYSE_TAQ', symbology='OID', show_original_symbols=True, pattern='11%')
oid, _ = oid[oid['SYMBOL_NAME'] != '']
otp.run(oid, start=otp.dt(2023, 5, 15), end=otp.dt(2023, 5, 16), timezone='GMT')
| Time | SYMBOL_NAME | ORIGINAL_SYMBOL_NAME | |
|---|---|---|---|
| 0 | 2023-05-15 | 11233 | NYSE_TAQ::AAME |
| 1 | 2023-05-15 | 11809 | NYSE_TAQ::ADP |
| 2 | 2023-05-15 | 11757 | NYSE_TAQ::ADSK |
| 3 | 2023-05-15 | 11060 | NYSE_TAQ::AIZ |
| 4 | 2023-05-15 | 11120 | NYSE_TAQ::ALOT |
| ... | ... | ... | ... |
| 67 | 2023-05-15 | 114707 | NYSE_TAQ::PRSO |
| 68 | 2023-05-15 | 114852 | NYSE_TAQ::SBFM |
| 69 | 2023-05-15 | 112560 | NYSE_TAQ::TAP |
| 70 | 2023-05-15 | 112559 | NYSE_TAQ::TAPA |
| 71 | 2023-05-15 | 11574 | NYSE_TAQ::VOXX |
72 rows × 3 columns
We can now query OHLCV using OIDs. The code below retrieves OHLCV for all US equities for 5 days.
ohlcv = otp.oqd.sources.OHLCV(exch='USCOMP')
ohlcv['ticker'] = ohlcv.Symbol.ORIGINAL_SYMBOL_NAME
ohlcv = otp.merge([ohlcv], symbols=oid)
otp.run(ohlcv, start=otp.dt(2023, 5, 15), end=otp.dt(2023, 5, 15, 23), timezone='GMT')
/tmp/ipykernel_205/2814181847.py:2: DeprecationWarning: `__getattr__` method is deprecated. Please, use `__getitem__` method instead.
ohlcv['ticker'] = ohlcv.Symbol.ORIGINAL_SYMBOL_NAME
| Time | OID | EXCH | CURRENCY | OPEN | HIGH | LOW | CLOSE | VOLUME | ticker | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2023-05-15 | 11233 | USCOMP | USD | 2.2500 | 2.2500 | 2.2200 | 2.25 | 4700.0 | NYSE_TAQ::AAME |
| 1 | 2023-05-15 | 11809 | USCOMP | USD | 212.2400 | 214.5700 | 212.0000 | 214.39 | 893415.0 | NYSE_TAQ::ADP |
| 2 | 2023-05-15 | 11757 | USCOMP | USD | 193.7300 | 197.0050 | 191.2600 | 196.91 | 834985.0 | NYSE_TAQ::ADSK |
| 3 | 2023-05-15 | 11060 | USCOMP | USD | 130.2200 | 130.8100 | 129.1250 | 130.66 | 372910.0 | NYSE_TAQ::AIZ |
| 4 | 2023-05-15 | 11120 | USCOMP | USD | 14.8500 | 15.0000 | 14.7600 | 14.96 | 3068.0 | NYSE_TAQ::ALOT |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 67 | 2023-05-15 | 114707 | USCOMP | USD | 0.4399 | 0.4599 | 0.3484 | 0.37 | 1874561.0 | NYSE_TAQ::PRSO |
| 68 | 2023-05-15 | 114852 | USCOMP | USD | 0.7200 | 0.7200 | 0.6000 | 0.63 | 1407405.0 | NYSE_TAQ::SBFM |
| 69 | 2023-05-15 | 112560 | USCOMP | USD | 64.5800 | 64.9500 | 63.5900 | 63.70 | 2135902.0 | NYSE_TAQ::TAP |
| 70 | 2023-05-15 | 112559 | USCOMP | USD | 0.0000 | 67.6500 | 66.0100 | 66.83 | 0.0 | NYSE_TAQ::TAPA |
| 71 | 2023-05-15 | 11574 | USCOMP | USD | 11.9400 | 12.2550 | 11.9400 | 12.00 | 63111.0 | NYSE_TAQ::VOXX |
72 rows × 10 columns