{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Acquisition DateTickerQuantityUnit CostCost BasisStart of Year
02013-02-07AAPL12565.408175.002017-12-29
12014-02-27JNJ10081.908190.002017-12-29
22013-06-04MCD10084.998499.002017-12-29
32015-12-14MTCH60013.638178.002017-12-29
42016-01-14NFLX75108.718153.252017-12-29
52013-08-14WMT12568.308537.502017-12-29
62013-12-13FB15053.327998.002017-12-29
72015-01-05TWTR22536.388185.502017-12-29
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OpenHighLowCloseAdj CloseVolume
Date
2013-01-021426.1899411462.4300541426.1899411462.4200441462.420044-92367296
2013-01-031462.4200441465.4699711455.5300291459.3699951459.369995-465237296
2013-01-041459.3699951467.9399411458.9899901466.4699711466.469971-870677296
2013-01-071466.4699711466.4699711456.6199951461.8900151461.890015-989997296
2013-01-081461.8900151461.8900151451.6400151457.1500241457.150024-693367296
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OpenHighLowCloseAdj CloseVolume
Date
2018-02-232715.8000492747.7600102713.7399902747.3000492747.300049-1105777296
2018-02-262757.3701172780.6398932753.7800292779.6000982779.600098-870317296
2018-02-272780.4499512789.1499022744.2199712744.2800292744.280029-549887296
2018-02-282753.7800292761.5200202713.5400392713.8300782713.830078-64307296
2018-03-012715.2199712730.8898932659.6499022677.6699222677.669922209002704
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DateAdj Close
12952018-02-232747.300049
12962018-02-262779.600098
12972018-02-272744.280029
12982018-02-282713.830078
12992018-03-012677.669922
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DateAdj Close
12582017-12-292673.610107
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
OpenHighLowCloseAdj CloseVolume
TickerDate
AAPL2013-01-0279.11714279.28571377.37571778.43285456.532822140129500
2013-01-0378.26857078.52428477.28571377.44285655.81924888241300
2013-01-0476.70999976.94714475.11856875.28571354.264439148583400
2013-01-0774.57142675.61428873.59999874.84285753.945232121039100
2013-01-0875.60142575.98428374.46428775.04428954.090427114676800
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj Close
0AAPL2013-01-0256.532822
1AAPL2013-01-0355.819248
2AAPL2013-01-0454.264439
3AAPL2013-01-0753.945232
4AAPL2013-01-0854.090427
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj Close
1258AAPL2017-12-29168.542831
2558JNJ2017-12-29138.831009
3858MCD2017-12-29171.037949
4431MTCH2017-12-2931.309999
5731NFLX2017-12-29191.960007
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj Close
1299AAPL2018-03-01175.000000
2599JNJ2018-03-01127.279999
3899MCD2018-03-01155.699997
4472MTCH2018-03-0139.320000
5772NFLX2018-03-01290.390015
7072WMT2018-03-0189.080002
8372FB2018-03-01175.940002
9457TWTR2018-03-0132.240002
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DateAdj Close
Ticker
AAPL2018-03-01175.000000
JNJ2018-03-01127.279999
MCD2018-03-01155.699997
MTCH2018-03-0139.320000
NFLX2018-03-01290.390015
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Acquisition DateQuantityUnit CostCost BasisStart of Year
Ticker
AAPL2013-02-0712565.408175.002017-12-29
JNJ2014-02-2710081.908190.002017-12-29
MCD2013-06-0410084.998499.002017-12-29
MTCH2015-12-1460013.638178.002017-12-29
NFLX2016-01-1475108.718153.252017-12-29
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Acquisition DateQuantityUnit CostCost BasisStart of YearDateAdj Close
Ticker
AAPL2013-02-0712565.408175.002017-12-292018-03-01175.000000
JNJ2014-02-2710081.908190.002017-12-292018-03-01127.279999
MCD2013-06-0410084.998499.002017-12-292018-03-01155.699997
MTCH2015-12-1460013.638178.002017-12-292018-03-0139.320000
NFLX2016-01-1475108.718153.252017-12-292018-03-01290.390015
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Acquisition DateQuantityUnit CostCost BasisStart of YearDateAdj Closeticker return
Ticker
AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.675841
JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.554090
MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.831980
MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.884813
NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.671236
WMT2013-08-1412568.308537.502017-12-292018-03-0189.0800020.304246
FB2013-12-1315053.327998.002017-12-292018-03-01175.9400022.299700
TWTR2015-01-0522536.388185.502017-12-292018-03-0132.240002-0.113799
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearDate_xAdj Close_xticker returnDate_yAdj Close_y
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758412013-02-071509.390015
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540902014-02-271854.290039
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319802013-06-041631.380005
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132015-12-142021.939941
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712362016-01-141921.839966
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.390015
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.290039
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.380005
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.939941
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.839966
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial CloseEquiv SP Shares
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.3900155.416095
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.2900394.416785
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.3800055.209700
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.9399414.044631
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.8399664.242419
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial CloseEquiv SP SharesDateAdj Close
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.3900155.4160952018-03-012677.669922
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.2900394.4167852018-03-012677.669922
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.3800055.2097002018-03-012677.669922
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.9399414.0446312018-03-012677.669922
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.8399664.2424192018-03-012677.669922
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial CloseEquiv SP SharesSP 500 Latest Close
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.3900155.4160952677.669922
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.2900394.4167852677.669922
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.3800055.2097002677.669922
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.9399414.0446312677.669922
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.8399664.2424192677.669922
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial CloseEquiv SP SharesSP 500 Latest CloseSP ReturnAbs. Return CompareTicker Share ValueSP 500 ValueAbs Value CompareStock Gain / (Loss)SP 500 Gain / (Loss)
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.3900155.4160952677.6699220.7740080.90183321875.00000014502.5151857372.48481513700.0000006327.515185
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.2900394.4167852677.6699220.4440410.11005012727.99990011826.691726901.3081744537.9999003636.691726
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.3800055.2097002677.6699220.6413530.19062815569.99970013949.8563161620.1433847070.9997005450.856316
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.9399414.0446312677.6699220.3243071.56050623592.00000010830.18549612761.81450415414.0000002652.185496
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.8399664.2424192677.6699220.3932851.27795121779.25112511359.79721410419.45391113626.0011253206.547214
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close...SP 500 Latest CloseSP ReturnAbs. Return CompareTicker Share ValueSP 500 ValueAbs Value CompareStock Gain / (Loss)SP 500 Gain / (Loss)DateAdj Close
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.390015...2677.6699220.7740080.90183321875.00000014502.5151857372.48481513700.0000006327.5151852017-12-29168.542831
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.290039...2677.6699220.4440410.11005012727.99990011826.691726901.3081744537.9999003636.6917262017-12-29138.831009
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.380005...2677.6699220.6413530.19062815569.99970013949.8563161620.1433847070.9997005450.8563162017-12-29171.037949
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.939941...2677.6699220.3243071.56050623592.00000010830.18549612761.81450415414.0000002652.1854962017-12-2931.309999
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.839966...2677.6699220.3932851.27795121779.25112511359.79721410419.45391113626.0011253206.5472142017-12-29191.960007
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial CloseEquiv SP SharesSP 500 Latest CloseSP ReturnAbs. Return CompareTicker Share ValueSP 500 ValueAbs Value CompareStock Gain / (Loss)SP 500 Gain / (Loss)Ticker Start Year Close
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.3900155.4160952677.6699220.7740080.90183321875.00000014502.5151857372.48481513700.0000006327.515185168.542831
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.2900394.4167852677.6699220.4440410.11005012727.99990011826.691726901.3081744537.9999003636.691726138.831009
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.3800055.2097002677.6699220.6413530.19062815569.99970013949.8563161620.1433847070.9997005450.856316171.037949
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.9399414.0446312677.6699220.3243071.56050623592.00000010830.18549612761.81450415414.0000002652.18549631.309999
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.8399664.2424192677.6699220.3932851.27795121779.25112511359.79721410419.45391113626.0011253206.547214191.960007
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close...SP ReturnAbs. Return CompareTicker Share ValueSP 500 ValueAbs Value CompareStock Gain / (Loss)SP 500 Gain / (Loss)Ticker Start Year CloseDateAdj Close
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.390015...0.7740080.90183321875.00000014502.5151857372.48481513700.0000006327.515185168.5428312017-12-292673.610107
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.290039...0.4440410.11005012727.99990011826.691726901.3081744537.9999003636.691726138.8310092017-12-292673.610107
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.380005...0.6413530.19062815569.99970013949.8563161620.1433847070.9997005450.856316171.0379492017-12-292673.610107
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.939941...0.3243071.56050623592.00000010830.18549612761.81450415414.0000002652.18549631.3099992017-12-292673.610107
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.839966...0.3932851.27795121779.25112511359.79721410419.45391113626.0011253206.547214191.9600072017-12-292673.610107
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close...Abs. Return CompareTicker Share ValueSP 500 ValueAbs Value CompareStock Gain / (Loss)SP 500 Gain / (Loss)Ticker Start Year CloseSP Start Year CloseShare YTDSP 500 YTD
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.390015...0.90183321875.00000014502.5151857372.48481513700.0000006327.515185168.5428312673.6101070.0383120.001518
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.290039...0.11005012727.99990011826.691726901.3081744537.9999003636.691726138.8310092673.610107-0.0832020.001518
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.380005...0.19062815569.99970013949.8563161620.1433847070.9997005450.856316171.0379492673.610107-0.0896760.001518
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.939941...1.56050623592.00000010830.18549612761.81450415414.0000002652.18549631.3099992673.6101070.2558290.001518
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.839966...1.27795121779.25112511359.79721410419.45391113626.0011253206.547214191.9600072673.6101070.5127630.001518
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close...Abs. Return CompareTicker Share ValueSP 500 ValueAbs Value CompareStock Gain / (Loss)SP 500 Gain / (Loss)Ticker Start Year CloseSP Start Year CloseShare YTDSP 500 YTD
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.390015...0.90183321875.00000014502.5151857372.48481513700.0000006327.515185168.5428312673.6101070.0383120.001518
6FB2013-12-1315053.327998.002017-12-292018-03-01175.9400022.2997001775.319946...1.79142526391.00030012063.17998314327.82031718393.0003004065.179983176.4600072673.610107-0.0029470.001518
1JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.290039...0.11005012727.99990011826.691726901.3081744537.9999003636.691726138.8310092673.610107-0.0832020.001518
2MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.380005...0.19062815569.99970013949.8563161620.1433847070.9997005450.856316171.0379492673.610107-0.0896760.001518
3MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.939941...1.56050623592.00000010830.18549612761.81450415414.0000002652.18549631.3099992673.6101070.2558290.001518
4NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.839966...1.27795121779.25112511359.79721410419.45391113626.0011253206.547214191.9600072673.6101070.5127630.001518
7TWTR2015-01-0522536.388185.502017-12-292018-03-0132.240002-0.1137992020.579956...-0.4389977254.00045010847.413923-3593.413473-931.4995502661.91392324.0100002673.6101070.3427740.001518
5WMT2013-08-1412568.308537.502017-12-292018-03-0189.0800020.3042461685.390015...-0.28450811135.00025013563.986232-2428.9859822597.5002505026.48623298.7500002673.610107-0.0979240.001518
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close...Stock Gain / (Loss)SP 500 Gain / (Loss)Ticker Start Year CloseSP Start Year CloseShare YTDSP 500 YTDCum InvstCum Ticker ReturnsCum SP ReturnsCum Ticker ROI Mult
0AAPL2013-02-0712565.408175.02017-12-292018-03-01175.0000001.6758411509.390015...13700.00006327.515185168.5428312673.6101070.0383120.0015188175.021875.000014502.5151852.675841
6FB2013-12-1315053.327998.02017-12-292018-03-01175.9400022.2997001775.319946...18393.00034065.179983176.4600072673.610107-0.0029470.00151816173.048266.000326565.6951682.984357
1JNJ2014-02-2710081.908190.02017-12-292018-03-01127.2799990.5540901854.290039...4537.99993636.691726138.8310092673.610107-0.0832020.00151824363.060994.000238392.3868942.503550
2MCD2013-06-0410084.998499.02017-12-292018-03-01155.6999970.8319801631.380005...7070.99975450.856316171.0379492673.610107-0.0896760.00151832862.076563.999952342.2432102.329864
3MTCH2015-12-1460013.638178.02017-12-292018-03-0139.3200001.8848132021.939941...15414.00002652.18549631.3099992673.6101070.2558290.00151841040.0100155.999963172.4287062.440448
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj Close
0AAPL2013-01-0256.532822
1AAPL2013-01-0355.819248
2AAPL2013-01-0454.264439
3AAPL2013-01-0753.945232
4AAPL2013-01-0854.090427
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of Year
0AAPL2013-02-0712565.408175.002017-12-29
1JNJ2014-02-2710081.908190.002017-12-29
2MCD2013-06-0410084.998499.002017-12-29
3MTCH2015-12-1460013.638178.002017-12-29
4NFLX2016-01-1475108.718153.252017-12-29
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj CloseindexAcquisition DateQuantityUnit CostCost BasisStart of Year
0AAPL2013-01-0256.53282202013-02-0712565.48175.02017-12-29
1AAPL2013-01-0355.81924802013-02-0712565.48175.02017-12-29
2AAPL2013-01-0454.26443902013-02-0712565.48175.02017-12-29
3AAPL2013-01-0753.94523202013-02-0712565.48175.02017-12-29
4AAPL2013-01-0854.09042702013-02-0712565.48175.02017-12-29
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj CloseindexAcquisition DateDate Delta
0AAPL2013-01-0256.53282202013-02-07-3110400000000000
1AAPL2013-01-0355.81924802013-02-07-3024000000000000
2AAPL2013-01-0454.26443902013-02-07-2937600000000000
3AAPL2013-01-0753.94523202013-02-07-2678400000000000
4AAPL2013-01-0854.09042702013-02-07-2592000000000000
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerDateAdj CloseindexAcquisition DateDate Delta
25AAPL2013-02-0750.25008002013-02-070
26AAPL2013-02-0850.97556702013-02-0786400000000000
27AAPL2013-02-1151.50681302013-02-07345600000000000
28AAPL2013-02-1250.21573602013-02-07432000000000000
29AAPL2013-02-1350.12021602013-02-07518400000000000
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateAdj Close
0AAPL2013-02-07178.970001
1FB2013-12-13193.089996
2JNJ2014-02-27147.197433
3MCD2013-06-04177.238724
4MTCH2015-12-1442.099998
5NFLX2016-01-14294.160004
6TWTR2015-01-0552.869999
7WMT2013-08-14109.550003
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateAdj CloseDate
0AAPL2013-02-07178.9700012018-02-26
1FB2013-12-13193.0899962018-02-01
2JNJ2014-02-27147.1974332018-01-22
3MCD2013-06-04177.2387242018-01-26
4MTCH2015-12-1442.0999982018-02-20
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TickerAcquisition DateQuantityUnit CostCost BasisStart of YearLatest DateTicker Adj Closeticker returnSP 500 Initial Close...SP Start Year CloseShare YTDSP 500 YTDCum InvstCum Ticker ReturnsCum SP ReturnsCum Ticker ROI MultClosing High Adj CloseClosing High Adj Close DatePct off High
0AAPL2013-02-0712565.408175.002017-12-292018-03-01175.0000001.6758411509.390015...2673.6101070.0383120.0015188175.0021875.00000014502.5151852.675841178.9700012018-02-26-0.022182
1FB2013-12-1315053.327998.002017-12-292018-03-01175.9400022.2997001775.319946...2673.610107-0.0029470.00151816173.0048266.00030026565.6951682.984357193.0899962018-02-01-0.088819
2JNJ2014-02-2710081.908190.002017-12-292018-03-01127.2799990.5540901854.290039...2673.610107-0.0832020.00151824363.0060994.00020038392.3868942.503550147.1974332018-01-22-0.135311
3MCD2013-06-0410084.998499.002017-12-292018-03-01155.6999970.8319801631.380005...2673.610107-0.0896760.00151832862.0076563.99990052342.2432102.329864177.2387242018-01-26-0.121524
4MTCH2015-12-1460013.638178.002017-12-292018-03-0139.3200001.8848132021.939941...2673.6101070.2558290.00151841040.00100155.99990063172.4287062.44044842.0999982018-02-20-0.066033
5NFLX2016-01-1475108.718153.252017-12-292018-03-01290.3900151.6712361921.839966...2673.6101070.5127630.00151849193.25121935.25102574532.2259202.478699294.1600042018-02-26-0.012816
6TWTR2015-01-0522536.388185.502017-12-292018-03-0132.240002-0.1137992020.579956...2673.6101070.3427740.00151857378.75129189.25147585379.6398422.25151752.8699992015-04-07-0.390202
7WMT2013-08-1412568.308537.502017-12-292018-03-0189.0800020.3042461685.390015...2673.610107-0.0979240.00151865916.25140324.25172598943.6260742.128826109.5500032018-01-29-0.186855
\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 }