Exemplo n.º 1
0
    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()
Exemplo n.º 2
0
    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()