data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/pandas_datareader/compat/__init__.py:7: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.\n",
      "  from pandas.util.testing import assert_frame_equal\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "from pandas_datareader import data as wb\n",
    "from datetime import datetime, timedelta\n",
    "import json\n",
    "import requests\n",
    "import random\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Symbol</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MMM</td>\n",
       "      <td>3M Company</td>\n",
       "      <td>Industrials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AOS</td>\n",
       "      <td>A.O. Smith Corp</td>\n",
       "      <td>Industrials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABT</td>\n",
       "      <td>Abbott Laboratories</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABBV</td>\n",
       "      <td>AbbVie Inc.</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACN</td>\n",
       "      <td>Accenture plc</td>\n",
       "      <td>Information Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>XYL</td>\n",
       "      <td>Xylem Inc.</td>\n",
       "      <td>Industrials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>YUM</td>\n",
       "      <td>Yum! Brands Inc</td>\n",
       "      <td>Consumer Discretionary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>ZBH</td>\n",
       "      <td>Zimmer Biomet Holdings</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>ZION</td>\n",
       "      <td>Zions Bancorp</td>\n",
       "      <td>Financials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>ZTS</td>\n",
       "      <td>Zoetis</td>\n",
       "      <td>Health Care</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>505 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Symbol                    Name                  Sector\n",
       "0      MMM              3M Company             Industrials\n",
       "1      AOS         A.O. Smith Corp             Industrials\n",
       "2      ABT     Abbott Laboratories             Health Care\n",
       "3     ABBV             AbbVie Inc.             Health Care\n",
       "4      ACN           Accenture plc  Information Technology\n",
       "..     ...                     ...                     ...\n",
       "500    XYL              Xylem Inc.             Industrials\n",
       "501    YUM         Yum! Brands Inc  Consumer Discretionary\n",
       "502    ZBH  Zimmer Biomet Holdings             Health Care\n",
       "503   ZION           Zions Bancorp              Financials\n",
       "504    ZTS                  Zoetis             Health Care\n",
       "\n",
       "[505 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_path = Path('S&P500.csv')\n",
    "sp500 = pd.read_csv(csv_path)\n",
    "sp500"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Symbol</th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Consumer Discretionary</th>\n",
       "      <td>84</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consumer Staples</th>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Energy</th>\n",
       "      <td>32</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Financials</th>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care</th>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Industrials</th>\n",
       "      <td>67</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information Technology</th>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Materials</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate</th>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Telecommunication Services</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Symbol  Name\n",
       "Sector                                  \n",
       "Consumer Discretionary          84    84\n",
       "Consumer Staples                34    34\n",
       "Energy                          32    32\n",
       "Financials                      68    68\n",
       "Health Care                     61    61\n",
       "Industrials                     67    67\n",
       "Information Technology          70    70\n",
       "Materials                       25    25\n",
       "Real Estate                     33    33\n",
       "Telecommunication Services       3     3\n",
       "Utilities                       28    28"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_sectors = sp500.groupby(['Sector']).count()\n",
    "grouped_sectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = sp500.index.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "index.sort()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = ['A', 'AAL', 'AAP', 'AAPL', 'ABBV', 'ABC', 'ABT', 'ACN', 'ADBE', 'ADI', 'ADM', 'ADP', 'ADS', 'ADSK', 'AEE', 'AEP', 'AES', 'AET', \n",
    "         'AFL', 'AGN', 'AIG', 'AIV', 'AIZ', 'AJG', 'AKAM', 'ALB', 'ALGN', 'ALK', 'ALL', 'ALLE', 'ALXN', 'AMAT', 'AMD', 'AME', 'AMG', \n",
    "         'AMGN', 'AMP', 'AMT', 'AMZN', 'ANDV', 'ANSS', 'ANTM', 'AON', 'AOS', 'APA', 'APD', 'APH', 'APTV', 'ARE', 'ARNC', 'ATVI',\n",
    "         'AVB', 'AVGO', 'AVY', 'AWK', 'AXP', 'AYI', 'AZO', 'BA', 'BAC', 'BAX', 'BBT', 'BBY', 'BDX', 'BEN', 'BF.B', 'BHF', \n",
    "         'BHGE', 'BIIB', 'BK', 'BLK', 'BLL', 'BMY','BRK.B', 'BSX', 'BWA', 'BXP', 'C', 'CA', 'CAG', 'CAH', 'CAT', 'CB', 'CBG', 'CBOE', \n",
    "         'CBS', 'CCI', 'CCL', 'CDNS', 'CELG', 'CERN', 'CF', 'CFG', 'CHD', 'CHK', 'CHRW', 'CHTR', 'CI', 'CINF', 'CL', 'CLX', 'CMA',\n",
    "         'CMCSA', 'CME', 'CMG', 'CMI', 'CMS', 'CNC', 'CNP', 'COF', 'COG', 'COL', 'COO', 'COP', 'COST', 'COTY', 'CPB', 'CRM', 'CSCO',\n",
    "         'CSRA', 'CSX', 'CTAS', 'CTL', 'CTSH', 'CTXS', 'CVS', 'CVX', 'CXO', 'D', 'DAL', 'DE', 'DFS', 'DG', 'DGX', 'DHI', 'DHR',\n",
    "         'DIS', 'DISCA', 'DISCK', 'DISH', 'DLR', 'DLTR', 'DOV', 'DPS', 'DRE', 'DRI', 'DTE', 'DUK', 'DVA', 'DVN', 'DWDP', 'DXC',\n",
    "         'EA', 'EBAY', 'ECL', 'ED', 'EFX', 'EIX', 'EL', 'EMN', 'EMR', 'EOG', 'EQIX', 'EQR', 'EQT', 'ES', 'ESRX', 'ESS',  'ETFC', \n",
    "         'ETN', 'ETR', 'EVHC', 'EW', 'EXC', 'EXPD', 'EXPE', 'EXR', 'F', 'FAST', 'FB', 'FBHS', 'FCX', 'FDX', 'FE', 'FFIV',\n",
    "         'FIS', 'FISV', 'FITB','FL', 'FLIR', 'FLR', 'FLS', 'FMC', 'FOX', 'FOXA', 'FRT', 'FTI', 'FTV', 'GD', 'GE', 'GGP', 'GILD',\n",
    "         'GIS', 'GLW', 'GM', 'GOOG', 'GOOGL', 'GPC', 'GPN', 'GPS', 'GRMN', 'GS', 'GT', 'GWW', 'HAL', 'HAS', 'HBAN', 'HBI', 'HCA',\n",
    "         'HCN', 'HD', 'HES', 'HIG', 'HII', 'HLT', 'HOG', 'HOLX', 'HON', 'HP', 'HPE', 'HPQ', 'HRB', 'HRL', 'HRS', 'HSIC',\n",
    "         'HST', 'HSY', 'HUM', 'IBM', 'ICE', 'IDXX', 'IFF', 'ILMN', 'INCY', 'INFO', 'INTC', 'INTU', 'IP', 'IPG', 'IQV', 'IR',\n",
    "         'IRM', 'ISRG', 'IT', 'ITW', 'IVZ', 'JBHT', 'JCI', 'JEC', 'JNJ', 'JNPR', 'JPM', 'JWN', 'K', 'KEY', 'KHC', 'KIM', 'KLAC',\n",
    "         'KMB', 'KMI', 'KMX', 'KO', 'KORS', 'KR', 'KSS', 'KSU', 'L', 'LB', 'LEG', 'LEN', 'LH', 'LKQ', 'LLL', 'LLY', 'LMT', 'LNC',\n",
    "         'LNT', 'LOW', 'LRCX', 'LUK', 'LUV', 'LYB', 'M', 'MA', 'MAA', 'MAC', 'MAR', 'MAS', 'MAT', 'MCD', 'MCHP', 'MCK', 'MCO', 'MDLZ',\n",
    "         'MDT', 'MET', 'MGM', 'MHK', 'MKC', 'MLM', 'MMC', 'MMM', 'MNST', 'MO', 'MON', 'MOS', 'MPC', 'MRK', 'MRO', 'MS', 'MSFT',\n",
    "         'MSI', 'MTB', 'MTD', 'MU', 'MYL', 'NAVI', 'NBL', 'NCLH', 'NDAQ', 'NEE', 'NEM', 'NFLX', 'NFX', 'NI', 'NKE', 'NLSN', 'NOC',\n",
    "         'NOV', 'NRG', 'NSC', 'NTAP', 'NTRS', 'NUE', 'NVDA', 'NWL', 'NWS', 'NWSA', 'O', 'OKE', 'OMC', 'ORCL', 'ORLY', 'OXY',\n",
    "         'PAYX', 'PBCT', 'PCAR', 'PCG', 'PCLN', 'PDCO', 'PEG', 'PEP', 'PFE', 'PFG', 'PG', 'PGR', 'PH', 'PHM', 'PKG', 'PKI', 'PLD', \n",
    "         'PM', 'PNC', 'PNR', 'PNW', 'PPG', 'PPL', 'PRGO', 'PRU', 'PSA', 'PSX', 'PVH', 'PWR', 'PX', 'PXD', 'PYPL', 'QCOM', \n",
    "         'QRVO', 'RCL', 'RE', 'REG', 'REGN', 'RF', 'RHI', 'RHT', 'RJF', 'RL', 'RMD', 'ROK', 'ROP', 'ROST', 'RRC', 'RSG', 'RTN', 'SBAC', \n",
    "         'SBUX', 'SCG', 'SCHW', 'SEE', 'SHW', 'SIG', 'SJM', 'SLB', 'SLG', 'SNA', 'SNI', 'SNPS', 'SO', 'SPG', 'SPGI', 'SRCL', 'SRE', \n",
    "         'STI', 'STT', 'STX', 'STZ', 'SWK', 'SWKS', 'SYF', 'SYK', 'SYMC', 'SYY', 'T', 'TAP', 'TDG', 'TEL', 'TGT', 'TIF', 'TJX',\n",
    "         'TMK', 'TMO', 'TPR', 'TRIP', 'TROW', 'TRV', 'TSCO', 'TSN', 'TSS', 'TWX', 'TXN', 'TXT', 'UA', 'UAA', 'UAL', 'UDR', 'UHS',\n",
    "         'ULTA', 'UNH', 'UNM', 'UNP', 'UPS', 'URI', 'USB', 'UTX', 'V','VAR', 'VFC', 'VIAB', 'VLO', 'VMC', 'VNO', 'VRSK', \n",
    "         'VRSN', 'VRTX','VTR','VZ','WAT', 'WBA','WDC','WEC', 'WFC', 'WHR', 'WLTW', 'WM', 'WMB','WMT', 'WRK', 'WU', 'WY', 'WYN', \n",
    "         'WYNN', 'XEC', 'XEL', 'XL', 'XLNX', 'XOM', 'XRAY', 'XRX', 'XYL', 'YUM', 'ZBH', 'ZION', 'ZTS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['IBM',\n",
       " 'ZION',\n",
       " 'LRCX',\n",
       " 'IVZ',\n",
       " 'BWA',\n",
       " 'MTB',\n",
       " 'MDLZ',\n",
       " 'ADP',\n",
       " 'SLG',\n",
       " 'HPQ',\n",
       " 'MNST',\n",
       " 'DAL',\n",
       " 'URI',\n",
       " 'KSU',\n",
       " 'HRS',\n",
       " 'TROW',\n",
       " 'HES',\n",
       " 'SRE',\n",
       " 'DVN',\n",
       " 'MPC',\n",
       " 'FFIV',\n",
       " 'MAC',\n",
       " 'UNM',\n",
       " 'HRL',\n",
       " 'CCL',\n",
       " 'SRCL',\n",
       " 'DWDP',\n",
       " 'PEP',\n",
       " 'SYK',\n",
       " 'IQV',\n",
       " 'SBUX',\n",
       " 'CMCSA',\n",
       " 'YUM',\n",
       " 'AXP',\n",
       " 'NCLH',\n",
       " 'NBL',\n",
       " 'CLX',\n",
       " 'ABBV',\n",
       " 'BLL',\n",
       " 'HIG',\n",
       " 'ETR',\n",
       " 'PGR',\n",
       " 'UNH',\n",
       " 'IDXX',\n",
       " 'TSS',\n",
       " 'CINF',\n",
       " 'JWN',\n",
       " 'NEE',\n",
       " 'CSCO',\n",
       " 'HON',\n",
       " 'BF.B',\n",
       " 'AAP',\n",
       " 'GRMN',\n",
       " 'IFF',\n",
       " 'EQIX',\n",
       " 'XOM',\n",
       " 'TSCO',\n",
       " 'KR',\n",
       " 'EVHC',\n",
       " 'XEC',\n",
       " 'FMC',\n",
       " 'PAYX',\n",
       " 'MAA',\n",
       " 'ESS',\n",
       " 'COF',\n",
       " 'MCHP',\n",
       " 'PFE',\n",
       " 'ADSK',\n",
       " 'WRK',\n",
       " 'EXC',\n",
       " 'APH',\n",
       " 'GS',\n",
       " 'CERN',\n",
       " 'WBA',\n",
       " 'AMP',\n",
       " 'CHTR',\n",
       " 'LLY',\n",
       " 'CHRW',\n",
       " 'DFS',\n",
       " 'WU',\n",
       " 'MSI',\n",
       " 'LB',\n",
       " 'TRV',\n",
       " 'PM',\n",
       " 'DXC',\n",
       " 'AMD',\n",
       " 'QCOM',\n",
       " 'PCLN',\n",
       " 'DOV',\n",
       " 'VAR',\n",
       " 'PCAR',\n",
       " 'EBAY',\n",
       " 'VNO',\n",
       " 'MCO',\n",
       " 'ALK',\n",
       " 'SBAC',\n",
       " 'AGN',\n",
       " 'DISCA',\n",
       " 'FDX',\n",
       " 'MGM']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tickers = random.sample(index,k=100)\n",
    "tickers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = ['DXC', 'BBY', 'AMG', 'CNP', 'WMT', 'MRK', 'NEM', 'VFC', 'MDT', 'HST', 'MTD', 'CCI', 'KMI',\n",
    " 'RMD', 'LUV', 'NWS', 'AMGN', 'APD', 'TGT', 'MYL', 'GPC', 'FCX', 'UA', 'DISCK', 'FISV', 'ACN',\n",
    " 'SRCL', 'IT', 'PHM', 'ALB', 'AAPL', 'EFX', 'FE', 'COST', 'GWW', 'M', 'CRM', 'LKQ', 'AON',\n",
    " 'PEP', 'RJF', 'ORCL', 'MHK', 'PPL', 'FMC', 'OKE', 'EQR', 'RCL', 'IRM', 'BA', 'ABC', 'GIS',\n",
    " 'SYF', 'BMY', 'ADBE', 'SNPS', 'BWA', 'MSI', 'COF', 'LYB', 'RTN', 'FFIV', 'HLT', 'CVS', 'NEE',\n",
    " 'KMX', 'INCY', 'MGM', 'AES', 'GM', 'FDX', 'ADP', 'HOLX', 'NRG', 'NBL', 'MSFT', 'COG', 'OXY',\n",
    " 'GOOGL', 'CSRA', 'HRB', 'AJG', 'CINF', 'TXN', 'ETN', 'EXPE', 'TMO', 'BSX', 'CTAS', 'WBA', 'UNH',\n",
    " 'HSY', 'FB', 'EIX', 'SBUX', 'MCO', 'HII', 'RL', 'LNT', 'AXP']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers1 = ['DXC', 'BBY', 'AMG', 'CNP', 'WMT', 'MRK', 'NEM', 'VFC', 'MDT', 'HST', 'MTD', 'CCI', 'KMI']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df1 = pd.DataFrame()\n",
    "\n",
    "for t in tickers1:\n",
    "    stocks_df1[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers3 = ['RMD', 'LUV', 'NWS', 'AMGN', 'APD', 'TGT', 'MYL', 'GPC', 'FCX', 'KO', 'DISCK', 'FISV', 'ACN']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df3 = pd.DataFrame()\n",
    "\n",
    "for t in tickers3:\n",
    "    stocks_df3[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers4 = ['SRCL', 'IT', 'PHM', 'ALB', 'AAPL', 'EFX', 'FE', 'COST', 'GWW', 'M', 'CRM', 'LKQ', 'AON']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df4 = pd.DataFrame()\n",
    "\n",
    "for t in tickers4:\n",
    "    stocks_df4[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers5 = ['PEP', 'RJF', 'ORCL', 'MHK', 'PPL', 'FMC', 'OKE', 'EQR', 'RCL', 'IRM', 'BA', 'ABC', 'GIS']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df5 = pd.DataFrame()\n",
    "\n",
    "for t in tickers5:\n",
    "    stocks_df5[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers6 = ['SYF', 'BMY', 'ADBE', 'SNPS', 'BWA', 'MSI', 'COF', 'LYB', 'RTN', 'FFIV', 'HLT', 'CVS', 'NEE']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df6 = pd.DataFrame()\n",
    "\n",
    "for t in tickers6:\n",
    "    stocks_df6[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers7 = ['KMX', 'INCY', 'MGM', 'AES', 'GM', 'FDX', 'ADP', 'HOLX', 'NRG', 'NBL', 'MSFT', 'COG', 'OXY']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df7 = pd.DataFrame()\n",
    "\n",
    "for t in tickers7:\n",
    "    stocks_df7[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers8 = ['GOOGL','GS', 'HRB', 'AJG', 'CINF', 'TXN', 'ETN', 'EXPE', 'TMO', 'BSX', 'CTAS', 'WBA', 'UNH']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df8 = pd.DataFrame()\n",
    "\n",
    "for t in tickers8:\n",
    "    stocks_df8[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "tickers9 = [ 'HSY', 'FB', 'EIX', 'SBUX', 'MCO', 'HII', 'RL', 'LNT', 'AXP']\n",
    "start_date = pd.to_datetime('2015-1-1')\n",
    "end_date = pd.to_datetime('2020-1-10')\n",
    "stocks_df9 = pd.DataFrame()\n",
    "\n",
    "for t in tickers9:\n",
    "    stocks_df9[t]=wb.DataReader(t, data_source = 'yahoo', start = start_date, end = end_date) ['Adj Close']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>DXC</th>\n",
       "      <th>BBY</th>\n",
       "      <th>AMG</th>\n",
       "      <th>CNP</th>\n",
       "      <th>WMT</th>\n",
       "      <th>MRK</th>\n",
       "      <th>NEM</th>\n",
       "      <th>VFC</th>\n",
       "      <th>MDT</th>\n",
       "      <th>HST</th>\n",
       "      <th>...</th>\n",
       "      <th>UNH</th>\n",
       "      <th>HSY</th>\n",
       "      <th>FB</th>\n",
       "      <th>EIX</th>\n",
       "      <th>SBUX</th>\n",
       "      <th>MCO</th>\n",
       "      <th>HII</th>\n",
       "      <th>RL</th>\n",
       "      <th>LNT</th>\n",
       "      <th>AXP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</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",
       "      <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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-01-02</th>\n",
       "      <td>21.286510</td>\n",
       "      <td>33.869621</td>\n",
       "      <td>204.065598</td>\n",
       "      <td>18.782833</td>\n",
       "      <td>75.800331</td>\n",
       "      <td>49.132111</td>\n",
       "      <td>17.923134</td>\n",
       "      <td>60.863708</td>\n",
       "      <td>64.685036</td>\n",
       "      <td>18.943111</td>\n",
       "      <td>...</td>\n",
       "      <td>93.161888</td>\n",
       "      <td>91.816597</td>\n",
       "      <td>78.449997</td>\n",
       "      <td>55.423393</td>\n",
       "      <td>37.132599</td>\n",
       "      <td>89.816803</td>\n",
       "      <td>104.540627</td>\n",
       "      <td>164.706375</td>\n",
       "      <td>28.115042</td>\n",
       "      <td>85.741974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-05</th>\n",
       "      <td>20.855738</td>\n",
       "      <td>32.996471</td>\n",
       "      <td>196.666382</td>\n",
       "      <td>18.186682</td>\n",
       "      <td>75.579727</td>\n",
       "      <td>49.862339</td>\n",
       "      <td>18.034346</td>\n",
       "      <td>60.352108</td>\n",
       "      <td>64.415070</td>\n",
       "      <td>18.791948</td>\n",
       "      <td>...</td>\n",
       "      <td>91.627373</td>\n",
       "      <td>91.170326</td>\n",
       "      <td>77.190002</td>\n",
       "      <td>55.288025</td>\n",
       "      <td>36.421303</td>\n",
       "      <td>88.055870</td>\n",
       "      <td>102.971252</td>\n",
       "      <td>160.010773</td>\n",
       "      <td>27.772594</td>\n",
       "      <td>83.474472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-06</th>\n",
       "      <td>20.909586</td>\n",
       "      <td>32.699589</td>\n",
       "      <td>192.428558</td>\n",
       "      <td>17.781296</td>\n",
       "      <td>76.162109</td>\n",
       "      <td>51.821095</td>\n",
       "      <td>18.785009</td>\n",
       "      <td>59.997307</td>\n",
       "      <td>64.091087</td>\n",
       "      <td>18.712391</td>\n",
       "      <td>...</td>\n",
       "      <td>91.442474</td>\n",
       "      <td>91.471321</td>\n",
       "      <td>76.150002</td>\n",
       "      <td>55.499523</td>\n",
       "      <td>36.124943</td>\n",
       "      <td>87.128571</td>\n",
       "      <td>106.754547</td>\n",
       "      <td>156.889389</td>\n",
       "      <td>27.683804</td>\n",
       "      <td>81.695465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-07</th>\n",
       "      <td>21.061031</td>\n",
       "      <td>33.642605</td>\n",
       "      <td>197.151260</td>\n",
       "      <td>18.011810</td>\n",
       "      <td>78.182854</td>\n",
       "      <td>52.929340</td>\n",
       "      <td>18.618195</td>\n",
       "      <td>60.764694</td>\n",
       "      <td>65.692909</td>\n",
       "      <td>18.815811</td>\n",
       "      <td>...</td>\n",
       "      <td>92.376137</td>\n",
       "      <td>93.082603</td>\n",
       "      <td>76.150002</td>\n",
       "      <td>56.430172</td>\n",
       "      <td>37.014053</td>\n",
       "      <td>88.458626</td>\n",
       "      <td>106.567703</td>\n",
       "      <td>160.820389</td>\n",
       "      <td>28.140408</td>\n",
       "      <td>83.479713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-08</th>\n",
       "      <td>21.299976</td>\n",
       "      <td>34.149033</td>\n",
       "      <td>199.158630</td>\n",
       "      <td>18.441044</td>\n",
       "      <td>79.833008</td>\n",
       "      <td>53.994629</td>\n",
       "      <td>18.516251</td>\n",
       "      <td>62.134457</td>\n",
       "      <td>67.267754</td>\n",
       "      <td>19.014713</td>\n",
       "      <td>...</td>\n",
       "      <td>96.785576</td>\n",
       "      <td>94.879814</td>\n",
       "      <td>78.180000</td>\n",
       "      <td>56.743195</td>\n",
       "      <td>37.611340</td>\n",
       "      <td>89.320381</td>\n",
       "      <td>109.986694</td>\n",
       "      <td>160.631485</td>\n",
       "      <td>28.584330</td>\n",
       "      <td>84.663033</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>2020-01-06</th>\n",
       "      <td>35.330002</td>\n",
       "      <td>87.910004</td>\n",
       "      <td>83.008774</td>\n",
       "      <td>26.463535</td>\n",
       "      <td>117.650002</td>\n",
       "      <td>91.639999</td>\n",
       "      <td>43.259998</td>\n",
       "      <td>99.209999</td>\n",
       "      <td>114.889999</td>\n",
       "      <td>17.570000</td>\n",
       "      <td>...</td>\n",
       "      <td>291.549988</td>\n",
       "      <td>144.570007</td>\n",
       "      <td>212.600006</td>\n",
       "      <td>75.129997</td>\n",
       "      <td>87.721161</td>\n",
       "      <td>241.869995</td>\n",
       "      <td>265.000000</td>\n",
       "      <td>119.809998</td>\n",
       "      <td>53.404282</td>\n",
       "      <td>124.059998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-07</th>\n",
       "      <td>35.849998</td>\n",
       "      <td>88.410004</td>\n",
       "      <td>82.072395</td>\n",
       "      <td>26.582251</td>\n",
       "      <td>116.559998</td>\n",
       "      <td>89.199997</td>\n",
       "      <td>43.230000</td>\n",
       "      <td>98.720001</td>\n",
       "      <td>114.489998</td>\n",
       "      <td>17.520000</td>\n",
       "      <td>...</td>\n",
       "      <td>289.790009</td>\n",
       "      <td>144.850006</td>\n",
       "      <td>213.059998</td>\n",
       "      <td>75.860001</td>\n",
       "      <td>87.452415</td>\n",
       "      <td>241.000000</td>\n",
       "      <td>267.459991</td>\n",
       "      <td>120.330002</td>\n",
       "      <td>53.453960</td>\n",
       "      <td>123.410004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-08</th>\n",
       "      <td>36.459999</td>\n",
       "      <td>88.650002</td>\n",
       "      <td>82.211853</td>\n",
       "      <td>26.354712</td>\n",
       "      <td>116.160004</td>\n",
       "      <td>88.599998</td>\n",
       "      <td>42.080002</td>\n",
       "      <td>97.040001</td>\n",
       "      <td>116.500000</td>\n",
       "      <td>17.639999</td>\n",
       "      <td>...</td>\n",
       "      <td>295.899994</td>\n",
       "      <td>146.039993</td>\n",
       "      <td>215.220001</td>\n",
       "      <td>75.739998</td>\n",
       "      <td>88.467682</td>\n",
       "      <td>245.619995</td>\n",
       "      <td>268.959991</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>53.573189</td>\n",
       "      <td>125.540001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-09</th>\n",
       "      <td>36.220001</td>\n",
       "      <td>89.370003</td>\n",
       "      <td>82.610313</td>\n",
       "      <td>26.166748</td>\n",
       "      <td>117.360001</td>\n",
       "      <td>89.379997</td>\n",
       "      <td>41.660000</td>\n",
       "      <td>95.779999</td>\n",
       "      <td>118.389999</td>\n",
       "      <td>17.430000</td>\n",
       "      <td>...</td>\n",
       "      <td>294.220001</td>\n",
       "      <td>146.570007</td>\n",
       "      <td>218.300003</td>\n",
       "      <td>76.139999</td>\n",
       "      <td>90.110031</td>\n",
       "      <td>248.850006</td>\n",
       "      <td>276.010010</td>\n",
       "      <td>117.930000</td>\n",
       "      <td>54.179264</td>\n",
       "      <td>127.809998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-10</th>\n",
       "      <td>35.740002</td>\n",
       "      <td>90.650002</td>\n",
       "      <td>82.610313</td>\n",
       "      <td>26.018353</td>\n",
       "      <td>116.379997</td>\n",
       "      <td>89.529999</td>\n",
       "      <td>42.270000</td>\n",
       "      <td>95.059998</td>\n",
       "      <td>118.639999</td>\n",
       "      <td>17.520000</td>\n",
       "      <td>...</td>\n",
       "      <td>295.130005</td>\n",
       "      <td>148.130005</td>\n",
       "      <td>218.059998</td>\n",
       "      <td>75.500000</td>\n",
       "      <td>89.741745</td>\n",
       "      <td>248.029999</td>\n",
       "      <td>270.940002</td>\n",
       "      <td>117.959999</td>\n",
       "      <td>54.715794</td>\n",
       "      <td>127.279999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1265 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  DXC        BBY         AMG        CNP         WMT  \\\n",
       "Date                                                                  \n",
       "2015-01-02  21.286510  33.869621  204.065598  18.782833   75.800331   \n",
       "2015-01-05  20.855738  32.996471  196.666382  18.186682   75.579727   \n",
       "2015-01-06  20.909586  32.699589  192.428558  17.781296   76.162109   \n",
       "2015-01-07  21.061031  33.642605  197.151260  18.011810   78.182854   \n",
       "2015-01-08  21.299976  34.149033  199.158630  18.441044   79.833008   \n",
       "...               ...        ...         ...        ...         ...   \n",
       "2020-01-06  35.330002  87.910004   83.008774  26.463535  117.650002   \n",
       "2020-01-07  35.849998  88.410004   82.072395  26.582251  116.559998   \n",
       "2020-01-08  36.459999  88.650002   82.211853  26.354712  116.160004   \n",
       "2020-01-09  36.220001  89.370003   82.610313  26.166748  117.360001   \n",
       "2020-01-10  35.740002  90.650002   82.610313  26.018353  116.379997   \n",
       "\n",
       "                  MRK        NEM        VFC         MDT        HST  ...  \\\n",
       "Date                                                                ...   \n",
       "2015-01-02  49.132111  17.923134  60.863708   64.685036  18.943111  ...   \n",
       "2015-01-05  49.862339  18.034346  60.352108   64.415070  18.791948  ...   \n",
       "2015-01-06  51.821095  18.785009  59.997307   64.091087  18.712391  ...   \n",
       "2015-01-07  52.929340  18.618195  60.764694   65.692909  18.815811  ...   \n",
       "2015-01-08  53.994629  18.516251  62.134457   67.267754  19.014713  ...   \n",
       "...               ...        ...        ...         ...        ...  ...   \n",
       "2020-01-06  91.639999  43.259998  99.209999  114.889999  17.570000  ...   \n",
       "2020-01-07  89.199997  43.230000  98.720001  114.489998  17.520000  ...   \n",
       "2020-01-08  88.599998  42.080002  97.040001  116.500000  17.639999  ...   \n",
       "2020-01-09  89.379997  41.660000  95.779999  118.389999  17.430000  ...   \n",
       "2020-01-10  89.529999  42.270000  95.059998  118.639999  17.520000  ...   \n",
       "\n",
       "                   UNH         HSY          FB        EIX       SBUX  \\\n",
       "Date                                                                   \n",
       "2015-01-02   93.161888   91.816597   78.449997  55.423393  37.132599   \n",
       "2015-01-05   91.627373   91.170326   77.190002  55.288025  36.421303   \n",
       "2015-01-06   91.442474   91.471321   76.150002  55.499523  36.124943   \n",
       "2015-01-07   92.376137   93.082603   76.150002  56.430172  37.014053   \n",
       "2015-01-08   96.785576   94.879814   78.180000  56.743195  37.611340   \n",
       "...                ...         ...         ...        ...        ...   \n",
       "2020-01-06  291.549988  144.570007  212.600006  75.129997  87.721161   \n",
       "2020-01-07  289.790009  144.850006  213.059998  75.860001  87.452415   \n",
       "2020-01-08  295.899994  146.039993  215.220001  75.739998  88.467682   \n",
       "2020-01-09  294.220001  146.570007  218.300003  76.139999  90.110031   \n",
       "2020-01-10  295.130005  148.130005  218.059998  75.500000  89.741745   \n",
       "\n",
       "                   MCO         HII          RL        LNT         AXP  \n",
       "Date                                                                   \n",
       "2015-01-02   89.816803  104.540627  164.706375  28.115042   85.741974  \n",
       "2015-01-05   88.055870  102.971252  160.010773  27.772594   83.474472  \n",
       "2015-01-06   87.128571  106.754547  156.889389  27.683804   81.695465  \n",
       "2015-01-07   88.458626  106.567703  160.820389  28.140408   83.479713  \n",
       "2015-01-08   89.320381  109.986694  160.631485  28.584330   84.663033  \n",
       "...                ...         ...         ...        ...         ...  \n",
       "2020-01-06  241.869995  265.000000  119.809998  53.404282  124.059998  \n",
       "2020-01-07  241.000000  267.459991  120.330002  53.453960  123.410004  \n",
       "2020-01-08  245.619995  268.959991  120.000000  53.573189  125.540001  \n",
       "2020-01-09  248.850006  276.010010  117.930000  54.179264  127.809998  \n",
       "2020-01-10  248.029999  270.940002  117.959999  54.715794  127.279999  \n",
       "\n",
       "[1265 rows x 100 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df_1 = pd.concat([stocks_df1,stocks_df3,stocks_df4,stocks_df5,stocks_df6,stocks_df7,stocks_df8,stocks_df9], axis = 'columns', join='inner')\n",
    "#combined_df_1.reset_index(inplace = True)\n",
    "combined_df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_monthly = combined_df_1.asfreq('M').ffill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_returns = combined_monthly.pct_change()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DXC    0.001581\n",
       "BBY    0.001581\n",
       "AMG    0.001581\n",
       "CNP    0.001581\n",
       "WMT    0.001581\n",
       "         ...   \n",
       "MCO    0.001581\n",
       "HII    0.001581\n",
       "RL     0.001581\n",
       "LNT    0.001581\n",
       "AXP    0.001581\n",
       "Length: 100, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_monthly.isnull().sum()/len(combined_df_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_monthly.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# printing the dataset to csv \n",
    "output = 'monthly_data.csv'\n",
    "\n",
    "with open(output, 'w') as file:\n",
    "    file.write(combined_monthly.to_csv(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}