金融数据分析（八）计算股票的超额收益率

项目二：计算股票的超额收益率

设计一个程序，计算股票的季收益率和年收益率，并计算单支股票收益率相对同一时期股票市场平均收益率的超额收益率（即相对收益率）。

本项目采用tushare的Python SDK 获取数据 具体方法另开新篇。

# -*- coding: utf-8 -*-
"""
Created on Sat Sept 19 9:30:36 2020

@author: mly
"""
import tushare as ts
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib import ticker

plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签

startday = '2015-01-01'
endday = '2020-04-01'
tscode = '600519'
tsindx = 'sh'
df1 = ts.get_k_data(tscode, start = startday, end = endday, ktype = 'M')
df2 = ts.get_k_data(tsindx, start = startday, end = endday, ktype = 'M')
df1.to_excel('600519.xlsx',index=False)
df2.to_excel('sh.xlsx',index=False)
df1=pd.read_excel('600519.xlsx',dtype={'code':'str'})
df2=pd.read_excel('sh.xlsx',dtype={'code':'str'})
df = df2[['date', 'close']].copy()
df.rename(columns={'close': 'indclose'}, inplace = True)
df = pd.merge(df[['date', 'indclose']],
              df1[['date', 'close']], on = 'date', how = 'left')
df.fillna(method = 'ffill',inplace=True) #先后向填充
df.fillna(method = 'bfill',inplace=True) # 再前向填充
# 计算月收益率和累计收益率
df['stk_log_ret'] = np.round(np.log(df['close']/df['close'].shift(1)), 4)
df['ind_log_ret'] = np.round(np.log(df['indclose']/df['indclose'].shift(1)), 4)
df['stk_log_ret'].fillna(method = 'bfill',inplace=True) # 再前向填充
df['ind_log_ret'].fillna(method = 'bfill',inplace=True) # 再前向填充
df['xd_ret']=df['stk_log_ret']-df['ind_log_ret']
df['xd_ret'].fillna(method = 'bfill',inplace=True) # 再前向填充

df_list=list(df['stk_log_ret'].values)
print(df['stk_log_ret'].values)

ret_year=[]
ret_quarter=[]
for i in range(len(df_list)//3):
    ret_quarter.append(np.round(df_list[3*i]+df_list[3*i+1]+df_list[3*i+2],4))
ret_quarter1=pd.Series(ret_quarter)
   
for n in range(len(ret_quarter)//4):
    ret_year.append(np.round(ret_quarter[4*n]+ret_quarter[4*n+1]+ret_quarter[4*n+2]+ret_quarter[4*n+3],4))
ret_year1=pd.Series(ret_year)

quarter_list=[]
year=[]
df_index=list(df.date) 
for value in df_index:
    tempvalue = value.split("-")
    if tempvalue[1] in ['01','02','03']:
        quarter_list.append(tempvalue[0] + "Q1")
        year.append(tempvalue[0])
    elif tempvalue[1] in ['04','05','06']:
        quarter_list.append(tempvalue[0] + "Q2")
        year.append(tempvalue[0])
    elif tempvalue[1] in ['07', '08', '09']:
        quarter_list.append(tempvalue[0] + "Q3")
        year.append(tempvalue[0])
    elif tempvalue[1] in ['10', '11', '12']:
        quarter_list.append(tempvalue[0] + "Q4")
        year.append(tempvalue[0])
   
quarter_set = set(quarter_list)
quarter_list = list(quarter_set)
quarter_list.sort()

year_set = set(year)
year = list(year_set)
year.sort()
year.pop() #由于2020还没有完整，所以删除

ymajorFormatter = ticker.FormatStrFormatter('%.2f%%') # 设置 y 轴标签文本的格式

fig = plt.figure(figsize=(14, 24))
ax1 = fig.add_subplot(3, 1, 1) 
fig.subplots_adjust(bottom = 0.2)
plt.ylabel('季度收益率')
plt.xticks(rotation = 60)
ax1.yaxis.set_major_formatter(ymajorFormatter) # 显示百分比
ax1.plot(quarter_list, ret_quarter1*100, '-cs', lw = 1.5, label = tscode+' 季度收益率')
ax1.legend(loc = 'upper left')

ax2 = fig.add_subplot(3, 1, 2) 
plt.ylabel('年收益率')
plt.xticks(rotation = 60)
ax2.yaxis.set_major_formatter(ymajorFormatter) # 显示百分比
ax2.plot(year, ret_year1*100, '-gp', lw = 1.5, label = tscode+' 年收益率')
ax2.legend(loc = 'upper left')

ax3 = fig.add_subplot(3, 1, 3) 
plt.ylabel('相对收益率')
plt.xticks(rotation = 60)
ax3.yaxis.set_major_formatter(ymajorFormatter) # 显示百分比
ax3.plot(df.date, df['xd_ret']*100, '-rp', lw = 1.5, label = tscode+' 相对市场收益率')
ax3.legend(loc = 'upper left')
plt.show()

运行结果：