import pandas as pd
import numpy as np
import scipy as sp
import statsmodels.formula.api as smf

ret=pd.read_excel('#Asset_Return.xlsx','Sheet1')
vme=pd.read_excel('#VME.xlsx','Sheet1')
      
vme=vme[vme['YM']>=198601]                 
# =============================================================================
# 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']
  
tsm_tsh=pd.DataFrame()
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=500,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=pd.concat([tsm_tsh,ret_sub.dropna()])#drop the first 12 variables

ym=tsm_tsh['YM'].drop_duplicates().sort_values().reset_index(drop=True)

r_tsm_long=[];r_tsm_short=[];r_tsh_long=[];r_tsh_short=[]
for i in ym:
    strategy=tsm_tsh[tsm_tsh['YM']==i]
    strategy_tsm_long=strategy[strategy['SignExRet12']>=0]
    strategy_tsm_short=strategy[strategy['SignExRet12']<0]
    strategy_tsh_long=strategy[strategy['Signhistorical_mean']>=0]
    strategy_tsh_short=strategy[strategy['Signhistorical_mean']<0]
    length=len(strategy)
    r_tsm_long.append(strategy_tsm_long['TSM'].sum()/length)
    r_tsm_short.append(-strategy_tsm_short['TSM'].sum()/length)
    
    r_tsh_long.append(strategy_tsh_long['TSH'].sum()/length)
    r_tsh_short.append(-strategy_tsh_short['TSH'].sum()/length)

vme['r_tsm_long']=r_tsm_long
vme['r_tsm_short']=r_tsm_short
vme['r_tsm_longshort']=vme['r_tsm_long']-vme['r_tsm_short']
vme['r_tsh_long']=r_tsh_long
vme['r_tsh_short']=r_tsh_short
vme['r_tsh_longshort']=vme['r_tsh_long']-vme['r_tsh_short']


Table9=pd.DataFrame(index=['tsm_long','tsm_long_t','tsm_short','tsm_short_t','tsm_longshort','tsm_longshort_t','tsh_long','tsh_long_t','tsh_short','tsh_short_t','tsh_longshort','tsh_longshort_t'],columns=['Mean','Alpha1','MSCI1','SMB1','HML1','UMD1','Rsquared1','Alpha2','MSCI2','VAL2','MOM2','Rsquared2'])

Table9.loc[['tsm_long'],['Mean']]=vme['r_tsm_long'].mean()*100
Table9.loc[['tsm_long_t'],['Mean']]=sp.stats.ttest_ind(vme['r_tsm_long'], np.zeros(len(vme['r_tsm_long'])))[0]
Table9.loc[['tsm_short'],['Mean']]=vme['r_tsm_short'].mean()*100
Table9.loc[['tsm_short_t'],['Mean']]=sp.stats.ttest_ind(vme['r_tsm_short'], np.zeros(len(vme['r_tsm_short'])))[0]
Table9.loc[['tsm_longshort'],['Mean']]=vme['r_tsm_longshort'].mean()*100
Table9.loc[['tsm_longshort_t'],['Mean']]=sp.stats.ttest_ind(vme['r_tsm_longshort'], np.zeros(len(vme['r_tsm_longshort'])))[0]

Table9.loc[['tsh_long'],['Mean']]=vme['r_tsh_long'].mean()*100
Table9.loc[['tsh_long_t'],['Mean']]=sp.stats.ttest_ind(vme['r_tsh_long'], np.zeros(len(vme['r_tsh_long'])))[0]
Table9.loc[['tsh_short'],['Mean']]=vme['r_tsh_short'].mean()*100
Table9.loc[['tsh_short_t'],['Mean']]=sp.stats.ttest_ind(vme['r_tsh_short'], np.zeros(len(vme['r_tsh_short'])))[0]
Table9.loc[['tsh_longshort'],['Mean']]=vme['r_tsh_longshort'].mean()*100
Table9.loc[['tsh_longshort_t'],['Mean']]=sp.stats.ttest_ind(vme['r_tsh_longshort'], np.zeros(len(vme['r_tsh_longshort'])))[0]


result = smf.ols(formula="r_tsm_long ~ MSCI+SMB+HML+UMD", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsm_long'],['Alpha1']]=result.params[0]*100
Table9.loc[['tsm_long'],['MSCI1']]=result.params[1]
Table9.loc[['tsm_long'],['SMB1']]=result.params[2]
Table9.loc[['tsm_long'],['HML1']]=result.params[3]
Table9.loc[['tsm_long'],['UMD1']]=result.params[4]
Table9.loc[['tsm_long'],['Rsquared1']]=result.rsquared

Table9.loc[['tsm_long_t'],['Alpha1']]=result.tvalues[0]
Table9.loc[['tsm_long_t'],['MSCI1']]=result.tvalues[1]
Table9.loc[['tsm_long_t'],['SMB1']]=result.tvalues[2]
Table9.loc[['tsm_long_t'],['HML1']]=result.tvalues[3]
Table9.loc[['tsm_long_t'],['UMD1']]=result.tvalues[4]

result = smf.ols(formula="r_tsm_short ~ MSCI+SMB+HML+UMD", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsm_short'],['Alpha1']]=result.params[0]*100
Table9.loc[['tsm_short'],['MSCI1']]=result.params[1]
Table9.loc[['tsm_short'],['SMB1']]=result.params[2]
Table9.loc[['tsm_short'],['HML1']]=result.params[3]
Table9.loc[['tsm_short'],['UMD1']]=result.params[4]
Table9.loc[['tsm_short'],['Rsquared1']]=result.rsquared

Table9.loc[['tsm_short_t'],['Alpha1']]=result.tvalues[0]
Table9.loc[['tsm_short_t'],['MSCI1']]=result.tvalues[1]
Table9.loc[['tsm_short_t'],['SMB1']]=result.tvalues[2]
Table9.loc[['tsm_short_t'],['HML1']]=result.tvalues[3]
Table9.loc[['tsm_short_t'],['UMD1']]=result.tvalues[4]

result = smf.ols(formula="r_tsm_longshort ~ MSCI+SMB+HML+UMD", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsm_longshort'],['Alpha1']]=result.params[0]*100
Table9.loc[['tsm_longshort'],['MSCI1']]=result.params[1]
Table9.loc[['tsm_longshort'],['SMB1']]=result.params[2]
Table9.loc[['tsm_longshort'],['HML1']]=result.params[3]
Table9.loc[['tsm_longshort'],['UMD1']]=result.params[4]
Table9.loc[['tsm_longshort'],['Rsquared1']]=result.rsquared

Table9.loc[['tsm_longshort_t'],['Alpha1']]=result.tvalues[0]
Table9.loc[['tsm_longshort_t'],['MSCI1']]=result.tvalues[1]
Table9.loc[['tsm_longshort_t'],['SMB1']]=result.tvalues[2]
Table9.loc[['tsm_longshort_t'],['HML1']]=result.tvalues[3]
Table9.loc[['tsm_longshort_t'],['UMD1']]=result.tvalues[4]

tsm_alpha1=vme['r_tsm_longshort']-result.params[1]*vme['MSCI']-result.params[2]*vme['SMB']-result.params[3]*vme['HML']-result.params[4]*vme['UMD']

result = smf.ols(formula="r_tsh_long ~ MSCI+SMB+HML+UMD", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsh_long'],['Alpha1']]=result.params[0]*100
Table9.loc[['tsh_long'],['MSCI1']]=result.params[1]
Table9.loc[['tsh_long'],['SMB1']]=result.params[2]
Table9.loc[['tsh_long'],['HML1']]=result.params[3]
Table9.loc[['tsh_long'],['UMD1']]=result.params[4]
Table9.loc[['tsh_long'],['Rsquared1']]=result.rsquared

Table9.loc[['tsh_long_t'],['Alpha1']]=result.tvalues[0]
Table9.loc[['tsh_long_t'],['MSCI1']]=result.tvalues[1]
Table9.loc[['tsh_long_t'],['SMB1']]=result.tvalues[2]
Table9.loc[['tsh_long_t'],['HML1']]=result.tvalues[3]
Table9.loc[['tsh_long_t'],['UMD1']]=result.tvalues[4]

result = smf.ols(formula="r_tsh_short ~ MSCI+SMB+HML+UMD", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsh_short'],['Alpha1']]=result.params[0]*100
Table9.loc[['tsh_short'],['MSCI1']]=result.params[1]
Table9.loc[['tsh_short'],['SMB1']]=result.params[2]
Table9.loc[['tsh_short'],['HML1']]=result.params[3]
Table9.loc[['tsh_short'],['UMD1']]=result.params[4]
Table9.loc[['tsh_short'],['Rsquared1']]=result.rsquared

Table9.loc[['tsh_short_t'],['Alpha1']]=result.tvalues[0]
Table9.loc[['tsh_short_t'],['MSCI1']]=result.tvalues[1]
Table9.loc[['tsh_short_t'],['SMB1']]=result.tvalues[2]
Table9.loc[['tsh_short_t'],['HML1']]=result.tvalues[3]
Table9.loc[['tsh_short_t'],['UMD1']]=result.tvalues[4]

result = smf.ols(formula="r_tsh_longshort ~ MSCI+SMB+HML+UMD", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsh_longshort'],['Alpha1']]=result.params[0]*100
Table9.loc[['tsh_longshort'],['MSCI1']]=result.params[1]
Table9.loc[['tsh_longshort'],['SMB1']]=result.params[2]
Table9.loc[['tsh_longshort'],['HML1']]=result.params[3]
Table9.loc[['tsh_longshort'],['UMD1']]=result.params[4]
Table9.loc[['tsh_longshort'],['Rsquared1']]=result.rsquared

Table9.loc[['tsh_longshort_t'],['Alpha1']]=result.tvalues[0]
Table9.loc[['tsh_longshort_t'],['MSCI1']]=result.tvalues[1]
Table9.loc[['tsh_longshort_t'],['SMB1']]=result.tvalues[2]
Table9.loc[['tsh_longshort_t'],['HML1']]=result.tvalues[3]
Table9.loc[['tsh_longshort_t'],['UMD1']]=result.tvalues[4]

tsh_alpha1=vme['r_tsh_longshort']-result.params[1]*vme['MSCI']-result.params[2]*vme['SMB']-result.params[3]*vme['HML']-result.params[4]*vme['UMD']


result = smf.ols(formula="r_tsm_long ~ MSCI+VAL+MOM", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsm_long'],['Alpha2']]=result.params[0]*100
Table9.loc[['tsm_long'],['MSCI2']]=result.params[1]
Table9.loc[['tsm_long'],['VAL2']]=result.params[2]
Table9.loc[['tsm_long'],['MOM2']]=result.params[3]
Table9.loc[['tsm_long'],['Rsquared2']]=result.rsquared

Table9.loc[['tsm_long_t'],['Alpha2']]=result.tvalues[0]
Table9.loc[['tsm_long_t'],['MSCI2']]=result.tvalues[1]
Table9.loc[['tsm_long_t'],['VAL2']]=result.tvalues[2]
Table9.loc[['tsm_long_t'],['MOM2']]=result.tvalues[3]

result = smf.ols(formula="r_tsm_short ~ MSCI+VAL+MOM", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsm_short'],['Alpha2']]=result.params[0]*100
Table9.loc[['tsm_short'],['MSCI2']]=result.params[1]
Table9.loc[['tsm_short'],['VAL2']]=result.params[2]
Table9.loc[['tsm_short'],['MOM2']]=result.params[3]
Table9.loc[['tsm_short'],['Rsquared2']]=result.rsquared

Table9.loc[['tsm_short_t'],['Alpha2']]=result.tvalues[0]
Table9.loc[['tsm_short_t'],['MSCI2']]=result.tvalues[1]
Table9.loc[['tsm_short_t'],['VAL2']]=result.tvalues[2]
Table9.loc[['tsm_short_t'],['MOM2']]=result.tvalues[3]

result = smf.ols(formula="r_tsm_longshort ~ MSCI+VAL+MOM", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsm_longshort'],['Alpha2']]=result.params[0]*100
Table9.loc[['tsm_longshort'],['MSCI2']]=result.params[1]
Table9.loc[['tsm_longshort'],['VAL2']]=result.params[2]
Table9.loc[['tsm_longshort'],['MOM2']]=result.params[3]
Table9.loc[['tsm_longshort'],['Rsquared2']]=result.rsquared

Table9.loc[['tsm_longshort_t'],['Alpha2']]=result.tvalues[0]
Table9.loc[['tsm_longshort_t'],['MSCI2']]=result.tvalues[1]
Table9.loc[['tsm_longshort_t'],['VAL2']]=result.tvalues[2]
Table9.loc[['tsm_longshort_t'],['MOM2']]=result.tvalues[3]

tsm_alpha2=vme['r_tsm_longshort']-result.params[1]*vme['MSCI']-result.params[2]*vme['VAL']-result.params[3]*vme['MOM']


result = smf.ols(formula="r_tsh_long ~ MSCI+VAL+MOM", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsh_long'],['Alpha2']]=result.params[0]*100
Table9.loc[['tsh_long'],['MSCI2']]=result.params[1]
Table9.loc[['tsh_long'],['VAL2']]=result.params[2]
Table9.loc[['tsh_long'],['MOM2']]=result.params[3]
Table9.loc[['tsh_long'],['Rsquared2']]=result.rsquared

Table9.loc[['tsh_long_t'],['Alpha2']]=result.tvalues[0]
Table9.loc[['tsh_long_t'],['MSCI2']]=result.tvalues[1]
Table9.loc[['tsh_long_t'],['VAL2']]=result.tvalues[2]
Table9.loc[['tsh_long_t'],['MOM2']]=result.tvalues[3]

result = smf.ols(formula="r_tsh_short ~ MSCI+VAL+MOM", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsh_short'],['Alpha2']]=result.params[0]*100
Table9.loc[['tsh_short'],['MSCI2']]=result.params[1]
Table9.loc[['tsh_short'],['VAL2']]=result.params[2]
Table9.loc[['tsh_short'],['MOM2']]=result.params[3]
Table9.loc[['tsh_short'],['Rsquared2']]=result.rsquared

Table9.loc[['tsh_short_t'],['Alpha2']]=result.tvalues[0]
Table9.loc[['tsh_short_t'],['MSCI2']]=result.tvalues[1]
Table9.loc[['tsh_short_t'],['VAL2']]=result.tvalues[2]
Table9.loc[['tsh_short_t'],['MOM2']]=result.tvalues[3]

result = smf.ols(formula="r_tsh_longshort ~ MSCI+VAL+MOM", data=vme).fit(cov_type='HAC',cov_kwds={'maxlags':12})
Table9.loc[['tsh_longshort'],['Alpha2']]=result.params[0]*100
Table9.loc[['tsh_longshort'],['MSCI2']]=result.params[1]
Table9.loc[['tsh_longshort'],['VAL2']]=result.params[2]
Table9.loc[['tsh_longshort'],['MOM2']]=result.params[3]
Table9.loc[['tsh_longshort'],['Rsquared2']]=result.rsquared

Table9.loc[['tsh_longshort_t'],['Alpha2']]=result.tvalues[0]
Table9.loc[['tsh_longshort_t'],['MSCI2']]=result.tvalues[1]
Table9.loc[['tsh_longshort_t'],['VAL2']]=result.tvalues[2]
Table9.loc[['tsh_longshort_t'],['MOM2']]=result.tvalues[3]

tsh_alpha2=vme['r_tsh_longshort']-result.params[1]*vme['MSCI']-result.params[2]*vme['VAL']-result.params[3]*vme['MOM']

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