Low-vol v3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda3\\lib\\site-packages\\numpy\\lib\\arraysetops.py:569: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv('with_36mvols.csv', low_memory=False, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "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>PERMNO</th>\n",
       "      <th>date</th>\n",
       "      <th>DIVAMT</th>\n",
       "      <th>PRC</th>\n",
       "      <th>SHROUT</th>\n",
       "      <th>Rolling 36m Vol</th>\n",
       "      <th>Mkt Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.37500</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.610000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.196000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.43750</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.633000e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.517200e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.10938</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.179388e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.173459e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.84375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.078634e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.148594e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.03125</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.911531e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.78125</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.002344e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.82813</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.182504e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.51563</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.981566e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.581531e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.581531e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.732500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.23438</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.124413e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.21875</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.515938e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.893000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10001</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.033125e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.25000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.156250e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.217812e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.279375e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.217812e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.033125e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.06250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.971562e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.402500e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>991.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.317625e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.62500</td>\n",
       "      <td>991.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.565375e+03</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098016</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.20000</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>67.974093</td>\n",
       "      <td>2.524911e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098017</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.92999</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>62.937431</td>\n",
       "      <td>2.534551e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098018</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>229.77000</td>\n",
       "      <td>133858.0</td>\n",
       "      <td>56.929345</td>\n",
       "      <td>3.075655e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098019</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240.75999</td>\n",
       "      <td>133945.0</td>\n",
       "      <td>50.275961</td>\n",
       "      <td>3.224860e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098020</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.23000</td>\n",
       "      <td>147543.0</td>\n",
       "      <td>43.810568</td>\n",
       "      <td>3.293602e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098021</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.28000</td>\n",
       "      <td>148015.0</td>\n",
       "      <td>39.647662</td>\n",
       "      <td>3.142062e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098022</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>234.78999</td>\n",
       "      <td>148693.0</td>\n",
       "      <td>35.479931</td>\n",
       "      <td>3.491163e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098023</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.00999</td>\n",
       "      <td>149239.0</td>\n",
       "      <td>32.877783</td>\n",
       "      <td>3.164016e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098024</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>204.03000</td>\n",
       "      <td>149825.0</td>\n",
       "      <td>31.848610</td>\n",
       "      <td>3.056879e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098025</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>197.73000</td>\n",
       "      <td>149891.0</td>\n",
       "      <td>31.658101</td>\n",
       "      <td>2.963795e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098026</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>189.39999</td>\n",
       "      <td>149964.0</td>\n",
       "      <td>30.269247</td>\n",
       "      <td>2.840318e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098027</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.69000</td>\n",
       "      <td>161561.0</td>\n",
       "      <td>26.444773</td>\n",
       "      <td>3.452397e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098028</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>251.92999</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>23.572926</td>\n",
       "      <td>4.072952e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098029</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>249.99001</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>22.888288</td>\n",
       "      <td>4.041588e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098030</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>278.29999</td>\n",
       "      <td>164164.0</td>\n",
       "      <td>23.023424</td>\n",
       "      <td>4.568684e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098031</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>314.07001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>24.373707</td>\n",
       "      <td>5.158914e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098032</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.01001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>27.942659</td>\n",
       "      <td>5.601430e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098033</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>361.60999</td>\n",
       "      <td>166863.0</td>\n",
       "      <td>33.011217</td>\n",
       "      <td>6.033933e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098034</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>323.47000</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>39.067422</td>\n",
       "      <td>5.398294e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098035</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>355.89999</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>41.425934</td>\n",
       "      <td>5.939508e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098036</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.10001</td>\n",
       "      <td>168017.0</td>\n",
       "      <td>45.341310</td>\n",
       "      <td>5.731060e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098037</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>331.53000</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>48.118293</td>\n",
       "      <td>5.571925e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098038</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>308.85001</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>50.128638</td>\n",
       "      <td>5.190749e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098039</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>311.35001</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>51.099733</td>\n",
       "      <td>5.255495e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098040</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>354.31000</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>51.847135</td>\n",
       "      <td>5.980647e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098041</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>343.06000</td>\n",
       "      <td>168920.0</td>\n",
       "      <td>53.774889</td>\n",
       "      <td>5.794970e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098042</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>266.13000</td>\n",
       "      <td>169750.0</td>\n",
       "      <td>54.813517</td>\n",
       "      <td>4.517557e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098043</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>293.89999</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>53.454523</td>\n",
       "      <td>4.990245e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098044</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>284.73001</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>53.322950</td>\n",
       "      <td>4.834545e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098045</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.95001</td>\n",
       "      <td>170516.0</td>\n",
       "      <td>53.369757</td>\n",
       "      <td>5.847846e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4098046 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         PERMNO        date    DIVAMT        PRC    SHROUT  Rolling 36m Vol  \\\n",
       "0         10000  1985-12-31  0.000000   -1.00000      -1.0              NaN   \n",
       "1         10000  1986-01-31  0.000000    4.37500    3680.0              NaN   \n",
       "2         10000  1986-02-28  0.000000    3.25000    3680.0              NaN   \n",
       "3         10000  1986-03-31  0.000000    4.43750    3680.0              NaN   \n",
       "4         10000  1986-04-30  0.000000    4.00000    3793.0              NaN   \n",
       "5         10000  1986-05-31  0.000000    3.10938    3793.0              NaN   \n",
       "6         10000  1986-06-30  0.000000    3.09375    3793.0              NaN   \n",
       "7         10000  1986-07-31  0.000000    2.84375    3793.0              NaN   \n",
       "8         10000  1986-08-31  0.000000    1.09375    3793.0              NaN   \n",
       "9         10000  1986-09-30  0.000000    1.03125    3793.0              NaN   \n",
       "10        10000  1986-10-31  0.000000    0.78125    3843.0              NaN   \n",
       "11        10000  1986-11-30  0.000000    0.82813    3843.0              NaN   \n",
       "12        10000  1986-12-31  0.000000    0.51563    3843.0              NaN   \n",
       "13        10000  1987-01-31  0.000000    0.40625    3893.0              NaN   \n",
       "14        10000  1987-02-28  0.000000    0.40625    3893.0              NaN   \n",
       "15        10000  1987-03-31  0.000000    0.25000    3893.0              NaN   \n",
       "16        10000  1987-04-30  0.000000    0.23438    3893.0              NaN   \n",
       "17        10000  1987-05-31  0.000000    0.21875    3893.0              NaN   \n",
       "18        10000  1987-06-30  0.000000   -1.00000    3893.0              NaN   \n",
       "19        10001  1985-12-31  0.000000   -1.00000      -1.0              NaN   \n",
       "20        10001  1986-01-31  0.000000    6.12500     985.0              NaN   \n",
       "21        10001  1986-02-28  0.000000    6.25000     985.0              NaN   \n",
       "22        10001  1986-03-31  0.031667    6.31250     985.0              NaN   \n",
       "23        10001  1986-04-30  0.031667    6.37500     985.0              NaN   \n",
       "24        10001  1986-05-31  0.031667    6.31250     985.0              NaN   \n",
       "25        10001  1986-06-30  0.035000    6.12500     985.0              NaN   \n",
       "26        10001  1986-07-31  0.035000    6.06250     985.0              NaN   \n",
       "27        10001  1986-08-31  0.035000    6.50000     985.0              NaN   \n",
       "28        10001  1986-09-30  0.035000    6.37500     991.0              NaN   \n",
       "29        10001  1986-10-31  0.035000    6.62500     991.0              NaN   \n",
       "...         ...         ...       ...        ...       ...              ...   \n",
       "4098016   93436  2016-01-31  0.000000  191.20000  132056.0        67.974093   \n",
       "4098017   93436  2016-02-29  0.000000  191.92999  132056.0        62.937431   \n",
       "4098018   93436  2016-03-31  0.000000  229.77000  133858.0        56.929345   \n",
       "4098019   93436  2016-04-30  0.000000  240.75999  133945.0        50.275961   \n",
       "4098020   93436  2016-05-31  0.000000  223.23000  147543.0        43.810568   \n",
       "4098021   93436  2016-06-30  0.000000  212.28000  148015.0        39.647662   \n",
       "4098022   93436  2016-07-31  0.000000  234.78999  148693.0        35.479931   \n",
       "4098023   93436  2016-08-31  0.000000  212.00999  149239.0        32.877783   \n",
       "4098024   93436  2016-09-30  0.000000  204.03000  149825.0        31.848610   \n",
       "4098025   93436  2016-10-31  0.000000  197.73000  149891.0        31.658101   \n",
       "4098026   93436  2016-11-30  0.000000  189.39999  149964.0        30.269247   \n",
       "4098027   93436  2016-12-31  0.000000  213.69000  161561.0        26.444773   \n",
       "4098028   93436  2017-01-31  0.000000  251.92999  161670.0        23.572926   \n",
       "4098029   93436  2017-02-28  0.000000  249.99001  161670.0        22.888288   \n",
       "4098030   93436  2017-03-31  0.000000  278.29999  164164.0        23.023424   \n",
       "4098031   93436  2017-04-30  0.000000  314.07001  164260.0        24.373707   \n",
       "4098032   93436  2017-05-31  0.000000  341.01001  164260.0        27.942659   \n",
       "4098033   93436  2017-06-30  0.000000  361.60999  166863.0        33.011217   \n",
       "4098034   93436  2017-07-31  0.000000  323.47000  166887.0        39.067422   \n",
       "4098035   93436  2017-08-31  0.000000  355.89999  166887.0        41.425934   \n",
       "4098036   93436  2017-09-30  0.000000  341.10001  168017.0        45.341310   \n",
       "4098037   93436  2017-10-31  0.000000  331.53000  168067.0        48.118293   \n",
       "4098038   93436  2017-11-30  0.000000  308.85001  168067.0        50.128638   \n",
       "4098039   93436  2017-12-31  0.000000  311.35001  168797.0        51.099733   \n",
       "4098040   93436  2018-01-31  0.000000  354.31000  168797.0        51.847135   \n",
       "4098041   93436  2018-02-28  0.000000  343.06000  168920.0        53.774889   \n",
       "4098042   93436  2018-03-31  0.000000  266.13000  169750.0        54.813517   \n",
       "4098043   93436  2018-04-30  0.000000  293.89999  169794.0        53.454523   \n",
       "4098044   93436  2018-05-31  0.000000  284.73001  169794.0        53.322950   \n",
       "4098045   93436  2018-06-30  0.000000  342.95001  170516.0        53.369757   \n",
       "\n",
       "              Mkt Cap  \n",
       "0        1.000000e+00  \n",
       "1        1.610000e+04  \n",
       "2        1.196000e+04  \n",
       "3        1.633000e+04  \n",
       "4        1.517200e+04  \n",
       "5        1.179388e+04  \n",
       "6        1.173459e+04  \n",
       "7        1.078634e+04  \n",
       "8        4.148594e+03  \n",
       "9        3.911531e+03  \n",
       "10       3.002344e+03  \n",
       "11       3.182504e+03  \n",
       "12       1.981566e+03  \n",
       "13       1.581531e+03  \n",
       "14       1.581531e+03  \n",
       "15       9.732500e+02  \n",
       "16       9.124413e+02  \n",
       "17       8.515938e+02  \n",
       "18      -3.893000e+03  \n",
       "19       1.000000e+00  \n",
       "20       6.033125e+03  \n",
       "21       6.156250e+03  \n",
       "22       6.217812e+03  \n",
       "23       6.279375e+03  \n",
       "24       6.217812e+03  \n",
       "25       6.033125e+03  \n",
       "26       5.971562e+03  \n",
       "27       6.402500e+03  \n",
       "28       6.317625e+03  \n",
       "29       6.565375e+03  \n",
       "...               ...  \n",
       "4098016  2.524911e+07  \n",
       "4098017  2.534551e+07  \n",
       "4098018  3.075655e+07  \n",
       "4098019  3.224860e+07  \n",
       "4098020  3.293602e+07  \n",
       "4098021  3.142062e+07  \n",
       "4098022  3.491163e+07  \n",
       "4098023  3.164016e+07  \n",
       "4098024  3.056879e+07  \n",
       "4098025  2.963795e+07  \n",
       "4098026  2.840318e+07  \n",
       "4098027  3.452397e+07  \n",
       "4098028  4.072952e+07  \n",
       "4098029  4.041588e+07  \n",
       "4098030  4.568684e+07  \n",
       "4098031  5.158914e+07  \n",
       "4098032  5.601430e+07  \n",
       "4098033  6.033933e+07  \n",
       "4098034  5.398294e+07  \n",
       "4098035  5.939508e+07  \n",
       "4098036  5.731060e+07  \n",
       "4098037  5.571925e+07  \n",
       "4098038  5.190749e+07  \n",
       "4098039  5.255495e+07  \n",
       "4098040  5.980647e+07  \n",
       "4098041  5.794970e+07  \n",
       "4098042  4.517557e+07  \n",
       "4098043  4.990245e+07  \n",
       "4098044  4.834545e+07  \n",
       "4098045  5.847846e+07  \n",
       "\n",
       "[4098046 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Mkt Cap'] = data['PRC'] * data['SHROUT']\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['date'] = data['date'].apply(lambda x:datetime.datetime.strptime(x,'%Y-%m-%d'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data, get when are the dates used\n",
    "head, mid, tail = [], [], []\n",
    "for i1 in range(1964, 2018):\n",
    "    for i2 in range(2,13):\n",
    "        mid.append(datetime.datetime(i1, i2, 1)-datetime.timedelta(days = 1))\n",
    "    mid.append(datetime.datetime(i1+1, 1, 1)-datetime.timedelta(days = 1))\n",
    "\n",
    "for i1 in range(8, 13):\n",
    "    head.append(datetime.datetime(1963, i1, 1)-datetime.timedelta(days = 1))\n",
    "head.append(datetime.datetime(1964, 1, 1)-datetime.timedelta(days = 1))\n",
    "    \n",
    "for i1 in range(2, 8):\n",
    "    tail.append(datetime.datetime(2018,i1,1)-datetime.timedelta(days = 1))\n",
    "\n",
    "dates_month = head+mid+tail # The index of the data file, in months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "144.89897203445435\n"
     ]
    }
   ],
   "source": [
    "# Record dates\n",
    "cur_tic = data['PERMNO'][0]\n",
    "start_ind = 0\n",
    "dates_ind = {}\n",
    "dates = {}\n",
    "start_time = time.time()\n",
    "\n",
    "def strtodatetime(string):\n",
    "    datetime.strptime(string)\n",
    "\n",
    "for i in range(len(data)):\n",
    "    if data['PERMNO'][i]==cur_tic:\n",
    "        pass\n",
    "    else:\n",
    "        dates_ind.update({cur_tic:[start_ind, i-1]})\n",
    "        dates.update({cur_tic:[data['date'][start_ind],data['date'][i-1]]})\n",
    "        start_ind=i\n",
    "        cur_tic = data['PERMNO'][i]\n",
    "        \n",
    "dates_ind.update({cur_tic:[start_ind, i]})\n",
    "dates.update({cur_tic:[data['date'][start_ind],data['date'][i]]})\n",
    "print(time.time()-start_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "392.32103633880615\n"
     ]
    }
   ],
   "source": [
    "# For some stocks, the shares outstanding and the prices may be missing, \n",
    "# if so we need to mark the date when they have both price and shares outstanding\n",
    "volnan = data['Rolling 36m Vol'].isnull() # True for vol is NaN\n",
    "pop_lst = []\n",
    "\n",
    "for ticker in dates_ind:\n",
    "    # Get the start right\n",
    "    si, ei = dates_ind[ticker][0], dates_ind[ticker][1]\n",
    "    if volnan[si] and volnan[ei]:\n",
    "        pop_lst.append(ticker)\n",
    "        continue\n",
    "    while(data['PRC'].iloc[si]<0 or data['SHROUT'].iloc[si]<0 or volnan[si]):\n",
    "        si+=1\n",
    "    while(data['PRC'].iloc[ei]<0 or data['SHROUT'].iloc[ei]<0 or volnan[ei]):\n",
    "        ei-=1\n",
    "    dates_ind.update({ticker:[si,ei]})\n",
    "    dates.update({ticker:[data['date'][si],data['date'][ei]]})\n",
    "    \n",
    "for ticker in pop_lst:\n",
    "    dates_ind.pop(ticker)\n",
    "    dates.pop(ticker)\n",
    "print('Run time: %.2f s. Remained Stocks: %d'%(time.time()-start_time,len(dates_ind)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "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>PERMNO</th>\n",
       "      <th>date</th>\n",
       "      <th>DIVAMT</th>\n",
       "      <th>PRC</th>\n",
       "      <th>SHROUT</th>\n",
       "      <th>Rolling 36m Vol</th>\n",
       "      <th>Mkt Cap</th>\n",
       "      <th>12m Mom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.37500</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.610000e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.196000e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.43750</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.633000e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.517200e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.10938</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.179388e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.173459e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.84375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.078634e+04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.148594e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.03125</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.911531e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.78125</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.002344e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.82813</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.182504e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.51563</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.981566e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.581531e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.581531e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.732500e+02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.23438</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.124413e+02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.21875</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.515938e+02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.893000e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10001</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.033125e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.25000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.156250e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.217812e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.279375e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.217812e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.033125e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.06250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.971562e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.402500e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>991.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.317625e+03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.62500</td>\n",
       "      <td>991.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.565375e+03</td>\n",
       "      <td>NaN</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>4098016</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.20000</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>67.974093</td>\n",
       "      <td>2.524911e+07</td>\n",
       "      <td>-0.060904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098017</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.92999</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>62.937431</td>\n",
       "      <td>2.534551e+07</td>\n",
       "      <td>-0.056113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098018</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>229.77000</td>\n",
       "      <td>133858.0</td>\n",
       "      <td>56.929345</td>\n",
       "      <td>3.075655e+07</td>\n",
       "      <td>0.217196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098019</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240.75999</td>\n",
       "      <td>133945.0</td>\n",
       "      <td>50.275961</td>\n",
       "      <td>3.224860e+07</td>\n",
       "      <td>0.065074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098020</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.23000</td>\n",
       "      <td>147543.0</td>\n",
       "      <td>43.810568</td>\n",
       "      <td>3.293602e+07</td>\n",
       "      <td>-0.109928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098021</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.28000</td>\n",
       "      <td>148015.0</td>\n",
       "      <td>39.647662</td>\n",
       "      <td>3.142062e+07</td>\n",
       "      <td>-0.208678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098022</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>234.78999</td>\n",
       "      <td>148693.0</td>\n",
       "      <td>35.479931</td>\n",
       "      <td>3.491163e+07</td>\n",
       "      <td>-0.117828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098023</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.00999</td>\n",
       "      <td>149239.0</td>\n",
       "      <td>32.877783</td>\n",
       "      <td>3.164016e+07</td>\n",
       "      <td>-0.148759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098024</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>204.03000</td>\n",
       "      <td>149825.0</td>\n",
       "      <td>31.848610</td>\n",
       "      <td>3.056879e+07</td>\n",
       "      <td>-0.178623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098025</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>197.73000</td>\n",
       "      <td>149891.0</td>\n",
       "      <td>31.658101</td>\n",
       "      <td>2.963795e+07</td>\n",
       "      <td>-0.044459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098026</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>189.39999</td>\n",
       "      <td>149964.0</td>\n",
       "      <td>30.269247</td>\n",
       "      <td>2.840318e+07</td>\n",
       "      <td>-0.177452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098027</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.69000</td>\n",
       "      <td>161561.0</td>\n",
       "      <td>26.444773</td>\n",
       "      <td>3.452397e+07</td>\n",
       "      <td>-0.109662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098028</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>251.92999</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>23.572926</td>\n",
       "      <td>4.072952e+07</td>\n",
       "      <td>0.317625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098029</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>249.99001</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>22.888288</td>\n",
       "      <td>4.041588e+07</td>\n",
       "      <td>0.302506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098030</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>278.29999</td>\n",
       "      <td>164164.0</td>\n",
       "      <td>23.023424</td>\n",
       "      <td>4.568684e+07</td>\n",
       "      <td>0.211211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098031</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>314.07001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>24.373707</td>\n",
       "      <td>5.158914e+07</td>\n",
       "      <td>0.304494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098032</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.01001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>27.942659</td>\n",
       "      <td>5.601430e+07</td>\n",
       "      <td>0.527617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098033</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>361.60999</td>\n",
       "      <td>166863.0</td>\n",
       "      <td>33.011217</td>\n",
       "      <td>6.033933e+07</td>\n",
       "      <td>0.703458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098034</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>323.47000</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>39.067422</td>\n",
       "      <td>5.398294e+07</td>\n",
       "      <td>0.377699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098035</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>355.89999</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>41.425934</td>\n",
       "      <td>5.939508e+07</td>\n",
       "      <td>0.678694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098036</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.10001</td>\n",
       "      <td>168017.0</td>\n",
       "      <td>45.341310</td>\n",
       "      <td>5.731060e+07</td>\n",
       "      <td>0.671813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098037</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>331.53000</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>48.118293</td>\n",
       "      <td>5.571925e+07</td>\n",
       "      <td>0.676680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098038</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>308.85001</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>50.128638</td>\n",
       "      <td>5.190749e+07</td>\n",
       "      <td>0.630676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098039</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>311.35001</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>51.099733</td>\n",
       "      <td>5.255495e+07</td>\n",
       "      <td>0.457017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098040</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>354.31000</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>51.847135</td>\n",
       "      <td>5.980647e+07</td>\n",
       "      <td>0.406383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098041</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>343.06000</td>\n",
       "      <td>168920.0</td>\n",
       "      <td>53.774889</td>\n",
       "      <td>5.794970e+07</td>\n",
       "      <td>0.372295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098042</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>266.13000</td>\n",
       "      <td>169750.0</td>\n",
       "      <td>54.813517</td>\n",
       "      <td>4.517557e+07</td>\n",
       "      <td>-0.043730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098043</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>293.89999</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>53.454523</td>\n",
       "      <td>4.990245e+07</td>\n",
       "      <td>-0.064221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098044</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>284.73001</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>53.322950</td>\n",
       "      <td>4.834545e+07</td>\n",
       "      <td>-0.165039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098045</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.95001</td>\n",
       "      <td>170516.0</td>\n",
       "      <td>53.369757</td>\n",
       "      <td>5.847846e+07</td>\n",
       "      <td>-0.051603</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4098046 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         PERMNO       date    DIVAMT        PRC    SHROUT  Rolling 36m Vol  \\\n",
       "0         10000 1985-12-31  0.000000   -1.00000      -1.0              NaN   \n",
       "1         10000 1986-01-31  0.000000    4.37500    3680.0              NaN   \n",
       "2         10000 1986-02-28  0.000000    3.25000    3680.0              NaN   \n",
       "3         10000 1986-03-31  0.000000    4.43750    3680.0              NaN   \n",
       "4         10000 1986-04-30  0.000000    4.00000    3793.0              NaN   \n",
       "5         10000 1986-05-31  0.000000    3.10938    3793.0              NaN   \n",
       "6         10000 1986-06-30  0.000000    3.09375    3793.0              NaN   \n",
       "7         10000 1986-07-31  0.000000    2.84375    3793.0              NaN   \n",
       "8         10000 1986-08-31  0.000000    1.09375    3793.0              NaN   \n",
       "9         10000 1986-09-30  0.000000    1.03125    3793.0              NaN   \n",
       "10        10000 1986-10-31  0.000000    0.78125    3843.0              NaN   \n",
       "11        10000 1986-11-30  0.000000    0.82813    3843.0              NaN   \n",
       "12        10000 1986-12-31  0.000000    0.51563    3843.0              NaN   \n",
       "13        10000 1987-01-31  0.000000    0.40625    3893.0              NaN   \n",
       "14        10000 1987-02-28  0.000000    0.40625    3893.0              NaN   \n",
       "15        10000 1987-03-31  0.000000    0.25000    3893.0              NaN   \n",
       "16        10000 1987-04-30  0.000000    0.23438    3893.0              NaN   \n",
       "17        10000 1987-05-31  0.000000    0.21875    3893.0              NaN   \n",
       "18        10000 1987-06-30  0.000000   -1.00000    3893.0              NaN   \n",
       "19        10001 1985-12-31  0.000000   -1.00000      -1.0              NaN   \n",
       "20        10001 1986-01-31  0.000000    6.12500     985.0              NaN   \n",
       "21        10001 1986-02-28  0.000000    6.25000     985.0              NaN   \n",
       "22        10001 1986-03-31  0.031667    6.31250     985.0              NaN   \n",
       "23        10001 1986-04-30  0.031667    6.37500     985.0              NaN   \n",
       "24        10001 1986-05-31  0.031667    6.31250     985.0              NaN   \n",
       "25        10001 1986-06-30  0.035000    6.12500     985.0              NaN   \n",
       "26        10001 1986-07-31  0.035000    6.06250     985.0              NaN   \n",
       "27        10001 1986-08-31  0.035000    6.50000     985.0              NaN   \n",
       "28        10001 1986-09-30  0.035000    6.37500     991.0              NaN   \n",
       "29        10001 1986-10-31  0.035000    6.62500     991.0              NaN   \n",
       "...         ...        ...       ...        ...       ...              ...   \n",
       "4098016   93436 2016-01-31  0.000000  191.20000  132056.0        67.974093   \n",
       "4098017   93436 2016-02-29  0.000000  191.92999  132056.0        62.937431   \n",
       "4098018   93436 2016-03-31  0.000000  229.77000  133858.0        56.929345   \n",
       "4098019   93436 2016-04-30  0.000000  240.75999  133945.0        50.275961   \n",
       "4098020   93436 2016-05-31  0.000000  223.23000  147543.0        43.810568   \n",
       "4098021   93436 2016-06-30  0.000000  212.28000  148015.0        39.647662   \n",
       "4098022   93436 2016-07-31  0.000000  234.78999  148693.0        35.479931   \n",
       "4098023   93436 2016-08-31  0.000000  212.00999  149239.0        32.877783   \n",
       "4098024   93436 2016-09-30  0.000000  204.03000  149825.0        31.848610   \n",
       "4098025   93436 2016-10-31  0.000000  197.73000  149891.0        31.658101   \n",
       "4098026   93436 2016-11-30  0.000000  189.39999  149964.0        30.269247   \n",
       "4098027   93436 2016-12-31  0.000000  213.69000  161561.0        26.444773   \n",
       "4098028   93436 2017-01-31  0.000000  251.92999  161670.0        23.572926   \n",
       "4098029   93436 2017-02-28  0.000000  249.99001  161670.0        22.888288   \n",
       "4098030   93436 2017-03-31  0.000000  278.29999  164164.0        23.023424   \n",
       "4098031   93436 2017-04-30  0.000000  314.07001  164260.0        24.373707   \n",
       "4098032   93436 2017-05-31  0.000000  341.01001  164260.0        27.942659   \n",
       "4098033   93436 2017-06-30  0.000000  361.60999  166863.0        33.011217   \n",
       "4098034   93436 2017-07-31  0.000000  323.47000  166887.0        39.067422   \n",
       "4098035   93436 2017-08-31  0.000000  355.89999  166887.0        41.425934   \n",
       "4098036   93436 2017-09-30  0.000000  341.10001  168017.0        45.341310   \n",
       "4098037   93436 2017-10-31  0.000000  331.53000  168067.0        48.118293   \n",
       "4098038   93436 2017-11-30  0.000000  308.85001  168067.0        50.128638   \n",
       "4098039   93436 2017-12-31  0.000000  311.35001  168797.0        51.099733   \n",
       "4098040   93436 2018-01-31  0.000000  354.31000  168797.0        51.847135   \n",
       "4098041   93436 2018-02-28  0.000000  343.06000  168920.0        53.774889   \n",
       "4098042   93436 2018-03-31  0.000000  266.13000  169750.0        54.813517   \n",
       "4098043   93436 2018-04-30  0.000000  293.89999  169794.0        53.454523   \n",
       "4098044   93436 2018-05-31  0.000000  284.73001  169794.0        53.322950   \n",
       "4098045   93436 2018-06-30  0.000000  342.95001  170516.0        53.369757   \n",
       "\n",
       "              Mkt Cap   12m Mom  \n",
       "0        1.000000e+00       NaN  \n",
       "1        1.610000e+04       NaN  \n",
       "2        1.196000e+04       NaN  \n",
       "3        1.633000e+04       NaN  \n",
       "4        1.517200e+04       NaN  \n",
       "5        1.179388e+04       NaN  \n",
       "6        1.173459e+04       NaN  \n",
       "7        1.078634e+04       NaN  \n",
       "8        4.148594e+03       NaN  \n",
       "9        3.911531e+03       NaN  \n",
       "10       3.002344e+03       NaN  \n",
       "11       3.182504e+03       NaN  \n",
       "12       1.981566e+03       NaN  \n",
       "13       1.581531e+03       NaN  \n",
       "14       1.581531e+03       NaN  \n",
       "15       9.732500e+02       NaN  \n",
       "16       9.124413e+02       NaN  \n",
       "17       8.515938e+02       NaN  \n",
       "18      -3.893000e+03       NaN  \n",
       "19       1.000000e+00       NaN  \n",
       "20       6.033125e+03       NaN  \n",
       "21       6.156250e+03       NaN  \n",
       "22       6.217812e+03       NaN  \n",
       "23       6.279375e+03       NaN  \n",
       "24       6.217812e+03       NaN  \n",
       "25       6.033125e+03       NaN  \n",
       "26       5.971562e+03       NaN  \n",
       "27       6.402500e+03       NaN  \n",
       "28       6.317625e+03       NaN  \n",
       "29       6.565375e+03       NaN  \n",
       "...               ...       ...  \n",
       "4098016  2.524911e+07 -0.060904  \n",
       "4098017  2.534551e+07 -0.056113  \n",
       "4098018  3.075655e+07  0.217196  \n",
       "4098019  3.224860e+07  0.065074  \n",
       "4098020  3.293602e+07 -0.109928  \n",
       "4098021  3.142062e+07 -0.208678  \n",
       "4098022  3.491163e+07 -0.117828  \n",
       "4098023  3.164016e+07 -0.148759  \n",
       "4098024  3.056879e+07 -0.178623  \n",
       "4098025  2.963795e+07 -0.044459  \n",
       "4098026  2.840318e+07 -0.177452  \n",
       "4098027  3.452397e+07 -0.109662  \n",
       "4098028  4.072952e+07  0.317625  \n",
       "4098029  4.041588e+07  0.302506  \n",
       "4098030  4.568684e+07  0.211211  \n",
       "4098031  5.158914e+07  0.304494  \n",
       "4098032  5.601430e+07  0.527617  \n",
       "4098033  6.033933e+07  0.703458  \n",
       "4098034  5.398294e+07  0.377699  \n",
       "4098035  5.939508e+07  0.678694  \n",
       "4098036  5.731060e+07  0.671813  \n",
       "4098037  5.571925e+07  0.676680  \n",
       "4098038  5.190749e+07  0.630676  \n",
       "4098039  5.255495e+07  0.457017  \n",
       "4098040  5.980647e+07  0.406383  \n",
       "4098041  5.794970e+07  0.372295  \n",
       "4098042  4.517557e+07 -0.043730  \n",
       "4098043  4.990245e+07 -0.064221  \n",
       "4098044  4.834545e+07 -0.165039  \n",
       "4098045  5.847846e+07 -0.051603  \n",
       "\n",
       "[4098046 rows x 8 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get 12m momentum\n",
    "# Given that all the stocks here have at least 36m of price, you can easily get their 12m momentum without filtering\n",
    "momentum_lst = [np.nan]*len(data)\n",
    "for ticker in dates_ind:\n",
    "    si, ei = dates_ind[ticker][0], dates_ind[ticker][1]\n",
    "    for i in range(si,ei+1):\n",
    "        momentum_lst[i] = (data['PRC'][i]-data['PRC'][i-12])/data['PRC'][i-12]\n",
    "data['12m Mom'] = momentum_lst        \n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv('data_12mom_36vol_div.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "stock_components = {}\n",
    "\n",
    "# find_stocks will save the tickers of the stocks available at given date and return a list of them\n",
    "def find_stocks(target_date):\n",
    "    stock_lst = []\n",
    "    for ticker in dates:\n",
    "        if dates[ticker][0] <= target_date and dates[ticker][1] >= target_date:\n",
    "            stock_lst.append(ticker)\n",
    "    return stock_lst\n",
    "\n",
    "\n",
    "for i in dates_month:\n",
    "    stock_components.update({i:find_stocks(i)})\n",
    "    \n",
    "print(time.time()-start_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Find the components of the portfolio for a given date \n",
    "def find_component(date,\n",
    "                  start_time):\n",
    "    date_df = []\n",
    "    for stock in stock_components[date]:\n",
    "        data_slice = pd.DataFrame(data[dates_ind[stock][0]:dates_ind[stock][1]+1])\n",
    "        n = np.where(data_slice['date']==date)\n",
    "        date_df.append(data_slice.iloc[n])\n",
    "    print(time.time()-start_time)\n",
    "    return pd.concat(date_df)\n",
    "\n",
    "frame = find_component(datetime.datetime(2018, 6, 30, 0, 0), time.time())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "frame = frame.sort_values(by=['Mkt Cap'],ascending=False)[0:1000]\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "''",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-10-9677233ea5e4>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Pick low-vol decile\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0mfil\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mframe\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'Mkt Cap'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mascending\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;36m1000\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mfil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m''\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0mform_portfolio\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36msort_values\u001b[1;34m(self, by, axis, ascending, inplace, kind, na_position)\u001b[0m\n\u001b[0;32m   4416\u001b[0m             \u001b[0mby\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mby\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4417\u001b[0m             k = self._get_label_or_level_values(by, axis=axis,\n\u001b[1;32m-> 4418\u001b[1;33m                                                 stacklevel=stacklevel)\n\u001b[0m\u001b[0;32m   4419\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4420\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mascending\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mtuple\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m_get_label_or_level_values\u001b[1;34m(self, key, axis, stacklevel)\u001b[0m\n\u001b[0;32m   1377\u001b[0m             \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_level_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1378\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1379\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1380\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1381\u001b[0m         \u001b[1;31m# Check for duplicates\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: ''"
     ]
    }
   ],
   "source": [
    "# Pick low-vol decile\n",
    "# The rebalancing happens every quarter\n",
    "# Take the 1000 most liquid stocks. Rank the 500 most low-vol stocks\n",
    "# Give scores based on 12m momentum and income (dividend), The higher they are the better\n",
    "\n",
    "def \n",
    "\n",
    "def form_portfolio():\n",
    "    for month in dates_month:\n",
    "        frame = find_component(month, time.time()).sort_values(by=['Mkt Cap'],ascending=False)\n",
    "        \n",
    "    \n",
    "def process_strat():\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Stock(object):\n",
    "    def __init__(self, ticker, buy_price, enter_time):\n",
    "        self.ticker = ticker\n",
    "        self.enter_price = buy_price\n",
    "        self.price_seq = [buy_price]\n",
    "        self.enter_time = enter_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}