{"nbformat": 4, "nbformat_minor": 0, "metadata": {"kernelspec": {"language": "python", "name": "python3", "display_name": "Python 3"}, "language_info": {"pygments_lexer": "ipython3", "codemirror_mode": {"version": 3, "name": "ipython"}, "mimetype": "text/x-python", "nbconvert_exporter": "python", "version": "3.4.3", "name": "python", "file_extension": ".py"}}, "cells": [{"cell_type": "markdown", "source": "#### **Using Implied Cost of Capital as a Sector ETF Valuation Tool**\n#### _[Monday 7.6.2015]_\n------------\n\n***This is a follow-up to these posts [here](http://blkarb.se/1FCBL1R) and [here](http://blkarb.se/1HJ3tsR) where I detail the `ICC` methodology used. All the data and plots have been updated and reflects current information. Scroll to the bottom to see the results of our previous predictions.*** \n\nThe following IPython Notebook examines the **Implied Cost of Capital (ICC)** method of valuation for purposes of trade/portfolio positioning. The **ICC** model is a forward looking estimate that uses earnings forecasts to calculate an implied earnings growth rate. The goal of this analysis is to identify asymmetric investing opportunities due to incongruence between *\"recent\"* historical returns and forward looking expectations of earnings growth (as measured by the ICC). \n\n**_Please note:_** _there will be some category overlap as some of the groupings include international sector ETF's while other groupings contain regional and/or country ETF's._ \n____\n", "metadata": {"hide_input": false, "run_control": {"marked": true}}}, {"execution_count": 1, "source": "# ================================================================== #\n# composite returns; vol; risk adjusted returns; correlation matrix, ICC analysis\n\nimport pandas as pd\nimport numpy as np\nimport pandas.io.data as web\nfrom pandas.tseries.offsets import *\nimport datetime as dt\n\npd.set_option('colheader_justify', 'right')\n\nimport matplotlib.pyplot as plt\n%matplotlib inline\nsize=(10,8)\n\nimport seaborn as sns\nsns.set_style('white')\n\nimport plotly.plotly as py\nfrom plotly.graph_objs import *\nimport plotly.tools as tls\n\nimport cufflinks ", "cell_type": "code", "outputs": [], "metadata": {"trusted": true, "collapsed": true}}, {"execution_count": 2, "source": "# ================================================================== #\n# datetime management\n\nnum_weeks = 3 # number of weeks since last update\n\ndate_today = dt.date.today()\nd_mon, d_day = date_today.month, date_today.day\n\nprev_date_today = date_today - num_weeks * Week(weekday=0) # weekday 0 = Monday\npre_d_mon, pre_d_day = prev_date_today.month, prev_date_today.day\n\npprev_dt_today = date_today - 2 * num_weeks * Week(weekday=0)\npprev_d_mon, pprev_d_day = pprev_dt_today.month, pprev_dt_today.day\n\nlast_month = date_today - 21 * BDay() # switch to 21\none_year_ago = date_today - 252 * BDay()", "cell_type": "code", "outputs": [], "metadata": {"trusted": true, "collapsed": false}}, {"execution_count": 3, "source": "# ~~~ Market Cap ~~~ #\nBroad_mkts = ['THRK','RSCO'] # Russell 3000, Russell Small Cap Completeness\nLarge_cap = ['ONEK','SPY','SPYG','SPYV'] # Russell 1000, sp500 (growth, value)\nMid_cap = ['MDY','MDYG', 'MDYV'] # sp400 mid (growth, value)\nSmall_cap = ['TWOK','SLY','SLYG','SLYV'] # russ 2K, sp600, (growth, value)\n\n# ~~~ International/Global Equities ~~~ #\nGlobal = [\n 'DGT', # global dow\n 'BIK', # sp BRIC 40 ETF\n 'GMM', # sp emerging mkts \n 'EWX', # sp emerging mkts small caps\n 'CWI', # msci acwi ex-US\n 'GII', # global infrastructure\n 'GNR', # global natural resources\n 'DWX', # intl dividends\n 'GWL', # sp developed world ex-US \n 'MDD', # intl mid cap (2B-5B USD)\n 'GWX' # intl small cap (<2B USD)\n ]\n\nAsia = ['JPP','JSC','GXC','GMF'] # japan, smallcap japan, china, emg asiapac\nEurope = ['FEZ','GUR','RBL','FEU'] # euro stoxx 50, emg europe, russia, stoxx europe 50\nLatam = ['GML'] # emg latin america\nAfrica = ['GAF'] # emg mideast/africa\n\n# ~~~ Real Assets ~~~ #\nReal_assets = [ 'RWO', # global real estate\n 'RWX', # intl real estate ex-US\n 'RWR' # US select REIT\n ] \n\n# ~~~ sectors and industries ETF's ~~~ #\nSector = [\n 'XLY','XHB','IPD','XRT', # consumer discretionary\n 'XLP','IPS', # consumer staples\n 'XLE','IPW','XES','XOP', # energy\n 'XLF','KBE','KCE','KIE','IPF','KRE', # financials\n 'XLV','XBI','XHE','XHS','IRY','XPH', # healthcare\n 'XLI','XAR','IPN','XTN', # industrial\n 'XLB','IRV','XME', # materials\n 'XLK','MTK','IPK','XSD','XSW', # technology\n 'IST','XTL', # telecom\n 'IPU','XLU' # utilities\n ]\n \nstock_list = [Broad_mkts, Large_cap, Mid_cap, Small_cap, Global, Asia, Europe, Latam, Africa, Real_assets, Sector]\n\n# ~~~ Category structure ~~~ #\ncat = {'Broad_Market' :['THRK','RSCO'],\n 'Large_Cap' :['ONEK','SPY','SPYG','SPYV'],\n 'Mid_Cap' :['MDY','MDYG', 'MDYV'], \n 'Small_Cap' :['TWOK','SLY','SLYG','SLYV'],\n 'Global_Equity' :['DGT','BIK','GMM','EWX','CWI','GII','GNR','DWX','GWL','MDD','GWX'],\n 'AsiaPac_Equity' :['JPP','JSC','GXC','GMF'],\n 'Europe_Equity' :['FEZ','GUR','RBL','FEU'],\n 'Latam_MidEast_Africa' :['GML','GAF'],\n 'Real_Estate' :['RWO','RWX','RWR'],\n 'Consumer_Discretionary':['XLY','XHB','IPD','XRT'],\n 'Consumer_Staples' :['XLP','IPS'], \n 'Energy' :['XLE','IPW','XES','XOP'], \n 'Financials' :['XLF','KBE','KCE','KIE','IPF','KRE'],\n 'Healthcare' :['XLV','XBI','XHE','XHS','IRY','XPH'],\n 'Industrial' :['XLI','XAR','IPN','XTN'],\n 'Materials' :['XLB','IRV','XME'],\n 'Technology' :['XLK','MTK','IPK','XSD','XSW'],\n 'Telecom' :['IST','XTL'], \n 'Utilities' :['IPU','XLU']\n } \n\nfilepath = r'C:\\Users\\Owner\\Documents\\Visual_Studio_2013\\Projects\\iVC_Reporting_Engine\\PythonApplication2\\\\'", "cell_type": "code", "outputs": [], "metadata": {"hide_input": false, "trusted": true, "collapsed": true, "run_control": {"marked": true}}}, {"execution_count": 4, "source": "# ================================================================== #\n# get prices\ndef get_px(stock, start, end):\n '''\n Function to call Pandas' Yahoo Finance API to get daily stock prices.\n ''' \n try:\n return web.DataReader(stock, 'yahoo', start, end)['Adj Close']\n except Exception as e:\n print( 'something is fucking up' )\n\npx = pd.DataFrame()\nfor category in stock_list:\n for stock in category:\n px[stock] = get_px( stock, one_year_ago, date_today )\n \n# ================================================================== #\n# construct dataframe and proper multi index\nlog_rets = np.log( px / px.shift(1) ).dropna()\nlrets = log_rets.T.copy()\nlrets.index.name = 'ETF'\nlrets['Category'] = pd.Series()\n\nfor cat_key, etf_val in cat.items():\n for val in etf_val:\n if val in lrets.index:\n idx_loc = lrets.index.get_loc(val)\n lrets.ix[idx_loc,'Category'] = cat_key\n else:\n pass\n\nlrets.set_index('Category', append=True, inplace=True)\nlrets = lrets.swaplevel('ETF','Category').sortlevel('Category')\n# lrets.head() \n\n# ================================================================== #\n# cumulative returns of ETF's\ncum_rets = lrets.groupby(level='Category').cumsum(axis=1)\n# cum_rets.head()\n\n# ================================================================== #\n# composite groupings of cumulative ETF returns (equally weighted intra-category mean returns)\ncomposite_rets = pd.DataFrame()\nfor label in cat.keys():\n composite_rets[label] = cum_rets.ix[label].mean(axis=0) # equal weighted mean\n \ncomp_rets = np.round(composite_rets.copy(),4) # rounding", "cell_type": "code", "outputs": [], "metadata": {"hide_input": false, "trusted": true, "collapsed": false, "run_control": {"marked": true}}}, {"execution_count": 5, "source": "# ~~~~~ Additional risk and return computations ~~~~~ #\n\n# ================================================================== #\n# composite rolling std\n\nsigmas = lrets.groupby(level='Category').std() # equal weighted std\n\ncomposite_sigs = pd.DataFrame()\nfor label in cat.keys():\n composite_sigs[label] = sigmas.ix[label] \n\nrsigs = pd.rolling_mean( composite_sigs, window=60 ).dropna()*np.sqrt(60)\n\n# ================================================================== #\n# composite rolling risk adjusted returns\n\nmean_rets = lrets.groupby(level='Category').mean() # equal weighted mean\n\ncomposite_risk_rets = pd.DataFrame()\nfor label in cat.keys():\n composite_risk_rets[label] = mean_rets.ix[label] \n\nrs = pd.rolling_mean( composite_risk_rets, window=60 ).dropna() \nrisk_rets = rs/rsigs\n\n# ================================================================== #\n# correlation matrix of composite ETF groups' risk adjusted returns\ncor = risk_rets.corr()", "cell_type": "code", "outputs": [], "metadata": {"trusted": true, "collapsed": false}}, {"cell_type": "markdown", "source": "### Current ICC Estimates and Rankings", "metadata": {}}, {"execution_count": 6, "source": "# ================================================================== #\n# import ICC estimates\nframe = pd.read_csv( filepath+'Spdr_ICC_est_{}.csv'.format(date_today)) #.dropna()\npre_frame = pd.read_csv( filepath+'Spdr_ICC_est_{}.csv'.format(prev_date_today.date()), index_col=0 ) #.dropna() \n\n# ================================================================== #\n# group ICC data by category\n\n# ~~~~ setup current estimates\nf = frame.copy()\ngrp = f.groupby('Category')\ngrp_mean = grp.mean().sort('ETF_ICC_est', ascending=False)\ngrp_mean_rnd = grp_mean['ETF_ICC_est'].round(3)\ngrp_mean = pd.DataFrame( grp_mean_rnd )\n\n# ~~~~ setup last updates' estimates\npre_f = pre_frame[['ETF_ICC_est','Category']]\npre_grp = pre_f.groupby('Category')\npre_grp_mean = pre_grp.mean().sort('ETF_ICC_est', ascending=False) \npre_grp_mean = np.round( pre_grp_mean, 3 )\n\n# ~~~~ setup combined dataframe using current df est.\ngm_cols = ['Current ICC Est', 'Rank', 'Previous ICC Est', 'Previous Rank', 'Change in Rank']\ngrp_mean['Rank'] = grp_mean.rank(ascending=False, method='dense')\ngrp_mean['Previous ICC est'] = pre_grp_mean\ngrp_mean['Previous Rank'] = pre_grp_mean.rank(ascending=False, method='dense')\ngrp_mean['Change in Ranking'] = grp_mean['Previous Rank'] - grp_mean['Rank']\ngrp_mean.columns = gm_cols\ngrp_mean", "cell_type": "code", "outputs": [{"execution_count": 6, "data": {"text/plain": " Current ICC Est Rank Previous ICC Est \\\nCategory \nMid_Cap 1.891 1 0.132 \nBroad_Market 0.263 2 0.130 \nEurope_Equity 0.242 3 0.234 \nFinancials 0.232 4 0.228 \nEnergy 0.228 5 0.187 \nMaterials 0.209 6 0.177 \nAsiaPac_Equity 0.209 6 0.202 \nUtilities 0.204 7 0.190 \nGlobal_Equity 0.191 8 0.183 \nLatam_MidEast_Africa 0.173 9 0.171 \nIndustrial 0.145 10 0.140 \nLarge_Cap 0.141 11 0.133 \nReal_Estate 0.140 12 0.135 \nTelecom 0.139 13 0.135 \nSmall_Cap 0.139 13 0.136 \nConsumer_Discretionary 0.132 14 0.131 \nTechnology 0.126 15 0.122 \nConsumer_Staples 0.115 16 0.114 \nHealthcare 0.108 17 0.109 \n\n Previous Rank Change in Rank \nCategory \nMid_Cap 13 12 \nBroad_Market 15 13 \nEurope_Equity 1 -2 \nFinancials 2 -2 \nEnergy 5 0 \nMaterials 7 1 \nAsiaPac_Equity 3 -3 \nUtilities 4 -3 \nGlobal_Equity 6 -2 \nLatam_MidEast_Africa 8 -1 \nIndustrial 9 -1 \nLarge_Cap 12 1 \nReal_Estate 11 -1 \nTelecom 11 -2 \nSmall_Cap 10 -3 \nConsumer_Discretionary 14 0 \nTechnology 16 1 \nConsumer_Staples 17 1 \nHealthcare 18 1 ", "text/html": "\n
\n \n \n | \n Current ICC Est | \n Rank | \n Previous ICC Est | \n Previous Rank | \n Change in Rank | \n
\n \n | Category | \n | \n | \n | \n | \n | \n
\n \n \n \n | Mid_Cap | \n 1.891 | \n 1 | \n 0.132 | \n 13 | \n 12 | \n
\n \n | Broad_Market | \n 0.263 | \n 2 | \n 0.130 | \n 15 | \n 13 | \n
\n \n | Europe_Equity | \n 0.242 | \n 3 | \n 0.234 | \n 1 | \n -2 | \n
\n \n | Financials | \n 0.232 | \n 4 | \n 0.228 | \n 2 | \n -2 | \n
\n \n | Energy | \n 0.228 | \n 5 | \n 0.187 | \n 5 | \n 0 | \n
\n \n | Materials | \n 0.209 | \n 6 | \n 0.177 | \n 7 | \n 1 | \n
\n \n | AsiaPac_Equity | \n 0.209 | \n 6 | \n 0.202 | \n 3 | \n -3 | \n
\n \n | Utilities | \n 0.204 | \n 7 | \n 0.190 | \n 4 | \n -3 | \n
\n \n | Global_Equity | \n 0.191 | \n 8 | \n 0.183 | \n 6 | \n -2 | \n
\n \n | Latam_MidEast_Africa | \n 0.173 | \n 9 | \n 0.171 | \n 8 | \n -1 | \n
\n \n | Industrial | \n 0.145 | \n 10 | \n 0.140 | \n 9 | \n -1 | \n
\n \n | Large_Cap | \n 0.141 | \n 11 | \n 0.133 | \n 12 | \n 1 | \n
\n \n | Real_Estate | \n 0.140 | \n 12 | \n 0.135 | \n 11 | \n -1 | \n
\n \n | Telecom | \n 0.139 | \n 13 | \n 0.135 | \n 11 | \n -2 | \n
\n \n | Small_Cap | \n 0.139 | \n 13 | \n 0.136 | \n 10 | \n -3 | \n
\n \n | Consumer_Discretionary | \n 0.132 | \n 14 | \n 0.131 | \n 14 | \n 0 | \n
\n \n | Technology | \n 0.126 | \n 15 | \n 0.122 | \n 16 | \n 1 | \n
\n \n | Consumer_Staples | \n 0.115 | \n 16 | \n 0.114 | \n 17 | \n 1 | \n
\n \n | Healthcare | \n 0.108 | \n 17 | \n 0.109 | \n 18 | \n 1 | \n
\n \n
\n
\n
\n \n \n | \n Current Cum. Returns [6.15 - 7.6] | \n Rank | \n Previous Cum. Returns [5.25 - 6.15] | \n Previous Rank | \n Change in Rank | \n
\n \n | Category | \n | \n | \n | \n | \n | \n
\n \n \n \n | Healthcare | \n 0.0096 | \n 1 | \n 0.0158 | \n 3 | \n 2 | \n
\n \n | Consumer_Staples | \n 0.0016 | \n 2 | \n -0.0133 | \n 12 | \n 10 | \n
\n \n | Consumer_Discretionary | \n -0.0028 | \n 3 | \n 0.0055 | \n 8 | \n 5 | \n
\n \n | Latam_MidEast_Africa | \n -0.0081 | \n 4 | \n -0.0282 | \n 18 | \n 14 | \n
\n \n | Broad_Market | \n -0.0082 | \n 5 | \n 0.0058 | \n 6 | \n 1 | \n
\n \n | Real_Estate | \n -0.0084 | \n 6 | \n -0.0245 | \n 16 | \n 10 | \n
\n \n | Large_Cap | \n -0.0085 | \n 7 | \n -0.0026 | \n 9 | \n 2 | \n
\n \n | Utilities | \n -0.0091 | \n 8 | \n -0.0275 | \n 17 | \n 9 | \n
\n \n | Small_Cap | \n -0.0114 | \n 9 | \n 0.0233 | \n 2 | \n -7 | \n
\n \n | Financials | \n -0.0130 | \n 10 | \n 0.0309 | \n 1 | \n -9 | \n
\n \n | Mid_Cap | \n -0.0154 | \n 11 | \n 0.0056 | \n 7 | \n -4 | \n
\n \n | Industrial | \n -0.0291 | \n 12 | \n -0.0048 | \n 10 | \n -2 | \n
\n \n | Technology | \n -0.0315 | \n 13 | \n 0.0062 | \n 5 | \n -8 | \n
\n \n | AsiaPac_Equity | \n -0.0344 | \n 14 | \n -0.0227 | \n 15 | \n 1 | \n
\n \n | Global_Equity | \n -0.0352 | \n 15 | \n -0.0195 | \n 14 | \n -1 | \n
\n \n | Telecom | \n -0.0365 | \n 16 | \n 0.0098 | \n 4 | \n -12 | \n
\n \n | Europe_Equity | \n -0.0433 | \n 17 | \n -0.0308 | \n 19 | \n 2 | \n
\n \n | Materials | \n -0.0681 | \n 18 | \n -0.0091 | \n 11 | \n -7 | \n
\n \n | Energy | \n -0.0776 | \n 19 | \n -0.0152 | \n 13 | \n -6 | \n
\n \n
\n
_**Acknowledgements:** Ipython Notebook styling modded from Plotly and Cam Davidson-Pilon custom CSS_", "metadata": {"hide_input": false, "run_control": {"marked": true}}}, {"execution_count": 18, "source": "from IPython.core.display import HTML \n\nHTML('''\nThe raw code for this IPython notebook is by default hidden for easier reading.\nTo toggle on/off the raw code, click here.''')", "cell_type": "code", "outputs": [{"execution_count": 18, "data": {"text/plain": "", "text/html": "\nThe raw code for this IPython notebook is by default hidden for easier reading.\nTo toggle on/off the raw code, click here."}, "metadata": {}, "output_type": "execute_result"}], "metadata": {"hide_input": false, "trusted": true, "collapsed": false, "run_control": {"marked": true}}}, {"execution_count": 19, "source": "from IPython.core.display import HTML \nimport requests\n\nstyles = requests.get(\"https://raw.githubusercontent.com/BlackArbsCEO/BlackArbsCEO.github.io/Equity-Analysis/Equity%20Analysis/custom.css\")\nHTML(styles.text)", "cell_type": "code", "outputs": [{"execution_count": 19, "data": {"text/plain": "", "text/html": "\n\n"}, "metadata": {}, "output_type": "execute_result"}], "metadata": {"trusted": true, "collapsed": false}}, {"execution_count": null, "source": "", "cell_type": "code", "outputs": [], "metadata": {"trusted": true, "collapsed": true}}]}