{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**References:** \n",
"\n",
"https://www.datacamp.com/community/tutorials/finance-python-trading\n",
"\n",
"https://github.com/datacamp/datacamp-community-tutorials/blob/master/Python%20Finance%20Tutorial%20For%20Beginners/Python%20For%20Finance%20Beginners%20Tutorial.ipynb\n",
"\n",
"https://pypi.python.org/pypi/fix-yahoo-finance\n",
"\n",
"http://www.learndatasci.com/python-finance-part-yahoo-finance-api-pandas-matplotlib/"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"# Import initial libraries\n",
"\n",
"import pandas as pd\n",
"import numpy as np\n",
"import datetime\n",
"import matplotlib.pyplot as plt\n",
"%matplotlib inline"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2.2.2\n"
]
},
{
"data": {
"text/html": [
""
],
"text/vnd.plotly.v1+html": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Imports in order to be able to use Plotly offline.\n",
"from plotly import __version__\n",
"from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n",
"\n",
"import plotly.graph_objs as go\n",
"\n",
"print(__version__) # requires version >= 1.9.0\n",
"\n",
"init_notebook_mode(connected=True)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Acquisition Date | \n",
" Ticker | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 2013-02-07 | \n",
" AAPL | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 1 | \n",
" 2014-02-27 | \n",
" JNJ | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 2 | \n",
" 2013-06-04 | \n",
" MCD | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 3 | \n",
" 2015-12-14 | \n",
" MTCH | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 4 | \n",
" 2016-01-14 | \n",
" NFLX | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 5 | \n",
" 2013-08-14 | \n",
" WMT | \n",
" 125 | \n",
" 68.30 | \n",
" 8537.50 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 6 | \n",
" 2013-12-13 | \n",
" FB | \n",
" 150 | \n",
" 53.32 | \n",
" 7998.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 7 | \n",
" 2015-01-05 | \n",
" TWTR | \n",
" 225 | \n",
" 36.38 | \n",
" 8185.50 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Acquisition Date Ticker Quantity Unit Cost Cost Basis Start of Year\n",
"0 2013-02-07 AAPL 125 65.40 8175.00 2017-12-29\n",
"1 2014-02-27 JNJ 100 81.90 8190.00 2017-12-29\n",
"2 2013-06-04 MCD 100 84.99 8499.00 2017-12-29\n",
"3 2015-12-14 MTCH 600 13.63 8178.00 2017-12-29\n",
"4 2016-01-14 NFLX 75 108.71 8153.25 2017-12-29\n",
"5 2013-08-14 WMT 125 68.30 8537.50 2017-12-29\n",
"6 2013-12-13 FB 150 53.32 7998.00 2017-12-29\n",
"7 2015-01-05 TWTR 225 36.38 8185.50 2017-12-29"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Import the Sample worksheet with acquisition dates and initial cost basis:\n",
"\n",
"portfolio_df = pd.read_excel('Sample stocks acquisition dates_costs.xlsx', sheetname='Sample')\n",
"\n",
"portfolio_df.head(10)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"RangeIndex: 8 entries, 0 to 7\n",
"Data columns (total 6 columns):\n",
"Acquisition Date 8 non-null datetime64[ns]\n",
"Ticker 8 non-null object\n",
"Quantity 8 non-null int64\n",
"Unit Cost 8 non-null float64\n",
"Cost Basis 8 non-null float64\n",
"Start of Year 8 non-null datetime64[ns]\n",
"dtypes: datetime64[ns](2), float64(2), int64(1), object(1)\n",
"memory usage: 456.0+ bytes\n"
]
}
],
"source": [
"# Confirm that you have 8 values for each column.\n",
"\n",
"portfolio_df.info()"
]
},
{
"cell_type": "code",
"execution_count": 48,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"# Date Ranges for SP 500 and for all tickers\n",
"# Modify these date ranges each week.\n",
"\n",
"# The below will pull back stock prices from 2010 until end date specified.\n",
"start_sp = datetime.datetime(2013, 1, 1)\n",
"end_sp = datetime.datetime(2018, 3, 1)\n",
"\n",
"# This variable is used for YTD performance.\n",
"end_of_last_year = datetime.datetime(2017, 12, 29)\n",
"\n",
"# These are separate if for some reason want different date range than SP.\n",
"stocks_start = datetime.datetime(2013, 1, 1)\n",
"stocks_end = datetime.datetime(2018, 3, 1)"
]
},
{
"cell_type": "code",
"execution_count": 49,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[*********************100%***********************] 1 of 1 downloaded"
]
},
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Open | \n",
" High | \n",
" Low | \n",
" Close | \n",
" Adj Close | \n",
" Volume | \n",
"
\n",
" \n",
" Date | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2013-01-02 | \n",
" 1426.189941 | \n",
" 1462.430054 | \n",
" 1426.189941 | \n",
" 1462.420044 | \n",
" 1462.420044 | \n",
" -92367296 | \n",
"
\n",
" \n",
" 2013-01-03 | \n",
" 1462.420044 | \n",
" 1465.469971 | \n",
" 1455.530029 | \n",
" 1459.369995 | \n",
" 1459.369995 | \n",
" -465237296 | \n",
"
\n",
" \n",
" 2013-01-04 | \n",
" 1459.369995 | \n",
" 1467.939941 | \n",
" 1458.989990 | \n",
" 1466.469971 | \n",
" 1466.469971 | \n",
" -870677296 | \n",
"
\n",
" \n",
" 2013-01-07 | \n",
" 1466.469971 | \n",
" 1466.469971 | \n",
" 1456.619995 | \n",
" 1461.890015 | \n",
" 1461.890015 | \n",
" -989997296 | \n",
"
\n",
" \n",
" 2013-01-08 | \n",
" 1461.890015 | \n",
" 1461.890015 | \n",
" 1451.640015 | \n",
" 1457.150024 | \n",
" 1457.150024 | \n",
" -693367296 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Open High Low Close Adj Close \\\n",
"Date \n",
"2013-01-02 1426.189941 1462.430054 1426.189941 1462.420044 1462.420044 \n",
"2013-01-03 1462.420044 1465.469971 1455.530029 1459.369995 1459.369995 \n",
"2013-01-04 1459.369995 1467.939941 1458.989990 1466.469971 1466.469971 \n",
"2013-01-07 1466.469971 1466.469971 1456.619995 1461.890015 1461.890015 \n",
"2013-01-08 1461.890015 1461.890015 1451.640015 1457.150024 1457.150024 \n",
"\n",
" Volume \n",
"Date \n",
"2013-01-02 -92367296 \n",
"2013-01-03 -465237296 \n",
"2013-01-04 -870677296 \n",
"2013-01-07 -989997296 \n",
"2013-01-08 -693367296 "
]
},
"execution_count": 49,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Leveraged from the helpful Datacamp Python Finance trading blog post.\n",
"\n",
"from pandas_datareader import data as pdr\n",
"import fix_yahoo_finance as yf\n",
"yf.pdr_override() # <== that's all it takes :-)\n",
"\n",
"sp500 = pdr.get_data_yahoo('^GSPC', \n",
" start_sp,\n",
" end_sp)\n",
" \n",
"sp500.head()"
]
},
{
"cell_type": "code",
"execution_count": 50,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Open | \n",
" High | \n",
" Low | \n",
" Close | \n",
" Adj Close | \n",
" Volume | \n",
"
\n",
" \n",
" Date | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 2018-02-23 | \n",
" 2715.800049 | \n",
" 2747.760010 | \n",
" 2713.739990 | \n",
" 2747.300049 | \n",
" 2747.300049 | \n",
" -1105777296 | \n",
"
\n",
" \n",
" 2018-02-26 | \n",
" 2757.370117 | \n",
" 2780.639893 | \n",
" 2753.780029 | \n",
" 2779.600098 | \n",
" 2779.600098 | \n",
" -870317296 | \n",
"
\n",
" \n",
" 2018-02-27 | \n",
" 2780.449951 | \n",
" 2789.149902 | \n",
" 2744.219971 | \n",
" 2744.280029 | \n",
" 2744.280029 | \n",
" -549887296 | \n",
"
\n",
" \n",
" 2018-02-28 | \n",
" 2753.780029 | \n",
" 2761.520020 | \n",
" 2713.540039 | \n",
" 2713.830078 | \n",
" 2713.830078 | \n",
" -64307296 | \n",
"
\n",
" \n",
" 2018-03-01 | \n",
" 2715.219971 | \n",
" 2730.889893 | \n",
" 2659.649902 | \n",
" 2677.669922 | \n",
" 2677.669922 | \n",
" 209002704 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Open High Low Close Adj Close \\\n",
"Date \n",
"2018-02-23 2715.800049 2747.760010 2713.739990 2747.300049 2747.300049 \n",
"2018-02-26 2757.370117 2780.639893 2753.780029 2779.600098 2779.600098 \n",
"2018-02-27 2780.449951 2789.149902 2744.219971 2744.280029 2744.280029 \n",
"2018-02-28 2753.780029 2761.520020 2713.540039 2713.830078 2713.830078 \n",
"2018-03-01 2715.219971 2730.889893 2659.649902 2677.669922 2677.669922 \n",
"\n",
" Volume \n",
"Date \n",
"2018-02-23 -1105777296 \n",
"2018-02-26 -870317296 \n",
"2018-02-27 -549887296 \n",
"2018-02-28 -64307296 \n",
"2018-03-01 209002704 "
]
},
"execution_count": 50,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sp500.tail()"
]
},
{
"cell_type": "code",
"execution_count": 51,
"metadata": {},
"outputs": [],
"source": [
"# Create a dataframe with only the Adj Close column as that's all we need for this analysis.\n",
"\n",
"sp_500_adj_close = sp500[['Adj Close']].reset_index()"
]
},
{
"cell_type": "code",
"execution_count": 52,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 1295 | \n",
" 2018-02-23 | \n",
" 2747.300049 | \n",
"
\n",
" \n",
" 1296 | \n",
" 2018-02-26 | \n",
" 2779.600098 | \n",
"
\n",
" \n",
" 1297 | \n",
" 2018-02-27 | \n",
" 2744.280029 | \n",
"
\n",
" \n",
" 1298 | \n",
" 2018-02-28 | \n",
" 2713.830078 | \n",
"
\n",
" \n",
" 1299 | \n",
" 2018-03-01 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Date Adj Close\n",
"1295 2018-02-23 2747.300049\n",
"1296 2018-02-26 2779.600098\n",
"1297 2018-02-27 2744.280029\n",
"1298 2018-02-28 2713.830078\n",
"1299 2018-03-01 2677.669922"
]
},
"execution_count": 52,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sp_500_adj_close.tail()"
]
},
{
"cell_type": "code",
"execution_count": 53,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 1258 | \n",
" 2017-12-29 | \n",
" 2673.610107 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Date Adj Close\n",
"1258 2017-12-29 2673.610107"
]
},
"execution_count": 53,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Adj Close for the EOY in 2017 in order to run comparisons versus stocks YTD performances.\n",
"\n",
"sp_500_adj_close_start = sp_500_adj_close[sp_500_adj_close['Date']==end_of_last_year]\n",
"sp_500_adj_close_start"
]
},
{
"cell_type": "code",
"execution_count": 54,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"array([u'AAPL', u'JNJ', u'MCD', u'MTCH', u'NFLX', u'WMT', u'FB', u'TWTR'], dtype=object)"
]
},
"execution_count": 54,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Generate a dynamic list of tickers to pull from Yahoo Finance API based on the imported file with tickers.\n",
"tickers = portfolio_df['Ticker'].unique()\n",
"tickers"
]
},
{
"cell_type": "code",
"execution_count": 55,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[*********************100%***********************] 1 of 1 downloaded"
]
}
],
"source": [
"# Stock comparison code\n",
"\n",
"def get(tickers, startdate, enddate):\n",
" def data(ticker):\n",
" return (pdr.get_data_yahoo(ticker, start=startdate, end=enddate))\n",
" datas = map(data, tickers)\n",
" return(pd.concat(datas, keys=tickers, names=['Ticker', 'Date']))\n",
" \n",
"all_data = get(tickers, stocks_start, stocks_end)"
]
},
{
"cell_type": "code",
"execution_count": 56,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" | \n",
" Open | \n",
" High | \n",
" Low | \n",
" Close | \n",
" Adj Close | \n",
" Volume | \n",
"
\n",
" \n",
" Ticker | \n",
" Date | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" AAPL | \n",
" 2013-01-02 | \n",
" 79.117142 | \n",
" 79.285713 | \n",
" 77.375717 | \n",
" 78.432854 | \n",
" 56.532822 | \n",
" 140129500 | \n",
"
\n",
" \n",
" 2013-01-03 | \n",
" 78.268570 | \n",
" 78.524284 | \n",
" 77.285713 | \n",
" 77.442856 | \n",
" 55.819248 | \n",
" 88241300 | \n",
"
\n",
" \n",
" 2013-01-04 | \n",
" 76.709999 | \n",
" 76.947144 | \n",
" 75.118568 | \n",
" 75.285713 | \n",
" 54.264439 | \n",
" 148583400 | \n",
"
\n",
" \n",
" 2013-01-07 | \n",
" 74.571426 | \n",
" 75.614288 | \n",
" 73.599998 | \n",
" 74.842857 | \n",
" 53.945232 | \n",
" 121039100 | \n",
"
\n",
" \n",
" 2013-01-08 | \n",
" 75.601425 | \n",
" 75.984283 | \n",
" 74.464287 | \n",
" 75.044289 | \n",
" 54.090427 | \n",
" 114676800 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Open High Low Close Adj Close \\\n",
"Ticker Date \n",
"AAPL 2013-01-02 79.117142 79.285713 77.375717 78.432854 56.532822 \n",
" 2013-01-03 78.268570 78.524284 77.285713 77.442856 55.819248 \n",
" 2013-01-04 76.709999 76.947144 75.118568 75.285713 54.264439 \n",
" 2013-01-07 74.571426 75.614288 73.599998 74.842857 53.945232 \n",
" 2013-01-08 75.601425 75.984283 74.464287 75.044289 54.090427 \n",
"\n",
" Volume \n",
"Ticker Date \n",
"AAPL 2013-01-02 140129500 \n",
" 2013-01-03 88241300 \n",
" 2013-01-04 148583400 \n",
" 2013-01-07 121039100 \n",
" 2013-01-08 114676800 "
]
},
"execution_count": 56,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"all_data.head()"
]
},
{
"cell_type": "code",
"execution_count": 57,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-01-02 | \n",
" 56.532822 | \n",
"
\n",
" \n",
" 1 | \n",
" AAPL | \n",
" 2013-01-03 | \n",
" 55.819248 | \n",
"
\n",
" \n",
" 2 | \n",
" AAPL | \n",
" 2013-01-04 | \n",
" 54.264439 | \n",
"
\n",
" \n",
" 3 | \n",
" AAPL | \n",
" 2013-01-07 | \n",
" 53.945232 | \n",
"
\n",
" \n",
" 4 | \n",
" AAPL | \n",
" 2013-01-08 | \n",
" 54.090427 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close\n",
"0 AAPL 2013-01-02 56.532822\n",
"1 AAPL 2013-01-03 55.819248\n",
"2 AAPL 2013-01-04 54.264439\n",
"3 AAPL 2013-01-07 53.945232\n",
"4 AAPL 2013-01-08 54.090427"
]
},
"execution_count": 57,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Also only pulling the ticker, date and adj. close columns for our tickers.\n",
"\n",
"adj_close = all_data[['Adj Close']].reset_index()\n",
"adj_close.head()"
]
},
{
"cell_type": "code",
"execution_count": 58,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 1258 | \n",
" AAPL | \n",
" 2017-12-29 | \n",
" 168.542831 | \n",
"
\n",
" \n",
" 2558 | \n",
" JNJ | \n",
" 2017-12-29 | \n",
" 138.831009 | \n",
"
\n",
" \n",
" 3858 | \n",
" MCD | \n",
" 2017-12-29 | \n",
" 171.037949 | \n",
"
\n",
" \n",
" 4431 | \n",
" MTCH | \n",
" 2017-12-29 | \n",
" 31.309999 | \n",
"
\n",
" \n",
" 5731 | \n",
" NFLX | \n",
" 2017-12-29 | \n",
" 191.960007 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close\n",
"1258 AAPL 2017-12-29 168.542831\n",
"2558 JNJ 2017-12-29 138.831009\n",
"3858 MCD 2017-12-29 171.037949\n",
"4431 MTCH 2017-12-29 31.309999\n",
"5731 NFLX 2017-12-29 191.960007"
]
},
"execution_count": 58,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Grabbing the ticker close from the end of last year\n",
"adj_close_start = adj_close[adj_close['Date']==end_of_last_year]\n",
"adj_close_start.head()"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 1299 | \n",
" AAPL | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
"
\n",
" \n",
" 2599 | \n",
" JNJ | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
"
\n",
" \n",
" 3899 | \n",
" MCD | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
"
\n",
" \n",
" 4472 | \n",
" MTCH | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
"
\n",
" \n",
" 5772 | \n",
" NFLX | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
"
\n",
" \n",
" 7072 | \n",
" WMT | \n",
" 2018-03-01 | \n",
" 89.080002 | \n",
"
\n",
" \n",
" 8372 | \n",
" FB | \n",
" 2018-03-01 | \n",
" 175.940002 | \n",
"
\n",
" \n",
" 9457 | \n",
" TWTR | \n",
" 2018-03-01 | \n",
" 32.240002 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close\n",
"1299 AAPL 2018-03-01 175.000000\n",
"2599 JNJ 2018-03-01 127.279999\n",
"3899 MCD 2018-03-01 155.699997\n",
"4472 MTCH 2018-03-01 39.320000\n",
"5772 NFLX 2018-03-01 290.390015\n",
"7072 WMT 2018-03-01 89.080002\n",
"8372 FB 2018-03-01 175.940002\n",
"9457 TWTR 2018-03-01 32.240002"
]
},
"execution_count": 60,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Grab the latest stock close price\n",
"\n",
"adj_close_latest = adj_close[adj_close['Date']==stocks_end]\n",
"adj_close_latest"
]
},
{
"cell_type": "code",
"execution_count": 61,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" Ticker | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" AAPL | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
"
\n",
" \n",
" JNJ | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
"
\n",
" \n",
" MCD | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
"
\n",
" \n",
" MTCH | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
"
\n",
" \n",
" NFLX | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Date Adj Close\n",
"Ticker \n",
"AAPL 2018-03-01 175.000000\n",
"JNJ 2018-03-01 127.279999\n",
"MCD 2018-03-01 155.699997\n",
"MTCH 2018-03-01 39.320000\n",
"NFLX 2018-03-01 290.390015"
]
},
"execution_count": 61,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"adj_close_latest.set_index('Ticker', inplace=True)\n",
"adj_close_latest.head()"
]
},
{
"cell_type": "code",
"execution_count": 62,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
"
\n",
" \n",
" Ticker | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Acquisition Date Quantity Unit Cost Cost Basis Start of Year\n",
"Ticker \n",
"AAPL 2013-02-07 125 65.40 8175.00 2017-12-29\n",
"JNJ 2014-02-27 100 81.90 8190.00 2017-12-29\n",
"MCD 2013-06-04 100 84.99 8499.00 2017-12-29\n",
"MTCH 2015-12-14 600 13.63 8178.00 2017-12-29\n",
"NFLX 2016-01-14 75 108.71 8153.25 2017-12-29"
]
},
"execution_count": 62,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"portfolio_df.set_index(['Ticker'], inplace=True)\n",
"\n",
"portfolio_df.head()"
]
},
{
"cell_type": "code",
"execution_count": 63,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" Ticker | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
"
\n",
" \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
"
\n",
" \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
"
\n",
" \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
"
\n",
" \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"Ticker \n",
"AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Date Adj Close \n",
"Ticker \n",
"AAPL 2018-03-01 175.000000 \n",
"JNJ 2018-03-01 127.279999 \n",
"MCD 2018-03-01 155.699997 \n",
"MTCH 2018-03-01 39.320000 \n",
"NFLX 2018-03-01 290.390015 "
]
},
"execution_count": 63,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Merge the portfolio dataframe with the adj close dataframe; they are being joined by their indexes.\n",
"\n",
"merged_portfolio = pd.merge(portfolio_df, adj_close_latest, left_index=True, right_index=True)\n",
"merged_portfolio.head()"
]
},
{
"cell_type": "code",
"execution_count": 64,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Date | \n",
" Adj Close | \n",
" ticker return | \n",
"
\n",
" \n",
" Ticker | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
"
\n",
" \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
"
\n",
" \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
"
\n",
" \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
"
\n",
" \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
"
\n",
" \n",
" WMT | \n",
" 2013-08-14 | \n",
" 125 | \n",
" 68.30 | \n",
" 8537.50 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 89.080002 | \n",
" 0.304246 | \n",
"
\n",
" \n",
" FB | \n",
" 2013-12-13 | \n",
" 150 | \n",
" 53.32 | \n",
" 7998.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.940002 | \n",
" 2.299700 | \n",
"
\n",
" \n",
" TWTR | \n",
" 2015-01-05 | \n",
" 225 | \n",
" 36.38 | \n",
" 8185.50 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 32.240002 | \n",
" -0.113799 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"Ticker \n",
"AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"WMT 2013-08-14 125 68.30 8537.50 2017-12-29 \n",
"FB 2013-12-13 150 53.32 7998.00 2017-12-29 \n",
"TWTR 2015-01-05 225 36.38 8185.50 2017-12-29 \n",
"\n",
" Date Adj Close ticker return \n",
"Ticker \n",
"AAPL 2018-03-01 175.000000 1.675841 \n",
"JNJ 2018-03-01 127.279999 0.554090 \n",
"MCD 2018-03-01 155.699997 0.831980 \n",
"MTCH 2018-03-01 39.320000 1.884813 \n",
"NFLX 2018-03-01 290.390015 1.671236 \n",
"WMT 2018-03-01 89.080002 0.304246 \n",
"FB 2018-03-01 175.940002 2.299700 \n",
"TWTR 2018-03-01 32.240002 -0.113799 "
]
},
"execution_count": 64,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# The below creates a new column which is the ticker return; takes the latest adjusted close for each position\n",
"# and divides that by the initial share cost.\n",
"\n",
"merged_portfolio['ticker return'] = merged_portfolio['Adj Close'] / merged_portfolio['Unit Cost'] - 1\n",
"\n",
"merged_portfolio"
]
},
{
"cell_type": "code",
"execution_count": 65,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"merged_portfolio.reset_index(inplace=True)"
]
},
{
"cell_type": "code",
"execution_count": 66,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Date_x | \n",
" Adj Close_x | \n",
" ticker return | \n",
" Date_y | \n",
" Adj Close_y | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 2013-02-07 | \n",
" 1509.390015 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 2014-02-27 | \n",
" 1854.290039 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 2013-06-04 | \n",
" 1631.380005 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2015-12-14 | \n",
" 2021.939941 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 2016-01-14 | \n",
" 1921.839966 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Date_x Adj Close_x ticker return Date_y Adj Close_y \n",
"0 2018-03-01 175.000000 1.675841 2013-02-07 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 2014-02-27 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 2013-06-04 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2015-12-14 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 2016-01-14 1921.839966 "
]
},
"execution_count": 66,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Here we are merging the new dataframe with the sp500 adjusted closes since the sp start price based on \n",
"# each ticker's acquisition date and sp500 close date.\n",
"\n",
"merged_portfolio_sp = pd.merge(merged_portfolio, sp_500_adj_close, left_on='Acquisition Date', right_on='Date')\n",
"# .set_index('Ticker')\n",
"\n",
"merged_portfolio_sp.head()"
]
},
{
"cell_type": "code",
"execution_count": 67,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 "
]
},
"execution_count": 67,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# We will delete the additional date column which is created from this merge.\n",
"# We then rename columns to Latest Date and then reflect Ticker Adj Close and SP 500 Initial Close.\n",
"\n",
"del merged_portfolio_sp['Date_y']\n",
"\n",
"merged_portfolio_sp.rename(columns={'Date_x': 'Latest Date', 'Adj Close_x': 'Ticker Adj Close'\n",
" , 'Adj Close_y': 'SP 500 Initial Close'}, inplace=True)\n",
"\n",
"merged_portfolio_sp.head()"
]
},
{
"cell_type": "code",
"execution_count": 68,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" Equiv SP Shares | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" 5.416095 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" 4.416785 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" 5.209700 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" 4.044631 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" 4.242419 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" Equiv SP Shares \n",
"0 5.416095 \n",
"1 4.416785 \n",
"2 5.209700 \n",
"3 4.044631 \n",
"4 4.242419 "
]
},
"execution_count": 68,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# This new column determines what SP 500 equivalent purchase would have been at purchase date of stock.\n",
"merged_portfolio_sp['Equiv SP Shares'] = merged_portfolio_sp['Cost Basis'] / merged_portfolio_sp['SP 500 Initial Close']\n",
"merged_portfolio_sp.head()"
]
},
{
"cell_type": "code",
"execution_count": 69,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" Equiv SP Shares | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" 5.416095 | \n",
" 2018-03-01 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" 4.416785 | \n",
" 2018-03-01 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" 5.209700 | \n",
" 2018-03-01 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" 4.044631 | \n",
" 2018-03-01 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" 4.242419 | \n",
" 2018-03-01 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" Equiv SP Shares Date Adj Close \n",
"0 5.416095 2018-03-01 2677.669922 \n",
"1 4.416785 2018-03-01 2677.669922 \n",
"2 5.209700 2018-03-01 2677.669922 \n",
"3 4.044631 2018-03-01 2677.669922 \n",
"4 4.242419 2018-03-01 2677.669922 "
]
},
"execution_count": 69,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# We are joining the developing dataframe with the sp500 closes again, this time with the latest close for SP.\n",
"merged_portfolio_sp_latest = pd.merge(merged_portfolio_sp, sp_500_adj_close, left_on='Latest Date', right_on='Date')\n",
"\n",
"merged_portfolio_sp_latest.head()"
]
},
{
"cell_type": "code",
"execution_count": 70,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" Equiv SP Shares | \n",
" SP 500 Latest Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" 5.416095 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" 4.416785 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" 5.209700 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" 4.044631 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" 4.242419 | \n",
" 2677.669922 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" Equiv SP Shares SP 500 Latest Close \n",
"0 5.416095 2677.669922 \n",
"1 4.416785 2677.669922 \n",
"2 5.209700 2677.669922 \n",
"3 4.044631 2677.669922 \n",
"4 4.242419 2677.669922 "
]
},
"execution_count": 70,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Once again need to delete the new Date column added as it's redundant to Latest Date. \n",
"# Modify Adj Close from the sp dataframe to distinguish it by calling it the SP 500 Latest Close.\n",
"\n",
"del merged_portfolio_sp_latest['Date']\n",
"\n",
"merged_portfolio_sp_latest.rename(columns={'Adj Close': 'SP 500 Latest Close'}, inplace=True)\n",
"\n",
"merged_portfolio_sp_latest.head()"
]
},
{
"cell_type": "code",
"execution_count": 71,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" Equiv SP Shares | \n",
" SP 500 Latest Close | \n",
" SP Return | \n",
" Abs. Return Compare | \n",
" Ticker Share Value | \n",
" SP 500 Value | \n",
" Abs Value Compare | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" 5.416095 | \n",
" 2677.669922 | \n",
" 0.774008 | \n",
" 0.901833 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 7372.484815 | \n",
" 13700.000000 | \n",
" 6327.515185 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" 4.416785 | \n",
" 2677.669922 | \n",
" 0.444041 | \n",
" 0.110050 | \n",
" 12727.999900 | \n",
" 11826.691726 | \n",
" 901.308174 | \n",
" 4537.999900 | \n",
" 3636.691726 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" 5.209700 | \n",
" 2677.669922 | \n",
" 0.641353 | \n",
" 0.190628 | \n",
" 15569.999700 | \n",
" 13949.856316 | \n",
" 1620.143384 | \n",
" 7070.999700 | \n",
" 5450.856316 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" 4.044631 | \n",
" 2677.669922 | \n",
" 0.324307 | \n",
" 1.560506 | \n",
" 23592.000000 | \n",
" 10830.185496 | \n",
" 12761.814504 | \n",
" 15414.000000 | \n",
" 2652.185496 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" 4.242419 | \n",
" 2677.669922 | \n",
" 0.393285 | \n",
" 1.277951 | \n",
" 21779.251125 | \n",
" 11359.797214 | \n",
" 10419.453911 | \n",
" 13626.001125 | \n",
" 3206.547214 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" Equiv SP Shares SP 500 Latest Close SP Return Abs. Return Compare \\\n",
"0 5.416095 2677.669922 0.774008 0.901833 \n",
"1 4.416785 2677.669922 0.444041 0.110050 \n",
"2 5.209700 2677.669922 0.641353 0.190628 \n",
"3 4.044631 2677.669922 0.324307 1.560506 \n",
"4 4.242419 2677.669922 0.393285 1.277951 \n",
"\n",
" Ticker Share Value SP 500 Value Abs Value Compare Stock Gain / (Loss) \\\n",
"0 21875.000000 14502.515185 7372.484815 13700.000000 \n",
"1 12727.999900 11826.691726 901.308174 4537.999900 \n",
"2 15569.999700 13949.856316 1620.143384 7070.999700 \n",
"3 23592.000000 10830.185496 12761.814504 15414.000000 \n",
"4 21779.251125 11359.797214 10419.453911 13626.001125 \n",
"\n",
" SP 500 Gain / (Loss) \n",
"0 6327.515185 \n",
"1 3636.691726 \n",
"2 5450.856316 \n",
"3 2652.185496 \n",
"4 3206.547214 "
]
},
"execution_count": 71,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Percent return of SP from acquisition date of position through latest trading day.\n",
"merged_portfolio_sp_latest['SP Return'] = merged_portfolio_sp_latest['SP 500 Latest Close'] / merged_portfolio_sp_latest['SP 500 Initial Close'] - 1\n",
"\n",
"# This is a new column which takes the tickers return and subtracts the sp 500 equivalent range return.\n",
"merged_portfolio_sp_latest['Abs. Return Compare'] = merged_portfolio_sp_latest['ticker return'] - merged_portfolio_sp_latest['SP Return']\n",
"\n",
"# This is a new column where we calculate the ticker's share value by multiplying the original quantity by the latest close.\n",
"merged_portfolio_sp_latest['Ticker Share Value'] = merged_portfolio_sp_latest['Quantity'] * merged_portfolio_sp_latest['Ticker Adj Close']\n",
"\n",
"# We calculate the equivalent SP 500 Value if we take the original SP shares * the latest SP 500 share price.\n",
"merged_portfolio_sp_latest['SP 500 Value'] = merged_portfolio_sp_latest['Equiv SP Shares'] * merged_portfolio_sp_latest['SP 500 Latest Close']\n",
"\n",
"# This is a new column where we take the current market value for the shares and subtract the SP 500 value.\n",
"merged_portfolio_sp_latest['Abs Value Compare'] = merged_portfolio_sp_latest['Ticker Share Value'] - merged_portfolio_sp_latest['SP 500 Value']\n",
"\n",
"# This column calculates profit / loss for stock position.\n",
"merged_portfolio_sp_latest['Stock Gain / (Loss)'] = merged_portfolio_sp_latest['Ticker Share Value'] - merged_portfolio_sp_latest['Cost Basis']\n",
"\n",
"# This column calculates profit / loss for SP 500.\n",
"merged_portfolio_sp_latest['SP 500 Gain / (Loss)'] = merged_portfolio_sp_latest['SP 500 Value'] - merged_portfolio_sp_latest['Cost Basis']\n",
"\n",
"merged_portfolio_sp_latest.head()"
]
},
{
"cell_type": "code",
"execution_count": 74,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" ... | \n",
" SP 500 Latest Close | \n",
" SP Return | \n",
" Abs. Return Compare | \n",
" Ticker Share Value | \n",
" SP 500 Value | \n",
" Abs Value Compare | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" ... | \n",
" 2677.669922 | \n",
" 0.774008 | \n",
" 0.901833 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 7372.484815 | \n",
" 13700.000000 | \n",
" 6327.515185 | \n",
" 2017-12-29 | \n",
" 168.542831 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" ... | \n",
" 2677.669922 | \n",
" 0.444041 | \n",
" 0.110050 | \n",
" 12727.999900 | \n",
" 11826.691726 | \n",
" 901.308174 | \n",
" 4537.999900 | \n",
" 3636.691726 | \n",
" 2017-12-29 | \n",
" 138.831009 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" ... | \n",
" 2677.669922 | \n",
" 0.641353 | \n",
" 0.190628 | \n",
" 15569.999700 | \n",
" 13949.856316 | \n",
" 1620.143384 | \n",
" 7070.999700 | \n",
" 5450.856316 | \n",
" 2017-12-29 | \n",
" 171.037949 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" ... | \n",
" 2677.669922 | \n",
" 0.324307 | \n",
" 1.560506 | \n",
" 23592.000000 | \n",
" 10830.185496 | \n",
" 12761.814504 | \n",
" 15414.000000 | \n",
" 2652.185496 | \n",
" 2017-12-29 | \n",
" 31.309999 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" ... | \n",
" 2677.669922 | \n",
" 0.393285 | \n",
" 1.277951 | \n",
" 21779.251125 | \n",
" 11359.797214 | \n",
" 10419.453911 | \n",
" 13626.001125 | \n",
" 3206.547214 | \n",
" 2017-12-29 | \n",
" 191.960007 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 21 columns
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" ... SP 500 Latest Close SP Return Abs. Return Compare \\\n",
"0 ... 2677.669922 0.774008 0.901833 \n",
"1 ... 2677.669922 0.444041 0.110050 \n",
"2 ... 2677.669922 0.641353 0.190628 \n",
"3 ... 2677.669922 0.324307 1.560506 \n",
"4 ... 2677.669922 0.393285 1.277951 \n",
"\n",
" Ticker Share Value SP 500 Value Abs Value Compare Stock Gain / (Loss) \\\n",
"0 21875.000000 14502.515185 7372.484815 13700.000000 \n",
"1 12727.999900 11826.691726 901.308174 4537.999900 \n",
"2 15569.999700 13949.856316 1620.143384 7070.999700 \n",
"3 23592.000000 10830.185496 12761.814504 15414.000000 \n",
"4 21779.251125 11359.797214 10419.453911 13626.001125 \n",
"\n",
" SP 500 Gain / (Loss) Date Adj Close \n",
"0 6327.515185 2017-12-29 168.542831 \n",
"1 3636.691726 2017-12-29 138.831009 \n",
"2 5450.856316 2017-12-29 171.037949 \n",
"3 2652.185496 2017-12-29 31.309999 \n",
"4 3206.547214 2017-12-29 191.960007 \n",
"\n",
"[5 rows x 21 columns]"
]
},
"execution_count": 74,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Merge the overall dataframe with the adj close start of year dataframe for YTD tracking of tickers.\n",
"# Should not need to do the outer join;\n",
"\n",
"merged_portfolio_sp_latest_YTD = pd.merge(merged_portfolio_sp_latest, adj_close_start, on='Ticker')\n",
"# , how='outer'\n",
"\n",
"merged_portfolio_sp_latest_YTD.head()"
]
},
{
"cell_type": "code",
"execution_count": 75,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" Equiv SP Shares | \n",
" SP 500 Latest Close | \n",
" SP Return | \n",
" Abs. Return Compare | \n",
" Ticker Share Value | \n",
" SP 500 Value | \n",
" Abs Value Compare | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
" Ticker Start Year Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" 5.416095 | \n",
" 2677.669922 | \n",
" 0.774008 | \n",
" 0.901833 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 7372.484815 | \n",
" 13700.000000 | \n",
" 6327.515185 | \n",
" 168.542831 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" 4.416785 | \n",
" 2677.669922 | \n",
" 0.444041 | \n",
" 0.110050 | \n",
" 12727.999900 | \n",
" 11826.691726 | \n",
" 901.308174 | \n",
" 4537.999900 | \n",
" 3636.691726 | \n",
" 138.831009 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" 5.209700 | \n",
" 2677.669922 | \n",
" 0.641353 | \n",
" 0.190628 | \n",
" 15569.999700 | \n",
" 13949.856316 | \n",
" 1620.143384 | \n",
" 7070.999700 | \n",
" 5450.856316 | \n",
" 171.037949 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" 4.044631 | \n",
" 2677.669922 | \n",
" 0.324307 | \n",
" 1.560506 | \n",
" 23592.000000 | \n",
" 10830.185496 | \n",
" 12761.814504 | \n",
" 15414.000000 | \n",
" 2652.185496 | \n",
" 31.309999 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" 4.242419 | \n",
" 2677.669922 | \n",
" 0.393285 | \n",
" 1.277951 | \n",
" 21779.251125 | \n",
" 11359.797214 | \n",
" 10419.453911 | \n",
" 13626.001125 | \n",
" 3206.547214 | \n",
" 191.960007 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" Equiv SP Shares SP 500 Latest Close SP Return Abs. Return Compare \\\n",
"0 5.416095 2677.669922 0.774008 0.901833 \n",
"1 4.416785 2677.669922 0.444041 0.110050 \n",
"2 5.209700 2677.669922 0.641353 0.190628 \n",
"3 4.044631 2677.669922 0.324307 1.560506 \n",
"4 4.242419 2677.669922 0.393285 1.277951 \n",
"\n",
" Ticker Share Value SP 500 Value Abs Value Compare Stock Gain / (Loss) \\\n",
"0 21875.000000 14502.515185 7372.484815 13700.000000 \n",
"1 12727.999900 11826.691726 901.308174 4537.999900 \n",
"2 15569.999700 13949.856316 1620.143384 7070.999700 \n",
"3 23592.000000 10830.185496 12761.814504 15414.000000 \n",
"4 21779.251125 11359.797214 10419.453911 13626.001125 \n",
"\n",
" SP 500 Gain / (Loss) Ticker Start Year Close \n",
"0 6327.515185 168.542831 \n",
"1 3636.691726 138.831009 \n",
"2 5450.856316 171.037949 \n",
"3 2652.185496 31.309999 \n",
"4 3206.547214 191.960007 "
]
},
"execution_count": 75,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Deleting date again as it's an unnecessary column. Explaining that new column is the Ticker Start of Year Close.\n",
"\n",
"del merged_portfolio_sp_latest_YTD['Date']\n",
"\n",
"merged_portfolio_sp_latest_YTD.rename(columns={'Adj Close': 'Ticker Start Year Close'}, inplace=True)\n",
"\n",
"merged_portfolio_sp_latest_YTD.head()"
]
},
{
"cell_type": "code",
"execution_count": 76,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" ... | \n",
" SP Return | \n",
" Abs. Return Compare | \n",
" Ticker Share Value | \n",
" SP 500 Value | \n",
" Abs Value Compare | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
" Ticker Start Year Close | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" ... | \n",
" 0.774008 | \n",
" 0.901833 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 7372.484815 | \n",
" 13700.000000 | \n",
" 6327.515185 | \n",
" 168.542831 | \n",
" 2017-12-29 | \n",
" 2673.610107 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" ... | \n",
" 0.444041 | \n",
" 0.110050 | \n",
" 12727.999900 | \n",
" 11826.691726 | \n",
" 901.308174 | \n",
" 4537.999900 | \n",
" 3636.691726 | \n",
" 138.831009 | \n",
" 2017-12-29 | \n",
" 2673.610107 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" ... | \n",
" 0.641353 | \n",
" 0.190628 | \n",
" 15569.999700 | \n",
" 13949.856316 | \n",
" 1620.143384 | \n",
" 7070.999700 | \n",
" 5450.856316 | \n",
" 171.037949 | \n",
" 2017-12-29 | \n",
" 2673.610107 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" ... | \n",
" 0.324307 | \n",
" 1.560506 | \n",
" 23592.000000 | \n",
" 10830.185496 | \n",
" 12761.814504 | \n",
" 15414.000000 | \n",
" 2652.185496 | \n",
" 31.309999 | \n",
" 2017-12-29 | \n",
" 2673.610107 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" ... | \n",
" 0.393285 | \n",
" 1.277951 | \n",
" 21779.251125 | \n",
" 11359.797214 | \n",
" 10419.453911 | \n",
" 13626.001125 | \n",
" 3206.547214 | \n",
" 191.960007 | \n",
" 2017-12-29 | \n",
" 2673.610107 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 22 columns
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" ... SP Return Abs. Return Compare Ticker Share Value \\\n",
"0 ... 0.774008 0.901833 21875.000000 \n",
"1 ... 0.444041 0.110050 12727.999900 \n",
"2 ... 0.641353 0.190628 15569.999700 \n",
"3 ... 0.324307 1.560506 23592.000000 \n",
"4 ... 0.393285 1.277951 21779.251125 \n",
"\n",
" SP 500 Value Abs Value Compare Stock Gain / (Loss) SP 500 Gain / (Loss) \\\n",
"0 14502.515185 7372.484815 13700.000000 6327.515185 \n",
"1 11826.691726 901.308174 4537.999900 3636.691726 \n",
"2 13949.856316 1620.143384 7070.999700 5450.856316 \n",
"3 10830.185496 12761.814504 15414.000000 2652.185496 \n",
"4 11359.797214 10419.453911 13626.001125 3206.547214 \n",
"\n",
" Ticker Start Year Close Date Adj Close \n",
"0 168.542831 2017-12-29 2673.610107 \n",
"1 138.831009 2017-12-29 2673.610107 \n",
"2 171.037949 2017-12-29 2673.610107 \n",
"3 31.309999 2017-12-29 2673.610107 \n",
"4 191.960007 2017-12-29 2673.610107 \n",
"\n",
"[5 rows x 22 columns]"
]
},
"execution_count": 76,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Join the SP 500 start of year with current dataframe for SP 500 ytd comparisons to tickers.\n",
"\n",
"merged_portfolio_sp_latest_YTD_sp = pd.merge(merged_portfolio_sp_latest_YTD, sp_500_adj_close_start\n",
" , left_on='Start of Year', right_on='Date')\n",
"\n",
"merged_portfolio_sp_latest_YTD_sp.head()"
]
},
{
"cell_type": "code",
"execution_count": 77,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" ... | \n",
" Abs. Return Compare | \n",
" Ticker Share Value | \n",
" SP 500 Value | \n",
" Abs Value Compare | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
" Ticker Start Year Close | \n",
" SP Start Year Close | \n",
" Share YTD | \n",
" SP 500 YTD | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" ... | \n",
" 0.901833 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 7372.484815 | \n",
" 13700.000000 | \n",
" 6327.515185 | \n",
" 168.542831 | \n",
" 2673.610107 | \n",
" 0.038312 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" ... | \n",
" 0.110050 | \n",
" 12727.999900 | \n",
" 11826.691726 | \n",
" 901.308174 | \n",
" 4537.999900 | \n",
" 3636.691726 | \n",
" 138.831009 | \n",
" 2673.610107 | \n",
" -0.083202 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" ... | \n",
" 0.190628 | \n",
" 15569.999700 | \n",
" 13949.856316 | \n",
" 1620.143384 | \n",
" 7070.999700 | \n",
" 5450.856316 | \n",
" 171.037949 | \n",
" 2673.610107 | \n",
" -0.089676 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" ... | \n",
" 1.560506 | \n",
" 23592.000000 | \n",
" 10830.185496 | \n",
" 12761.814504 | \n",
" 15414.000000 | \n",
" 2652.185496 | \n",
" 31.309999 | \n",
" 2673.610107 | \n",
" 0.255829 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" ... | \n",
" 1.277951 | \n",
" 21779.251125 | \n",
" 11359.797214 | \n",
" 10419.453911 | \n",
" 13626.001125 | \n",
" 3206.547214 | \n",
" 191.960007 | \n",
" 2673.610107 | \n",
" 0.512763 | \n",
" 0.001518 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 23 columns
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"\n",
" ... Abs. Return Compare Ticker Share Value SP 500 Value \\\n",
"0 ... 0.901833 21875.000000 14502.515185 \n",
"1 ... 0.110050 12727.999900 11826.691726 \n",
"2 ... 0.190628 15569.999700 13949.856316 \n",
"3 ... 1.560506 23592.000000 10830.185496 \n",
"4 ... 1.277951 21779.251125 11359.797214 \n",
"\n",
" Abs Value Compare Stock Gain / (Loss) SP 500 Gain / (Loss) \\\n",
"0 7372.484815 13700.000000 6327.515185 \n",
"1 901.308174 4537.999900 3636.691726 \n",
"2 1620.143384 7070.999700 5450.856316 \n",
"3 12761.814504 15414.000000 2652.185496 \n",
"4 10419.453911 13626.001125 3206.547214 \n",
"\n",
" Ticker Start Year Close SP Start Year Close Share YTD SP 500 YTD \n",
"0 168.542831 2673.610107 0.038312 0.001518 \n",
"1 138.831009 2673.610107 -0.083202 0.001518 \n",
"2 171.037949 2673.610107 -0.089676 0.001518 \n",
"3 31.309999 2673.610107 0.255829 0.001518 \n",
"4 191.960007 2673.610107 0.512763 0.001518 \n",
"\n",
"[5 rows x 23 columns]"
]
},
"execution_count": 77,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Deleting another unneeded Date column.\n",
"\n",
"del merged_portfolio_sp_latest_YTD_sp['Date']\n",
"\n",
"# Renaming so that it's clear this column is SP 500 start of year close.\n",
"merged_portfolio_sp_latest_YTD_sp.rename(columns={'Adj Close': 'SP Start Year Close'}, inplace=True)\n",
"\n",
"# YTD return for portfolio position.\n",
"merged_portfolio_sp_latest_YTD_sp['Share YTD'] = merged_portfolio_sp_latest_YTD_sp['Ticker Adj Close'] / merged_portfolio_sp_latest_YTD_sp['Ticker Start Year Close'] - 1\n",
"\n",
"# YTD return for SP to run compares.\n",
"merged_portfolio_sp_latest_YTD_sp['SP 500 YTD'] = merged_portfolio_sp_latest_YTD_sp['SP 500 Latest Close'] / merged_portfolio_sp_latest_YTD_sp['SP Start Year Close'] - 1\n",
"\n",
"merged_portfolio_sp_latest_YTD_sp.head()"
]
},
{
"cell_type": "code",
"execution_count": 79,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" ... | \n",
" Abs. Return Compare | \n",
" Ticker Share Value | \n",
" SP 500 Value | \n",
" Abs Value Compare | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
" Ticker Start Year Close | \n",
" SP Start Year Close | \n",
" Share YTD | \n",
" SP 500 YTD | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" ... | \n",
" 0.901833 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 7372.484815 | \n",
" 13700.000000 | \n",
" 6327.515185 | \n",
" 168.542831 | \n",
" 2673.610107 | \n",
" 0.038312 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 6 | \n",
" FB | \n",
" 2013-12-13 | \n",
" 150 | \n",
" 53.32 | \n",
" 7998.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.940002 | \n",
" 2.299700 | \n",
" 1775.319946 | \n",
" ... | \n",
" 1.791425 | \n",
" 26391.000300 | \n",
" 12063.179983 | \n",
" 14327.820317 | \n",
" 18393.000300 | \n",
" 4065.179983 | \n",
" 176.460007 | \n",
" 2673.610107 | \n",
" -0.002947 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" ... | \n",
" 0.110050 | \n",
" 12727.999900 | \n",
" 11826.691726 | \n",
" 901.308174 | \n",
" 4537.999900 | \n",
" 3636.691726 | \n",
" 138.831009 | \n",
" 2673.610107 | \n",
" -0.083202 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" ... | \n",
" 0.190628 | \n",
" 15569.999700 | \n",
" 13949.856316 | \n",
" 1620.143384 | \n",
" 7070.999700 | \n",
" 5450.856316 | \n",
" 171.037949 | \n",
" 2673.610107 | \n",
" -0.089676 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" ... | \n",
" 1.560506 | \n",
" 23592.000000 | \n",
" 10830.185496 | \n",
" 12761.814504 | \n",
" 15414.000000 | \n",
" 2652.185496 | \n",
" 31.309999 | \n",
" 2673.610107 | \n",
" 0.255829 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" ... | \n",
" 1.277951 | \n",
" 21779.251125 | \n",
" 11359.797214 | \n",
" 10419.453911 | \n",
" 13626.001125 | \n",
" 3206.547214 | \n",
" 191.960007 | \n",
" 2673.610107 | \n",
" 0.512763 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 7 | \n",
" TWTR | \n",
" 2015-01-05 | \n",
" 225 | \n",
" 36.38 | \n",
" 8185.50 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 32.240002 | \n",
" -0.113799 | \n",
" 2020.579956 | \n",
" ... | \n",
" -0.438997 | \n",
" 7254.000450 | \n",
" 10847.413923 | \n",
" -3593.413473 | \n",
" -931.499550 | \n",
" 2661.913923 | \n",
" 24.010000 | \n",
" 2673.610107 | \n",
" 0.342774 | \n",
" 0.001518 | \n",
"
\n",
" \n",
" 5 | \n",
" WMT | \n",
" 2013-08-14 | \n",
" 125 | \n",
" 68.30 | \n",
" 8537.50 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 89.080002 | \n",
" 0.304246 | \n",
" 1685.390015 | \n",
" ... | \n",
" -0.284508 | \n",
" 11135.000250 | \n",
" 13563.986232 | \n",
" -2428.985982 | \n",
" 2597.500250 | \n",
" 5026.486232 | \n",
" 98.750000 | \n",
" 2673.610107 | \n",
" -0.097924 | \n",
" 0.001518 | \n",
"
\n",
" \n",
"
\n",
"
8 rows × 23 columns
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"6 FB 2013-12-13 150 53.32 7998.00 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"7 TWTR 2015-01-05 225 36.38 8185.50 2017-12-29 \n",
"5 WMT 2013-08-14 125 68.30 8537.50 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"6 2018-03-01 175.940002 2.299700 1775.319946 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"4 2018-03-01 290.390015 1.671236 1921.839966 \n",
"7 2018-03-01 32.240002 -0.113799 2020.579956 \n",
"5 2018-03-01 89.080002 0.304246 1685.390015 \n",
"\n",
" ... Abs. Return Compare Ticker Share Value SP 500 Value \\\n",
"0 ... 0.901833 21875.000000 14502.515185 \n",
"6 ... 1.791425 26391.000300 12063.179983 \n",
"1 ... 0.110050 12727.999900 11826.691726 \n",
"2 ... 0.190628 15569.999700 13949.856316 \n",
"3 ... 1.560506 23592.000000 10830.185496 \n",
"4 ... 1.277951 21779.251125 11359.797214 \n",
"7 ... -0.438997 7254.000450 10847.413923 \n",
"5 ... -0.284508 11135.000250 13563.986232 \n",
"\n",
" Abs Value Compare Stock Gain / (Loss) SP 500 Gain / (Loss) \\\n",
"0 7372.484815 13700.000000 6327.515185 \n",
"6 14327.820317 18393.000300 4065.179983 \n",
"1 901.308174 4537.999900 3636.691726 \n",
"2 1620.143384 7070.999700 5450.856316 \n",
"3 12761.814504 15414.000000 2652.185496 \n",
"4 10419.453911 13626.001125 3206.547214 \n",
"7 -3593.413473 -931.499550 2661.913923 \n",
"5 -2428.985982 2597.500250 5026.486232 \n",
"\n",
" Ticker Start Year Close SP Start Year Close Share YTD SP 500 YTD \n",
"0 168.542831 2673.610107 0.038312 0.001518 \n",
"6 176.460007 2673.610107 -0.002947 0.001518 \n",
"1 138.831009 2673.610107 -0.083202 0.001518 \n",
"2 171.037949 2673.610107 -0.089676 0.001518 \n",
"3 31.309999 2673.610107 0.255829 0.001518 \n",
"4 191.960007 2673.610107 0.512763 0.001518 \n",
"7 24.010000 2673.610107 0.342774 0.001518 \n",
"5 98.750000 2673.610107 -0.097924 0.001518 \n",
"\n",
"[8 rows x 23 columns]"
]
},
"execution_count": 79,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"merged_portfolio_sp_latest_YTD_sp = merged_portfolio_sp_latest_YTD_sp.sort_values(by='Ticker', ascending=True)\n",
"merged_portfolio_sp_latest_YTD_sp"
]
},
{
"cell_type": "code",
"execution_count": 135,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" ... | \n",
" Stock Gain / (Loss) | \n",
" SP 500 Gain / (Loss) | \n",
" Ticker Start Year Close | \n",
" SP Start Year Close | \n",
" Share YTD | \n",
" SP 500 YTD | \n",
" Cum Invst | \n",
" Cum Ticker Returns | \n",
" Cum SP Returns | \n",
" Cum Ticker ROI Mult | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.0 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" ... | \n",
" 13700.0000 | \n",
" 6327.515185 | \n",
" 168.542831 | \n",
" 2673.610107 | \n",
" 0.038312 | \n",
" 0.001518 | \n",
" 8175.0 | \n",
" 21875.0000 | \n",
" 14502.515185 | \n",
" 2.675841 | \n",
"
\n",
" \n",
" 6 | \n",
" FB | \n",
" 2013-12-13 | \n",
" 150 | \n",
" 53.32 | \n",
" 7998.0 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.940002 | \n",
" 2.299700 | \n",
" 1775.319946 | \n",
" ... | \n",
" 18393.0003 | \n",
" 4065.179983 | \n",
" 176.460007 | \n",
" 2673.610107 | \n",
" -0.002947 | \n",
" 0.001518 | \n",
" 16173.0 | \n",
" 48266.0003 | \n",
" 26565.695168 | \n",
" 2.984357 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.0 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" ... | \n",
" 4537.9999 | \n",
" 3636.691726 | \n",
" 138.831009 | \n",
" 2673.610107 | \n",
" -0.083202 | \n",
" 0.001518 | \n",
" 24363.0 | \n",
" 60994.0002 | \n",
" 38392.386894 | \n",
" 2.503550 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.0 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" ... | \n",
" 7070.9997 | \n",
" 5450.856316 | \n",
" 171.037949 | \n",
" 2673.610107 | \n",
" -0.089676 | \n",
" 0.001518 | \n",
" 32862.0 | \n",
" 76563.9999 | \n",
" 52342.243210 | \n",
" 2.329864 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.0 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" ... | \n",
" 15414.0000 | \n",
" 2652.185496 | \n",
" 31.309999 | \n",
" 2673.610107 | \n",
" 0.255829 | \n",
" 0.001518 | \n",
" 41040.0 | \n",
" 100155.9999 | \n",
" 63172.428706 | \n",
" 2.440448 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 27 columns
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.0 2017-12-29 \n",
"6 FB 2013-12-13 150 53.32 7998.0 2017-12-29 \n",
"1 JNJ 2014-02-27 100 81.90 8190.0 2017-12-29 \n",
"2 MCD 2013-06-04 100 84.99 8499.0 2017-12-29 \n",
"3 MTCH 2015-12-14 600 13.63 8178.0 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"6 2018-03-01 175.940002 2.299700 1775.319946 \n",
"1 2018-03-01 127.279999 0.554090 1854.290039 \n",
"2 2018-03-01 155.699997 0.831980 1631.380005 \n",
"3 2018-03-01 39.320000 1.884813 2021.939941 \n",
"\n",
" ... Stock Gain / (Loss) SP 500 Gain / (Loss) \\\n",
"0 ... 13700.0000 6327.515185 \n",
"6 ... 18393.0003 4065.179983 \n",
"1 ... 4537.9999 3636.691726 \n",
"2 ... 7070.9997 5450.856316 \n",
"3 ... 15414.0000 2652.185496 \n",
"\n",
" Ticker Start Year Close SP Start Year Close Share YTD SP 500 YTD \\\n",
"0 168.542831 2673.610107 0.038312 0.001518 \n",
"6 176.460007 2673.610107 -0.002947 0.001518 \n",
"1 138.831009 2673.610107 -0.083202 0.001518 \n",
"2 171.037949 2673.610107 -0.089676 0.001518 \n",
"3 31.309999 2673.610107 0.255829 0.001518 \n",
"\n",
" Cum Invst Cum Ticker Returns Cum SP Returns Cum Ticker ROI Mult \n",
"0 8175.0 21875.0000 14502.515185 2.675841 \n",
"6 16173.0 48266.0003 26565.695168 2.984357 \n",
"1 24363.0 60994.0002 38392.386894 2.503550 \n",
"2 32862.0 76563.9999 52342.243210 2.329864 \n",
"3 41040.0 100155.9999 63172.428706 2.440448 \n",
"\n",
"[5 rows x 27 columns]"
]
},
"execution_count": 135,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Cumulative sum of original investment\n",
"merged_portfolio_sp_latest_YTD_sp['Cum Invst'] = merged_portfolio_sp_latest_YTD_sp['Cost Basis'].cumsum()\n",
"\n",
"# Cumulative sum of Ticker Share Value (latest FMV based on initial quantity purchased).\n",
"merged_portfolio_sp_latest_YTD_sp['Cum Ticker Returns'] = merged_portfolio_sp_latest_YTD_sp['Ticker Share Value'].cumsum()\n",
"\n",
"# Cumulative sum of SP Share Value (latest FMV driven off of initial SP equiv purchase).\n",
"merged_portfolio_sp_latest_YTD_sp['Cum SP Returns'] = merged_portfolio_sp_latest_YTD_sp['SP 500 Value'].cumsum()\n",
"\n",
"# Cumulative CoC multiple return for stock investments\n",
"merged_portfolio_sp_latest_YTD_sp['Cum Ticker ROI Mult'] = merged_portfolio_sp_latest_YTD_sp['Cum Ticker Returns'] / merged_portfolio_sp_latest_YTD_sp['Cum Invst']\n",
"\n",
"merged_portfolio_sp_latest_YTD_sp.head()\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Assessing Where Positions are At versus Highest Close"
]
},
{
"cell_type": "code",
"execution_count": 139,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-01-02 | \n",
" 56.532822 | \n",
"
\n",
" \n",
" 1 | \n",
" AAPL | \n",
" 2013-01-03 | \n",
" 55.819248 | \n",
"
\n",
" \n",
" 2 | \n",
" AAPL | \n",
" 2013-01-04 | \n",
" 54.264439 | \n",
"
\n",
" \n",
" 3 | \n",
" AAPL | \n",
" 2013-01-07 | \n",
" 53.945232 | \n",
"
\n",
" \n",
" 4 | \n",
" AAPL | \n",
" 2013-01-08 | \n",
" 54.090427 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close\n",
"0 AAPL 2013-01-02 56.532822\n",
"1 AAPL 2013-01-03 55.819248\n",
"2 AAPL 2013-01-04 54.264439\n",
"3 AAPL 2013-01-07 53.945232\n",
"4 AAPL 2013-01-08 54.090427"
]
},
"execution_count": 139,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Referencing the adj_close dataframe from above\n",
"\n",
"adj_close.head()"
]
},
{
"cell_type": "code",
"execution_count": 140,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 1 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 2 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 3 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 4 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29\n",
"1 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29\n",
"2 MCD 2013-06-04 100 84.99 8499.00 2017-12-29\n",
"3 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29\n",
"4 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29"
]
},
"execution_count": 140,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"portfolio_df.head()"
]
},
{
"cell_type": "code",
"execution_count": 141,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
" index | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-01-02 | \n",
" 56.532822 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.4 | \n",
" 8175.0 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 1 | \n",
" AAPL | \n",
" 2013-01-03 | \n",
" 55.819248 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.4 | \n",
" 8175.0 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 2 | \n",
" AAPL | \n",
" 2013-01-04 | \n",
" 54.264439 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.4 | \n",
" 8175.0 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 3 | \n",
" AAPL | \n",
" 2013-01-07 | \n",
" 53.945232 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.4 | \n",
" 8175.0 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
" 4 | \n",
" AAPL | \n",
" 2013-01-08 | \n",
" 54.090427 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.4 | \n",
" 8175.0 | \n",
" 2017-12-29 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close index Acquisition Date Quantity Unit Cost \\\n",
"0 AAPL 2013-01-02 56.532822 0 2013-02-07 125 65.4 \n",
"1 AAPL 2013-01-03 55.819248 0 2013-02-07 125 65.4 \n",
"2 AAPL 2013-01-04 54.264439 0 2013-02-07 125 65.4 \n",
"3 AAPL 2013-01-07 53.945232 0 2013-02-07 125 65.4 \n",
"4 AAPL 2013-01-08 54.090427 0 2013-02-07 125 65.4 \n",
"\n",
" Cost Basis Start of Year \n",
"0 8175.0 2017-12-29 \n",
"1 8175.0 2017-12-29 \n",
"2 8175.0 2017-12-29 \n",
"3 8175.0 2017-12-29 \n",
"4 8175.0 2017-12-29 "
]
},
"execution_count": 141,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Need to factor in that some positions were purchased much more recently than others.\n",
"# Join adj_close dataframe with portfolio in order to have acquisition date.\n",
"\n",
"portfolio_df.reset_index(inplace=True)\n",
"\n",
"adj_close_acq_date = pd.merge(adj_close, portfolio_df, on='Ticker')\n",
"\n",
"adj_close_acq_date.head()"
]
},
{
"cell_type": "code",
"execution_count": 142,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"# delete_columns = ['Quantity', 'Unit Cost', 'Cost Basis', 'Start of Year']\n",
"\n",
"del adj_close_acq_date['Quantity']\n",
"del adj_close_acq_date['Unit Cost']\n",
"del adj_close_acq_date['Cost Basis']\n",
"del adj_close_acq_date['Start of Year']\n",
"\n",
"# Sort by these columns in this order in order to make it clearer where compare for each position should begin.\n",
"adj_close_acq_date.sort_values(by=['Ticker', 'Acquisition Date', 'Date'], ascending=[True, True, True], inplace=True)"
]
},
{
"cell_type": "code",
"execution_count": 143,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
" index | \n",
" Acquisition Date | \n",
" Date Delta | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-01-02 | \n",
" 56.532822 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" -3110400000000000 | \n",
"
\n",
" \n",
" 1 | \n",
" AAPL | \n",
" 2013-01-03 | \n",
" 55.819248 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" -3024000000000000 | \n",
"
\n",
" \n",
" 2 | \n",
" AAPL | \n",
" 2013-01-04 | \n",
" 54.264439 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" -2937600000000000 | \n",
"
\n",
" \n",
" 3 | \n",
" AAPL | \n",
" 2013-01-07 | \n",
" 53.945232 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" -2678400000000000 | \n",
"
\n",
" \n",
" 4 | \n",
" AAPL | \n",
" 2013-01-08 | \n",
" 54.090427 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" -2592000000000000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close index Acquisition Date Date Delta\n",
"0 AAPL 2013-01-02 56.532822 0 2013-02-07 -3110400000000000\n",
"1 AAPL 2013-01-03 55.819248 0 2013-02-07 -3024000000000000\n",
"2 AAPL 2013-01-04 54.264439 0 2013-02-07 -2937600000000000\n",
"3 AAPL 2013-01-07 53.945232 0 2013-02-07 -2678400000000000\n",
"4 AAPL 2013-01-08 54.090427 0 2013-02-07 -2592000000000000"
]
},
"execution_count": 143,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Anything less than 0 means that the stock close was prior to acquisition.\n",
"adj_close_acq_date['Date Delta'] = adj_close_acq_date['Date'] - adj_close_acq_date['Acquisition Date']\n",
"\n",
"adj_close_acq_date['Date Delta'] = adj_close_acq_date[['Date Delta']].apply(pd.to_numeric) \n",
"\n",
"adj_close_acq_date.head()"
]
},
{
"cell_type": "code",
"execution_count": 144,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Date | \n",
" Adj Close | \n",
" index | \n",
" Acquisition Date | \n",
" Date Delta | \n",
"
\n",
" \n",
" \n",
" \n",
" 25 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 50.250080 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 0 | \n",
"
\n",
" \n",
" 26 | \n",
" AAPL | \n",
" 2013-02-08 | \n",
" 50.975567 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 86400000000000 | \n",
"
\n",
" \n",
" 27 | \n",
" AAPL | \n",
" 2013-02-11 | \n",
" 51.506813 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 345600000000000 | \n",
"
\n",
" \n",
" 28 | \n",
" AAPL | \n",
" 2013-02-12 | \n",
" 50.215736 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 432000000000000 | \n",
"
\n",
" \n",
" 29 | \n",
" AAPL | \n",
" 2013-02-13 | \n",
" 50.120216 | \n",
" 0 | \n",
" 2013-02-07 | \n",
" 518400000000000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Date Adj Close index Acquisition Date Date Delta\n",
"25 AAPL 2013-02-07 50.250080 0 2013-02-07 0\n",
"26 AAPL 2013-02-08 50.975567 0 2013-02-07 86400000000000\n",
"27 AAPL 2013-02-11 51.506813 0 2013-02-07 345600000000000\n",
"28 AAPL 2013-02-12 50.215736 0 2013-02-07 432000000000000\n",
"29 AAPL 2013-02-13 50.120216 0 2013-02-07 518400000000000"
]
},
"execution_count": 144,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Modified the dataframe being evaluated to look at highest close which occurred after Acquisition Date (aka, not prior to purchase).\n",
"\n",
"adj_close_acq_date_modified = adj_close_acq_date[adj_close_acq_date['Date Delta']>=0]\n",
"\n",
"adj_close_acq_date_modified.head()"
]
},
{
"cell_type": "code",
"execution_count": 145,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Adj Close | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 178.970001 | \n",
"
\n",
" \n",
" 1 | \n",
" FB | \n",
" 2013-12-13 | \n",
" 193.089996 | \n",
"
\n",
" \n",
" 2 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 147.197433 | \n",
"
\n",
" \n",
" 3 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 177.238724 | \n",
"
\n",
" \n",
" 4 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 42.099998 | \n",
"
\n",
" \n",
" 5 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 294.160004 | \n",
"
\n",
" \n",
" 6 | \n",
" TWTR | \n",
" 2015-01-05 | \n",
" 52.869999 | \n",
"
\n",
" \n",
" 7 | \n",
" WMT | \n",
" 2013-08-14 | \n",
" 109.550003 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Adj Close\n",
"0 AAPL 2013-02-07 178.970001\n",
"1 FB 2013-12-13 193.089996\n",
"2 JNJ 2014-02-27 147.197433\n",
"3 MCD 2013-06-04 177.238724\n",
"4 MTCH 2015-12-14 42.099998\n",
"5 NFLX 2016-01-14 294.160004\n",
"6 TWTR 2015-01-05 52.869999\n",
"7 WMT 2013-08-14 109.550003"
]
},
"execution_count": 145,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# This pivot table will index on the Ticker and Acquisition Date, and find the max adjusted close.\n",
"\n",
"adj_close_pivot = adj_close_acq_date_modified.pivot_table(index=['Ticker', 'Acquisition Date'], values='Adj Close', aggfunc=np.max)\n",
"\n",
"adj_close_pivot.reset_index(inplace=True)\n",
"\n",
"adj_close_pivot"
]
},
{
"cell_type": "code",
"execution_count": 146,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Adj Close | \n",
" Date | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 178.970001 | \n",
" 2018-02-26 | \n",
"
\n",
" \n",
" 1 | \n",
" FB | \n",
" 2013-12-13 | \n",
" 193.089996 | \n",
" 2018-02-01 | \n",
"
\n",
" \n",
" 2 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 147.197433 | \n",
" 2018-01-22 | \n",
"
\n",
" \n",
" 3 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 177.238724 | \n",
" 2018-01-26 | \n",
"
\n",
" \n",
" 4 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 42.099998 | \n",
" 2018-02-20 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Adj Close Date\n",
"0 AAPL 2013-02-07 178.970001 2018-02-26\n",
"1 FB 2013-12-13 193.089996 2018-02-01\n",
"2 JNJ 2014-02-27 147.197433 2018-01-22\n",
"3 MCD 2013-06-04 177.238724 2018-01-26\n",
"4 MTCH 2015-12-14 42.099998 2018-02-20"
]
},
"execution_count": 146,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Merge the adj close pivot table with the adj_close table in order to grab the date of the Adj Close High (good to know).\n",
"\n",
"adj_close_pivot_merged = pd.merge(adj_close_pivot, adj_close\n",
" , on=['Ticker', 'Adj Close'])\n",
"\n",
"adj_close_pivot_merged.head()"
]
},
{
"cell_type": "code",
"execution_count": 147,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Ticker | \n",
" Acquisition Date | \n",
" Quantity | \n",
" Unit Cost | \n",
" Cost Basis | \n",
" Start of Year | \n",
" Latest Date | \n",
" Ticker Adj Close | \n",
" ticker return | \n",
" SP 500 Initial Close | \n",
" ... | \n",
" SP Start Year Close | \n",
" Share YTD | \n",
" SP 500 YTD | \n",
" Cum Invst | \n",
" Cum Ticker Returns | \n",
" Cum SP Returns | \n",
" Cum Ticker ROI Mult | \n",
" Closing High Adj Close | \n",
" Closing High Adj Close Date | \n",
" Pct off High | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" AAPL | \n",
" 2013-02-07 | \n",
" 125 | \n",
" 65.40 | \n",
" 8175.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.000000 | \n",
" 1.675841 | \n",
" 1509.390015 | \n",
" ... | \n",
" 2673.610107 | \n",
" 0.038312 | \n",
" 0.001518 | \n",
" 8175.00 | \n",
" 21875.000000 | \n",
" 14502.515185 | \n",
" 2.675841 | \n",
" 178.970001 | \n",
" 2018-02-26 | \n",
" -0.022182 | \n",
"
\n",
" \n",
" 1 | \n",
" FB | \n",
" 2013-12-13 | \n",
" 150 | \n",
" 53.32 | \n",
" 7998.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 175.940002 | \n",
" 2.299700 | \n",
" 1775.319946 | \n",
" ... | \n",
" 2673.610107 | \n",
" -0.002947 | \n",
" 0.001518 | \n",
" 16173.00 | \n",
" 48266.000300 | \n",
" 26565.695168 | \n",
" 2.984357 | \n",
" 193.089996 | \n",
" 2018-02-01 | \n",
" -0.088819 | \n",
"
\n",
" \n",
" 2 | \n",
" JNJ | \n",
" 2014-02-27 | \n",
" 100 | \n",
" 81.90 | \n",
" 8190.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 127.279999 | \n",
" 0.554090 | \n",
" 1854.290039 | \n",
" ... | \n",
" 2673.610107 | \n",
" -0.083202 | \n",
" 0.001518 | \n",
" 24363.00 | \n",
" 60994.000200 | \n",
" 38392.386894 | \n",
" 2.503550 | \n",
" 147.197433 | \n",
" 2018-01-22 | \n",
" -0.135311 | \n",
"
\n",
" \n",
" 3 | \n",
" MCD | \n",
" 2013-06-04 | \n",
" 100 | \n",
" 84.99 | \n",
" 8499.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 155.699997 | \n",
" 0.831980 | \n",
" 1631.380005 | \n",
" ... | \n",
" 2673.610107 | \n",
" -0.089676 | \n",
" 0.001518 | \n",
" 32862.00 | \n",
" 76563.999900 | \n",
" 52342.243210 | \n",
" 2.329864 | \n",
" 177.238724 | \n",
" 2018-01-26 | \n",
" -0.121524 | \n",
"
\n",
" \n",
" 4 | \n",
" MTCH | \n",
" 2015-12-14 | \n",
" 600 | \n",
" 13.63 | \n",
" 8178.00 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 39.320000 | \n",
" 1.884813 | \n",
" 2021.939941 | \n",
" ... | \n",
" 2673.610107 | \n",
" 0.255829 | \n",
" 0.001518 | \n",
" 41040.00 | \n",
" 100155.999900 | \n",
" 63172.428706 | \n",
" 2.440448 | \n",
" 42.099998 | \n",
" 2018-02-20 | \n",
" -0.066033 | \n",
"
\n",
" \n",
" 5 | \n",
" NFLX | \n",
" 2016-01-14 | \n",
" 75 | \n",
" 108.71 | \n",
" 8153.25 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 290.390015 | \n",
" 1.671236 | \n",
" 1921.839966 | \n",
" ... | \n",
" 2673.610107 | \n",
" 0.512763 | \n",
" 0.001518 | \n",
" 49193.25 | \n",
" 121935.251025 | \n",
" 74532.225920 | \n",
" 2.478699 | \n",
" 294.160004 | \n",
" 2018-02-26 | \n",
" -0.012816 | \n",
"
\n",
" \n",
" 6 | \n",
" TWTR | \n",
" 2015-01-05 | \n",
" 225 | \n",
" 36.38 | \n",
" 8185.50 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 32.240002 | \n",
" -0.113799 | \n",
" 2020.579956 | \n",
" ... | \n",
" 2673.610107 | \n",
" 0.342774 | \n",
" 0.001518 | \n",
" 57378.75 | \n",
" 129189.251475 | \n",
" 85379.639842 | \n",
" 2.251517 | \n",
" 52.869999 | \n",
" 2015-04-07 | \n",
" -0.390202 | \n",
"
\n",
" \n",
" 7 | \n",
" WMT | \n",
" 2013-08-14 | \n",
" 125 | \n",
" 68.30 | \n",
" 8537.50 | \n",
" 2017-12-29 | \n",
" 2018-03-01 | \n",
" 89.080002 | \n",
" 0.304246 | \n",
" 1685.390015 | \n",
" ... | \n",
" 2673.610107 | \n",
" -0.097924 | \n",
" 0.001518 | \n",
" 65916.25 | \n",
" 140324.251725 | \n",
" 98943.626074 | \n",
" 2.128826 | \n",
" 109.550003 | \n",
" 2018-01-29 | \n",
" -0.186855 | \n",
"
\n",
" \n",
"
\n",
"
8 rows × 30 columns
\n",
"
"
],
"text/plain": [
" Ticker Acquisition Date Quantity Unit Cost Cost Basis Start of Year \\\n",
"0 AAPL 2013-02-07 125 65.40 8175.00 2017-12-29 \n",
"1 FB 2013-12-13 150 53.32 7998.00 2017-12-29 \n",
"2 JNJ 2014-02-27 100 81.90 8190.00 2017-12-29 \n",
"3 MCD 2013-06-04 100 84.99 8499.00 2017-12-29 \n",
"4 MTCH 2015-12-14 600 13.63 8178.00 2017-12-29 \n",
"5 NFLX 2016-01-14 75 108.71 8153.25 2017-12-29 \n",
"6 TWTR 2015-01-05 225 36.38 8185.50 2017-12-29 \n",
"7 WMT 2013-08-14 125 68.30 8537.50 2017-12-29 \n",
"\n",
" Latest Date Ticker Adj Close ticker return SP 500 Initial Close \\\n",
"0 2018-03-01 175.000000 1.675841 1509.390015 \n",
"1 2018-03-01 175.940002 2.299700 1775.319946 \n",
"2 2018-03-01 127.279999 0.554090 1854.290039 \n",
"3 2018-03-01 155.699997 0.831980 1631.380005 \n",
"4 2018-03-01 39.320000 1.884813 2021.939941 \n",
"5 2018-03-01 290.390015 1.671236 1921.839966 \n",
"6 2018-03-01 32.240002 -0.113799 2020.579956 \n",
"7 2018-03-01 89.080002 0.304246 1685.390015 \n",
"\n",
" ... SP Start Year Close Share YTD SP 500 YTD Cum Invst \\\n",
"0 ... 2673.610107 0.038312 0.001518 8175.00 \n",
"1 ... 2673.610107 -0.002947 0.001518 16173.00 \n",
"2 ... 2673.610107 -0.083202 0.001518 24363.00 \n",
"3 ... 2673.610107 -0.089676 0.001518 32862.00 \n",
"4 ... 2673.610107 0.255829 0.001518 41040.00 \n",
"5 ... 2673.610107 0.512763 0.001518 49193.25 \n",
"6 ... 2673.610107 0.342774 0.001518 57378.75 \n",
"7 ... 2673.610107 -0.097924 0.001518 65916.25 \n",
"\n",
" Cum Ticker Returns Cum SP Returns Cum Ticker ROI Mult \\\n",
"0 21875.000000 14502.515185 2.675841 \n",
"1 48266.000300 26565.695168 2.984357 \n",
"2 60994.000200 38392.386894 2.503550 \n",
"3 76563.999900 52342.243210 2.329864 \n",
"4 100155.999900 63172.428706 2.440448 \n",
"5 121935.251025 74532.225920 2.478699 \n",
"6 129189.251475 85379.639842 2.251517 \n",
"7 140324.251725 98943.626074 2.128826 \n",
"\n",
" Closing High Adj Close Closing High Adj Close Date Pct off High \n",
"0 178.970001 2018-02-26 -0.022182 \n",
"1 193.089996 2018-02-01 -0.088819 \n",
"2 147.197433 2018-01-22 -0.135311 \n",
"3 177.238724 2018-01-26 -0.121524 \n",
"4 42.099998 2018-02-20 -0.066033 \n",
"5 294.160004 2018-02-26 -0.012816 \n",
"6 52.869999 2015-04-07 -0.390202 \n",
"7 109.550003 2018-01-29 -0.186855 \n",
"\n",
"[8 rows x 30 columns]"
]
},
"execution_count": 147,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Merge the Adj Close pivot table with the master dataframe to have the closing high since you have owned the stock.\n",
"\n",
"merged_portfolio_sp_latest_YTD_sp_closing_high = pd.merge(merged_portfolio_sp_latest_YTD_sp, adj_close_pivot_merged\n",
" , on=['Ticker', 'Acquisition Date'])\n",
"\n",
"# Renaming so that it's clear that the new columns are two year closing high and two year closing high date.\n",
"merged_portfolio_sp_latest_YTD_sp_closing_high.rename(columns={'Adj Close': 'Closing High Adj Close', 'Date': 'Closing High Adj Close Date'}, inplace=True)\n",
"\n",
"merged_portfolio_sp_latest_YTD_sp_closing_high['Pct off High'] = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker Adj Close'] / merged_portfolio_sp_latest_YTD_sp_closing_high['Closing High Adj Close'] - 1 \n",
"\n",
"merged_portfolio_sp_latest_YTD_sp_closing_high"
]
},
{
"cell_type": "code",
"execution_count": 148,
"metadata": {},
"outputs": [],
"source": [
"# Not needed for this blog post -- this is if you have multiple positions for the same ticker with different acquisition dates.\n",
"# merged_portfolio_sp_latest_YTD_sp_closing_high['Counts'] = merged_portfolio_sp_latest_YTD_sp_closing_high.index\n",
"\n",
"# merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker #'] = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'].map(str) + ' ' + merged_portfolio_sp_latest_YTD_sp_closing_high['Counts'].map(str)\n",
"\n",
"# merged_portfolio_sp_latest_YTD_sp_closing_high.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## YTD and Trailing Stop Charts"
]
},
{
"cell_type": "code",
"execution_count": 154,
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.plotly.v1+json": {
"data": [
{
"name": "Ticker YTD",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
0.0383117392872081,
-0.0029468716954093033,
-0.0832019451792646,
-0.08967572453759953,
0.25582884879683343,
0.5127630986177238,
0.34277392753019575,
-0.09792403037974673
]
},
{
"name": "SP500 YTD",
"type": "scatter",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
0.0015184768300249285,
0.0015184768300249285,
0.0015184768300249285,
0.0015184768300249285,
0.0015184768300249285,
0.0015184768300249285,
0.0015184768300249285,
0.0015184768300249285
]
}
],
"layout": {
"barmode": "group",
"legend": {
"x": 0.8,
"y": 1
},
"title": "YTD Return vs S&P 500 YTD",
"xaxis": {
"title": "Ticker"
},
"yaxis": {
"tickformat": ".2%",
"title": "Returns"
}
}
},
"text/html": [
""
],
"text/vnd.plotly.v1+html": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Ploty is an outstanding resource for interactive charts.\n",
"\n",
"trace1 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp['Share YTD'][0:10],\n",
" name = 'Ticker YTD')\n",
"\n",
"trace2 = go.Scatter(\n",
" x = merged_portfolio_sp_latest_YTD_sp['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp['SP 500 YTD'][0:10],\n",
" name = 'SP500 YTD')\n",
" \n",
"data = [trace1, trace2]\n",
"\n",
"layout = go.Layout(title = 'YTD Return vs S&P 500 YTD'\n",
" , barmode = 'group'\n",
" , yaxis=dict(title='Returns', tickformat=\".2%\")\n",
" , xaxis=dict(title='Ticker')\n",
" , legend=dict(x=.8,y=1)\n",
" )\n",
"\n",
"fig = go.Figure(data=data, layout=layout)\n",
"iplot(fig)"
]
},
{
"cell_type": "code",
"execution_count": 150,
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.plotly.v1+json": {
"data": [
{
"name": "Pct off High",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
-0.022182494148837772,
-0.08881865635338249,
-0.13531101456096728,
-0.12152382117126947,
-0.06603320978780092,
-0.012816116904866504,
-0.3902023338415421,
-0.1868553212180195
]
}
],
"layout": {
"barmode": "group",
"legend": {
"x": 0.8,
"y": 1
},
"title": "Adj Close % off of High",
"xaxis": {
"title": "Ticker"
},
"yaxis": {
"tickformat": ".2%",
"title": "% Below Adj Close High"
}
}
},
"text/html": [
""
],
"text/vnd.plotly.v1+html": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Current Share Price versus Closing High Since Purchased\n",
"\n",
"trace1 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['Pct off High'][0:10],\n",
" name = 'Pct off High')\n",
" \n",
"data = [trace1]\n",
"\n",
"layout = go.Layout(title = 'Adj Close % off of High'\n",
" , barmode = 'group'\n",
" , yaxis=dict(title='% Below Adj Close High', tickformat=\".2%\")\n",
" , xaxis=dict(title='Ticker')\n",
" , legend=dict(x=.8,y=1)\n",
" )\n",
"\n",
"fig = go.Figure(data=data, layout=layout)\n",
"iplot(fig)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Total Return Comparison Charts"
]
},
{
"cell_type": "code",
"execution_count": 151,
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.plotly.v1+json": {
"data": [
{
"name": "Ticker Total Return",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
1.6758409785932717,
2.2996999624906227,
0.5540903418803418,
0.8319801976703143,
1.8848129126925897,
1.6712355349093921,
-0.11379873556899389,
0.3042460029282579
]
},
{
"name": "SP500 Total Return",
"type": "scatter",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
0.7740079736780292,
0.5082745665270636,
0.44404050374128134,
0.6413526669404044,
0.3243073484545207,
0.3932845446924169,
0.3251986955768853,
0.5887538778375878
]
}
],
"layout": {
"barmode": "group",
"legend": {
"x": 0.8,
"y": 1
},
"title": "Total Return vs S&P 500",
"xaxis": {
"tickformat": ".2%",
"title": "Ticker"
},
"yaxis": {
"tickformat": ".2%",
"title": "Returns"
}
}
},
"text/html": [
""
],
"text/vnd.plotly.v1+html": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"trace1 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['ticker return'][0:10],\n",
" name = 'Ticker Total Return')\n",
"\n",
"trace2 = go.Scatter(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['SP Return'][0:10],\n",
" name = 'SP500 Total Return')\n",
" \n",
"data = [trace1, trace2]\n",
"\n",
"layout = go.Layout(title = 'Total Return vs S&P 500'\n",
" , barmode = 'group'\n",
" , yaxis=dict(title='Returns', tickformat=\".2%\")\n",
" , xaxis=dict(title='Ticker', tickformat=\".2%\")\n",
" , legend=dict(x=.8,y=1)\n",
" )\n",
"\n",
"fig = go.Figure(data=data, layout=layout)\n",
"iplot(fig)"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"## Cumulative Returns Over Time"
]
},
{
"cell_type": "code",
"execution_count": 152,
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.plotly.v1+json": {
"data": [
{
"name": "Ticker Total Return ($)",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
13700,
18393.0003,
4537.9999,
7070.9997,
15414,
13626.001124999999,
-931.4995499999995,
2597.500250000001
]
},
{
"name": "SP 500 Total Return ($)",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
6327.515184817889,
4065.179983083455,
3636.691725641095,
5450.856316326495,
2652.1854956610705,
3206.547214013447,
2661.913922644596,
5026.486232038404
]
},
{
"name": "Ticker Total Return %",
"type": "scatter",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
1.6758409785932717,
2.2996999624906227,
0.5540903418803418,
0.8319801976703143,
1.8848129126925897,
1.6712355349093921,
-0.11379873556899389,
0.3042460029282579
],
"yaxis": "y2"
}
],
"layout": {
"barmode": "group",
"legend": {
"x": 0.75,
"y": 1
},
"title": "Gain / (Loss) Total Return vs S&P 500",
"xaxis": {
"title": "Ticker"
},
"yaxis": {
"title": "Gain / (Loss) ($)"
},
"yaxis2": {
"overlaying": "y",
"side": "right",
"tickformat": ".2%",
"title": "Ticker Return"
}
}
},
"text/html": [
""
],
"text/vnd.plotly.v1+html": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"trace1 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['Stock Gain / (Loss)'][0:10],\n",
" name = 'Ticker Total Return ($)')\n",
"\n",
"trace2 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['SP 500 Gain / (Loss)'][0:10],\n",
" name = 'SP 500 Total Return ($)')\n",
"\n",
"trace3 = go.Scatter(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'][0:10],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['ticker return'][0:10],\n",
" name = 'Ticker Total Return %',\n",
" yaxis='y2')\n",
"\n",
"data = [trace1, trace2, trace3]\n",
"\n",
"layout = go.Layout(title = 'Gain / (Loss) Total Return vs S&P 500'\n",
" , barmode = 'group'\n",
" , yaxis=dict(title='Gain / (Loss) ($)')\n",
" , yaxis2=dict(title='Ticker Return', overlaying='y', side='right', tickformat=\".2%\")\n",
" , xaxis=dict(title='Ticker')\n",
" , legend=dict(x=.75,y=1)\n",
" )\n",
"\n",
"fig = go.Figure(data=data, layout=layout)\n",
"iplot(fig)"
]
},
{
"cell_type": "code",
"execution_count": 159,
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.plotly.v1+json": {
"data": [
{
"name": "Cum Invst",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
8175.000000000001,
16173,
24363,
32862,
41040,
49193.25,
57378.75,
65916.25
]
},
{
"name": "Cum SP500 Returns",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
14502.51518481789,
26565.695167901344,
38392.38689354244,
52342.24320986893,
63172.42870553,
74532.22591954345,
85379.63984218804,
98943.62607422644
]
},
{
"name": "Cum Ticker Returns",
"type": "bar",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
21875,
48266.0003,
60994.0002,
76563.9999,
100155.9999,
121935.25102499999,
129189.251475,
140324.251725
]
},
{
"name": "Cum ROI Mult",
"type": "scatter",
"x": [
"AAPL",
"FB",
"JNJ",
"MCD",
"MTCH",
"NFLX",
"TWTR",
"WMT"
],
"y": [
2.6758409785932717,
2.9843566623384654,
2.503550474079547,
2.3298642778893552,
2.440448340643275,
2.478698826058453,
2.251517355728384,
2.128826377789999
],
"yaxis": "y2"
}
],
"layout": {
"barmode": "group",
"legend": {
"x": 0.4,
"y": 1
},
"title": "Total Cumulative Investments Over Time",
"xaxis": {
"title": "Ticker"
},
"yaxis": {
"title": "Returns"
},
"yaxis2": {
"overlaying": "y",
"side": "right",
"title": "Cum ROI Mult"
}
}
},
"text/html": [
""
],
"text/vnd.plotly.v1+html": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"trace1 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['Cum Invst'],\n",
" # mode = 'lines+markers',\n",
" name = 'Cum Invst')\n",
"\n",
"trace2 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['Cum SP Returns'],\n",
" # mode = 'lines+markers',\n",
" name = 'Cum SP500 Returns')\n",
"\n",
"trace3 = go.Bar(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['Cum Ticker Returns'],\n",
" # mode = 'lines+markers',\n",
" name = 'Cum Ticker Returns')\n",
"\n",
"trace4 = go.Scatter(\n",
" x = merged_portfolio_sp_latest_YTD_sp_closing_high['Ticker'],\n",
" y = merged_portfolio_sp_latest_YTD_sp_closing_high['Cum Ticker ROI Mult'],\n",
" # mode = 'lines+markers',\n",
" name = 'Cum ROI Mult'\n",
" , yaxis='y2')\n",
"\n",
"\n",
"data = [trace1, trace2, trace3, trace4]\n",
"\n",
"layout = go.Layout(title = 'Total Cumulative Investments Over Time'\n",
" , barmode = 'group'\n",
" , yaxis=dict(title='Returns')\n",
" , xaxis=dict(title='Ticker')\n",
" , legend=dict(x=.4,y=1)\n",
" , yaxis2=dict(title='Cum ROI Mult', overlaying='y', side='right') \n",
" )\n",
"\n",
"fig = go.Figure(data=data, layout=layout)\n",
"iplot(fig)"
]
},
{
"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
}