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