# 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.7.0
# Import the Sample worksheet with acquisition dates and initial cost basis:
portfolio_df = pd.read_excel('Sample stocks acquisition dates_costs.xlsx', sheet_name='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 |
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: 464.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(2010, 1, 1)
end_sp = datetime.datetime(2018, 7, 13)
# 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(2010, 1, 1)
stocks_end = datetime.datetime(2018, 7, 13)
# 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 | ||||||
2010-01-04 | 1116.560059 | 1133.869995 | 1116.560059 | 1132.989990 | 1132.989990 | -303567296 |
2010-01-05 | 1132.660034 | 1136.630005 | 1129.660034 | 1136.520020 | 1136.520020 | -1803947296 |
2010-01-06 | 1135.709961 | 1139.189941 | 1133.949951 | 1137.140015 | 1137.140015 | 677692704 |
2010-01-07 | 1136.270020 | 1142.459961 | 1131.319946 | 1141.689941 | 1141.689941 | 975712704 |
2010-01-08 | 1140.520020 | 1145.390015 | 1136.219971 | 1144.979980 | 1144.979980 | 94622704 |
sp500.tail()
Open | High | Low | Close | Adj Close | Volume | |
---|---|---|---|---|---|---|
Date | ||||||
2018-07-09 | 2775.620117 | 2784.649902 | 2770.729980 | 2784.169922 | 2784.169922 | -1244927296 |
2018-07-10 | 2788.560059 | 2795.580078 | 2786.239990 | 2793.840088 | 2793.840088 | -1231117296 |
2018-07-11 | 2779.820068 | 2785.909912 | 2770.770020 | 2774.020020 | 2774.020020 | -1330227296 |
2018-07-12 | 2783.139893 | 2799.219971 | 2781.530029 | 2798.290039 | 2798.290039 | -1473277296 |
2018-07-13 | 2796.929932 | 2804.530029 | 2791.689941 | 2801.310059 | 2801.310059 | -1680967296 |
# 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 | |
---|---|---|
2142 | 2018-07-09 | 2784.169922 |
2143 | 2018-07-10 | 2793.840088 |
2144 | 2018-07-11 | 2774.020020 |
2145 | 2018-07-12 | 2798.290039 |
2146 | 2018-07-13 | 2801.310059 |
# 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 | |
---|---|---|
2012 | 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(['AAPL', 'JNJ', 'MCD', 'MTCH', 'NFLX', 'WMT', 'FB', '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 [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded
all_data.tail()
Open | High | Low | Close | Adj Close | Volume | ||
---|---|---|---|---|---|---|---|
Ticker | Date | ||||||
TWTR | 2018-07-09 | 46.740002 | 46.900002 | 42.080002 | 44.139999 | 44.139999 | 107582400 |
2018-07-10 | 44.200001 | 45.259998 | 43.630001 | 43.750000 | 43.750000 | 38467400 | |
2018-07-11 | 42.630001 | 44.099998 | 42.220001 | 43.869999 | 43.869999 | 35100100 | |
2018-07-12 | 44.799999 | 45.340000 | 44.360001 | 45.259998 | 45.259998 | 27078500 | |
2018-07-13 | 45.279999 | 45.320000 | 43.930000 | 44.490002 | 44.490002 | 16426700 |
# 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 | 2010-01-04 | 20.616993 |
1 | AAPL | 2010-01-05 | 20.652637 |
2 | AAPL | 2010-01-06 | 20.324135 |
3 | AAPL | 2010-01-07 | 20.286560 |
4 | AAPL | 2010-01-08 | 20.421427 |
adj_close['Ticker'].unique()
array(['AAPL', 'JNJ', 'MCD', 'MTCH', 'NFLX', 'WMT', 'FB', 'TWTR'], dtype=object)
# 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 | |
---|---|---|---|
2012 | AAPL | 2017-12-29 | 167.895416 |
4159 | JNJ | 2017-12-29 | 137.808945 |
6306 | MCD | 2017-12-29 | 169.958328 |
6972 | MTCH | 2017-12-29 | 31.309999 |
9119 | 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.head()
Ticker | Date | Adj Close | |
---|---|---|---|
2146 | AAPL | 2018-07-13 | 191.330002 |
4293 | JNJ | 2018-07-13 | 125.930000 |
6440 | MCD | 2018-07-13 | 158.509995 |
7106 | MTCH | 2018-07-13 | 38.889999 |
9253 | NFLX | 2018-07-13 | 395.799988 |
# Merge the portfolio dataframe with the adj close dataframe; they are being joined by the indexes.
merged_portfolio = pd.merge(portfolio_df, adj_close_latest, on='Ticker')
merged_portfolio.head()
Acquisition Date | Ticker | Quantity | Unit Cost | Cost Basis | Start of Year | Date | Adj Close | |
---|---|---|---|---|---|---|---|---|
0 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 |
# 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.head()
Acquisition Date | Ticker | Quantity | Unit Cost | Cost Basis | Start of Year | Date | Adj Close | ticker return | |
---|---|---|---|---|---|---|---|---|---|
0 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 |
# Above we reset the index to the newly merged dataframe. This is because we have a flat dataframe for the sp500 returns
# and we merge the the new dataframe with the sp500 adjusted closes since the sp start on 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()
Acquisition Date | Ticker | Quantity | Unit Cost | Cost Basis | Start of Year | Date_x | Adj Close_x | ticker return | Date_y | Adj Close_y | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 2013-02-07 | 1509.390015 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 2014-02-27 | 1854.290039 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 2013-06-04 | 1631.380005 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2015-12-14 | 2021.939941 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 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()
Acquisition Date | Ticker | Quantity | Unit Cost | Cost Basis | Start of Year | Latest Date | Ticker Adj Close | ticker return | SP 500 Initial Close | |
---|---|---|---|---|---|---|---|---|---|---|
0 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 |
# This new column is intended to figure out 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()
Acquisition Date | Ticker | Quantity | Unit Cost | Cost Basis | Start of Year | Latest Date | Ticker Adj Close | ticker return | SP 500 Initial Close | Equiv SP Shares | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | 5.416095 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | 4.416785 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | 5.209700 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | 4.044631 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | 5.416095 | 2018-07-13 | 2801.310059 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | 4.416785 | 2018-07-13 | 2801.310059 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | 5.209700 | 2018-07-13 | 2801.310059 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | 4.044631 | 2018-07-13 | 2801.310059 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | 4.242419 | 2018-07-13 | 2801.310059 |
# 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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | 5.416095 | 2801.310059 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | 4.416785 | 2801.310059 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | 5.209700 | 2801.310059 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | 4.044631 | 2801.310059 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | 4.242419 | 2801.310059 |
# 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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | 5.416095 | 2801.310059 | 0.855922 | 1.069613 | 23916.25025 | 15172.161936 | 8744.088314 | 15741.25025 | 6997.161936 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | 4.416785 | 2801.310059 | 0.510718 | 0.026888 | 12593.00000 | 12372.783599 | 220.216401 | 4403.00000 | 4182.783599 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | 5.209700 | 2801.310059 | 0.717141 | 0.147902 | 15850.99950 | 14593.984307 | 1257.015193 | 7351.99950 | 6094.984307 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | 4.044631 | 2801.310059 | 0.385457 | 1.467808 | 23333.99940 | 11330.264168 | 12003.735232 | 15155.99940 | 3152.264168 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | 4.242419 | 2801.310059 | 0.457619 | 2.183260 | 29684.99910 | 11884.330456 | 17800.668644 | 21531.74910 | 3731.080456 |
# Merge the overall dataframe with the adj close start of year dataframe for YTD tracking.
merged_portfolio_sp_latest_YTD = pd.merge(merged_portfolio_sp_latest, adj_close_start, on='Ticker')
merged_portfolio_sp_latest_YTD.head()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 2801.310059 | 0.855922 | 1.069613 | 23916.25025 | 15172.161936 | 8744.088314 | 15741.25025 | 6997.161936 | 2017-12-29 | 167.895416 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 2801.310059 | 0.510718 | 0.026888 | 12593.00000 | 12372.783599 | 220.216401 | 4403.00000 | 4182.783599 | 2017-12-29 | 137.808945 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 2801.310059 | 0.717141 | 0.147902 | 15850.99950 | 14593.984307 | 1257.015193 | 7351.99950 | 6094.984307 | 2017-12-29 | 169.958328 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 2801.310059 | 0.385457 | 1.467808 | 23333.99940 | 11330.264168 | 12003.735232 | 15155.99940 | 3152.264168 | 2017-12-29 | 31.309999 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | ... | 2801.310059 | 0.457619 | 2.183260 | 29684.99910 | 11884.330456 | 17800.668644 | 21531.74910 | 3731.080456 | 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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | 5.416095 | 2801.310059 | 0.855922 | 1.069613 | 23916.25025 | 15172.161936 | 8744.088314 | 15741.25025 | 6997.161936 | 167.895416 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | 4.416785 | 2801.310059 | 0.510718 | 0.026888 | 12593.00000 | 12372.783599 | 220.216401 | 4403.00000 | 4182.783599 | 137.808945 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | 5.209700 | 2801.310059 | 0.717141 | 0.147902 | 15850.99950 | 14593.984307 | 1257.015193 | 7351.99950 | 6094.984307 | 169.958328 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | 4.044631 | 2801.310059 | 0.385457 | 1.467808 | 23333.99940 | 11330.264168 | 12003.735232 | 15155.99940 | 3152.264168 | 31.309999 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | 4.242419 | 2801.310059 | 0.457619 | 2.183260 | 29684.99910 | 11884.330456 | 17800.668644 | 21531.74910 | 3731.080456 | 191.960007 |
# Join the SP 500 start of year with current dataframe.
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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 0.855922 | 1.069613 | 23916.25025 | 15172.161936 | 8744.088314 | 15741.25025 | 6997.161936 | 167.895416 | 2017-12-29 | 2673.610107 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 0.510718 | 0.026888 | 12593.00000 | 12372.783599 | 220.216401 | 4403.00000 | 4182.783599 | 137.808945 | 2017-12-29 | 2673.610107 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 0.717141 | 0.147902 | 15850.99950 | 14593.984307 | 1257.015193 | 7351.99950 | 6094.984307 | 169.958328 | 2017-12-29 | 2673.610107 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 0.385457 | 1.467808 | 23333.99940 | 11330.264168 | 12003.735232 | 15155.99940 | 3152.264168 | 31.309999 | 2017-12-29 | 2673.610107 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | ... | 0.457619 | 2.183260 | 29684.99910 | 11884.330456 | 17800.668644 | 21531.74910 | 3731.080456 | 191.960007 | 2017-12-29 | 2673.610107 |
5 rows × 22 columns
# Deleting another unneeded Data 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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.00 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 1.069613 | 23916.25025 | 15172.161936 | 8744.088314 | 15741.25025 | 6997.161936 | 167.895416 | 2673.610107 | 0.139578 | 0.047763 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.00 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 0.026888 | 12593.00000 | 12372.783599 | 220.216401 | 4403.00000 | 4182.783599 | 137.808945 | 2673.610107 | -0.086199 | 0.047763 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.00 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 0.147902 | 15850.99950 | 14593.984307 | 1257.015193 | 7351.99950 | 6094.984307 | 169.958328 | 2673.610107 | -0.067360 | 0.047763 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.00 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 1.467808 | 23333.99940 | 11330.264168 | 12003.735232 | 15155.99940 | 3152.264168 | 31.309999 | 2673.610107 | 0.242095 | 0.047763 |
4 | 2016-01-14 | NFLX | 75 | 108.71 | 8153.25 | 2017-12-29 | 2018-07-13 | 395.799988 | 2.640879 | 1921.839966 | ... | 2.183260 | 29684.99910 | 11884.330456 | 17800.668644 | 21531.74910 | 3731.080456 | 191.960007 | 2673.610107 | 1.061888 | 0.047763 |
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.head()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.0 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 1.069613 | 23916.25025 | 15172.161936 | 8744.088314 | 15741.25025 | 6997.161936 | 167.895416 | 2673.610107 | 0.139578 | 0.047763 |
6 | 2013-12-13 | FB | 150 | 53.32 | 7998.0 | 2017-12-29 | 2018-07-13 | 207.320007 | 2.888222 | 1775.319946 | ... | 2.310304 | 31098.00105 | 12620.191590 | 18477.809460 | 23100.00105 | 4622.191590 | 176.460007 | 2673.610107 | 0.174884 | 0.047763 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.0 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 0.026888 | 12593.00000 | 12372.783599 | 220.216401 | 4403.00000 | 4182.783599 | 137.808945 | 2673.610107 | -0.086199 | 0.047763 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.0 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 0.147902 | 15850.99950 | 14593.984307 | 1257.015193 | 7351.99950 | 6094.984307 | 169.958328 | 2673.610107 | -0.067360 | 0.047763 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.0 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 1.467808 | 23333.99940 | 11330.264168 | 12003.735232 | 15155.99940 | 3152.264168 | 31.309999 | 2673.610107 | 0.242095 | 0.047763 |
5 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()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.0 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 15741.25025 | 6997.161936 | 167.895416 | 2673.610107 | 0.139578 | 0.047763 | 8175.0 | 23916.25025 | 15172.161936 | 2.925535 |
6 | 2013-12-13 | FB | 150 | 53.32 | 7998.0 | 2017-12-29 | 2018-07-13 | 207.320007 | 2.888222 | 1775.319946 | ... | 23100.00105 | 4622.191590 | 176.460007 | 2673.610107 | 0.174884 | 0.047763 | 16173.0 | 55014.25130 | 27792.353526 | 3.401611 |
1 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.0 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 4403.00000 | 4182.783599 | 137.808945 | 2673.610107 | -0.086199 | 0.047763 | 24363.0 | 67607.25130 | 40165.137125 | 2.774997 |
2 | 2013-06-04 | MCD | 100 | 84.99 | 8499.0 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 7351.99950 | 6094.984307 | 169.958328 | 2673.610107 | -0.067360 | 0.047763 | 32862.0 | 83458.25080 | 54759.121432 | 2.539658 |
3 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.0 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 15155.99940 | 3152.264168 | 31.309999 | 2673.610107 | 0.242095 | 0.047763 | 41040.0 | 106792.25020 | 66089.385600 | 2.602150 |
5 rows × 27 columns
# Referencing the adj_close dataframe from above
adj_close.head()
Ticker | Date | Adj Close | |
---|---|---|---|
0 | AAPL | 2010-01-04 | 20.616993 |
1 | AAPL | 2010-01-05 | 20.652637 |
2 | AAPL | 2010-01-06 | 20.324135 |
3 | AAPL | 2010-01-07 | 20.286560 |
4 | AAPL | 2010-01-08 | 20.421427 |
portfolio_df.head()
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 |
# 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.
adj_close_acq_date = pd.merge(adj_close, portfolio_df, on='Ticker')
adj_close_acq_date.head()
Ticker | Date | Adj Close | Acquisition Date | Quantity | Unit Cost | Cost Basis | Start of Year | |
---|---|---|---|---|---|---|---|---|
0 | AAPL | 2010-01-04 | 20.616993 | 2013-02-07 | 125 | 65.4 | 8175.0 | 2017-12-29 |
1 | AAPL | 2010-01-05 | 20.652637 | 2013-02-07 | 125 | 65.4 | 8175.0 | 2017-12-29 |
2 | AAPL | 2010-01-06 | 20.324135 | 2013-02-07 | 125 | 65.4 | 8175.0 | 2017-12-29 |
3 | AAPL | 2010-01-07 | 20.286560 | 2013-02-07 | 125 | 65.4 | 8175.0 | 2017-12-29 |
4 | AAPL | 2010-01-08 | 20.421427 | 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']
adj_close_acq_date.sort_values(by=['Ticker', 'Acquisition Date', 'Date'], ascending=[True, True, True], inplace=True)
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 | Acquisition Date | Date Delta | |
---|---|---|---|---|---|
0 | AAPL | 2010-01-04 | 20.616993 | 2013-02-07 | -97632000000000000 |
1 | AAPL | 2010-01-05 | 20.652637 | 2013-02-07 | -97545600000000000 |
2 | AAPL | 2010-01-06 | 20.324135 | 2013-02-07 | -97459200000000000 |
3 | AAPL | 2010-01-07 | 20.286560 | 2013-02-07 | -97372800000000000 |
4 | AAPL | 2010-01-08 | 20.421427 | 2013-02-07 | -97286400000000000 |
# 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 | Acquisition Date | Date Delta | |
---|---|---|---|---|---|
779 | AAPL | 2013-02-07 | 50.057049 | 2013-02-07 | 0 |
780 | AAPL | 2013-02-08 | 50.779766 | 2013-02-07 | 86400000000000 |
781 | AAPL | 2013-02-11 | 51.308968 | 2013-02-07 | 345600000000000 |
782 | AAPL | 2013-02-12 | 50.022839 | 2013-02-07 | 432000000000000 |
783 | AAPL | 2013-02-13 | 49.927689 | 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[0:10]
Ticker | Acquisition Date | Adj Close | |
---|---|---|---|
0 | AAPL | 2013-02-07 | 193.979996 |
1 | FB | 2013-12-13 | 207.320007 |
2 | JNJ | 2014-02-27 | 146.113785 |
3 | MCD | 2013-06-04 | 176.119965 |
4 | MTCH | 2015-12-14 | 48.090000 |
5 | NFLX | 2016-01-14 | 418.970001 |
6 | TWTR | 2015-01-05 | 52.869999 |
7 | WMT | 2013-08-14 | 108.218971 |
# 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 | 193.979996 | 2018-06-06 |
1 | FB | 2013-12-13 | 207.320007 | 2018-07-13 |
2 | JNJ | 2014-02-27 | 146.113785 | 2018-01-22 |
3 | MCD | 2013-06-04 | 176.119965 | 2018-01-26 |
4 | MTCH | 2015-12-14 | 48.090000 | 2018-04-17 |
# Duplicates could be created where the stock had the same high on multiple dates.
# Sorted by latest date and then dropped duplicates, which drops the earlier high from consideration.
adj_close_pivot_merged.sort_values(by=['Ticker', 'Acquisition Date', 'Date'], ascending=[True, True, False], inplace=True)
adj_close_pivot_merged.drop_duplicates(['Ticker', 'Acquisition Date', 'Adj Close'], inplace=True)
adj_close_pivot_merged.head()
Ticker | Acquisition Date | Adj Close | Date | |
---|---|---|---|---|
0 | AAPL | 2013-02-07 | 193.979996 | 2018-06-06 |
1 | FB | 2013-12-13 | 207.320007 | 2018-07-13 |
2 | JNJ | 2014-02-27 | 146.113785 | 2018-01-22 |
3 | MCD | 2013-06-04 | 176.119965 | 2018-01-26 |
4 | MTCH | 2015-12-14 | 48.090000 | 2018-04-17 |
# 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.head()
Acquisition Date | Ticker | 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 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.0 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 2673.610107 | 0.139578 | 0.047763 | 8175.0 | 23916.25025 | 15172.161936 | 2.925535 | 193.979996 | 2018-06-06 | -0.013661 |
1 | 2013-12-13 | FB | 150 | 53.32 | 7998.0 | 2017-12-29 | 2018-07-13 | 207.320007 | 2.888222 | 1775.319946 | ... | 2673.610107 | 0.174884 | 0.047763 | 16173.0 | 55014.25130 | 27792.353526 | 3.401611 | 207.320007 | 2018-07-13 | 0.000000 |
2 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.0 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 2673.610107 | -0.086199 | 0.047763 | 24363.0 | 67607.25130 | 40165.137125 | 2.774997 | 146.113785 | 2018-01-22 | -0.138137 |
3 | 2013-06-04 | MCD | 100 | 84.99 | 8499.0 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 2673.610107 | -0.067360 | 0.047763 | 32862.0 | 83458.25080 | 54759.121432 | 2.539658 | 176.119965 | 2018-01-26 | -0.099988 |
4 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.0 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 2673.610107 | 0.242095 | 0.047763 | 41040.0 | 106792.25020 | 66089.385600 | 2.602150 | 48.090000 | 2018-04-17 | -0.191308 |
5 rows × 30 columns
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()
Acquisition Date | Ticker | Quantity | Unit Cost | Cost Basis | Start of Year | Latest Date | Ticker Adj Close | ticker return | SP 500 Initial Close | ... | 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 | Counts | Ticker # | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2013-02-07 | AAPL | 125 | 65.40 | 8175.0 | 2017-12-29 | 2018-07-13 | 191.330002 | 1.925535 | 1509.390015 | ... | 0.047763 | 8175.0 | 23916.25025 | 15172.161936 | 2.925535 | 193.979996 | 2018-06-06 | -0.013661 | 0 | AAPL 0 |
1 | 2013-12-13 | FB | 150 | 53.32 | 7998.0 | 2017-12-29 | 2018-07-13 | 207.320007 | 2.888222 | 1775.319946 | ... | 0.047763 | 16173.0 | 55014.25130 | 27792.353526 | 3.401611 | 207.320007 | 2018-07-13 | 0.000000 | 1 | FB 1 |
2 | 2014-02-27 | JNJ | 100 | 81.90 | 8190.0 | 2017-12-29 | 2018-07-13 | 125.930000 | 0.537607 | 1854.290039 | ... | 0.047763 | 24363.0 | 67607.25130 | 40165.137125 | 2.774997 | 146.113785 | 2018-01-22 | -0.138137 | 2 | JNJ 2 |
3 | 2013-06-04 | MCD | 100 | 84.99 | 8499.0 | 2017-12-29 | 2018-07-13 | 158.509995 | 0.865043 | 1631.380005 | ... | 0.047763 | 32862.0 | 83458.25080 | 54759.121432 | 2.539658 | 176.119965 | 2018-01-26 | -0.099988 | 3 | MCD 3 |
4 | 2015-12-14 | MTCH | 600 | 13.63 | 8178.0 | 2017-12-29 | 2018-07-13 | 38.889999 | 1.853265 | 2021.939941 | ... | 0.047763 | 41040.0 | 106792.25020 | 66089.385600 | 2.602150 | 48.090000 | 2018-04-17 | -0.191308 | 4 | MTCH 4 |
5 rows × 32 columns
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 = 'Total Return vs S&P 500, YTD'
, barmode = 'group'
, yaxis=dict(title='Returns', tickformat=".2%")
, xaxis=dict(title='Ticker')
, legend=dict(x=0.8,y=1.2)
)
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')
, legend=dict(x=.8,y=1.2)
)
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]
#, , trace4
layout = go.Layout(title = 'Gain / (Loss) and Total Return vs S&P 500'
, barmode = 'group'
, yaxis=dict(title='Gain / (Loss) ($)')
, yaxis2=dict(title='Ticker Return', overlaying='y', side='right', tickformat=".1%")
, xaxis=dict(title='Ticker')
, legend=dict(x=.75,y=1.2)
)
fig = go.Figure(data=data, layout=layout)
iplot(fig)
trace1 = go.Scatter(
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.Scatter(
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')
trace3 = go.Scatter(
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')
data = [trace1, trace2, trace3]
layout = go.Layout(title = 'Total Investment Comparisons by Ticker'
, barmode = 'group'
, yaxis=dict(title='Returns')
, xaxis=dict(title='Ticker')
, legend=dict(x=1,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)
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 Since Purchased'
, barmode = 'group'
, yaxis=dict(title='% Below Adj Close High Since Purchased', tickformat=".2%")
, xaxis=dict(title='Ticker')
, legend=dict(x=.8,y=1)
)
fig = go.Figure(data=data, layout=layout)
iplot(fig)
# Generate a dynamic list of tickers to pull from Yahoo Finance API based on the imported file with tickers.
chart_tickers = portfolio_df['Ticker'].unique()
chart_tickers = chart_tickers.tolist()
chart_tickers.append('^GSPC')
chart_tickers = np.array(chart_tickers)
chart_tickers
array(['AAPL', 'JNJ', 'MCD', 'MTCH', 'NFLX', 'WMT', 'FB', 'TWTR', '^GSPC'], dtype='<U5')
# The below will pull back stock prices from chart start date until end date specified.
chart_start = datetime.datetime(2017, 1, 4)
chart_end = datetime.datetime(2018, 7, 13)
# Run the same function as above, but for a different date range and including the SP500.
chart_data = get(chart_tickers, chart_start, chart_end)
[*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded [*********************100%***********************] 1 of 1 downloaded
chart_data.tail()
Open | High | Low | Close | Adj Close | Volume | ||
---|---|---|---|---|---|---|---|
Ticker | Date | ||||||
^GSPC | 2018-07-09 | 2775.620117 | 2784.649902 | 2770.729980 | 2784.169922 | 2784.169922 | -1244927296 |
2018-07-10 | 2788.560059 | 2795.580078 | 2786.239990 | 2793.840088 | 2793.840088 | -1231117296 | |
2018-07-11 | 2779.820068 | 2785.909912 | 2770.770020 | 2774.020020 | 2774.020020 | -1330227296 | |
2018-07-12 | 2783.139893 | 2799.219971 | 2781.530029 | 2798.290039 | 2798.290039 | -1473277296 | |
2018-07-13 | 2796.929932 | 2804.530029 | 2791.689941 | 2801.310059 | 2801.310059 | -1680967296 |
chart_data_eval = chart_data[['Close']]
chart_data_eval.reset_index(inplace=True)
chart_data_eval.head()
Ticker | Date | Close | |
---|---|---|---|
0 | AAPL | 2017-01-04 | 116.019997 |
1 | AAPL | 2017-01-05 | 116.610001 |
2 | AAPL | 2017-01-06 | 117.910004 |
3 | AAPL | 2017-01-09 | 118.989998 |
4 | AAPL | 2017-01-10 | 119.110001 |
chart_data_eval_pivot = pd.pivot_table(chart_data_eval, index='Date', columns='Ticker', values = 'Close')
chart_data_eval_pivot.reset_index(inplace=True)
chart_data_eval_pivot.head()
Ticker | Date | AAPL | FB | JNJ | MCD | MTCH | NFLX | TWTR | WMT | ^GSPC |
---|---|---|---|---|---|---|---|---|---|---|
0 | 2017-01-04 | 116.019997 | 118.690002 | 115.650002 | 119.480003 | 17.290001 | 129.410004 | 16.860001 | 69.059998 | 2270.750000 |
1 | 2017-01-05 | 116.610001 | 120.669998 | 116.860001 | 119.699997 | 17.990000 | 131.809998 | 17.090000 | 69.209999 | 2269.000000 |
2 | 2017-01-06 | 117.910004 | 123.410004 | 116.300003 | 120.760002 | 18.030001 | 131.070007 | 17.170000 | 68.260002 | 2276.979980 |
3 | 2017-01-09 | 118.989998 | 124.900002 | 116.279999 | 120.430000 | 18.150000 | 130.949997 | 17.500000 | 68.709999 | 2268.899902 |
4 | 2017-01-10 | 119.110001 | 124.349998 | 116.160004 | 120.250000 | 18.280001 | 129.889999 | 17.370001 | 68.230003 | 2268.899902 |
trace1 = go.Scatter(
x = chart_data_eval_pivot['Date'],
y = chart_data_eval_pivot['^GSPC'],
mode = 'lines',
name = 'SP Prices')
trace2 = go.Scatter(
x = chart_data_eval_pivot['Date'],
y = chart_data_eval_pivot['AAPL'],
mode = 'lines',
name = 'AAPL Returns')
trace3 = go.Scatter(
x = chart_data_eval_pivot['Date'],
y = chart_data_eval_pivot['NFLX'],
mode = 'lines',
name = 'NFLX Returns')
data = [trace1, trace2, trace3]
layout = go.Layout(title = 'Share Price Returns by Ticker'
, barmode = 'group'
, yaxis=dict(title='Returns')
, xaxis=dict(title='Ticker')
, legend=dict(x=1,y=1)
)
fig = go.Figure(data=data, layout=layout)
iplot(fig)
chart_data_eval_pivot_relative = pd.pivot_table(chart_data_eval, index='Date', columns='Ticker', values = 'Close')
chart_data_eval_pivot_relative.tail()
Ticker | AAPL | FB | JNJ | MCD | MTCH | NFLX | TWTR | WMT | ^GSPC |
---|---|---|---|---|---|---|---|---|---|
Date | |||||||||
2018-07-09 | 190.580002 | 204.740005 | 126.050003 | 159.940002 | 39.820000 | 418.970001 | 44.139999 | 85.930000 | 2784.169922 |
2018-07-10 | 190.350006 | 203.539993 | 127.379997 | 160.619995 | 38.400002 | 415.630005 | 43.750000 | 87.209999 | 2793.840088 |
2018-07-11 | 187.880005 | 202.539993 | 126.239998 | 158.619995 | 38.090000 | 418.649994 | 43.869999 | 86.529999 | 2774.020020 |
2018-07-12 | 191.029999 | 206.919998 | 127.760002 | 159.119995 | 39.570000 | 413.500000 | 45.259998 | 86.519997 | 2798.290039 |
2018-07-13 | 191.330002 | 207.320007 | 125.930000 | 158.509995 | 38.889999 | 395.799988 | 44.490002 | 87.699997 | 2801.310059 |
chart_data_eval_pivot_relative_first = chart_data_eval_pivot_relative.iloc[0,:]
chart_data_eval_pivot_relative_first.head()
Ticker AAPL 116.019997 FB 118.690002 JNJ 115.650002 MCD 119.480003 MTCH 17.290001 Name: 2017-01-04 00:00:00, dtype: float64
chart_data_eval_pivot_relative = (chart_data_eval_pivot_relative.divide(chart_data_eval_pivot_relative_first, axis=1))-1
chart_data_eval_pivot_relative.reset_index(inplace=True)
chart_data_eval_pivot_relative.head()
Ticker | Date | AAPL | FB | JNJ | MCD | MTCH | NFLX | TWTR | WMT | ^GSPC |
---|---|---|---|---|---|---|---|---|---|---|
0 | 2017-01-04 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 |
1 | 2017-01-05 | 0.005085 | 0.016682 | 0.010463 | 0.001841 | 0.040486 | 0.018546 | 0.013642 | 0.002172 | -0.000771 |
2 | 2017-01-06 | 0.016290 | 0.039767 | 0.005620 | 0.010713 | 0.042799 | 0.012827 | 0.018387 | -0.011584 | 0.002744 |
3 | 2017-01-09 | 0.025599 | 0.052321 | 0.005447 | 0.007951 | 0.049740 | 0.011900 | 0.037960 | -0.005068 | -0.000815 |
4 | 2017-01-10 | 0.026633 | 0.047687 | 0.004410 | 0.006445 | 0.057259 | 0.003709 | 0.030249 | -0.012018 | -0.000815 |
chart_data_eval_pivot_relative.tail()
Ticker | Date | AAPL | FB | JNJ | MCD | MTCH | NFLX | TWTR | WMT | ^GSPC |
---|---|---|---|---|---|---|---|---|---|---|
379 | 2018-07-09 | 0.642648 | 0.724998 | 0.089927 | 0.338634 | 1.303065 | 2.237540 | 1.618031 | 0.244280 | 0.226101 |
380 | 2018-07-10 | 0.640665 | 0.714887 | 0.101427 | 0.344325 | 1.220937 | 2.211730 | 1.594899 | 0.262815 | 0.230360 |
381 | 2018-07-11 | 0.619376 | 0.706462 | 0.091569 | 0.327586 | 1.203007 | 2.235067 | 1.602016 | 0.252968 | 0.221632 |
382 | 2018-07-12 | 0.646526 | 0.743365 | 0.104712 | 0.331771 | 1.288606 | 2.195271 | 1.684460 | 0.252824 | 0.232320 |
383 | 2018-07-13 | 0.649112 | 0.746735 | 0.088889 | 0.326665 | 1.249277 | 2.058496 | 1.638790 | 0.269910 | 0.233650 |
trace1 = go.Scatter(
x = chart_data_eval_pivot_relative['Date'],
y = chart_data_eval_pivot_relative['^GSPC'],
mode = 'lines',
name = 'SP Return')
trace2 = go.Scatter(
x = chart_data_eval_pivot_relative['Date'],
y = chart_data_eval_pivot_relative['AAPL'],
mode = 'lines',
name = 'AAPL Return')
trace3 = go.Scatter(
x = chart_data_eval_pivot_relative['Date'],
y = chart_data_eval_pivot_relative['NFLX'],
mode = 'lines',
name = 'NFLX Return')
trace4 = go.Scatter(
x = chart_data_eval_pivot_relative['Date'],
y = chart_data_eval_pivot_relative['MTCH'],
mode = 'lines',
name = 'MTCH Return')
data = [trace1, trace2, trace3, trace4]
layout = go.Layout(title = 'Return Comparisons by Ticker'
, barmode = 'group'
, yaxis=dict(title='Relative Returns', tickformat=".1%")
, xaxis=dict(title='Ticker')
, legend=dict(x=1,y=1)
)
fig = go.Figure(data=data, layout=layout)
iplot(fig)
# Generate the base file that will be used for Dash dashboard.
merged_portfolio_sp_latest_YTD_sp_closing_high.to_csv('analyzed_portfolio.csv')
merged_portfolio_sp_latest_YTD_sp_closing_high.head()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = merged_portfolio_sp_latest_YTD_sp_closing_high[['Ticker']]
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.drop_duplicates(['Ticker'], keep='first')
# merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.head()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = merged_portfolio_sp_latest_YTD_sp_closing_high_tickers['Ticker'].unique()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.tolist()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.append('SPY')
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = pd.DataFrame(data=merged_portfolio_sp_latest_YTD_sp_closing_high_tickers, columns=['Ticker'])
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.sort_values(by='Ticker', ascending=True, inplace=True)
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.head()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.to_csv('tickers.csv')
# The below generates the tickers that will be used in the Dash ticker dropdown.
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = merged_portfolio_sp_latest_YTD_sp_closing_high_tickers['Ticker'].unique()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers = merged_portfolio_sp_latest_YTD_sp_closing_high_tickers.tolist()
merged_portfolio_sp_latest_YTD_sp_closing_high_tickers
['AAPL', 'FB', 'JNJ', 'MCD', 'MTCH', 'NFLX', 'SPY', 'TWTR', 'WMT']