Exemple #1
0
    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)
Exemple #2
0
    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()
Exemple #3
0
    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()
Exemple #4
0
    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()
Exemple #5
0
    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()
Exemple #6
0
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()
Exemple #7
0
    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)
Exemple #8
0
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
Exemple #9
0
    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)
Exemple #10
0
    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
Exemple #12
0
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()
Exemple #16
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()
    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()
Exemple #18
0
    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()
Exemple #19
0
    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)
Exemple #20
0
    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()
Exemple #21
0
    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()
Exemple #22
0
    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()
Exemple #24
0
    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)
Exemple #25
0
    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
Exemple #29
0
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