## 2 Analyzing the Data


```python
import numpy as np
import pandas as pd
import scipy.stats as st
import statsmodels.api as sm
import seaborn as sns
from statsmodels.regression.rolling import RollingOLS
import warnings
warnings.filterwarnings('ignore')
```


```python
df = pd.read_excel("proshares_analysis_data.xlsx",sheet_name="hedge_fund_series",index_col="date")
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>HFRIFWI Index</th>
      <th>MLEIFCTR Index</th>
      <th>MLEIFCTX Index</th>
      <th>HDG US Equity</th>
      <th>QAI US Equity</th>
    </tr>
    <tr>
      <th>date</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>2011-08-31</th>
      <td>-0.032149</td>
      <td>-0.025588</td>
      <td>-0.025689</td>
      <td>-0.027035</td>
      <td>-0.006491</td>
    </tr>
    <tr>
      <th>2011-09-30</th>
      <td>-0.038903</td>
      <td>-0.032414</td>
      <td>-0.032593</td>
      <td>-0.032466</td>
      <td>-0.022142</td>
    </tr>
    <tr>
      <th>2011-10-31</th>
      <td>0.026858</td>
      <td>0.043593</td>
      <td>0.043320</td>
      <td>0.050532</td>
      <td>0.025241</td>
    </tr>
    <tr>
      <th>2011-11-30</th>
      <td>-0.013453</td>
      <td>-0.012142</td>
      <td>-0.012431</td>
      <td>-0.028608</td>
      <td>-0.007965</td>
    </tr>
    <tr>
      <th>2011-12-31</th>
      <td>-0.004479</td>
      <td>0.001938</td>
      <td>0.001796</td>
      <td>0.012875</td>
      <td>0.001854</td>
    </tr>
  </tbody>
</table>
</div>



### 1. For the series in the “hedge fund series” tab, report the following summary statistics:<br>
(a) mean <br>
(b) volatility <br>
(c) Sharpe ratio <br>
Annualize these statistics.


```python
def summary(df):
    '''
    Given a pandas DataFrame of time series with monthly data, returns annualized mean, volatility and Sharpe Ratio
    '''
    mean = df.mean()*12
    volatility = df.std()*np.sqrt(12)
    out = pd.DataFrame({'mean':mean,'volatility':volatility})
    out['SharpeRatio'] = out['mean']/out['volatility']
    return out
summary(df)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>mean</th>
      <th>volatility</th>
      <th>SharpeRatio</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>HFRIFWI Index</th>
      <td>0.050784</td>
      <td>0.061507</td>
      <td>0.825665</td>
    </tr>
    <tr>
      <th>MLEIFCTR Index</th>
      <td>0.038821</td>
      <td>0.053848</td>
      <td>0.720933</td>
    </tr>
    <tr>
      <th>MLEIFCTX Index</th>
      <td>0.037330</td>
      <td>0.053682</td>
      <td>0.695382</td>
    </tr>
    <tr>
      <th>HDG US Equity</th>
      <td>0.028100</td>
      <td>0.056380</td>
      <td>0.498407</td>
    </tr>
    <tr>
      <th>QAI US Equity</th>
      <td>0.025491</td>
      <td>0.045484</td>
      <td>0.560434</td>
    </tr>
  </tbody>
</table>
</div>



### 2. For the series in the “hedge fund series” tab, , calculate the following statistics related to tailrisk. <br>
(a) Skewness<br>
(b) Excess Kurtosis (in excess of 3)<br>
(c) VaR (.05) - the fifth quantile of historic returns<br>
(d) CVaR (.05) - the mean of the returns at or below the fifth quantile<br>
(e) Maximum drawdown - include the dates of the max/min/recovery within the max drawdown period.<br>

There is no need to annualize any of these statistics


```python
def tailRisks(df):
    '''
    Given a pandas DataFrame of time series, returns Skewness, Excess Kurtosis, 5% VaR & CVar, Maximum Drawdown
    '''
        
    sk = df.skew()
    #kt = st.kurtosis(df,fisher=True)
    kt = df.kurtosis()
    VaR = df.quantile(0.05)
    CVaR = df[df < VaR].mean()

    levels = (1+df).cumprod()
    high = levels.cummax()
    drawdown = (levels - high)/high
    maxDrawdown = drawdown.min()
    
    bottom = pd.Series(pd.to_datetime([drawdown[col].idxmin() for col in drawdown]),index=df.columns)
    peak = pd.Series(pd.to_datetime([(levels[col][:bottom[col]].idxmax()) for col in levels]),index=df.columns)
    
    peakLevels = pd.Series([levels[col].loc[peak.loc[col]] for col in levels],index=df.columns)
    

    recovered = []
    for col in levels:
        for lev in levels[col][bottom[col]:]:
            if lev >= peakLevels[col]:
                recovered.append(levels.index[levels[col] == lev][0])
                break

    recovered = pd.Series(recovered,index=df.columns)
    
    drawdown.plot(figsize=(9,6), title = 'Drawdown')    
    

    return pd.DataFrame({'Skewness':sk,'Excess Kurtosis':kt,'5% VaR':VaR,'5% CVaR':CVaR,'Max Drawdown':maxDrawdown,\
                        'Peak':peak,'Bottom':bottom,'Recovered':recovered})
   
    
tailRisks(df)    
```

## 3. For the series in the “hedge fund series” tab, run a regression of each against SPY (found in the “merrill factors” tab.) Include an intercept. Report the following regression-based statistics:

(a) Market Beta

(b) Treynor Ratio

(c) Information ratio

Annualize these three statistics as appropriate.


```python
merrill_factors = pd.read_excel("proshares_analysis_data.xlsx",sheet_name= "merrill_factors",index_col="date")
spy = merrill_factors['SPY US Equity']
merrill_factors.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>SPY US Equity</th>
      <th>USGG3M Index</th>
      <th>EEM US Equity</th>
      <th>EFA US Equity</th>
      <th>EUO US Equity</th>
      <th>IWM US Equity</th>
    </tr>
    <tr>
      <th>date</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>2011-08-31</th>
      <td>-0.054976</td>
      <td>0.000009</td>
      <td>-0.092549</td>
      <td>-0.087549</td>
      <td>-0.005889</td>
      <td>-0.088913</td>
    </tr>
    <tr>
      <th>2011-09-30</th>
      <td>-0.069449</td>
      <td>0.000017</td>
      <td>-0.179064</td>
      <td>-0.108083</td>
      <td>0.142180</td>
      <td>-0.111541</td>
    </tr>
    <tr>
      <th>2011-10-31</th>
      <td>0.109147</td>
      <td>-0.000013</td>
      <td>0.162986</td>
      <td>0.096275</td>
      <td>-0.069502</td>
      <td>0.151012</td>
    </tr>
    <tr>
      <th>2011-11-30</th>
      <td>-0.004064</td>
      <td>0.000000</td>
      <td>-0.019723</td>
      <td>-0.021764</td>
      <td>0.054627</td>
      <td>-0.003783</td>
    </tr>
    <tr>
      <th>2011-12-31</th>
      <td>0.010440</td>
      <td>0.000009</td>
      <td>-0.043017</td>
      <td>-0.022139</td>
      <td>0.075581</td>
      <td>0.005114</td>
    </tr>
  </tbody>
</table>
</div>




```python
def performanceMeasures(seriesY,seriesX):
    
    mean =seriesY.mean()*12
    sharpe = mean/(seriesY.std()*(12**0.5))
    model = sm.OLS(seriesY,sm.add_constant(seriesX)).fit()
    rsq = model.rsquared
    beta = model.params[1]
    treynor = mean/beta
    information = model.params[0]/(model.resid.std()*np.sqrt(12))
    
    return pd.DataFrame({'Mean Return':mean,'Sharpe Ratio':sharpe,'R Squared':rsq, 'Beta':beta,\
                         'Treynor Ratio':treynor, 'Information Ratio':information},index= [seriesY.name])
```


```python
frames = []
for col in df:
    p = performanceMeasures(df[col],spy)
    frames.append(p) 
pd.concat(frames)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Mean Return</th>
      <th>Sharpe Ratio</th>
      <th>R Squared</th>
      <th>Beta</th>
      <th>Treynor Ratio</th>
      <th>Information Ratio</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>HFRIFWI Index</th>
      <td>0.050784</td>
      <td>0.825665</td>
      <td>0.753090</td>
      <td>0.394320</td>
      <td>0.128789</td>
      <td>-0.020169</td>
    </tr>
    <tr>
      <th>MLEIFCTR Index</th>
      <td>0.038821</td>
      <td>0.720933</td>
      <td>0.816158</td>
      <td>0.359382</td>
      <td>0.108021</td>
      <td>-0.051272</td>
    </tr>
    <tr>
      <th>MLEIFCTX Index</th>
      <td>0.037330</td>
      <td>0.695382</td>
      <td>0.815047</td>
      <td>0.358034</td>
      <td>0.104263</td>
      <td>-0.055939</td>
    </tr>
    <tr>
      <th>HDG US Equity</th>
      <td>0.028100</td>
      <td>0.498407</td>
      <td>0.785733</td>
      <td>0.369199</td>
      <td>0.076111</td>
      <td>-0.084218</td>
    </tr>
    <tr>
      <th>QAI US Equity</th>
      <td>0.025491</td>
      <td>0.560434</td>
      <td>0.719379</td>
      <td>0.284993</td>
      <td>0.089443</td>
      <td>-0.057274</td>
    </tr>
  </tbody>
</table>
</div>



### 4. Relative Performance

Discuss the previous statistics, and what they tell us about...

(a) the differences between SPY and the hedge-fund series?

The large part of most Hedge Fund series can be explained using SPY as the R Squared from Regrssening Hedge Fund series on SPY high (greater than 70% for each series. 

(b) which performs better between HDG and QAI.

HDG has higher Mean Returns than QAI but has lower Sharpe Ratio, Treynor Ratio and Information Ratios. Thus higher returns have come with higher volaility in HDG. From a mean variance optimized standpoint, QAI has performed better.

(c) whether HDG and the ML series capture the most notable properties of HFRI.

We note (from question 5) that both HDG and ML are highly correlated with HFRI with a correlation of about 90%. However, HFRI's excess kurtosis of 6.73 is substantially higher than the excess kurtosis of HDG and ML (about 2.4-2.5). Thus HDG and ML would fail to capture the very high tail-risk of HFRI. <br>
Furhter, HDG and ML series are highly correlated (~98% correlation). Regressing HFRI on HDG and ML together would thus suffer from a high multi-collinearity.

## 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?


```python
correlations = df.corr()
correlations
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>HFRIFWI Index</th>
      <th>MLEIFCTR Index</th>
      <th>MLEIFCTX Index</th>
      <th>HDG US Equity</th>
      <th>QAI US Equity</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>HFRIFWI Index</th>
      <td>1.000000</td>
      <td>0.910392</td>
      <td>0.910063</td>
      <td>0.897653</td>
      <td>0.866714</td>
    </tr>
    <tr>
      <th>MLEIFCTR Index</th>
      <td>0.910392</td>
      <td>1.000000</td>
      <td>0.999939</td>
      <td>0.984621</td>
      <td>0.864431</td>
    </tr>
    <tr>
      <th>MLEIFCTX Index</th>
      <td>0.910063</td>
      <td>0.999939</td>
      <td>1.000000</td>
      <td>0.984471</td>
      <td>0.863734</td>
    </tr>
    <tr>
      <th>HDG US Equity</th>
      <td>0.897653</td>
      <td>0.984621</td>
      <td>0.984471</td>
      <td>1.000000</td>
      <td>0.847597</td>
    </tr>
    <tr>
      <th>QAI US Equity</th>
      <td>0.866714</td>
      <td>0.864431</td>
      <td>0.863734</td>
      <td>0.847597</td>
      <td>1.000000</td>
    </tr>
  </tbody>
</table>
</div>




```python
sns.heatmap(correlations)
```


```python
def findHighestLowestCorrelations(correlationsMatrix):
    high, low = [],[]
    for col in correlationsMatrix.columns:

        val_high = correlationsMatrix[col][(correlationsMatrix[col] == correlationsMatrix[col].nlargest(2).iloc[1])]
        high.append((val_high.index[0],val_high.name,val_high[0]))

        val_low = correlationsMatrix[col][(correlationsMatrix[col] == min(correlationsMatrix[col]))]
        low.append((val_low.index[0],val_low.name,val_low[0]))

    print("Highest correlation pair = ",max(high,key = lambda x:x[2]))
    print("Lowest correlation pair = ",min(low,key = lambda x:x[2]))
    
findHighestLowestCorrelations(correlations)
```

    Highest correlation pair =  ('MLEIFCTX Index', 'MLEIFCTR Index', 0.9999391791142366)
    Lowest correlation pair =  ('QAI US Equity', 'HDG US Equity', 0.8475965688236916)


## 6. Replicate HFRI with the six factors listed on the “merrill factors” 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}


Note that the second equation is just our notation for the fitted replication.

(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.)


```python
model = sm.OLS(df['HFRIFWI Index'],sm.add_constant(merrill_factors)).fit()
model.params
```




    const            0.001147
    SPY US Equity    0.072022
    USGG3M Index    -0.400591
    EEM US Equity    0.072159
    EFA US Equity    0.106318
    EUO US Equity    0.022431
    IWM US Equity    0.130892
    dtype: float64



The Betas are realistic position sizes except for USGG3M Index


```python
print("R-squared of the Model =",round(model.rsquared,6))
```

    R-squared of the Model = 0.855695



```python
print("Volatility of Error term (annualized) =",round(model.resid.std()*(12**0.5),6))
```

    Volatility of Error term (annualized) = 0.023365


### 7. Let’s examine the replication out-of-sample.
Starting with t = 61 month of the sample, do the following:

• Use the previous 60 months of data to estimate the regression equation, (1). This gives time-t estimates of the regression parameters, $\tilde \alpha^{merr}_{t}$ and $\tilde \beta^{merr}_{t}$

• Use the estimated regression parameters, along with the time-t regressor values, $x^{merr}_{t}$, to calculate the time-t replication value that is, with respect to the regression estimate, built

\begin{align}
\tilde r^{merr}_{t} \equiv \tilde \alpha^{merr}_{t} + (x^{merr}_{t})\prime \tilde \beta^{merr}_{t}
\end{align}

• Step forward to t = 62, and now use t = 2 through t = 61 for the estimation. Re-run the
steps above, and continue this process throughout the data series. Thus, we are running a
rolling, 60-month regression for each point-in-time.
How well does the out-of-sample replication perform with respect to the target?


```python
def get_est_coef(Y, X):

    X_add_const = sm.add_constant(X)

    model = sm.OLS(Y, X_add_const)

    res = model.fit()

    return res.params[0], res.params[1:]



fitted_values = []
window_size = 60



for i in range(df.shape[0] - window_size - 1):

    alpha, beta = get_est_coef(df['HFRIFWI Index'][i : i+window_size], merrill_factors[i : i+window_size])

    fitted_values.append((alpha + np.dot(merrill_factors[i+window_size+1 : i+window_size+2], beta).item()))
    
table_7 = pd.DataFrame({'True Values': df['HFRIFWI Index'][61:], 'Fitted Values': fitted_values})

table_7.plot(figsize=(13,8))

```


```python
table_7.corr()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>True Values</th>
      <th>Fitted Values</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>True Values</th>
      <td>1.000000</td>
      <td>0.943363</td>
    </tr>
    <tr>
      <th>Fitted Values</th>
      <td>0.943363</td>
      <td>1.000000</td>
    </tr>
  </tbody>
</table>
</div>



We observe that the predicted value is 94.34% correlated with actual performance. Thus out-of-sample replication performs very well. The same can also be visualized in the chart below:

## 8. We estimated the replications using an intercept. Try the full-sample estimation, but this time without an intercept.

 \begin{align}
  r^{hfri}_{t} = x^{merr}_{t} \beta^{merr} + \epsilon^{merr}_{t}
  \end{align}

  \begin{align}
  \check r^{hfri}_{t} = x^{merr}_{t} \check \beta^{merr}
  \end{align}

Report

(a) the regression beta. How does it compare to the estimated beta with an intercept, $\hat \beta^{merr}$?



```python
model_without_intercept = sm.OLS(df['HFRIFWI Index'],merrill_factors).fit()
regressionBetas = pd.DataFrame({"With Intercept":model.params,"Without Intercept":model_without_intercept.params})
regressionBetas
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>With Intercept</th>
      <th>Without Intercept</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>EEM US Equity</th>
      <td>0.072159</td>
      <td>0.069936</td>
    </tr>
    <tr>
      <th>EFA US Equity</th>
      <td>0.106318</td>
      <td>0.101524</td>
    </tr>
    <tr>
      <th>EUO US Equity</th>
      <td>0.022431</td>
      <td>0.023479</td>
    </tr>
    <tr>
      <th>IWM US Equity</th>
      <td>0.130892</td>
      <td>0.128999</td>
    </tr>
    <tr>
      <th>SPY US Equity</th>
      <td>0.072022</td>
      <td>0.087505</td>
    </tr>
    <tr>
      <th>USGG3M Index</th>
      <td>-0.400591</td>
      <td>0.334503</td>
    </tr>
    <tr>
      <th>const</th>
      <td>0.001147</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
</div>



The Regression Betas are mostly similar in both the regressions except for USGG3M Index Beta. In the absence of intercept, there is a much higher weightage of USGG3M which could be thought of as a proxy of Risk Free Interest Rates

(b) the mean of the fitted value, $\check r^{hfri}_{t}$. How does it compare to the mean of the HFRI?


```python
pd.Series([df['HFRIFWI Index'].mean()*12,model_without_intercept.predict(merrill_factors).mean()*12],\
          index=['Mean of HFRI','Mean of Fitted Values'])
```




    Mean of HFRI             0.050784
    Mean of Fitted Values    0.042888
    dtype: float64



The mean of the Fitted Values is lower than the mean of HFRI

(c) the correlations of the fitted values, $\check r^{hfri}_{t}$ to the HFRI. How does the correlation compare to that of the fitted values with an intercept, $\hat r^{hfri}_{t}$?


```python
HFRIpredictions = pd.DataFrame({'HFRI Index':df['HFRIFWI Index'],\
                                'Predict with intercept':model.predict(sm.add_constant(merrill_factors)),\
                               'Predict without intercept':model_without_intercept.predict(merrill_factors)})
HFRIpredictions.corr()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>HFRI Index</th>
      <th>Predict with intercept</th>
      <th>Predict without intercept</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>HFRI Index</th>
      <td>1.000000</td>
      <td>0.925038</td>
      <td>0.924517</td>
    </tr>
    <tr>
      <th>Predict with intercept</th>
      <td>0.925038</td>
      <td>1.000000</td>
      <td>0.999437</td>
    </tr>
    <tr>
      <th>Predict without intercept</th>
      <td>0.924517</td>
      <td>0.999437</td>
      <td>1.000000</td>
    </tr>
  </tbody>
</table>
</div>



The correlations of HFRI in both the cases is almost equally good. 92.45% correlatation between HFRI Index and Prediction without intercept. 92.50% correlation between HFRI and prediction with intercept. The predicted values with and without intercept are highly correlated. Thusincluding the intercept or not doesn't affect much for the prediction. If including the intercept, the intercept part would be non-tradable asset. Without intercept, it's in accordance with our purpose of replicating(using all tradable assets).

Do you think Merrill and ProShares fit their replicators with an intercept or not?

We think Merrill and ProShares would use the replicators <u><b> without the Intercept </u></b> as the purpose is to replicate the entire HFRI Index and not just mimic the returns from HFRI Index.

32da937a0a5b98619df59ec813d30a0a4344916f
ipynb
Jupyter Notebook
solutions/hw2/FINM36700_HW2_GroupA11v3.ipynb
tulyu96/finm-portfolio-2021
6b26e235323064f2bf0a5bbc81f922b2150b75ef
MIT
solutions/hw2/FINM36700_HW2_GroupA11v3.ipynb
tulyu96/finm-portfolio-2021
6b26e235323064f2bf0a5bbc81f922b2150b75ef
MIT
solutions/hw2/FINM36700_HW2_GroupA11v3.ipynb
tulyu96/finm-portfolio-2021
6b26e235323064f2bf0a5bbc81f922b2150b75ef
MIT
Qwen/Qwen-72B
1. YES
2. YES