import pandas as pd
import numpy as np
import scipy as sp

ret=pd.read_excel('#Asset_Return.xlsx','Sheet1')
ret=ret[ret['YM']>=198501]# make sure all returns are after 198501

# =============================================================================
# define the equity premium for different horizons
# =============================================================================
ret['RF']=ret['Rf']+1#capital RF is the gross risk free rate
ret['Return']=ret['ExRet']+ret['RF']
#equity premium for one month is the same as before
ret['ExRet1']=ret['ExRet']

Table8=pd.DataFrame(columns=['TSM_return','TSH_return','TSM_SR','TSH_SR','Return_Difference','p_return','SR_Difference','p_SR'],index=np.arange(1,56))

for i in range(1,56):
    ret_sub=ret[ret['ID']==i]
    ret_sub['ExRet1_f1']=ret_sub['ExRet1'].shift(-1)
    ret_sub['ExRet12']=(pd.rolling_apply(ret_sub['Return'],12,lambda x : x.prod())-pd.rolling_apply(ret_sub['RF'],12,lambda x : x.prod()))
    ret_sub['SignExRet12']=np.where(ret_sub['ExRet12']>=0,1,np.where(ret_sub['ExRet12']<0,-1,np.nan))
    ret_sub['historical_mean']=ret_sub['ExRet1'].rolling(window=400,min_periods=1).mean()
    ret_sub['Signhistorical_mean']=np.where(ret_sub['historical_mean']>=0,1,np.where(ret_sub['historical_mean']<0,-1,np.nan))
    ret_sub['TSM']=ret_sub['SignExRet12']*ret_sub['ExRet1_f1']
    ret_sub['TSH']=ret_sub['Signhistorical_mean']*ret_sub['ExRet1_f1']
    tsm_tsh=ret_sub[['TSM','TSH']].dropna()
    
    mu_tsm=tsm_tsh['TSM'].mean()
    mu_tsh=tsm_tsh['TSH'].mean()
    std_tsm=tsm_tsh['TSM'].std()
    std_tsh=tsm_tsh['TSH'].std()
    covar=np.cov(tsm_tsh['TSM'],tsm_tsh['TSH'])[0,1]
    t=len(tsm_tsh)
    v_tsm_tsh=1/t*(2*std_tsm**2*std_tsh**2-2*std_tsm*std_tsh*covar+0.5*mu_tsm**2*std_tsh**2+0.5*mu_tsh**2*std_tsm**2-mu_tsm*mu_tsh*covar**2/(std_tsm*std_tsh))
     
    Table8.loc[[i],['TSM_return']]=mu_tsm*100
    Table8.loc[[i],['TSH_return']]=mu_tsh*100
    Table8.loc[[i],['TSM_SR']]=mu_tsm/std_tsm
    Table8.loc[[i],['TSH_SR']]=mu_tsh/std_tsh
    Table8.loc[[i],['Return_Difference']]=(mu_tsm-mu_tsh)*100
    Table8.loc[[i],['p_return']]=sp.stats.ttest_ind(tsm_tsh['TSM'],tsm_tsh['TSH'])[1]
    Table8.loc[[i],['SR_Difference']]=tsm_tsh['TSM'].mean()/tsm_tsh['TSM'].std()-tsm_tsh['TSH'].mean()/tsm_tsh['TSH'].std()
    Table8.loc[[i],['p_SR']]=(std_tsh*mu_tsm-std_tsm*mu_tsh)/np.sqrt(v_tsm_tsh)
    

writer = pd.ExcelWriter('Table8.xlsx')
pd.DataFrame.to_excel(Table8,writer,'Table8.xlsx',startrow=0, startcol=0)  