示例#1
0
def rank_all_manager(end_date):

    """ 所有基金经理业绩 """

    path = r'E:\Data\mfcteda_data\基金经理'
    param_file = os.path.join(path, '泰达宏利基金经理排名.xlsx')
    data = pd.read_excel(param_file, index_col=[0])
    data = data.iloc[0:19, :]
    performance = 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_manager(end_date, fund_code, rank_pool, fund_name, mage_date)
        performance = pd.concat([performance, rank_percent], axis=0)

    file = os.path.join(path, '泰达宏利基金经理排名%s.xlsx' % end_date)
    we = WriteExcel(file)

    # write pandas
    sheet_name = "泰达宏利基金经理排名百分比"
    num_format_pd = pd.DataFrame([], columns=performance.columns, index=['format'])
    num_format_pd.ix['format', :] = '0.00%'
    worksheet = we.add_worksheet(sheet_name)
    we.write_pandas(performance, 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), len(performance.columns) + 1,
                          reverse=True)
    we.close()
示例#2
0
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()
示例#3
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()