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