sp500_historical.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-16T14:29:50.731803Z",
     "start_time": "2019-11-16T14:29:50.715887Z"
    }
   },
   "source": [
    "# Lists of S&P 500 companies historical components\n",
    "\n",
    "1. Read 'S&P 500 Historical Components & Changes.csv' which contains historical S&P 500 index membership since 1996.  Each row contains 2 fields: date and tickers; tickers is a comma delimited string of the symbols for that specified date.  This file was provided as open source via download for the book \"Trading Evolved\" by Andreas F. Clenow.  \n",
    "2. Clean up the list by fixing up symbols, removing duplicates in each row, and sorting the symbols  \n",
    "3. Read in 'sp500_changes_since_2019.csv' which contains the changes to the index since 2019  \n",
    "4. Create new rows in the historical membership list for these changes  \n",
    "5. Compare the last row to current list of S&P 500 components on the wikipedia  \n",
    "6. A list of differences is generated.  If this list is NOT empty, then changes have been made to the index that should be included in 'sp500_changes_since_2019.csv'.  \n",
    "7. Write out date stamped 'S&P 500 Historical Components & Changes' csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:27.558004Z",
     "start_time": "2019-11-21T06:08:27.542153Z"
    }
   },
   "outputs": [],
   "source": [
    "# use future imports for python 3.x forward compatibility\n",
    "from __future__ import print_function\n",
    "from __future__ import division\n",
    "from __future__ import absolute_import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:28.084597Z",
     "start_time": "2019-11-21T06:08:27.567656Z"
    }
   },
   "outputs": [],
   "source": [
    "# other imports\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "pd.set_option('display.max_rows', 600)\n",
    "\n",
    "# -*- encoding: utf-8 -*-\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:28.108051Z",
     "start_time": "2019-11-21T06:08:28.095097Z"
    }
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.OutputArea.prototype._should_scroll = function(lines) {\n",
       "    return false;\n",
       "}"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "IPython.OutputArea.prototype._should_scroll = function(lines) {\n",
    "    return false;\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:28.122418Z",
     "start_time": "2019-11-21T06:08:28.113066Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_table(filename):\n",
    "\n",
    "    if os.path.isfile(filename):\n",
    "        df = pd.read_csv(filename, index_col='date')\n",
    "        return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:28.301604Z",
     "start_time": "2019-11-21T06:08:28.132556Z"
    },
    "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02</th>\n",
       "      <td>TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03</th>\n",
       "      <td>AAL-199702,AAMRQ-201312,AAPL,ABI-200811,ABS-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04</th>\n",
       "      <td>TMC-200006,AAL-199702,AAMRQ-201312,AAPL,OAT-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-10</th>\n",
       "      <td>TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-11</th>\n",
       "      <td>FLMIQ-200408,FLTWQ-200907,FMC,FMCC,FNMA,FTL.A-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "1996-01-02  TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...\n",
       "1996-01-03  AAL-199702,AAMRQ-201312,AAPL,ABI-200811,ABS-20...\n",
       "1996-01-04  TMC-200006,AAL-199702,AAMRQ-201312,AAPL,OAT-20...\n",
       "1996-01-10  TMC-200006,AAL-199702,AAMRQ-201312,AAPL,ABI-20...\n",
       "1996-01-11  FLMIQ-200408,FLTWQ-200907,FMC,FMCC,FNMA,FTL.A-..."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = 'S&P 500 Historical Components & Changes.csv'\n",
    "df = get_table(filename)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:28.491238Z",
     "start_time": "2019-11-21T06:08:28.311832Z"
    },
    "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-08</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-09</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-10</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-11</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "2019-01-03  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-08  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-09  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-10  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-11  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,..."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert ticker column from csv to list, then sort\n",
    "df['tickers'] = df['tickers'].apply(lambda x: sorted(x.split(',')))\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:28.877405Z",
     "start_time": "2019-11-21T06:08:28.500332Z"
    }
   },
   "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-10</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-11</th>\n",
       "      <td>[AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "1996-01-02  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-03  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-04  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-10  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A...\n",
       "1996-01-11  [AAL, AAMRQ, AAPL, ABI, ABS, ABT, ABX, ACKH, A..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# replace SYMBOL-yyyymm with SYMBOL\n",
    "df['tickers'] = [[ticker.split('-')[0] for ticker in tickers] for tickers in df['tickers']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.317995Z",
     "start_time": "2019-11-21T06:08:28.881583Z"
    },
    "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-08</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-09</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-10</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-11</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "2019-01-03  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-08  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-09  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-10  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-01-11  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,..."
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove duplicates in each row\n",
    "df['tickers'] = [sorted(list(set(tickers))) for tickers in df['tickers']]\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Changes to the list of S&P 500 components\n",
    "https://en.wikipedia.org/wiki/List_of_S%26P_500_companies#Selected_changes_to_the_list_of_S&P_500_components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.384487Z",
     "start_time": "2019-11-21T06:08:29.327391Z"
    }
   },
   "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>add</th>\n",
       "      <th>remove</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-31</th>\n",
       "      <td>[LIN]</td>\n",
       "      <td>[PX]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-18</th>\n",
       "      <td>[TFX]</td>\n",
       "      <td>[PCG]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-15</th>\n",
       "      <td>[ATO]</td>\n",
       "      <td>[NFX]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-02-27</th>\n",
       "      <td>[WAB]</td>\n",
       "      <td>[GT]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-04-02</th>\n",
       "      <td>[DOW]</td>\n",
       "      <td>[BHF]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-01</th>\n",
       "      <td>[LHX]</td>\n",
       "      <td>[HRS]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-03</th>\n",
       "      <td>[CTVA, DD]</td>\n",
       "      <td>[DWDP, FLR]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-07</th>\n",
       "      <td>[AMCR]</td>\n",
       "      <td>[MAT]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-01</th>\n",
       "      <td>[MKTX]</td>\n",
       "      <td>[LLL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-15</th>\n",
       "      <td>[TMUS]</td>\n",
       "      <td>[RHT]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-08-08</th>\n",
       "      <td>[GL]</td>\n",
       "      <td>[TMK]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-08-09</th>\n",
       "      <td>[IEX, LDOS]</td>\n",
       "      <td>[APC, FL]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-23</th>\n",
       "      <td>[CDW]</td>\n",
       "      <td>[TSS]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-26</th>\n",
       "      <td>[NVR]</td>\n",
       "      <td>[JEF]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-03</th>\n",
       "      <td>[LVS]</td>\n",
       "      <td>[NKTR]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-18</th>\n",
       "      <td>[BKR]</td>\n",
       "      <td>[BHGE]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-05</th>\n",
       "      <td>[NLOK, PEAK]</td>\n",
       "      <td>[HCP, SYMC]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-21</th>\n",
       "      <td>[NOW]</td>\n",
       "      <td>[CELG]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     add       remove\n",
       "date                                 \n",
       "2018-10-31         [LIN]         [PX]\n",
       "2019-01-18         [TFX]        [PCG]\n",
       "2019-02-15         [ATO]        [NFX]\n",
       "2019-02-27         [WAB]         [GT]\n",
       "2019-04-02         [DOW]        [BHF]\n",
       "2019-06-01         [LHX]        [HRS]\n",
       "2019-06-03    [CTVA, DD]  [DWDP, FLR]\n",
       "2019-06-07        [AMCR]        [MAT]\n",
       "2019-07-01        [MKTX]        [LLL]\n",
       "2019-07-15        [TMUS]        [RHT]\n",
       "2019-08-08          [GL]        [TMK]\n",
       "2019-08-09   [IEX, LDOS]    [APC, FL]\n",
       "2019-09-23         [CDW]        [TSS]\n",
       "2019-09-26         [NVR]        [JEF]\n",
       "2019-10-03         [LVS]       [NKTR]\n",
       "2019-10-18         [BKR]       [BHGE]\n",
       "2019-11-05  [NLOK, PEAK]  [HCP, SYMC]\n",
       "2019-11-21         [NOW]       [CELG]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read S&P 500 changes csv file\n",
    "filename = 'sp500_changes_since_2019.csv'\n",
    "changes = get_table(filename)\n",
    "\n",
    "# convert ticker column from csv to list, then sort\n",
    "changes['add'] = changes['add'].apply(lambda x: sorted(x.split(',')))\n",
    "changes['remove'] = changes['remove'].apply(lambda x: sorted(x.split(',')))\n",
    "changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.550600Z",
     "start_time": "2019-11-21T06:08:29.394435Z"
    }
   },
   "outputs": [],
   "source": [
    "# copy the last row in dataframe, modify for changes, then append\n",
    "for change in changes.itertuples():\n",
    "\n",
    "    new_row = df.tail(1)\n",
    "    \n",
    "    tickers = list(new_row['tickers'][0])\n",
    "    tickers += change.add\n",
    "    tickers = list(set(tickers) - set(change.remove))\n",
    "    tickers = sorted(tickers)\n",
    "    \n",
    "    d = {'date':change.Index, 'tickers':[tickers]}\n",
    "    new_entry = pd.DataFrame(d)\n",
    "    new_entry.set_index('date', inplace=True)\n",
    "    df = df.append(new_entry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.589418Z",
     "start_time": "2019-11-21T06:08:29.559506Z"
    },
    "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-09-26</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-03</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-10-18</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-05</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-11-21</th>\n",
       "      <td>[A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "2019-09-26  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-10-03  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-10-18  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-11-05  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,...\n",
       "2019-11-21  [A, AAL, AAP, AAPL, ABBV, ABC, ABMD, ABT, ACN,..."
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.622756Z",
     "start_time": "2019-11-21T06:08:29.593814Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compare last row to current S&P500 list\n",
    "filename = 'sp500.csv'\n",
    "current = pd.read_csv(filename)\n",
    "current = list(current['Symbol'])\n",
    "last_entry = list(df['tickers'][-1])\n",
    "\n",
    "diff = list(set(current) - set(last_entry)) + list(set(last_entry) - set(current))\n",
    "diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.689386Z",
     "start_time": "2019-11-21T06:08:29.630259Z"
    },
    "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>tickers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-10</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-11</th>\n",
       "      <td>AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      tickers\n",
       "date                                                         \n",
       "1996-01-02  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-03  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-04  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-10  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD...\n",
       "1996-01-11  AAL,AAMRQ,AAPL,ABI,ABS,ABT,ABX,ACKH,ACV,ADM,AD..."
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert tickers column back to csv\n",
    "df['tickers'] = df['tickers'].apply(lambda x: \",\".join(x))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-21T06:08:29.840014Z",
     "start_time": "2019-11-21T06:08:29.693811Z"
    }
   },
   "outputs": [],
   "source": [
    "now = datetime.now()\n",
    "dt_string = now.strftime('%d-%m-%Y') # dd-mm-YY\n",
    "filename = 'S&P 500 Historical Components & Changes({}).csv'.format(dt_string)\n",
    "df.to_csv(filename)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15+"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}