In [151]:
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)
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 [152]:
# Data is uploaded into uqer, but where is it?
! ls
In [153]:
! ls ..
In [154]:
# 👇 There it is!
! ls ../datas
In [155]:
rf_df = pd.read_csv('rf-daily.csv')
In [156]:
rf_df.head()
Out[156]:
In [157]:
rf_df.info()
In [158]:
rf_df.drop('Unnamed: 2',axis=1,inplace=True)
In [159]:
rf_df.rename(columns={'日无风险收益率_DRFRet':'rf','日期_Date':'date'},inplace=True)
In [160]:
rf_df['date'] = pd.to_datetime(rf_df['date'], format='%Y-%m-%d')
In [161]:
rf_df.head()
Out[161]:
In [162]:
stk_info = DataAPI.SecIDGet(assetClass="E",pandas="1")
stk_id = stk_info['secID']
index_info = DataAPI.SecIDGet(assetClass="IDX",pandas="1")
In [163]:
stk_info.head()
Out[163]:
In [164]:
index_info.head()
Out[164]:
In [165]:
index_info['secID'].nunique()
Out[165]:
Market Index return¶
In [166]:
index_info[index_info['secShortName'].isin(['上证综指','深证综指','创业板指','沪深300','中证500','中证1000'])]
Out[166]:
In [167]:
index_info[index_info['secShortName'].isin(['上证综指','深证综指','创业板指','沪深300','中证500','中证1000'])].drop_duplicates('secShortName').secID.values
Out[167]:
In [168]:
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")
In [169]:
index_df.info()
In [170]:
index_df['tradeDate'] = pd.to_datetime(index_df['tradeDate'],format='%Y-%m-%d')
In [171]:
index_df.head()
Out[171]:
In [172]:
index_ret_df = index_df.pivot(index='tradeDate',columns='secShortName',values='CHGPct')
In [173]:
index_ret_df.head()
Out[173]:
In [174]:
rf_df.set_index('date',inplace=True)
In [175]:
rf_df.head()
Out[175]:
In [176]:
index_ret_df = pd.merge(index_ret_df,rf_df,left_index=True,right_index=True)
In [177]:
index_ret_df.head()
Out[177]:
In [178]:
index_retx_df = index_ret_df.iloc[:,:-1].sub(index_ret_df['rf'], axis=0) # 计算超额收益率
In [179]:
index_retx_df.head()
Out[179]:
In [180]:
# 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 [181]:
# Raw index plot
index_df.pivot(index='tradeDate',columns='secShortName',values='closeIndex').plot()
Out[181]:
In [182]:
((1 + index_retx_df).cumprod()*100).plot()
Out[182]:
早期的平稳可能是一种假象：数据到后面会越来越大。log return会看得更清楚
In [183]:
# log return
(np.log(1 + index_retx_df).cumsum()*100).plot()
Out[183]:
In [184]:
((1 + index_retx_df.loc['2006':'2020']).cumprod()*100).plot()
Out[184]:
In [185]:
((1 + index_retx_df.loc['2010':'2020']).cumprod()*100).plot()
Out[185]:
In [186]:
((1 + index_retx_df.loc['2018':'2020']).cumprod()*100).plot()
Out[186]:
In [187]:
index_retx_df.describe()
Out[187]:
In [188]:
0.0195*np.sqrt(250)
Out[188]:
In [189]:
0.0152*np.sqrt(250)
Out[189]:
Market return in US¶
In [190]:
us_df = pd.read_csv('F-F_Research_Data_Factors_daily.csv',skiprows=3)
us_df.drop(us_df.index.values[-1],inplace=True)
In [191]:
us_df.rename(columns={'Unnamed: 0': 'date'},inplace=True)
In [192]:
us_df['date'] = pd.to_datetime(us_df['date'],format='%Y%m%d')
In [193]:
us_df['Mkt-RF'] = us_df['Mkt-RF']*0.01
us_df.set_index('date',inplace=True)
In [194]:
((1 + us_df.loc['2000':'2022','Mkt-RF']).cumprod()*100).plot()
Out[194]:
In [195]:
(np.log(1 + us_df.loc['2000':'2022','Mkt-RF']).cumsum()*100).plot()
Out[195]:
In [196]:
us_df.loc['2000':'2022','Mkt-RF'].describe()
Out[196]:
In [197]:
0.0125*np.sqrt(250)
Out[197]:
Industry Index Return¶
In [198]:
shenwan_industry_idx1 = ['传媒','电力设备','电子','房地产','纺织服饰','非银金融','钢铁','公用事业','国防军工','环保','机械设备','基础化工','计算机','家用电器','建筑材料','建筑装饰','交通运输','煤炭','美容护理','农林牧渔','社会服务','石油石化','食品饮料','汽车','轻工制造','通信','综合','医药生物','银行','有色金属','商贸零售']
In [199]:
len(shenwan_industry_idx1)
Out[199]:
In [200]:
index_info['secShortName'][(index_info['secShortName'].str.contains('申万'))]
Out[200]:
In [201]:
shenwan_industry_idx1 = ['申万' + indry for indry in shenwan_industry_idx1]
In [202]:
shenwan_industry_idx1
Out[202]:
In [203]:
index_info['secShortName'][(index_info['secShortName'].str.contains('申万')) &
(~index_info['secShortName'].str.contains('Ⅱ$'))]
Out[203]:
In [204]:
index_info['secShortName'][index_info['secShortName'].str.contains('^申万.*Ⅰ$')]
Out[204]:
In [205]:
index_info[index_info['secShortName'].isin(shenwan_industry_idx1)]
Out[205]:
In [206]:
set(shenwan_industry_idx1) - set(index_info[index_info['secShortName'].isin(shenwan_industry_idx1)]['secShortName'])
Out[206]:
The Industry index by Shenwan¶
In [207]:
index_info.loc[index_info['secShortName'].isin(shenwan_industry_idx1)].sort_values('secShortName')
Out[207]:
In [208]:
industry_id = index_info.loc[index_info['secShortName'].isin(shenwan_industry_idx1)].drop_duplicates(keep='last').loc[:,'secID']
In [209]:
industry_id
Out[209]:
In [210]:
index_df = DataAPI.MktIdxdGet(indexID=list(industry_id) + ['000001.ZICN'],beginDate="20031015",endDate="20221231",
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 [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)
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':'2022']).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':'2022',cols]).cumprod()*100).plot()
Out[223]:
Individual Stocks¶
观察以下几个指标：
- 股票数目
- 市值
- 成交额
- 换手率
按照以下几种分组：
- 沪深两市
- 板块
- 市值
In [224]:
# %%time
# stk_df = DataAPI.MktEqudAdjAfGet(secID=stk_id,beginDate="20150101",endDate="20221231",isOpen=1,
# field=["secID","secShortName","exchangeCD","tradeDate",
# "preClosePrice","closePrice","turnoverVol",
# "turnoverValue","dealAmount","turnoverRate",
# "negMarketValue","marketValue"],pandas="1")
In [225]:
# stk_df.to_pickle('stk_df.pkl')
In [226]:
stk_df = pd.read_pickle('stk_df.pkl')
In [227]:
stk_df.info()
In [228]:
stk_df['ret'] = stk_df['closePrice'] / stk_df['preClosePrice'] - 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 = pd.merge(stk_df,q33,on='yearMonth')
stk_df = pd.merge(stk_df,q67,on='yearMonth')
In [241]:
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 [242]:
stk_df
Out[242]:
股票数目¶
In [243]:
stk_df.groupby('yearMonth')['secShortName'].nunique().plot()
Out[243]:
In [244]:
n_stk_by_list = stk_df.groupby(['ListSector','yearMonth'])['secShortName'].nunique().reset_index().pivot(index='yearMonth',columns='ListSector',values='secShortName')
In [245]:
n_stk_by_list
Out[245]:
In [246]:
n_stk_by_list.plot()
Out[246]:
In [247]:
stk_df.groupby(['exchangeCD','yearMonth'])['secShortName'].nunique().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='secShortName').plot()
Out[247]:
In [248]:
stk_df.groupby(['size','yearMonth'])['secShortName'].nunique().reset_index().pivot(index='yearMonth',columns='size',values='secShortName').plot()
Out[248]:
总流通市值¶
In [249]:
stk_df.groupby(['yearMonth'])['negMarketValue'].sum()
Out[249]:
In [250]:
(stk_df.groupby(['yearMonth'])['negMarketValue'].sum() / 1e12).plot()
Out[250]:
In [251]:
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[251]:
In [252]:
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[252]:
In [253]:
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[253]:
总成交额¶
In [254]:
stk_df.groupby(['yearMonth'])['turnoverValue'].sum()
Out[254]:
In [255]:
(stk_df.groupby(['yearMonth'])['turnoverValue'].sum() / 1e12).plot()
Out[255]:
In [256]:
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[256]:
In [257]:
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[257]:
In [258]:
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[258]:
In [259]:
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[259]:
平均换手率¶
In [260]:
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[260]:
平均收益率¶
In [261]:
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[261]:
波动率¶
In [262]:
stk_df.groupby(['yearMonth','secShortName'])['ret'].std().groupby('yearMonth').mean()
Out[262]:
In [263]:
(stk_df.groupby(['yearMonth','secShortName'])['ret'].std().groupby('yearMonth').mean() * 100).plot()
Out[263]:
In [264]:
vol = stk_df.groupby(['yearMonth','secShortName'])['ret'].std()
In [265]:
temp = stk_df[['secShortName','yearMonth','ListSector','exchangeCD','size']].drop_duplicates(['secShortName','yearMonth'])
In [266]:
vol = pd.merge(vol,temp,on=['secShortName','yearMonth'])
In [267]:
vol.rename(columns={'ret':'vol'},inplace=True)
In [268]:
vol_by_sector = vol.groupby(['ListSector','yearMonth'])['vol'].mean().reset_index().pivot(index='yearMonth',columns='ListSector',values='vol') * 100
vol_by_sector.plot()
vol_by_sector = vol.groupby(['exchangeCD','yearMonth'])['vol'].std().reset_index().pivot(index='yearMonth',columns='exchangeCD',values='vol') * 100
vol_by_sector.plot()
vol_by_sector = vol.groupby(['size','yearMonth'])['vol'].std().reset_index().pivot(index='yearMonth',columns='size',values='vol') * 100
vol_by_sector.plot()
Out[268]:
所有在一起¶
In [269]:
group = stk_df.groupby(['yearMonth'])[['negMarketValue','turnoverValue','turnoverRate','ret']].agg({'negMarketValue':'sum','turnoverValue':'sum',
'turnoverRate':'mean','ret':'mean'})
In [270]:
vol_mean = vol.groupby('yearMonth')['vol'].mean()
In [271]:
group = pd.merge(group,vol_mean,on='yearMonth')
In [272]:
# Standardize
from sklearn.preprocessing import StandardScaler
In [273]:
group
Out[273]:
In [274]:
group[['negMarketValue','turnoverValue','turnoverRate']] = StandardScaler().fit(group[['negMarketValue','turnoverValue','turnoverRate']]).transform(group[['negMarketValue','turnoverValue','turnoverRate']])
In [275]:
group
Out[275]:
In [276]:
group.plot()
Out[276]:
In [277]:
group.plot(subplots=True)
Out[277]:
In [278]:
group[group.columns] = StandardScaler().fit(group).transform(group)
group.plot()
group.plot(subplots=True)
Out[278]:
In [279]:
group[['turnoverRate','ret']].corr()
Out[279]:
In [280]:
group[['turnoverValue','ret']].corr()
Out[280]:
In [281]:
group.corr()
Out[281]:
In [ ]: