list.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "num = {'xlb': 2,\n",
    " 'xle': 6,\n",
    " 'xlf': 14,\n",
    " 'xli': 10,\n",
    " 'xlk': 14,\n",
    " 'xlp': 10,\n",
    " 'xlre': 4,\n",
    " 'xlu': 6,\n",
    " 'xlv': 10,\n",
    " 'xly': 24}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'xlb': 2,\n",
       " 'xle': 6,\n",
       " 'xlf': 14,\n",
       " 'xli': 10,\n",
       " 'xlk': 14,\n",
       " 'xlp': 10,\n",
       " 'xlre': 4,\n",
       " 'xlu': 6,\n",
       " 'xlv': 10,\n",
       " 'xly': 24}"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ticker = ['xlb',\n",
    " 'xle',\n",
    " 'xlf',\n",
    " 'xli',\n",
    " 'xlk',\n",
    " 'xlp',\n",
    " 'xlre',\n",
    " 'xlu',\n",
    " 'xlv',\n",
    " 'xly']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Alpha_Three_Factor</th>\n",
       "      <th>Sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BHF</th>\n",
       "      <td>-0.012673</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SCG</th>\n",
       "      <td>-0.002586</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>-0.001994</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P</th>\n",
       "      <td>-0.001847</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EVHC</th>\n",
       "      <td>-0.001326</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ICPT</th>\n",
       "      <td>-0.000977</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WBA</th>\n",
       "      <td>-0.000634</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LPNT</th>\n",
       "      <td>-0.000586</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>THS</th>\n",
       "      <td>-0.000547</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RGC</th>\n",
       "      <td>-0.000465</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORLY</th>\n",
       "      <td>-0.000448</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BPOP</th>\n",
       "      <td>-0.000344</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AHL</th>\n",
       "      <td>-0.000317</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>-0.000297</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRT</th>\n",
       "      <td>-0.000157</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AXS</th>\n",
       "      <td>-0.000106</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SJM</th>\n",
       "      <td>-0.000089</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRGO</th>\n",
       "      <td>-0.000075</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AIG</th>\n",
       "      <td>-0.000074</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O</th>\n",
       "      <td>-0.000073</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LB</th>\n",
       "      <td>0.000003</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>XOG</th>\n",
       "      <td>0.000054</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WU</th>\n",
       "      <td>0.000088</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HBI</th>\n",
       "      <td>0.000129</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MOS</th>\n",
       "      <td>0.000137</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSCO</th>\n",
       "      <td>0.000203</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNVR</th>\n",
       "      <td>0.000208</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGO</th>\n",
       "      <td>0.000224</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CINF</th>\n",
       "      <td>0.000249</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM</th>\n",
       "      <td>0.000250</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALL</th>\n",
       "      <td>0.001771</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BAC</th>\n",
       "      <td>0.001780</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ROL</th>\n",
       "      <td>0.001824</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CNA</th>\n",
       "      <td>0.001841</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>0.001869</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RGA</th>\n",
       "      <td>0.001877</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>STZ</th>\n",
       "      <td>0.001923</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JBGS</th>\n",
       "      <td>0.001925</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PNR</th>\n",
       "      <td>0.001925</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLK</th>\n",
       "      <td>0.001939</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZTS</th>\n",
       "      <td>0.001997</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMG</th>\n",
       "      <td>0.002017</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA</th>\n",
       "      <td>0.002140</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USG</th>\n",
       "      <td>0.002149</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PBF</th>\n",
       "      <td>0.002158</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SEIC</th>\n",
       "      <td>0.002163</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YUMC</th>\n",
       "      <td>0.002227</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FND</th>\n",
       "      <td>0.002296</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VRSN</th>\n",
       "      <td>0.002298</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HPQ</th>\n",
       "      <td>0.002301</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CACC</th>\n",
       "      <td>0.002472</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TDC</th>\n",
       "      <td>0.002515</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMP</th>\n",
       "      <td>0.002519</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALLY</th>\n",
       "      <td>0.002550</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BGCP</th>\n",
       "      <td>0.002552</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CBOE</th>\n",
       "      <td>0.002748</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ON</th>\n",
       "      <td>0.002765</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MTCH</th>\n",
       "      <td>0.002823</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NBIX</th>\n",
       "      <td>0.004027</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JUNO</th>\n",
       "      <td>0.006500</td>\n",
       "      <td>xlf</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Alpha_Three_Factor Sector\n",
       "BHF            -0.012673    xlf\n",
       "SCG            -0.002586    xlf\n",
       "FL             -0.001994    xlf\n",
       "P              -0.001847    xlf\n",
       "EVHC           -0.001326    xlf\n",
       "ICPT           -0.000977    xlf\n",
       "WBA            -0.000634    xlf\n",
       "LPNT           -0.000586    xlf\n",
       "THS            -0.000547    xlf\n",
       "RGC            -0.000465    xlf\n",
       "ORLY           -0.000448    xlf\n",
       "BPOP           -0.000344    xlf\n",
       "AHL            -0.000317    xlf\n",
       "MD             -0.000297    xlf\n",
       "FRT            -0.000157    xlf\n",
       "AXS            -0.000106    xlf\n",
       "SJM            -0.000089    xlf\n",
       "PRGO           -0.000075    xlf\n",
       "AIG            -0.000074    xlf\n",
       "O              -0.000073    xlf\n",
       "LB              0.000003    xlf\n",
       "XOG             0.000054    xlf\n",
       "WU              0.000088    xlf\n",
       "HBI             0.000129    xlf\n",
       "MOS             0.000137    xlf\n",
       "TSCO            0.000203    xlf\n",
       "UNVR            0.000208    xlf\n",
       "AGO             0.000224    xlf\n",
       "CINF            0.000249    xlf\n",
       "IBM             0.000250    xlf\n",
       "...                  ...    ...\n",
       "ALL             0.001771    xlf\n",
       "BAC             0.001780    xlf\n",
       "ROL             0.001824    xlf\n",
       "CNA             0.001841    xlf\n",
       "SC              0.001869    xlf\n",
       "RGA             0.001877    xlf\n",
       "STZ             0.001923    xlf\n",
       "JBGS            0.001925    xlf\n",
       "PNR             0.001925    xlf\n",
       "BLK             0.001939    xlf\n",
       "ZTS             0.001997    xlf\n",
       "AMG             0.002017    xlf\n",
       "TSLA            0.002140    xlf\n",
       "USG             0.002149    xlf\n",
       "PBF             0.002158    xlf\n",
       "SEIC            0.002163    xlf\n",
       "YUMC            0.002227    xlf\n",
       "FND             0.002296    xlf\n",
       "VRSN            0.002298    xlf\n",
       "HPQ             0.002301    xlf\n",
       "CACC            0.002472    xlf\n",
       "TDC             0.002515    xlf\n",
       "AMP             0.002519    xlf\n",
       "ALLY            0.002550    xlf\n",
       "BGCP            0.002552    xlf\n",
       "CBOE            0.002748    xlf\n",
       "ON              0.002765    xlf\n",
       "MTCH            0.002823    xlf\n",
       "NBIX            0.004027    xlf\n",
       "JUNO            0.006500    xlf\n",
       "\n",
       "[141 rows x 2 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('./rank_result/xlf_rank.csv', index_col =0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('./rank_result/xlf_rank.csv', index_col =0).index[:num['xlf']/2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['BHF', 'SCG', 'FL', 'P', 'EVHC', 'ICPT', 'WBA']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('./rank_result/xlf_rank.csv', index_col =0).index[-num['xlf']/2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ALLY', 'BGCP', 'CBOE', 'ON', 'MTCH', 'NBIX', 'JUNO']"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "long_ticker = []\n",
    "for s in ticker:\n",
    "    long_ticker +=(pd.read_csv('./rank_result/'+s+'_rank.csv', index_col =0).index[:num[s]/2]).tolist()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "50"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(long_ticker)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "short_ticker = []\n",
    "for s in ticker:\n",
    "    short_ticker +=(pd.read_csv('./rank_result/'+s+'_rank.csv', index_col =0).index[-num[s]/2:]).tolist()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "50"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(short_ticker)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "long_ticker = []\n",
    "for s in ticker:\n",
    "    long_ticker +=(pd.read_csv('./rank_result/'+s+'_rank.csv', index_col =0).index[:num[s]/2]).tolist()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.Series(short_ticker).to_csv('./short.csv')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "long_price =pd.DataFrame()\n",
    "for s in long_ticker:\n",
    "    long_price[s] = pd.read_csv('./price_data/'+s+'.csv', index_col = 'Date')['Adj Close']\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "long_price.to_csv('./long_price.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "short_price =pd.DataFrame()\n",
    "for s in short_ticker:\n",
    "    short_price[s] = pd.read_csv('./price_data/'+s+'.csv', index_col = 'Date')['Adj Close']\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "short_price.to_csv('./short_price.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df =pd.read_csv('./OpenPosition_11_27_2017.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Currency</th>\n",
       "      <th>LastPrice</th>\n",
       "      <th>PricePaid</th>\n",
       "      <th>DayChange</th>\n",
       "      <th>ProfitLoss</th>\n",
       "      <th>MarketValue</th>\n",
       "      <th>ProfitLossPercentage</th>\n",
       "      <th>betaValue</th>\n",
       "      <th>beta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA</td>\n",
       "      <td>Alcoa Inc.</td>\n",
       "      <td>-463</td>\n",
       "      <td>USD</td>\n",
       "      <td>42.04</td>\n",
       "      <td>43.07</td>\n",
       "      <td>-0.17</td>\n",
       "      <td>476.89</td>\n",
       "      <td>19464.52</td>\n",
       "      <td>2.39</td>\n",
       "      <td>3.04987</td>\n",
       "      <td>3.04987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAP</td>\n",
       "      <td>Advance Auto Parts Inc.</td>\n",
       "      <td>247</td>\n",
       "      <td>USD</td>\n",
       "      <td>89.87</td>\n",
       "      <td>81.22</td>\n",
       "      <td>0.64</td>\n",
       "      <td>2,136.55</td>\n",
       "      <td>22197.89</td>\n",
       "      <td>10.65</td>\n",
       "      <td>0.91066</td>\n",
       "      <td>0.91066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AES</td>\n",
       "      <td>The AES Corporation</td>\n",
       "      <td>1955</td>\n",
       "      <td>USD</td>\n",
       "      <td>10.74</td>\n",
       "      <td>10.25</td>\n",
       "      <td>0.05</td>\n",
       "      <td>957.95</td>\n",
       "      <td>20996.70</td>\n",
       "      <td>4.78</td>\n",
       "      <td>1.23094</td>\n",
       "      <td>1.23094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AHL</td>\n",
       "      <td>Aspen Insurance Holdings Limited</td>\n",
       "      <td>474</td>\n",
       "      <td>USD</td>\n",
       "      <td>41.00</td>\n",
       "      <td>42.20</td>\n",
       "      <td>0.45</td>\n",
       "      <td>-568.8</td>\n",
       "      <td>19434.00</td>\n",
       "      <td>-2.84</td>\n",
       "      <td>0.76347</td>\n",
       "      <td>0.76347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AKAM</td>\n",
       "      <td>Akamai Technologies Inc.</td>\n",
       "      <td>369</td>\n",
       "      <td>USD</td>\n",
       "      <td>55.35</td>\n",
       "      <td>54.25</td>\n",
       "      <td>-0.44</td>\n",
       "      <td>405.9</td>\n",
       "      <td>20424.15</td>\n",
       "      <td>2.03</td>\n",
       "      <td>0.65281</td>\n",
       "      <td>0.65281</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Symbol                       Description  Quantity Currency  LastPrice  \\\n",
       "0     AA                        Alcoa Inc.      -463      USD      42.04   \n",
       "1    AAP           Advance Auto Parts Inc.       247      USD      89.87   \n",
       "2    AES               The AES Corporation      1955      USD      10.74   \n",
       "3    AHL  Aspen Insurance Holdings Limited       474      USD      41.00   \n",
       "4   AKAM         Akamai Technologies Inc.        369      USD      55.35   \n",
       "\n",
       "   PricePaid  DayChange ProfitLoss  MarketValue  ProfitLossPercentage  \\\n",
       "0      43.07      -0.17     476.89     19464.52                  2.39   \n",
       "1      81.22       0.64   2,136.55     22197.89                 10.65   \n",
       "2      10.25       0.05     957.95     20996.70                  4.78   \n",
       "3      42.20       0.45     -568.8     19434.00                 -2.84   \n",
       "4      54.25      -0.44      405.9     20424.15                  2.03   \n",
       "\n",
       "   betaValue     beta  \n",
       "0    3.04987  3.04987  \n",
       "1    0.91066  0.91066  \n",
       "2    1.23094  1.23094  \n",
       "3    0.76347  0.76347  \n",
       "4    0.65281  0.65281  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_long =df.Symbol[(df['Quantity']>0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_short =df.Symbol[(df['Quantity']<0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "old = old_long.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(list(set(old) & set(long_ticker))).to_csv('./sell.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(list(set(old_short) & set(short_ticker))).to_csv('./short_buy.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}