Exemple #1
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_public_fxwy(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name = '泰达宏利复兴伟业'
    fund_code = '001170.OF'
    fund_type = "公募"

    benchmark_code = '885001.WI'
    benchmark_name = '偏股混合基金总指数'
    benchmark_code_2 = "000300.SH"
    benchmark_name_2 = "沪深300"
    benchmark_ratio = 0.95

    setup_date = '20150421'  # 吴华开始管理 也是成立日
    today = datetime.strptime(end_date, "%Y%m%d")
    before_1y = datetime(year=today.year-1, month=today.month, day=today.day).strftime("%Y%m%d")
    before_2y = datetime(year=today.year-2, month=today.month, day=today.day).strftime("%Y%m%d")
    before_3y = datetime(year=today.year-3, month=today.month, day=today.day).strftime("%Y%m%d")
    before_5y = datetime(year=today.year-5, month=today.month, day=today.day).strftime("%Y%m%d")

    date_array = np.array([["2019年", '20190101', end_date, '20180930'],
                           ["2018年", "20180101", '20181231', "20170930"],
                           ["2017年", "20170101", '20171231', "20160930"],
                           ["2016年", '20160101', '20161231', "20150930"],
                           ["成立以来(吴华管理)", setup_date, end_date, setup_date],
                           ["过去1年", before_1y, end_date, before_1y],
                           ["过去2年", before_2y, end_date, before_2y],
                           ["过去3年", before_3y, end_date, before_3y],
                           ])

    benchmark_array = np.array([["沪深300", "000300.SH"],
                                ["中证500", "000905.SH"],
                                ["股票型基金总指数", '885012.WI'],
                                ["WIND全A", '881001.WI']])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name + '.xlsx')
    sheet_name = fund_name
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_sample(fund_name, fund_code, fund_type, date_array, benchmark_array)
    rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "偏股混合型基金", excess=False)
    rank2 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "灵活配置型基金_60", excess=False)
    rank3 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "股票+灵活配置60型基金", excess=False)
    performance_table = pd.concat([performance_table, rank1, rank2, rank3], axis=0)

    col_number = 1
    num_format_pd = pd.DataFrame([], columns=performance_table.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="red", fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(setup_date, end_date)

    benchmark_data = Index().get_index_factor(benchmark_code_2, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return2 = fs.get_bencnmark_cum_return_series(setup_date, end_date)

    # 写入基金和基准时间序列
    ###########################################################################################
    cum_return = pd.concat([cum_return, cum_return2], axis=1)
    cum_return.columns = [fund_name, benchmark_name, benchmark_name_2]
    cum_return = cum_return.dropna()

    num_format_pd = pd.DataFrame([], columns=cum_return.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return, worksheet, begin_row_number=0, begin_col_number=col_number,
                       num_format_pd=num_format_pd, color="blue", fillna=True)

    # 基金和基准时间序列图
    ###########################################################################################
    chart_name = fund_name + "累计收益(管理以来)"
    series_name = [fund_name, benchmark_name, benchmark_name_2]
    insert_pos = 'B12'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos, sheet_name)
    excel.close()
    ###########################################################################################
    return True
def write_public_zz500_adjust(end_date, save_path):

    # 参数
    ###########################################################################################
    fund_name_adjust = '泰达宏利中证500_adjust'
    fund_code_adjust = '162216.OF_adjust'
    fund_name = '泰达宏利中证500'
    fund_code = '162216.OF'
    fund_type = "公募"

    benchmark_code = '000905.SH'
    benchmark_name = '中证500'
    benchmark_ratio = 0.95

    setup_date = '20141003'
    today = datetime.strptime(end_date, "%Y%m%d")
    before_1y = datetime(year=today.year - 1, month=today.month,
                         day=today.day).strftime("%Y%m%d")
    before_2y = datetime(year=today.year - 2, month=today.month,
                         day=today.day).strftime("%Y%m%d")
    before_3y = datetime(year=today.year - 3, month=today.month,
                         day=today.day).strftime("%Y%m%d")

    date_array = np.array([
        ["2019年", '20190101', end_date, '20180930'],
        ["2018年", "20180101", '20181231', "20170930"],
        ["2017年", "20170101", '20171231', "20160930"],
        ["2016年", "20160101", "20161231", "20150930"],
        ["2015年", "20150101", "20151231", "20150101"],
        ["管理(20141003)以来", setup_date, end_date, setup_date],
        ["2015年以来", "20150101", end_date, setup_date],
        ["过去1年", before_1y, end_date, before_1y],
        ["过去2年", before_2y, end_date, before_2y],
        ["过去3年", before_3y, end_date, before_3y],
    ])

    benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
                                ["WIND全A", '881001.WI']])

    from quant.fund.fund import Fund
    fund_pct = Fund().get_fund_factor("Repair_Nav_Pct")
    bench_pct = Fund().get_fund_factor("Fund_Bench_Pct") * 100

    # 准备文件
    ###########################################################################################
    file_name = os.path.join(save_path, "OutFile", fund_name_adjust + '.xlsx')
    sheet_name = fund_name_adjust
    excel = WriteExcel(file_name)
    worksheet = excel.add_worksheet(sheet_name)

    # 写入基金表现 和基金排名
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_sample(
        fund_name, fund_code, fund_type, date_array, benchmark_array)
    # rank0 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "wind", excess=False)
    # rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "被动指数型基金", excess=True)
    rank2 = FundRank().rank_fund_array2(fund_pct,
                                        bench_pct,
                                        fund_code,
                                        date_array,
                                        "中证500基金",
                                        excess=False)
    performance_table = pd.concat([performance_table, rank2], axis=0)

    col_number = 1
    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 写入增强基金表现
    ###########################################################################################
    performance_table = MfcTable().cal_summary_table_enhanced_fund(
        fund_name, fund_code, fund_type, date_array, benchmark_code,
        benchmark_name, benchmark_ratio)

    num_format_pd = pd.DataFrame([],
                                 columns=performance_table.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(performance_table,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="red",
                       fillna=True)
    col_number = col_number + performance_table.shape[1] + 2

    # 读取基金和基准时间序列
    ###########################################################################################
    fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type)

    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data),
                         benchmark_ratio)

    # 写入超额收益时间序列
    ###########################################################################################
    excess_cum_return = fs.get_cum_excess_return_series("20150101", end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=excess_cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(excess_cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 超额收益图
    ###########################################################################################
    chart_name = fund_name + "累计超额收益(2015年以来)"
    insert_pos = 'B12'
    excel.line_chart_one_series_with_linear_plot(worksheet, 0, col_number,
                                                 excess_cum_return, chart_name,
                                                 insert_pos, sheet_name)

    col_number = col_number + excess_cum_return.shape[1] + 2

    # 写入基金收益时间序列
    ###########################################################################################
    benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"])
    fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data))
    cum_return = fs.get_fund_and_bencnmark_cum_return_series(
        "20150101", end_date)

    num_format_pd = pd.DataFrame([],
                                 columns=cum_return.columns,
                                 index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    excel.write_pandas(cum_return,
                       worksheet,
                       begin_row_number=0,
                       begin_col_number=col_number,
                       num_format_pd=num_format_pd,
                       color="blue",
                       fillna=True)

    # 写入基金收益时间序列图
    ############################################################################################
    series_name = [fund_name, benchmark_name]
    chart_name = fund_name + "累计收益(2015年以来)"
    insert_pos = 'B26'
    excel.line_chart_time_series_plot(worksheet, 0, col_number, cum_return,
                                      series_name, chart_name, insert_pos,
                                      sheet_name)
    excel.close()
    ###########################################################################################
    return True
def write_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
Exemple #5
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
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
Exemple #7
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