2-download-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e362f0a5",
   "metadata": {},
   "source": [
    "yfinance (https://pypi.org/project/yfinance/) is an open-source toll\n",
    "that uses Yahoo's publicly available APIs to download finance data.\n",
    "\n",
    "GOOD FOR >30m resolution, but has LIMITATIONS for others:\n",
    "\n",
    "- Minute Data: 7 days\n",
    "- 2 Minute Data: 60 days\n",
    "- 5 Minute Data: 60 days\n",
    "- 15 Minute Data: 60 days\n",
    "- 30 Minute Data: 60 days\n",
    "- Hourly Data: 730 days\n",
    "- Daily/Weekly/Monthly: No limit\n",
    "\n",
    "valid periods: 1d,5d,1mo,3mo,6mo,1y,2y,5y,10y,ytd,max\n",
    "\n",
    "valid intervals: 1m,2m,5m,15m,30m,60m,90m,1h,1d,5d,1wk,1mo,3mo\n",
    "\n",
    "<!-- EXAMPLE USAGE:\n",
    "\n",
    "tickers = 'MA V'\n",
    "start = '2011-12-30'\n",
    "end = '2022-01-01'\n",
    "data = pd.DataFrame()\n",
    "data = yf.download(tickers, start, end)['Close']\n",
    "data\n",
    "yf.Ticker(\"MA\").calendar # next event\n",
    "yf.Ticker(\"MA\").earnings_dates # historical events\n",
    "yf.Ticker(\"MA\").recommendations # grades\n",
    "yf.Ticker(\"MA\").actions # dividends & splits -->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "a7f77d6c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:02.246116Z",
     "start_time": "2022-11-01T17:35:02.213098Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import yfinance as yf\n",
    "import matplotlib.pyplot as plt\n",
    "from pathlib import Path\n",
    "\n",
    "# define data path\n",
    "Path(\"data\").mkdir(parents=True, exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "729b326e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-17T21:53:29.324281Z",
     "start_time": "2022-10-17T21:53:29.289611Z"
    }
   },
   "source": [
    "### GET TICKER NAMES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "8711d092",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:02.323098Z",
     "start_time": "2022-11-01T17:35:02.254095Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['APO', 'BAC', 'BEN', 'BX', 'C', 'COF', 'KKR', 'RF', 'SCHW', 'STT', 'USB']"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get dataframe from the previous step\n",
    "df = pd.read_pickle(\"pairs_to_download.pkl\")\n",
    "\n",
    "tickerStrings = list(df.index.union(df.columns))\n",
    "tickerStrings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bba079b",
   "metadata": {},
   "source": [
    "### DOWNLOAD & CREATE CSV FILE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2b14cdb",
   "metadata": {},
   "source": [
    "#### SETUP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "dbc1e8a1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:02.338095Z",
     "start_time": "2022-11-01T17:35:02.329097Z"
    }
   },
   "outputs": [],
   "source": [
    "int_per = {'1d':'1y', '1h':'1y'}  # define interval and corresponding period"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e2f2d8c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-17T22:26:38.207930Z",
     "start_time": "2022-10-17T22:26:38.202927Z"
    }
   },
   "source": [
    "#### OPTION 1 (DOWNLOAD  & CREATE A FILE FOR EACH TICKER PER INTERVAL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "94c044ed",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:08.931868Z",
     "start_time": "2022-11-01T17:35:02.347096Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n"
     ]
    }
   ],
   "source": [
    "# int_per = {'1d':'3mo', '1h':'3mo'}  # define interval and corresponding period\n",
    "\n",
    "\n",
    "# enable to enter manually\n",
    "#tickerStrings = ['MA', 'V', 'LNT', 'FTS', 'POR', 'CMS', 'OUT', 'WELL']\n",
    "\n",
    "for ticker in tickerStrings:\n",
    "    for key in int_per:\n",
    "        data = yf.download(ticker, group_by=\"Ticker\", period=int_per[key], interval=key)\n",
    "        data['ticker'] = ticker\n",
    "        data.set_index(data.columns[0]) # datetime row comes with different names or unnamed\n",
    "        data.index.names = ['time']\n",
    "    \n",
    "        # use in need of sorting and renaming\n",
    "        #data = data.set_index([\"time\"]).sort_index()\n",
    "        #data = data.rename(columns={\"Date\": \"time\"})\n",
    "\n",
    "        # save as seperate files\n",
    "        data.to_csv(f'data/{ticker}_{key.upper()}.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a27e8595",
   "metadata": {},
   "source": [
    "Check if downloaded correctly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "a4e2b7a0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:09.473572Z",
     "start_time": "2022-11-01T17:35:08.946869Z"
    },
    "scrolled": true
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 13:30:00-04:00</td>\n",
       "      <td>76.324997</td>\n",
       "      <td>76.639999</td>\n",
       "      <td>76.139999</td>\n",
       "      <td>76.290001</td>\n",
       "      <td>76.290001</td>\n",
       "      <td>1735242</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-01 14:30:00-04:00</td>\n",
       "      <td>76.330002</td>\n",
       "      <td>76.470001</td>\n",
       "      <td>75.879997</td>\n",
       "      <td>75.974998</td>\n",
       "      <td>75.974998</td>\n",
       "      <td>756397</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-01 15:30:00-04:00</td>\n",
       "      <td>76.000000</td>\n",
       "      <td>76.519997</td>\n",
       "      <td>75.879997</td>\n",
       "      <td>76.349998</td>\n",
       "      <td>76.349998</td>\n",
       "      <td>1640415</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-02 09:30:00-04:00</td>\n",
       "      <td>76.114403</td>\n",
       "      <td>76.309998</td>\n",
       "      <td>74.239998</td>\n",
       "      <td>75.169998</td>\n",
       "      <td>75.169998</td>\n",
       "      <td>970010</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-02 10:30:00-04:00</td>\n",
       "      <td>74.860001</td>\n",
       "      <td>76.149902</td>\n",
       "      <td>74.790001</td>\n",
       "      <td>75.669998</td>\n",
       "      <td>75.669998</td>\n",
       "      <td>757594</td>\n",
       "      <td>APO</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1759</th>\n",
       "      <td>2022-11-01 10:30:00-04:00</td>\n",
       "      <td>57.000000</td>\n",
       "      <td>57.250000</td>\n",
       "      <td>56.630001</td>\n",
       "      <td>57.209999</td>\n",
       "      <td>57.209999</td>\n",
       "      <td>453351</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1760</th>\n",
       "      <td>2022-11-01 11:30:00-04:00</td>\n",
       "      <td>57.224998</td>\n",
       "      <td>57.349998</td>\n",
       "      <td>56.639999</td>\n",
       "      <td>57.029999</td>\n",
       "      <td>57.029999</td>\n",
       "      <td>293098</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1761</th>\n",
       "      <td>2022-11-01 12:30:00-04:00</td>\n",
       "      <td>57.020000</td>\n",
       "      <td>57.330002</td>\n",
       "      <td>56.939999</td>\n",
       "      <td>57.139999</td>\n",
       "      <td>57.139999</td>\n",
       "      <td>187120</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1762</th>\n",
       "      <td>2022-11-01 13:30:00-04:00</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.169998</td>\n",
       "      <td>57.119999</td>\n",
       "      <td>57.150002</td>\n",
       "      <td>57.150002</td>\n",
       "      <td>12680</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1763</th>\n",
       "      <td>2022-11-01 13:34:30-04:00</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>0</td>\n",
       "      <td>APO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1764 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           time       Open       High        Low      Close  \\\n",
       "0     2021-11-01 13:30:00-04:00  76.324997  76.639999  76.139999  76.290001   \n",
       "1     2021-11-01 14:30:00-04:00  76.330002  76.470001  75.879997  75.974998   \n",
       "2     2021-11-01 15:30:00-04:00  76.000000  76.519997  75.879997  76.349998   \n",
       "3     2021-11-02 09:30:00-04:00  76.114403  76.309998  74.239998  75.169998   \n",
       "4     2021-11-02 10:30:00-04:00  74.860001  76.149902  74.790001  75.669998   \n",
       "...                         ...        ...        ...        ...        ...   \n",
       "1759  2022-11-01 10:30:00-04:00  57.000000  57.250000  56.630001  57.209999   \n",
       "1760  2022-11-01 11:30:00-04:00  57.224998  57.349998  56.639999  57.029999   \n",
       "1761  2022-11-01 12:30:00-04:00  57.020000  57.330002  56.939999  57.139999   \n",
       "1762  2022-11-01 13:30:00-04:00  57.130001  57.169998  57.119999  57.150002   \n",
       "1763  2022-11-01 13:34:30-04:00  57.130001  57.130001  57.130001  57.130001   \n",
       "\n",
       "      Adj Close   Volume ticker  \n",
       "0     76.290001  1735242    APO  \n",
       "1     75.974998   756397    APO  \n",
       "2     76.349998  1640415    APO  \n",
       "3     75.169998   970010    APO  \n",
       "4     75.669998   757594    APO  \n",
       "...         ...      ...    ...  \n",
       "1759  57.209999   453351    APO  \n",
       "1760  57.029999   293098    APO  \n",
       "1761  57.139999   187120    APO  \n",
       "1762  57.150002    12680    APO  \n",
       "1763  57.130001        0    APO  \n",
       "\n",
       "[1764 rows x 8 columns]"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = 'data/' +  tickerStrings[0] + '_1H.csv'\n",
    "df = pd.read_csv(filename, parse_dates=[\"time\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7371058",
   "metadata": {},
   "source": [
    "#### OPTION 2 (DOWNLOAD  & CREATE SINGLE DF FROM ALL TICKERS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "85c6649e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:16.663127Z",
     "start_time": "2022-11-01T17:35:09.478584Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n",
      "[*********************100%***********************]  1 of 1 completed\n"
     ]
    }
   ],
   "source": [
    "# int_per = {'1d':'1y', '1h':'1y'}  # define interval and corresponding period\n",
    "\n",
    "df_list = list()\n",
    "\n",
    "for key in int_per:\n",
    "    for ticker in tickerStrings:\n",
    "        data = yf.download(ticker, group_by=\"Ticker\", period=int_per[key], interval=key)\n",
    "        data['ticker'] = ticker\n",
    "        data.index.names = ['time']\n",
    "        df_list.append(data)\n",
    "\n",
    "    # combine all dataframes into a single dataframe\n",
    "    df = pd.concat(df_list)\n",
    "\n",
    "    # save to csv\n",
    "    df.to_csv('data/tickers_'+key.upper()+'.csv')\n",
    "    \n",
    "    df_list = []"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6896225d",
   "metadata": {},
   "source": [
    "Check if downloaded correctly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "be626cf0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:27.125774Z",
     "start_time": "2022-11-01T17:35:16.665123Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "filename = 'data/tickers_1H.csv'\n",
    "df = pd.read_csv(filename, parse_dates=[\"time\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "3703a323",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:27.341545Z",
     "start_time": "2022-11-01T17:35:27.130415Z"
    }
   },
   "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>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</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-11-01 13:30:00-04:00</th>\n",
       "      <td>76.324997</td>\n",
       "      <td>76.639999</td>\n",
       "      <td>76.139999</td>\n",
       "      <td>76.290001</td>\n",
       "      <td>76.290001</td>\n",
       "      <td>1735242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-01 14:30:00-04:00</th>\n",
       "      <td>76.330002</td>\n",
       "      <td>76.470001</td>\n",
       "      <td>75.879997</td>\n",
       "      <td>75.974998</td>\n",
       "      <td>75.974998</td>\n",
       "      <td>756397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-01 15:30:00-04:00</th>\n",
       "      <td>76.000000</td>\n",
       "      <td>76.519997</td>\n",
       "      <td>75.879997</td>\n",
       "      <td>76.349998</td>\n",
       "      <td>76.349998</td>\n",
       "      <td>1640415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-02 09:30:00-04:00</th>\n",
       "      <td>76.114403</td>\n",
       "      <td>76.309998</td>\n",
       "      <td>74.239998</td>\n",
       "      <td>75.169998</td>\n",
       "      <td>75.169998</td>\n",
       "      <td>970010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-02 10:30:00-04:00</th>\n",
       "      <td>74.860001</td>\n",
       "      <td>76.149902</td>\n",
       "      <td>74.790001</td>\n",
       "      <td>75.669998</td>\n",
       "      <td>75.669998</td>\n",
       "      <td>757594</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 10:30:00-04:00</th>\n",
       "      <td>57.000000</td>\n",
       "      <td>57.250000</td>\n",
       "      <td>56.630001</td>\n",
       "      <td>57.209999</td>\n",
       "      <td>57.209999</td>\n",
       "      <td>453351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 11:30:00-04:00</th>\n",
       "      <td>57.224998</td>\n",
       "      <td>57.349998</td>\n",
       "      <td>56.639999</td>\n",
       "      <td>57.029999</td>\n",
       "      <td>57.029999</td>\n",
       "      <td>293098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 12:30:00-04:00</th>\n",
       "      <td>57.020000</td>\n",
       "      <td>57.330002</td>\n",
       "      <td>56.939999</td>\n",
       "      <td>57.139999</td>\n",
       "      <td>57.139999</td>\n",
       "      <td>187765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 13:30:00-04:00</th>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.169998</td>\n",
       "      <td>57.119999</td>\n",
       "      <td>57.150002</td>\n",
       "      <td>57.150002</td>\n",
       "      <td>12680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 13:34:30-04:00</th>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>57.130001</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1764 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Open       High        Low      Close  \\\n",
       "time                                                                    \n",
       "2021-11-01 13:30:00-04:00  76.324997  76.639999  76.139999  76.290001   \n",
       "2021-11-01 14:30:00-04:00  76.330002  76.470001  75.879997  75.974998   \n",
       "2021-11-01 15:30:00-04:00  76.000000  76.519997  75.879997  76.349998   \n",
       "2021-11-02 09:30:00-04:00  76.114403  76.309998  74.239998  75.169998   \n",
       "2021-11-02 10:30:00-04:00  74.860001  76.149902  74.790001  75.669998   \n",
       "...                              ...        ...        ...        ...   \n",
       "2022-11-01 10:30:00-04:00  57.000000  57.250000  56.630001  57.209999   \n",
       "2022-11-01 11:30:00-04:00  57.224998  57.349998  56.639999  57.029999   \n",
       "2022-11-01 12:30:00-04:00  57.020000  57.330002  56.939999  57.139999   \n",
       "2022-11-01 13:30:00-04:00  57.130001  57.169998  57.119999  57.150002   \n",
       "2022-11-01 13:34:30-04:00  57.130001  57.130001  57.130001  57.130001   \n",
       "\n",
       "                           Adj Close   Volume  \n",
       "time                                           \n",
       "2021-11-01 13:30:00-04:00  76.290001  1735242  \n",
       "2021-11-01 14:30:00-04:00  75.974998   756397  \n",
       "2021-11-01 15:30:00-04:00  76.349998  1640415  \n",
       "2021-11-02 09:30:00-04:00  75.169998   970010  \n",
       "2021-11-02 10:30:00-04:00  75.669998   757594  \n",
       "...                              ...      ...  \n",
       "2022-11-01 10:30:00-04:00  57.209999   453351  \n",
       "2022-11-01 11:30:00-04:00  57.029999   293098  \n",
       "2022-11-01 12:30:00-04:00  57.139999   187765  \n",
       "2022-11-01 13:30:00-04:00  57.150002    12680  \n",
       "2022-11-01 13:34:30-04:00  57.130001        0  \n",
       "\n",
       "[1764 rows x 6 columns]"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_c = df.set_index([\"ticker\", \"time\"]).sort_index() # set indexes\n",
    "df_c\n",
    "df_c.xs(tickerStrings[0]) # check the first ticker"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be8faba5",
   "metadata": {},
   "source": [
    "#### EXERCISE (DOWNLOAD MULTIPLE TICKERS AND FLATTEN THE LEVELS )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "31d68082",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:27.847275Z",
     "start_time": "2022-11-01T17:35:27.348545Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*********************100%***********************]  2 of 2 completed\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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"5\" halign=\"left\">WELL</th>\n",
       "      <th colspan=\"5\" halign=\"left\">OUT</th>\n",
       "    </tr>\n",
       "    <tr>\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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Datetime</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-03 09:30:00-04:00</th>\n",
       "      <td>65.099998</td>\n",
       "      <td>65.300003</td>\n",
       "      <td>63.849998</td>\n",
       "      <td>64.169998</td>\n",
       "      <td>256361</td>\n",
       "      <td>15.520000</td>\n",
       "      <td>15.540000</td>\n",
       "      <td>15.010000</td>\n",
       "      <td>15.100000</td>\n",
       "      <td>70525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-03 10:00:00-04:00</th>\n",
       "      <td>64.220001</td>\n",
       "      <td>65.029999</td>\n",
       "      <td>64.180000</td>\n",
       "      <td>64.995003</td>\n",
       "      <td>222513</td>\n",
       "      <td>15.140000</td>\n",
       "      <td>15.470000</td>\n",
       "      <td>15.120000</td>\n",
       "      <td>15.450000</td>\n",
       "      <td>83731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-03 10:30:00-04:00</th>\n",
       "      <td>65.029999</td>\n",
       "      <td>65.309998</td>\n",
       "      <td>64.750000</td>\n",
       "      <td>65.260002</td>\n",
       "      <td>226219</td>\n",
       "      <td>15.460000</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>15.375000</td>\n",
       "      <td>15.410000</td>\n",
       "      <td>53183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-03 11:00:00-04:00</th>\n",
       "      <td>65.250000</td>\n",
       "      <td>65.500000</td>\n",
       "      <td>65.110001</td>\n",
       "      <td>65.160004</td>\n",
       "      <td>151454</td>\n",
       "      <td>15.410000</td>\n",
       "      <td>15.565000</td>\n",
       "      <td>15.410000</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>51303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-03 11:30:00-04:00</th>\n",
       "      <td>65.199997</td>\n",
       "      <td>65.285004</td>\n",
       "      <td>64.750000</td>\n",
       "      <td>64.820000</td>\n",
       "      <td>102661</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>15.520000</td>\n",
       "      <td>15.310000</td>\n",
       "      <td>15.350000</td>\n",
       "      <td>48560</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>2022-11-01 11:30:00-04:00</th>\n",
       "      <td>60.430000</td>\n",
       "      <td>60.430000</td>\n",
       "      <td>60.090000</td>\n",
       "      <td>60.290001</td>\n",
       "      <td>60996</td>\n",
       "      <td>18.320000</td>\n",
       "      <td>18.379999</td>\n",
       "      <td>18.240000</td>\n",
       "      <td>18.375000</td>\n",
       "      <td>53978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 12:00:00-04:00</th>\n",
       "      <td>60.259998</td>\n",
       "      <td>60.470001</td>\n",
       "      <td>60.029999</td>\n",
       "      <td>60.029999</td>\n",
       "      <td>99617</td>\n",
       "      <td>18.370001</td>\n",
       "      <td>18.490000</td>\n",
       "      <td>18.350000</td>\n",
       "      <td>18.405001</td>\n",
       "      <td>74688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 12:30:00-04:00</th>\n",
       "      <td>60.025002</td>\n",
       "      <td>60.040001</td>\n",
       "      <td>59.860001</td>\n",
       "      <td>59.990002</td>\n",
       "      <td>44902</td>\n",
       "      <td>18.410000</td>\n",
       "      <td>18.480000</td>\n",
       "      <td>18.375000</td>\n",
       "      <td>18.459999</td>\n",
       "      <td>57331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 13:00:00-04:00</th>\n",
       "      <td>59.984699</td>\n",
       "      <td>60.299999</td>\n",
       "      <td>59.939999</td>\n",
       "      <td>60.160000</td>\n",
       "      <td>71972</td>\n",
       "      <td>18.459999</td>\n",
       "      <td>18.600000</td>\n",
       "      <td>18.450001</td>\n",
       "      <td>18.480000</td>\n",
       "      <td>79032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-01 13:30:00-04:00</th>\n",
       "      <td>60.139999</td>\n",
       "      <td>60.150002</td>\n",
       "      <td>60.099998</td>\n",
       "      <td>60.099998</td>\n",
       "      <td>8256</td>\n",
       "      <td>18.469999</td>\n",
       "      <td>18.490000</td>\n",
       "      <td>18.450001</td>\n",
       "      <td>18.465000</td>\n",
       "      <td>19171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>282 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                WELL                                           \\\n",
       "                                Open       High        Low      Close  Volume   \n",
       "Datetime                                                                        \n",
       "2022-10-03 09:30:00-04:00  65.099998  65.300003  63.849998  64.169998  256361   \n",
       "2022-10-03 10:00:00-04:00  64.220001  65.029999  64.180000  64.995003  222513   \n",
       "2022-10-03 10:30:00-04:00  65.029999  65.309998  64.750000  65.260002  226219   \n",
       "2022-10-03 11:00:00-04:00  65.250000  65.500000  65.110001  65.160004  151454   \n",
       "2022-10-03 11:30:00-04:00  65.199997  65.285004  64.750000  64.820000  102661   \n",
       "...                              ...        ...        ...        ...     ...   \n",
       "2022-11-01 11:30:00-04:00  60.430000  60.430000  60.090000  60.290001   60996   \n",
       "2022-11-01 12:00:00-04:00  60.259998  60.470001  60.029999  60.029999   99617   \n",
       "2022-11-01 12:30:00-04:00  60.025002  60.040001  59.860001  59.990002   44902   \n",
       "2022-11-01 13:00:00-04:00  59.984699  60.299999  59.939999  60.160000   71972   \n",
       "2022-11-01 13:30:00-04:00  60.139999  60.150002  60.099998  60.099998    8256   \n",
       "\n",
       "                                 OUT                                          \n",
       "                                Open       High        Low      Close Volume  \n",
       "Datetime                                                                      \n",
       "2022-10-03 09:30:00-04:00  15.520000  15.540000  15.010000  15.100000  70525  \n",
       "2022-10-03 10:00:00-04:00  15.140000  15.470000  15.120000  15.450000  83731  \n",
       "2022-10-03 10:30:00-04:00  15.460000  15.500000  15.375000  15.410000  53183  \n",
       "2022-10-03 11:00:00-04:00  15.410000  15.565000  15.410000  15.500000  51303  \n",
       "2022-10-03 11:30:00-04:00  15.500000  15.520000  15.310000  15.350000  48560  \n",
       "...                              ...        ...        ...        ...    ...  \n",
       "2022-11-01 11:30:00-04:00  18.320000  18.379999  18.240000  18.375000  53978  \n",
       "2022-11-01 12:00:00-04:00  18.370001  18.490000  18.350000  18.405001  74688  \n",
       "2022-11-01 12:30:00-04:00  18.410000  18.480000  18.375000  18.459999  57331  \n",
       "2022-11-01 13:00:00-04:00  18.459999  18.600000  18.450001  18.480000  79032  \n",
       "2022-11-01 13:30:00-04:00  18.469999  18.490000  18.450001  18.465000  19171  \n",
       "\n",
       "[282 rows x 10 columns]"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = yf.download(  # or pdr.get_data_yahoo(...\n",
    "        # tickers list or string as well\n",
    "        tickers = \"OUT WELL\",\n",
    "\n",
    "        # use \"period\" instead of start/end\n",
    "        # valid periods: 1d,5d,1mo,3mo,6mo,1y,2y,5y,10y,ytd,max\n",
    "        # (optional, default is '1mo')\n",
    "        period = \"1mo\",\n",
    "\n",
    "        # fetch data by interval (including intraday if period < 60 days)\n",
    "        # valid intervals: 1m,2m,5m,15m,30m,60m,90m,1h,1d,5d,1wk,1mo,3mo\n",
    "        # (optional, default is '1d')\n",
    "        interval = \"30m\",\n",
    "\n",
    "        # group by ticker (to access via data['SPY'])\n",
    "        # (optional, default is 'column')\n",
    "        group_by = 'ticker',\n",
    "\n",
    "        # adjust all OHLC automatically\n",
    "        # (optional, default is False)\n",
    "        auto_adjust = True,\n",
    "\n",
    "        # download pre/post regular market hours data\n",
    "        # (optional, default is False)\n",
    "        prepost = False,\n",
    "\n",
    "        # use threads for mass downloading? (True/False/Integer)\n",
    "        # (optional, default is True)\n",
    "        threads = True,\n",
    "\n",
    "        # proxy URL scheme use use when downloading?\n",
    "        # (optional, default is None)\n",
    "        proxy = None\n",
    "    )\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e335b24",
   "metadata": {},
   "source": [
    "to flatten the MultiIndex use map with join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "be1f61c0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:28.054805Z",
     "start_time": "2022-11-01T17:35:27.853296Z"
    },
    "scrolled": false
   },
   "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>Datetime</th>\n",
       "      <th>WELL_Open</th>\n",
       "      <th>WELL_High</th>\n",
       "      <th>WELL_Low</th>\n",
       "      <th>WELL_Close</th>\n",
       "      <th>WELL_Volume</th>\n",
       "      <th>OUT_Open</th>\n",
       "      <th>OUT_High</th>\n",
       "      <th>OUT_Low</th>\n",
       "      <th>OUT_Close</th>\n",
       "      <th>OUT_Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-03 09:30:00-04:00</td>\n",
       "      <td>65.099998</td>\n",
       "      <td>65.300003</td>\n",
       "      <td>63.849998</td>\n",
       "      <td>64.169998</td>\n",
       "      <td>256361</td>\n",
       "      <td>15.520000</td>\n",
       "      <td>15.540000</td>\n",
       "      <td>15.010000</td>\n",
       "      <td>15.100000</td>\n",
       "      <td>70525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-03 10:00:00-04:00</td>\n",
       "      <td>64.220001</td>\n",
       "      <td>65.029999</td>\n",
       "      <td>64.180000</td>\n",
       "      <td>64.995003</td>\n",
       "      <td>222513</td>\n",
       "      <td>15.140000</td>\n",
       "      <td>15.470000</td>\n",
       "      <td>15.120000</td>\n",
       "      <td>15.450000</td>\n",
       "      <td>83731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-03 10:30:00-04:00</td>\n",
       "      <td>65.029999</td>\n",
       "      <td>65.309998</td>\n",
       "      <td>64.750000</td>\n",
       "      <td>65.260002</td>\n",
       "      <td>226219</td>\n",
       "      <td>15.460000</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>15.375000</td>\n",
       "      <td>15.410000</td>\n",
       "      <td>53183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-03 11:00:00-04:00</td>\n",
       "      <td>65.250000</td>\n",
       "      <td>65.500000</td>\n",
       "      <td>65.110001</td>\n",
       "      <td>65.160004</td>\n",
       "      <td>151454</td>\n",
       "      <td>15.410000</td>\n",
       "      <td>15.565000</td>\n",
       "      <td>15.410000</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>51303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-03 11:30:00-04:00</td>\n",
       "      <td>65.199997</td>\n",
       "      <td>65.285004</td>\n",
       "      <td>64.750000</td>\n",
       "      <td>64.820000</td>\n",
       "      <td>102661</td>\n",
       "      <td>15.500000</td>\n",
       "      <td>15.520000</td>\n",
       "      <td>15.310000</td>\n",
       "      <td>15.350000</td>\n",
       "      <td>48560</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>2022-11-01 11:30:00-04:00</td>\n",
       "      <td>60.430000</td>\n",
       "      <td>60.430000</td>\n",
       "      <td>60.090000</td>\n",
       "      <td>60.290001</td>\n",
       "      <td>60996</td>\n",
       "      <td>18.320000</td>\n",
       "      <td>18.379999</td>\n",
       "      <td>18.240000</td>\n",
       "      <td>18.375000</td>\n",
       "      <td>53978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>2022-11-01 12:00:00-04:00</td>\n",
       "      <td>60.259998</td>\n",
       "      <td>60.470001</td>\n",
       "      <td>60.029999</td>\n",
       "      <td>60.029999</td>\n",
       "      <td>99617</td>\n",
       "      <td>18.370001</td>\n",
       "      <td>18.490000</td>\n",
       "      <td>18.350000</td>\n",
       "      <td>18.405001</td>\n",
       "      <td>74688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>2022-11-01 12:30:00-04:00</td>\n",
       "      <td>60.025002</td>\n",
       "      <td>60.040001</td>\n",
       "      <td>59.860001</td>\n",
       "      <td>59.990002</td>\n",
       "      <td>44902</td>\n",
       "      <td>18.410000</td>\n",
       "      <td>18.480000</td>\n",
       "      <td>18.375000</td>\n",
       "      <td>18.459999</td>\n",
       "      <td>57331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>2022-11-01 13:00:00-04:00</td>\n",
       "      <td>59.984699</td>\n",
       "      <td>60.299999</td>\n",
       "      <td>59.939999</td>\n",
       "      <td>60.160000</td>\n",
       "      <td>71972</td>\n",
       "      <td>18.459999</td>\n",
       "      <td>18.600000</td>\n",
       "      <td>18.450001</td>\n",
       "      <td>18.480000</td>\n",
       "      <td>79032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>2022-11-01 13:30:00-04:00</td>\n",
       "      <td>60.139999</td>\n",
       "      <td>60.150002</td>\n",
       "      <td>60.099998</td>\n",
       "      <td>60.099998</td>\n",
       "      <td>8256</td>\n",
       "      <td>18.469999</td>\n",
       "      <td>18.490000</td>\n",
       "      <td>18.450001</td>\n",
       "      <td>18.465000</td>\n",
       "      <td>19171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>282 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Datetime  WELL_Open  WELL_High   WELL_Low  WELL_Close  \\\n",
       "0   2022-10-03 09:30:00-04:00  65.099998  65.300003  63.849998   64.169998   \n",
       "1   2022-10-03 10:00:00-04:00  64.220001  65.029999  64.180000   64.995003   \n",
       "2   2022-10-03 10:30:00-04:00  65.029999  65.309998  64.750000   65.260002   \n",
       "3   2022-10-03 11:00:00-04:00  65.250000  65.500000  65.110001   65.160004   \n",
       "4   2022-10-03 11:30:00-04:00  65.199997  65.285004  64.750000   64.820000   \n",
       "..                        ...        ...        ...        ...         ...   \n",
       "277 2022-11-01 11:30:00-04:00  60.430000  60.430000  60.090000   60.290001   \n",
       "278 2022-11-01 12:00:00-04:00  60.259998  60.470001  60.029999   60.029999   \n",
       "279 2022-11-01 12:30:00-04:00  60.025002  60.040001  59.860001   59.990002   \n",
       "280 2022-11-01 13:00:00-04:00  59.984699  60.299999  59.939999   60.160000   \n",
       "281 2022-11-01 13:30:00-04:00  60.139999  60.150002  60.099998   60.099998   \n",
       "\n",
       "     WELL_Volume   OUT_Open   OUT_High    OUT_Low  OUT_Close  OUT_Volume  \n",
       "0         256361  15.520000  15.540000  15.010000  15.100000       70525  \n",
       "1         222513  15.140000  15.470000  15.120000  15.450000       83731  \n",
       "2         226219  15.460000  15.500000  15.375000  15.410000       53183  \n",
       "3         151454  15.410000  15.565000  15.410000  15.500000       51303  \n",
       "4         102661  15.500000  15.520000  15.310000  15.350000       48560  \n",
       "..           ...        ...        ...        ...        ...         ...  \n",
       "277        60996  18.320000  18.379999  18.240000  18.375000       53978  \n",
       "278        99617  18.370001  18.490000  18.350000  18.405001       74688  \n",
       "279        44902  18.410000  18.480000  18.375000  18.459999       57331  \n",
       "280        71972  18.459999  18.600000  18.450001  18.480000       79032  \n",
       "281         8256  18.469999  18.490000  18.450001  18.465000       19171  \n",
       "\n",
       "[282 rows x 11 columns]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_flat = data.copy()\n",
    "data_flat.columns = data_flat.columns.map('_'.join)\n",
    "data_flat =data_flat.reset_index()\n",
    "data_flat"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b4a1540",
   "metadata": {},
   "source": [
    "or use index values to get the data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1072ae7a",
   "metadata": {},
   "source": [
    "save as a file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "7a7ba15a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:35:28.951954Z",
     "start_time": "2022-11-01T17:35:28.137065Z"
    }
   },
   "outputs": [],
   "source": [
    "multiindex = data.columns\n",
    "ticker_list = set([item[0] for item in multiindex])\n",
    "\n",
    "for ticker in ticker_list:\n",
    "    data_i = data[(ticker, 'Close')].reset_index().droplevel(level=0, axis=1)\n",
    "    data_i = data_i.rename(columns={ data_i.columns[0]: \"time\" })\n",
    "    data_i = data_i.set_index([\"time\"]).sort_index()\n",
    "    #save as seperate files\n",
    "    data_i.to_csv(f'data/ticker_{ticker}.csv')"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}