After a deep dive into the Covid19 situation (e.g., whether things are going to get worse or better; how government interventions on the biological, economical, and financial warfare have been working; how certain stocks, industries, and asset classes tend to perform amidst the pandemic) in the chosen country, you have developed a point of view of how things are going to evolve the coming year. Based on the point of view and the team brainstorming, you have developed a sense of what stocks, industries, or asset classes to invest in. Even though your fund is based in the chosen country, you can diversify your portfolio beyond the country and into other asset classes.

1.     Based on your investment strategy (i.e., chosen stocks, industries, asset classes), identify the maximum Sharpe ratio and minimum volatility portfolios. 

2.     If you have USD 1 million now, how much should you invest in each. 

3.      Plot a graph showing how your portfolios (maximum Sharpe and minimum volatility portfolios) perform relative to the market (i.e., SPY).

 4.      Comment on your strategy note and highlight the potential future issues/risks

B 1. Plot a histogram of the portfolio return of your strategy (both maximum Sharpe ratio and minimum volatility). Hint: you can use the portfolio returns you calculated and see how you plot histogram in the oil pipeline example

 B 2.      Construct the confidence intervals for both portfolios. Hint: you can see an example of how to do this in the oil pipeline example. There are two ways to construct. It can be calculated based on either the bootstrapped sample (bootstrapped confidence interval) or mathematically (theoretical confidence interval).

Importing Packages and Data

# Install spatial packages !apt install gdal-bin python-gdal python3-gdal --quiet !apt install python3-rtree --quiet !pip install --upgrade pip --quiet !pip install git+git://github.com/geopandas/geopandas.git --quiet !pip install descartes --quiet # Install Pysal packages !pip install splot --quiet !pip install libpysal --quiet !pip install esda --quiet ## Import Libraries # math operations from numpy import inf # time operations from datetime import timedelta # for numerical analyiss import numpy as np # to store and process data in dataframe import pandas as pd # basic visualization package import matplotlib.pyplot as plt # advanced ploting !pip3 install plotly.geopandas # interactive visualization import plotly.express as px import plotly.graph_objs as go import plotly.figure_factory as ff from plotly.subplots import make_subplots # Import modules for API calls import requests import io import requests import json from datetime import datetime # Import module for plotting import seaborn as sns # hide warnings import warnings warnings.filterwarnings("ignore") # setup color pallette cnf, dth, rec, act = "#393e46", "#ff2e63", "#21bf73", "#fe9801" #importing Autoviz class !pip install wordcloud==1.8.1 !pip install autoviz==0.0.84 !pip install xlrd==2.0.1 from autoviz.AutoViz_Class import AutoViz_Class from libpysal import weights import mapclassify import esda from esda.moran import Moran, Moran_Local import splot from splot.esda import moran_scatterplot, plot_moran, lisa_cluster

## US Latest Covid Data (taken from: https://healthdata.gov/Hospital/COVID-19-Reported-Patient-Impact-and-Hospital-Capa/g62h-syeh) urlCov = "https://healthdata.gov/resource/g62h-syeh.csv" USCov = pd.read_csv(urlCov) # Convert Date from Dtype 'Object' (or String) to Dtype 'Datetime' USCov["date"] = pd.to_datetime(USCov["date"]) # Replace missing values '' with NAN and then 0 USCov = USCov.replace('', np.nan).fillna(0) USCov.info() USCov.head(10)

cov_API_KEY = "dbe3ecae6e094d54a5db1abf8c3faed9" ## Set up API call urlCov_S = "https://api.covidactnow.org/v2/states.timeseries.csv?apiKey=" + cov_API_KEY # Get CSV data USCov_S = pd.read_csv(urlCov_S) # Convert Date from Dtype 'Object' (or String) to Dtype 'Datetime' USCov_S["date"] = pd.to_datetime(USCov_S["date"]) # Replace missing values '' with NAN and then 0 USCov_S = USCov_S.replace('', np.nan).fillna(0) USCov_S.info() USCov_S.head(10)

# Covid Data per US County cov_API_KEY = "dbe3ecae6e094d54a5db1abf8c3faed9" ## Set up API call urlCov_C = "https://api.covidactnow.org/v2/counties.timeseries.csv?apiKey=" + cov_API_KEY # Get CSV data USCov_C = pd.read_csv(urlCov_S) # Convert Date from Dtype 'Object' (or String) to Dtype 'Datetime' USCov_C["date"] = pd.to_datetime(USCov_C["date"]) # Replace missing values '' with NAN and then 0 USCov_C = USCov_C.replace('', np.nan).fillna(0) USCov_C.info() USCov_C.head(100)

## US Latest Vaccination Data (taken from: https://github.com/owid/covid-19-data/tree/master/public/data/vaccinations) urlVac = "https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/us_state_vaccinations.csv" USVac = pd.read_csv(urlVac) # Convert Date from Dtype 'Object' (or String) to Dtype 'Datetime' USVac["date"] = pd.to_datetime(USVac["date"]) # Replace missing values '' with NAN and then 0 USVac = USVac.replace('', np.nan).fillna(0) USVac.info() USVac.head(10)

## US Google COmmunity Mobility (taken from:https://raw.githubusercontent.com/ActiveConclusion/COVID19_mobility/master/google_reports/mobility_report_US.csv) urlGCM = "https://raw.githubusercontent.com/ActiveConclusion/COVID19_mobility/master/google_reports/mobility_report_US.csv" USGCM = pd.read_csv(urlGCM) # Convert Date from Dtype 'Object' (or String) to Dtype 'Datetime' USGCM["date"] = pd.to_datetime(USGCM["date"]) # Replace missing values '' with NAN and then 0 USGCM = USGCM.replace('', np.nan).fillna(0) USGCM.info() USGCM.tail(10)

states = ["AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DC", "DE", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY"] statePopulations = { 'AK': '733391', 'AL': '5024279', 'AR' : '3011524', 'AZ' : '7151502', 'CA': '39538223', 'CO' : '5773714', 'CT' : '3605944', 'DE': '989948', 'FL': '21538187', 'GA': '10711908', 'HI': '1455271', 'IA': '3190369', 'ID': '1839106', 'IL': '12812508', 'IN': '6785528', 'KS': '2937880', 'KY': '4505836', 'LA': '4657757', 'MA': '7029917', 'MD': '6177224', 'ME': '1362359', 'MI': '10077331', 'MN': '5706494', 'MO': '6154913', 'MS': '2961279', 'MT': '1084225', 'NC': '10439388', 'ND': '779094', 'NE': '1961504', 'NH': '1377529', 'NJ': '9288994', 'NM': '2117522', 'NV': '3104614', 'NYC': '20201249', 'OH': '11799448', 'OK': '3959353', 'OR': '4237256', 'PA': '13011844', 'RI': '1097379', 'SC': '5118425', 'SD': '886667', 'TN': '6910840', 'TX': '29145505', 'UT': '3271616', 'VA': '8631393', 'WA': '7705281', 'WI': '5893718', 'WV': '1793716', 'WY': '576851' }

# Import Yahoo Finance API !pip install yfinance --upgrade --no-cache-dir

# Install FRED API FRED_API_KEY = "6aef92583b4ec26838278394ebd80611" # Set Parameters realtime_start = "1990-01-01" realtime_end = "2021-03-31" series_id = "GDP" freq = "q" ## Set up API call url = "https://api.stlouisfed.org/fred/series/observations?series_id=" + \ series_id + "&realtime_start=" + \ realtime_start + "&realtime_end=" + realtime_end + "&aggregation_method=avg" + "&api_key=" + \ FRED_API_KEY + "&file_type=json" # Get JSON data, convert from binary data = json.loads(requests.get(url).content.decode('utf-8')) data fred = pd.DataFrame.from_dict(pd.json_normalize(data['observations'])) # Drop unnecessary columns fred = fred.drop(columns = ["realtime_start", "realtime_end"]) # Set date fred = fred.set_index("date") # Data cleaning - convert values to numerical fred['value'] = pd.to_numeric(fred['value'], errors = "coerce", downcast = "float") # Interpolate missing data by linear function fred = fred.interpolate() # note: .interpolate() is a immutable method, returns a new df. Need to re-assign to variable # Rename column fred = fred.rename(columns = {"value": series_id}) fred

#Instantiate the AutoViz class AV = AutoViz_Class() USVac_data = AV.AutoViz('https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/us_state_vaccinations.csv')

4.1 Covid-19 Situation in the United States of America

!pip3 install geopandas !pip3 install pyshp !pip3 install shapely !pip3 install plotly-geo

db = USCov_C list_states = sorted(pd.unique(db['state'])) db_Jan2021 = db[(db["date"]=="2021-01-01")] from urllib.request import urlopen import json with urlopen('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json') as response: counties = json.load(response) import pandas as pd df = pd.read_csv("https://raw.githubusercontent.com/plotly/datasets/master/fips-unemp-16.csv", dtype={"fips": str}) import plotly.express as px fig = px.choropleth(db_Jan2021, geojson=counties, locations=list_states, locationmode="USA-states", color='metrics.caseDensity', color_continuous_scale="amp", range_color=(0, 100), scope="usa", labels={'metrics.caseDensity': 'Case Density'}, ) fig.update_layout( title_text = 'Jan 2021 Covid Case Density', geo_scope='usa', # limit map scope to USA ) fig.show()

db_Jan2021

db.info() db.tail()

# Plot log(Confirmed Cases) for each State Over time import plotly.express as px import pandas as pd fig_2 = px.choropleth( db, locations='state', color=np.log(db["actuals.cases"]), locationmode="USA-states", hover_name='state', animation_frame=db["date"].dt.strftime("%Y-%m-%d"), color_continuous_scale=px.colors.sequential.matter ) fig_2.update_layout(title_text="Cases over time",geo_scope="usa") fig_2.update(layout_coloraxis_showscale=False) fig_2.update_geos(resolution=50) fig_2.show()

# Plot log(Confirmed Cases) for each State Over time import plotly.express as px import pandas as pd fig_2b = px.choropleth( db, locations='state', color=db["metrics.vaccinationsCompletedRatio"], locationmode="USA-states", hover_name='state', animation_frame=db["date"].dt.strftime("%Y-%m-%d"), color_continuous_scale="BuGn", range_color=(0, 1), scope="usa", ) fig_2b.update_layout(title_text="Vaccination over time",geo_scope="usa") fig_2b.update(layout_coloraxis_showscale=False) fig_2b.update_geos(resolution=50) fig_2b.show()

db.tail()

db_Jan2021.info() # Replace missing values '' with NAN and then 0 # What are the alternatives? Drop or impute. Do they make sense in this context? db_Jan2021 = db_Jan2021.replace('', np.nan).fillna(0) #db_Jan2021['Case Positivity'] = round(db_Jan2021['TotalCases']/db_Jan2021['TotalTests'],2) #case positivity: already exist in the columns, metrics.testPositivityRatio db_Jan2021['Case Fatality'] = round(db_Jan2021['actuals.deaths']/db_Jan2021['actuals.cases'],2) # Case Positivity is infinity when there is zero TotalTests due to division by zero #db_Jan2021[db_Jan2021['Case Positivity'] == inf] = 0 # Qcut is quantile cut. Here we specify three equally sized bins and label them low, medium, and high, respectively. db_Jan2021 ['metrics.testPositivityRatio Bin']= pd.qcut(db_Jan2021['metrics.testPositivityRatio'], q=3, labels=['low', 'medium', 'high']) db_Jan2021.info()

temp = db.groupby("date")["actuals.newDeaths", "actuals.newCases", "actuals.hospitalBeds.currentUsageCovid"].sum().reset_index() temp = temp.melt( id_vars="date", value_vars=["actuals.newDeaths", "actuals.newCases", "actuals.hospitalBeds.currentUsageCovid"], var_name="Case", value_name="Count", ) temp.head() # Plot a stack area graph with the three types of cases (i.e., recovered, deaths, and active) fig_3 = px.area( temp, x="date", y="Count", color="Case", height=600, width=700, title="Cases over time", color_discrete_sequence=[rec, dth, act], ) fig_3.update_layout(xaxis_rangeslider_visible=True) fig_3.show()

temp = db.groupby("date")["metrics.vaccinationsCompletedRatio", "metrics.vaccinationsInitiatedRatio"].sum().reset_index() temp = temp.melt( id_vars="date", value_vars=["metrics.vaccinationsCompletedRatio", "metrics.vaccinationsInitiatedRatio"], var_name="Vaccination Rate", value_name="Count", ).replace(to_replace=0, method='bfill') temp.head() # Plot a stack area graph with the three types of cases (i.e., recovered, deaths, and active) fig_3b = px.area( temp, x="date", y="Count", color="Vaccination Rate", height=600, width=700, title="Vaccination Rate over time", color_discrete_sequence=[rec, dth, act], ) fig_3b.update_layout(xaxis_rangeslider_visible=True) fig_3b.show()

# Identify countries with cases greater than N # Then among these countries choose the unique set of countries def gt_n(n): states = db[db["actuals.cases"] > n]["state"].unique() # Filter countries that are in the unique set of countries with cases cases greater than N temp_2= db[db["state"].isin(states)] # Aggregate (i.e., sum up) cases cases by State and Date # Reset the index (it is no longer in running order) temp_2 = temp_2.groupby(["state", "date"])["actuals.cases"].sum().reset_index() # Filter observations with cases cases more than N temp_2 = temp_2[temp_2["actuals.cases"] > n] # print(temp_2.head()) # Identify the start date when cases cases exceed N for each country min_date = temp_2.groupby("state")["date"].min().reset_index() # Name the columns in the dataframe min_date min_date.columns = ["state", "Min Date"] # print(min_date.head()) # Merge dataframe temp_2 with dataframe min_date by 'State' from_nth_case = pd.merge(temp_2, min_date, on="state") # Convert data type to datetime object from_nth_case["date"] = pd.to_datetime(from_nth_case["date"]) from_nth_case["Min Date"] = pd.to_datetime(from_nth_case["Min Date"]) # Create a variable that counts the number of days relative to the day when cases cases exceed N from_nth_case["N days"] = ( from_nth_case["date"] - from_nth_case["Min Date"] ).dt.days # print(from_nth_case.head()) # Plot a line graph from dataframe from_nth_case with column 'N days' and 'Cases' mapped to x-axis and y-axis, respectively. # Distinguish each country by color (system-determined color) # str converts n integer into string and ''N days from '+ str(n) +' case'' is the title fig_4 = px.line( from_nth_case, x="N days", y="actuals.cases", color="state", title="N days from " + str(n) + " case", height=600, ) fig_4.show()

# Call function gt_n with argument 100000 (i.e., 100000 confirmed cases) gt_n(100000)

4.2 Evaluate the potential hidden cases (e.g., case positivity rate) and deaths (e.g., estimated infection fatality rate, excess death).

db.head(10) db_daywise=db.set_index("date").between_time('00:00','00:00') db_daywise

#Average fatality rate in the US print(db_Jan2021['actuals.deaths'].sum()/db_Jan2021['actuals.cases'].sum())

# Plotting Case Fatality def plot_hbar_wm( db_Jan2021, col): fig = px.bar( db_Jan2021, x=col, y="state", color="riskLevels.overall", text=col, orientation="h", width=700, color_discrete_sequence=px.colors.qualitative.Dark2, ) fig.update_layout( title=col , xaxis_title="", yaxis_title="", yaxis_categoryorder="total ascending", uniformtext_minsize=8, uniformtext_mode="hide", ) fig.show() plot_hbar_wm(db_Jan2021, "Case Fatality")

4.3 Explore the relationship between the country's Covid19 cases and deaths and government health intervention policies (e.g., vaccination rate, closure), as well as Google community mobility reports.

db_daywise = db.set_index("date").between_time("00:00","00:00") db_daywise.reset_index() db_daywise_2 = db_daywise.groupby("date")["actuals.cases","actuals.deaths","actuals.vaccinationsCompleted","metrics.caseDensity"].sum() db_daywise_2["case fatality"]=db_daywise_2["actuals.deaths"]/db_daywise_2["actuals.cases"] db_daywise_2 # Filter data from Feb 2021 onwards db_daywise_3 = db_daywise_2.loc['20210201':'20210914'] db_daywise_3.ffill()

# Draw a joint plot to diagnose the relationship between Tests/1M population vs Total Cases/1M population # Draw a regression line on the scatter plot sns.jointplot(x ='actuals.vaccinationsCompleted', y = 'actuals.cases', data = db_daywise_3, kind='reg')

# Show the descriptive statistics for case positivity bin (categorical variable) db_Jan2021.groupby('metrics.testPositivityRatio Bin')['metrics.testPositivityRatio Bin'].describe()

# Draw a Violin plot to diagnose the relationship betwen case positivity and case fatality rates fig_5 = go.Figure() # Create a list of case positivity bin categories bins = ['low', 'medium', 'high'] # Loop through each case positivity bin for bin in bins: # worldometer_data['Case Positivity Bin'][worldometer_data['Case Positivity Bin'] == bin] means take the column 'Case Positivity Bin' and # filter the column, such that Case Positivity Bin equals 'low', 'medium', or 'high' fig_5.add_trace(go.Violin(x=db_Jan2021['metrics.testPositivityRatio Bin'][db_Jan2021['metrics.testPositivityRatio Bin'] == bin], y=db_Jan2021['Case Fatality'][db_Jan2021['metrics.testPositivityRatio Bin'] == bin], name=bin, box_visible=True, meanline_visible=True)) fig_5.update_layout(title='Case Fatality by Case Positivity Bins', yaxis_title='Case Fatality', xaxis_title='Case Positivity Bins', uniformtext_minsize=8, uniformtext_mode='hide') fig_5.show() db_Jan2021.head()

# Merge Google Community Mobility Data with Covid Data USGCM_CA=USGCM[(USGCM['state'] == "California")&(USGCM['date'] >= "2020-02-15")&(USGCM['county'] == "Total")] db_daywise_CA=db_daywise.reset_index() db_daywise_CA=db_daywise_CA[(db_daywise_CA["state"]=="CA")&(db_daywise_CA['date'] >= "2020-02-15")] #USGCM_CA.head()

USGCM_CA.head()

db_daywise_CA.head()

# Merge Google Community Mobility Data with Covid Data db_daywise.head(5) db_CA=pd.merge(USGCM_CA,db_daywise_CA,how="left",on="date") db_CA

import plotly.graph_objects as go from plotly.subplots import make_subplots # Create figure with secondary y-axis fig = make_subplots(specs=[[{"secondary_y": True}]]) # Add traces fig.add_trace( go.Scatter(x=db_CA["date"], y=db_CA["transit stations"], name="Transit Station Traffic"), secondary_y=False, ) fig.add_trace( go.Scatter(x=db_CA["date"], y=db_CA["actuals.newCases"], name="New Covid Cases in California"), secondary_y=True, ) # Add figure title fig.update_layout( title_text="Transit Station Traffic vs New Covid Cases in California" ) # Set x-axis title fig.update_xaxes(title_text="Date") # Set y-axes titles fig.update_yaxes(title_text="Transit Station Traffic ", secondary_y=False) fig.update_yaxes(title_text="New Covid Cases ", secondary_y=True) fig.show()

# Wyoming Data Merged USGCM_WY=USGCM[(USGCM['state'] == "Wyoming")&(USGCM['date'] >= "2020-02-15")&(USGCM['county'] == "Total")] db_daywise_WY=db_daywise.reset_index() db_daywise_WY=db_daywise_WY[(db_daywise_WY["state"]=="WY")&(db_daywise_WY['date'] >= "2020-02-15")] #USGCM_WY.head() # Merge Google Community Mobility Data with Covid Data db_daywise.head(5) db_WY=pd.merge(USGCM_WY,db_daywise_WY,how="left",on="date") db_WY

# Create figure with secondary y-axis fig = make_subplots(specs=[[{"secondary_y": True}]]) # Add traces fig.add_trace( go.Scatter(x=db_WY["date"], y=db_WY["transit stations"], name="Transit Station Traffic"), secondary_y=False, ) fig.add_trace( go.Scatter(x=db_WY["date"], y=db_WY["actuals.newCases"], name="New Covid Cases in Wyoming"), secondary_y=True, ) # Add figure title fig.update_layout( title_text="Transit Station Traffic vs New Covid Cases in Wyoming" ) # Set x-axis title fig.update_xaxes(title_text="Date") # Set y-axes titles fig.update_yaxes(title_text="Transit Station Traffic ", secondary_y=False) fig.update_yaxes(title_text="New Covid Cases ", secondary_y=True) fig.show()

# Wyoming Data Merged USGCM_WY=USGCM[(USGCM['state'] == "Wyoming")&(USGCM['date'] >= "2020-02-15")&(USGCM['county'] == "Total")] db_daywise_WY=db_daywise.reset_index() db_daywise_WY=db_daywise_WY[(db_daywise_WY["state"]=="WY")&(db_daywise_WY['date'] >= "2020-02-15")] #USGCM_WY.head() # Merge Google Community Mobility Data with Covid Data db_daywise.head(5) db_WY=pd.merge(USGCM_WY,db_daywise_WY,how="left",on="date") db_WY

# Create figure with secondary y-axis fig = make_subplots(specs=[[{"secondary_y": True}]]) # Add traces fig.add_trace( go.Scatter(x=db_WY["date"], y=db_WY["transit stations"], name="Transit Station Traffic"), secondary_y=False, ) fig.add_trace( go.Scatter(x=db_WY["date"], y=db_WY["actuals.newCases"], name="New Covid Cases in Wyoming"), secondary_y=True, ) # Add figure title fig.update_layout( title_text="Transit Station Traffic vs New Covid Cases in Wyoming" ) # Set x-axis title fig.update_xaxes(title_text="Date") # Set y-axes titles fig.update_yaxes(title_text="Transit Station Traffic ", secondary_y=False) fig.update_yaxes(title_text="New Covid Cases ", secondary_y=True) fig.show()

4.4 Brainstorm on an investment strategy for the country.

Importing Packages and Data

4.1 Covid-19 Situation in the United States of America

4.2 Evaluate the potential hidden cases (e.g., case positivity rate) and deaths (e.g., estimated infection fatality rate, excess death).

4.3 Explore the relationship between the country's Covid19 cases and deaths and government health intervention policies (e.g., vaccination rate, closure), as well as Google community mobility reports.

4.4 Brainstorm on an investment strategy for the country.

Proceed to Next File for Lab 5