{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "3966fa65-b8a8-4d09-9e2c-e031727a0d8c",
   "metadata": {},
   "source": [
    "# Daily OHLCV (with closing prices)"
   ]
  },
  {
   "attachments": {},
   "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>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b7336ed2-69ef-4d96-860d-2b13184cfa1b",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import onetick.py as otp\n",
    "\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), timezone='GMT')"
   ]
  },
  {
   "attachments": {},
   "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"
   ]
  },
  {
   "attachments": {},
   "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": 7,
   "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>2023-05-15</td>\n",
       "      <td>11233</td>\n",
       "      <td>NYSE_TAQ::AAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11809</td>\n",
       "      <td>NYSE_TAQ::ADP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11757</td>\n",
       "      <td>NYSE_TAQ::ADSK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11060</td>\n",
       "      <td>NYSE_TAQ::AIZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11120</td>\n",
       "      <td>NYSE_TAQ::ALOT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>114707</td>\n",
       "      <td>NYSE_TAQ::PRSO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>114852</td>\n",
       "      <td>NYSE_TAQ::SBFM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>112560</td>\n",
       "      <td>NYSE_TAQ::TAP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>112559</td>\n",
       "      <td>NYSE_TAQ::TAPA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11574</td>\n",
       "      <td>NYSE_TAQ::VOXX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Time SYMBOL_NAME ORIGINAL_SYMBOL_NAME\n",
       "0  2023-05-15       11233       NYSE_TAQ::AAME\n",
       "1  2023-05-15       11809        NYSE_TAQ::ADP\n",
       "2  2023-05-15       11757       NYSE_TAQ::ADSK\n",
       "3  2023-05-15       11060        NYSE_TAQ::AIZ\n",
       "4  2023-05-15       11120       NYSE_TAQ::ALOT\n",
       "..        ...         ...                  ...\n",
       "67 2023-05-15      114707       NYSE_TAQ::PRSO\n",
       "68 2023-05-15      114852       NYSE_TAQ::SBFM\n",
       "69 2023-05-15      112560        NYSE_TAQ::TAP\n",
       "70 2023-05-15      112559       NYSE_TAQ::TAPA\n",
       "71 2023-05-15       11574       NYSE_TAQ::VOXX\n",
       "\n",
       "[72 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oid = otp.Symbols('NYSE_TAQ', symbology='OID', show_original_symbols=True, pattern='11%')\n",
    "oid, _ = oid[oid['SYMBOL_NAME'] != '']\n",
    "otp.run(oid, start=otp.dt(2023, 5, 15), end=otp.dt(2023, 5, 16), timezone='GMT')"
   ]
  },
  {
   "attachments": {},
   "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": 14,
   "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>2023-05-15</td>\n",
       "      <td>11233</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>2.2500</td>\n",
       "      <td>2.2500</td>\n",
       "      <td>2.2200</td>\n",
       "      <td>2.25</td>\n",
       "      <td>4700.0</td>\n",
       "      <td>NYSE_TAQ::AAME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11809</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>212.2400</td>\n",
       "      <td>214.5700</td>\n",
       "      <td>212.0000</td>\n",
       "      <td>214.39</td>\n",
       "      <td>893415.0</td>\n",
       "      <td>NYSE_TAQ::ADP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11757</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>193.7300</td>\n",
       "      <td>197.0050</td>\n",
       "      <td>191.2600</td>\n",
       "      <td>196.91</td>\n",
       "      <td>834985.0</td>\n",
       "      <td>NYSE_TAQ::ADSK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11060</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>130.2200</td>\n",
       "      <td>130.8100</td>\n",
       "      <td>129.1250</td>\n",
       "      <td>130.66</td>\n",
       "      <td>372910.0</td>\n",
       "      <td>NYSE_TAQ::AIZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11120</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>14.8500</td>\n",
       "      <td>15.0000</td>\n",
       "      <td>14.7600</td>\n",
       "      <td>14.96</td>\n",
       "      <td>3068.0</td>\n",
       "      <td>NYSE_TAQ::ALOT</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>67</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>114707</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>0.4399</td>\n",
       "      <td>0.4599</td>\n",
       "      <td>0.3484</td>\n",
       "      <td>0.37</td>\n",
       "      <td>1874561.0</td>\n",
       "      <td>NYSE_TAQ::PRSO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>114852</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>0.7200</td>\n",
       "      <td>0.7200</td>\n",
       "      <td>0.6000</td>\n",
       "      <td>0.63</td>\n",
       "      <td>1407405.0</td>\n",
       "      <td>NYSE_TAQ::SBFM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>112560</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>64.5800</td>\n",
       "      <td>64.9500</td>\n",
       "      <td>63.5900</td>\n",
       "      <td>63.70</td>\n",
       "      <td>2135902.0</td>\n",
       "      <td>NYSE_TAQ::TAP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>112559</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>67.6500</td>\n",
       "      <td>66.0100</td>\n",
       "      <td>66.83</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NYSE_TAQ::TAPA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>11574</td>\n",
       "      <td>USCOMP</td>\n",
       "      <td>USD</td>\n",
       "      <td>11.9400</td>\n",
       "      <td>12.2550</td>\n",
       "      <td>11.9400</td>\n",
       "      <td>12.00</td>\n",
       "      <td>63111.0</td>\n",
       "      <td>NYSE_TAQ::VOXX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Time     OID    EXCH CURRENCY      OPEN      HIGH       LOW   CLOSE     VOLUME          ticker\n",
       "0  2023-05-15   11233  USCOMP      USD    2.2500    2.2500    2.2200    2.25     4700.0  NYSE_TAQ::AAME\n",
       "1  2023-05-15   11809  USCOMP      USD  212.2400  214.5700  212.0000  214.39   893415.0   NYSE_TAQ::ADP\n",
       "2  2023-05-15   11757  USCOMP      USD  193.7300  197.0050  191.2600  196.91   834985.0  NYSE_TAQ::ADSK\n",
       "3  2023-05-15   11060  USCOMP      USD  130.2200  130.8100  129.1250  130.66   372910.0   NYSE_TAQ::AIZ\n",
       "4  2023-05-15   11120  USCOMP      USD   14.8500   15.0000   14.7600   14.96     3068.0  NYSE_TAQ::ALOT\n",
       "..        ...     ...     ...      ...       ...       ...       ...     ...        ...             ...\n",
       "67 2023-05-15  114707  USCOMP      USD    0.4399    0.4599    0.3484    0.37  1874561.0  NYSE_TAQ::PRSO\n",
       "68 2023-05-15  114852  USCOMP      USD    0.7200    0.7200    0.6000    0.63  1407405.0  NYSE_TAQ::SBFM\n",
       "69 2023-05-15  112560  USCOMP      USD   64.5800   64.9500   63.5900   63.70  2135902.0   NYSE_TAQ::TAP\n",
       "70 2023-05-15  112559  USCOMP      USD    0.0000   67.6500   66.0100   66.83        0.0  NYSE_TAQ::TAPA\n",
       "71 2023-05-15   11574  USCOMP      USD   11.9400   12.2550   11.9400   12.00    63111.0  NYSE_TAQ::VOXX\n",
       "\n",
       "[72 rows x 10 columns]"
      ]
     },
     "execution_count": 14,
     "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(2023, 5, 15), end=otp.dt(2023, 5, 15, 23), timezone='GMT')"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}