In [309]:
import pandas as pd
import numpy as np
import tqdm
# import gc
import matplotlib.pyplot as plt
plt.rcParams['figure.figsize'] = (16.0, 9.0)
import seaborn as sns
Data¶
Risk Free rate¶
Risk free rate is from RESSET database. Specifically,
- 本表提供用于研究的日无风险收益数据。
- 数据选择标准： 开始--2002年8月6日用三个月期定期银行存款利率； 2002年8月7日--2006年10月7日用三个月期中央银行票据的票面利率； 2006年10月8日--当前,用上海银行间3个月同业拆放利率。
- 本表已将年度化的基准利率转化为以日为单位计量的收益数据，研究时可直接引用。
In [144]:
# Data is uploaded into uqer, but where is it?
# ! ls
In [145]:
# ! ls ..
In [146]:
# 👇 There it is!
# ! ls ../datas
In [147]:
rf_df = pd.read_csv('./data/rf-daily-2023.csv')
In [148]:
rf_df.head()
Out[148]:
In [149]:
rf_df.tail()
Out[149]:
In [150]:
rf_df.info()
In [151]:
rf_df.drop('Unnamed: 2',axis=1,inplace=True)
In [152]:
rf_df.rename(columns={'日无风险收益率_DRFRet':'rf','日期_Date':'date'},inplace=True)
In [153]:
# rf_df.columns = ['a','b']
In [154]:
rf_df['date'] = pd.to_datetime(rf_df['date'], format='%Y-%m-%d')
In [155]:
rf_df.head()
Out[155]:
In [156]:
stk_info = DataAPI.SecIDGet(assetClass="E",pandas="1")
In [157]:
stk_info['exchangeCD'].unique()
Out[157]:
In [158]:
stk_info = DataAPI.SecIDGet(assetClass="E",pandas="1")
stk_id = stk_info['secID']
index_info = DataAPI.SecIDGet(assetClass="IDX",pandas="1")
In [159]:
stk_info.head()
Out[159]:
In [160]:
index_info.head()
Out[160]:
In [161]:
index_info['secID'].nunique()
Out[161]:
Market Index return¶
In [162]:
index_info[index_info['secShortName'].isin(['上证综指','深证综指','创业板指','沪深300','中证500','中证1000'])]
Out[162]:
In [163]:
index_info[index_info['secShortName'].isin(['上证综指','深证综指','创业板指','沪深300','中证500','中证1000'])].drop_duplicates('secShortName').secID.values
Out[163]:
In [164]:
sec_id = index_info[index_info['secShortName'].isin(['上证综指','深证综指','创业板指','沪深300','中证500','中证1000'])].drop_duplicates('secShortName').secID.values
start = '20000101'
end = '20231231'
index_df = DataAPI.MktIdxdGet(indexID=sec_id,beginDate=start,endDate=end,field=['indexID','secShortName','tradeDate','closeIndex','turnoverVol','turnoverValue','CHGPct'],pandas="1")
In [165]:
index_df.info()
In [166]:
index_df['tradeDate'] = pd.to_datetime(index_df['tradeDate'],format='%Y-%m-%d')
In [167]:
index_df.head()
Out[167]:
In [168]:
index_ret_df = index_df.pivot(index='tradeDate',columns='secShortName',values='CHGPct')
In [169]:
index_ret_df.head()
Out[169]:
In [170]:
rf_df.set_index('date',inplace=True)
In [171]:
rf_df.head()
Out[171]:
In [172]:
index_ret_df = pd.merge(index_ret_df,rf_df,left_index=True,right_index=True)
In [173]:
index_ret_df.head()
Out[173]:
In [174]:
index_retx_df = index_ret_df.iloc[:,:-1].sub(index_ret_df['rf'], axis=0) # 计算超额收益率
In [175]:
index_retx_df.head()
Out[175]:
In [176]:
index_retx_df.tail()
Out[176]:
In [177]:
# sec_id = index_info[index_info['secShortName'].isin(['上证综指','深证综指','创业板指','沪深300','中证500','中证1000'])].drop_duplicates('secShortName').secID.values
# start = '20000101'
# end = '20221231'
# index_df = DataAPI.MktIdxdGet(indexID=sec_id,beginDate=start,endDate=end,field=['indexID','secShortName','tradeDate','closeIndex','turnoverVol','turnoverValue','CHGPct'],pandas="1")
# index_df['tradeDate'] = pd.to_datetime(index_df['tradeDate'],format='%Y-%m-%d')
# index_ret_df = index_df.pivot(index='tradeDate',columns='secShortName',values='CHGPct')
# index_ret_df = pd.merge(index_ret_df,rf_df,left_index=True,right_index=True)
# index_retx_df = index_ret_df.iloc[:,:-1].sub(index_ret_df['rf'], axis=0)
Market Index Return Plot¶
In [178]:
# Raw index plot
index_df.pivot(index='tradeDate',columns='secShortName',values='closeIndex').plot()
Out[178]:
In [179]:
((1 + index_retx_df).cumprod()*100).plot()
Out[179]:
早期的平稳可能是一种假象：数据到后面会越来越大。log return会看得更清楚
In [180]:
# log return
(np.log(1 + index_retx_df).cumsum()*100).plot()
Out[180]:
In [181]:
((1 + index_retx_df.loc['2006':'2023']).cumprod()*100).plot()
Out[181]:
In [182]:
((1 + index_retx_df.loc['2010':'2023']).cumprod()*100).plot()
Out[182]:
In [183]:
((1 + index_retx_df.loc['2018':'2023']).cumprod()*100).plot()
Out[183]:
In [184]:
index_retx_df.describe()
Out[184]:
In [185]:
0.019465*np.sqrt(250)
Out[185]:
In [186]:
0.015047*np.sqrt(250)
Out[186]:
Market return in US¶
In [187]:
us_df = pd.read_csv('./data/F-F_Research_Data_Factors_daily.csv',skiprows=3)
us_df.drop(us_df.index.values[-1],inplace=True)
In [188]:
us_df.rename(columns={'Unnamed: 0': 'date'},inplace=True)
In [189]:
us_df['date'] = pd.to_datetime(us_df['date'],format='%Y%m%d')
In [190]:
us_df['Mkt-RF'] = us_df['Mkt-RF']*0.01
us_df.set_index('date',inplace=True)
In [191]:
((1 + us_df.loc['2000':'2023','Mkt-RF']).cumprod()*100).plot()
Out[191]:
In [192]:
(np.log(1 + us_df.loc['2000':'2023','Mkt-RF']).cumsum()*100).plot()
Out[192]:
In [193]:
us_df.loc['2000':'2022','Mkt-RF'].describe()
Out[193]:
In [194]:
0.012682*np.sqrt(250)
Out[194]:
Industry Index Return¶
In [195]:
# 2023, https://www.swsresearch.com/institute_sw/allIndex/downloadCenter/industryType
shenwan_industry_idx1 = ['传媒','电力设备','电子','房地产','纺织服饰','非银金融','钢铁','公用事业','国防军工','环保','机械设备','基础化工','计算机','家用电器','建筑材料','建筑装饰','交通运输','煤炭','美容护理','农林牧渔','社会服务','石油石化','食品饮料','汽车','轻工制造','通信','综合','医药生物','银行','有色金属','商贸零售']
# shenwan_industry_idx1 = ["农林牧渔","基础化工","钢铁","有色金属","电子","家用电器","食品饮料","纺织服饰","轻工制造","医药生物","公用事业","交通运输","房地产","商贸零售","社会服务","综合","建筑材料","建筑装饰","电力设备","国防军工","计算机","传媒","通信","银行","非银金融","汽车","机械设备","煤炭","石油石化","环保","美容护理"]
In [196]:
len(shenwan_industry_idx1)
Out[196]:
In [197]:
index_info['secShortName'][(index_info['secShortName'].str.contains('申万'))]
Out[197]:
In [198]:
shenwan_industry_idx1 = ['申万' + indry for indry in shenwan_industry_idx1]
In [199]:
shenwan_industry_idx1
Out[199]:
In [200]:
index_info['secShortName'][(index_info['secShortName'].str.contains('申万')) &
(~index_info['secShortName'].str.contains('Ⅱ$'))]
Out[200]:
In [201]:
index_info['secShortName'][index_info['secShortName'].str.contains('^申万.*Ⅰ$')]
Out[201]:
In [202]:
index_info[index_info['secShortName'].isin(shenwan_industry_idx1)]
Out[202]:
In [203]:
len(index_info[index_info['secShortName'].isin(shenwan_industry_idx1)])
Out[203]:
In [204]:
index_info[index_info['secShortName'].isin(shenwan_industry_idx1)].duplicated('secShortName',keep=False)
Out[204]:
In [205]:
set(shenwan_industry_idx1) - set(index_info[index_info['secShortName'].isin(shenwan_industry_idx1)]['secShortName'])
Out[205]:
The Industry index by Shenwan¶
In [206]:
index_info.loc[index_info['secShortName'].isin(shenwan_industry_idx1)].sort_values('secShortName')
Out[206]:
In [207]:
industry_id = index_info.loc[index_info['secShortName'].isin(shenwan_industry_idx1)].drop_duplicates(keep='last').loc[:,'secID']
In [208]:
industry_id
Out[208]:
In [209]:
index_df = DataAPI.MktIdxdGet(indexID=list(industry_id) + ['000001.ZICN'],beginDate="20031015",endDate="20231231",
field=['indexID','secShortName','tradeDate','preCloseIndex',
'closeIndex','turnoverVol','turnoverValue','CHG','CHGPct'],pandas="1")
index_df['tradeDate'] = pd.to_datetime(index_df['tradeDate'],format='%Y-%m-%d')
index_ret_df = index_df.pivot(index='tradeDate',columns='indexID',values='CHGPct')
index_ret_df = pd.merge(index_ret_df,rf_df,left_index=True,right_index=True)
index_retx_df = index_ret_df.iloc[:,:-1].sub(index_ret_df['rf'], axis=0)
In [210]:
index_df
Out[210]:
In [211]:
industry_id_name = dict(zip(index_df['indexID'],index_df['secShortName']))
In [212]:
industry_id_name
Out[212]:
In [213]:
index_df.head()
Out[213]:
In [214]:
index_retx_df.head()
Out[214]:
In [215]:
index_retx_df.rename(mapper=industry_id_name, axis=1, inplace=True) # 将column名称改变
In [216]:
((1 + index_retx_df).cumprod()*100).plot()
Out[216]:
In [217]:
industry_id_name_list = list(industry_id_name.values())
In [218]:
((1 + index_retx_df.loc[:,industry_id_name_list[0:10]]).cumprod()*100).plot()
Out[218]:
食品饮料，家用电器
In [219]:
((1 + index_retx_df.loc[:,industry_id_name_list[10:20]]).cumprod()*100).plot()
Out[219]:
社会服务，医药生物
In [220]:
((1 + index_retx_df.loc[:,industry_id_name_list[20:32]]).cumprod()*100).plot()
Out[220]:
电力设备
In [221]:
((1 + index_retx_df['2020':'2023']).cumprod()*100).plot()
Out[221]:
食品饮料, 家用电器，社会服务，医药生物，电力设备¶
In [222]:
cols = ['上证综指','申万食品饮料','申万家用电器','申万社会服务','申万医药生物','申万电力设备']
In [223]:
((1 + index_retx_df.loc['2003':'2010',cols]).cumprod()*100).plot()
((1 + index_retx_df.loc['2008':'2010',cols]).cumprod()*100).plot()
((1 + index_retx_df.loc['2006':'2009',cols]).cumprod()*100).plot()
((1 + index_retx_df.loc['2011':'2020',cols]).cumprod()*100).plot()
((1 + index_retx_df.loc['2018':'2020',cols]).cumprod()*100).plot()
((1 + index_retx_df.loc['2020':'2023',cols]).cumprod()*100).plot()
Out[223]:
Individual Stocks¶
观察以下几个指标：
- 股票数目
- 市值
- 成交额
- 换手率
按照以下几种分组：
- 沪深两市
- 板块
- 市值
In [224]:
# %%time
# # Need about 4 mins to read. Data size is about 450 MB.
# stk_df = DataAPI.MktEqudAdjAfGet(secID=stk_id,beginDate="20150101",endDate="20231231",isOpen=1,
# field=["secID","secShortName","exchangeCD","tradeDate",
# "preClosePrice","closePrice","turnoverVol",
# "turnoverValue","dealAmount","turnoverRate",
# "negMarketValue","marketValue"],pandas="1")
In [225]:
# stk_df.to_pickle('./data/stk_df.pkl')
In [226]:
stk_df = pd.read_pickle('./data/stk_df.pkl')
In [227]:
stk_df.info()
In [228]:
# stk_df.loc[(stk_df['tradeDate']>='2022-01-01') & (stk_df['tradeDate']<='2023-03-01')].to_pickle('./data/stk_df_22_23.pkl') # for exercise 1
In [229]:
stk_df.head()
Out[229]:
In [230]:
sec_sector = DataAPI.EquGet(secID=stk_id,field=["secID","ListSector"],pandas="1")
In [231]:
# industry = DataAPI.EquIndustryGet(secID=stk_id,industryVersionCD="010303",field=['secID','industryName1',''],pandas="1")
# industry['industryName1'].unique()
# industry.drop_duplicates()
In [232]:
stk_df = pd.merge(stk_df, sec_sector, on="secID")
In [233]:
stk_df
Out[233]:
In [234]:
stk_df['tradeDate'] = pd.to_datetime(stk_df['tradeDate'], format='%Y-%m-%d')
In [235]:
stk_df.set_index(['secID','tradeDate'],inplace=True)
In [236]:
stk_df['yearMonth'] = stk_df.index.get_level_values(1).to_period('M') # Change to Month
In [237]:
stk_df
Out[237]:
In [238]:
q33 = stk_df.groupby(['yearMonth'])['negMarketValue'].quantile(0.33)
q67 = stk_df.groupby(['yearMonth'])['negMarketValue'].quantile(0.67)
In [239]:
q33.name = 'negMarketValue_q33'
q67.name = 'negMarketValue_q67'
In [240]:
stk_df.reset_index(inplace=True)
In [241]:
stk_df = pd.merge(stk_df,q33,on='yearMonth')
stk_df = pd.merge(stk_df,q67,on='yearMonth')
In [242]:
stk_df['size'] = 'small'
stk_df.loc[(stk_df['negMarketValue'] > stk_df['negMarketValue_q33']) & (stk_df['negMarketValue'] <= stk_df['negMarketValue_q67']), 'size'] = 'medium'
stk_df.loc[stk_df['negMarketValue'] > stk_df['negMarketValue_q67'], 'size'] = 'large'
In [243]:
stk_df.head()
Out[243]:
股票数目¶
In [244]:
stk_df.groupby('yearMonth')['secShortName'].nunique().plot()
Out[244]:
In [245]:
n_stk_by_list = stk_df.groupby(['ListSector','yearMonth'])['secShortName'].nunique().reset_index().pivot(index='yearMonth',columns='ListSector',values='secShortName')
In [246]:
n_stk_by_list
Out[246]:
In [247]:
n_stk_by_list.plot()
Out[247]:
In [248]:
stk_df.groupby(['exchangeCD','yearMonth'])['secShortName'].nunique().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='secShortName').plot()
Out[248]:
In [249]:
stk_df.groupby(['size','yearMonth'])['secShortName'].nunique().reset_index().pivot(index='yearMonth',columns='size',values='secShortName').plot()
Out[249]:
总流通市值¶
In [250]:
stk_df.groupby(['yearMonth'])['negMarketValue'].sum()
Out[250]:
In [251]:
(stk_df.groupby(['yearMonth'])['negMarketValue'].sum() / 1e12).plot()
Out[251]:
In [252]:
mkt_value_by_sector = stk_df.groupby(['ListSector','yearMonth'])['negMarketValue'].sum().reset_index().pivot(index='yearMonth',columns='ListSector',values='negMarketValue') / 1e12
mkt_value_by_sector.plot()
Out[252]:
In [253]:
mkt_value_by_exch = stk_df.groupby(['exchangeCD','yearMonth'])['negMarketValue'].sum().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='negMarketValue') / 1e12
mkt_value_by_exch.plot()
Out[253]:
In [254]:
mkt_value_by_exch = stk_df.groupby(['size','yearMonth'])['negMarketValue'].sum().reset_index().pivot(index='yearMonth',columns='size',values='negMarketValue') / 1e12
mkt_value_by_exch.plot()
Out[254]:
In [255]:
mkt_value_by_exch.plot(subplots=True)
Out[255]:
总成交额¶
In [256]:
stk_df.groupby(['yearMonth'])['turnoverValue'].sum()
Out[256]:
In [257]:
(stk_df.groupby(['yearMonth'])['turnoverValue'].sum() / 1e12).plot()
Out[257]:
In [258]:
turnover_value_by_sector = stk_df.groupby(['ListSector','yearMonth'])['turnoverValue'].sum().reset_index().pivot(index='yearMonth',columns='ListSector',values='turnoverValue') / 1e12
turnover_value_by_sector.plot()
Out[258]:
In [259]:
turnover_value_by_sector = stk_df.groupby(['exchangeCD','yearMonth'])['turnoverValue'].sum().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='turnoverValue') / 1e12
turnover_value_by_sector.plot()
Out[259]:
In [260]:
turnover_value_by_sector = stk_df.groupby(['size','yearMonth'])['turnoverValue'].sum().reset_index().pivot(index='yearMonth',columns='size',values='turnoverValue') / 1e12
turnover_value_by_sector.plot()
Out[260]:
In [261]:
turnover_vol_by_sector = stk_df.groupby(['size','yearMonth'])['turnoverVol'].sum().reset_index().pivot(index='yearMonth',columns='size',values='turnoverVol') / 1e12
turnover_vol_by_sector.plot()
Out[261]:
平均换手率¶
In [262]:
stk_df.groupby(['yearMonth'])['turnoverRate'].mean()
(stk_df.groupby(['yearMonth'])['turnoverRate'].mean() / 1e12).plot()
turnover_by_sector = stk_df.groupby(['ListSector','yearMonth'])['turnoverRate'].mean().reset_index().pivot(index='yearMonth',columns='ListSector',values='turnoverRate') / 1e12
turnover_by_sector.plot()
turnover_by_sector = stk_df.groupby(['exchangeCD','yearMonth'])['turnoverRate'].mean().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='turnoverRate') / 1e12
turnover_by_sector.plot()
turnover_by_size = stk_df.groupby(['size','yearMonth'])['turnoverRate'].mean().reset_index().pivot(index='yearMonth',columns='size',values='turnoverRate') / 1e12
turnover_by_size.plot()
Out[262]:
平均收益率¶
In [263]:
stk_df
Out[263]:
In [264]:
stk_df['ret'] = stk_df['closePrice'] / stk_df['preClosePrice'] - 1
In [265]:
stk_df.groupby(['yearMonth'])['ret'].mean()
(stk_df.groupby(['yearMonth'])['ret'].mean() * 100).plot()
ret_by_sector = stk_df.groupby(['ListSector','yearMonth'])['ret'].mean().reset_index().pivot(index='yearMonth',columns='ListSector',values='ret') * 100
ret_by_sector.plot()
ret_by_exch = stk_df.groupby(['exchangeCD','yearMonth'])['ret'].mean().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='ret') * 100
ret_by_exch.plot()
ret_by_size = stk_df.groupby(['size','yearMonth'])['ret'].mean().reset_index().pivot(index='yearMonth',columns='size',values='ret') * 100
ret_by_size.plot()
Out[265]:
波动率¶
In [267]:
stk_df.groupby(['yearMonth','secShortName'])['ret'].std().groupby('yearMonth').mean()
Out[267]:
In [268]:
(stk_df.groupby(['yearMonth','secShortName'])['ret'].std().groupby('yearMonth').mean() * 100).plot()
Out[268]:
In [269]:
(stk_df.groupby(['yearMonth','secShortName'])['ret'].std().groupby('yearMonth').mean() * 100).plot()
Out[269]:
In [270]:
vol = stk_df.groupby(['yearMonth','secShortName'])['ret'].std()
In [275]:
temp = stk_df[['secShortName','yearMonth','ListSector','exchangeCD','size']].drop_duplicates(['secShortName','yearMonth'])
In [276]:
vol = pd.merge(vol,temp,on=['secShortName','yearMonth'])
In [289]:
del temp
In [277]:
vol.rename(columns={'ret':'vol'},inplace=True)
In [288]:
vol.groupby(['ListSector','yearMonth'])['vol'].mean().reset_index().pivot(index='yearMonth',columns='ListSector',values='vol')
Out[288]:
In [294]:
vol_by_sector = vol.groupby(['ListSector','yearMonth'])['vol'].mean().reset_index().pivot(index='yearMonth',columns='ListSector',values='vol') * np.sqrt(240) * 100
vol_by_sector.plot()
vol_by_sector = vol.groupby(['exchangeCD','yearMonth'])['vol'].std().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='vol') * np.sqrt(240) * 100
vol_by_sector.plot()
vol_by_sector = vol.groupby(['size','yearMonth'])['vol'].std().reset_index().pivot(index='yearMonth',columns='size',values='vol') * np.sqrt(240) * 100
vol_by_sector.plot()
Out[294]:
所有在一起¶
In [295]:
group = stk_df.groupby(['yearMonth'])[['negMarketValue','turnoverValue','turnoverRate','ret']].agg({'negMarketValue':'sum','turnoverValue':'sum',
'turnoverRate':'mean','ret':'mean'})
In [296]:
vol_mean = vol.groupby('yearMonth')['vol'].mean()
In [297]:
group = pd.merge(group,vol_mean,on='yearMonth')
In [298]:
# Standardize
from sklearn.preprocessing import StandardScaler
In [299]:
group
Out[299]:
In [300]:
group[['negMarketValue','turnoverValue','turnoverRate']] = StandardScaler().fit(group[['negMarketValue','turnoverValue','turnoverRate']]).transform(group[['negMarketValue','turnoverValue','turnoverRate']])
In [301]:
group
Out[301]:
In [302]:
group.plot()
Out[302]:
In [303]:
group.plot(subplots=True)
Out[303]:
In [304]:
group[group.columns] = StandardScaler().fit(group).transform(group)
group.plot()
group.plot(subplots=True)
Out[304]:
In [305]:
group[['turnoverRate','ret']].corr()
Out[305]:
In [306]:
group[['turnoverValue','ret']].corr()
Out[306]:
In [307]:
group.corr()
Out[307]:
In [316]:
corr = group.corr()
mask = np.triu(np.ones_like(corr, dtype=bool))
# Generate a custom diverging colormap
cmap = sns.diverging_palette(220, 10, as_cmap=True)
# Draw the heatmap with the mask and correct aspect ratio
sns.heatmap(corr, mask=mask, cmap=cmap, vmax=.3, center=0, annot=True,
square=True, linewidths=.5, annot_kws={"size": 10},cbar_kws={"shrink": .5})
Out[316]:
In [ ]: