{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3966fa65-b8a8-4d09-9e2c-e031727a0d8c",
   "metadata": {},
   "source": [
    "# Daily OHLCV (with closing prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0186c6e6-6979-499f-aadb-4c76b5fa7f5c",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "This is part of the OQD offering. Available in OneTick Cloud. Can be configured on prem.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7336ed2-69ef-4d96-860d-2b13184cfa1b",
   "metadata": {},
   "source": [
    "We can retrieve daily OHLCV data for specific tickers using various symbologies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "397be0a1-5817-4341-b0b2-4159c32558a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>OID</th>\n",
       "      <th>EXCH</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>OPEN</th>\n",
       "      <th>HIGH</th>\n",
       "      <th>LOW</th>\n",
       "      <th>CLOSE</th>\n",
       "      <th>VOLUME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>9706</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>138.210</td>\n",
       "      <td>143.07</td>\n",
       "      <td>137.685</td>\n",
       "      <td>142.45</td>\n",
       "      <td>114311700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-04</td>\n",
       "      <td>9706</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>145.030</td>\n",
       "      <td>146.22</td>\n",
       "      <td>144.260</td>\n",
       "      <td>146.10</td>\n",
       "      <td>87830060.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-05</td>\n",
       "      <td>9706</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>144.075</td>\n",
       "      <td>147.38</td>\n",
       "      <td>143.010</td>\n",
       "      <td>146.40</td>\n",
       "      <td>79470970.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-06</td>\n",
       "      <td>9706</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>145.810</td>\n",
       "      <td>147.54</td>\n",
       "      <td>145.220</td>\n",
       "      <td>145.43</td>\n",
       "      <td>68402170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-07</td>\n",
       "      <td>9706</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>142.540</td>\n",
       "      <td>143.10</td>\n",
       "      <td>139.445</td>\n",
       "      <td>140.09</td>\n",
       "      <td>85925560.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Time   OID    EXCH CURRENCY     OPEN    HIGH      LOW   CLOSE       VOLUME\n",
       "0 2022-10-03  9706  USCOMP      USD  138.210  143.07  137.685  142.45  114311700.0\n",
       "1 2022-10-04  9706  USCOMP      USD  145.030  146.22  144.260  146.10   87830060.0\n",
       "2 2022-10-05  9706  USCOMP      USD  144.075  147.38  143.010  146.40   79470970.0\n",
       "3 2022-10-06  9706  USCOMP      USD  145.810  147.54  145.220  145.43   68402170.0\n",
       "4 2022-10-07  9706  USCOMP      USD  142.540  143.10  139.445  140.09   85925560.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import onetick.py as otp\n",
    "src = otp.oqd.sources.OHLCV(exch='USCOMP')\n",
    "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))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "478031d0-4e11-4ced-aa39-665fb1764a6a",
   "metadata": {},
   "source": [
    "Examples of other supported symbologies include:\n",
    "`TDEQ::::AAPL`\n",
    "`SED::::2046251`\n",
    "`FGV::::BBG000B9XRY4`\n",
    "`ISN::::US0378331005`\n",
    "`TKR::::ALSPW.FRXPAR`.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9926fd46-0ef5-40cf-b04b-740b189c72b1",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9a759dec-ac49-4944-b0da-41f6d002f24b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>SYMBOL_NAME</th>\n",
       "      <th>ORIGINAL_SYMBOL_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>3751</td>\n",
       "      <td>NYSE_TAQ::A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>647321</td>\n",
       "      <td>NYSE_TAQ::AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>695581</td>\n",
       "      <td>NYSE_TAQ::AAA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>673522</td>\n",
       "      <td>NYSE_TAQ::AAAU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>703090</td>\n",
       "      <td>NYSE_TAQ::AAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12182</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>273584</td>\n",
       "      <td>NYSE_TAQ::ZWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12183</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>704054</td>\n",
       "      <td>NYSE_TAQ::ZY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12184</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>655470</td>\n",
       "      <td>NYSE_TAQ::ZYME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12185</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>633589</td>\n",
       "      <td>NYSE_TAQ::ZYNE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12186</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>208375</td>\n",
       "      <td>NYSE_TAQ::ZYXI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12187 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Time SYMBOL_NAME ORIGINAL_SYMBOL_NAME\n",
       "0     2022-10-01        3751          NYSE_TAQ::A\n",
       "1     2022-10-01      647321         NYSE_TAQ::AA\n",
       "2     2022-10-01      695581        NYSE_TAQ::AAA\n",
       "3     2022-10-01      673522       NYSE_TAQ::AAAU\n",
       "4     2022-10-01      703090        NYSE_TAQ::AAC\n",
       "...          ...         ...                  ...\n",
       "12182 2022-10-01      273584        NYSE_TAQ::ZWS\n",
       "12183 2022-10-01      704054         NYSE_TAQ::ZY\n",
       "12184 2022-10-01      655470       NYSE_TAQ::ZYME\n",
       "12185 2022-10-01      633589       NYSE_TAQ::ZYNE\n",
       "12186 2022-10-01      208375       NYSE_TAQ::ZYXI\n",
       "\n",
       "[12187 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oid = otp.Symbols('NYSE_TAQ', symbology='OID', show_original_symbols=True)\n",
    "oid, _ = oid[oid['SYMBOL_NAME']!='']\n",
    "otp.run(oid, start=otp.dt(2022, 10, 1), end=otp.dt(2022, 10, 7))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0609339-9625-4c4a-a0ec-efa77b72a448",
   "metadata": {},
   "source": [
    "We can now query OHLCV using OIDs. The code below retrieves OHLCV for all US equities for 5 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "57d0047e-d5da-4256-a307-83da7080765e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>OID</th>\n",
       "      <th>EXCH</th>\n",
       "      <th>CURRENCY</th>\n",
       "      <th>OPEN</th>\n",
       "      <th>HIGH</th>\n",
       "      <th>LOW</th>\n",
       "      <th>CLOSE</th>\n",
       "      <th>VOLUME</th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>3751</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>122.730</td>\n",
       "      <td>127.300</td>\n",
       "      <td>121.6800</td>\n",
       "      <td>126.3800</td>\n",
       "      <td>1343211.0</td>\n",
       "      <td>NYSE_TAQ::A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>647321</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>34.350</td>\n",
       "      <td>37.940</td>\n",
       "      <td>34.0000</td>\n",
       "      <td>37.3900</td>\n",
       "      <td>9731361.0</td>\n",
       "      <td>NYSE_TAQ::AA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>695581</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>24.075</td>\n",
       "      <td>24.075</td>\n",
       "      <td>24.0750</td>\n",
       "      <td>24.0750</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NYSE_TAQ::AAA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>673522</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>16.560</td>\n",
       "      <td>16.890</td>\n",
       "      <td>16.5500</td>\n",
       "      <td>16.8750</td>\n",
       "      <td>585071.0</td>\n",
       "      <td>NYSE_TAQ::AAAU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>703090</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>9.930</td>\n",
       "      <td>9.940</td>\n",
       "      <td>9.9300</td>\n",
       "      <td>9.9400</td>\n",
       "      <td>294860.0</td>\n",
       "      <td>NYSE_TAQ::AAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60752</th>\n",
       "      <td>2022-10-07</td>\n",
       "      <td>273584</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>24.920</td>\n",
       "      <td>25.250</td>\n",
       "      <td>24.5000</td>\n",
       "      <td>24.5800</td>\n",
       "      <td>574758.0</td>\n",
       "      <td>NYSE_TAQ::ZWS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60753</th>\n",
       "      <td>2022-10-07</td>\n",
       "      <td>704054</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>2.830</td>\n",
       "      <td>2.910</td>\n",
       "      <td>2.7500</td>\n",
       "      <td>2.7800</td>\n",
       "      <td>755683.0</td>\n",
       "      <td>NYSE_TAQ::ZY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60754</th>\n",
       "      <td>2022-10-07</td>\n",
       "      <td>655470</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>6.060</td>\n",
       "      <td>6.170</td>\n",
       "      <td>5.7400</td>\n",
       "      <td>5.9000</td>\n",
       "      <td>1057437.0</td>\n",
       "      <td>NYSE_TAQ::ZYME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60755</th>\n",
       "      <td>2022-10-07</td>\n",
       "      <td>633589</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>0.769</td>\n",
       "      <td>0.820</td>\n",
       "      <td>0.7411</td>\n",
       "      <td>0.7411</td>\n",
       "      <td>242326.0</td>\n",
       "      <td>NYSE_TAQ::ZYNE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60756</th>\n",
       "      <td>2022-10-07</td>\n",
       "      <td>208375</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>9.270</td>\n",
       "      <td>9.400</td>\n",
       "      <td>8.7500</td>\n",
       "      <td>8.8000</td>\n",
       "      <td>141339.0</td>\n",
       "      <td>NYSE_TAQ::ZYXI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>60757 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Time     OID    EXCH CURRENCY     OPEN     HIGH       LOW     CLOSE     VOLUME          ticker\n",
       "0     2022-10-03    3751  USCOMP      USD  122.730  127.300  121.6800  126.3800  1343211.0     NYSE_TAQ::A\n",
       "1     2022-10-03  647321  USCOMP      USD   34.350   37.940   34.0000   37.3900  9731361.0    NYSE_TAQ::AA\n",
       "2     2022-10-03  695581  USCOMP      USD   24.075   24.075   24.0750   24.0750        9.0   NYSE_TAQ::AAA\n",
       "3     2022-10-03  673522  USCOMP      USD   16.560   16.890   16.5500   16.8750   585071.0  NYSE_TAQ::AAAU\n",
       "4     2022-10-03  703090  USCOMP      USD    9.930    9.940    9.9300    9.9400   294860.0   NYSE_TAQ::AAC\n",
       "...          ...     ...     ...      ...      ...      ...       ...       ...        ...             ...\n",
       "60752 2022-10-07  273584  USCOMP      USD   24.920   25.250   24.5000   24.5800   574758.0   NYSE_TAQ::ZWS\n",
       "60753 2022-10-07  704054  USCOMP      USD    2.830    2.910    2.7500    2.7800   755683.0    NYSE_TAQ::ZY\n",
       "60754 2022-10-07  655470  USCOMP      USD    6.060    6.170    5.7400    5.9000  1057437.0  NYSE_TAQ::ZYME\n",
       "60755 2022-10-07  633589  USCOMP      USD    0.769    0.820    0.7411    0.7411   242326.0  NYSE_TAQ::ZYNE\n",
       "60756 2022-10-07  208375  USCOMP      USD    9.270    9.400    8.7500    8.8000   141339.0  NYSE_TAQ::ZYXI\n",
       "\n",
       "[60757 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ohlcv = otp.oqd.sources.OHLCV(exch='USCOMP')\n",
    "ohlcv['ticker'] = ohlcv.Symbol.ORIGINAL_SYMBOL_NAME\n",
    "ohlcv = otp.merge([ohlcv], symbols=oid)\n",
    "otp.run(ohlcv, start=otp.dt(2022, 10, 1), end=otp.dt(2022, 10, 7))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c81e126-bf5f-4771-a7a7-23f4c8c80b9e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}