Market Aggregation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pickle\n",
    "import collections\n",
    "from Graham.Aggregation import Aggregate\n",
    "from Graham.Standard import Standardize\n",
    "pd.set_option('mode.chained_assignment', None)\n",
    "pd.set_option('display.width', 1500)\n",
    "pd.set_option('display.precision', 2)\n",
    "pd.options.display.float_format = '{:20,.2f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s = Standardize('CL')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2007                   2.69\n",
       "2008                   2.69\n",
       "2009                   2.69\n",
       "2010                   2.30\n",
       "2011                   1.24\n",
       "2012                   1.11\n",
       "2013                   1.09\n",
       "2014                   0.59\n",
       "2015                  -0.18\n",
       "Name: WACC, dtype: float64"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.make_model()['WACC']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def aggregate():\n",
    "    \n",
    "    cash_ops = np.array(\"\"\"CashProvidedByUsedInOperatingActivitiesDiscontinuedOperations\n",
    "NetCashProvidedByUsedInOperatingActivities\n",
    "NetCashProvidedByUsedInOperatingActivitiesContinuingOperations\n",
    "NetCashProvidedByUsedInContinuingOperations\"\"\".split('\\n'))\n",
    "    \n",
    "    cash_invst = np.array(['NetCashProvidedByUsedInInvestingActivities',\n",
    "       'NetCashProvidedByUsedInInvestingActivitiesContinuingOperations'])\n",
    "    \n",
    "    cash_fin = np.array(['NetCashProvidedByUsedInFinancingActivities',\n",
    "       'ProceedsFromPaymentsForOtherFinancingActivities'])\n",
    "    \n",
    "    working = np.array(['ADS', 'AFL', 'AGN', 'AMZN', 'AON', 'BBY', 'BEN', 'BHI', 'BLL',\n",
    "       'CL', 'CRM', 'CXO', 'D', 'DLPH', 'EBAY', 'ED', 'ETFC', 'EXPD',\n",
    "       'FLS', 'GPC', 'GWW', 'HCP', 'HST', 'HUM', 'INTU', 'JEC', 'KHC',\n",
    "       'LH', 'MMM', 'NFLX', 'NVDA', 'ORCL', 'TSLA'], \n",
    "      dtype='<U4')\n",
    "    \n",
    "    stocks = {}\n",
    "    \n",
    "    for sym in working:\n",
    "        try:\n",
    "            s = Standardize(sym)\n",
    "            \n",
    "            df = pd.DataFrame({'Debt' : s.book_value_debt(), \n",
    "                              'Equity' : s.book_value_equity(),\n",
    "                              'FCF Equity' : s.free_cash_flow_equity(),\n",
    "                              'FCF Firm' : s.free_cash_flow_firm(),\n",
    "                              'EBIT' : s.ebit(), \n",
    "                              'WACC' : s.make_model()['WACC'], \n",
    "                              'CFO' : s.cash.loc[cash_ops].sum(), \n",
    "                              'CFF' : s.cash.loc[cash_fin].sum(), \n",
    "                              'CFI' : s.cash.loc[cash_invst].sum()}).fillna(0)\n",
    "            \n",
    "            stocks[sym] = df\n",
    "            \n",
    "        except:\n",
    "            pass\n",
    "    \n",
    "    panda = pd.Panel.from_dict(stocks, orient='minor').fillna(0)\n",
    "    return panda\n",
    "panda = aggregate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2006                  19.67\n",
       "2007                 790.77\n",
       "2008               1,103.09\n",
       "2009               1,048.50\n",
       "2010               1,383.21\n",
       "2011               1,631.03\n",
       "2012               1,708.18\n",
       "2013               1,399.79\n",
       "2014               1,875.42\n",
       "2015               1,073.48\n",
       "2016                 595.64\n",
       "dtype: float64"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "debt = panda['Debt']\n",
    "equity = panda['Equity']\n",
    "# panda['CFO']\n",
    "# panda['CFI']\n",
    "panda['EBIT'].mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CFF</th>\n",
       "      <th>CFI</th>\n",
       "      <th>CFO</th>\n",
       "      <th>Debt</th>\n",
       "      <th>EBIT</th>\n",
       "      <th>Equity</th>\n",
       "      <th>FCF Equity</th>\n",
       "      <th>FCF Firm</th>\n",
       "      <th>WACC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ADS</th>\n",
       "      <td>-715.67</td>\n",
       "      <td>-340.78</td>\n",
       "      <td>902.71</td>\n",
       "      <td>1,869.77</td>\n",
       "      <td>629.22</td>\n",
       "      <td>23.09</td>\n",
       "      <td>-3,160.59</td>\n",
       "      <td>-947.08</td>\n",
       "      <td>10.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AFL</th>\n",
       "      <td>217.00</td>\n",
       "      <td>-7,432.00</td>\n",
       "      <td>6,989.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3,734.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,641.00</td>\n",
       "      <td>3,883.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGN</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AMZN</th>\n",
       "      <td>181.00</td>\n",
       "      <td>-3,360.00</td>\n",
       "      <td>3,495.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1,406.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,200.00</td>\n",
       "      <td>2,350.00</td>\n",
       "      <td>4.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AON</th>\n",
       "      <td>1,838.00</td>\n",
       "      <td>-2,558.00</td>\n",
       "      <td>787.00</td>\n",
       "      <td>4,506.00</td>\n",
       "      <td>1,226.00</td>\n",
       "      <td>8,251.00</td>\n",
       "      <td>3,544.00</td>\n",
       "      <td>1,341.00</td>\n",
       "      <td>3.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BBY</th>\n",
       "      <td>-3,394.00</td>\n",
       "      <td>1,464.00</td>\n",
       "      <td>2,025.00</td>\n",
       "      <td>1,802.00</td>\n",
       "      <td>2,161.00</td>\n",
       "      <td>6,320.00</td>\n",
       "      <td>-508.00</td>\n",
       "      <td>175.00</td>\n",
       "      <td>3.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BHI</th>\n",
       "      <td>1,368.00</td>\n",
       "      <td>-2,376.00</td>\n",
       "      <td>856.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,288.00</td>\n",
       "      <td>861.00</td>\n",
       "      <td>5.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BLL</th>\n",
       "      <td>-478.50</td>\n",
       "      <td>-211.20</td>\n",
       "      <td>1,030.40</td>\n",
       "      <td>2,701.60</td>\n",
       "      <td>764.60</td>\n",
       "      <td>1,658.10</td>\n",
       "      <td>2,609.40</td>\n",
       "      <td>847.70</td>\n",
       "      <td>5.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CL</th>\n",
       "      <td>-2,624.00</td>\n",
       "      <td>-658.00</td>\n",
       "      <td>3,211.00</td>\n",
       "      <td>3,424.00</td>\n",
       "      <td>3,489.00</td>\n",
       "      <td>2,675.00</td>\n",
       "      <td>-2,227.00</td>\n",
       "      <td>4,133.00</td>\n",
       "      <td>2.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CRM</th>\n",
       "      <td>637.15</td>\n",
       "      <td>-378.62</td>\n",
       "      <td>270.91</td>\n",
       "      <td>463.68</td>\n",
       "      <td>115.27</td>\n",
       "      <td>1,043.80</td>\n",
       "      <td>217.21</td>\n",
       "      <td>127.26</td>\n",
       "      <td>3.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CXO</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>356.33</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>416.41</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>-2,230.00</td>\n",
       "      <td>419.00</td>\n",
       "      <td>1,825.00</td>\n",
       "      <td>31,664.00</td>\n",
       "      <td>5,700.00</td>\n",
       "      <td>11,997.00</td>\n",
       "      <td>2,038.00</td>\n",
       "      <td>4,763.00</td>\n",
       "      <td>1.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DLPH</th>\n",
       "      <td>-126.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1,142.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>940.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBAY</th>\n",
       "      <td>1,234.41</td>\n",
       "      <td>-2,282.47</td>\n",
       "      <td>2,745.76</td>\n",
       "      <td>1,794.23</td>\n",
       "      <td>2,053.57</td>\n",
       "      <td>15,302.18</td>\n",
       "      <td>1,597.63</td>\n",
       "      <td>361.54</td>\n",
       "      <td>4.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ED</th>\n",
       "      <td>-260.00</td>\n",
       "      <td>-1,998.00</td>\n",
       "      <td>2,205.00</td>\n",
       "      <td>9,750.00</td>\n",
       "      <td>1,922.00</td>\n",
       "      <td>10,136.00</td>\n",
       "      <td>1,045.00</td>\n",
       "      <td>2,603.00</td>\n",
       "      <td>3.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ETFC</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXPD</th>\n",
       "      <td>-208.59</td>\n",
       "      <td>-42.18</td>\n",
       "      <td>395.52</td>\n",
       "      <td>0.00</td>\n",
       "      <td>547.23</td>\n",
       "      <td>1,740.91</td>\n",
       "      <td>-897.66</td>\n",
       "      <td>-694.03</td>\n",
       "      <td>3.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLS</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>34.30</td>\n",
       "      <td>5.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GPC</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,793.82</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GWW</th>\n",
       "      <td>-578.59</td>\n",
       "      <td>0.00</td>\n",
       "      <td>596.45</td>\n",
       "      <td>42.77</td>\n",
       "      <td>860.48</td>\n",
       "      <td>0.00</td>\n",
       "      <td>325.87</td>\n",
       "      <td>883.67</td>\n",
       "      <td>11.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HCP</th>\n",
       "      <td>774.87</td>\n",
       "      <td>-430.93</td>\n",
       "      <td>580.50</td>\n",
       "      <td>0.00</td>\n",
       "      <td>619.36</td>\n",
       "      <td>7,957.37</td>\n",
       "      <td>-1,054.61</td>\n",
       "      <td>146.94</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HST</th>\n",
       "      <td>-343.00</td>\n",
       "      <td>-706.00</td>\n",
       "      <td>520.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>223.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>462.00</td>\n",
       "      <td>1,199.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HUM</th>\n",
       "      <td>-371.26</td>\n",
       "      <td>-1,810.99</td>\n",
       "      <td>2,241.79</td>\n",
       "      <td>1,668.85</td>\n",
       "      <td>1,854.62</td>\n",
       "      <td>6,924.06</td>\n",
       "      <td>747.53</td>\n",
       "      <td>1,600.04</td>\n",
       "      <td>3.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INTU</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JEC</th>\n",
       "      <td>93.55</td>\n",
       "      <td>-409.91</td>\n",
       "      <td>313.41</td>\n",
       "      <td>79.91</td>\n",
       "      <td>643.06</td>\n",
       "      <td>2,859.05</td>\n",
       "      <td>371.46</td>\n",
       "      <td>591.37</td>\n",
       "      <td>4.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KHC</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LH</th>\n",
       "      <td>515.30</td>\n",
       "      <td>-1,317.50</td>\n",
       "      <td>883.60</td>\n",
       "      <td>2,188.40</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2,466.30</td>\n",
       "      <td>-68.90</td>\n",
       "      <td>-28.60</td>\n",
       "      <td>2.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MMM</th>\n",
       "      <td>-2,135.00</td>\n",
       "      <td>-2,689.00</td>\n",
       "      <td>5,174.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5,918.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>4,090.00</td>\n",
       "      <td>6,148.00</td>\n",
       "      <td>3.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NFLX</th>\n",
       "      <td>-100.05</td>\n",
       "      <td>-116.08</td>\n",
       "      <td>276.40</td>\n",
       "      <td>234.12</td>\n",
       "      <td>283.64</td>\n",
       "      <td>290.16</td>\n",
       "      <td>357.02</td>\n",
       "      <td>501.22</td>\n",
       "      <td>5.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NVDA</th>\n",
       "      <td>62.16</td>\n",
       "      <td>-519.33</td>\n",
       "      <td>487.81</td>\n",
       "      <td>24.45</td>\n",
       "      <td>-98.94</td>\n",
       "      <td>2,665.14</td>\n",
       "      <td>-178.41</td>\n",
       "      <td>-206.05</td>\n",
       "      <td>5.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORCL</th>\n",
       "      <td>2,664.00</td>\n",
       "      <td>-10,319.00</td>\n",
       "      <td>8,681.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9,062.00</td>\n",
       "      <td>30,798.00</td>\n",
       "      <td>2,730.00</td>\n",
       "      <td>7,400.00</td>\n",
       "      <td>6.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TSLA</th>\n",
       "      <td>338.05</td>\n",
       "      <td>-180.30</td>\n",
       "      <td>-127.82</td>\n",
       "      <td>72.60</td>\n",
       "      <td>-146.84</td>\n",
       "      <td>207.05</td>\n",
       "      <td>-111.77</td>\n",
       "      <td>-175.43</td>\n",
       "      <td>4.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      CFF                  CFI                  CFO                 Debt                 EBIT               Equity           FCF Equity             FCF Firm                 WACC\n",
       "ADS               -715.67              -340.78               902.71             1,869.77               629.22                23.09            -3,160.59              -947.08                10.65\n",
       "AFL                217.00            -7,432.00             6,989.00                 0.00             3,734.00                 0.00             2,641.00             3,883.00                 0.00\n",
       "AGN                  0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00\n",
       "AMZN               181.00            -3,360.00             3,495.00                 0.00             1,406.00                 0.00             2,200.00             2,350.00                 4.51\n",
       "AON              1,838.00            -2,558.00               787.00             4,506.00             1,226.00             8,251.00             3,544.00             1,341.00                 3.81\n",
       "BBY             -3,394.00             1,464.00             2,025.00             1,802.00             2,161.00             6,320.00              -508.00               175.00                 3.92\n",
       "BHI              1,368.00            -2,376.00               856.00                 0.00                 0.00                 0.00             2,288.00               861.00                 5.10\n",
       "BLL               -478.50              -211.20             1,030.40             2,701.60               764.60             1,658.10             2,609.40               847.70                 5.29\n",
       "CL              -2,624.00              -658.00             3,211.00             3,424.00             3,489.00             2,675.00            -2,227.00             4,133.00                 2.30\n",
       "CRM                637.15              -378.62               270.91               463.68               115.27             1,043.80               217.21               127.26                 3.93\n",
       "CXO                  0.00                 0.00                 0.00                 0.00               356.33                 0.00                 0.00               416.41                 5.99\n",
       "D               -2,230.00               419.00             1,825.00            31,664.00             5,700.00            11,997.00             2,038.00             4,763.00                 1.94\n",
       "DLPH              -126.00                 0.00             1,142.00                 0.00               940.00                 0.00                 0.00                 0.00                 5.06\n",
       "EBAY             1,234.41            -2,282.47             2,745.76             1,794.23             2,053.57            15,302.18             1,597.63               361.54                 4.04\n",
       "ED                -260.00            -1,998.00             2,205.00             9,750.00             1,922.00            10,136.00             1,045.00             2,603.00                 3.04\n",
       "ETFC                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 6.47\n",
       "EXPD              -208.59               -42.18               395.52                 0.00               547.23             1,740.91              -897.66              -694.03                 3.92\n",
       "FLS                  0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                34.30                 5.31\n",
       "GPC                  0.00                 0.00                 0.00                 0.00                 0.00             2,793.82                 0.00                 0.00                 4.44\n",
       "GWW               -578.59                 0.00               596.45                42.77               860.48                 0.00               325.87               883.67                11.50\n",
       "HCP                774.87              -430.93               580.50                 0.00               619.36             7,957.37            -1,054.61               146.94                 0.00\n",
       "HST               -343.00              -706.00               520.00                 0.00               223.00                 0.00               462.00             1,199.00                 0.00\n",
       "HUM               -371.26            -1,810.99             2,241.79             1,668.85             1,854.62             6,924.06               747.53             1,600.04                 3.92\n",
       "INTU                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 6.06\n",
       "JEC                 93.55              -409.91               313.41                79.91               643.06             2,859.05               371.46               591.37                 4.95\n",
       "KHC                  0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00                 0.00\n",
       "LH                 515.30            -1,317.50               883.60             2,188.40                 0.00             2,466.30               -68.90               -28.60                 2.09\n",
       "MMM             -2,135.00            -2,689.00             5,174.00                 0.00             5,918.00                 0.00             4,090.00             6,148.00                 3.78\n",
       "NFLX              -100.05              -116.08               276.40               234.12               283.64               290.16               357.02               501.22                 5.26\n",
       "NVDA                62.16              -519.33               487.81                24.45               -98.94             2,665.14              -178.41              -206.05                 5.25\n",
       "ORCL             2,664.00           -10,319.00             8,681.00                 0.00             9,062.00            30,798.00             2,730.00             7,400.00                 6.06\n",
       "TSLA               338.05              -180.30              -127.82                72.60              -146.84               207.05              -111.77              -175.43                 4.38"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# panda.major_xs('2010')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Market Debt Funding</th>\n",
       "      <th>Market Equity Funding</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>0.03</td>\n",
       "      <td>0.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>0.24</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>0.39</td>\n",
       "      <td>0.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>0.33</td>\n",
       "      <td>0.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>0.30</td>\n",
       "      <td>0.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>0.30</td>\n",
       "      <td>0.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>0.28</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>0.33</td>\n",
       "      <td>0.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>0.37</td>\n",
       "      <td>0.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>0.05</td>\n",
       "      <td>0.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Market Debt Funding  Market Equity Funding\n",
       "2006                  nan                    nan\n",
       "2007                 0.03                   0.97\n",
       "2008                 0.24                   0.76\n",
       "2009                 0.39                   0.61\n",
       "2010                 0.33                   0.67\n",
       "2011                 0.30                   0.70\n",
       "2012                 0.30                   0.70\n",
       "2013                 0.28                   0.72\n",
       "2014                 0.33                   0.67\n",
       "2015                 0.37                   0.63\n",
       "2016                 0.05                   0.95"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "de = panda[['Debt', 'Equity']].to_frame()\n",
    "de = de.swaplevel(0, 1).unstack(0).swaplevel(0, 1, axis=1)\n",
    "total_capital = de.T.reset_index(0).groupby('minor').sum().T\n",
    "eq_pct = equity.sum() / total_capital.sum()\n",
    "dbt_pct = debt.sum() / total_capital.sum()\n",
    "\n",
    "\n",
    "pd.DataFrame({'Market Equity Funding' : equity.sum(axis=1) / total_capital.sum(axis=1),\n",
    " 'Market Debt Funding' : debt.sum(axis=1) / total_capital.sum(axis=1)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['CashProvidedByUsedInOperatingActivitiesDiscontinuedOperations',\n",
       "       'NetCashProvidedByUsedInOperatingActivities',\n",
       "       'NetCashProvidedByUsedInOperatingActivitiesContinuingOperations',\n",
       "       'NetCashProvidedByUsedInContinuingOperations'], \n",
       "      dtype='<U62')"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cash_ops = np.array(\"\"\"CashProvidedByUsedInOperatingActivitiesDiscontinuedOperations\n",
    "NetCashProvidedByUsedInOperatingActivities\n",
    "NetCashProvidedByUsedInOperatingActivitiesContinuingOperations\n",
    "NetCashProvidedByUsedInContinuingOperations\"\"\".split('\\n'))\n",
    "cash_ops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2007               2,252.00\n",
       "2008               2,302.00\n",
       "2009               3,277.00\n",
       "2010               3,211.00\n",
       "2011               2,896.00\n",
       "2012               3,196.00\n",
       "2013               3,204.00\n",
       "2014               3,298.00\n",
       "2015               2,949.00\n",
       "dtype: float64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.cash.loc[cash_ops].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# panda = pd.Panel.from_dict(data, orient='minor').fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cash_invst = np.array(['NetCashProvidedByUsedInInvestingActivities',\n",
    "       'NetCashProvidedByUsedInInvestingActivitiesContinuingOperations'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['NetCashProvidedByUsedInFinancingActivities',\n",
       "       'ProceedsFromPaymentsForOtherFinancingActivities'], \n",
       "      dtype='<U47')"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.array(\"\"\"NetCashProvidedByUsedInFinancingActivities\n",
    "ProceedsFromPaymentsForOtherFinancingActivities\"\"\".split('\\n'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "income = Standardize(sym).income\n",
    "fin = income.loc[['Revenues', 'OperatingExpenses', 'OperatingIncomeLoss']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Revenues</th>\n",
       "      <th>TSLA</th>\n",
       "      <td>111.94</td>\n",
       "      <td>116.74</td>\n",
       "      <td>204.24</td>\n",
       "      <td>413.26</td>\n",
       "      <td>2,013.50</td>\n",
       "      <td>3,198.36</td>\n",
       "      <td>4,046.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingExpenses</th>\n",
       "      <th>TSLA</th>\n",
       "      <td>61.43</td>\n",
       "      <td>177.57</td>\n",
       "      <td>313.08</td>\n",
       "      <td>424.35</td>\n",
       "      <td>517.54</td>\n",
       "      <td>1,068.36</td>\n",
       "      <td>1,640.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingIncomeLoss</th>\n",
       "      <th>TSLA</th>\n",
       "      <td>-51.90</td>\n",
       "      <td>-146.84</td>\n",
       "      <td>-251.49</td>\n",
       "      <td>-394.28</td>\n",
       "      <td>-61.28</td>\n",
       "      <td>-186.69</td>\n",
       "      <td>-716.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         2009                 2010                 2011                 2012                 2013                 2014                 2015\n",
       "Revenues            TSLA               111.94               116.74               204.24               413.26             2,013.50             3,198.36             4,046.03\n",
       "OperatingExpenses   TSLA                61.43               177.57               313.08               424.35               517.54             1,068.36             1,640.13\n",
       "OperatingIncomeLoss TSLA               -51.90              -146.84              -251.49              -394.28               -61.28              -186.69              -716.63"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin.index = pd.MultiIndex.from_product([fin.index.values, [sym]])\n",
    "fin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Revenues</th>\n",
       "      <th>AFL</th>\n",
       "      <td>15,393.00</td>\n",
       "      <td>16,554.00</td>\n",
       "      <td>18,254.00</td>\n",
       "      <td>20,732.00</td>\n",
       "      <td>22,171.00</td>\n",
       "      <td>25,364.00</td>\n",
       "      <td>23,939.00</td>\n",
       "      <td>22,728.00</td>\n",
       "      <td>20,872.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingExpenses</th>\n",
       "      <th>AFL</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingIncomeLoss</th>\n",
       "      <th>AFL</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        2007                 2008                 2009                 2010                 2011                 2012                 2013                 2014                 2015\n",
       "Revenues            AFL            15,393.00            16,554.00            18,254.00            20,732.00            22,171.00            25,364.00            23,939.00            22,728.00            20,872.00\n",
       "OperatingExpenses   AFL                  nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan\n",
       "OperatingIncomeLoss AFL                  nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i2 = Standardize('AFL').income.loc[['Revenues', 'OperatingExpenses', 'OperatingIncomeLoss']]\n",
    "i2.index = pd.MultiIndex.from_product([i2.index.values, ['AFL']])\n",
    "i2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Revenues</th>\n",
       "      <th>AFL</th>\n",
       "      <td>15,393.00</td>\n",
       "      <td>16,554.00</td>\n",
       "      <td>18,254.00</td>\n",
       "      <td>20,732.00</td>\n",
       "      <td>22,171.00</td>\n",
       "      <td>25,364.00</td>\n",
       "      <td>23,939.00</td>\n",
       "      <td>22,728.00</td>\n",
       "      <td>20,872.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingExpenses</th>\n",
       "      <th>AFL</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingIncomeLoss</th>\n",
       "      <th>AFL</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Revenues</th>\n",
       "      <th>TSLA</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>111.94</td>\n",
       "      <td>116.74</td>\n",
       "      <td>204.24</td>\n",
       "      <td>413.26</td>\n",
       "      <td>2,013.50</td>\n",
       "      <td>3,198.36</td>\n",
       "      <td>4,046.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingExpenses</th>\n",
       "      <th>TSLA</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>61.43</td>\n",
       "      <td>177.57</td>\n",
       "      <td>313.08</td>\n",
       "      <td>424.35</td>\n",
       "      <td>517.54</td>\n",
       "      <td>1,068.36</td>\n",
       "      <td>1,640.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OperatingIncomeLoss</th>\n",
       "      <th>TSLA</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>-51.90</td>\n",
       "      <td>-146.84</td>\n",
       "      <td>-251.49</td>\n",
       "      <td>-394.28</td>\n",
       "      <td>-61.28</td>\n",
       "      <td>-186.69</td>\n",
       "      <td>-716.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         2007                 2008                 2009                 2010                 2011                 2012                 2013                 2014                 2015\n",
       "Revenues            AFL             15,393.00            16,554.00            18,254.00            20,732.00            22,171.00            25,364.00            23,939.00            22,728.00            20,872.00\n",
       "OperatingExpenses   AFL                   nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan\n",
       "OperatingIncomeLoss AFL                   nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan                  nan\n",
       "Revenues            TSLA                  nan                  nan               111.94               116.74               204.24               413.26             2,013.50             3,198.36             4,046.03\n",
       "OperatingExpenses   TSLA                  nan                  nan                61.43               177.57               313.08               424.35               517.54             1,068.36             1,640.13\n",
       "OperatingIncomeLoss TSLA                  nan                  nan               -51.90              -146.84              -251.49              -394.28               -61.28              -186.69              -716.63"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([i2, fin])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}