def save_file_asset_daily(self, data, name, cal_type, date):
""" 资产收益（股票、新股、期货）计算过程文件存储 """
sub_path = os.path.join(self.data_path, self.fund_name, "资产收益", name)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
file = "%s收益分解_%s_%s_%s.xlsx" % (name, cal_type, self.fund_name, date)
file_name = os.path.join(sub_path, file)
num_format_pd = pd.DataFrame([],
columns=data.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(self.fund_name)
excel.write_pandas(data,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
excel.close()
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 get_all_fund_index(self, beg_date, end_date):
""" 得到所有基金的得分数据汇总 """
file = os.path.join(self.data_path, "基金经理考核范围.xlsx")
all_data = pd.read_excel(file, index_col=[0])
all_data.index = all_data['代码']
result = pd.DataFrame([])
for i_fund in range(0, len(all_data)):
my_fund_code = all_data.index[i_fund]
fund_pool_name = all_data.loc[my_fund_code, "考核分类"]
fund_type = all_data.loc[my_fund_code, "类型2"]
add_data = self.get_fund_index(fund_pool_name, my_fund_code, beg_date, end_date, fund_type)
result = pd.concat([result, add_data], axis=0)
result = pd.concat([all_data, result], axis=1)
result = result.drop(labels=['代码', '考核标准', '收益名次', '仓位'], axis=1)
file = os.path.join(self.data_path, "基金得分_%s_%s.xlsx" % (beg_date, end_date))
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
num_format_pd.loc['format', ['信息比率']] = '0.00'
num_format_pd.loc['format', ['现任经理管理开始日']] = '0'
sheet_name = "%s_%s" % (beg_date, end_date)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
def generate_excel_history(self, end_date):
""" 大股东增减持历史信息 2017年至今 """
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series("20150101", end_date, "M")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -20)
print("Major Holder Deal %s %s" % (bg_date, ed_date))
result_add = self.major_holder_deal_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
filename = os.path.join(sub_path, '重要股东二级市场交易历史.xlsx')
excel = WriteExcel(filename)
sheet_name = "读写EXCEL测试"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["增减持金额(亿)", "沪深300"],
chart_name="历史每月大股东增减持总和",
insert_pos="O2",
cat_beg="D2",
cat_end="D40",
val_beg_list=["E2", "G2"],
val_end_list=["E40", "G40"])
excel.close()
def cal_summary(self, all_beg_date=None, all_end_date=None):
"""
计算组合在每年的 收益率、波动率等表现情况
"""
self.get_port_return()
all_port_return = self.port_return
all_port_return["Year"] = all_port_return.index.map(lambda x: x[0:4])
if all_beg_date is None:
all_beg_date = all_port_return.index[0]
if all_end_date is None:
all_end_date = all_port_return.index[-1]
year_list = list(set(all_port_return["Year"]))
year_list.sort()
for i_year in range(len(year_list)):
year = str(year_list[i_year])
port_return = all_port_return.loc[all_port_return["Year"] ==
year, :]
beg_date = port_return.index[0]
end_date = port_return.index[-1]
if i_year == 0:
data = self.cal_summary_period(beg_date, end_date)
else:
data_add = self.cal_summary_period(beg_date, end_date)
data = pd.concat([data, data_add], axis=1)
data_add = self.cal_summary_period(all_beg_date, all_end_date)
data_add.columns = ['All']
data = pd.concat([data, data_add], axis=1)
data = data.T
sub_path = os.path.join(self.save_path, self.port_name)
file = os.path.join(sub_path, self.port_name + '_Summary.xlsx')
excel = WriteExcel(file)
worksheet = excel.add_worksheet("Summary")
num_format_pd = pd.DataFrame([],
columns=data.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(data,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="red",
fillna=True)
excel.close()
def check_alpha_factor_update_date(self):
""" 检查所有Alpha因子最后更新时间 """
factor_name_list = AlphaFactor().get_all_alpha_factor_name()
result = pd.DataFrame([],
columns=['开始日期', '结束日期'],
index=factor_name_list)
for i in range(0, len(factor_name_list)):
factor_name = factor_name_list[i]
try:
print("######### 检查更新日期 %s 数据 ############" % factor_name)
factor = AlphaFactor().get_alpha_factor_exposure(factor_name)
factor = factor.T.dropna(how='all').T
result.loc[factor_name, '开始日期'] = factor.columns[0]
result.loc[factor_name, '结束日期'] = factor.columns[-1]
result.loc[factor_name,
"最后一天有效数据个数"] = factor.iloc[:, -1].count()
result.loc[factor_name, "最后一天股票个数"] = len(factor.iloc[:, -1])
result.loc[factor_name,
"最后一天有效数据比率"] = factor.iloc[:, -1].count() / len(
factor.iloc[:, -1])
except Exception as e:
print(e)
result.loc[factor_name, '开始日期'] = ""
result.loc[factor_name, '结束日期'] = ""
result.loc[factor_name, "最后一天有效数据个数"] = ""
result.loc[factor_name, "最后一天股票个数"] = ""
result.loc[factor_name, "最后一天有效数据比率"] = ""
print("########### %s 检查更新数据 为空 ！！！###########" % factor_name)
out_file = os.path.join(self.data_path, "AlphaFactorUpdateDate.xlsx")
we = WriteExcel(out_file)
ws = we.add_worksheet("更新数据")
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.loc['format', :] = '0'
num_format_pd.loc['format', ['最后一天有效数据比率']] = '0.00%'
we.write_pandas(result,
ws,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
we.close()
def generate_excel_history(self, end_date):
""" 陆股通信息 输出到Excel """
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series("20170301", end_date, "M")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -20)
print("Hk Inflow Period %s %s" % (bg_date, ed_date))
result_add = self.hk_inflow_period(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
result['累计净流入(亿元)'] = result['期间净流入(亿元)'].cumsum()
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '陆股通北上资金历史.xlsx')
excel = WriteExcel(filename)
sheet_name = "陆股通北上资金"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.close()
def generate_excel_history(self, end_date):
""" 历史ETF净申购 和沪深300"""
# 一段时间内增减持额时间序列
date_series = Date().get_trade_date_series("20080101", end_date, "M")
result_all = pd.DataFrame([])
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -20)
print("ETF Fund %s %s" % (bg_date, ed_date))
result_add = self.get_etf_data_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result_all = pd.concat([result_all, result_add], axis=0)
ed_date = bg_date
result_all = result_all.sort_index()
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
filename = os.path.join(sub_path, 'ETF流入历史.xlsx')
excel = WriteExcel(filename)
sheet_name = "ETF流入"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result_all.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result_all,
worksheet,
begin_row_number=0,
begin_col_number=20,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.close()
def save_file_excel(self, new_data, type, name, num_format):
"""
将一段时间内的每日拆分写入文件（增量写入）
将一段时间内的每日暴露写入文件（增量写入）
"""
if len(new_data) > 0:
save_path = os.path.join(self.data_path, self.fund_name, "每日汇总")
if not os.path.exists(save_path):
os.makedirs(save_path)
file = '%s_%s_%s.xlsx' % (self.fund_name, type, name)
file_name = os.path.join(save_path, file)
print("写入", file_name)
if os.path.exists(file_name):
old_data = pd.read_excel(file_name, index_col=[0])
old_data.index = old_data.index.map(str)
data = FactorOperate().pandas_add_row(old_data, new_data)
else:
data = new_data
num_format_pd = pd.DataFrame([],
columns=data.columns,
index=['format'])
num_format_pd.loc['format', :] = num_format
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(self.fund_name)
excel.write_pandas(data,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
excel.close()
else:
save_path = os.path.join(self.data_path, self.fund_name, "每日汇总")
file = '%s_%s_%s.xlsx' % (self.fund_name, type, name)
file_name = os.path.join(save_path, file)
print("写入数据为0", file_name)
def load_ipo_data(self, beg_date):
""" 下载IPO数据 上市日期 发行价 中签率 申购上限 等等"""
data = self.get_new_stock_list(beg_date)
code_str = ','.join(data.index.values)
data = w.wss(code_str,
"sec_name,ipo_date,ipo_price,ipo_cashratio,ipo_lotteryrate_abc,ipo_otc_cash_pct,ipo_op_uplimit",
"instituteType=1")
data_pd = pd.DataFrame(data.Data, index=data.Fields, columns=data.Codes).T
data_pd["IPO_DATE"] = data_pd["IPO_DATE"].map(lambda x: x.strftime('%Y-%m-%d'))
data_pd.columns = ['股票名称', '上市日期', '发行价格', '网上中签率(%)',
'网下A类中签率(%)', '网下总计中签率(%)', '申购上限数量(万股)']
data_pd['申购上限金额(万元)'] = data_pd["申购上限数量(万股)"] * data_pd['发行价格']
data_pd = data_pd.dropna()
data_pd = data_pd.sort_values(by=['上市日期'], ascending=True)
for i_code in range(0, len(data_pd)):
code = data_pd.index.values[i_code]
ipo_date = data_pd.ix[i_code, '上市日期']
open_date, open_pct, open_price = self.get_open_date_pct(code, ipo_date)
data_pd.ix[i_code, '开板日期'] = open_date
data_pd.ix[i_code, '开板价格'] = open_price
data_pd.ix[i_code, '开板收益'] = open_pct
print(data_pd)
file = os.path.join(self.data_path, 'ipo_data.xlsx')
data = pd.read_excel(file, index_col=[1])
data = data.T.dropna(how='all').T
concat_data = FactorOperate().pandas_add_row(data, data_pd)
concat_data = concat_data.sort_values(by=['上市日期'], ascending=True)
excel = WriteExcel(file)
worksheet = excel.add_worksheet("新股检测")
excel.write_pandas(concat_data, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=None, color="orange", fillna=True)
excel.close()
def generate_excel(self, end_date):
""" 陆股通信息 输出到Excel """
# beg_date = Date().get_trade_date_offset(end_date, -60)
# beg_1m_date = Date().get_trade_date_offset(end_date, -20)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '股票基金风格仓位.xlsx')
excel = WriteExcel(filename)
###################################################################################
sheet_name = "普通股票型风格仓位"
worksheet = excel.add_worksheet(sheet_name)
date_1m = Date().get_trade_date_offset(end_date, -21)
style_series, position_series = self.get_fund_style_position("885000.WI", date_1m, end_date)
num_format_pd = pd.DataFrame([], columns=style_series.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(style_series, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="orange", fillna=True)
num_format_pd = pd.DataFrame([], columns=position_series.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(position_series, worksheet, begin_row_number=0, begin_col_number=12,
num_format_pd=num_format_pd, color="orange", fillna=True)
###################################################################################
sheet_name = "偏股混合型风格仓位"
worksheet = excel.add_worksheet(sheet_name)
style_series, position_series = self.get_fund_style_position("885001.WI", date_1m, end_date)
num_format_pd = pd.DataFrame([], columns=style_series.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(style_series, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="orange", fillna=True)
num_format_pd = pd.DataFrame([], columns=position_series.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(position_series, worksheet, begin_row_number=0, begin_col_number=12,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.close()
def plan(self, benchmark_array, invest_days, invest_money,
beg_date, end_date, file_prefix):
""" 定投计划回测 """
data = self.get_benchmark_pct(benchmark_array)
data = data.loc[beg_date:end_date, :]
beg_date = data.index[0]
end_date = data.index[-1]
filename = "%s_%s_%s.xlsx" % (file_prefix, beg_date, end_date)
data['星期数'] = data.index.map(lambda x: datetime.strptime(x, '%Y%m%d').strftime("%A"))
data['当日投资金额'] = data['星期数'].map(lambda x: invest_money if x == invest_days else 0)
data['累计投资金额'] = data['当日投资金额'].cumsum()
data['当日投资收益'] = data['累计投资金额'] * data['投资指数日收益']
data['累计投资收益'] = data['当日投资收益'].cumsum()
data['累计投资收益率'] = data['累计投资收益'] / data['累计投资金额']
file = os.path.join(self.data_path, filename)
excel = WriteExcel(file)
worksheet = excel.add_worksheet("定投")
num_format_pd = pd.DataFrame([], columns=data.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', ['累计投资收益率', '投资指数日收益']] = '0.00%'
excel.write_pandas(data, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="blue", fillna=True)
data_select = pd.DataFrame(data['累计投资收益率'])
num_format_pd = pd.DataFrame([], columns=data_select.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(data_select, worksheet, begin_row_number=0, begin_col_number=10,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.line_chart_time_series_plot(worksheet, 0, 10, data_select,
["累计投资收益率"], file_prefix + '累计投资收益率', "K2", "定投")
excel.close()
def generate_excel(self, end_date):
""" 大股东增减持信息 输出到Excel """
beg_date = "20050101"
# 一段时间内市值占比时间序列
result, ratio, ratio_free = self.get_all_data(beg_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '市场整体占比.xlsx')
excel = WriteExcel(filename)
sheet_name = "市场整体占比"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
num_format_pd = pd.DataFrame([], columns=ratio.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(ratio, worksheet, begin_row_number=0, begin_col_number=15,
num_format_pd=num_format_pd, color="red", fillna=True)
num_format_pd = pd.DataFrame([], columns=ratio_free.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(ratio_free, worksheet, begin_row_number=0, begin_col_number=30,
num_format_pd=num_format_pd, color="red", fillna=True)
summary = pd.concat([result.T[result.index[-1]], ratio.T[ratio.index[-1]],
ratio_free.T[ratio_free.index[-1]]], axis=1)
summary.columns = ["市值", 'A股总市值占比', 'A股流通市值占比']
num_format_pd = pd.DataFrame([], columns=summary.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', ['市值']] = '0.00'
excel.write_pandas(summary, worksheet, begin_row_number=0, begin_col_number=46,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()
def write_excel(self):
""" 写入Excel """
# cal need data
fund_basic_info = self.get_fund_basic_info()
update_date = self.get_update_date()
asset_allocation = self.get_fund_asset_allocation()
top10_stock = self.get_fund_top10_stock()
industry_allocation = self.get_fund_industry_allocation()
fund_strategy_info = self.get_fund_strategy()
last_performance = self.get_fund_last_performance()
characteristic = self.get_stock_characteristic()
# write xlsx
sub_path = os.path.join(self.data_path, self.last_trade_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
xlsx_file = os.path.join(sub_path,
'%s_%s.xlsx' % (self.prefix, self.fund_name))
excel = WriteExcel(xlsx_file)
worksheet = excel.add_worksheet(self.fund_name)
# update date
num_format_pd = pd.DataFrame([],
columns=update_date.columns,
index=['format'])
num_format_pd.ix['format', :] = ''
excel.write_pandas(update_date,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
# fund_basic_info
num_format_pd = pd.DataFrame([],
columns=fund_basic_info.columns,
index=['format'])
num_format_pd.ix['format', :] = ''
excel.write_pandas(fund_basic_info,
worksheet,
begin_row_number=5,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
# fund_strategy_info
num_format_pd = pd.DataFrame([],
columns=fund_strategy_info.columns,
index=['format'])
num_format_pd.ix['format', :] = ''
excel.write_pandas(fund_strategy_info,
worksheet,
begin_row_number=12,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
blank = ' '
excel.insert_merge_range(worksheet, 13, 2, 13, 9,
self.asset_allocation_strategy)
excel.insert_merge_range(worksheet, 14, 2, 14, 9, self.fund_strategy)
excel.insert_merge_range(worksheet, 15, 2, 15, 18, self.outlook)
excel.rewtite_cell_format(worksheet, 13, 2,
blank + self.asset_allocation_strategy, "",
"left")
excel.rewtite_cell_format(worksheet, 14, 2, blank + self.fund_strategy,
"", "left")
excel.rewtite_cell_format(worksheet, 15, 2, blank + self.outlook, "",
"left")
# asset_allocation
num_format_pd = pd.DataFrame([],
columns=asset_allocation.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(asset_allocation,
worksheet,
begin_row_number=17,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15])
# characteristic_size
num_format_pd = pd.DataFrame([],
columns=characteristic.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
beg_row_num = 24
excel.write_pandas(characteristic,
worksheet,
begin_row_number=beg_row_num,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 15, 8])
excel.insert_merge_range(worksheet, beg_row_num + 1, 1,
beg_row_num + 3, 1, "市值分布")
excel.insert_merge_range(worksheet, beg_row_num + 4, 1,
beg_row_num + 6, 1, "板块分布")
excel.insert_merge_range(worksheet, beg_row_num + 7, 1,
beg_row_num + 9, 1, "估值情况")
excel.insert_merge_range(worksheet, beg_row_num + 10, 1,
beg_row_num + 14, 1, "盈利能力")
excel.rewtite_cell_format(worksheet, beg_row_num + 7, 3,
characteristic.iloc[6, 2], "0.00")
excel.rewtite_cell_format(worksheet, beg_row_num + 8, 3,
characteristic.iloc[7, 2], "0.00")
# performance
num_format_pd = pd.DataFrame([],
columns=last_performance.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(last_performance,
worksheet,
begin_row_number=5,
begin_col_number=6,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 10, 10, 10])
# top10 multi_factor
num_format_pd = pd.DataFrame([],
columns=top10_stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.loc['format', '重仓股票(%s)' % self.last_trade_date] = ''
excel.write_pandas(top10_stock,
worksheet,
begin_row_number=17,
begin_col_number=6,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 10, 10, 10])
# industry_allocation
num_format_pd = pd.DataFrame([],
columns=industry_allocation.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.loc['format', '行业配置(%s)' % self.last_trade_date] = ''
excel.write_pandas(industry_allocation,
worksheet,
begin_row_number=29,
begin_col_number=6,
num_format_pd=num_format_pd,
color="blue",
fillna=True,
need_index=False,
header_font_color="white",
cell_delta=0.5,
cell_basic=4.0,
cell_len_list=[15, 10, 10, 10])
excel.close()
def filter_fund_pool(self, index_code, index_name, end_date,
track_error_up):
""" 得到沪深300 、中证500基金池 """
# 参数
# end_date = "20181231"
# index_name = '沪深300'
# index_code = '000300.SH'
# track_error_up = 0.03
beg_date = Date().get_trade_date_offset(end_date, -250)
# 读取数据
fund_nav = Fund().get_fund_factor("Repair_Nav")
index_close = Index().get_index_factor(index_code, attr=['CLOSE'])
index_close.columns = [index_code]
result = pd.DataFrame([],
index=fund_nav.columns,
columns=['跟踪误差', '数据长度'])
# 计算最近1年跟踪误差数据
fund_nav = fund_nav.loc[index_close.index, :]
fund_pct = fund_nav.pct_change()
index_pct = index_close.pct_change()
index_pct = index_pct[index_code]
fund_excess_pct = fund_pct.sub(index_pct, axis='index')
fund_excess_pct_period = fund_excess_pct.loc[beg_date:end_date, :]
result.loc[:, "数据长度"] = fund_excess_pct_period.count()
result.loc[:, "跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
# 筛选
result = result.dropna()
result = result[result['数据长度'] > self.data_min_length]
result = result[result['跟踪误差'] < track_error_up]
# concat fund basic info
data_pd = Fund().get_wind_fund_info()
data_pd = data_pd[[
'BenchMark', 'Name', 'FullName', 'SetupDate', 'InvestType'
]]
data_pd.columns = ['基金基准', '基金简称', '基金全称', '上市日期', '基金类型']
data = pd.concat([data_pd, result], axis=1)
data = data.dropna()
data = data[data["基金基准"].map(lambda x: index_name in x)]
data = data[data["上市日期"] < beg_date]
data = data[data["基金全称"].map(lambda x: "交易型开放式指数" not in x)]
data = data[data["基金全称"].map(lambda x: "联接" not in x)]
data['A类基金'] = data['基金简称'].map(Fund().if_a_fund)
data = data[data['A类基金'] == 'A类基金']
# 输出结果
out_path = os.path.join(self.data_path, "filter_fund_pool")
file_name = os.path.join(out_path, '基金最终筛选池_' + index_name + '.xlsx')
sheet_name = "基金筛选池"
num_format_pd = pd.DataFrame([],
columns=data.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', '跟踪误差'] = '0.00%'
num_format_pd.ix['format', '数据长度'] = '0'
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(data,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def fund_score_date(self,
index_code,
index_name,
end_date,
halfyear_normal_date,
adjust=False):
""" 从上述4个方面打分 """
# 读取 基金指标
print("Score %s %s %s" % (index_name, end_date, halfyear_normal_date))
sub_path = os.path.join(self.data_path, 'cal_fund_factor', index_name)
filename = os.path.join(
sub_path, '基金指标_' + index_name + '_' + end_date + '.xlsx')
fund_factor = pd.read_excel(filename, index_col=[0], encoding='gbk')
# 读取 风格暴露
sub_path = os.path.join(self.data_path, 'exposure', index_name)
filename = os.path.join(
sub_path,
'BARRA风格暴露_' + index_name + '_' + halfyear_normal_date + '.xlsx')
exposure = pd.read_excel(filename, index_col=[0], encoding='gbk')
exposure = exposure.dropna()
exposure_diff = exposure - exposure.ix[index_code, :]
exposure = exposure_diff.abs()
exp_col = [
'贝塔', '市净率', '市盈率', '成长', '杠杆', '流动性', '动量', '残差波动率', '市值', '非线性市值'
]
exposure.columns = exp_col
exposure_diff.columns = exp_col
exposure_diff = exposure_diff.round(3)
exposure_diff = exposure_diff.drop(index_code)
fund_code_list = list(
set(fund_factor.index) & set(exposure_diff.index))
fund_code_list.sort()
fund_factor = fund_factor.loc[fund_code_list, :]
exposure_diff = exposure_diff.loc[fund_code_list, :]
# 计算跟踪误差得分
result = pd.DataFrame([], index=fund_factor.index)
result.loc[:, '基金全称'] = fund_factor.loc[:, '基金全称']
if index_name == '中证500':
val_25 = max(0.03, fund_factor.loc[:, '跟踪误差'].quantile(0.20))
val_75 = min(0.08, fund_factor.loc[:, '跟踪误差'].quantile(0.80))
else:
val_25 = max(0.02, fund_factor.loc[:, '跟踪误差'].quantile(0.20))
val_75 = min(0.05, fund_factor.loc[:, '跟踪误差'].quantile(0.80))
result.loc[:, '跟踪误差'] = fund_factor.loc[:, '跟踪误差']
if adjust and "001733.OF" in result.index:
result.loc["001733.OF", '跟踪误差'] -= 0.001
result.loc[:, '跟踪误差得分'] = fund_factor.loc[:, '跟踪误差'].map(
lambda x: self.score_quarter_reverse(x, val_25, val_75)).round(2)
# 计算超额收益得分
if index_name == '中证500':
val_25 = max(0.00, fund_factor.loc[:, '超额收益'].quantile(0.20))
val_75 = min(0.15, fund_factor.loc[:, '超额收益'].quantile(0.80))
else:
val_25 = max(0.00, fund_factor.loc[:, '超额收益'].quantile(0.20))
val_75 = min(0.10, fund_factor.loc[:, '超额收益'].quantile(0.80))
result.loc[:, '超额收益'] = fund_factor.loc[:, '超额收益']
if adjust and "001733.OF" in result.index:
result.loc["001733.OF", '超额收益'] += 0.001
result.loc[:, '超额收益得分'] = fund_factor.loc[:, '超额收益'].map(
lambda x: self.score_quarter(x, val_25, val_75)).round(2)
# 计算信息比率得分
val_25 = fund_factor.loc[:, '信息比率'].quantile(0.20)
val_75 = fund_factor.loc[:, '信息比率'].quantile(0.80)
result.ix[:, '信息比率'] = fund_factor.loc[:, '信息比率'].round(2)
result.loc[:, '信息比率得分'] = fund_factor.loc[:, '信息比率'].map(
lambda x: self.score_quarter(x, val_25, val_75)).round(2)
# 计算风格偏露得分（十个风格 每个打1分，10个总共打10分，再乘以0.5）
result = result.dropna(subset=["基金全称"])
result.loc[:, '风格暴露得分'] = 0.0
result = pd.concat([result, exposure_diff], axis=1)
for i_col in range(len(exposure_diff.columns)):
col = exposure_diff.columns[i_col]
result.loc[:, '风格暴露得分'] += result.loc[:, col].map(self.score_ex)
result.loc[:, '风格暴露得分'] = result.loc[:, '风格暴露得分'] * 0.5
if adjust and "001733.OF" in result.index:
result.loc["001733.OF",
'风格暴露得分'] = min(5,
result.loc["001733.OF", '风格暴露得分'] + 0.5)
val_25 = result.loc[:, '风格暴露得分'].quantile(0.20)
val_75 = result.loc[:, '风格暴露得分'].quantile(0.80)
result.loc[:, '风格暴露得分'] = result.loc[:, '风格暴露得分'].map(
lambda x: self.score_quarter(x, val_25, val_75)).round(2)
# 计算总得分（后期调整沪深300、中证500权重不一样，可以给中证500在超额收益上多一点权重）
result['总得分'] = 0.0
if index_name == "中证500":
result['总得分'] += result['跟踪误差得分'] * 0.10
result['总得分'] += result['超额收益得分'] * 0.40
result['总得分'] += result['信息比率得分'] * 0.20
result['总得分'] += result['风格暴露得分'] * 0.30
else:
result['总得分'] += result['跟踪误差得分'] * 0.10
result['总得分'] += result['超额收益得分'] * 0.40
result['总得分'] += result['信息比率得分'] * 0.30
result['总得分'] += result['风格暴露得分'] * 0.20
result = result.sort_values(by=['总得分'], ascending=False)
col = [
"基金全称", '总得分', '跟踪误差', '跟踪误差得分', '超额收益', '超额收益得分', '信息比率',
'信息比率得分', '风格暴露得分'
]
col.extend(exposure_diff[0:10])
result = result[col]
result = result.dropna()
# 写到EXCEL表
sub_path = os.path.join(self.data_path, 'score_fund', index_name)
filename = os.path.join(
sub_path, '基金得分_' + index_name + '_' + end_date + '.xlsx')
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', ['跟踪误差', '超额收益']] = '0.00%'
sheet_name = "基金得分"
excel = WriteExcel(filename)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def fund_index_exposure(self,
index_code,
index_name,
end_date,
halfyear_date,
recal_exposure=False):
""" 计算和得到 上个半年报或者年报指数和基金的Barra暴露 """
# index_name = '沪深300'
# index_code = "000300.SH"
file = os.path.join(self.data_path, 'filter_fund_pool',
'基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
halfyear_trade_date = Date().get_normal_date_month_end_day(
halfyear_date)
index_exposure = Index().get_index_exposure_date(
index_code, halfyear_trade_date)
exposure_fund = pd.DataFrame()
for i_fund in range(0, len(fund_code.index)):
fund = fund_code.index[i_fund]
beg_date = Date().get_trade_date_offset(end_date, -260)
if recal_exposure:
Fund().cal_fund_holder_exposure_halfyear(fund_code=fund,
beg_date=beg_date,
end_date=end_date)
exposure_add = Fund().get_fund_holder_exposure_halfyear(
fund, type_list=['STYLE'])
try:
exposure_add = pd.DataFrame(exposure_add.loc[halfyear_date, :])
exposure_add.columns = [fund]
except Exception as e:
print(e)
exposure_add = pd.DataFrame([], columns=[fund])
exposure_fund = pd.concat([exposure_fund, exposure_add], axis=1)
exposure_fund = exposure_fund.T
exposure = pd.concat([exposure_fund, index_exposure], axis=0)
exposure = exposure.dropna()
file = "BARRA风格暴露_" + index_name + "_" + halfyear_date + ".xlsx"
file_name = os.path.join(self.data_path, "exposure", index_name, file)
sheet_name = "BARRA风格暴露"
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(exposure,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def calculate_fund_factor(self, index_code, index_name, end_date):
""" 计算基金最近一段时间内 跟踪误差、超额收益、信息比率 """
# 参数
# index_code = '000905.SH'
# index_name = '中证500'
# end_date = '20151231'
beg_date = Date().get_trade_date_offset(end_date, -self.data_length)
# 读取数据 基金池 基金净值数据 指数收盘价数据
file = os.path.join(self.data_path, 'filter_fund_pool',
'基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
fund_code['上市日期'] = fund_code['上市日期'].map(str)
fund_nav = Fund().get_fund_factor("Repair_Nav")
index_close = Index().get_index_factor(index_code, attr=['CLOSE'])
index_close.columns = [index_code]
# 筛选新基金 并下载基金规模
fund_code = fund_code.loc[:, ['上市日期', '基金全称', '基金简称']]
fund_code = fund_code[fund_code['上市日期'] < beg_date]
fund_code_str = ','.join(fund_code.index)
fund_asset = w.wss(fund_code_str, "netasset_total",
"unit=1;tradeDate=" + str(end_date))
fund_asset = pd.DataFrame(fund_asset.Data,
index=['基金规模'],
columns=fund_asset.Codes).T
fund_asset['基金规模'] /= 100000000.0
fund_asset['基金规模'] = fund_asset['基金规模'].round(2)
fund_asset = fund_asset[fund_asset['基金规模'] > 0.45]
fund_info = pd.concat([fund_code, fund_asset], axis=1)
fund_info = fund_info.dropna()
# 计算最近1年 各项指标
result = pd.DataFrame([], index=fund_code.index, columns=['跟踪误差'])
fund_nav = fund_nav.ix[index_close.index, fund_code.index]
fund_pct = fund_nav.pct_change()
index_pct = index_close.pct_change()
index_pct = index_pct[index_code]
fund_excess_pct = fund_pct.sub(index_pct, axis='index')
fund_excess_pct_period = fund_excess_pct.loc[beg_date:end_date, :]
fund_nav_period = fund_nav.loc[beg_date:end_date, :]
index_close_period = index_close.loc[beg_date:end_date, :]
result.ix[:, "数据长度"] = fund_excess_pct_period.count()
result.ix[:, "跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
fund_return_log = (fund_nav_period.pct_change() + 1.0).applymap(
np.log).cumsum().ix[-1, :]
fund_return = fund_return_log.map(np.exp) - 1
last_date_close = index_close_period.iloc[len(fund_nav_period) - 1, :]
first_date_close = index_close_period.iloc[0, :]
result.ix[:, "基金涨跌"] = fund_return
result.ix[:, "指数涨跌"] = (last_date_close / first_date_close -
1.0).values[0]
result.ix[:, "超额收益"] = result.ix[:, "基金涨跌"] - result.ix[:, "指数涨跌"]
result.ix[:, "信息比率"] = result.ix[:, "超额收益"] / result.ix[:, "跟踪误差"]
result = result[result['数据长度'] > self.data_min_length]
result = pd.concat([fund_info, result], axis=1)
result = result.sort_values(by=['信息比率'], ascending=False)
result = result.dropna()
result = result.fillna("")
# 写到EXCEL表
out_path = os.path.join(self.data_path, "cal_fund_factor", index_name)
file_name = os.path.join(
out_path, '基金指标_' + index_name + '_' + end_date + '.xlsx')
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', '数据长度'] = '0'
num_format_pd.ix['format', '信息比率'] = '0.00'
num_format_pd.ix['format', '基金规模'] = '0.00'
num_format_pd.ix['format', '信息比率'] = '0.00'
sheet_name = "基金指标"
excel = WriteExcel(file_name)
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=0,
num_format_pd=num_format_pd,
color="red",
fillna=True)
def concat_file(path, report_date, last_date):
#########################################################################################################
# path = 'E:\\3_Data\\4_fund_data\\8_fund_index_exposure_weekly\\'
# report_date = '20171231'
# last_date = '20180831'
#########################################################################################################
exposure_file = os.path.join(
path, "halfyear_holding_exposure",
'FundHalfYearExposure_' + report_date + '.csv')
fund_halyear_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
cols = list(fund_halyear_exposure.columns)
quant_fund_exposure = fund_halyear_exposure[
fund_halyear_exposure['Type'].map(lambda x: '主动量化' in x)].mean()
quant_fund_exposure = pd.DataFrame(quant_fund_exposure,
columns=['重点量化基金平均']).T
active_fund_exposure = fund_halyear_exposure[
fund_halyear_exposure['Type'].map(lambda x: '主动股票' in x)].mean()
active_fund_exposure = pd.DataFrame(active_fund_exposure,
columns=['重点主动基金平均']).T
fund_halyear_exposure_add = pd.concat(
[quant_fund_exposure, active_fund_exposure, fund_halyear_exposure],
axis=0)
fund_halyear_exposure_add['Type'] = fund_halyear_exposure_add[
'Type'].fillna('基金平均')
fund_halyear_exposure_add = fund_halyear_exposure_add[cols]
exposure_file = os.path.join(
path, "halfyear_holding_exposure",
'IndexHalfYearExposure_' + report_date + '.csv')
index_halyear_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
exposure_file = os.path.join(path, "lastyear_holding_exposure",
'IndexLastDateExposure_' + last_date + '.csv')
index_lastdate_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
exposure_file = os.path.join(path, "lastyear_holding_exposure",
'FundLastDateExposure_' + last_date + '.csv')
fund_lastdate_exposure = pd.read_csv(exposure_file,
index_col=[0],
encoding='gbk')
halfyear_exposure = pd.concat(
[index_halyear_exposure, fund_halyear_exposure_add], axis=0)
lastdate_exposure = pd.concat(
[index_lastdate_exposure, fund_lastdate_exposure], axis=0)
#########################################################################################################
exposure_file = os.path.join(path, "output_exposure",
'IndexFundExposure' + last_date + '.xlsx')
excel = WriteExcel(exposure_file)
worksheet = excel.add_worksheet("最近交易日风格暴露")
num_format_pd = pd.DataFrame([],
columns=lastdate_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(lastdate_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(lastdate_exposure),
len(lastdate_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(lastdate_exposure.columns),
1 + len(lastdate_exposure),
len(lastdate_exposure.columns),
{'type': 'data_bar'})
worksheet = excel.add_worksheet("最近半年报风格暴露")
num_format_pd = pd.DataFrame([],
columns=halfyear_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(halfyear_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(halfyear_exposure),
len(halfyear_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(halfyear_exposure.columns),
1 + len(halfyear_exposure),
len(halfyear_exposure.columns),
{'type': 'data_bar'})
excel.close()
exposure_file = os.path.join(path, "output_exposure",
'最近交易日风格暴露' + last_date + '.xlsx')
excel = WriteExcel(exposure_file)
worksheet = excel.add_worksheet("最近交易日风格暴露")
num_format_pd = pd.DataFrame([],
columns=lastdate_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(lastdate_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(lastdate_exposure),
len(lastdate_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(lastdate_exposure.columns),
1 + len(lastdate_exposure),
len(lastdate_exposure.columns),
{'type': 'data_bar'})
excel.close()
exposure_file = os.path.join(path, "output_exposure",
'最近半年报风格暴露' + report_date + '.xlsx')
excel = WriteExcel(exposure_file)
worksheet = excel.add_worksheet("最近半年报风格暴露")
num_format_pd = pd.DataFrame([],
columns=halfyear_exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.000'
excel.write_pandas(halfyear_exposure,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
excel.conditional_format(worksheet, 1, 2, 1 + len(halfyear_exposure),
len(halfyear_exposure.columns) - 1, None)
excel.conditional_format(worksheet, 1, len(halfyear_exposure.columns),
1 + len(halfyear_exposure),
len(halfyear_exposure.columns),
{'type': 'data_bar'})
excel.close()
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 generate_excel(self, end_date):
""" ETF净申购 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -120)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
# 一段时间内增减持额时间序列
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("ETF Fund %s %s" % (bg_date, ed_date))
result_add = self.get_etf_data_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 不同类型ETF基金流入流出
fund_type = self.get_etf_data_period_type(beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, 'ETF流入.xlsx')
excel = WriteExcel(filename)
sheet_name = "ETF流入"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["净申购金额", '沪深300'],
chart_name="每周ETF净申购金额（亿元）",
insert_pos="F8",
cat_beg="B2",
cat_end="B25",
val_beg_list=["D2", "F2"],
val_end_list=["D25", "F25"])
num_format_pd = pd.DataFrame([],
columns=fund_type.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(fund_type,
worksheet,
begin_row_number=0,
begin_col_number=15,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["净申购占比", '净申购金额'],
chart_name="规模前10类ETF基金最近1月净流入",
insert_pos="F24",
cat_beg="P2",
cat_end="P11",
val_beg_list=["S2", "R2"],
val_end_list=["S11", "R11"])
excel.close()
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 risk_factor_performance(self,
factor_name,
stock_pool_name="AllChinaStockFilter",
beg_date=None,
end_date=None,
period='M'):
""" 计算单风险因子的因子收益率波动率、自相关性、T值大于2的比例等等 找到有定价能力的风险因子 """
exposure = self.get_risk_factor_exposure(factor_name)
price = Stock().read_factor_h5("Price_Unadjust")
num = Date().get_period_number_for_year(period)
if beg_date is None:
beg_date = exposure.columns[0]
if end_date is None:
end_date = exposure.columns[-1]
date_series = Date().get_trade_date_series(beg_date, end_date, period)
date_series = list(set(date_series) & set(exposure.columns) & set(price.columns))
date_series.sort()
factor_return = pd.DataFrame([], index=date_series, columns=['因子收益率'])
for i_date in range(0, len(date_series)-1):
cur_date = date_series[i_date]
buy_date = cur_date
sell_date = date_series[i_date + 1]
stock_list = Stock().get_invest_stock_pool(stock_pool_name, cur_date)
stock_pct = price[sell_date] / price[buy_date] - 1.0
exposure_date = exposure[cur_date]
exposure_next = exposure[sell_date]
data = pd.concat([exposure_date, exposure_next], axis=1)
data = data.dropna()
stock_list_finally = list(set(stock_list) & set(data.index))
stock_list_finally.sort()
data = data.loc[stock_list_finally, :]
auto_corr = data.corr().iloc[0, 1]
data = pd.concat([exposure_date, stock_pct], axis=1)
stock_list_finally = list(set(stock_list) & set(data.index))
stock_list_finally.sort()
data = data.loc[stock_list_finally, :]
data.columns = ['x', 'y']
data = data.dropna()
if len(data) > 0:
print("Risk Factor %s %s %s" % (factor_name, stock_pool_name, cur_date))
y = data['y'].values
x = data['x'].values
x_add = sm.add_constant(x)
model = sm.OLS(y, x_add).fit()
factor_return_date = model.params[1]
rank_corr = data.corr(method="spearman").iloc[0, 1]
t_value = model.tvalues[1]
r2 = model.rsquared_adj
factor_return.loc[cur_date, '因子收益率'] = factor_return_date
factor_return.loc[cur_date, 'IC'] = rank_corr
factor_return.loc[cur_date, 'T值'] = t_value
factor_return.loc[cur_date, '自相关系数'] = auto_corr
factor_return.loc[cur_date, 'R2'] = r2
else:
print("Risk Factor is Null %s %s %s" % (factor_name, stock_pool_name, cur_date))
factor_return = factor_return.dropna(subset=['因子收益率', 'T值'])
factor_return['因子累计收益率'] = factor_return['因子收益率'].cumsum()
factor_return_mean = factor_return['因子收益率'].mean() * num
factor_return_std = factor_return['因子收益率'].std() * np.sqrt(num)
rank_ic_mean = factor_return['IC'].mean()
rank_ic_ir = rank_ic_mean / factor_return['IC'].std() * np.sqrt(num)
if len(factor_return) > 0:
abs_t_2_ratio = len(factor_return[factor_return['T值'].abs() > 2]) / len(factor_return)
data_beg_date = factor_return.index[0]
data_end_date = factor_return.index[-1]
abs_t_mean = factor_return['T值'].abs().mean()
auto_corr_mean = factor_return['自相关系数'].mean()
r2_mean = factor_return['R2'].mean()
summary = pd.DataFrame([], columns=['因子表现'])
summary.loc['因子年化收益率', "因子表现"] = factor_return_mean
summary.loc['因子年化波动率', "因子表现"] = factor_return_std
summary.loc['IC均值', "因子表现"] = rank_ic_mean
summary.loc['ICIR', "因子表现"] = rank_ic_ir
summary.loc['平均R2', "因子表现"] = r2_mean
summary.loc['T值绝对值大于2的比率', "因子表现"] = abs_t_2_ratio
summary.loc['T值绝对值平均数', "因子表现"] = abs_t_mean
summary.loc['自相关系数平均', "因子表现"] = auto_corr_mean
summary.loc['期数', "因子表现"] = str(len(factor_return))
summary.loc['开始日期', "因子表现"] = data_beg_date
summary.loc['结束日期', "因子表现"] = data_end_date
path = os.path.join(self.factor_performance_path, stock_pool_name)
if not os.path.exists(path):
os.makedirs(path)
file = os.path.join(path, 'Summary_%s.xlsx' % factor_name)
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=summary.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
worksheet = excel.add_worksheet(factor_name)
excel.write_pandas(summary, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
num_format_pd = pd.DataFrame([], columns=factor_return.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
excel.write_pandas(factor_return, worksheet, begin_row_number=0, begin_col_number=4,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()
else:
print("Risk Factor %s is Null in %s" % (factor_name, stock_pool_name))
def write_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_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 alpha_contribution(self, fmp_name, type):
"""
risk factor return multiply risk factor exposure_return of fmp
can contribute alpha return on risk factor
"""
for i_date in range(len(self.change_date_series) - 1):
# date
####################################################################################################
date = self.change_date_series[i_date]
next_date = self.change_date_series[i_date + 1]
change_date = Date().get_trade_date_offset(date, 1)
next_change_date = Date().get_trade_date_offset(next_date, 1)
# data
####################################################################################################
stock_return = self.get_stock_return(change_date, next_change_date)
fmp = self.get_fmp(fmp_name, type)
fmp_date = pd.DataFrame(fmp[date])
fmp_date.columns = ['FmpWeight']
fmp_date = fmp_date.dropna()
exposure_date = self.get_risk_factor_exposure(date)
risk_return = self.get_risk_factor_return().T
risk_return_date = pd.DataFrame(risk_return[date])
code_list = list(
set(exposure_date.index) & set(fmp_date.index)
& set(stock_return.index))
code_list.sort()
exposure_date = exposure_date.loc[code_list, :]
fmp_date = fmp_date.loc[code_list, :]
stock_return = stock_return.loc[code_list, :]
if len(fmp_date) > self.min_stock_num:
# risk factor return multiply alpha exposure_return on risk factor
####################################################################################################
fmp_exposure = np.dot(fmp_date.T, exposure_date)
fmp_exposure = pd.DataFrame(fmp_exposure,
index=[date],
columns=exposure_date.columns)
fmp_risk_factor = fmp_exposure.mul(risk_return_date.T)
fmp_alpha_factor = np.dot(fmp_date.T, stock_return)
col = list(fmp_risk_factor.columns)
fmp_risk_factor.loc[date, 'Res_Alpha'] = fmp_alpha_factor[0][
0] - fmp_risk_factor.sum().sum()
fmp_risk_factor.loc[date, 'Industry'] = fmp_risk_factor[
self.industry_factor_name].sum().sum()
fmp_risk_factor.loc[date, 'Style'] = fmp_risk_factor[
self.style_factor_name].sum().sum()
fmp_risk_factor.loc[date, 'Raw_Alpha'] = fmp_alpha_factor[0][0]
col.insert(0, 'Res_Alpha')
col.insert(0, 'Industry')
col.insert(0, 'Style')
col.insert(0, 'Raw_Alpha')
fmp_risk_factor = fmp_risk_factor[col]
print("Contribution for %s %s %s %s" %
(self.alpha_factor_name, fmp_name, type, date))
# 4 concat
####################################################################################################
if i_date == 0:
fmp_risk_factor_all = fmp_risk_factor
fmp_exposure_all = fmp_exposure
else:
fmp_risk_factor_all = pd.concat(
[fmp_risk_factor_all, fmp_risk_factor], axis=0)
fmp_exposure_all = pd.concat(
[fmp_exposure_all, fmp_exposure], axis=0)
# summary
####################################################################################################
sub_path = os.path.join(self.path, 'summary')
fmp_risk_summary = pd.DataFrame()
fmp_risk_summary['Contribution'] = fmp_risk_factor_all.mean(
) * self.annual_number
fmp_risk_summary['IR'] = fmp_risk_factor_all.mean(
) / fmp_risk_factor_all.std() * np.sqrt(self.annual_number)
risk_return = risk_return.T
risk_return_mean = pd.DataFrame(
risk_return.mean()) * self.annual_number
risk_return_mean.columns = ['Factor Return']
exposure_mean = pd.DataFrame(fmp_exposure_all.mean())
exposure_mean.columns = ['Avg Exposure']
exposure = pd.concat([risk_return_mean, exposure_mean], axis=1)
# write excel
####################################################################################################
filename = os.path.join(
sub_path,
'%s_%s_%s_Summary.xlsx' % (self.alpha_factor_name, fmp_name, type))
sheet_name = "Contribution"
we = WriteExcel(filename)
ws = we.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=fmp_risk_summary.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', 'IR'] = '0.00'
we.write_pandas(fmp_risk_summary,
ws,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
num_format_pd.ix['format', 'Avg Exposure'] = '0.000'
we.write_pandas(exposure,
ws,
begin_row_number=4,
begin_col_number=2 + len(fmp_risk_summary.columns),
num_format_pd=num_format_pd,
color="blue",
fillna=True)
we.close()
# Write Csv
####################################################################################################
sub_path = os.path.join(self.path, 'fmp_risk_factor')
filename = os.path.join(
sub_path, '%s_%s_%s_RiskContributionFMP.csv' %
(self.alpha_factor_name, fmp_name, type))
fmp_risk_factor_all.to_csv(filename)
sub_path = os.path.join(self.path, 'fmp_exposure')
filename = os.path.join(
sub_path, '%s_%s_%s_RiskExposureFMP.csv' %
(self.alpha_factor_name, fmp_name, type))
fmp_exposure_all.to_csv(filename)
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 generate_excel(self, end_date):
""" 陆股通信息 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -60)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
beg_1m_date = "20190201"
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series)-1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("Hk Inflow Period %s %s" % (bg_date, ed_date))
result_add = self.hk_inflow_period(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 最近一个月平均持股金额最大、最小的几个股票
stock = self.hk_inflow_period_stock(beg_1m_date, end_date)
# 最近一个月平均持股金额行业排序
industry = self.hk_inflow_period_industry(beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '融资融券情况春节后.xlsx')
print(filename)
excel = WriteExcel(filename)
sheet_name = "融资融券情况"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
series_name=["净流入金额", '沪深300'],
chart_name="最近3个月每周融资余额净买入金额(亿元)",
insert_pos="I15", cat_beg="B2", cat_end="B13",
val_beg_list=["F2", "H2"], val_end_list=["F13", "H13"])
num_format_pd = pd.DataFrame([], columns=stock.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(stock, worksheet, begin_row_number=0, begin_col_number=8,
num_format_pd=num_format_pd, color="orange", fillna=True)
num_format_pd = pd.DataFrame([], columns=industry.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(industry, worksheet, begin_row_number=0, begin_col_number=15,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
series_name=["净流入占比", '净流入金额'],
chart_name="行业最近1月融资净买入金额",
insert_pos="I32", cat_beg="P2", cat_end="P30",
val_beg_list=["U2", "S2"], val_end_list=["U30", "S30"])
excel.close()
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 fund_score_all(self, end_date):
""" 所有基金得分 """
file = os.path.join(self.data_path, "基金经理绩效考核.xlsx")
data = pd.read_excel(file, sheetname="基金经理考核范围", index_col=[0])
data['现任经理管理开始日'] = data['现任经理管理开始日'].map(str)
data.index = data['代码']
result = pd.DataFrame()
end_date = Date().change_to_datetime(end_date)
before_1y = datetime(year=end_date.year, month=1, day=1).strftime("%Y%m%d")
before_3y = datetime(year=end_date.year-2, month=1, day=1).strftime("%Y%m%d")
before_3y = max(before_3y, "20160101")
before_5y = datetime(year=end_date.year-4, month=1, day=1).strftime("%Y%m%d")
before_5y = max(before_5y, "20160101")
end_date = Date().change_to_str(end_date)
for i in range(0, len(data)):
fund_code = data.index[i]
index_code = data.loc[fund_code, "基准代码"]
rank_pool = data.loc[fund_code, "考核分类"]
mg_date = data.loc[fund_code, "现任经理管理开始日"]
fund_name = data.loc[fund_code, "名称"]
fund_type = data.loc[fund_code, "基金类型"]
if mg_date <= before_1y:
res = self.fund_score(fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, index_code)
res.loc[fund_code, "当年研究贡献得分"] = 1.00
res.loc[fund_code, "过去2年研究贡献得分"] = 1.00
if mg_date >= before_5y:
res.loc[fund_code, "5年收益"] = res.loc[fund_code, "管理以来收益"]
res.loc[fund_code, "5年排名"] = res.loc[fund_code, "管理以来排名"]
res.loc[fund_code, "5年排名百分比"] = res.loc[fund_code, "管理以来排名百分比"]
res.loc[fund_code, "5年得分"] = res.loc[fund_code, "管理以来得分"]
if mg_date >= before_3y:
res.loc[fund_code, "3年收益"] = res.loc[fund_code, "管理以来收益"]
res.loc[fund_code, "3年排名"] = res.loc[fund_code, "管理以来排名"]
res.loc[fund_code, "3年排名百分比"] = res.loc[fund_code, "管理以来排名百分比"]
res.loc[fund_code, "3年得分"] = res.loc[fund_code, "管理以来得分"]
res.loc[fund_code, "长期得分"] = 0.0
res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "过去2年研究贡献得分"]
res.loc[fund_code, "长期得分"] += 0.40 * res.loc[fund_code, "3年得分"]
res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "5年得分"]
res.loc[fund_code, "长期得分"] += 0.20 * res.loc[fund_code, "管理以来得分"]
res.loc[fund_code, "当年得分"] = 0.0
res.loc[fund_code, "当年得分"] += 0.20 * res.loc[fund_code, "当年研究贡献得分"]
res.loc[fund_code, "当年得分"] += 0.40 * res.loc[fund_code, "1年得分"]
res.loc[fund_code, "当年得分"] += 0.20 * res.loc[fund_code, "3年得分"]
res.loc[fund_code, "当年得分"] += 0.10 * res.loc[fund_code, "5年得分"]
res.loc[fund_code, "当年得分"] += 0.10 * res.loc[fund_code, "管理以来得分"]
result = pd.concat([result, res], axis=0)
file = os.path.join(self.data_path, "基金经理效绩得分.xlsx")
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
sheet_name = ""
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()
# 超额收益
result = pd.DataFrame()
for i in range(0, len(data)):
fund_code = data.index[i]
index_code = data.loc[fund_code, "基准代码"]
rank_pool = data.loc[fund_code, "考核分类"]
mg_date = data.loc[fund_code, "现任经理管理开始日"]
fund_name = data.loc[fund_code, "名称"]
fund_type = data.loc[fund_code, "基金类型"]
if (mg_date <= before_1y) and (fund_type == "行业基金"):
res = self.fund_excess_score(fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, index_code)
if mg_date >= before_5y:
res.loc[fund_code, "5年超额收益"] = res.loc[fund_code, "管理以来超额收益"]
res.loc[fund_code, "5年超额排名"] = res.loc[fund_code, "管理以来超额排名"]
res.loc[fund_code, "5年超额排名百分比"] = res.loc[fund_code, "管理以来超额排名百分比"]
res.loc[fund_code, "5年超额得分"] = res.loc[fund_code, "管理以来超额得分"]
if mg_date >= before_3y:
res.loc[fund_code, "3年超额收益"] = res.loc[fund_code, "管理以来超额收益"]
res.loc[fund_code, "3年超额排名"] = res.loc[fund_code, "管理以来超额排名"]
res.loc[fund_code, "3年超额排名百分比"] = res.loc[fund_code, "管理以来超额排名百分比"]
res.loc[fund_code, "3年超额得分"] = res.loc[fund_code, "管理以来超额得分"]
result = pd.concat([result, res], axis=0)
file = os.path.join(self.data_path, "基金经理效绩超额得分.xlsx")
excel = WriteExcel(file)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00%'
sheet_name = ""
worksheet = excel.add_worksheet(sheet_name)
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="red", fillna=True)
excel.close()