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 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()