<jupyter_start><jupyter_text>Group A12 submission : HW - 2   Section 2 - Solution<jupyter_code>import pandas as pd
import numpy as np
import datetime as dt
import statsmodels.api as sm
import matplotlib.pyplot as plt
plt.style.use('ggplot')
import seaborn as sns
import statsmodels.formula.api as smf
import numpy 
path_to_data_file = r'proshares_analysis_data.xlsx'   # Assuming that the file is in the root/home directory

df_sp = pd.read_excel(path_to_data_file,sheet_name = 2)

df_sp.set_index('date',inplace =True)

S_and_P_data = df_sp['SPY US Equity']

df_hedge_fund = pd.read_excel(path_to_data_file, sheet_name = 1)

df_hedge_fund.set_index('date',inplace = True)

periods = 12<jupyter_output><empty_output><jupyter_text>Question 1  1.  For the series in the “hedgefundseries” tab, report the following summary statistics:1(a)  mean (b)  volatility (c)  Sharpe ratioAnnualize these statistics.<jupyter_code>df_hfs = pd.read_excel('proshares_analysis_data.xlsx',sheet_name = 1).set_index('date')
periods = 12
df_hfs_annualized = df_hfs*periods
mean = df_hfs_annualized.mean()
std = df_hfs_annualized.std()/np.sqrt(periods)
sharpe = mean/std
df_summary = pd.DataFrame({'Mean(%)':mean*100,"Volatility":std,"Sharpe":sharpe})
df_summary<jupyter_output><empty_output><jupyter_text>Question 2  2.  For the series in the “hedgefundseries” tab, , calculate the following statistics related to tail-risk.(a)  Skewness(b)  Excess Kurtosis (in excess of 3)(c)  VaR (.05) - the fifth quantile of historic returns(d)  CVaR (.05) - the mean of the returns at or below the fifth quantile(e)  Maximum drawdown - include the dates of the max/min/recovery within the max drawdownperiod.<jupyter_code>df_hfs_2 = df_hfs 

skew =df_hfs_2.skew(axis = 0)

# Calculating kurtosis
from scipy.stats import kurtosis

kurtosis = pd.Series(index = df_hfs_2.skew(axis = 0).index, data = kurtosis(df_hfs_2, fisher=True, axis = 0))

# Calculating VaR(0.05)
VaR = pd.Series(index = df_hfs_2.skew(axis = 0).index, data = np.percentile(df_hfs, 0.05, axis = 0))



# Calculating CVaR(0.05) - - the mean of the returns at or below the fifth quantile
CVar = df_hfs_2[df_hfs_2 <= VaR].mean()

# Maximum drawdown
cum_return = (1+ df_hfs).cumprod()
rolling_max = cum_return.cummax()
drawdown = (cum_return - rolling_max) / rolling_max
maxDrawdown = drawdown.min()

bottom = pd.Series(pd.to_datetime([drawdown[col].idxmin() for col in drawdown]),index=df_hfs.columns)
peak = pd.Series(pd.to_datetime([(cum_return[col][:bottom[col]].idxmax()) for col in cum_return]),index=df_hfs.columns)
    
peakLevels = pd.Series([cum_return[col].loc[peak.loc[col]] for col in cum_return],index=df_hfs.columns)
recovered = []
for col in cum_return:
    for lev in cum_return[col][bottom[col]:]:
        if lev >= peakLevels[col]:
            recovered.append(cum_return.index[cum_return[col] == lev][0])
            break
pd.DataFrame({'Skewness':skew,'Excess Kurtosis':kurtosis,'5% VaR':VaR,'5% CVaR':CVar,'Max Drawdown':maxDrawdown,'Peak':peak,'Bottom':bottom,'Recovered':recovered})
df_hfs_2.loc['max drawdown'] = drawdown.min()
drawdown.plot(figsize = (15,8),title = 'Drawdown')<jupyter_output><empty_output><jupyter_text>Question 3   3. For the series in the "hedge_fund_series" tab, run a regression of each against SPY. Include an intercept. Report the following regression-based statistics     a. Maket Beta     b. Treynor Ratio     c. Information Ratio Annualize the three stats as appropriate<jupyter_code>## function to clean the data and replace missing values with 0
def clean(data):
    if data.isnull().values.any():
        
        return data.fillna(0)
    else:
        return data
def market_beta(model, x,y, market = False):
    
    beta = model.params.to_frame('Parameters').loc[x.columns[1],'Parameters']
   
    return beta
def Treynor_Ratio(model,x,y,periods):
    trey = []
    pred = model.predict(x).mean()*periods
    beta = model.params.to_frame('Parameters').loc[x.columns[1],'Parameters']
    trey = pred/beta
    return trey
def Information_Ratio(model,x,y,periods):
    
    alpha = model.params.to_frame('Parameters').loc[x.columns[0],'Parameters']
    sigma = (y-model.predict(x)).std()
    info = alpha/(sigma*(periods**0.5))
    return info
def regression(predicted_var, predicting_var, periods = 12, intercept = False):
    ## for the regression model
    y = predicted_var
    y = clean(y)
    
    
    
    
    x = predicting_var
    x = clean(x)
    if intercept == True:
        x = sm.add_constant(x)
    
    
    model  =sm.OLS(y,x,missing = 'drop').fit()
    
     
    meanr = y.mean()*periods
    s = meanr/(y.std()*(periods)**0.5)
    m = market_beta(model,x,y,market=True)
    t = Treynor_Ratio(model,x,y,periods)
    i = Information_Ratio(model,x,y,periods)
    r = model.rsquared
    
    
    return meanr,s,m,t, i,r



mean_returns = {}
sharpe = {}
beta = {}
trey = {}
info = {}
r_sq = {}
for c in df_hedge_fund.columns:
    mean_returns[c],sharpe[c],beta[c], trey[c], info[c],r_sq[c] = regression(df_hedge_fund[c],S_and_P_data,intercept = True)

df_summary = pd.DataFrame([mean_returns,sharpe,r_sq,beta,trey,info])

df_summary = df_summary.T.rename(columns = {0:'Mean Returns (%)',1:'Sharpe Ratio',2:'R-squared',3:'Market Beta',4:'Treynor Ratio',5:'Information Ratio'})

df_summary['Mean Returns (%)'] = df_summary['Mean Returns (%)']*100

df_summary.loc[S_and_P_data.name] = [S_and_P_data.mean()*100,(S_and_P_data.mean()/S_and_P_data.std())*(periods)**0.5,1,1,'NA','NA']

df_summary<jupyter_output><empty_output><jupyter_text>Question 4  Discuss the previous statistics, and what they tell us about... (a) the differences between SPY and the hedge-fund series? (b) which performs better between HDG and QAI. (c) whether HDG and the ML series capture the most notable properties of HFRI.  a. All the hedge funds got better mean returns while having a higher sharpe ratio. For a risk averse investor, S&P could have been a  better bet.     All the hedge funds had a low information ratio but a high R-squared, this means that they took on a bit more risk but did not deviate too far from the S&P as it can explain so much of the variance of the hedge funds. For the hedge funds with sharpe ratios close to S&P's sharpe ratio (HFRIFWI and MLEIFCTR), the fund managers were able to mean - variance optimize the portfolio well   b. HDG has a slightly higher mean return but lower values of Sharpe and Information ratios as compared to QAI. This means that the extra performance came at a cost of higher volatility and probably involved a bit of luck as compared to QAI. Overall it is close but QAI has slightly better statistics   c. HFRI has a very high correaltion with the ML series and HDG. Also all the 4 series have a very similar Beta with the S&P, which probably implies that they similar average volatilites. Overall, on average HDG and ML do capture the most notable features of HFRI but they will probably provide lesser mean returns  along with lower volatility and a less risk of major loss as HFRI's tails are fatter. This is we believe is a case of average returns and average volatility masking the risky tail events.  Question 5 5.  Report the correlation matrix for these assets.(a)  Show the correlations as a heat map.(b)  Which series have the highest and lowest correlations?<jupyter_code>path_to_data_file = 'proshares_analysis_data.xlsx'
df = pd.read_excel(path_to_data_file,sheet_name='hedge_fund_series')
df
df.corr()
sns.heatmap(df.corr())<jupyter_output><empty_output><jupyter_text>HDG US Equity and QAI US Equity has lowest correlation; MLEIFCTR Index and MLEIFCTX Index has highest correlation.  Question 6  Replicate HFRI with the six factors listed on the “merrillfactors” tab. Include a constant, and run the unrestricted regression,      \begin{align}  r^{hfri}_{t} = \alpha^{merr} + x^{merr}_{t} \beta^{merr} + \epsilon^{merr}_{t} (1)  \end{align}  \begin{align}  \hat r^{hfri}_{t} = \hat\alpha^{merr} + x^{merr}_{t} \hat \beta^{merr} (2)  \end{align}     (a)  Report the intercept and betas.  (b)  Are the betas realistic position sizes, or do they require huge long-short positions?  (c)  Report the R-squared.  (d)  Report the volatility of $\epsilon^{merr}$ (the tracking error.)<jupyter_code>from dataclasses import dataclass
import warnings
# regression function
@dataclass
class RegressionsOutput:
    excess_ret_stats: pd.DataFrame
    params: pd.DataFrame
    residuals: pd.DataFrame
    tstats: pd.DataFrame
    other: pd.DataFrame
    df: pd.DataFrame

def lfm_time_series_regression(df, portfolio_names, factors, annualize_factor=12):
    excess_ret_stats = pd.DataFrame(index=factors, columns=['average', 'std'], dtype=float)
    for factor in factors:
        excess_ret_stats.loc[factor, 'average'] = annualize_factor * df[factor].mean()
        excess_ret_stats.loc[factor, 'std'] = np.sqrt(annualize_factor) * df[factor].std()
        excess_ret_stats.loc[factor, 'sharpe_ratio'] = \
            excess_ret_stats.loc[factor, 'average'] / excess_ret_stats.loc[factor, 'std']
        # Here I'll just report the unscaled skewness
        excess_ret_stats.loc[factor, 'skewness'] = df[factor].skew()
        # excess_ret_stats.loc[factor, 'skewness'] = annualize_factor * df[factor].skew()

    _temp_excess_ret_stats = excess_ret_stats.copy()
    _temp_excess_ret_stats.loc['const', :] = 0

    with warnings.catch_warnings():
        warnings.simplefilter("ignore")
        rhs = sm.add_constant(df[factors])
    df_params = pd.DataFrame(columns=portfolio_names)
    df_other = pd.DataFrame(columns=portfolio_names)
    df_residuals = pd.DataFrame(columns=portfolio_names)
    df_tstats = pd.DataFrame(columns=portfolio_names)
    for portfolio in portfolio_names:
        lhs = df[portfolio]
        res = sm.OLS(lhs, rhs, missing='drop').fit()
        df_params[portfolio] = res.params
        df_params.loc['const', portfolio] = annualize_factor * res.params['const']
        
        df_other.loc['r_squared', portfolio] = res.rsquared
        df_other.loc['model_implied_excess_ret', portfolio] = df_params[portfolio] @ _temp_excess_ret_stats['average']
        df_other.loc['ave_excess_ret', portfolio] = \
            annualize_factor * df[portfolio].mean()
        df_other.loc['std_excess_ret', portfolio] = \
            np.sqrt(annualize_factor) * df[portfolio].std()
        df_other.loc['skewness_excess_ret', portfolio] = \
            annualize_factor * df[portfolio].skew()
        df_other.loc['sharpe_ratio', portfolio] = \
            df_other.loc['ave_excess_ret', portfolio] / df_other.loc['std_excess_ret', portfolio]
        df_residuals[portfolio] = res.resid
        df_tstats[portfolio] = res.tvalues

    regression_outputs = RegressionsOutput(
        excess_ret_stats.T,
        df_params.T,
        df_residuals,
        df_tstats.T,
        df_other.T,
        df)


    return regression_outputs
df_hfs = pd.read_excel('proshares_analysis_data.xlsx', sheet_name = 'hedge_fund_series').set_index('date')
df_mf = pd.read_excel('proshares_analysis_data.xlsx', sheet_name = 'merrill_factors').set_index('date')

merrill_factors = ['SPY US Equity', 'USGG3M Index', 'EEM US Equity', 'EFA US Equity', 'EUO US Equity', 'IWM US Equity']

df_mf['HFRIFWI Index'] = df_hfs['HFRIFWI Index']

prob6_regs = lfm_time_series_regression(df=df_mf, portfolio_names=['HFRIFWI Index'], factors=merrill_factors)<jupyter_output><empty_output><jupyter_text>(a) Report the intercept and betas.<jupyter_code>display(prob6_regs.params)<jupyter_output><empty_output><jupyter_text>(b) Are the betas realistic position sizes, or do they require huge long-short positions?These betas are realistic position sizes. Only the USGG3M is short and that might not be allowed  (c) Report the R-squared. (d) Report the volatility of  E(merr), (the tracking error.)<jupyter_code>display(prob6_regs.other)

print('\nR-squared = {:.5f}'.format(np.array(prob6_regs.other['r_squared'])[0]))
print('Tracking error = {:.5f}'.format(np.array(prob6_regs.residuals.std() * np.sqrt(12))[0]))<jupyter_output><empty_output><jupyter_text>7. Let's examine the replication out-of-sample.<jupyter_code>date_range = df_mf.iloc[60:, :].index
oos_fitted = pd.Series(index = date_range, name = 'OOS_fit', dtype='float64')
for i in range(60, len(df_mf)):
    date = df_mf.iloc[i:i+1, :].index
    # date_month_prior = pd.DatetimeIndex([date]).shift(periods = -1, freq = 'M')[0]
    df_subset = df_mf.iloc[i-60:i, :]
    with warnings.catch_warnings():
        warnings.simplefilter("ignore")
        rhs = sm.add_constant(df_subset[merrill_factors])
    lhs = df_subset['HFRIFWI Index']
    res = sm.OLS(lhs, rhs, drop="missing").fit()
    alpha = res.params['const']
    beta = res.params.drop(index='const')
    x_t = df_mf.loc[date, merrill_factors]
    predicted_next_value = alpha + x_t @ beta
    oos_fitted[date] = predicted_next_value

oos_fitted.plot(figsize=(14,3))
df_mf.iloc[60:,:]['HFRIFWI Index'].plot()
plt.legend()
plt.show()
None

display((pd.DataFrame([oos_fitted, df_mf.iloc[60:,:]['HFRIFWI Index']])).T.corr())<jupyter_output><empty_output><jupyter_text>The OOS results perform well, showing almost the same level of replicability - 94.5% correlation between the replicating portfolio and the HFRI.  8. (a) regression beta<jupyter_code>from dataclasses import dataclass
import warnings
df_hfs = pd.read_excel('proshares_analysis_data.xlsx', sheet_name = 'hedge_fund_series').set_index('date')
df_mf = pd.read_excel('proshares_analysis_data.xlsx', sheet_name = 'merrill_factors').set_index('date')
# regression function
@dataclass
class RegressionsOutput:
    excess_ret_stats: pd.DataFrame
    params: pd.DataFrame
    residuals: pd.DataFrame
    tstats: pd.DataFrame
    other: pd.DataFrame
    df: pd.DataFrame

def lfm_time_series_regression(df, portfolio_names, factors, annualize_factor=12):
    excess_ret_stats = pd.DataFrame(index=factors, columns=['average', 'std'], dtype=float)
    for factor in factors:
        excess_ret_stats.loc[factor, 'average'] = annualize_factor * df[factor].mean()
        excess_ret_stats.loc[factor, 'std'] = np.sqrt(annualize_factor) * df[factor].std()
        excess_ret_stats.loc[factor, 'sharpe_ratio'] = \
            excess_ret_stats.loc[factor, 'average'] / excess_ret_stats.loc[factor, 'std']
        # Here I'll just report the unscaled skewness
        excess_ret_stats.loc[factor, 'skewness'] = df[factor].skew()
        # excess_ret_stats.loc[factor, 'skewness'] = annualize_factor * df[factor].skew()

    _temp_excess_ret_stats = excess_ret_stats.copy()
    _temp_excess_ret_stats.loc['const', :] = 0

    with warnings.catch_warnings():
        warnings.simplefilter("ignore")
        rhs = sm.add_constant(df[factors])
    df_params = pd.DataFrame(columns=portfolio_names)
    df_other = pd.DataFrame(columns=portfolio_names)
    df_residuals = pd.DataFrame(columns=portfolio_names)
    df_tstats = pd.DataFrame(columns=portfolio_names)
    for portfolio in portfolio_names:
        lhs = df[portfolio]
        res = sm.OLS(lhs, rhs, missing='drop').fit()
        df_params[portfolio] = res.params
        df_params.loc['const', portfolio] = 0
        
        df_other.loc['r_squared', portfolio] = res.rsquared
        df_other.loc['model_implied_excess_ret', portfolio] = df_params[portfolio] @ _temp_excess_ret_stats['average']
        df_other.loc['ave_excess_ret', portfolio] = \
            annualize_factor * df[portfolio].mean()
        df_other.loc['std_excess_ret', portfolio] = \
            np.sqrt(annualize_factor) * df[portfolio].std()
        df_other.loc['skewness_excess_ret', portfolio] = \
            annualize_factor * df[portfolio].skew()
        df_other.loc['sharpe_ratio', portfolio] = \
            df_other.loc['ave_excess_ret', portfolio] / df_other.loc['std_excess_ret', portfolio]
        df_residuals[portfolio] = res.resid
        df_tstats[portfolio] = res.tvalues

    regression_outputs = RegressionsOutput(
        excess_ret_stats.T,
        df_params.T,
        df_residuals,
        df_tstats.T,
        df_other.T,
        df)


    return regression_outputs
merrill_factors = ['SPY US Equity', 'USGG3M Index', 'EEM US Equity', 'EFA US Equity', 'EUO US Equity', 'IWM US Equity']

df_mf['HFRIFWI Index'] = df_hfs['HFRIFWI Index']

prob8_regs = lfm_time_series_regression(df=df_mf, portfolio_names=['HFRIFWI Index'], factors=merrill_factors)
display(prob8_regs.params)<jupyter_output><empty_output><jupyter_text>8(b) mean of fitted value  the mean of HFRI is<jupyter_code>print(df_hfs['HFRIFWI Index'].mean()*12)

a=df_mf['SPY US Equity'].mean()
b=df_mf['USGG3M Index'].mean()
c=df_mf['EEM US Equity'].mean()
d=df_mf['EFA US Equity'].mean()
e=df_mf['EUO US Equity'].mean()
f=df_mf['IWM US Equity'].mean()
mean_fitted=(0.072022*a-0.400591*b+0.072159*c+0.106318*d+0.022431*e+0.130892*f)*12<jupyter_output><empty_output><jupyter_text>the mean of fitted value is<jupyter_code>print(mean_fitted)<jupyter_output>0.037023779152482235<jupyter_text>so we can see that compared to mean of MFRI, mean of fitted value is much lower  8(c) The correlation  parameters without interercept<jupyter_code>prob8_regs.params<jupyter_output><empty_output><jupyter_text>parameters with interercept<jupyter_code>prob6_regs.params


a=df_mf['SPY US Equity']
b=df_mf['USGG3M Index']
c=df_mf['EEM US Equity']
d=df_mf['EFA US Equity']
e=df_mf['EUO US Equity']
f=df_mf['IWM US Equity']
k=[]
g=[]
t=[]

for i in range(len(a)):
    fitted_no_intece=0.072022*a[i]-0.400591*b[i]+0.072159*c[i]+0.106318*d[i]+0.022431*e[i]+0.130892*f[i]
    k.append(fitted_no_intece)
    fitted_intece=0.01376+0.072022*a[i]-0.400591*b[i]+0.072159*c[i]+0.106318*d[i]+0.022431*e[i]+0.130892*f[i]
    g.append(fitted_intece)
    t.append(df_hfs['HFRIFWI Index'][i])
df_corr = pd.DataFrame({'HFRIFWI Index':df_hfs['HFRIFWI Index'],'Predicted with intercept':g,'Predicted witout intercept':k})

df_corr.corr()[['HFRIFWI Index']]<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup