import pandas as pd
import numpy as np

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']

Table7=pd.DataFrame(columns=['epTest'],index=np.arange(1,56))

retCum_summary=pd.DataFrame()
for i in range(1,56):
    ret_sub=ret[ret['ID']==i]
    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['ExRet1_f1']=ret_sub['ExRet1'].shift(-1)
    epTest=ret_sub[['ExRet1_f1','SignExRet12']].dropna()
    t=len(epTest)
    At=np.mean(epTest['ExRet1_f1']*epTest['SignExRet12'])
    Bt=(np.mean(epTest['ExRet1_f1']))*(np.mean(epTest['SignExRet12']))
    p=0.5*(1+np.mean(epTest['SignExRet12']))
    Vep=4*(t-1)/(t**2)*p*(1-p)*np.var(epTest['ExRet1_f1'])
    Ep=(At-Bt)/np.sqrt(Vep)
    Table7.loc[[i],['epTest']]=Ep

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