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