In [2]:
import numpy as np
import pandas as pd
import datetime as dt
pd.set_option('display.max_rows', 16)
import matplotlib.pyplot as plt
%matplotlib inline
plt.rcParams['figure.figsize'] = (16.0, 9.0)
import seaborn as sns
import statsmodels.api as sm
from sklearn.linear_model import LinearRegression
import gc
In [3]:
plt.rcParams['figure.figsize'] = (16.0, 9.0)
数据处理¶
财务数据¶
财务数据处理的难点在于“报表数据所处的时间”、“报表报告的时间”、“报表修改时间”带来的复杂性。两种处理方式比较合理：
- 预留充足的时间以便在使用报表数据的时间点上，报表数据是可用的（但不一定是最新的）
- 无论在哪个时间点上使用报表数据，都只用最新的数据(point-in-time)
交易数据¶
停牌¶
- 停牌在某些时候是可以不处理的，比如计算动量的时候，停牌之后的价格和停牌前的价格计算收益率，可以作为动量的一种衡量
- 但在有的时候，停牌不处理可能会有问题。
- 比如计算beta，市场收益率每个交易日都是有的，但个股停牌的时候没有，此时如果设为0，直接回归会有大的偏差
- 另外比如计算波动率，如果设为0，也有问题
- 从收益率的角度看，如果我们关注点是月收益率，也应当去掉，因为停牌的股票无法交易，也无法调仓
- 我们把停牌超过一个月的观测值删去
Data¶
In [4]:
START = '2007-01-01'
END = '2023-03-31'
In [5]:
# Security Id
stk_info = DataAPI.SecIDGet(assetClass="E",pandas="1")
cond1 = (stk_info['exchangeCD'] == 'XSHE') | (stk_info['exchangeCD'] == 'XSHG')
cond2 = (stk_info['listStatusCD'] == 'L') | (stk_info['listStatusCD'] == 'DE')
stk_info = stk_info[cond1 & cond2].copy()
stk_id = stk_info['secID'].unique()
In [6]:
stk_info
Out[6]:
In [7]:
len(stk_id)
Out[7]:
ST¶
In [8]:
st_df = DataAPI.SecSTGet(beginDate=START,endDate=END,secID=stk_id,field=['secID','tradeDate','STflg'],pandas="1")
In [9]:
st_df.info()
In [10]:
st_df
Out[10]:
In [11]:
st_df['tradeDate'] = pd.to_datetime(st_df['tradeDate'],format="%Y-%m-%d")
Book value¶
Book/Market ratio, 简称BM，也即价值因子，反映了公司的账面价值和市值的比值。Fama French (1993) 发现估值低(BM高)的股票和高的相比，预期收益为正。
BM ratio Fama-French(1993) 原文的构造方法：
- 每年的12月底的 book equity
- 每年12月最后一个交易日的mktcap
- 上述二者相除，得到 BM ratio
- 这个 BM ratio 作为下一年6月至下下一年5月的 portfolio 的 sorting variable
处理思路：
- 优矿的数据有发布日期，数据日期
- 这里book value比较简单，只取年报数据，也就是“数据日期”都是12月
- 取发布日期最晚，也就是最新的（也许年报和1季报中数据不同，或者年报发布后马上有更改），但不晚于次年6月
In [12]:
# fundmen_df = DataAPI.FdmtBSGet(secID=stk_id,reportType="A",beginDate=START,endDate=END,publishDateEnd=u"",publishDateBegin=u"",endDateRep="",beginDateRep="",beginYear="",endYear="",fiscalPeriod="",field=["secID","publishDate","endDate","endDateRep","actPubtime","fiscalPeriod","TShEquity","TEquityAttrP","minorityInt"],pandas="1")
# fundmen_df.to_pickle('./data/fundmen_df.pkl')
In [13]:
fundmen_df = pd.read_pickle('./data/fundmen_df.pkl')
In [14]:
fundmen_df
Out[14]:
In [15]:
fundmen_df.info()
In [16]:
fundmen_df['fiscalPeriod'].unique()
Out[16]:
In [17]:
fundmen_df[(fundmen_df['secID']=='000001.XSHE') & (fundmen_df['endDate']=='2019-12-31')]
Out[17]:
In [18]:
fundmen_df[(fundmen_df['secID']=='300720.XSHE') & (fundmen_df['endDate']=='2019-12-31')]
Out[18]:
In [19]:
fundmen_df[(fundmen_df['secID'] == '300720.XSHE') & (fundmen_df['endDate']=='2018-12-31')]
Out[19]:
publishDate: 实际公告日期endDate：数值所在日期endDateRep：数值所在报表日期。03-31是一季报，06-30是半年报，09-30是三季报，12-31是年报。后面的报表可能会对初始值做修改。
比如，300720.XSHE在2020-04-24公布了数据截止至2019-12-31的报告，里面包含了数据截止至2018-12-31的报表数据。
300720.XSHE在2019-08-28公布了数据截止至2019-06-30的报告，里面包含了数据截止至2018-12-31的报表数据。
在t年6月分组时，应当取最新更新过的t-1年12月31日的Book数值。
In [20]:
fundmen_df.drop(['actPubtime','fiscalPeriod'],axis=1, inplace=True)
In [21]:
fundmen_df[['publishDate','endDate']] = fundmen_df[['publishDate','endDate']].apply(pd.to_datetime)
In [22]:
fundmen_df['pub_month'] = fundmen_df['publishDate'].dt.month
fundmen_df['pub_year'] = fundmen_df['publishDate'].dt.year
fundmen_df['data_year'] = fundmen_df['endDate'].dt.year
In [23]:
fundmen_df
Out[23]:
In [24]:
fundmen_df[(fundmen_df['secID']=='300720.XSHE') & (fundmen_df['endDate']=='2019-12-31')]
Out[24]:
In [25]:
fundmen_df['pub_year'] - fundmen_df['data_year']
Out[25]:
In [26]:
fundmen_df
Out[26]:
In [27]:
# 每年6月底计算时，只能看到publishDate在6月之前的数值。
# 取 endDate 相同时，publishDate 最晚（但小于等于6）的那个数值
# 同时pub_year - data_year 不能大于1
fundmen_df['pub_month'] = fundmen_df['publishDate'].dt.month
fundmen_df['pub_year'] = fundmen_df['publishDate'].dt.year
fundmen_df['data_year'] = fundmen_df['endDate'].dt.year
fundmen_df = fundmen_df[fundmen_df['pub_year'] - fundmen_df['data_year'] == 1]
fundmen_df = fundmen_df[fundmen_df['pub_month'] <= 6]
fundmen_df.sort_values(['secID','endDate','publishDate'],inplace=True)
In [28]:
fundmen_df[(fundmen_df['secID']=='300720.XSHE') & (fundmen_df['endDate']=='2019-12-31')]
Out[28]:
In [29]:
fundmen_df = fundmen_df.groupby(['secID','endDate'],as_index=False).first()
In [30]:
fundmen_df['bm_date'] = fundmen_df['endDate'].dt.to_period('M')
In [31]:
fundmen_df
Out[31]:
In [32]:
fundmen_df.fillna(0,inplace=True)
In [33]:
# fundmen_df['book'] = fundmen_df['TShEquity'] - fundmen_df['minorityInt']
fundmen_df['book'] = fundmen_df['TEquityAttrP']
In [34]:
fundmen_df = fundmen_df[fundmen_df['book'] > 0]
In [35]:
fundmen_df
Out[35]:
In [36]:
np.allclose(fundmen_df['book'],fundmen_df['TEquityAttrP'])
Out[36]:
In [37]:
fundmen_df[fundmen_df['book']-fundmen_df['TEquityAttrP'] > 10]
Out[37]:
Risk free rate¶
In [38]:
pd.read_csv("./data/rf-monthly.csv")
Out[38]:
In [39]:
rf = pd.read_csv("./data/rf-monthly.csv").drop(["Unnamed: 4", "年份()_Year", "月份_Month"],axis=1)
rf.columns = ['Date', 'rf']
rf['Date'] = pd.to_datetime(rf["Date"])
rf['Date'] = rf['Date'].dt.to_period('M')
rf.rename(columns={'Date':'ym'},inplace=True)
In [40]:
rf
Out[40]:
Beta¶
In [41]:
beta_df = pd.read_pickle('./data/beta_df.pkl')
beta_df
beta_df['tradeDate'] = pd.to_datetime(beta_df['tradeDate'], format="%Y-%m-%d")
beta_df['ym'] = beta_df['tradeDate'].dt.to_period('M')
beta_df[['Beta60','Beta120','Beta252']] = beta_df[['Beta60','Beta120','Beta252']].apply(pd.to_numeric)
In [42]:
# # Winsorization
# up_q = 0.99999
# lower_q = 0.00001
# beta_df['Beta60_winsor'] = beta_df['Beta60'].clip(lower=beta_df['Beta60'].quantile(lower_q),upper=beta_df['Beta60'].quantile(up_q))
# beta_df['Beta120_winsor'] = beta_df['Beta120'].clip(lower=beta_df['Beta120'].quantile(lower_q),upper=beta_df['Beta120'].quantile(up_q))
Monthly beta¶
In [43]:
beta_m_df = beta_df.groupby(['secID','ym'],as_index=False)['Beta252'].last()
beta_m_df.rename(columns={'Beta252':'beta'},inplace=True)
beta_m_df
Out[43]:
Trading data¶
In [44]:
# stk_df = DataAPI.MktEqudAdjAfGet(secID=stk_id,beginDate=START,endDate=END,isOpen=1,
# field=["secID","tradeDate",
# "closePrice",
# "negMarketValue"],pandas="1")
# stk_df.to_pickle('./data/stk_df.pkl')
In [45]:
stk_df = pd.read_pickle('./data/stk_df.pkl')
stk_df['closePrice'] = pd.to_numeric(stk_df['closePrice'])
stk_df['negMarketValue'] = pd.to_numeric(stk_df['negMarketValue'])
stk_df['tradeDate'] = pd.to_datetime(stk_df['tradeDate'], format='%Y-%m-%d')
stk_df['ym'] = stk_df['tradeDate'].dt.to_period('M')
stk_df.sort_values(['secID','tradeDate'],inplace=True)
In [46]:
stk_df.info()
Exclude ST¶
In [47]:
stk_df.dropna().shape
Out[47]:
In [48]:
stk_df.shape
Out[48]:
In [49]:
stk_df = pd.merge(stk_df, st_df, on=['secID','tradeDate'],how='left')
In [50]:
stk_df = stk_df[stk_df['STflg'].isna()].copy()
In [51]:
stk_df.drop('STflg',axis=1,inplace=True)
In [52]:
stk_df.shape
Out[52]:
Monthly trading df¶
In [53]:
stk_df_m = stk_df.groupby(['secID','ym'],as_index=False).tail(1)
In [54]:
stk_df_m
Out[54]:
Fill na months¶
In [55]:
def fill_missing(df, full_dates, id_col='secID', date_col='ym'):
"""
This function fills the missing dates for stocks.
Parameters:
df: The dataframe. Could be a sub-dataframe created by "groupby".
The dataframe must be sorted on the "date_col".
full_dates: the unique dates covering all securities in the full dataframe.
Need to be sorted.
id_col: the security id.
date_col: the dates column for the security
Returns:
A dataframe with the missing dates filled with NA.
"""
one_stk_id = df[id_col].unique()
date_start = np.where(full_dates == df[date_col].min())[0][0]
date_end = np.where(full_dates == df[date_col].max())[0][0]
dates = full_dates[date_start:date_end+1]
idx = pd.MultiIndex.from_product([one_stk_id,dates],
names=(id_col,date_col))
df = df.set_index([id_col,date_col]).reindex(idx).reset_index()
return df
In [56]:
full_dates = np.sort(stk_df['ym'].unique())
In [57]:
%%time
stk_df_m = stk_df_m.groupby('secID').apply(fill_missing, full_dates=full_dates)
In [58]:
stk_df_m.reset_index(drop=True, inplace=True)
In [59]:
stk_df_m
Out[59]:
In [60]:
stk_df_m.info()
In [61]:
stk_df_m.drop('tradeDate',axis=1,inplace=True)
In [62]:
stk_df_m['ret'] = stk_df_m.groupby('secID')['closePrice'].apply(lambda x: x / x.shift() - 1)
# # Use last month's market cap for sorting
stk_df_m['mkt_cap'] = stk_df_m.groupby('secID')['negMarketValue'].shift()
stk_df_m['mkt_cap_date'] = stk_df_m.groupby('secID')['ym'].shift()
stk_df_m.drop(['closePrice','negMarketValue'],axis=1,inplace=True)
stk_df_m
Out[62]:
In [63]:
stk_df_m[(stk_df_m['secID']=='000007.XSHE') & (stk_df_m['ym']>='2021-03') & (stk_df_m['ym']<='2022-08')]
Out[63]:
In [64]:
stk_df_m[stk_df_m['ret'].isna()]
Out[64]:
In [65]:
stk_df_m[stk_df_m['mkt_cap'].isna()]
Out[65]:
In [66]:
stk_df_m.dropna(inplace=True)
In [67]:
stk_df_m
Out[67]:
Merge Book and Market Cap data¶
In [68]:
fundmen_df
Out[68]:
In [69]:
stk_df_m
Out[69]:
In [70]:
bm_df = pd.merge(stk_df_m[['secID','mkt_cap','mkt_cap_date']], fundmen_df[['secID','book','bm_date']],
left_on=['secID','mkt_cap_date'],right_on=['secID','bm_date'])
bm_df['bm'] = bm_df['book'] / bm_df['mkt_cap']
bm_df.drop(['mkt_cap_date','mkt_cap','book'],axis=1,inplace=True)
In [71]:
bm_df
Out[71]:
Merge data¶
Merge rf, ret, mktcap, beta¶
In [72]:
ret_df = pd.merge(stk_df_m, rf, on='ym')
ret_df['exret'] = ret_df['ret'] - ret_df['rf']
ret_df.sort_values(['secID','ym'],inplace=True)
ret_df.reset_index(drop=True,inplace=True)
display(ret_df)
In [73]:
# Use last month's beta for grouping
ret_df = pd.merge(ret_df,beta_m_df,left_on=['secID','mkt_cap_date'],right_on=['secID','ym'])
display(ret_df)
In [74]:
ret_df.drop(['ym_y'],axis=1,inplace=True)
In [75]:
ret_df.rename(columns={'ym_x':'ret_date',
'mkt_cap_date':'mktcap_beta_date'},inplace=True)
In [76]:
ret_df
Out[76]:
In [77]:
ret_df = ret_df[['secID','ret_date','ret','rf','exret','mktcap_beta_date','mkt_cap','beta']]
In [78]:
ret_df
Out[78]:
Merge all data with bm¶
调整return月份对应的bm月份
例：2007:12月的bm分组，对应的是 2008:07 -- 2009:06 的return
调整步骤：
- ret_year - 1, and set this variable as bm_date
- if ret_month is in [1,2,3,4,5,6], ret_year - 1 again
- convert bm_date to year-Dec format
In [79]:
ret_df['year'] = ret_df['ret_date'].dt.year
ret_df['month'] = ret_df['ret_date'].dt.month
ret_df['bm_date'] = ret_df['year'] - 1
idx = ret_df['month'].isin([1,2,3,4,5,6])
ret_df.loc[idx,'bm_date'] = ret_df.loc[idx,'bm_date'] - 1
In [80]:
ret_df
Out[80]:
In [81]:
ret_df.loc[(ret_df['secID']=='300349.XSHE')&(ret_df['ret_date']>='2013-01')&(ret_df['ret_date']<='2013-12')]
Out[81]:
In [82]:
pd.to_datetime(ret_df['bm_date'].astype('str'),format='%Y') + pd.tseries.offsets.YearEnd()
Out[82]:
In [83]:
ret_df['year'] = ret_df['ret_date'].dt.year
ret_df['month'] = ret_df['ret_date'].dt.month
ret_df['bm_date'] = ret_df['year'] - 1
idx = ret_df['month'].isin([1,2,3,4,5,6])
ret_df.loc[idx,'bm_date'] = ret_df.loc[idx,'bm_date'] - 1
ret_df['bm_date'] = pd.to_datetime(ret_df['bm_date'].astype('str'),format='%Y') + pd.tseries.offsets.YearEnd()
ret_df['bm_date'] = ret_df['bm_date'].dt.to_period('M')
ret_df.drop(['month','year'], axis=1, inplace=True)
In [84]:
ret_df
Out[84]:
In [85]:
bm_df
Out[85]:
In [86]:
ret_df = pd.merge(ret_df,bm_df,on=['secID','bm_date'])
In [87]:
ret_df[ret_df['ret_date']<='2010-07']
Out[87]:
In [88]:
gc.collect()
Out[88]:
Sorting on BM¶
In [89]:
q = dict()
keys = ['q'+str(i) for i in range(1, 10)]
values = np.arange(0.1, 1.0, 0.1)
q.update(zip(keys,values))
In [90]:
quantile_df = pd.DataFrame()
for key, value in q.items():
quantile_df[key] = ret_df.groupby(['bm_date'])['bm'].quantile(value)
In [91]:
quantile_df
Out[91]:
In [92]:
ret_df_q = pd.merge(ret_df, quantile_df, on='bm_date')
In [93]:
ret_df_q
Out[93]:
In [94]:
portfolios = dict()
drop_cols = [col for col in ret_df_q.columns if col[0]=='q']
portfolios['p1'] = ret_df_q.loc[ret_df_q['bm'] <= ret_df_q['q1']].copy().drop(drop_cols, axis=1)
for i in range(2,10):
idx = (ret_df_q[f'q{i-1}'] <= ret_df_q['bm']) & (ret_df_q['bm'] <= ret_df_q[f'q{i}'])
portfolios[f'p{i}'] = ret_df_q.loc[idx].copy().drop(drop_cols, axis=1)
portfolios['p10'] = ret_df_q.loc[ret_df_q['bm'] >= ret_df_q['q9']].copy().drop(drop_cols, axis=1)
In [95]:
portfolios['p2']
Out[95]:
return by portfolios¶
In [96]:
portfolios['p2'].info()
In [97]:
for k in portfolios.keys():
print(portfolios[k].groupby(['ret_date'])['exret'].mean().mean())
In [98]:
portfolios_crs_mean = dict()
for k in portfolios.keys():
portfolios_crs_mean[k] = portfolios[k].groupby(['ret_date'])['exret'].mean()
In [99]:
portfolios_crs_mean['p1']
Out[99]:
More robust by adjusting Newey-West Errors¶
In [100]:
mean_values = {}
t_values = {}
for k in portfolios_crs_mean.keys():
y = portfolios_crs_mean[k]
const = np.full(shape=len(y),fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values[k] = reg.params[0]
t_values[k] = reg.tvalues[0]
# Portfolio 10-1
y = portfolios_crs_mean['p9'] - portfolios_crs_mean['p1']
const = np.full(shape=len(y), fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values['p9-p1'] = reg.params[0]
t_values['p9-p1'] = reg.tvalues[0]
In [101]:
pd.DataFrame([mean_values.values(),t_values.values()],index=['mean','t-value'],
columns=mean_values.keys())
Out[101]:
既然一年调一次仓，年收益率呢？¶
In [102]:
portfolios['p1']
Out[102]:
In [103]:
portfolios[k]['1+ret'] = portfolios[k]['ret']+1
portfolios[k]['1+rf'] = portfolios[k]['rf']+1
In [104]:
portfolios[k]
Out[104]:
In [105]:
portfolios[k].groupby(['secID','bm_date'],as_index=False)['1+ret'].prod()
Out[105]:
In [106]:
pf_year_ret = {}
for k in portfolios.keys():
portfolios[k]['1+ret'] = portfolios[k]['ret']+1
portfolios[k]['1+rf'] = portfolios[k]['rf']+1
pf_year_ret[k] = portfolios[k].groupby(['secID','bm_date'],as_index=False)['1+ret'].prod()
pf_year_ret[k]['1+rf'] = portfolios[k].groupby(['secID','bm_date'],as_index=False)['1+rf'].prod()['1+rf']
pf_year_ret[k]['ret'] = pf_year_ret[k]['1+ret'] - 1
pf_year_ret[k]['rf'] = pf_year_ret[k]['1+rf'] - 1
pf_year_ret[k]['exret'] = pf_year_ret[k]['ret'] - pf_year_ret[k]['rf']
In [107]:
pf_year_ret['p1']
Out[107]:
In [108]:
portfolios_crs_mean = dict()
for k in pf_year_ret.keys():
portfolios_crs_mean[k] = pf_year_ret[k].groupby(['bm_date'])['exret'].mean()
In [109]:
mean_values = {}
t_values = {}
for k in portfolios_crs_mean.keys():
y = portfolios_crs_mean[k]
const = np.full(shape=len(y),fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values[k] = reg.params[0]
t_values[k] = reg.tvalues[0]
# Portfolio 10-1
y = portfolios_crs_mean['p10'] - portfolios_crs_mean['p1']
const = np.full(shape=len(y), fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values['p10-p1'] = reg.params[0]
t_values['p10-p1'] = reg.tvalues[0]
In [110]:
pd.DataFrame([mean_values.values(),t_values.values()],index=['mean','t-value'],
columns=mean_values.keys())
Out[110]:
Portfolio characteristics other than return¶
In [111]:
# average beta in each portfolio
for key in portfolios.keys():
print(portfolios[key].groupby('bm_date')['bm'].mean().mean())
In [112]:
portfolios['p1'].groupby('bm_date')['secID'].nunique()
Out[112]:
In [113]:
pf_n_stks = pd.DataFrame()
for key, value in portfolios.items():
pf_n_stks[key] = portfolios[key].groupby('bm_date')['secID'].nunique()
display(pf_n_stks)
pf_n_stks.plot()
Out[113]:
In [114]:
portfolios['p10'].groupby('bm_date')['mkt_cap'].mean()/1e9
Out[114]:
In [115]:
pf_mktcap = pd.DataFrame()
for key, value in portfolios.items():
pf_mktcap[key] = portfolios[key].groupby('bm_date')['mkt_cap'].mean()
display(pf_mktcap)
pf_mktcap.plot()
Out[115]:
In [116]:
pf_mktcap = pf_mktcap / 1e10
for i in range(10):
print(pf_mktcap.mean()[i])
BM 1年调仓单排结论¶
排序方法：t年7月至t+1年6月，按照t-1年12月的BM排序，考察区间内每月平均收益率以及区间年平均收益率
结论：
- 月、年平均收益率呈现微弱递增，但p10的收益率较差。年收益的显著性比较强。
- 最大BM组（也即估值最低组）的market cap起伏很大
- 最小BM组（也即估值最高组）的market cap在样本后期显著增大
- BM的效应可能和market cap有关系
BM Point-in-Time¶
In [117]:
del portfolios, portfolios_crs_mean
In [118]:
# fundmen_df = DataAPI.FdmtBSGet(secID=stk_id,beginDate=START,endDate=END,publishDateEnd=u"",publishDateBegin=u"",endDateRep="",beginDateRep="",beginYear="",endYear="",fiscalPeriod="",field=["secID","publishDate","endDate","endDateRep","actPubtime","fiscalPeriod","TShEquity","TEquityAttrP","minorityInt"],pandas="1")
# fundmen_df.to_pickle('./data/fundmen_df_pit.pkl')
In [119]:
fundmen_df = pd.read_pickle('./data/fundmen_df_pit.pkl')
In [120]:
fundmen_df[['publishDate','endDate']] = fundmen_df[['publishDate','endDate']].apply(pd.to_datetime)
In [121]:
fundmen_df.sort_values(['secID','publishDate','endDate'],inplace=True)
In [122]:
fundmen_df
Out[122]:
In [123]:
fundmen_df.sort_values(['secID','publishDate'])
Out[123]:
In [124]:
fundmen_df = fundmen_df.groupby(['secID','publishDate'],as_index=False).last() #不涉及上上个报表的信息
In [125]:
fundmen_df
Out[125]:
In [126]:
fundmen_df = fundmen_df[(fundmen_df['secID'].str.endswith('XSHE')) | (fundmen_df['secID'].str.endswith('XSHG'))]
In [127]:
# # minorityInt 有时报告，有时不报告。空值时，假设就是上一次报告的值
# # fundmen_df['minorityInt'] = fundmen_df.groupby('secID')['minorityInt'].fillna(method='ffill')
# # 第一轮填完空值为有效数值后，剩下的空值再用0填充。
# fundmen_df['minorityInt'].fillna(0,inplace=True)
- 假设是上一次报告的值可能出现误差，因为股权变动了（注意ffill的方法）
- 直接用TEquityAttrP
In [128]:
# fundmen_df['book'] = fundmen_df['TShEquity'] - fundmen_df['minorityInt']
fundmen_df['book'] = fundmen_df['TEquityAttrP']
In [129]:
fundmen_df
Out[129]:
考虑到报表公布时间可能在当天收市以后，以及报表解读可能需要时间，把publishDate往后加1- publishDate和 tradeDate merge,
- publishDate可能是非交易日，所以merge时要outer，左右表格都不丢观测值。
- 接着要把tradeDate为空值的设置为publishDate，便于排序，方便下面填充
- 然后按照secID和tradeDate sort，因为publishDate非交易日的被放到merge表格的最后了。
- 接着 book 空值由上面第一个非空值填充（当时已知的最新的book value）
- 再把 na 都丢弃即可
In [130]:
# fundmen_df['publishDate+1'] = fundmen_df['publishDate'] + dt.timedelta(days=1)
In [131]:
stk_fundmen_df = pd.merge(stk_df, fundmen_df[['secID','publishDate','endDate','book']],
left_on=['secID','tradeDate'], right_on=['secID','publishDate'],
how='outer')
In [132]:
stk_fundmen_df
Out[132]:
In [133]:
idx = stk_fundmen_df.loc[stk_fundmen_df['tradeDate'].isna()].index
In [134]:
stk_fundmen_df.loc[stk_fundmen_df['tradeDate'].isna(),'tradeDate'] = stk_fundmen_df.loc[stk_fundmen_df['tradeDate'].isna(),'publishDate']
In [135]:
stk_fundmen_df.loc[idx]
Out[135]:
In [136]:
stk_fundmen_df
Out[136]:
In [137]:
stk_fundmen_df.sort_values(['secID','tradeDate'],inplace=True)
In [138]:
stk_fundmen_df
Out[138]:
In [139]:
temp = stk_fundmen_df[stk_fundmen_df['secID']=='000001.XSHE'].copy()
temp
Out[139]:
In [140]:
temp[~temp['book'].isna()]
Out[140]:
In [141]:
stk_fundmen_df[['secID','publishDate','endDate','book']].groupby('secID').fillna(method='ffill')
Out[141]:
In [142]:
stk_fundmen_df.loc[:,['publishDate','endDate','book']] = stk_fundmen_df[['secID','publishDate','endDate','book']].groupby('secID').fillna(method='ffill')
In [143]:
## 查看数据
idx = pd.IndexSlice
stk_fundmen_df.set_index(['secID','tradeDate'],inplace=True)
pd.options.display.max_rows = 20
In [144]:
stk_fundmen_df.loc[idx['000001.XSHE','2010-03'],:]
Out[144]:
In [145]:
stk_fundmen_df.loc[idx['000001.XSHE','2007-04'],:]
Out[145]:
In [146]:
stk_fundmen_df.loc[idx['000001.XSHE','2007-04':'2007-06'],:]
Out[146]:
In [147]:
stk_fundmen_df.reset_index(inplace=True)
In [148]:
stk_fundmen_df
Out[148]:
In [149]:
stk_df_m = stk_fundmen_df.groupby(['secID','ym'],as_index=False).last()
stk_df_m['ret'] = stk_df_m.groupby('secID')['closePrice'].apply(lambda x: x / x.shift() - 1)
In [150]:
stk_df_m
Out[150]:
In [151]:
stk_df_m['ret'] = stk_df_m.groupby(['secID'])['ret'].shift(-1)
stk_df_m['ret_date'] = stk_df_m.groupby('secID')['ym'].shift(-1)
In [152]:
stk_df_m
Out[152]:
In [153]:
stk_df_m['ym_diff'] = stk_df_m['ret_date'].astype(int) - stk_df_m['ym'].astype(int)
stk_df_m.loc[stk_df_m['ym_diff'] != 1]
Out[153]:
In [154]:
# 停牌删去
stk_df_m['ym_diff'] = stk_df_m['ret_date'].astype(int) - stk_df_m['ym'].astype(int)
stk_df_m.loc[stk_df_m['ym_diff'] != 1,'ret'] = np.nan
In [155]:
# 查看数据
temp = stk_df_m['secID'].unique()
display(stk_df_m[stk_df_m['secID'] == np.random.choice(temp,1)[0]])
In [156]:
del temp
In [157]:
stk_df_m.drop(['tradeDate','closePrice','publishDate','endDate', 'ym_diff'],axis=1,inplace=True)
stk_df_m.rename(columns={'ym':'mktcap_book_date','negMarketValue':'mktcap'},inplace=True)
stk_df_m.dropna(inplace=True)
stk_df_m
Out[157]:
Merge¶
In [158]:
ret_df = pd.merge(stk_df_m, rf, left_on='ret_date',right_on='ym')
ret_df.drop('ym',axis=1,inplace=True)
ret_df['exret'] = ret_df['ret'] - ret_df['rf']
ret_df.sort_values(['secID','ret_date'],inplace=True)
ret_df.reset_index(drop=True,inplace=True)
# Use last month's beta for grouping
ret_df = pd.merge(ret_df,beta_m_df,left_on=['secID','mktcap_book_date'],right_on=['secID','ym'])
# display(ret_df)
ret_df.drop(['ym','rf','ret'],axis=1,inplace=True)
ret_df.rename(columns={'mktcap_book_date':'grouping_date'},inplace=True)
ret_df['bm'] = ret_df['book'] / ret_df['mktcap']
ret_df['size'] = np.log(ret_df['mktcap'])
ret_df = ret_df[['secID','grouping_date','size','mktcap','bm',
'beta','ret_date','exret']]
In [159]:
ret_df
Out[159]:
Sorting on BM poin-in-time¶
In [160]:
q = dict()
keys = ['q'+str(i) for i in range(1, 10)]
values = np.arange(0.1, 1.0, 0.1)
q.update(zip(keys,values))
quantile_df = pd.DataFrame()
for key, value in q.items():
quantile_df[key] = ret_df.groupby(['grouping_date'])['bm'].quantile(value)
ret_df_q = pd.merge(ret_df, quantile_df, on='grouping_date')
portfolios = dict()
drop_cols = [col for col in ret_df_q.columns if col[0]=='q']
portfolios['p1'] = ret_df_q.loc[ret_df_q['bm'] <= ret_df_q['q1']].copy().drop(drop_cols, axis=1)
for i in range(2,10):
idx = (ret_df_q[f'q{i-1}'] <= ret_df_q['bm']) & (ret_df_q['bm'] <= ret_df_q[f'q{i}'])
portfolios[f'p{i}'] = ret_df_q.loc[idx].copy().drop(drop_cols, axis=1)
portfolios['p10'] = ret_df_q.loc[ret_df_q['bm'] >= ret_df_q['q9']].copy().drop(drop_cols, axis=1)
portfolios_crs_mean = dict()
for k in portfolios.keys():
portfolios_crs_mean[k] = portfolios[k].groupby(['ret_date'])['exret'].mean()
mean_values = {}
t_values = {}
for k in portfolios_crs_mean.keys():
y = portfolios_crs_mean[k]
const = np.full(shape=len(y),fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values[k] = reg.params[0]
t_values[k] = reg.tvalues[0]
# Portfolio 10-1
y = portfolios_crs_mean['p10'] - portfolios_crs_mean['p1']
const = np.full(shape=len(y), fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values['p10-p1'] = reg.params[0]
t_values['p10-p1'] = reg.tvalues[0]
pd.DataFrame([mean_values.values(),t_values.values()],index=['mean','t-value'],
columns=mean_values.keys())
Out[160]:
结论：
- 用最新的BM更新portfolio可以带来收益率的递增，但每个portfolio本身的收益率并不显著为正，除了p10
- p10和p1的差距是显著为正的
Sorting on BM with data from Uqer¶
In [161]:
# %%time
# begin_ = 2007
# yesterday = dt.datetime.today() - dt.timedelta(days=1)
# yesterday.strftime('%Y%m%d')
# pb_df = DataAPI.MktStockFactorsDateRangeProGet(secID=stk_id,
# beginDate=f'{begin_}0101',
# endDate=yesterday,
# field=['secID','tradeDate','PB'],pandas="1")
In [162]:
# # # 从优矿下载 PB，时间较长。由于优矿的限制，每次下载3年的数据
# pb = {}
# begin_ = 2007
# end_ = 2010
# while begin_ <= 2023:
# if begin_ == 2023:
# yesterday = dt.datetime.today() - dt.timedelta(days=1)
# yesterday.strftime('%Y%m%d')
# pb[begin_] = DataAPI.MktStockFactorsDateRangeProGet(secID=stk_id,
# beginDate=f'{begin_}0101',
# endDate=yesterday,
# field=['secID','tradeDate','PB'],pandas="1")
# else:
# pb[begin_] = DataAPI.MktStockFactorsDateRangeProGet(secID=stk_id,
# beginDate=f'{begin_}0101',
# endDate=f'{end_}1231',
# field=['secID','tradeDate','PB'],pandas="1")
# begin_ = end_ + 1
# end_ = begin_ + 3
# for i in range(len(pb)):
# pb_df = pd.DataFrame(np.vstack([_df for _df in pb.values()]),columns=['secID','tradeDate','PB'])
# pb_df.to_pickle('./data/pb_df.pkl')
In [164]:
pb_df
Out[164]:
In [172]:
# pb_df = pd.read_pickle('./data/pb_df.pkl')
In [165]:
pb_df['tradeDate'] = pd.to_datetime(pb_df['tradeDate'])
pb_df['PB'] = pd.to_numeric(pb_df['PB'])
pb_df['grouping_date'] = pb_df['tradeDate'].dt.to_period('M')
pb_df = pb_df.groupby(['secID','grouping_date'],as_index=False).last()
pb_df['bm_uqer'] = 1 / pb_df['PB']
ret_df = pd.merge(ret_df,pb_df[['secID','grouping_date','bm_uqer']],on=['secID','grouping_date'])
del pb_df
In [166]:
ret_df
Out[166]:
In [173]:
ret_df.loc[ret_df['secID']=='000001.XSHE',['grouping_date','bm','bm_uqer']].set_index('grouping_date').plot()
Out[173]:
In [180]:
sample_id = np.random.choice(ret_df['secID'].unique(),1)
display(ret_df.loc[ret_df['secID'].isin(sample_id),['grouping_date','bm','bm_uqer']])
ret_df.loc[ret_df['secID'].isin(sample_id),['grouping_date','bm','bm_uqer']].set_index('grouping_date').plot()
Out[180]:
In [181]:
ret_df.loc[ret_df['secID'].isin(sample_id),['grouping_date','bm','bm_uqer']].set_index('grouping_date').plot(subplots=True)
Out[181]:
In [182]:
q = dict()
keys = ['q'+str(i) for i in range(1, 10)]
values = np.arange(0.1, 1.0, 0.1)
q.update(zip(keys,values))
quantile_df = pd.DataFrame()
for key, value in q.items():
quantile_df[key] = ret_df.groupby(['grouping_date'])['bm_uqer'].quantile(value)
ret_df_q = pd.merge(ret_df, quantile_df, on='grouping_date')
portfolios = dict()
drop_cols = [col for col in ret_df_q.columns if col[0]=='q']
portfolios['p1'] = ret_df_q.loc[ret_df_q['bm_uqer'] <= ret_df_q['q1']].copy().drop(drop_cols, axis=1)
for i in range(2,10):
idx = (ret_df_q[f'q{i-1}'] <= ret_df_q['bm_uqer']) & (ret_df_q['bm_uqer'] <= ret_df_q[f'q{i}'])
portfolios[f'p{i}'] = ret_df_q.loc[idx].copy().drop(drop_cols, axis=1)
portfolios['p10'] = ret_df_q.loc[ret_df_q['bm_uqer'] >= ret_df_q['q9']].copy().drop(drop_cols, axis=1)
portfolios_crs_mean = dict()
for k in portfolios.keys():
portfolios_crs_mean[k] = portfolios[k].groupby(['ret_date'])['exret'].mean()
mean_values = {}
t_values = {}
for k in portfolios_crs_mean.keys():
y = portfolios_crs_mean[k]
const = np.full(shape=len(y),fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values[k] = reg.params[0]
t_values[k] = reg.tvalues[0]
# Portfolio 10-1
y = portfolios_crs_mean['p10'] - portfolios_crs_mean['p1']
const = np.full(shape=len(y), fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values['p10-p1'] = reg.params[0]
t_values['p10-p1'] = reg.tvalues[0]
pd.DataFrame([mean_values.values(),t_values.values()],index=['mean','t-value'],
columns=mean_values.keys())
Out[182]:
Double Sorting on Size and BM¶
In [183]:
q_size = dict()
keys = ['q_size_1']
values = [0.5]
q_size.update(zip(keys,values))
q_bm = dict()
keys = ['q_bm_1','q_bm_2']
values = [0.3, 0.7]
q_bm.update(zip(keys,values))
q_size_df = pd.DataFrame()
for key, value in q_size.items():
q_size_df[key] = ret_df.groupby(['grouping_date'])['size'].quantile(value)
q_bm_df = pd.DataFrame()
for key, value in q_bm.items():
q_bm_df[key] = ret_df.groupby(['grouping_date'])['bm'].quantile(value)
ret_df_q = pd.merge(ret_df, q_size_df, on='grouping_date')
ret_df_q = pd.merge(ret_df_q, q_bm_df, on='grouping_date')
portfolios_size = dict()
portfolios_size['size1'] = ret_df_q.loc[ret_df_q['size'] <= ret_df_q['q_size_1'],
['secID','grouping_date','ret_date','exret','size','mktcap']]
portfolios_size['size2'] = ret_df_q.loc[ret_df_q['size'] >= ret_df_q['q_size_1'],
['secID','grouping_date','ret_date','exret','size','mktcap']]
portfolios_bm = dict()
portfolios_bm['bm1'] = ret_df_q.loc[ret_df_q['bm'] <= ret_df_q['q_bm_1'],
['secID','grouping_date','ret_date','exret','bm']]
portfolios_bm['bm2'] = ret_df_q.loc[(ret_df_q['bm'] >= ret_df_q['q_bm_1']) & \
(ret_df_q['bm'] <= ret_df_q['q_bm_2']),
['secID','grouping_date','ret_date','exret','bm']]
portfolios_bm['bm3'] = ret_df_q.loc[ret_df_q['bm'] >= ret_df_q['q_bm_2'],
['secID','grouping_date','ret_date','exret','bm']]
portfolios = dict()
for bm_group in portfolios_bm.keys():
for size_group in portfolios_size.keys():
portfolios[f'{bm_group}_{size_group}'] = pd.merge(portfolios_size[size_group],
portfolios_bm[bm_group][['secID','ret_date','bm']],
on=['secID','ret_date'])
mean_portfolios_ret = dict()
for pf in portfolios.keys():
mean_portfolios_ret[pf] = portfolios[pf].groupby('ret_date')['exret'].mean()
print(mean_portfolios_ret[pf].shape) # print 看一下会不会存在某个月份上没有bm和size分组没有任何交叉
# Fast merge by stacking
mean_portfolios_ret_df = pd.DataFrame(np.vstack([pf for pf in mean_portfolios_ret.values()])).T
mean_portfolios_ret_df.columns = mean_portfolios_ret.keys()
mean_portfolios_ret_df.index = mean_portfolios_ret['bm1_size1'].index
# Newey-West adjustment
mean_values = {}
t_values = {}
for k in mean_portfolios_ret.keys():
y = mean_portfolios_ret[k]
const = np.full(shape=len(y),fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=4)
mean_values[k] = reg.params[0]
t_values[k] = reg.tvalues[0]
pd.DataFrame([mean_values.values(),t_values.values()],index=['ret_mean','t_values'],columns=mean_values.keys())
Out[183]:
Fama MacBeth regression¶
In [184]:
# ret_df[(ret_df['ret_date'] >= '2008-02') & (ret_df['secID'] == '000001.XSHE')]
In [185]:
ret_df['exret100'] = ret_df['exret'] * 100
def fm_reg(df):
df_ = df.dropna()
if df_.shape[0] < 15:
return None
reg = LinearRegression().fit(y=df_.loc[:,'exret100'], X=df_.loc[:,['beta','size','bm']])
return np.insert(reg.coef_, 0, reg.intercept_)
temp = ret_df.groupby('ret_date').apply(fm_reg)
reg_result_df = pd.DataFrame(temp.values.tolist())
reg_result_df.index=temp.index
reg_result_df.columns = ['intercept', 'beta_coef','size_coef', 'bm_coef']
# Mean of coefs with NW adjustment
mean_values = {}
t_values = {}
for k in reg_result_df.columns:
y = reg_result_df[k]
const = np.full(shape=len(y),fill_value=1)
reg = sm.OLS(y, const).fit().get_robustcov_results(cov_type='HAC', maxlags=6)
mean_values[k] = reg.params[0]
t_values[k] = reg.tvalues[0]
pd.DataFrame([mean_values.values(),t_values.values()],index=['ret_mean','t_values'],columns=mean_values.keys())
Out[185]:
In [186]:
portfolios
Out[186]:
Fama French 3 factors¶
Fama-French 3 factors 的构建：
- Market return 就是 CAPM 里面的市场收益率
- 另外还有 SMB，HML，也即 Small-Minus-Big, High-Minus-Low
构建方法：
- mktcap1 也叫做 Small, mktcap2 Big. bm1 Low, bm2 Medium, bm3 High.
- 因此对应的，我们的
- bm1_mktcap1: SL
- bm2_mktcap1: SM
- bm3_mktcap1: SH
- bm1_mktcap2: BL
- bm2_mktcap2: BM
- bm3_mktcap2: BH
- 在 Fama French (1993) 的构建里，mktcap 是在t年6月形成并保持到t+1年5月不变。bm和我们这里的构建一样，t年6月按照t-1年的BM ratio构建，保持到t+1年5月不变。
- Fama French 计算了这6组资产组合每一年从7月到下一年6月(资产形成期的第二个月的收益率)的 value-weighted excess return。weight 是t年6月的mktcap占所在portfolio 总的 mktcap 的比重。
- SMB: (SL+SM+SH)/3 - (BL+BM+BH)/3。这样构建的意思是把BM的影响平均掉。
- HML: (SH+BH)/2 - (SL+BL)/2
这里我们还是按照mktcap组合的构建日期，不改成和 Fama-French (1993) 原文一样的日期(t年6月)
In [187]:
portfolios_vwret = {}
for pf in portfolios.keys():
temp = portfolios[pf].groupby('ret_date')['mktcap'].agg({'mktcapsum':np.sum})
portfolios[pf] = pd.merge(portfolios[pf], temp, on='ret_date')
portfolios[pf]['weight'] = portfolios[pf]['mktcap'] / portfolios[pf]['mktcapsum']
portfolios[pf]['weighted_exret'] = portfolios[pf]['exret'] * portfolios[pf]['weight']
portfolios_vwret[pf] = portfolios[pf].groupby('ret_date')['weighted_exret'].sum()
portfolios_vwret_df = pd.DataFrame(np.vstack([pf for pf in portfolios_vwret.values()])).T
portfolios_vwret_df.index = portfolios_vwret['bm1_size1'].index
portfolios_vwret_df.columns = portfolios_vwret.keys()
portfolios_vwret_df.rename(columns={"bm1_size1": "SL",
"bm2_size1": "SM",
"bm3_size1": "SH",
"bm1_size2": "BL",
"bm2_size2": "BM",
"bm3_size2": "BH"},
inplace=True) # vw: value weighted
In [188]:
portfolios_vwret_df
Out[188]:
In [189]:
SMB = (portfolios_vwret_df['SL'] + portfolios_vwret_df['SM'] + portfolios_vwret_df['SH']) / 3 - \
(portfolios_vwret_df['BL'] + portfolios_vwret_df['BM'] + portfolios_vwret_df['BH']) / 3
HML = (portfolios_vwret_df['SH'] + portfolios_vwret_df['BH']) / 2 - \
(portfolios_vwret_df['SL'] + portfolios_vwret_df['BL']) / 2
factors_df = pd.DataFrame(np.vstack([SMB,HML])).T
factors_df.columns = ['SMB','HML']
factors_df.index = SMB.index
In [190]:
factors_df
Out[190]:
百度百科：中证800指数是由中证指数有限公司编制，其成份股是由中证500和沪深300成份股一起构成，中证800指数综合反映沪深证券市场内大中小市值公司的整体状况。
In [191]:
# index_info = DataAPI.SecIDGet(assetClass="IDX",pandas="1")
# 用中证800作为market return
sec_id = ['000906.ZICN']
index_df = DataAPI.MktIdxdGet(indexID=sec_id,beginDate=START,endDate=END,field=['indexID','secShortName','tradeDate','closeIndex','CHGPct'],pandas="1")
index_df['tradeDate'] = pd.to_datetime(index_df['tradeDate'])
index_df['ret_date'] = index_df['tradeDate'].dt.to_period('M')
index_df.sort_values('tradeDate',inplace=True)
index_df = index_df.groupby('ret_date',as_index=False).last()
index_df['mktret'] = index_df['closeIndex'] / index_df['closeIndex'].shift() - 1
index_df = pd.merge(index_df,rf,left_on=['ret_date'],right_on=['ym'])
index_df['exmktret'] = index_df['mktret'] - index_df['rf']
index_df.drop(['ym','mktret','rf','indexID','secShortName','tradeDate',
'closeIndex','CHGPct'],axis=1,inplace=True)
index_df.dropna(inplace=True)
factors_df = pd.merge(index_df, factors_df, on='ret_date')
factors_df['ret_date'] = factors_df['ret_date'].dt.to_timestamp(how='end').dt.normalize()
factors_df.set_index('ret_date',inplace=True)
((1 + factors_df).cumprod()*100).plot()
Out[191]:
In [192]:
((1 + factors_df.loc['2018':]).cumprod()*100).plot()
Out[192]:
In [193]:
factors_df
Out[193]:
In [194]:
factors_df.to_csv('./output_data/factors/ff3.csv')
Long-only factors¶
In [195]:
small_only = (portfolios_vwret_df['SL'] + portfolios_vwret_df['SM'] + portfolios_vwret_df['SH']) / 3
high_only = (portfolios_vwret_df['SH'] + portfolios_vwret_df['BH']) / 2
factors_long_df = pd.DataFrame(np.vstack([small_only,high_only])).T
factors_long_df.columns = ['small_only','high_only']
factors_long_df.index = small_only.index
factors_long_df = pd.merge(index_df, factors_long_df, on='ret_date')
factors_long_df['ret_date'] = factors_long_df['ret_date'].dt.to_timestamp(freq='day',how='end').dt.normalize()
factors_long_df.set_index('ret_date',inplace=True)
((1 + factors_long_df).cumprod()*100).plot()
Out[195]:
In [196]:
((1 + factors_long_df.loc['2018':]).cumprod()*100).plot()
Out[196]:
In [197]:
factors_long_df
Out[197]:
In [198]:
factors_long_df.to_csv('./output_data/factors/ff3_long_only.csv')
In [ ]:
In [ ]: