sp500.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import ssl\n",
    "import numpy as np\n",
    "from pathlib import Path\n",
    "import sqlalchemy\n",
    "import datetime\n",
    "# import hvplot.pandas\n",
    "# import matplotlib.pyplot as plt\n",
    "# from sklearn import svm\n",
    "# from sklearn.preprocessing import StandardScaler\n",
    "# from sklearn.linear_model import LogisticRegression\n",
    "# from pandas.tseries.offsets import DateOffset\n",
    "# from sklearn.metrics import classification_report\n",
    "\n",
    "from utils.alpaca import alpaca_symbol_data\n",
    "from utils.talltowide import TalltoWide\n",
    "\n",
    "# For creating the DB\n",
    "database_connection_string = 'sqlite:///SP500.db'\n",
    "engine = sqlalchemy.create_engine(database_connection_string)\n",
    "\n",
    "# prevent an ssl failure\n",
    "ssl._create_default_https_context = ssl._create_unverified_context\n",
    "\n",
    "payload=pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')\n",
    "\n",
    "# There are 2 tables on the Wikipedia page\n",
    "# we want the first table\n",
    "first_table = payload[0]\n",
    "second_table = payload[1]\n",
    "\n",
    "df500table = first_table\n",
    "\n",
    "sp500Tickers = df500table['Symbol'].values.tolist()\n",
    "df500sectors = df500table.drop(columns={'Security','SEC filings','GICS Sub-Industry', 'Headquarters Location', 'Date first added', 'CIK','Founded'})\n",
    "df500sectors.set_index('Symbol', inplace=True)\n",
    "# print(sp500Tickers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "504\n",
      "100\n",
      "100\n",
      "100\n",
      "100\n",
      "100\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "# Sets up lists to hold the tickers\n",
    "count = 0\n",
    "tickers_0 = []\n",
    "tickers_1 = []\n",
    "tickers_2 = []\n",
    "tickers_3 = []\n",
    "tickers_4 = []\n",
    "tickers_5 = []\n",
    "\n",
    "\n",
    "size = len(sp500Tickers)\n",
    "print(size)\n",
    "\n",
    "# Reads the tickers from the Wiki Table into the ticker holders\n",
    "for each_ticker in sp500Tickers:\n",
    "    if count <= 99:\n",
    "        tickers_0.append(each_ticker)\n",
    "    if count >= 100 and count <= 199:\n",
    "        tickers_1.append(each_ticker)\n",
    "    if count >= 200 and count <= 299:\n",
    "        tickers_2.append(each_ticker)\n",
    "    if count >= 300 and count <= 399:\n",
    "        tickers_3.append(each_ticker)\n",
    "    if count >= 400 and count <= 499:\n",
    "        tickers_4.append(each_ticker)\n",
    "    if count >= 500:\n",
    "        tickers_5.append(each_ticker)\n",
    "    count = count + 1\n",
    "# print(count)\n",
    "\n",
    "# Adds SPY to the last list, which will be used for y in the machine learning models\n",
    "tickers_5.append('SPY')\n",
    "\n",
    "\n",
    "# Verify's appropriate lengths, should be 100 each except the last which should be 5\n",
    "print(len(tickers_0))\n",
    "# print(tickers_0)\n",
    "print(len(tickers_1))\n",
    "# print(tickers_1)\n",
    "print(len(tickers_2))\n",
    "# print(tickers_2)\n",
    "print(len(tickers_3))\n",
    "# print(tickers_3)\n",
    "print(len(tickers_4))\n",
    "# print(tickers_4)\n",
    "print(len(tickers_5))\n",
    "# print(tickers_5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trade_count</th>\n",
       "      <th>vwap</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-04-19 04:00:00+00:00</th>\n",
       "      <td>21.98</td>\n",
       "      <td>21.985</td>\n",
       "      <td>21.37</td>\n",
       "      <td>21.55</td>\n",
       "      <td>26643764</td>\n",
       "      <td>128347</td>\n",
       "      <td>21.641655</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-20 04:00:00+00:00</th>\n",
       "      <td>21.01</td>\n",
       "      <td>21.020</td>\n",
       "      <td>19.96</td>\n",
       "      <td>20.36</td>\n",
       "      <td>47740892</td>\n",
       "      <td>236257</td>\n",
       "      <td>20.326026</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-21 04:00:00+00:00</th>\n",
       "      <td>20.00</td>\n",
       "      <td>21.030</td>\n",
       "      <td>19.64</td>\n",
       "      <td>21.01</td>\n",
       "      <td>48033754</td>\n",
       "      <td>191826</td>\n",
       "      <td>20.593699</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-22 04:00:00+00:00</th>\n",
       "      <td>21.57</td>\n",
       "      <td>21.610</td>\n",
       "      <td>20.04</td>\n",
       "      <td>20.07</td>\n",
       "      <td>72567588</td>\n",
       "      <td>290574</td>\n",
       "      <td>20.655093</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-23 04:00:00+00:00</th>\n",
       "      <td>20.27</td>\n",
       "      <td>21.200</td>\n",
       "      <td>20.18</td>\n",
       "      <td>21.11</td>\n",
       "      <td>48357876</td>\n",
       "      <td>181554</td>\n",
       "      <td>20.858045</td>\n",
       "      <td>AAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            open    high    low  close    volume  trade_count  \\\n",
       "timestamp                                                                       \n",
       "2021-04-19 04:00:00+00:00  21.98  21.985  21.37  21.55  26643764       128347   \n",
       "2021-04-20 04:00:00+00:00  21.01  21.020  19.96  20.36  47740892       236257   \n",
       "2021-04-21 04:00:00+00:00  20.00  21.030  19.64  21.01  48033754       191826   \n",
       "2021-04-22 04:00:00+00:00  21.57  21.610  20.04  20.07  72567588       290574   \n",
       "2021-04-23 04:00:00+00:00  20.27  21.200  20.18  21.11  48357876       181554   \n",
       "\n",
       "                                vwap symbol  \n",
       "timestamp                                    \n",
       "2021-04-19 04:00:00+00:00  21.641655    AAL  \n",
       "2021-04-20 04:00:00+00:00  20.326026    AAL  \n",
       "2021-04-21 04:00:00+00:00  20.593699    AAL  \n",
       "2021-04-22 04:00:00+00:00  20.655093    AAL  \n",
       "2021-04-23 04:00:00+00:00  20.858045    AAL  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trade_count</th>\n",
       "      <th>vwap</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-04-19 04:00:00+00:00</th>\n",
       "      <td>37.72</td>\n",
       "      <td>37.92</td>\n",
       "      <td>37.410</td>\n",
       "      <td>37.85</td>\n",
       "      <td>3086344</td>\n",
       "      <td>23425</td>\n",
       "      <td>37.783903</td>\n",
       "      <td>CAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-20 04:00:00+00:00</th>\n",
       "      <td>37.73</td>\n",
       "      <td>38.35</td>\n",
       "      <td>37.505</td>\n",
       "      <td>38.27</td>\n",
       "      <td>3551909</td>\n",
       "      <td>29557</td>\n",
       "      <td>38.108433</td>\n",
       "      <td>CAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-21 04:00:00+00:00</th>\n",
       "      <td>38.39</td>\n",
       "      <td>38.65</td>\n",
       "      <td>38.180</td>\n",
       "      <td>38.55</td>\n",
       "      <td>3130521</td>\n",
       "      <td>28887</td>\n",
       "      <td>38.461660</td>\n",
       "      <td>CAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-22 04:00:00+00:00</th>\n",
       "      <td>38.59</td>\n",
       "      <td>38.69</td>\n",
       "      <td>38.045</td>\n",
       "      <td>38.17</td>\n",
       "      <td>2074361</td>\n",
       "      <td>20811</td>\n",
       "      <td>38.203525</td>\n",
       "      <td>CAG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-23 04:00:00+00:00</th>\n",
       "      <td>38.01</td>\n",
       "      <td>38.07</td>\n",
       "      <td>37.340</td>\n",
       "      <td>37.61</td>\n",
       "      <td>2685201</td>\n",
       "      <td>23639</td>\n",
       "      <td>37.602605</td>\n",
       "      <td>CAG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            open   high     low  close   volume  trade_count  \\\n",
       "timestamp                                                                      \n",
       "2021-04-19 04:00:00+00:00  37.72  37.92  37.410  37.85  3086344        23425   \n",
       "2021-04-20 04:00:00+00:00  37.73  38.35  37.505  38.27  3551909        29557   \n",
       "2021-04-21 04:00:00+00:00  38.39  38.65  38.180  38.55  3130521        28887   \n",
       "2021-04-22 04:00:00+00:00  38.59  38.69  38.045  38.17  2074361        20811   \n",
       "2021-04-23 04:00:00+00:00  38.01  38.07  37.340  37.61  2685201        23639   \n",
       "\n",
       "                                vwap symbol  \n",
       "timestamp                                    \n",
       "2021-04-19 04:00:00+00:00  37.783903    CAG  \n",
       "2021-04-20 04:00:00+00:00  38.108433    CAG  \n",
       "2021-04-21 04:00:00+00:00  38.461660    CAG  \n",
       "2021-04-22 04:00:00+00:00  38.203525    CAG  \n",
       "2021-04-23 04:00:00+00:00  37.602605    CAG  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trade_count</th>\n",
       "      <th>vwap</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-04-19 04:00:00+00:00</th>\n",
       "      <td>136.27</td>\n",
       "      <td>136.610</td>\n",
       "      <td>134.840</td>\n",
       "      <td>136.26</td>\n",
       "      <td>616095</td>\n",
       "      <td>10589</td>\n",
       "      <td>135.989666</td>\n",
       "      <td>AJG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-20 04:00:00+00:00</th>\n",
       "      <td>136.47</td>\n",
       "      <td>137.340</td>\n",
       "      <td>135.920</td>\n",
       "      <td>136.26</td>\n",
       "      <td>562944</td>\n",
       "      <td>10473</td>\n",
       "      <td>136.433863</td>\n",
       "      <td>AJG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-21 04:00:00+00:00</th>\n",
       "      <td>136.57</td>\n",
       "      <td>137.295</td>\n",
       "      <td>135.830</td>\n",
       "      <td>137.17</td>\n",
       "      <td>437274</td>\n",
       "      <td>9160</td>\n",
       "      <td>136.892308</td>\n",
       "      <td>AJG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-22 04:00:00+00:00</th>\n",
       "      <td>137.00</td>\n",
       "      <td>137.160</td>\n",
       "      <td>135.655</td>\n",
       "      <td>136.25</td>\n",
       "      <td>405983</td>\n",
       "      <td>9803</td>\n",
       "      <td>136.402039</td>\n",
       "      <td>AJG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-23 04:00:00+00:00</th>\n",
       "      <td>136.50</td>\n",
       "      <td>138.300</td>\n",
       "      <td>136.270</td>\n",
       "      <td>137.94</td>\n",
       "      <td>562948</td>\n",
       "      <td>9725</td>\n",
       "      <td>137.718109</td>\n",
       "      <td>AJG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             open     high      low   close  volume  \\\n",
       "timestamp                                                             \n",
       "2021-04-19 04:00:00+00:00  136.27  136.610  134.840  136.26  616095   \n",
       "2021-04-20 04:00:00+00:00  136.47  137.340  135.920  136.26  562944   \n",
       "2021-04-21 04:00:00+00:00  136.57  137.295  135.830  137.17  437274   \n",
       "2021-04-22 04:00:00+00:00  137.00  137.160  135.655  136.25  405983   \n",
       "2021-04-23 04:00:00+00:00  136.50  138.300  136.270  137.94  562948   \n",
       "\n",
       "                           trade_count        vwap symbol  \n",
       "timestamp                                                  \n",
       "2021-04-19 04:00:00+00:00        10589  135.989666    AJG  \n",
       "2021-04-20 04:00:00+00:00        10473  136.433863    AJG  \n",
       "2021-04-21 04:00:00+00:00         9160  136.892308    AJG  \n",
       "2021-04-22 04:00:00+00:00         9803  136.402039    AJG  \n",
       "2021-04-23 04:00:00+00:00         9725  137.718109    AJG  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trade_count</th>\n",
       "      <th>vwap</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-04-19 04:00:00+00:00</th>\n",
       "      <td>130.60</td>\n",
       "      <td>131.09</td>\n",
       "      <td>129.47</td>\n",
       "      <td>131.09</td>\n",
       "      <td>866758</td>\n",
       "      <td>13218</td>\n",
       "      <td>130.423612</td>\n",
       "      <td>DGX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-20 04:00:00+00:00</th>\n",
       "      <td>130.50</td>\n",
       "      <td>131.40</td>\n",
       "      <td>128.17</td>\n",
       "      <td>129.37</td>\n",
       "      <td>1024356</td>\n",
       "      <td>14789</td>\n",
       "      <td>129.369187</td>\n",
       "      <td>DGX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-21 04:00:00+00:00</th>\n",
       "      <td>129.47</td>\n",
       "      <td>130.67</td>\n",
       "      <td>129.22</td>\n",
       "      <td>130.02</td>\n",
       "      <td>1166288</td>\n",
       "      <td>19732</td>\n",
       "      <td>129.797679</td>\n",
       "      <td>DGX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-22 04:00:00+00:00</th>\n",
       "      <td>131.11</td>\n",
       "      <td>133.90</td>\n",
       "      <td>128.33</td>\n",
       "      <td>133.69</td>\n",
       "      <td>2127915</td>\n",
       "      <td>25094</td>\n",
       "      <td>132.186699</td>\n",
       "      <td>DGX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-23 04:00:00+00:00</th>\n",
       "      <td>131.10</td>\n",
       "      <td>132.78</td>\n",
       "      <td>130.01</td>\n",
       "      <td>131.21</td>\n",
       "      <td>1586034</td>\n",
       "      <td>20479</td>\n",
       "      <td>131.328296</td>\n",
       "      <td>DGX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             open    high     low   close   volume  \\\n",
       "timestamp                                                            \n",
       "2021-04-19 04:00:00+00:00  130.60  131.09  129.47  131.09   866758   \n",
       "2021-04-20 04:00:00+00:00  130.50  131.40  128.17  129.37  1024356   \n",
       "2021-04-21 04:00:00+00:00  129.47  130.67  129.22  130.02  1166288   \n",
       "2021-04-22 04:00:00+00:00  131.11  133.90  128.33  133.69  2127915   \n",
       "2021-04-23 04:00:00+00:00  131.10  132.78  130.01  131.21  1586034   \n",
       "\n",
       "                           trade_count        vwap symbol  \n",
       "timestamp                                                  \n",
       "2021-04-19 04:00:00+00:00        13218  130.423612    DGX  \n",
       "2021-04-20 04:00:00+00:00        14789  129.369187    DGX  \n",
       "2021-04-21 04:00:00+00:00        19732  129.797679    DGX  \n",
       "2021-04-22 04:00:00+00:00        25094  132.186699    DGX  \n",
       "2021-04-23 04:00:00+00:00        20479  131.328296    DGX  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trade_count</th>\n",
       "      <th>vwap</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-04-19 04:00:00+00:00</th>\n",
       "      <td>230.83</td>\n",
       "      <td>232.1953</td>\n",
       "      <td>228.87</td>\n",
       "      <td>230.50</td>\n",
       "      <td>3837150</td>\n",
       "      <td>71649</td>\n",
       "      <td>230.191173</td>\n",
       "      <td>CRM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-20 04:00:00+00:00</th>\n",
       "      <td>230.00</td>\n",
       "      <td>231.4500</td>\n",
       "      <td>226.89</td>\n",
       "      <td>227.96</td>\n",
       "      <td>3624123</td>\n",
       "      <td>69632</td>\n",
       "      <td>228.541365</td>\n",
       "      <td>CRM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-21 04:00:00+00:00</th>\n",
       "      <td>227.11</td>\n",
       "      <td>230.7900</td>\n",
       "      <td>226.67</td>\n",
       "      <td>230.56</td>\n",
       "      <td>4013916</td>\n",
       "      <td>63844</td>\n",
       "      <td>229.415149</td>\n",
       "      <td>CRM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-22 04:00:00+00:00</th>\n",
       "      <td>230.00</td>\n",
       "      <td>233.8900</td>\n",
       "      <td>229.01</td>\n",
       "      <td>231.35</td>\n",
       "      <td>4963892</td>\n",
       "      <td>83672</td>\n",
       "      <td>231.737397</td>\n",
       "      <td>CRM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-23 04:00:00+00:00</th>\n",
       "      <td>232.94</td>\n",
       "      <td>235.4900</td>\n",
       "      <td>231.75</td>\n",
       "      <td>233.51</td>\n",
       "      <td>5475468</td>\n",
       "      <td>78352</td>\n",
       "      <td>234.304060</td>\n",
       "      <td>CRM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             open      high     low   close   volume  \\\n",
       "timestamp                                                              \n",
       "2021-04-19 04:00:00+00:00  230.83  232.1953  228.87  230.50  3837150   \n",
       "2021-04-20 04:00:00+00:00  230.00  231.4500  226.89  227.96  3624123   \n",
       "2021-04-21 04:00:00+00:00  227.11  230.7900  226.67  230.56  4013916   \n",
       "2021-04-22 04:00:00+00:00  230.00  233.8900  229.01  231.35  4963892   \n",
       "2021-04-23 04:00:00+00:00  232.94  235.4900  231.75  233.51  5475468   \n",
       "\n",
       "                           trade_count        vwap symbol  \n",
       "timestamp                                                  \n",
       "2021-04-19 04:00:00+00:00        71649  230.191173    CRM  \n",
       "2021-04-20 04:00:00+00:00        69632  228.541365    CRM  \n",
       "2021-04-21 04:00:00+00:00        63844  229.415149    CRM  \n",
       "2021-04-22 04:00:00+00:00        83672  231.737397    CRM  \n",
       "2021-04-23 04:00:00+00:00        78352  234.304060    CRM  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>trade_count</th>\n",
       "      <th>vwap</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-04-19 04:00:00+00:00</th>\n",
       "      <td>416.26</td>\n",
       "      <td>416.7400</td>\n",
       "      <td>413.79</td>\n",
       "      <td>415.21</td>\n",
       "      <td>75527000</td>\n",
       "      <td>450182</td>\n",
       "      <td>415.352416</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-20 04:00:00+00:00</th>\n",
       "      <td>413.93</td>\n",
       "      <td>415.0859</td>\n",
       "      <td>410.59</td>\n",
       "      <td>412.17</td>\n",
       "      <td>79104051</td>\n",
       "      <td>476871</td>\n",
       "      <td>412.677447</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-21 04:00:00+00:00</th>\n",
       "      <td>411.55</td>\n",
       "      <td>416.2900</td>\n",
       "      <td>411.36</td>\n",
       "      <td>416.07</td>\n",
       "      <td>64672721</td>\n",
       "      <td>359135</td>\n",
       "      <td>414.531238</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-22 04:00:00+00:00</th>\n",
       "      <td>415.91</td>\n",
       "      <td>416.7800</td>\n",
       "      <td>411.13</td>\n",
       "      <td>412.27</td>\n",
       "      <td>97276832</td>\n",
       "      <td>587727</td>\n",
       "      <td>413.942573</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-04-23 04:00:00+00:00</th>\n",
       "      <td>412.82</td>\n",
       "      <td>418.2500</td>\n",
       "      <td>412.79</td>\n",
       "      <td>416.74</td>\n",
       "      <td>73241320</td>\n",
       "      <td>362355</td>\n",
       "      <td>415.667363</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             open      high     low   close    volume  \\\n",
       "timestamp                                                               \n",
       "2021-04-19 04:00:00+00:00  416.26  416.7400  413.79  415.21  75527000   \n",
       "2021-04-20 04:00:00+00:00  413.93  415.0859  410.59  412.17  79104051   \n",
       "2021-04-21 04:00:00+00:00  411.55  416.2900  411.36  416.07  64672721   \n",
       "2021-04-22 04:00:00+00:00  415.91  416.7800  411.13  412.27  97276832   \n",
       "2021-04-23 04:00:00+00:00  412.82  418.2500  412.79  416.74  73241320   \n",
       "\n",
       "                           trade_count        vwap symbol  \n",
       "timestamp                                                  \n",
       "2021-04-19 04:00:00+00:00       450182  415.352416    SPY  \n",
       "2021-04-20 04:00:00+00:00       476871  412.677447    SPY  \n",
       "2021-04-21 04:00:00+00:00       359135  414.531238    SPY  \n",
       "2021-04-22 04:00:00+00:00       587727  413.942573    SPY  \n",
       "2021-04-23 04:00:00+00:00       362355  415.667363    SPY  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Calls the API on each ticker\n",
    "\n",
    "# All the tickers\n",
    "tickerList =[tickers_0, tickers_1, tickers_2, tickers_3, tickers_4, tickers_5]\n",
    "\n",
    "\n",
    "# Set start datetimes n years from today\n",
    "years_back = 1\n",
    "\n",
    "# Get the symbol_data and store it into a variable ohlcv_df\n",
    "# ohlcv_df = alpaca_symbol_data(tickers, years_back)\n",
    "ohlcv_df = pd.DataFrame()\n",
    "\n",
    "# Iterates through every ticker and stores it in a DF, then saves that DF into the DB\n",
    "StockCount = 100\n",
    "for tickers in tickerList:\n",
    "    ohlcv_df = alpaca_symbol_data(tickers, years_back)\n",
    "    display(ohlcv_df.head())\n",
    "    ohlcv_df.to_sql('stocks_'+ str(StockCount), engine, if_exists='replace')\n",
    "    StockCount +=100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "filepath = Path('./resources/ohlcv_df_df.csv') \n",
    "filepath.parent.mkdir(parents=True, exist_ok=True) \n",
    "ohlcv_df.to_csv(filepath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reads all the stocks from the DB\n",
    "Stocks100 = pd.read_sql_table('stocks_100', engine, parse_dates='timestamp')\n",
    "Stocks200 = pd.read_sql_table('stocks_200', engine, parse_dates='timestamp')\n",
    "Stocks300 = pd.read_sql_table('stocks_300', engine, parse_dates='timestamp')\n",
    "Stocks400 = pd.read_sql_table('stocks_400', engine, parse_dates='timestamp')\n",
    "Stocks500 = pd.read_sql_table('stocks_500', engine, parse_dates='timestamp')\n",
    "Stocks600 = pd.read_sql_table('stocks_600', engine, parse_dates='timestamp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dlpst\\anaconda3\\envs\\dev\\envs\\dev\\lib\\site-packages\\pandas\\core\\frame.py:4913: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n",
      "C:\\Users\\dlpst\\anaconda3\\envs\\dev\\envs\\dev\\lib\\site-packages\\pandas\\core\\frame.py:5047: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n"
     ]
    },
    {
     "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>symbol</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>A</th>\n",
       "      <th>AAL</th>\n",
       "      <th>AAP</th>\n",
       "      <th>AAPL</th>\n",
       "      <th>ABBV</th>\n",
       "      <th>ABC</th>\n",
       "      <th>ABMD</th>\n",
       "      <th>ABT</th>\n",
       "      <th>ACN</th>\n",
       "      <th>...</th>\n",
       "      <th>XOM</th>\n",
       "      <th>XRAY</th>\n",
       "      <th>XYL</th>\n",
       "      <th>YUM</th>\n",
       "      <th>ZBH</th>\n",
       "      <th>ZBRA</th>\n",
       "      <th>ZION</th>\n",
       "      <th>ZTS</th>\n",
       "      <th>SPY Open</th>\n",
       "      <th>SPY Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-04-19 04:00:00</td>\n",
       "      <td>-0.006549</td>\n",
       "      <td>-0.019563</td>\n",
       "      <td>-0.005424</td>\n",
       "      <td>0.010110</td>\n",
       "      <td>0.005928</td>\n",
       "      <td>-0.004167</td>\n",
       "      <td>0.003415</td>\n",
       "      <td>0.007524</td>\n",
       "      <td>-0.001951</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.005634</td>\n",
       "      <td>0.000611</td>\n",
       "      <td>-0.006974</td>\n",
       "      <td>-0.005660</td>\n",
       "      <td>0.003548</td>\n",
       "      <td>-0.007537</td>\n",
       "      <td>-0.004802</td>\n",
       "      <td>0.003582</td>\n",
       "      <td>416.26</td>\n",
       "      <td>415.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-04-20 04:00:00</td>\n",
       "      <td>-0.004060</td>\n",
       "      <td>-0.030938</td>\n",
       "      <td>-0.020657</td>\n",
       "      <td>-0.013926</td>\n",
       "      <td>0.008417</td>\n",
       "      <td>0.010059</td>\n",
       "      <td>-0.002388</td>\n",
       "      <td>-0.012020</td>\n",
       "      <td>0.009338</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.016717</td>\n",
       "      <td>0.001989</td>\n",
       "      <td>-0.005638</td>\n",
       "      <td>0.007258</td>\n",
       "      <td>0.007419</td>\n",
       "      <td>-0.011536</td>\n",
       "      <td>-0.050245</td>\n",
       "      <td>0.004704</td>\n",
       "      <td>413.93</td>\n",
       "      <td>412.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-04-21 04:00:00</td>\n",
       "      <td>0.007149</td>\n",
       "      <td>0.050500</td>\n",
       "      <td>0.027873</td>\n",
       "      <td>0.008614</td>\n",
       "      <td>0.009199</td>\n",
       "      <td>0.001322</td>\n",
       "      <td>0.032889</td>\n",
       "      <td>0.018019</td>\n",
       "      <td>0.003625</td>\n",
       "      <td>...</td>\n",
       "      <td>0.027523</td>\n",
       "      <td>0.011623</td>\n",
       "      <td>0.017189</td>\n",
       "      <td>-0.002632</td>\n",
       "      <td>0.014997</td>\n",
       "      <td>0.009901</td>\n",
       "      <td>0.030146</td>\n",
       "      <td>0.003361</td>\n",
       "      <td>411.55</td>\n",
       "      <td>416.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-04-22 04:00:00</td>\n",
       "      <td>0.003731</td>\n",
       "      <td>-0.069541</td>\n",
       "      <td>0.006533</td>\n",
       "      <td>-0.006850</td>\n",
       "      <td>-0.005423</td>\n",
       "      <td>-0.004857</td>\n",
       "      <td>0.007363</td>\n",
       "      <td>0.005618</td>\n",
       "      <td>-0.002852</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.011270</td>\n",
       "      <td>-0.016418</td>\n",
       "      <td>-0.008364</td>\n",
       "      <td>-0.003046</td>\n",
       "      <td>0.004531</td>\n",
       "      <td>-0.017499</td>\n",
       "      <td>-0.021140</td>\n",
       "      <td>-0.003771</td>\n",
       "      <td>415.91</td>\n",
       "      <td>412.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-04-23 04:00:00</td>\n",
       "      <td>0.015680</td>\n",
       "      <td>0.041441</td>\n",
       "      <td>-0.006120</td>\n",
       "      <td>0.015652</td>\n",
       "      <td>0.015222</td>\n",
       "      <td>0.006536</td>\n",
       "      <td>0.015741</td>\n",
       "      <td>-0.002508</td>\n",
       "      <td>0.005341</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005246</td>\n",
       "      <td>0.011305</td>\n",
       "      <td>0.007570</td>\n",
       "      <td>-0.000085</td>\n",
       "      <td>0.003389</td>\n",
       "      <td>0.011777</td>\n",
       "      <td>0.035489</td>\n",
       "      <td>0.009112</td>\n",
       "      <td>412.82</td>\n",
       "      <td>416.74</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>2022-04-11 04:00:00</td>\n",
       "      <td>-0.014713</td>\n",
       "      <td>0.028485</td>\n",
       "      <td>-0.021131</td>\n",
       "      <td>-0.017894</td>\n",
       "      <td>-0.029543</td>\n",
       "      <td>-0.018407</td>\n",
       "      <td>-0.042728</td>\n",
       "      <td>-0.027859</td>\n",
       "      <td>-0.026272</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.023524</td>\n",
       "      <td>0.026032</td>\n",
       "      <td>-0.009245</td>\n",
       "      <td>-0.005213</td>\n",
       "      <td>-0.017041</td>\n",
       "      <td>-0.005064</td>\n",
       "      <td>0.004221</td>\n",
       "      <td>-0.032517</td>\n",
       "      <td>444.11</td>\n",
       "      <td>439.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2022-04-12 04:00:00</td>\n",
       "      <td>-0.047840</td>\n",
       "      <td>0.005282</td>\n",
       "      <td>-0.001260</td>\n",
       "      <td>-0.001905</td>\n",
       "      <td>-0.011813</td>\n",
       "      <td>0.008872</td>\n",
       "      <td>-0.011187</td>\n",
       "      <td>-0.011687</td>\n",
       "      <td>-0.009098</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.004651</td>\n",
       "      <td>-0.000413</td>\n",
       "      <td>-0.014289</td>\n",
       "      <td>0.007479</td>\n",
       "      <td>-0.003776</td>\n",
       "      <td>-0.021101</td>\n",
       "      <td>-0.016490</td>\n",
       "      <td>-0.021862</td>\n",
       "      <td>443.02</td>\n",
       "      <td>438.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>2022-04-13 04:00:00</td>\n",
       "      <td>0.024190</td>\n",
       "      <td>0.060436</td>\n",
       "      <td>-0.005142</td>\n",
       "      <td>0.018225</td>\n",
       "      <td>-0.036667</td>\n",
       "      <td>0.002029</td>\n",
       "      <td>0.013956</td>\n",
       "      <td>0.016332</td>\n",
       "      <td>0.003854</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000692</td>\n",
       "      <td>0.007440</td>\n",
       "      <td>0.023294</td>\n",
       "      <td>0.015922</td>\n",
       "      <td>0.015073</td>\n",
       "      <td>0.009472</td>\n",
       "      <td>0.023338</td>\n",
       "      <td>0.008139</td>\n",
       "      <td>437.96</td>\n",
       "      <td>443.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2022-04-14 04:00:00</td>\n",
       "      <td>-0.015418</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.010498</td>\n",
       "      <td>-0.031296</td>\n",
       "      <td>0.028255</td>\n",
       "      <td>-0.012738</td>\n",
       "      <td>-0.000783</td>\n",
       "      <td>-0.019903</td>\n",
       "      <td>-0.022669</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012216</td>\n",
       "      <td>0.005121</td>\n",
       "      <td>-0.031983</td>\n",
       "      <td>0.006878</td>\n",
       "      <td>-0.016854</td>\n",
       "      <td>-0.032727</td>\n",
       "      <td>-0.012400</td>\n",
       "      <td>-0.013691</td>\n",
       "      <td>443.44</td>\n",
       "      <td>437.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>2022-04-18 04:00:00</td>\n",
       "      <td>-0.020563</td>\n",
       "      <td>-0.016446</td>\n",
       "      <td>0.004002</td>\n",
       "      <td>0.007138</td>\n",
       "      <td>-0.016053</td>\n",
       "      <td>0.009729</td>\n",
       "      <td>-0.015927</td>\n",
       "      <td>-0.007765</td>\n",
       "      <td>-0.015446</td>\n",
       "      <td>...</td>\n",
       "      <td>0.001357</td>\n",
       "      <td>-0.007133</td>\n",
       "      <td>-0.009587</td>\n",
       "      <td>0.006373</td>\n",
       "      <td>-0.003105</td>\n",
       "      <td>0.008389</td>\n",
       "      <td>0.009672</td>\n",
       "      <td>-0.020048</td>\n",
       "      <td>436.73</td>\n",
       "      <td>437.97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>253 rows × 508 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "symbol           timestamp         A       AAL       AAP      AAPL      ABBV  \\\n",
       "0      2021-04-19 04:00:00 -0.006549 -0.019563 -0.005424  0.010110  0.005928   \n",
       "1      2021-04-20 04:00:00 -0.004060 -0.030938 -0.020657 -0.013926  0.008417   \n",
       "2      2021-04-21 04:00:00  0.007149  0.050500  0.027873  0.008614  0.009199   \n",
       "3      2021-04-22 04:00:00  0.003731 -0.069541  0.006533 -0.006850 -0.005423   \n",
       "4      2021-04-23 04:00:00  0.015680  0.041441 -0.006120  0.015652  0.015222   \n",
       "..                     ...       ...       ...       ...       ...       ...   \n",
       "248    2022-04-11 04:00:00 -0.014713  0.028485 -0.021131 -0.017894 -0.029543   \n",
       "249    2022-04-12 04:00:00 -0.047840  0.005282 -0.001260 -0.001905 -0.011813   \n",
       "250    2022-04-13 04:00:00  0.024190  0.060436 -0.005142  0.018225 -0.036667   \n",
       "251    2022-04-14 04:00:00 -0.015418  0.000000 -0.010498 -0.031296  0.028255   \n",
       "252    2022-04-18 04:00:00 -0.020563 -0.016446  0.004002  0.007138 -0.016053   \n",
       "\n",
       "symbol       ABC      ABMD       ABT       ACN  ...       XOM      XRAY  \\\n",
       "0      -0.004167  0.003415  0.007524 -0.001951  ... -0.005634  0.000611   \n",
       "1       0.010059 -0.002388 -0.012020  0.009338  ... -0.016717  0.001989   \n",
       "2       0.001322  0.032889  0.018019  0.003625  ...  0.027523  0.011623   \n",
       "3      -0.004857  0.007363  0.005618 -0.002852  ... -0.011270 -0.016418   \n",
       "4       0.006536  0.015741 -0.002508  0.005341  ...  0.005246  0.011305   \n",
       "..           ...       ...       ...       ...  ...       ...       ...   \n",
       "248    -0.018407 -0.042728 -0.027859 -0.026272  ... -0.023524  0.026032   \n",
       "249     0.008872 -0.011187 -0.011687 -0.009098  ... -0.004651 -0.000413   \n",
       "250     0.002029  0.013956  0.016332  0.003854  ...  0.000692  0.007440   \n",
       "251    -0.012738 -0.000783 -0.019903 -0.022669  ...  0.012216  0.005121   \n",
       "252     0.009729 -0.015927 -0.007765 -0.015446  ...  0.001357 -0.007133   \n",
       "\n",
       "symbol       XYL       YUM       ZBH      ZBRA      ZION       ZTS  SPY Open  \\\n",
       "0      -0.006974 -0.005660  0.003548 -0.007537 -0.004802  0.003582    416.26   \n",
       "1      -0.005638  0.007258  0.007419 -0.011536 -0.050245  0.004704    413.93   \n",
       "2       0.017189 -0.002632  0.014997  0.009901  0.030146  0.003361    411.55   \n",
       "3      -0.008364 -0.003046  0.004531 -0.017499 -0.021140 -0.003771    415.91   \n",
       "4       0.007570 -0.000085  0.003389  0.011777  0.035489  0.009112    412.82   \n",
       "..           ...       ...       ...       ...       ...       ...       ...   \n",
       "248    -0.009245 -0.005213 -0.017041 -0.005064  0.004221 -0.032517    444.11   \n",
       "249    -0.014289  0.007479 -0.003776 -0.021101 -0.016490 -0.021862    443.02   \n",
       "250     0.023294  0.015922  0.015073  0.009472  0.023338  0.008139    437.96   \n",
       "251    -0.031983  0.006878 -0.016854 -0.032727 -0.012400 -0.013691    443.44   \n",
       "252    -0.009587  0.006373 -0.003105  0.008389  0.009672 -0.020048    436.73   \n",
       "\n",
       "symbol  SPY Close  \n",
       "0          415.21  \n",
       "1          412.17  \n",
       "2          416.07  \n",
       "3          412.27  \n",
       "4          416.74  \n",
       "..            ...  \n",
       "248        439.92  \n",
       "249        438.29  \n",
       "250        443.31  \n",
       "251        437.79  \n",
       "252        437.97  \n",
       "\n",
       "[253 rows x 508 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Stocks = pd.concat([Stocks100, Stocks200, Stocks300, Stocks400, Stocks500, Stocks600], axis=0)\n",
    "StocksWide = TalltoWide(Stocks)\n",
    "StocksWide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saves the raw data into a DB\n",
    "StocksWide.to_sql('StocksWide', engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3Y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For 3Y\n",
    "# Read from DB\n",
    "Stocks3Y100 = pd.read_sql_table('stocks3Y_100', engine, parse_dates='timestamp')\n",
    "Stocks3Y200 = pd.read_sql_table('stocks3Y_200', engine, parse_dates='timestamp')\n",
    "Stocks3Y300 = pd.read_sql_table('stocks3Y_300', engine, parse_dates='timestamp')\n",
    "Stocks3Y400 = pd.read_sql_table('stocks3Y_400', engine, parse_dates='timestamp')\n",
    "Stocks3Y500 = pd.read_sql_table('stocks3Y_500', engine, parse_dates='timestamp')\n",
    "Stocks3Y600 = pd.read_sql_table('stocks3Y_600', engine, parse_dates='timestamp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dlpst\\anaconda3\\envs\\dev\\envs\\dev\\lib\\site-packages\\pandas\\core\\frame.py:4913: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n",
      "C:\\Users\\dlpst\\anaconda3\\envs\\dev\\envs\\dev\\lib\\site-packages\\pandas\\core\\frame.py:5047: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n"
     ]
    },
    {
     "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>symbol</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>A</th>\n",
       "      <th>AAL</th>\n",
       "      <th>AAP</th>\n",
       "      <th>AAPL</th>\n",
       "      <th>ABBV</th>\n",
       "      <th>ABC</th>\n",
       "      <th>ABMD</th>\n",
       "      <th>ABT</th>\n",
       "      <th>ACN</th>\n",
       "      <th>...</th>\n",
       "      <th>XOM</th>\n",
       "      <th>XRAY</th>\n",
       "      <th>XYL</th>\n",
       "      <th>YUM</th>\n",
       "      <th>ZBH</th>\n",
       "      <th>ZBRA</th>\n",
       "      <th>ZION</th>\n",
       "      <th>ZTS</th>\n",
       "      <th>SPY Open</th>\n",
       "      <th>SPY Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-04-15 04:00:00</td>\n",
       "      <td>-0.007407</td>\n",
       "      <td>-0.025974</td>\n",
       "      <td>0.004930</td>\n",
       "      <td>0.003526</td>\n",
       "      <td>0.001607</td>\n",
       "      <td>0.013989</td>\n",
       "      <td>0.003131</td>\n",
       "      <td>-0.007174</td>\n",
       "      <td>0.004989</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.001980</td>\n",
       "      <td>-0.004469</td>\n",
       "      <td>-0.003893</td>\n",
       "      <td>0.000394</td>\n",
       "      <td>-0.004127</td>\n",
       "      <td>-0.008318</td>\n",
       "      <td>-0.015292</td>\n",
       "      <td>-0.005890</td>\n",
       "      <td>290.27</td>\n",
       "      <td>289.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-04-16 04:00:00</td>\n",
       "      <td>-0.040460</td>\n",
       "      <td>0.002061</td>\n",
       "      <td>-0.014297</td>\n",
       "      <td>-0.000351</td>\n",
       "      <td>-0.011705</td>\n",
       "      <td>-0.012321</td>\n",
       "      <td>-0.029202</td>\n",
       "      <td>-0.023523</td>\n",
       "      <td>-0.004116</td>\n",
       "      <td>...</td>\n",
       "      <td>0.004702</td>\n",
       "      <td>-0.018890</td>\n",
       "      <td>0.003408</td>\n",
       "      <td>-0.003627</td>\n",
       "      <td>-0.026297</td>\n",
       "      <td>0.007138</td>\n",
       "      <td>0.009623</td>\n",
       "      <td>-0.028602</td>\n",
       "      <td>290.96</td>\n",
       "      <td>290.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-04-17 04:00:00</td>\n",
       "      <td>-0.034805</td>\n",
       "      <td>-0.002032</td>\n",
       "      <td>-0.017449</td>\n",
       "      <td>0.019320</td>\n",
       "      <td>-0.026102</td>\n",
       "      <td>-0.043524</td>\n",
       "      <td>-0.060132</td>\n",
       "      <td>-0.041053</td>\n",
       "      <td>-0.004727</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000123</td>\n",
       "      <td>-0.035792</td>\n",
       "      <td>-0.000966</td>\n",
       "      <td>-0.003730</td>\n",
       "      <td>-0.048872</td>\n",
       "      <td>-0.013509</td>\n",
       "      <td>-0.003092</td>\n",
       "      <td>-0.014385</td>\n",
       "      <td>291.41</td>\n",
       "      <td>289.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-04-18 04:00:00</td>\n",
       "      <td>0.005810</td>\n",
       "      <td>-0.001743</td>\n",
       "      <td>-0.006570</td>\n",
       "      <td>0.002015</td>\n",
       "      <td>-0.005640</td>\n",
       "      <td>0.002075</td>\n",
       "      <td>0.016079</td>\n",
       "      <td>0.005714</td>\n",
       "      <td>-0.000615</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.005516</td>\n",
       "      <td>0.014691</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.015929</td>\n",
       "      <td>0.002841</td>\n",
       "      <td>0.003796</td>\n",
       "      <td>-0.021109</td>\n",
       "      <td>0.007631</td>\n",
       "      <td>290.12</td>\n",
       "      <td>290.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-04-22 04:00:00</td>\n",
       "      <td>-0.004741</td>\n",
       "      <td>-0.018182</td>\n",
       "      <td>-0.025050</td>\n",
       "      <td>0.009227</td>\n",
       "      <td>0.007217</td>\n",
       "      <td>-0.018915</td>\n",
       "      <td>0.020454</td>\n",
       "      <td>0.019010</td>\n",
       "      <td>0.002082</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012581</td>\n",
       "      <td>-0.002428</td>\n",
       "      <td>0.007968</td>\n",
       "      <td>0.001748</td>\n",
       "      <td>0.015841</td>\n",
       "      <td>-0.004319</td>\n",
       "      <td>-0.008070</td>\n",
       "      <td>0.014182</td>\n",
       "      <td>289.17</td>\n",
       "      <td>290.27</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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>753</th>\n",
       "      <td>2022-04-08 04:00:00</td>\n",
       "      <td>-0.006190</td>\n",
       "      <td>-0.016598</td>\n",
       "      <td>0.037742</td>\n",
       "      <td>-0.009665</td>\n",
       "      <td>0.011329</td>\n",
       "      <td>0.011742</td>\n",
       "      <td>-0.002632</td>\n",
       "      <td>-0.001377</td>\n",
       "      <td>-0.010250</td>\n",
       "      <td>...</td>\n",
       "      <td>0.016624</td>\n",
       "      <td>0.000625</td>\n",
       "      <td>-0.003106</td>\n",
       "      <td>0.003790</td>\n",
       "      <td>-0.002386</td>\n",
       "      <td>-0.001513</td>\n",
       "      <td>0.003298</td>\n",
       "      <td>0.011628</td>\n",
       "      <td>448.04</td>\n",
       "      <td>447.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>754</th>\n",
       "      <td>2022-04-11 04:00:00</td>\n",
       "      <td>-0.014713</td>\n",
       "      <td>0.028485</td>\n",
       "      <td>-0.021131</td>\n",
       "      <td>-0.017894</td>\n",
       "      <td>-0.029543</td>\n",
       "      <td>-0.018407</td>\n",
       "      <td>-0.042728</td>\n",
       "      <td>-0.027859</td>\n",
       "      <td>-0.026272</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.023524</td>\n",
       "      <td>0.026032</td>\n",
       "      <td>-0.009245</td>\n",
       "      <td>-0.005213</td>\n",
       "      <td>-0.017041</td>\n",
       "      <td>-0.005064</td>\n",
       "      <td>0.004221</td>\n",
       "      <td>-0.032517</td>\n",
       "      <td>444.11</td>\n",
       "      <td>439.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>755</th>\n",
       "      <td>2022-04-12 04:00:00</td>\n",
       "      <td>-0.047840</td>\n",
       "      <td>0.005282</td>\n",
       "      <td>-0.001260</td>\n",
       "      <td>-0.001905</td>\n",
       "      <td>-0.011813</td>\n",
       "      <td>0.008872</td>\n",
       "      <td>-0.011187</td>\n",
       "      <td>-0.011687</td>\n",
       "      <td>-0.009098</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.004651</td>\n",
       "      <td>-0.000413</td>\n",
       "      <td>-0.014289</td>\n",
       "      <td>0.007479</td>\n",
       "      <td>-0.003776</td>\n",
       "      <td>-0.021101</td>\n",
       "      <td>-0.016490</td>\n",
       "      <td>-0.021862</td>\n",
       "      <td>443.02</td>\n",
       "      <td>438.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>756</th>\n",
       "      <td>2022-04-13 04:00:00</td>\n",
       "      <td>0.024190</td>\n",
       "      <td>0.060436</td>\n",
       "      <td>-0.005142</td>\n",
       "      <td>0.018225</td>\n",
       "      <td>-0.036667</td>\n",
       "      <td>0.002029</td>\n",
       "      <td>0.013956</td>\n",
       "      <td>0.016332</td>\n",
       "      <td>0.003854</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000692</td>\n",
       "      <td>0.007440</td>\n",
       "      <td>0.023294</td>\n",
       "      <td>0.015922</td>\n",
       "      <td>0.015073</td>\n",
       "      <td>0.009472</td>\n",
       "      <td>0.023338</td>\n",
       "      <td>0.008139</td>\n",
       "      <td>437.96</td>\n",
       "      <td>443.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>757</th>\n",
       "      <td>2022-04-14 04:00:00</td>\n",
       "      <td>-0.015418</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.010498</td>\n",
       "      <td>-0.031296</td>\n",
       "      <td>0.028255</td>\n",
       "      <td>-0.012738</td>\n",
       "      <td>-0.000783</td>\n",
       "      <td>-0.019903</td>\n",
       "      <td>-0.022669</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012216</td>\n",
       "      <td>0.005121</td>\n",
       "      <td>-0.031983</td>\n",
       "      <td>0.006878</td>\n",
       "      <td>-0.016854</td>\n",
       "      <td>-0.032727</td>\n",
       "      <td>-0.012400</td>\n",
       "      <td>-0.013691</td>\n",
       "      <td>443.44</td>\n",
       "      <td>437.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>758 rows × 508 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "symbol           timestamp         A       AAL       AAP      AAPL      ABBV  \\\n",
       "0      2019-04-15 04:00:00 -0.007407 -0.025974  0.004930  0.003526  0.001607   \n",
       "1      2019-04-16 04:00:00 -0.040460  0.002061 -0.014297 -0.000351 -0.011705   \n",
       "2      2019-04-17 04:00:00 -0.034805 -0.002032 -0.017449  0.019320 -0.026102   \n",
       "3      2019-04-18 04:00:00  0.005810 -0.001743 -0.006570  0.002015 -0.005640   \n",
       "4      2019-04-22 04:00:00 -0.004741 -0.018182 -0.025050  0.009227  0.007217   \n",
       "..                     ...       ...       ...       ...       ...       ...   \n",
       "753    2022-04-08 04:00:00 -0.006190 -0.016598  0.037742 -0.009665  0.011329   \n",
       "754    2022-04-11 04:00:00 -0.014713  0.028485 -0.021131 -0.017894 -0.029543   \n",
       "755    2022-04-12 04:00:00 -0.047840  0.005282 -0.001260 -0.001905 -0.011813   \n",
       "756    2022-04-13 04:00:00  0.024190  0.060436 -0.005142  0.018225 -0.036667   \n",
       "757    2022-04-14 04:00:00 -0.015418  0.000000 -0.010498 -0.031296  0.028255   \n",
       "\n",
       "symbol       ABC      ABMD       ABT       ACN  ...       XOM      XRAY  \\\n",
       "0       0.013989  0.003131 -0.007174  0.004989  ... -0.001980 -0.004469   \n",
       "1      -0.012321 -0.029202 -0.023523 -0.004116  ...  0.004702 -0.018890   \n",
       "2      -0.043524 -0.060132 -0.041053 -0.004727  ...  0.000123 -0.035792   \n",
       "3       0.002075  0.016079  0.005714 -0.000615  ... -0.005516  0.014691   \n",
       "4      -0.018915  0.020454  0.019010  0.002082  ...  0.012581 -0.002428   \n",
       "..           ...       ...       ...       ...  ...       ...       ...   \n",
       "753     0.011742 -0.002632 -0.001377 -0.010250  ...  0.016624  0.000625   \n",
       "754    -0.018407 -0.042728 -0.027859 -0.026272  ... -0.023524  0.026032   \n",
       "755     0.008872 -0.011187 -0.011687 -0.009098  ... -0.004651 -0.000413   \n",
       "756     0.002029  0.013956  0.016332  0.003854  ...  0.000692  0.007440   \n",
       "757    -0.012738 -0.000783 -0.019903 -0.022669  ...  0.012216  0.005121   \n",
       "\n",
       "symbol       XYL       YUM       ZBH      ZBRA      ZION       ZTS  SPY Open  \\\n",
       "0      -0.003893  0.000394 -0.004127 -0.008318 -0.015292 -0.005890    290.27   \n",
       "1       0.003408 -0.003627 -0.026297  0.007138  0.009623 -0.028602    290.96   \n",
       "2      -0.000966 -0.003730 -0.048872 -0.013509 -0.003092 -0.014385    291.41   \n",
       "3       0.000000  0.015929  0.002841  0.003796 -0.021109  0.007631    290.12   \n",
       "4       0.007968  0.001748  0.015841 -0.004319 -0.008070  0.014182    289.17   \n",
       "..           ...       ...       ...       ...       ...       ...       ...   \n",
       "753    -0.003106  0.003790 -0.002386 -0.001513  0.003298  0.011628    448.04   \n",
       "754    -0.009245 -0.005213 -0.017041 -0.005064  0.004221 -0.032517    444.11   \n",
       "755    -0.014289  0.007479 -0.003776 -0.021101 -0.016490 -0.021862    443.02   \n",
       "756     0.023294  0.015922  0.015073  0.009472  0.023338  0.008139    437.96   \n",
       "757    -0.031983  0.006878 -0.016854 -0.032727 -0.012400 -0.013691    443.44   \n",
       "\n",
       "symbol  SPY Close  \n",
       "0          289.97  \n",
       "1          290.16  \n",
       "2          289.45  \n",
       "3          290.02  \n",
       "4          290.27  \n",
       "..            ...  \n",
       "753        447.57  \n",
       "754        439.92  \n",
       "755        438.29  \n",
       "756        443.31  \n",
       "757        437.79  \n",
       "\n",
       "[758 rows x 508 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Stocks3Y = pd.concat([Stocks3Y100, Stocks3Y200, Stocks3Y300, Stocks3Y400, Stocks3Y500, Stocks3Y600], axis=0)\n",
    "StocksWide3Y = TalltoWide(Stocks3Y)\n",
    "StocksWide3Y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "StocksWide.to_sql('StocksWide3Y', engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5 Day Moving Average"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "c4b0b55e92ab1351e5a1f94286072d021fbab334671496e18a96cada826a13ce"
  },
  "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.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}