def save_file_excel(self, new_data, type, name, num_format):
"""
将一段时间内的每日拆分写入文件（增量写入）
将一段时间内的每日暴露写入文件（增量写入）
"""
if len(new_data) > 0:
save_path = os.path.join(self.data_path, self.fund_name, "每日汇总")
if not os.path.exists(save_path):
os.makedirs(save_path)
file = '%s_%s_%s.xlsx' % (self.fund_name, type, name)
file_name = os.path.join(save_path, file)
print("写入", file_name)
if os.path.exists(file_name):
old_data = pd.read_excel(file_name, index_col=[0])
old_data.index = old_data.index.map(str)
data = FactorOperate().pandas_add_row(old_data, new_data)
else:
data = new_data
num_format_pd = pd.DataFrame([],
columns=data.columns,
index=['format'])
num_format_pd.loc['format', :] = num_format
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(self.fund_name)
excel.write_pandas(data,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
excel.close()
else:
save_path = os.path.join(self.data_path, self.fund_name, "每日汇总")
file = '%s_%s_%s.xlsx' % (self.fund_name, type, name)
file_name = os.path.join(save_path, file)
print("写入数据为0", file_name)
def load_ipo_data(self, beg_date):
""" 下载IPO数据 上市日期 发行价 中签率 申购上限 等等"""
data = self.get_new_stock_list(beg_date)
code_str = ','.join(data.index.values)
data = w.wss(code_str,
"sec_name,ipo_date,ipo_price,ipo_cashratio,ipo_lotteryrate_abc,ipo_otc_cash_pct,ipo_op_uplimit",
"instituteType=1")
data_pd = pd.DataFrame(data.Data, index=data.Fields, columns=data.Codes).T
data_pd["IPO_DATE"] = data_pd["IPO_DATE"].map(lambda x: x.strftime('%Y-%m-%d'))
data_pd.columns = ['股票名称', '上市日期', '发行价格', '网上中签率(%)',
'网下A类中签率(%)', '网下总计中签率(%)', '申购上限数量(万股)']
data_pd['申购上限金额(万元)'] = data_pd["申购上限数量(万股)"] * data_pd['发行价格']
data_pd = data_pd.dropna()
data_pd = data_pd.sort_values(by=['上市日期'], ascending=True)
for i_code in range(0, len(data_pd)):
code = data_pd.index.values[i_code]
ipo_date = data_pd.ix[i_code, '上市日期']
open_date, open_pct, open_price = self.get_open_date_pct(code, ipo_date)
data_pd.ix[i_code, '开板日期'] = open_date
data_pd.ix[i_code, '开板价格'] = open_price
data_pd.ix[i_code, '开板收益'] = open_pct
print(data_pd)
file = os.path.join(self.data_path, 'ipo_data.xlsx')
data = pd.read_excel(file, index_col=[1])
data = data.T.dropna(how='all').T
concat_data = FactorOperate().pandas_add_row(data, data_pd)
concat_data = concat_data.sort_values(by=['上市日期'], ascending=True)
excel = WriteExcel(file)
worksheet = excel.add_worksheet("新股检测")
excel.write_pandas(concat_data, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=None, color="orange", fillna=True)
excel.close()
def generate_excel_history(self, end_date):
""" 陆股通信息 输出到Excel """
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series("20170301", end_date, "M")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series)-1):
bg_date = Date().get_trade_date_offset(ed_date, -20)
print("Hk Inflow Period %s %s" % (bg_date, ed_date))
result_add = self.hk_inflow_period(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
result['累计净流入(亿元)'] = result['期间净流入(亿元)'].cumsum()
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '融资融券情况历史.xlsx')
excel = WriteExcel(filename)
sheet_name = "陆股通北上资金"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.close()
def plan(self, benchmark_array, invest_days, invest_money,
beg_date, end_date, file_prefix):
""" 定投计划回测 """
data = self.get_benchmark_pct(benchmark_array)
data = data.loc[beg_date:end_date, :]
beg_date = data.index[0]
end_date = data.index[-1]
filename = "%s_%s_%s.xlsx" % (file_prefix, beg_date, end_date)
data['星期数'] = data.index.map(lambda x: datetime.strptime(x, '%Y%m%d').strftime("%A"))
data['当日投资金额'] = data['星期数'].map(lambda x: invest_money if x == invest_days else 0)
data['累计投资金额'] = data['当日投资金额'].cumsum()
data['当日投资收益'] = data['累计投资金额'] * data['投资指数日收益']
data['累计投资收益'] = data['当日投资收益'].cumsum()
data['累计投资收益率'] = data['累计投资收益'] / data['累计投资金额']
file = os.path.join(self.data_path, filename)
excel = WriteExcel(file)
worksheet = excel.add_worksheet("定投")
num_format_pd = pd.DataFrame([], columns=data.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', ['累计投资收益率', '投资指数日收益']] = '0.00%'
excel.write_pandas(data, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="blue", fillna=True)
data_select = pd.DataFrame(data['累计投资收益率'])
num_format_pd = pd.DataFrame([], columns=data_select.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(data_select, worksheet, begin_row_number=0, begin_col_number=10,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.line_chart_time_series_plot(worksheet, 0, 10, data_select,
["累计投资收益率"], file_prefix + '累计投资收益率', "K2", "定投")
excel.close()
def generate_excel(self, end_date):
""" 陆股通信息 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -60)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
beg_1m_date = "20190201"
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series)-1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("Hk Inflow Period %s %s" % (bg_date, ed_date))
result_add = self.hk_inflow_period(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 最近一个月平均持股金额最大、最小的几个股票
stock = self.hk_inflow_period_stock(beg_1m_date, end_date)
# 最近一个月平均持股金额行业排序
industry = self.hk_inflow_period_industry(beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '融资融券情况春节后.xlsx')
print(filename)
excel = WriteExcel(filename)
sheet_name = "融资融券情况"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
series_name=["净流入金额", '沪深300'],
chart_name="最近3个月每周融资余额净买入金额(亿元)",
insert_pos="I15", cat_beg="B2", cat_end="B13",
val_beg_list=["F2", "H2"], val_end_list=["F13", "H13"])
num_format_pd = pd.DataFrame([], columns=stock.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(stock, worksheet, begin_row_number=0, begin_col_number=8,
num_format_pd=num_format_pd, color="orange", fillna=True)
num_format_pd = pd.DataFrame([], columns=industry.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(industry, worksheet, begin_row_number=0, begin_col_number=15,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
series_name=["净流入占比", '净流入金额'],
chart_name="行业最近1月融资净买入金额",
insert_pos="I32", cat_beg="P2", cat_end="P30",
val_beg_list=["U2", "S2"], val_end_list=["U30", "S30"])
excel.close()
def rank_all_fund(end_date):
""" 所有基金业绩 """
path = r'E:\Data\mfcteda_data\基金经理'
param_file = os.path.join(path, '泰达宏利基金排名.xlsx')
data = pd.read_excel(param_file, index_col=[0], sheetname="基金排名百分比")
data['基金成立日'] = data['基金成立日'].map(lambda x:Date().change_to_str(x))
data = data.iloc[0:, :]
performance_rank_str = pd.DataFrame()
performance_rank_pct = pd.DataFrame()
performance_return_pct = pd.DataFrame()
for i in range(len(data)):
index = data.index[i]
manager = data.loc[index, "基金经理"]
mage_date = str(int(data.loc[index, "基金成立日"]))
fund_name = data.loc[index, "名称"]
fund_code = data.loc[index, "代码"]
rank_pool = data.loc[index, '公司分类']
print(manager, fund_name)
rank_percent, rank_str = rank_fund_self(end_date, fund_code, rank_pool, fund_name, mage_date)
return_pct = return_fund(end_date, fund_code, fund_name, mage_date)
performance_rank_pct = pd.concat([performance_rank_pct, rank_percent], axis=0)
performance_rank_str = pd.concat([performance_rank_str, rank_str], axis=0)
performance_return_pct = pd.concat([performance_return_pct, return_pct], axis=0)
index_array = np.array([["沪深300*80%", '000300.SH', 0.8, "20050101"],
["中证500*80%", '000905.SH', 0.8, "20050101"],
["万德全A*80%", '881001.WI', 0.8, "20050101"],
["FTSE成长*80%", "FTSE成长", 0.8, "20131008"],
["FTSE周期*80%", "FTSE周期", 0.8, "20131008"],
["FTSE稳定*80%", "FTSE稳定", 0.8, "20131008"]])
index_return_pct = pd.DataFrame()
for i in range(len(index_array)):
index_name = index_array[i][0]
index_code = index_array[i][1]
ratio = index_array[i][2]
mg_date = index_array[i][3]
print(fund_code, fund_name)
return_i = return_index(end_date, index_code, index_name, ratio, mg_date)
index_return_pct = pd.concat([index_return_pct, return_i], axis=0)
performance_return_pct = pd.concat([performance_return_pct, index_return_pct], axis=0)
# date_index = ["2019年", "2018年", "2017年", "2016年", "2015年", "成立以来", "过去1年", "过去2年", "过去3年", "过去5年"]
# performance_return_pct = performance_return_pct[date_index]
file = os.path.join(path, '泰达宏利基金排名%s.xlsx' % end_date)
we = WriteExcel(file)
# write pandas
sheet_name = "泰达宏利基金排名百分比"
num_format_pd = pd.DataFrame([], columns=performance_rank_pct.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
worksheet = we.add_worksheet(sheet_name)
we.write_pandas(performance_rank_pct, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="blue", fillna=True)
we.conditional_format(worksheet, 0, 1, len(performance_rank_pct), len(performance_rank_pct.columns) + 1,
reverse=True)
sheet_name = "泰达宏利基金排名字符串"
num_format_pd = pd.DataFrame([], columns=performance_rank_str.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00'
worksheet = we.add_worksheet(sheet_name)
we.write_pandas(performance_rank_str, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="blue", fillna=True)
sheet_name = "泰达宏利基金收益率"
num_format_pd = pd.DataFrame([], columns=performance_return_pct.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
worksheet = we.add_worksheet(sheet_name)
we.write_pandas(performance_return_pct, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="blue", fillna=True)
we.conditional_format(worksheet, 0, 1, len(performance_return_pct), len(performance_return_pct.columns) + 1)
we.close()
def alpha_contribution(self, fmp_name, type):
"""
risk factor return multiply risk factor exposure_return of fmp
can contribute alpha return on risk factor
"""
for i_date in range(len(self.change_date_series) - 1):
# date
####################################################################################################
date = self.change_date_series[i_date]
next_date = self.change_date_series[i_date + 1]
change_date = Date().get_trade_date_offset(date, 1)
next_change_date = Date().get_trade_date_offset(next_date, 1)
# data
####################################################################################################
stock_return = self.get_stock_return(change_date, next_change_date)
fmp = self.get_fmp(fmp_name, type)
fmp_date = pd.DataFrame(fmp[date])
fmp_date.columns = ['FmpWeight']
fmp_date = fmp_date.dropna()
exposure_date = self.get_risk_factor_exposure(date)
risk_return = self.get_risk_factor_return().T
risk_return_date = pd.DataFrame(risk_return[date])
code_list = list(
set(exposure_date.index) & set(fmp_date.index)
& set(stock_return.index))
code_list.sort()
exposure_date = exposure_date.loc[code_list, :]
fmp_date = fmp_date.loc[code_list, :]
stock_return = stock_return.loc[code_list, :]
if len(fmp_date) > self.min_stock_num:
# risk factor return multiply alpha exposure_return on risk factor
####################################################################################################
fmp_exposure = np.dot(fmp_date.T, exposure_date)
fmp_exposure = pd.DataFrame(fmp_exposure,
index=[date],
columns=exposure_date.columns)
fmp_risk_factor = fmp_exposure.mul(risk_return_date.T)
fmp_alpha_factor = np.dot(fmp_date.T, stock_return)
col = list(fmp_risk_factor.columns)
fmp_risk_factor.loc[date, 'Res_Alpha'] = fmp_alpha_factor[0][
0] - fmp_risk_factor.sum().sum()
fmp_risk_factor.loc[date, 'Industry'] = fmp_risk_factor[
self.industry_factor_name].sum().sum()
fmp_risk_factor.loc[date, 'Style'] = fmp_risk_factor[
self.style_factor_name].sum().sum()
fmp_risk_factor.loc[date, 'Raw_Alpha'] = fmp_alpha_factor[0][0]
col.insert(0, 'Res_Alpha')
col.insert(0, 'Industry')
col.insert(0, 'Style')
col.insert(0, 'Raw_Alpha')
fmp_risk_factor = fmp_risk_factor[col]
print("Contribution for %s %s %s %s" %
(self.alpha_factor_name, fmp_name, type, date))
# 4 concat
####################################################################################################
if i_date == 0:
fmp_risk_factor_all = fmp_risk_factor
fmp_exposure_all = fmp_exposure
else:
fmp_risk_factor_all = pd.concat(
[fmp_risk_factor_all, fmp_risk_factor], axis=0)
fmp_exposure_all = pd.concat(
[fmp_exposure_all, fmp_exposure], axis=0)
# summary
####################################################################################################
sub_path = os.path.join(self.path, 'summary')
fmp_risk_summary = pd.DataFrame()
fmp_risk_summary['Contribution'] = fmp_risk_factor_all.mean(
) * self.annual_number
fmp_risk_summary['IR'] = fmp_risk_factor_all.mean(
) / fmp_risk_factor_all.std() * np.sqrt(self.annual_number)
risk_return = risk_return.T
risk_return_mean = pd.DataFrame(
risk_return.mean()) * self.annual_number
risk_return_mean.columns = ['Factor Return']
exposure_mean = pd.DataFrame(fmp_exposure_all.mean())
exposure_mean.columns = ['Avg Exposure']
exposure = pd.concat([risk_return_mean, exposure_mean], axis=1)
# write excel
####################################################################################################
filename = os.path.join(
sub_path,
'%s_%s_%s_Summary.xlsx' % (self.alpha_factor_name, fmp_name, type))
sheet_name = "Contribution"
we = WriteExcel(filename)
ws = we.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=fmp_risk_summary.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', 'IR'] = '0.00'
we.write_pandas(fmp_risk_summary,
ws,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', 'Avg Exposure'] = '0.000'
we.write_pandas(exposure,
ws,
begin_row_number=4,
begin_col_number=2 + len(fmp_risk_summary.columns),
num_format_pd=num_format_pd,
color="blue",
fillna=True)
we.close()
# Write Csv
####################################################################################################
sub_path = os.path.join(self.path, 'fmp_risk_factor')
filename = os.path.join(
sub_path, '%s_%s_%s_RiskContributionFMP.csv' %
(self.alpha_factor_name, fmp_name, type))
fmp_risk_factor_all.to_csv(filename)
sub_path = os.path.join(self.path, 'fmp_exposure')
filename = os.path.join(
sub_path, '%s_%s_%s_RiskExposureFMP.csv' %
(self.alpha_factor_name, fmp_name, type))
fmp_exposure_all.to_csv(filename)
def write_rs_500(end_date, save_path):
###########################################################################################
fund_name = '建行中国人寿中证500管理计划'
fund_code = fund_name
fund_type = "专户"
benchmark_code = "中证500全收益指数80%+固定收益1%"
benchmark_name = "中证500全收益指数80%+固定收益1%"
benchmark_code_2 = 'H00905.CSI'
benchmark_name_2 = "中证500全收益指数"
setup_date = '20151021'
date_array = np.array([["2019年", '20190101', end_date],
["2018年", "20180101", '20181231'],
['20171110至今', "20171110", end_date],
["2017年", "20170101", '20171231'],
["2016年", "2016001", '20161231'],
["2016年以来", "20160101", end_date],
["成立以来", setup_date, end_date]])
benchmark_array = np.array(
[["中证500全收益指数80%+固定收益1%", "中证500全收益指数80%+固定收益1%"],
["中证500", "000905.SH"], ["中证500全收益", 'H00905.CSI']])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入增强基金表现 相对基准
###########################################################################################
col_number = 1
performance_table = MfcTable().cal_summary_table_enhanced_fund(
fund_name, fund_code, fund_type, date_array, benchmark_code,
benchmark_name)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 写入增强基金表现 相对指数
###########################################################################################
performance_table = MfcTable().cal_summary_table_enhanced_fund(
fund_name, fund_code, fund_type, date_array, benchmark_code_2,
benchmark_name_2)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
# 写入基金和基准收益时间序列 相对基准
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 基金和基准收益图 相对基准
###########################################################################################
series_name = [fund_name, benchmark_name]
chart_name = fund_name + "相对基准（全收益80%+1%）累计超额收益（成立以来）"
insert_pos = 'B16'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
col_number = col_number + cum_return.shape[1] + 2
daliy_return = fs.get_fund_benchmark_daily_return_series(
setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=daliy_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(daliy_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
col_number = col_number + cum_return.shape[1] + 2
# 写入基金和基准收益时间序列 相对基准
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
"20160101", end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 基金和基准收益图 相对基准
###########################################################################################
series_name = [fund_name, benchmark_name]
chart_name = fund_name + "相对基准（全收益80%+1%）累计超额收益（2016年以来）"
insert_pos = 'B32'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
col_number = col_number + cum_return.shape[1] + 2
# 写入基金和基准收益时间序列 相对指数
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 基金和基准收益图 相对指数
###########################################################################################
series_name = [fund_name, benchmark_name_2]
chart_name = fund_name + "相对500全收益指数累计超额收益（成立以来）"
insert_pos = 'B48'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
excel.close()
###########################################################################################
return True
def fund_index_exposure(self,
index_code,
index_name,
end_date,
halfyear_date,
recal_exposure=False):
""" 计算和得到 上个半年报或者年报指数和基金的Barra暴露 """
# index_name = '沪深300'
# index_code = "000300.SH"
file = os.path.join(self.data_path, 'filter_fund_pool',
'基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
halfyear_trade_date = Date().get_normal_date_month_end_day(
halfyear_date)
index_exposure = Index().get_index_exposure_date(
index_code, halfyear_trade_date)
exposure_fund = pd.DataFrame()
for i_fund in range(0, len(fund_code.index)):
fund = fund_code.index[i_fund]
beg_date = Date().get_trade_date_offset(end_date, -260)
if recal_exposure:
Fund().cal_fund_holder_exposure_halfyear(fund_code=fund,
beg_date=beg_date,
end_date=end_date)
exposure_add = Fund().get_fund_holder_exposure_halfyear(
fund, type_list=['STYLE'])
try:
exposure_add = pd.DataFrame(exposure_add.loc[halfyear_date, :])
exposure_add.columns = [fund]
except Exception as e:
print(e)
exposure_add = pd.DataFrame([], columns=[fund])
exposure_fund = pd.concat([exposure_fund, exposure_add], axis=1)
exposure_fund = exposure_fund.T
exposure = pd.concat([exposure_fund, index_exposure], axis=0)
exposure = exposure.dropna()
file = "BARRA风格暴露_" + index_name + "_" + halfyear_date + ".xlsx"
file_name = os.path.join(self.data_path, "exposure", index_name, file)
sheet_name = "BARRA风格暴露"
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(exposure,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def filter_fund_pool(self, index_code, index_name, end_date,
track_error_up):
""" 得到沪深300 、中证500基金池 """
# 参数
# end_date = "20181231"
# index_name = '沪深300'
# index_code = '000300.SH'
# track_error_up = 0.03
beg_date = Date().get_trade_date_offset(end_date, -250)
# 读取数据
fund_nav = Fund().get_fund_factor("Repair_Nav")
index_close = Index().get_index_factor(index_code, attr=['CLOSE'])
index_close.columns = [index_code]
result = pd.DataFrame([],
index=fund_nav.columns,
columns=['跟踪误差', '数据长度'])
# 计算最近1年跟踪误差数据
fund_nav = fund_nav.loc[index_close.index, :]
fund_pct = fund_nav.pct_change()
index_pct = index_close.pct_change()
index_pct = index_pct[index_code]
fund_excess_pct = fund_pct.sub(index_pct, axis='index')
fund_excess_pct_period = fund_excess_pct.loc[beg_date:end_date, :]
result.loc[:, "数据长度"] = fund_excess_pct_period.count()
result.loc[:, "跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
# 筛选
result = result.dropna()
result = result[result['数据长度'] > self.data_min_length]
result = result[result['跟踪误差'] < track_error_up]
# concat fund basic info
data_pd = Fund().get_wind_fund_info()
data_pd = data_pd[[
'BenchMark', 'Name', 'FullName', 'SetupDate', 'InvestType'
]]
data_pd.columns = ['基金基准', '基金简称', '基金全称', '上市日期', '基金类型']
data = pd.concat([data_pd, result], axis=1)
data = data.dropna()
data = data[data["基金基准"].map(lambda x: index_name in x)]
data = data[data["上市日期"] < beg_date]
data = data[data["基金全称"].map(lambda x: "交易型开放式指数" not in x)]
data = data[data["基金全称"].map(lambda x: "联接" not in x)]
data['A类基金'] = data['基金简称'].map(Fund().if_a_fund)
data = data[data['A类基金'] == 'A类基金']
# 输出结果
out_path = os.path.join(self.data_path, "filter_fund_pool")
file_name = os.path.join(out_path, '基金最终筛选池_' + index_name + '.xlsx')
sheet_name = "基金筛选池"
num_format_pd = pd.DataFrame([],
columns=data.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', '跟踪误差'] = '0.00%'
num_format_pd.ix['format', '数据长度'] = '0'
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(data,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def write_public_zz500_adjust(end_date, save_path):
# 参数
###########################################################################################
fund_name_adjust = '泰达宏利中证500_adjust'
fund_code_adjust = '162216.OF_adjust'
fund_name = '泰达宏利中证500'
fund_code = '162216.OF'
fund_type = "公募"
benchmark_code = '000905.SH'
benchmark_name = '中证500'
benchmark_ratio = 0.95
setup_date = '20141003'
today = datetime.strptime(end_date, "%Y%m%d")
before_1y = datetime(year=today.year - 1, month=today.month,
day=today.day).strftime("%Y%m%d")
before_2y = datetime(year=today.year - 2, month=today.month,
day=today.day).strftime("%Y%m%d")
before_3y = datetime(year=today.year - 3, month=today.month,
day=today.day).strftime("%Y%m%d")
date_array = np.array([
["2019年", '20190101', end_date, '20180930'],
["2018年", "20180101", '20181231', "20170930"],
["2017年", "20170101", '20171231', "20160930"],
["2016年", "20160101", "20161231", "20150930"],
["2015年", "20150101", "20151231", "20150101"],
["管理(20141003)以来", setup_date, end_date, setup_date],
["2015年以来", "20150101", end_date, setup_date],
["过去1年", before_1y, end_date, before_1y],
["过去2年", before_2y, end_date, before_2y],
["过去3年", before_3y, end_date, before_3y],
])
benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
["WIND全A", '881001.WI']])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name_adjust + '.xlsx')
sheet_name = fund_name_adjust
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入基金表现 和基金排名
###########################################################################################
performance_table = MfcTable().cal_summary_table_sample(
fund_name, fund_code, fund_type, date_array, benchmark_array)
# rank0 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "wind", excess=False)
# rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "被动指数型基金", excess=True)
rank2 = FundRank().rank_fund_array2(fund_pct,
bench_pct,
fund_code,
date_array,
"中证500基金",
excess=False)
performance_table = pd.concat([performance_table, rank2], axis=0)
col_number = 1
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 写入增强基金表现
###########################################################################################
performance_table = MfcTable().cal_summary_table_enhanced_fund(
fund_name, fund_code, fund_type, date_array, benchmark_code,
benchmark_name, benchmark_ratio)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data),
benchmark_ratio)
# 写入超额收益时间序列
###########################################################################################
excess_cum_return = fs.get_cum_excess_return_series("20150101", end_date)
num_format_pd = pd.DataFrame([],
columns=excess_cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(excess_cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 超额收益图
###########################################################################################
chart_name = fund_name + "累计超额收益（2015年以来）"
insert_pos = 'B12'
excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number,
excess_cum_return, chart_name,
insert_pos, sheet_name)
col_number = col_number + excess_cum_return.shape[1] + 2
# 写入基金收益时间序列
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
"20150101", end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 写入基金收益时间序列图
############################################################################################
series_name = [fund_name, benchmark_name]
chart_name = fund_name + "累计收益（2015年以来）"
insert_pos = 'B26'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
excel.close()
###########################################################################################
return True
def write_public_qf(end_date, save_path):
# 参数
###########################################################################################
fund_name = '泰达宏利启富'
fund_code = '003912.OF'
fund_type = "公募"
benchmark_code = '885003.WI'
benchmark_name = '偏债混合型基金总指数'
benchmark_code_2 = "881001.WI"
benchmark_name_2 = "WIND全A"
benchmark_ratio = 0.95
setup_date = '20170315'
date_array = np.array([["2019年", '20190101', end_date, '20180930'],
["2018年", "20180101", '20181231', "20170930"],
["2017年", setup_date, '20171231', setup_date],
["成立以来", setup_date, end_date, setup_date]])
benchmark_array = np.array([["沪深300",
"000300.SH"], ["WIND全A", "881001.WI"],
["中证全债", "H11001.CSI"],
["偏债混合基金指数", '885003.WI']])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入基金表现 和基金排名
###########################################################################################
performance_table = MfcTable().cal_summary_table(fund_name, fund_code,
fund_type, date_array,
benchmark_array)
rank0 = FundRank().rank_fund_array2(fund_pct,
bench_pct,
fund_code,
date_array,
"灵活配置型基金_30",
excess=False)
rank1 = FundRank().rank_fund_array2(fund_pct,
bench_pct,
fund_code,
date_array,
"wind",
excess=False)
performance_table = pd.concat([performance_table, rank0, rank1], axis=0)
col_number = 1
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
setup_date, end_date)
benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return2 = fs.get_bencnmark_cum_return_series(setup_date, end_date)
# 写入基金和基准时间序列
###########################################################################################
cum_return = pd.concat([cum_return, cum_return2], axis=1)
cum_return.columns = [fund_name, benchmark_name, benchmark_name_2]
cum_return = cum_return.dropna()
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 基金和基准时间序列图
###########################################################################################
chart_name = fund_name + "累计收益（成立以来）"
series_name = [fund_name, benchmark_name, benchmark_name_2]
insert_pos = 'B12'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
excel.close()
###########################################################################################
return True
def write_zlhl2018(end_date, save_path):
# 参数
###########################################################################################
fund_name = '广州农商行梓霖惠利1号'
fund_code = fund_name
fund_type = "专户"
benchmark_code = '885007.WI'
benchmark_name = "混合债券二级基金指数"
benchmark_code_2 = "H11001.CSI"
benchmark_name_2 = "中证全债指数"
setup_date = '20180628'
date_array = np.array([["2019年", '20190101', end_date],
["2018年", "20180101", '20181231'],
["20180628以来", '20180628', end_date]])
benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
["股票型基金", '885012.WI'],
["混合债券二级基金指数", '885007.WI'],
["中证全债指数", "H11001.CSI"]])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name + '2018年.xlsx')
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入增强基金表现 相对基准
###########################################################################################
col_number = 1
performance_table = MfcTable().cal_summary_table_enhanced_fund(
fund_name, fund_code, fund_type, date_array, benchmark_code,
benchmark_name)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 写入增强基金表现 相对指数
###########################################################################################
performance_table = MfcTable().cal_summary_table_enhanced_fund(
fund_name, fund_code, fund_type, date_array, benchmark_code_2,
benchmark_name_2)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 写入基金绝对表现
###########################################################################################
performance_table = MfcTable().cal_summary_table(fund_name, fund_code,
fund_type, date_array,
benchmark_array)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
# 写入基金和基准收益时间序列 相对基准
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 基金和基准收益图 相对基准
###########################################################################################
series_name = [fund_name, benchmark_name]
chart_name = fund_name + "相对" + benchmark_name + " 累计超额收益（成立以来）"
insert_pos = 'B16'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
col_number = col_number + cum_return.shape[1] + 2
# 写入基金和基准收益时间序列 相对指数
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 基金和基准收益图 相对指数
###########################################################################################
series_name = [fund_name, benchmark_name_2]
chart_name = fund_name + "相对" + benchmark_name_2 + " 累计超额收益（成立以来）"
insert_pos = 'B32'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
excel.close()
###########################################################################################
return True
def write_excel(self, end_date):
""" 将所有基金收益率、超额收益、排名写入文件 """
###########################################################
file_name = os.path.join(self.data_path,
"基金最近表现_" + str(end_date) + ".xlsx")
sheet_name = "指数和基金收益"
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
###########################################################
table = self.get_mfcteda_all_return(end_date)
table2 = self.get_benchmark_all_return(end_date)
table = pd.concat([table2, table], axis=0)
num_format_pd = pd.DataFrame([],
columns=table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(table,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="red",
fillna=True)
###########################################################
sheet_name = "基金超额收益"
worksheet = excel.add_worksheet(sheet_name)
table = self.get_mfcteda_all_excess_return(end_date)
num_format_pd = pd.DataFrame([],
columns=table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(table,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="green",
fillna=True)
###########################################################
sheet_name = "基金排名"
worksheet = excel.add_worksheet(sheet_name)
table = self.get_mfcteda_all_rank(end_date)
print(table)
num_format_pd = pd.DataFrame([],
columns=table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(table,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="bule",
fillna=True)
excel.close()
def generate_excel(self, end_date, quarter_date, quarter_last_date):
""" 陆股通信息 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -400)
quarter_date_series = Date().get_normal_date_series(
beg_date, end_date, "Q")
# 最近4个季度重仓持股金额
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
result = pd.DataFrame()
for i in range(len(quarter_date_series) - 1):
quarter_last_date_temp = quarter_date_series[i]
quarter_date_temp = quarter_date_series[i + 1]
add = self.fund_holder_quarter_sum(quarter_date_temp,
quarter_last_date_temp)
quarter_trade_date = Date().get_trade_date_offset(
quarter_date_temp, 0)
add.loc[quarter_date_temp,
'沪深300'] = index_data.loc[quarter_trade_date, "CLOSE"]
result = pd.concat([result, add], axis=0)
# 最近一个月平均持股金额最大、最小的几个股票
stock = self.hk_inflow_period_stock(quarter_date, quarter_last_date)
# 最近一个月平均持股金额行业排序
industry = self.fund_holder_quarter_industry(quarter_date,
quarter_last_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '基金重仓季报持股.xlsx')
excel = WriteExcel(filename)
sheet_name = "基金重仓季报持股"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', "净流入占比"] = '0.00%'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["季度流入金额", '沪深300'],
chart_name="基金季报重仓持股净流入金额(亿元)",
insert_pos="B10",
cat_beg="B2",
cat_end="B6",
val_beg_list=["E2", "G2"],
val_end_list=["E6", "G6"])
num_format_pd = pd.DataFrame([],
columns=stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', "净流入占比"] = '0.00%'
excel.write_pandas(stock,
worksheet,
begin_row_number=0,
begin_col_number=8,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=industry.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', "净流入占比"] = '0.00%'
excel.write_pandas(industry,
worksheet,
begin_row_number=0,
begin_col_number=15,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["季度流入金额", "季度流入占比"],
chart_name="基金季报重仓行业净流入金额(亿元)",
insert_pos="B26",
cat_beg="P2",
cat_end="P30",
val_beg_list=["S2", "T2"],
val_end_list=["S30", "T30"])
excel.close()
def save_file_summary(self):
""" 将一段时间内的归因总结写入文件 """
unit_nav_daliy = self.read_file_excel("拆分", "单位净值", self.beg_date,
self.end_date)
pct_daliy = self.read_file_excel("拆分", "收益率", self.beg_date,
self.end_date)
excess_exposure = self.read_file_excel("Barra", "基金超额暴露",
self.beg_date, self.end_date)
fund_risk_return = self.read_file_excel("Barra", "基金风格收益",
self.beg_date, self.end_date)
risk_factor_return = self.read_file_excel("Barra", '风格因子收益',
self.beg_date, self.end_date)
# Result
index = [
'资产盈亏', '股票盈亏', '新股盈亏', '固收其他盈亏', '日内交易盈亏', '期货多头盈亏', '期货空头盈亏',
'管理托管费用', '交易印花费用', '基准盈亏', '超额盈亏', '择时盈亏', '选股盈亏', 'Alpha',
'Style', 'Industry', '全仓选股盈亏'
]
change_index = [
'基金整体', '股票部分', '新股部分', '固收+其他部分', "日内交易部分", '期货多头', '期货空头',
'管理托管', '交易印花', '股票基准', '股票超额', '股票择时', '股票选股', 'Alpha', 'Barra风格',
'Barra行业', '全仓股票选股'
]
unit_nav_sum = unit_nav_daliy[index].sum()
pct_sum = ((pct_daliy[index] + 1.0).cumprod() - 1.0).iloc[-1, :]
result = pd.concat([unit_nav_sum, pct_sum], axis=1)
result.index = change_index
result.loc['股票平均仓位', :] = unit_nav_daliy['股票仓位'].mean()
result.loc['归因开始时间', :] = unit_nav_daliy.index[0]
result.loc['归因结束时间', :] = unit_nav_daliy.index[-1]
result.columns = ["净值拆分", '收益率拆分']
# Barra
type_list = ["STYLE", "INDUSTRY"]
factor_name = Barra().get_factor_name(type_list=type_list)
factor_name = list(factor_name["NAME_EN"].values)
barra = pd.DataFrame([],
index=factor_name,
columns=['基金超额暴露', '因子区间收益率', '基金暴露因子收益'])
barra.loc[factor_name,
"基金超额暴露"] = excess_exposure.loc[:, factor_name].mean().values
barra.loc[factor_name,
"因子区间收益率"] = risk_factor_return.loc[:,
factor_name].sum().values
barra.loc[factor_name,
"基金暴露因子收益"] = fund_risk_return.loc[:,
factor_name].sum().values
type_list = ["STYLE"]
factor_name = Barra().get_factor_name(type_list=type_list)
factor_name = list(factor_name["NAME_EN"].values)
style_barra = barra.loc[factor_name, :]
style_barra = style_barra.sort_values(by=['基金暴露因子收益'], ascending=False)
style_barra.loc['Barra风格汇总',
"基金暴露因子收益"] = style_barra["基金暴露因子收益"].sum()
type_list = ["INDUSTRY"]
factor_name = Barra().get_factor_name(type_list=type_list)
factor_name = list(factor_name["NAME_EN"].values)
industry_barra = barra.loc[factor_name, :]
industry_barra = industry_barra.sort_values(by=['基金暴露因子收益'],
ascending=False)
industry_barra.loc['Barra行业汇总',
"基金暴露因子收益"] = industry_barra["基金暴露因子收益"].sum()
# 备注部分
notes = pd.DataFrame([],
index=["基金整体=", "股票部分=", "股票超额=", "股票选股="],
columns=["备注"])
notes.loc["基金整体=", "备注"] = "基金整体=股票部分+新股部分+固收其他部分+日内交易部分+管理托管+交易印花"
notes.loc["日内交易部分=", "备注"] = "日内交易部分=当日交易均价和收盘价的差带来的盈亏"
notes.loc["股票部分=", "备注"] = "股票部分=股票基准+股票超额"
notes.loc["股票超额=", "备注"] = "股票超额=股票择时+股票选股"
notes.loc["股票选股=", "备注"] = "股票选股=Alpha+Barra风格+Barra行业"
# Save File
save_path = os.path.join(self.data_path, self.fund_name, "阶段归因")
file = '%s_归因汇总_%s.xlsx' % (self.fund_name, self.period_name)
file_name = os.path.join(save_path, file)
print(file_name)
if not os.path.exists(save_path):
os.makedirs(save_path)
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(self.fund_name)
# Result
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00%'
num_format_pd.loc['format', ['净值拆分']] = '0.0000'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="red",
fillna=True)
# Notes
begin_row_number = len(style_barra) + 2
for i in range(len(notes.index)):
excel.insert_merge_range(worksheet, begin_row_number + i, 5,
begin_row_number + i, 8,
notes.loc[notes.index[i], "备注"])
num_format_pd = pd.DataFrame([],
columns=style_barra.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00%'
num_format_pd.loc['format', ['基金超额暴露']] = '0.0000'
excel.write_pandas(style_barra,
worksheet,
begin_row_number=0,
begin_col_number=5,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=industry_barra.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00%'
num_format_pd.loc['format', ['基金超额暴露']] = '0.0000'
excel.write_pandas(industry_barra,
worksheet,
begin_row_number=0,
begin_col_number=10,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.close()
def generate_excel(self, end_date):
""" 大股东增减持信息 输出到EXcel"""
beg_date = Date().get_trade_date_offset(end_date, -60)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("Major Holder Deal %s %s" % (bg_date, ed_date))
result_add = self.major_holder_deal_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 最近一个月增减持额最大的几个股票
stock = self.major_holder_deal_period_stock(beg_1m_date, end_date)
# 最近一个月增减持额行业排序
industry = self.major_holder_deal_period_industry(
beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '重要股东二级市场交易.xlsx')
excel = WriteExcel(filename)
sheet_name = "读写EXCEL测试"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["增减持金额(亿)", "沪深300"],
chart_name="最近3个月每周大股东增减持总和",
insert_pos="O2",
cat_beg="D2",
cat_end="D14",
val_beg_list=["E2", "G2"],
val_end_list=["E14", "G14"])
num_format_pd = pd.DataFrame([],
columns=stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(stock,
worksheet,
begin_row_number=0,
begin_col_number=8,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=industry.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(industry,
worksheet,
begin_row_number=0,
begin_col_number=11,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["行业分布"],
chart_name="行业最近1月增减持金额",
insert_pos="O16",
cat_beg="L2",
cat_end="L30",
val_beg_list=["M2"],
val_end_list=["M30"])
excel.close()
def fund_score_all(self, end_date):
""" 所有基金得分 """
file = os.path.join(self.data_path, "基金经理绩效考核.xlsx")
data = pd.read_excel(file, sheetname="基金经理考核范围", index_col=[0])
data['现任经理管理开始日'] = data['现任经理管理开始日'].map(str)
data.index = data['代码']
result = pd.DataFrame()
end_date = Date().change_to_datetime(end_date)
before_1y = datetime(year=end_date.year, month=1, day=1).strftime("%Y%m%d")
before_3y = datetime(year=end_date.year-2, month=1, day=1).strftime("%Y%m%d")
before_3y = max(before_3y, "20160101")
before_5y = datetime(year=end_date.year-4, month=1, day=1).strftime("%Y%m%d")
before_5y = max(before_5y, "20160101")
end_date = Date().change_to_str(end_date)
for i in range(0, len(data)):
fund_code = data.index[i]
index_code = data.loc[fund_code, "基准代码"]
rank_pool = data.loc[fund_code, "考核分类"]
mg_date = data.loc[fund_code, "现任经理管理开始日"]
fund_name = data.loc[fund_code, "名称"]
fund_type = data.loc[fund_code, "基金类型"]
if mg_date <= before_1y:
res = self.fund_score(fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, index_code)
res.loc[fund_code, "当年研究贡献得分"] = 1.00
res.loc[fund_code, "过去2年研究贡献得分"] = 1.00
if mg_date >= before_5y:
res.loc[fund_code, "5年收益"] = res.loc[fund_code, "管理以来收益"]
res.loc[fund_code, "5年排名"] = res.loc[fund_code, "管理以来排名"]
res.loc[fund_code, "5年排名百分比"] = res.loc[fund_code, "管理以来排名百分比"]
res.loc[fund_code, "5年得分"] = res.loc[fund_code, "管理以来得分"]
if mg_date >= before_3y:
res.loc[fund_code, "3年收益"] = res.loc[fund_code, "管理以来收益"]
res.loc[fund_code, "3年排名"] = res.loc[fund_code, "管理以来排名"]
res.loc[fund_code, "3年排名百分比"] = res.loc[fund_code, "管理以来排名百分比"]
res.loc[fund_code, "3年得分"] = res.loc[fund_code, "管理以来得分"]
res.loc[fund_code, "长期得分"] = 0.0
res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "过去2年研究贡献得分"]
res.loc[fund_code, "长期得分"] += 0.40 * res.loc[fund_code, "3年得分"]
res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "5年得分"]
res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "管理以来得分"]
res.loc[fund_code, "当年得分"] = 0.0
res.loc[fund_code, "当年得分"] += 0.20 * res.loc[fund_code, "当年研究贡献得分"]
res.loc[fund_code, "当年得分"] += 0.40 * res.loc[fund_code, "1年得分"]
res.loc[fund_code, "当年得分"] += 0.20 * res.loc[fund_code, "3年得分"]
res.loc[fund_code, "当年得分"] += 0.10 * res.loc[fund_code, "5年得分"]
res.loc[fund_code, "当年得分"] += 0.10 * res.loc[fund_code, "管理以来得分"]
result = pd.concat([result, res], axis=0)
file = os.path.join(self.data_path, "基金经理效绩得分.xlsx")
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
sheet_name = ""
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()
# 超额收益
result = pd.DataFrame()
for i in range(0, len(data)):
fund_code = data.index[i]
index_code = data.loc[fund_code, "基准代码"]
rank_pool = data.loc[fund_code, "考核分类"]
mg_date = data.loc[fund_code, "现任经理管理开始日"]
fund_name = data.loc[fund_code, "名称"]
fund_type = data.loc[fund_code, "基金类型"]
if (mg_date <= before_1y) and (fund_type == "行业基金"):
res = self.fund_excess_score(fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, index_code)
if mg_date >= before_5y:
res.loc[fund_code, "5年超额收益"] = res.loc[fund_code, "管理以来超额收益"]
res.loc[fund_code, "5年超额排名"] = res.loc[fund_code, "管理以来超额排名"]
res.loc[fund_code, "5年超额排名百分比"] = res.loc[fund_code, "管理以来超额排名百分比"]
res.loc[fund_code, "5年超额得分"] = res.loc[fund_code, "管理以来超额得分"]
if mg_date >= before_3y:
res.loc[fund_code, "3年超额收益"] = res.loc[fund_code, "管理以来超额收益"]
res.loc[fund_code, "3年超额排名"] = res.loc[fund_code, "管理以来超额排名"]
res.loc[fund_code, "3年超额排名百分比"] = res.loc[fund_code, "管理以来超额排名百分比"]
res.loc[fund_code, "3年超额得分"] = res.loc[fund_code, "管理以来超额得分"]
result = pd.concat([result, res], axis=0)
file = os.path.join(self.data_path, "基金经理效绩超额得分.xlsx")
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
sheet_name = ""
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()
def alpha_contribution(self,
neutralize='Raw',
W_mat="Equal",
stock_pool='Astock'):
"""
利用Barra风险模型对alpha因子进行归因
严格的，对于不同股票池的风险因子的因子收益率应该重新计算
但是这里暂时用的还是全市场的风险因子收益率
"""
type_list = ['COUNTRY', 'STYLE', 'INDUSTRY']
stock_return = Barra().get_stock_return().T
risk_return = Barra().get_factor_return(type_list=type_list)
for i_date in range(len(self.change_date_series) - 1):
# date
####################################################################################################
date = self.change_date_series[i_date]
bg_date = Date().get_trade_date_offset(date, 1)
ed_date = self.change_date_series[i_date + 1]
# data
####################################################################################################
stock_return_period = stock_return.loc[bg_date:ed_date, :]
stock_return_period = stock_return_period.T.dropna().T
stock_return_period = pd.DataFrame(
stock_return_period.sum(skipna=True))
stock_return_period.columns = ['Pct']
risk_return_period = risk_return.loc[bg_date:ed_date, :]
risk_return_period = pd.DataFrame(
risk_return_period.sum(skipna=True))
risk_return_period.columns = [date]
exposure_date = Barra().get_factor_exposure_date(date, type_list)
fmp = self.get_fmp(neutralize, W_mat, stock_pool)
fmp_date = pd.DataFrame(fmp[date])
fmp_date.columns = ['FmpWeight']
fmp_date = fmp_date.dropna()
code_list = list(
set(exposure_date.index) & set(fmp_date.index)
& set(stock_return_period.index))
code_list.sort()
exposure_date = exposure_date.loc[code_list, :]
fmp_date = fmp_date.loc[code_list, :]
stock_return_period = stock_return_period.loc[code_list, :]
if len(fmp_date) > self.min_stock_num:
# risk factor return multiply alpha exposure_return on risk factor
####################################################################################################
fmp_exposure = np.dot(fmp_date.T, exposure_date)
fmp_exposure = pd.DataFrame(fmp_exposure,
index=[date],
columns=exposure_date.columns)
fmp_risk_factor = fmp_exposure.mul(risk_return_period.T)
fmp_alpha_factor = np.dot(fmp_date.T, stock_return_period)
col = list(fmp_risk_factor.columns)
fmp_risk_factor.loc[date, 'Res_Alpha'] = fmp_alpha_factor[0][
0] - fmp_risk_factor.sum().sum()
fmp_risk_factor.loc[date, 'Industry'] = fmp_risk_factor[
self.industry_factor_name].sum().sum()
fmp_risk_factor.loc[date, 'Style'] = fmp_risk_factor[
self.style_factor_name].sum().sum()
fmp_risk_factor.loc[date, 'Raw_Alpha'] = fmp_alpha_factor[0][0]
col.insert(0, 'Res_Alpha')
col.insert(0, 'Industry')
col.insert(0, 'Style')
col.insert(0, 'Raw_Alpha')
fmp_risk_factor = fmp_risk_factor[col]
print("Contribution for %s %s %s %s %s" %
(self.alpha_factor_name, neutralize, W_mat, date,
stock_pool))
# 4 concat
####################################################################################################
if i_date == 0:
fmp_risk_factor_all = fmp_risk_factor
fmp_exposure_all = fmp_exposure
else:
fmp_risk_factor_all = pd.concat(
[fmp_risk_factor_all, fmp_risk_factor], axis=0)
fmp_exposure_all = pd.concat(
[fmp_exposure_all, fmp_exposure], axis=0)
# summary
####################################################################################################
sub_path = os.path.join(self.path, 'summary')
fmp_risk_summary = pd.DataFrame()
fmp_risk_summary['Contribution'] = fmp_risk_factor_all.mean(
) * self.annual_number
fmp_risk_summary['IR'] = fmp_risk_factor_all.mean(
) / fmp_risk_factor_all.std() * np.sqrt(self.annual_number)
risk_return_mean = pd.DataFrame(risk_return.mean()) * 250
risk_return_mean.columns = ['Factor Return']
exposure_mean = pd.DataFrame(fmp_exposure_all.mean())
exposure_mean.columns = ['Avg Exposure']
exposure = pd.concat([risk_return_mean, exposure_mean], axis=1)
# write excel
####################################################################################################
filename = os.path.join(
sub_path, '%s_%s_%s_%s_Summary.xlsx' %
(self.alpha_factor_name, neutralize, W_mat, stock_pool))
sheet_name = "Contribution"
we = WriteExcel(filename)
ws = we.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=fmp_risk_summary.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
we.write_pandas(fmp_risk_summary,
ws,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', 'Avg Exposure'] = '0.00%'
we.write_pandas(exposure,
ws,
begin_row_number=4,
begin_col_number=2 + len(fmp_risk_summary.columns),
num_format_pd=num_format_pd,
color="blue",
fillna=True)
we.close()
# Write Csv
####################################################################################################
sub_path = os.path.join(self.path, 'fmp_risk_factor')
filename = os.path.join(
sub_path, '%s_%s_%s_%s_RiskContributionFMP.csv' %
(self.alpha_factor_name, neutralize, W_mat, stock_pool))
fmp_risk_factor_all.to_csv(filename)
sub_path = os.path.join(self.path, 'fmp_exposure')
filename = os.path.join(
sub_path, '%s_%s_%s_%s_RiskExposureFMP.csv' %
(self.alpha_factor_name, neutralize, W_mat, stock_pool))
fmp_exposure_all.to_csv(filename)
def generate_excel(self, end_date):
""" ETF净申购 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -120)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
# 一段时间内增减持额时间序列
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("ETF Fund %s %s" % (bg_date, ed_date))
result_add = self.get_etf_data_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 不同类型ETF基金流入流出
fund_type = self.get_etf_data_period_type(beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, 'ETF流入.xlsx')
excel = WriteExcel(filename)
sheet_name = "ETF流入"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["净申购金额", '沪深300'],
chart_name="每周ETF净申购金额（亿元）",
insert_pos="F8",
cat_beg="B2",
cat_end="B25",
val_beg_list=["D2", "F2"],
val_end_list=["D25", "F25"])
num_format_pd = pd.DataFrame([],
columns=fund_type.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(fund_type,
worksheet,
begin_row_number=0,
begin_col_number=15,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["净申购占比", '净申购金额'],
chart_name="规模前10类ETF基金最近1月净流入",
insert_pos="F24",
cat_beg="P2",
cat_end="P11",
val_beg_list=["S2", "R2"],
val_end_list=["S11", "R11"])
excel.close()
def risk_model_performance(self, risk_model_name="cne5", period='M'):
""" 检验模型当中的因子对收益率的解释能力 主要计算 R2 """
self.set_model_name(risk_model_name)
self.get_data_all(risk_model_name)
price = Stock().read_factor_h5("Price_Unadjust")
num = Date().get_period_number_for_year(period)
print("Test Risk Model %s From %s To %s" %
(risk_model_name, beg_date, end_date))
date_series = Date().get_trade_date_series(beg_date, end_date, period)
date_series = list(
set(date_series) & set(self.style_factor_data.minor_axis)
& set(price.columns))
date_series.sort()
factor_return_total = pd.DataFrame()
t_value_total = pd.DataFrame()
r_square = pd.DataFrame([], columns=['R2'])
# 因子暴露文件
for i_date in range(0, len(date_series) - 1):
cur_date = date_series[i_date]
next_date = Date().get_trade_date_offset(cur_date, 1)
exposure_date, industry_data = self.get_data_date(next_date)
buy_date = cur_date
sell_date = date_series[i_date + 1]
stock_pct = price[sell_date] / price[buy_date] - 1.0
exposure_date['Pct'] = stock_pct
data = exposure_date
data = data.dropna()
data = data.drop(labels=['ChinaEquity'], axis=1)
y = data['Pct'].values
x = data.iloc[:, 1:].values
model = sm.OLS(y, x).fit()
factor_return = pd.DataFrame(model.params[:],
index=data.columns[1:],
columns=[cur_date])
t_value = pd.DataFrame(model.tvalues[:],
index=data.columns[1:],
columns=[cur_date])
factor_return_total = pd.concat(
[factor_return_total, factor_return], axis=1)
t_value_total = pd.concat([t_value_total, t_value], axis=1)
r_square.loc[cur_date, 'R2'] = model.rsquared_adj
# 去掉行业因子
factor_return_total = factor_return_total.T
factor_return_total = factor_return_total[self.get_risk_factor_list()]
t_value_total = t_value_total.T
t_value_total = t_value_total[self.get_risk_factor_list()]
t_value_ratio = (t_value_total.abs() > 2).sum(
axis=0) / t_value_total.count(axis=0)
t_value_abs_mean = t_value_total.abs().mean()
factor_return_std = factor_return_total.std(axis=0) * np.sqrt(num)
summary = pd.concat(
[t_value_ratio, t_value_abs_mean, factor_return_std], axis=1)
summary = summary.dropna()
summary.columns = ["T值绝对值大于2的比率", 'T值绝对值均值', '因子年化波动率']
summary['模型平均R2'] = r_square['R2'].mean()
summary = summary.sort_values(by=['T值绝对值大于2的比率'], ascending=True)
file = os.path.join(self.model_performance_path,
'Summary_%s.xlsx' % risk_model_name)
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([],
columns=summary.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00%'
num_format_pd.loc['format', ['T值绝对值均值']] = '0.00'
worksheet = excel.add_worksheet(risk_model_name)
excel.write_pandas(summary,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="red",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=r_square.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(r_square,
worksheet,
begin_row_number=0,
begin_col_number=8,
num_format_pd=num_format_pd,
color="red",
fillna=True)
excel.close()
def calculate_fund_factor(self, index_code, index_name, end_date):
""" 计算基金最近一段时间内 跟踪误差、超额收益、信息比率 """
# 参数
# index_code = '000905.SH'
# index_name = '中证500'
# end_date = '20151231'
beg_date = Date().get_trade_date_offset(end_date, -self.data_length)
# 读取数据 基金池 基金净值数据 指数收盘价数据
file = os.path.join(self.data_path, 'filter_fund_pool',
'基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
fund_code['上市日期'] = fund_code['上市日期'].map(str)
fund_nav = Fund().get_fund_factor("Repair_Nav")
index_close = Index().get_index_factor(index_code, attr=['CLOSE'])
index_close.columns = [index_code]
# 筛选新基金 并下载基金规模
fund_code = fund_code.loc[:, ['上市日期', '基金全称', '基金简称']]
fund_code = fund_code[fund_code['上市日期'] < beg_date]
fund_code_str = ','.join(fund_code.index)
fund_asset = w.wss(fund_code_str, "netasset_total",
"unit=1;tradeDate=" + str(end_date))
fund_asset = pd.DataFrame(fund_asset.Data,
index=['基金规模'],
columns=fund_asset.Codes).T
fund_asset['基金规模'] /= 100000000.0
fund_asset['基金规模'] = fund_asset['基金规模'].round(2)
fund_asset = fund_asset[fund_asset['基金规模'] > 0.45]
fund_info = pd.concat([fund_code, fund_asset], axis=1)
fund_info = fund_info.dropna()
# 计算最近1年 各项指标
result = pd.DataFrame([], index=fund_code.index, columns=['跟踪误差'])
fund_nav = fund_nav.ix[index_close.index, fund_code.index]
fund_pct = fund_nav.pct_change()
index_pct = index_close.pct_change()
index_pct = index_pct[index_code]
fund_excess_pct = fund_pct.sub(index_pct, axis='index')
fund_excess_pct_period = fund_excess_pct.loc[beg_date:end_date, :]
fund_nav_period = fund_nav.loc[beg_date:end_date, :]
index_close_period = index_close.loc[beg_date:end_date, :]
result.ix[:, "数据长度"] = fund_excess_pct_period.count()
result.ix[:, "跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
fund_return_log = (fund_nav_period.pct_change() + 1.0).applymap(
np.log).cumsum().ix[-1, :]
fund_return = fund_return_log.map(np.exp) - 1
last_date_close = index_close_period.iloc[len(fund_nav_period) - 1, :]
first_date_close = index_close_period.iloc[0, :]
result.ix[:, "基金涨跌"] = fund_return
result.ix[:, "指数涨跌"] = (last_date_close / first_date_close -
1.0).values[0]
result.ix[:, "超额收益"] = result.ix[:, "基金涨跌"] - result.ix[:, "指数涨跌"]
result.ix[:, "信息比率"] = result.ix[:, "超额收益"] / result.ix[:, "跟踪误差"]
result = result[result['数据长度'] > self.data_min_length]
result = pd.concat([fund_info, result], axis=1)
result = result.sort_values(by=['信息比率'], ascending=False)
result = result.dropna()
result = result.fillna("")
# 写到EXCEL表
out_path = os.path.join(self.data_path, "cal_fund_factor", index_name)
file_name = os.path.join(
out_path, '基金指标_' + index_name + '_' + end_date + '.xlsx')
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', '数据长度'] = '0'
num_format_pd.ix['format', '信息比率'] = '0.00'
num_format_pd.ix['format', '基金规模'] = '0.00'
num_format_pd.ix['format', '信息比率'] = '0.00'
sheet_name = "基金指标"
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def concat_file(path, report_date, last_date):
#########################################################################################################
# path = 'E:\\3_Data\\4_fund_data\\8_fund_index_exposure_weekly\\'
# report_date = '20171231'
# last_date = '20180831'
#########################################################################################################
exposure_file = os.path.join(
path, "halfyear_holding_exposure",
'FundHalfYearExposure_' + report_date + '.csv')
fund_halyear_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
cols = list(fund_halyear_exposure.columns)
quant_fund_exposure = fund_halyear_exposure[
fund_halyear_exposure['Type'].map(lambda x: '主动量化' in x)].mean()
quant_fund_exposure = pd.DataFrame(quant_fund_exposure,
columns=['重点量化基金平均']).T
active_fund_exposure = fund_halyear_exposure[
fund_halyear_exposure['Type'].map(lambda x: '主动股票' in x)].mean()
active_fund_exposure = pd.DataFrame(active_fund_exposure,
columns=['重点主动基金平均']).T
fund_halyear_exposure_add = pd.concat(
[quant_fund_exposure, active_fund_exposure, fund_halyear_exposure],
axis=0)
fund_halyear_exposure_add['Type'] = fund_halyear_exposure_add[
'Type'].fillna('基金平均')
fund_halyear_exposure_add = fund_halyear_exposure_add[cols]
exposure_file = os.path.join(
path, "halfyear_holding_exposure",
'IndexHalfYearExposure_' + report_date + '.csv')
index_halyear_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
exposure_file = os.path.join(path, "lastyear_holding_exposure",
'IndexLastDateExposure_' + last_date + '.csv')
index_lastdate_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
exposure_file = os.path.join(path, "lastyear_holding_exposure",
'FundLastDateExposure_' + last_date + '.csv')
fund_lastdate_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
halfyear_exposure = pd.concat(
[index_halyear_exposure, fund_halyear_exposure_add], axis=0)
lastdate_exposure = pd.concat(
[index_lastdate_exposure, fund_lastdate_exposure], axis=0)
#########################################################################################################
exposure_file = os.path.join(path, "output_exposure",
'IndexFundExposure' + last_date + '.xlsx')
excel = WriteExcel(exposure_file)
worksheet = excel.add_worksheet("最近交易日风格暴露")
num_format_pd = pd.DataFrame([],
columns=lastdate_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(lastdate_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(lastdate_exposure),
len(lastdate_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(lastdate_exposure.columns),
1 + len(lastdate_exposure),
len(lastdate_exposure.columns),
{'type': 'data_bar'})
worksheet = excel.add_worksheet("最近半年报风格暴露")
num_format_pd = pd.DataFrame([],
columns=halfyear_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(halfyear_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(halfyear_exposure),
len(halfyear_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(halfyear_exposure.columns),
1 + len(halfyear_exposure),
len(halfyear_exposure.columns),
{'type': 'data_bar'})
excel.close()
exposure_file = os.path.join(path, "output_exposure",
'最近交易日风格暴露' + last_date + '.xlsx')
excel = WriteExcel(exposure_file)
worksheet = excel.add_worksheet("最近交易日风格暴露")
num_format_pd = pd.DataFrame([],
columns=lastdate_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(lastdate_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(lastdate_exposure),
len(lastdate_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(lastdate_exposure.columns),
1 + len(lastdate_exposure),
len(lastdate_exposure.columns),
{'type': 'data_bar'})
excel.close()
exposure_file = os.path.join(path, "output_exposure",
'最近半年报风格暴露' + report_date + '.xlsx')
excel = WriteExcel(exposure_file)
worksheet = excel.add_worksheet("最近半年报风格暴露")
num_format_pd = pd.DataFrame([],
columns=halfyear_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(halfyear_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(halfyear_exposure),
len(halfyear_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(halfyear_exposure.columns),
1 + len(halfyear_exposure),
len(halfyear_exposure.columns),
{'type': 'data_bar'})
excel.close()
def fund_score_date(self,
index_code,
index_name,
end_date,
halfyear_normal_date,
adjust=False):
""" 从上述4个方面打分 """
# 读取 基金指标
print("Score %s %s %s" % (index_name, end_date, halfyear_normal_date))
sub_path = os.path.join(self.data_path, 'cal_fund_factor', index_name)
filename = os.path.join(
sub_path, '基金指标_' + index_name + '_' + end_date + '.xlsx')
fund_factor = pd.read_excel(filename, index_col=[0], encoding='gbk')
# 读取 风格暴露
sub_path = os.path.join(self.data_path, 'exposure', index_name)
filename = os.path.join(
sub_path,
'BARRA风格暴露_' + index_name + '_' + halfyear_normal_date + '.xlsx')
exposure = pd.read_excel(filename, index_col=[0], encoding='gbk')
exposure = exposure.dropna()
exposure_diff = exposure - exposure.ix[index_code, :]
exposure = exposure_diff.abs()
exp_col = [
'贝塔', '市净率', '市盈率', '成长', '杠杆', '流动性', '动量', '残差波动率', '市值', '非线性市值'
]
exposure.columns = exp_col
exposure_diff.columns = exp_col
exposure_diff = exposure_diff.round(3)
exposure_diff = exposure_diff.drop(index_code)
fund_code_list = list(
set(fund_factor.index) & set(exposure_diff.index))
fund_code_list.sort()
fund_factor = fund_factor.loc[fund_code_list, :]
exposure_diff = exposure_diff.loc[fund_code_list, :]
# 计算跟踪误差得分
result = pd.DataFrame([], index=fund_factor.index)
result.loc[:, '基金全称'] = fund_factor.loc[:, '基金全称']
if index_name == '中证500':
val_25 = max(0.03, fund_factor.loc[:, '跟踪误差'].quantile(0.20))
val_75 = min(0.08, fund_factor.loc[:, '跟踪误差'].quantile(0.80))
else:
val_25 = max(0.02, fund_factor.loc[:, '跟踪误差'].quantile(0.20))
val_75 = min(0.05, fund_factor.loc[:, '跟踪误差'].quantile(0.80))
result.loc[:, '跟踪误差'] = fund_factor.loc[:, '跟踪误差']
if adjust and "001733.OF" in result.index:
result.loc["001733.OF", '跟踪误差'] -= 0.001
result.loc[:, '跟踪误差得分'] = fund_factor.loc[:, '跟踪误差'].map(
lambda x: self.score_quarter_reverse(x, val_25, val_75)).round(2)
# 计算超额收益得分
if index_name == '中证500':
val_25 = max(0.00, fund_factor.loc[:, '超额收益'].quantile(0.20))
val_75 = min(0.15, fund_factor.loc[:, '超额收益'].quantile(0.80))
else:
val_25 = max(0.00, fund_factor.loc[:, '超额收益'].quantile(0.20))
val_75 = min(0.10, fund_factor.loc[:, '超额收益'].quantile(0.80))
result.loc[:, '超额收益'] = fund_factor.loc[:, '超额收益']
if adjust and "001733.OF" in result.index:
result.loc["001733.OF", '超额收益'] += 0.001
result.loc[:, '超额收益得分'] = fund_factor.loc[:, '超额收益'].map(
lambda x: self.score_quarter(x, val_25, val_75)).round(2)
# 计算信息比率得分
val_25 = fund_factor.loc[:, '信息比率'].quantile(0.20)
val_75 = fund_factor.loc[:, '信息比率'].quantile(0.80)
result.ix[:, '信息比率'] = fund_factor.loc[:, '信息比率'].round(2)
result.loc[:, '信息比率得分'] = fund_factor.loc[:, '信息比率'].map(
lambda x: self.score_quarter(x, val_25, val_75)).round(2)
# 计算风格偏露得分（十个风格 每个打1分，10个总共打10分，再乘以0.5）
result = result.dropna(subset=["基金全称"])
result.loc[:, '风格暴露得分'] = 0.0
result = pd.concat([result, exposure_diff], axis=1)
for i_col in range(len(exposure_diff.columns)):
col = exposure_diff.columns[i_col]
result.loc[:, '风格暴露得分'] += result.loc[:, col].map(self.score_ex)
result.loc[:, '风格暴露得分'] = result.loc[:, '风格暴露得分'] * 0.5
if adjust and "001733.OF" in result.index:
result.loc["001733.OF",
'风格暴露得分'] = min(5,
result.loc["001733.OF", '风格暴露得分'] + 0.5)
val_25 = result.loc[:, '风格暴露得分'].quantile(0.20)
val_75 = result.loc[:, '风格暴露得分'].quantile(0.80)
result.loc[:, '风格暴露得分'] = result.loc[:, '风格暴露得分'].map(
lambda x: self.score_quarter(x, val_25, val_75)).round(2)
# 计算总得分（后期调整沪深300、中证500权重不一样，可以给中证500在超额收益上多一点权重）
result['总得分'] = 0.0
if index_name == "中证500":
result['总得分'] += result['跟踪误差得分'] * 0.10
result['总得分'] += result['超额收益得分'] * 0.40
result['总得分'] += result['信息比率得分'] * 0.20
result['总得分'] += result['风格暴露得分'] * 0.30
else:
result['总得分'] += result['跟踪误差得分'] * 0.10
result['总得分'] += result['超额收益得分'] * 0.40
result['总得分'] += result['信息比率得分'] * 0.30
result['总得分'] += result['风格暴露得分'] * 0.20
result = result.sort_values(by=['总得分'], ascending=False)
col = [
"基金全称", '总得分', '跟踪误差', '跟踪误差得分', '超额收益', '超额收益得分', '信息比率',
'信息比率得分', '风格暴露得分'
]
col.extend(exposure_diff[0:10])
result = result[col]
result = result.dropna()
# 写到EXCEL表
sub_path = os.path.join(self.data_path, 'score_fund', index_name)
filename = os.path.join(
sub_path, '基金得分_' + index_name + '_' + end_date + '.xlsx')
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', ['跟踪误差', '超额收益']] = '0.00%'
sheet_name = "基金得分"
excel = WriteExcel(filename)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def risk_factor_performance(self,
factor_name,
stock_pool_name="AllChinaStockFilter",
beg_date=None,
end_date=None,
period='M'):
""" 计算单风险因子的因子收益率波动率、自相关性、T值大于2的比例等等 找到有定价能力的风险因子 """
exposure = self.get_risk_factor_exposure(factor_name)
price = Stock().read_factor_h5("Price_Unadjust")
num = Date().get_period_number_for_year(period)
if beg_date is None:
beg_date = exposure.columns[0]
if end_date is None:
end_date = exposure.columns[-1]
date_series = Date().get_trade_date_series(beg_date, end_date, period)
date_series = list(set(date_series) & set(exposure.columns) & set(price.columns))
date_series.sort()
factor_return = pd.DataFrame([], index=date_series, columns=['因子收益率'])
for i_date in range(0, len(date_series)-1):
cur_date = date_series[i_date]
buy_date = cur_date
sell_date = date_series[i_date + 1]
stock_list = Stock().get_invest_stock_pool(stock_pool_name, cur_date)
stock_pct = price[sell_date] / price[buy_date] - 1.0
exposure_date = exposure[cur_date]
exposure_next = exposure[sell_date]
data = pd.concat([exposure_date, exposure_next], axis=1)
data = data.dropna()
stock_list_finally = list(set(stock_list) & set(data.index))
stock_list_finally.sort()
data = data.loc[stock_list_finally, :]
auto_corr = data.corr().iloc[0, 1]
data = pd.concat([exposure_date, stock_pct], axis=1)
stock_list_finally = list(set(stock_list) & set(data.index))
stock_list_finally.sort()
data = data.loc[stock_list_finally, :]
data.columns = ['x', 'y']
data = data.dropna()
if len(data) > 0:
print("Risk Factor %s %s %s" % (factor_name, stock_pool_name, cur_date))
y = data['y'].values
x = data['x'].values
x_add = sm.add_constant(x)
model = sm.OLS(y, x_add).fit()
factor_return_date = model.params[1]
rank_corr = data.corr(method="spearman").iloc[0, 1]
t_value = model.tvalues[1]
r2 = model.rsquared_adj
factor_return.loc[cur_date, '因子收益率'] = factor_return_date
factor_return.loc[cur_date, 'IC'] = rank_corr
factor_return.loc[cur_date, 'T值'] = t_value
factor_return.loc[cur_date, '自相关系数'] = auto_corr
factor_return.loc[cur_date, 'R2'] = r2
else:
print("Risk Factor is Null %s %s %s" % (factor_name, stock_pool_name, cur_date))
factor_return = factor_return.dropna(subset=['因子收益率', 'T值'])
factor_return['因子累计收益率'] = factor_return['因子收益率'].cumsum()
factor_return_mean = factor_return['因子收益率'].mean() * num
factor_return_std = factor_return['因子收益率'].std() * np.sqrt(num)
rank_ic_mean = factor_return['IC'].mean()
rank_ic_ir = rank_ic_mean / factor_return['IC'].std() * np.sqrt(num)
if len(factor_return) > 0:
abs_t_2_ratio = len(factor_return[factor_return['T值'].abs() > 2]) / len(factor_return)
data_beg_date = factor_return.index[0]
data_end_date = factor_return.index[-1]
abs_t_mean = factor_return['T值'].abs().mean()
auto_corr_mean = factor_return['自相关系数'].mean()
r2_mean = factor_return['R2'].mean()
summary = pd.DataFrame([], columns=['因子表现'])
summary.loc['因子年化收益率', "因子表现"] = factor_return_mean
summary.loc['因子年化波动率', "因子表现"] = factor_return_std
summary.loc['IC均值', "因子表现"] = rank_ic_mean
summary.loc['ICIR', "因子表现"] = rank_ic_ir
summary.loc['平均R2', "因子表现"] = r2_mean
summary.loc['T值绝对值大于2的比率', "因子表现"] = abs_t_2_ratio
summary.loc['T值绝对值平均数', "因子表现"] = abs_t_mean
summary.loc['自相关系数平均', "因子表现"] = auto_corr_mean
summary.loc['期数', "因子表现"] = str(len(factor_return))
summary.loc['开始日期', "因子表现"] = data_beg_date
summary.loc['结束日期', "因子表现"] = data_end_date
path = os.path.join(self.factor_performance_path, stock_pool_name)
if not os.path.exists(path):
os.makedirs(path)
file = os.path.join(path, 'Summary_%s.xlsx' % factor_name)
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=summary.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
worksheet = excel.add_worksheet(factor_name)
excel.write_pandas(summary, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
num_format_pd = pd.DataFrame([], columns=factor_return.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(factor_return, worksheet, begin_row_number=0, begin_col_number=4,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()
else:
print("Risk Factor %s is Null in %s" % (factor_name, stock_pool_name))
def write_excel(self):
""" 写入Excel """
# cal need data
fund_basic_info = self.get_fund_basic_info()
update_date = self.get_update_date()
asset_allocation = self.get_fund_asset_allocation()
top10_stock = self.get_fund_top10_stock()
industry_allocation = self.get_fund_industry_allocation()
fund_strategy_info = self.get_fund_strategy()
last_performance = self.get_fund_last_performance()
characteristic = self.get_stock_characteristic()
# write xlsx
sub_path = os.path.join(self.data_path, self.last_trade_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
xlsx_file = os.path.join(sub_path,
'%s_%s.xlsx' % (self.prefix, self.fund_name))
excel = WriteExcel(xlsx_file)
worksheet = excel.add_worksheet(self.fund_name)
# update date
num_format_pd = pd.DataFrame([],
columns=update_date.columns,
index=['format'])
num_format_pd.ix['format', :] = ''
excel.write_pandas(update_date,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
# fund_basic_info
num_format_pd = pd.DataFrame([],
columns=fund_basic_info.columns,
index=['format'])
num_format_pd.ix['format', :] = ''
excel.write_pandas(fund_basic_info,
worksheet,
begin_row_number=5,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
# fund_strategy_info
num_format_pd = pd.DataFrame([],
columns=fund_strategy_info.columns,
index=['format'])
num_format_pd.ix['format', :] = ''
excel.write_pandas(fund_strategy_info,
worksheet,
begin_row_number=12,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
blank = ' '
excel.insert_merge_range(worksheet, 13, 2, 13, 9,
self.asset_allocation_strategy)
excel.insert_merge_range(worksheet, 14, 2, 14, 9, self.fund_strategy)
excel.insert_merge_range(worksheet, 15, 2, 15, 18, self.outlook)
excel.rewtite_cell_format(worksheet, 13, 2,
blank + self.asset_allocation_strategy, "",
"left")
excel.rewtite_cell_format(worksheet, 14, 2, blank + self.fund_strategy,
"", "left")
excel.rewtite_cell_format(worksheet, 15, 2, blank + self.outlook, "",
"left")
# asset_allocation
num_format_pd = pd.DataFrame([],
columns=asset_allocation.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(asset_allocation,
worksheet,
begin_row_number=17,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
# characteristic_size
num_format_pd = pd.DataFrame([],
columns=characteristic.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
beg_row_num = 24
excel.write_pandas(characteristic,
worksheet,
begin_row_number=beg_row_num,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15, 8])
excel.insert_merge_range(worksheet, beg_row_num + 1, 1,
beg_row_num + 3, 1, "市值分布")
excel.insert_merge_range(worksheet, beg_row_num + 4, 1,
beg_row_num + 6, 1, "板块分布")
excel.insert_merge_range(worksheet, beg_row_num + 7, 1,
beg_row_num + 9, 1, "估值情况")
excel.insert_merge_range(worksheet, beg_row_num + 10, 1,
beg_row_num + 14, 1, "盈利能力")
excel.rewtite_cell_format(worksheet, beg_row_num + 7, 3,
characteristic.iloc[6, 2], "0.00")
excel.rewtite_cell_format(worksheet, beg_row_num + 8, 3,
characteristic.iloc[7, 2], "0.00")
# performance
num_format_pd = pd.DataFrame([],
columns=last_performance.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(last_performance,
worksheet,
begin_row_number=5,
begin_col_number=6,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 10, 10, 10])
# top10 multi_factor
num_format_pd = pd.DataFrame([],
columns=top10_stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.loc['format', '重仓股票(%s)' % self.last_trade_date] = ''
excel.write_pandas(top10_stock,
worksheet,
begin_row_number=17,
begin_col_number=6,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 10, 10, 10])
# industry_allocation
num_format_pd = pd.DataFrame([],
columns=industry_allocation.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.loc['format', '行业配置(%s)' % self.last_trade_date] = ''
excel.write_pandas(industry_allocation,
worksheet,
begin_row_number=29,
begin_col_number=6,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 10, 10, 10])
excel.close()
def write_public_lh(end_date, save_path):
# 参数
###########################################################################################
fund_name = '泰达宏利量化增强'
fund_code = '001733.OF'
fund_type = "公募"
benchmark_code = '000905.SH'
benchmark_name = '中证500'
benchmark_ratio = 0.95
setup_date = '20160830'
date_array = np.array(
[["2019年", '20190101', end_date, '20180930'],
["2018年", "20180101", '20181231', "20170930"],
["2017年", "20170101", '20171231', "20160930"],
["2016年", setup_date, "20161231", setup_date],
["成立(20160830)以来", setup_date, end_date, setup_date]])
benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
["股票型基金", '885012.WI'], ["创业板指", '399006.SZ'],
["WIND全A", '881001.WI']])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入基金表现 和基金排名
###########################################################################################
performance_table = MfcTable().cal_summary_table_sample(
fund_name, fund_code, fund_type, date_array, benchmark_array)
rank0 = FundRank().rank_fund_array2(fund_pct,
bench_pct,
fund_code,
date_array,
"中证500基金",
excess=False)
rank1 = FundRank().rank_fund_array2(fund_pct,
bench_pct,
fund_code,
date_array,
"普通股票型基金",
excess=False)
# rank2 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "中证500基金", excess=True)
# rank3 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "指数增强型基金", excess=True)
performance_table = pd.concat([performance_table, rank0, rank1], axis=0)
col_number = 1
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 写入增强基金表现
###########################################################################################
performance_table = MfcTable().cal_summary_table_enhanced_fund(
fund_name, fund_code, fund_type, date_array, benchmark_code,
benchmark_name, benchmark_ratio)
num_format_pd = pd.DataFrame([],
columns=performance_table.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data),
benchmark_ratio)
# 写入超额收益时间序列
###########################################################################################
excess_cum_return = fs.get_cum_excess_return_series(setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=excess_cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(excess_cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 超额收益图
###########################################################################################
chart_name = fund_name + "累计超额收益（成立以来）"
insert_pos = 'B16'
excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number,
excess_cum_return, chart_name,
insert_pos, sheet_name)
col_number = col_number + excess_cum_return.shape[1] + 2
# 写入基金收益时间序列
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(
setup_date, end_date)
num_format_pd = pd.DataFrame([],
columns=cum_return.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 写入基金收益时间序列图
############################################################################################
series_name = [fund_name, benchmark_name]
chart_name = fund_name + "累计收益（成立以来）"
insert_pos = 'B32'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos,
sheet_name)
excel.close()
###########################################################################################
return True
def write_public_fxwy(end_date, save_path):
# 参数
###########################################################################################
fund_name = '泰达宏利复兴伟业'
fund_code = '001170.OF'
fund_type = "公募"
benchmark_code = '885001.WI'
benchmark_name = '偏股混合基金总指数'
benchmark_code_2 = "000300.SH"
benchmark_name_2 = "沪深300"
benchmark_ratio = 0.95
setup_date = '20150421' # 吴华开始管理 也是成立日
today = datetime.strptime(end_date, "%Y%m%d")
before_1y = datetime(year=today.year-1, month=today.month, day=today.day).strftime("%Y%m%d")
before_2y = datetime(year=today.year-2, month=today.month, day=today.day).strftime("%Y%m%d")
before_3y = datetime(year=today.year-3, month=today.month, day=today.day).strftime("%Y%m%d")
before_5y = datetime(year=today.year-5, month=today.month, day=today.day).strftime("%Y%m%d")
date_array = np.array([["2019年", '20190101', end_date, '20180930'],
["2018年", "20180101", '20181231', "20170930"],
["2017年", "20170101", '20171231', "20160930"],
["2016年", '20160101', '20161231', "20150930"],
["成立以来（吴华管理）", setup_date, end_date, setup_date],
["过去1年", before_1y, end_date, before_1y],
["过去2年", before_2y, end_date, before_2y],
["过去3年", before_3y, end_date, before_3y],
])
benchmark_array = np.array([["沪深300", "000300.SH"],
["中证500", "000905.SH"],
["股票型基金总指数", '885012.WI'],
["WIND全A", '881001.WI']])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入基金表现 和基金排名
###########################################################################################
performance_table = MfcTable().cal_summary_table_sample(fund_name, fund_code, fund_type, date_array, benchmark_array)
rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "偏股混合型基金", excess=False)
rank2 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "灵活配置型基金_60", excess=False)
rank3 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "股票+灵活配置60型基金", excess=False)
performance_table = pd.concat([performance_table, rank1, rank2, rank3], axis=0)
col_number = 1
num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
num_format_pd=num_format_pd, color="red", fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(setup_date, end_date)
benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return2 = fs.get_bencnmark_cum_return_series(setup_date, end_date)
# 写入基金和基准时间序列
###########################################################################################
cum_return = pd.concat([cum_return, cum_return2], axis=1)
cum_return.columns = [fund_name, benchmark_name, benchmark_name_2]
cum_return = cum_return.dropna()
num_format_pd = pd.DataFrame([], columns=cum_return.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
num_format_pd=num_format_pd, color="blue", fillna=True)
# 基金和基准时间序列图
###########################################################################################
chart_name = fund_name + "累计收益（管理以来）"
series_name = [fund_name, benchmark_name, benchmark_name_2]
insert_pos = 'B12'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos, sheet_name)
excel.close()
###########################################################################################
return True
def write_quant12(end_date, save_path):
# 参数
###########################################################################################
fund_name = '光大量化组合12号'
fund_code = fund_name
fund_type = "专户"
benchmark_code = "000905.SH"
benchmark_name = "中证500"
setup_date = "20160714"
date_array = np.array([["2019年", '20190101', end_date],
["2018年", "20180101", '20181231'],
["2017年", "20170101", '20171231'],
["成立(20160714)至2016年末", setup_date, '20161231'],
["成立(20160714)以来", setup_date, end_date]])
benchmark_array = np.array([["沪深300", "000300.SH"],
["中证500", "000905.SH"],
["股票型基金", '885012.WI'],
["WIND全A", '881001.WI']])
from quant.fund.fund import Fund
fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100
# 准备文件
###########################################################################################
file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
sheet_name = fund_name
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
# 写入基金表现 和基金排名
###########################################################################################
performance_table = MfcTable().cal_summary_table_sample(fund_name, fund_code, fund_type, date_array, benchmark_array)
col_number = 1
num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
num_format_pd=num_format_pd, color="red", fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 写入增强基金表现
###########################################################################################
performance_table = MfcTable().cal_summary_table_enhanced_fund(fund_name, fund_code,
fund_type, date_array, benchmark_code, benchmark_name)
num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
num_format_pd=num_format_pd, color="red", fillna=True)
col_number = col_number + performance_table.shape[1] + 2
# 读取基金和基准时间序列
###########################################################################################
fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
# 写入超额收益时间序列
###########################################################################################
excess_cum_return = fs.get_cum_excess_return_series(setup_date, end_date)
num_format_pd = pd.DataFrame([], columns=excess_cum_return.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(excess_cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
num_format_pd=num_format_pd, color="blue", fillna=True)
# 超额收益图
###########################################################################################
chart_name = fund_name + "累计超额收益（成立以来）"
insert_pos = 'B12'
excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number, excess_cum_return,
chart_name, insert_pos, sheet_name)
col_number = col_number + excess_cum_return.shape[1] + 2
# 写入基金收益时间序列
###########################################################################################
benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
cum_return = fs.get_fund_and_bencnmark_cum_return_series(setup_date, end_date)
num_format_pd = pd.DataFrame([], columns=cum_return.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
num_format_pd=num_format_pd, color="blue", fillna=True)
# 写入基金收益时间序列图
############################################################################################
series_name = [fund_name, benchmark_name]
chart_name = fund_name + "累计收益（成立以来）"
insert_pos = 'B26'
excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
series_name, chart_name, insert_pos, sheet_name)
excel.close()
###########################################################################################
return True