<jupyter_start><jupyter_text>A Whale off the Port(folio) In this assignment, you'll get to use what you've learned this week to evaluate the performance among various algorithmic, hedge, and mutual fund portfolios and compare them against the S&P 500.<jupyter_code>import pandas as pd
import numpy as np
import datetime as dt
from pathlib import Path
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Data CleaningIn this section, you will need to read the CSV files into DataFrames and perform any necessary data cleaning steps. After cleaning, combine all DataFrames into a single DataFrame.Files:1. whale_returns.csv2. algo_returns.csv3. sp500_history.csv  Whale ReturnsRead the Whale Portfolio daily returns and clean the data<jupyter_code># Reading whale returns
whale_returns_csv = Path("whale_returns.csv")
whale_returns_dataframe = pd.read_csv(whale_returns_csv)
whale_returns_dataframe.set_index(pd.to_datetime(whale_returns_dataframe['Date'], infer_datetime_format=True), inplace=True)
whale_returns_dataframe.drop(columns=['Date'], inplace=True)
whale_returns_dataframe.sort_index(ascending=True, inplace=True)
whale_returns_dataframe.head(5)

whale_returns_dataframe.dtypes
# Count nulls
whale_returns_dataframe.isnull().sum()
# Drop nulls
whale_returns_dataframe.dropna(inplace=True)
whale_returns_dataframe.isnull().sum()<jupyter_output><empty_output><jupyter_text>Algorithmic Daily ReturnsRead the algorithmic daily returns and clean the data<jupyter_code># Reading algorithmic returns
algo_returns_csv = Path("algo_returns.csv")
algo_returns_dataframe = pd.read_csv(algo_returns_csv)
algo_returns_dataframe.set_index(pd.to_datetime(algo_returns_dataframe['Date'], infer_datetime_format=True), inplace=True)
algo_returns_dataframe.drop(columns=['Date'], inplace=True)
algo_returns_dataframe.sort_index(ascending=True, inplace=True)
algo_returns_dataframe.head(10)
# Count nulls
algo_returns_dataframe.isnull().sum()
# Drop nulls
algo_returns_dataframe.dropna(inplace=True)
algo_returns_dataframe.isnull().sum()<jupyter_output><empty_output><jupyter_text>S&P 500 ReturnsRead the S&P500 Historic Closing Prices and create a new daily returns DataFrame from the data.<jupyter_code># Reading S&P 500 Closing Prices, sorting index
sp500_history_csv = Path("sp500_history.csv")
sp500_history_dataframe = pd.read_csv(sp500_history_csv)
sp500_history_dataframe.set_index(pd.to_datetime(sp500_history_dataframe['Date'], infer_datetime_format=True), inplace=True)
sp500_history_dataframe.drop(columns=['Date'], inplace=True)
sp500_history_dataframe.sort_index(ascending=True, inplace=True)
sp500_history_dataframe.head(10)
# Check Data Types
sp500_history_dataframe.dtypes
# Fix Data Types
sp500_history_dataframe['Close'] = sp500_history_dataframe['Close'].str.replace('$', '')
sp500_history_dataframe['Close'] = sp500_history_dataframe['Close'].astype('float')
sp500_history_dataframe.dtypes
# Calculate Daily Returns
sp500_history_dataframe = sp500_history_dataframe.pct_change()
sp500_history_dataframe
# Drop nulls
sp500_history_dataframe.dropna(inplace=True)
sp500_history_dataframe.isnull().sum()
# Rename Column
sp500_history_dataframe = sp500_history_dataframe.rename(columns={
    "Close": "S&P500"
})<jupyter_output><empty_output><jupyter_text>Combine Whale, Algorithmic, and S&P 500 Returns<jupyter_code># Concatenate all DataFrames into a single DataFrame
combined_returns_dataframe = pd.concat([sp500_history_dataframe, algo_returns_dataframe, whale_returns_dataframe], axis="columns", join="inner")
combined_returns_dataframe
combined_returns_dataframe.isnull().sum()
combined_returns_dataframe.dropna(inplace=True)
# Concatenate all DataFrames into a single DataFrame
combined_returns_dataframe.sum()<jupyter_output><empty_output><jupyter_text>---  Portfolio AnalysisIn this section, you will calculate and visualize performance and risk metrics for the portfolios.  PerformanceCalculate and Plot the daily returns and cumulative returns.<jupyter_code># Plot daily returns
combined_returns_dataframe.plot()
# Plot cumulative returns
cumulative_combined_returns = (1 + combined_returns_dataframe).cumprod().plot()<jupyter_output><empty_output><jupyter_text>---  RiskDetermine the _risk_ of each portfolio:1. Create a box plot for each portfolio. 2. Calculate the standard deviation for all portfolios4. Determine which portfolios are riskier than the S&P 5005. Calculate the Annualized Standard Deviation<jupyter_code># Box plot to visually show risk
combined_returns_dataframe.plot.box(rot=45,figsize=(10,10), title="Box Plot of Daily Returns")
# Daily Standard Deviations
# Calculate the standard deviation for each portfolio. Which portfolios are riskier than the S&P 500?
daily_std_combined = combined_returns_dataframe.std()
daily_std_combined
# Determine which portfolios are riskier than the S&P 500
daily_std_combined = daily_std_combined.sort_values(ascending=False)
daily_std_combined
# Calculate the annualized standard deviation (252 trading days)
annualized_std_combined = daily_std_combined * np.sqrt(252)
annualized_std_combined<jupyter_output><empty_output><jupyter_text>---  Rolling StatisticsRisk changes over time. Analyze the rolling statistics for Risk and Beta. 1. Calculate and plot the rolling standard deviation for the S&PP 500 using a 21 day window2. Calculate the correlation between each stock to determine which portfolios may mimick the S&P 5002. Calculate and plot a 60 day Beta for Berkshire Hathaway Inc compared to the S&&P 500<jupyter_code># Calculate and plot the rolling standard deviation for the S&PP 500 using a 21 day window
combined_returns_dataframe["S&P500"].rolling(window=21).std().plot(figsize=(20,10), title="Rolling standard deviation for the S&P 500")
# Calculate and plot the rolling standard deviation for the S&PP 500 using a 21 day window
combined_returns_dataframe.rolling(window=21).std().plot(figsize=(20,10), title="Rolling standard deviation for all portfolios and the S&P 500")
# Correlation
combined_corr_table = combined_returns_dataframe.corr()
combined_corr_table
# Calculate Beta for a single portfolio compared to the total market (S&P 500)
covariance = combined_returns_dataframe['Algo 2'].cov(combined_returns_dataframe['S&P500'])
print(covariance)
variance = combined_returns_dataframe['S&P500'].var()
print(variance)
algo2_beta = covariance / variance
print(algo2_beta)
rolling_covariance = combined_returns_dataframe['Algo 2'].rolling(window=30).cov(combined_returns_dataframe['S&P500'])
rolling_covariance.plot(figsize=(20, 10), title='Rolling 30-Day Covariance of Algo 2 Returns vs. S&P500 Returns')
rolling_variance = combined_returns_dataframe['S&P500'].rolling(window=30).var()
rolling_variance.plot(figsize=(20, 10), title='Rolling 30-Day Variance of S&P500 Returns')
# Calculate 30-day rolling beta of AMZN and plot the data
rolling_beta = rolling_covariance / rolling_variance
rolling_beta.plot(figsize=(20, 10), title='Rolling 30-Day Beta of Algo 2 portfolio to the S&P500')<jupyter_output><empty_output><jupyter_text>---  Sharpe RatiosIn reality, investment managers and thier institutional investors look at the ratio of return-to-risk, and not just returns alone. (After all, if you could invest in one of two portfolios, each offered the same 10% return, yet one offered lower risk, you'd take that one, right?)Calculate and plot the annualized Sharpe ratios for all portfolios to determine which portfolio has the best performance<jupyter_code># Annualzied Sharpe Ratios
sharpe_ratios = (combined_returns_dataframe.mean() * 252) / (daily_std_combined * np.sqrt(252))
sharpe_ratios<jupyter_output><empty_output><jupyter_text>plot() these sharpe ratios using a barplot. On the basis of this performance metric, do our algo strategies outperform both 'the market' and the whales?<jupyter_code># Visualize the sharpe ratios as a bar plot
sharpe_ratios.plot.bar(rot=45, figsize=(20, 10), title='Portfolio Sharpe Ratios')<jupyter_output><empty_output><jupyter_text>---  Portfolio ReturnsIn this section, you will build your own portfolio of stocks, calculate the returns, and compare the results to the Whale Portfolios and the S&P 500. 1. Choose 3-5 custom stocks with at last 1 year's worth of historic prices and create a DataFrame of the closing prices and dates for each stock.2. Calculate the weighted returns for the portfolio assuming an equal number of shares for each stock3. Join your portfolio returns to the DataFrame that contains all of the portfolio returns4. Re-run the performance and risk analysis with your portfolio to see how it compares to the others5. Include correlation analysis to determine which stocks (if any) are correlated  Choose 3-5 custom stocks with at last 1 year's worth of historic prices and create a DataFrame of the closing prices and dates for each stock.<jupyter_code>aapl_path = Path("customportfolio/aapl_history.csv")
aep_path = Path("customportfolio/aep_history.csv")
amn_path = Path("customportfolio/amn_history.csv")
amt_path = Path("customportfolio/amt_history.csv")
amzn_path = Path("customportfolio/amzn_history.csv")
cashusd_path = Path("customportfolio/cashusd_history.csv")
cci_path = Path("customportfolio/cci_history.csv")
crm_path = Path("customportfolio/crm_history.csv")
dltr_path = Path("customportfolio/dltr_history.csv")
ecpg_path = Path("customportfolio/ecpg_history.csv")
eqix_path = Path("customportfolio/eqix_history.csv")
etr_path = Path("customportfolio/etr_history.csv")
gld_path = Path("customportfolio/gld_history.csv")
hrl_path = Path("customportfolio/hrl_history.csv")
hsy_path = Path("customportfolio/hsy_history.csv")
ko_path = Path("customportfolio/ko_history.csv")
lmt_path = Path("customportfolio/lmt_history.csv")
msft_path = Path("customportfolio/msft_history.csv")
nee_path = Path("customportfolio/nee_history.csv")
praa_path = Path("customportfolio/praa_history.csv")
uup_path = Path("customportfolio/uup_history.csv")
v_path = Path("customportfolio/v_history.csv")
wmt_path = Path("customportfolio/wmt_history.csv")
spy_path = Path("customportfolio/spy_history.csv")

aapl_data = pd.read_csv(aapl_path)
aep_data = pd.read_csv(aep_path)
amn_data = pd.read_csv(amn_path)
amt_data = pd.read_csv(amt_path)
amzn_data = pd.read_csv(amzn_path)
cashusd_data = pd.read_csv(cashusd_path)
cci_data = pd.read_csv(cci_path)
crm_data = pd.read_csv(crm_path)
dltr_data = pd.read_csv(dltr_path)
ecpg_data = pd.read_csv(ecpg_path)
eqix_data = pd.read_csv(eqix_path)
etr_data = pd.read_csv(etr_path)
gld_data = pd.read_csv(gld_path)
hrl_data = pd.read_csv(hrl_path)
hsy_data = pd.read_csv(hsy_path)
ko_data = pd.read_csv(ko_path)
lmt_data = pd.read_csv(lmt_path)
msft_data = pd.read_csv(msft_path)
nee_data = pd.read_csv(nee_path)
praa_data = pd.read_csv(praa_path)
uup_data = pd.read_csv(uup_path)
v_data = pd.read_csv(v_path)
wmt_data = pd.read_csv(wmt_path)
spy_data = pd.read_csv(spy_path)
aapl_data.set_index(pd.to_datetime(aapl_data['Date'], infer_datetime_format=True), inplace=True)
aapl_data.drop(columns=['Date'], inplace=True)
aapl_data.sort_index(ascending=True, inplace=True)

aep_data.set_index(pd.to_datetime(aep_data['Date'], infer_datetime_format=True), inplace=True)
aep_data.drop(columns=['Date'], inplace=True)
aep_data.sort_index(ascending=True, inplace=True)

amn_data.set_index(pd.to_datetime(amn_data['Date'], infer_datetime_format=True), inplace=True)
amn_data.drop(columns=['Date'], inplace=True)
amn_data.sort_index(ascending=True, inplace=True)

amt_data.set_index(pd.to_datetime(amt_data['Date'], infer_datetime_format=True), inplace=True)
amt_data.drop(columns=['Date'], inplace=True)
amt_data.sort_index(ascending=True, inplace=True)

amzn_data.set_index(pd.to_datetime(amzn_data['Date'], infer_datetime_format=True), inplace=True)
amzn_data.drop(columns=['Date'], inplace=True)
amzn_data.sort_index(ascending=True, inplace=True)

cashusd_data.set_index(pd.to_datetime(cashusd_data['Date'], infer_datetime_format=True), inplace=True)
cashusd_data.drop(columns=['Date'], inplace=True)
cashusd_data.sort_index(ascending=True, inplace=True)

cci_data.set_index(pd.to_datetime(cci_data['Date'], infer_datetime_format=True), inplace=True)
cci_data.drop(columns=['Date'], inplace=True)
cci_data.sort_index(ascending=True, inplace=True)

crm_data.set_index(pd.to_datetime(crm_data['Date'], infer_datetime_format=True), inplace=True)
crm_data.drop(columns=['Date'], inplace=True)
crm_data.sort_index(ascending=True, inplace=True)

dltr_data.set_index(pd.to_datetime(dltr_data['Date'], infer_datetime_format=True), inplace=True)
dltr_data.drop(columns=['Date'], inplace=True)
dltr_data.sort_index(ascending=True, inplace=True)

ecpg_data.set_index(pd.to_datetime(ecpg_data['Date'], infer_datetime_format=True), inplace=True)
ecpg_data.drop(columns=['Date'], inplace=True)
ecpg_data.sort_index(ascending=True, inplace=True)

eqix_data.set_index(pd.to_datetime(eqix_data['Date'], infer_datetime_format=True), inplace=True)
eqix_data.drop(columns=['Date'], inplace=True)
eqix_data.sort_index(ascending=True, inplace=True)

etr_data.set_index(pd.to_datetime(etr_data['Date'], infer_datetime_format=True), inplace=True)
etr_data.drop(columns=['Date'], inplace=True)
etr_data.sort_index(ascending=True, inplace=True)

gld_data.set_index(pd.to_datetime(gld_data['Date'], infer_datetime_format=True), inplace=True)
gld_data.drop(columns=['Date'], inplace=True)
gld_data.sort_index(ascending=True, inplace=True)

hrl_data.set_index(pd.to_datetime(hrl_data['Date'], infer_datetime_format=True), inplace=True)
hrl_data.drop(columns=['Date'], inplace=True)
hrl_data.sort_index(ascending=True, inplace=True)

hsy_data.set_index(pd.to_datetime(hsy_data['Date'], infer_datetime_format=True), inplace=True)
hsy_data.drop(columns=['Date'], inplace=True)
hsy_data.sort_index(ascending=True, inplace=True)

ko_data.set_index(pd.to_datetime(ko_data['Date'], infer_datetime_format=True), inplace=True)
ko_data.drop(columns=['Date'], inplace=True)
ko_data.sort_index(ascending=True, inplace=True)

lmt_data.set_index(pd.to_datetime(lmt_data['Date'], infer_datetime_format=True), inplace=True)
lmt_data.drop(columns=['Date'], inplace=True)
lmt_data.sort_index(ascending=True, inplace=True)

msft_data.set_index(pd.to_datetime(msft_data['Date'], infer_datetime_format=True), inplace=True)
msft_data.drop(columns=['Date'], inplace=True)
msft_data.sort_index(ascending=True, inplace=True)

nee_data.set_index(pd.to_datetime(nee_data['Date'], infer_datetime_format=True), inplace=True)
nee_data.drop(columns=['Date'], inplace=True)
nee_data.sort_index(ascending=True, inplace=True)

praa_data.set_index(pd.to_datetime(praa_data['Date'], infer_datetime_format=True), inplace=True)
praa_data.drop(columns=['Date'], inplace=True)
praa_data.sort_index(ascending=True, inplace=True)

uup_data.set_index(pd.to_datetime(uup_data['Date'], infer_datetime_format=True), inplace=True)
uup_data.drop(columns=['Date'], inplace=True)
uup_data.sort_index(ascending=True, inplace=True)

v_data.set_index(pd.to_datetime(v_data['Date'], infer_datetime_format=True), inplace=True)
v_data.drop(columns=['Date'], inplace=True)
v_data.sort_index(ascending=True, inplace=True)

wmt_data.set_index(pd.to_datetime(wmt_data['Date'], infer_datetime_format=True), inplace=True)
wmt_data.drop(columns=['Date'], inplace=True)
wmt_data.sort_index(ascending=True, inplace=True)

spy_data.set_index(pd.to_datetime(spy_data['Date'], infer_datetime_format=True), inplace=True)
spy_data.drop(columns=['Date'], inplace=True)
spy_data.sort_index(ascending=True, inplace=True)
aapl_data = aapl_data.rename(columns={
    "Close": "aapl"
})

aep_data = aep_data.rename(columns={
    "Close": "aep"
})

amn_data = amn_data.rename(columns={
    "Close": "amn"
})

amt_data = amt_data.rename(columns={
    "Close": "amt"
})

amzn_data = amzn_data.rename(columns={
    "Close": "amzn"
})

cashusd_data = cashusd_data.rename(columns={
    "Close": "cashusd"
})
cashusd_data['cashusd'] = cashusd_data['cashusd'].astype('float')

cci_data = cci_data.rename(columns={
    "Close": "cci"
})

crm_data = crm_data.rename(columns={
    "Close": "crm"
})

dltr_data = dltr_data.rename(columns={
    "Close": "dltr"
})

ecpg_data = ecpg_data.rename(columns={
    "Close": "ecpg"
})

eqix_data = eqix_data.rename(columns={
    "Close": "eqix"
})

etr_data = etr_data.rename(columns={
    "Close": "etr"
})

gld_data = gld_data.rename(columns={
    "Close": "gld"
})

hrl_data = hrl_data.rename(columns={
    "Close": "hrl"
})

hsy_data = hsy_data.rename(columns={
    "Close": "hsy"
})

ko_data = ko_data.rename(columns={
    "Close": "ko"
})

lmt_data = lmt_data.rename(columns={
    "Close": "lmt"
})

msft_data = msft_data.rename(columns={
    "Close": "msft"
})

nee_data = nee_data.rename(columns={
    "Close": "nee"
})

praa_data = praa_data.rename(columns={
    "Close": "praa"
})

uup_data = uup_data.rename(columns={
    "Close": "uup"
})

v_data = v_data.rename(columns={
    "Close": "v"
})

wmt_data = wmt_data.rename(columns={
    "Close": "wmt"
})

spy_data = spy_data.rename(columns={
    "Close": "spy"
})
# Concatenate all stocks into a single DataFrame
custom_portfolio = pd.concat([aapl_data, aep_data, amn_data, amt_data, amzn_data, cashusd_data, cci_data, crm_data, dltr_data, ecpg_data, eqix_data, etr_data, gld_data, hrl_data, hsy_data, ko_data, lmt_data, msft_data, nee_data, praa_data, uup_data, v_data, wmt_data], axis="columns", join="inner")
custom_portfolio = custom_portfolio.pct_change()
custom_portfolio.head()


# Drop nulls
custom_portfolio.dropna(inplace=True)
custom_portfolio.isnull().sum()<jupyter_output><empty_output><jupyter_text>Calculate the weighted returns for the portfolio assuming an equal number of shares for each stock<jupyter_code># Calculate weighted portfolio returns
weights = [0.05, 0.03, 0.02, 0.03, 0.03, 0.24, 0.03, 0.03, 0.03, 0.01, 0.03, 0.03, 0.07, 0.02, 0.03, 0.04, 0.03, 0.03, 0.03, 0.01, 0.08, 0.06, 0.04]
custom_portfolio_returns = custom_portfolio.dot(weights)
custom_portfolio_returns.head()<jupyter_output><empty_output><jupyter_text>Join your portfolio returns to the DataFrame that contains all of the portfolio returns<jupyter_code>combined_with_custom = pd.concat([custom_portfolio_returns, combined_returns_dataframe], axis="columns", join="inner")

combined_with_custom = combined_with_custom.rename(columns={
    0: "Custom"
})

combined_with_custom.head(10)
#note - instructions to download one year of data from current left very little overlap with performance data from starter files
spy_data = spy_data.pct_change()
custom_and_spy = pd.concat([custom_portfolio_returns, spy_data], axis="columns", join="inner")
custom_and_spy = custom_and_spy.rename(columns={
    0: "Custom"
})
custom_and_spy.head()


# Only compare dates where return data exists for all the stocks (drop NaNs)
combined_with_custom.dropna(inplace=True)
combined_with_custom.isnull().sum()

# Only compare dates where return data exists for all the stocks (drop NaNs)

custom_and_spy.dropna(inplace=True)
custom_and_spy.isnull().sum()<jupyter_output><empty_output><jupyter_text>Re-run the performance and risk analysis with your portfolio to see how it compares to the others<jupyter_code>custom_and_spy.head()
# Risk
# Box plot to visually show risk
custom_and_spy.plot.box(rot=45,figsize=(10,10), title="Box Plot of Daily Returns")
#calculate std
daily_std_custom = custom_and_spy.std()
daily_std_custom

#calculate annualized std
annualized_std_custom = daily_std_custom * np.sqrt(252)
annualized_std_custom
# Rolling
custom_and_spy.rolling(window=21).std().plot(figsize=(20,10), title="Rolling standard deviation for the custom portfolio and the S&P 500")
# Beta
covariancecustom = custom_and_spy['Custom'].cov(custom_and_spy['spy'])
print(covariancecustom)
variancecustom = custom_and_spy['spy'].var()
print(variancecustom)
custom_beta = covariance / variance
print(custom_beta)
# Annualzied Sharpe Ratios
sharpe_ratios_custom = (custom_and_spy.mean() * 252) / (daily_std_custom * np.sqrt(252))
sharpe_ratios_custom

# Visualize the sharpe ratios as a bar plot
sharpe_ratios_custom.plot.bar(rot=45, figsize=(20, 10), title='Custom Portfolio Sharpe Ratio')<jupyter_output><empty_output><jupyter_text>Include correlation analysis to determine which stocks (if any) are correlated<jupyter_code>custom_corr_table = custom_portfolio.corr()
custom_corr_table
#There are a number of highly correlated stocks, such as NEE vs AMT vs CCI, as these are energy stocks. Oddly though, KO is also highly correlated with NEE, AEO<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11425229089491129, 'fraction_numerical': 0.017352310391889256, 'mean_word_length': 8.233573357335734, 'pattern_counts': {'":': 25, '<': 61, '<?xml version=': 0, '>': 61, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '304746', 'license': 'ADSL', 'n_tokens_mistral': 7522, 'n_tokens_neox': 7157, 'n_words': 1474, 'path': 'pandas-homework.ipynb', 'repo_name': 'bobbent9/pandas-homework', 'size': '20516'}