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