def cal_fund_regression_exposure_index(self, fund, beg_date, end_date, period="D"):
"""
计算一只基金每日对不同指数的暴露
"""
# 参数
####################################################################
one_index_up_limit = 1.0
one_index_low_limit = 0.0
sum_index = 1.0
beg_date = Date().change_to_str(beg_date)
end_date = Date().change_to_str(end_date)
# 取得 指数收益率数据
####################################################################
for i_index in range(len(self.index_code_list)):
index_code = self.index_code_list[i_index]
index_return = Index().get_index_factor(index_code, Nattr=["PCT"])
if i_index == 0:
index_return = Index().get_index_factor(index_code, attr=["PCT"])
index_return_all = index_return
else:
index_return_all = pd.concat([index_return_all, index_return], axis=1)
index_return_all.columns = self.index_code_list
# 取得 基金涨跌幅数据
####################################################################
if fund[len(fund)-2:] == 'OF':
fund_return = FundFactor().get_fund_factor("Repair_Nav_Pct", None, [fund]) / 100.0
else:
fund_return = Index().get_index_factor(fund, attr=["PCT"])
fund_return.columns = [fund]
# 合并数据
####################################################################
data = pd.concat([fund_return, index_return_all], axis=1)
data = data.dropna(subset=[fund])
# 回归日期
####################################################################
date_series = Date().get_trade_date_series(beg_date, end_date, period=period)
date_series = list(set(date_series) & set(data.index))
date_series.sort()
# 循环优化计算每天的暴露
####################################################################
for i_date in range(0, len(date_series)):
# 约束回归所需要的数据
#############################################################################################
period_end_date = date_series[i_date]
period_beg_date = Date().get_trade_date_offset(period_end_date, -self.regression_period)
period_date_series = Date().get_trade_date_series(period_beg_date, period_end_date)
data_periods = data.ix[period_date_series, :]
data_periods = data_periods.dropna(subset=[fund])
data_periods = data_periods.T.dropna(how='all').T
data_periods = data_periods.T.fillna(data_periods.mean(axis=1)).T
data_periods = data_periods.dropna()
# 有约束的回归 可以转换为二次规划
#############################################################################################
if len(data_periods) > self.regression_period_min and (len(data_periods.columns) > 1):
# 平方和最小
#############################################################################################
y = data_periods.ix[:, 0].values
x = data_periods.ix[:, 1:].values
P = 2 * np.dot(x.T, x)
Q = -2 * np.dot(x.T, y)
# 单个指数上下限为 0
#############################################################################################
G_up = np.diag(np.ones(x.shape[1]))
G_low = - np.diag(np.ones(x.shape[1]))
G = np.row_stack((G_up, G_low))
h_up = np.row_stack(np.ones((x.shape[1], 1))) * one_index_up_limit
h_low = - np.row_stack(np.ones((x.shape[1], 1))) * one_index_low_limit
h = np.row_stack((h_up, h_low))
#############################################################################################
A = np.column_stack(np.ones((x.shape[1], 1)))
b = np.array([sum_index])
# 开始规划求解
############################################################################################
try:
P = matrix(P)
Q = matrix(Q)
G = matrix(G)
h = matrix(h)
A = matrix(A)
b = matrix(b)
result = sol.qp(P, Q, G, h, A, b)
params_add = pd.DataFrame(np.array(result['x'][0:]),
columns=[period_end_date], index=data_periods.columns[1:]).T
print("########## Fund Regression Index Exposure GF %s %s ##########" % (fund, period_end_date))
except Exception as e:
params_add = pd.DataFrame([], columns=[period_end_date], index=data_periods.columns[1:]).T
print("########## Quadratic Programming is InCorrect %s %s ##########" % (fund, period_end_date))
else:
params_add = pd.DataFrame([], columns=[period_end_date], index=data_periods.columns[1:]).T
print("########## Fund Regression Data Len is Too Small %s %s ##########" % (fund, period_end_date))
if i_date == 0:
params_new = params_add
else:
params_new = pd.concat([params_new, params_add], axis=0)
# 合并新数据 并存储数据
####################################################################
out_file = os.path.join(self.data_path, self.file_prefix + fund + '.csv')
if os.path.exists(out_file):
params_old = pd.read_csv(out_file, index_col=[0], encoding='gbk')
params_old.index = params_old.index.map(str)
params = FactorOperate().pandas_add_row(params_old, params_new)
else:
params = params_new
params.to_csv(out_file)
def get_data(self):
""" 收益率数据 """
index_pct = Index().get_index_cross_factor("PCT") * 100
fund_pct = FundFactor().get_fund_factor("Repair_Nav_Pct")
self.asset_pct = pd.concat([fund_pct, index_pct], axis=1)
self = FundPublicIndexQuarterHeavilyHeld()
date = "20171229"
# FundPublicIndexQuarterHeavilyHeld().cal_quarter_holding_allfund_allquarter()
# FundPublicIndexQuarterHeavilyHeld().cal_quarter_holding_allfund_alldaily()
# FundPublicIndexQuarterHeavilyHeld().cal_return_daily()
from quant.stock.index import Index
beg_date = "20040502"
end_date = datetime.today()
name_list = ["偏股基金总指数", "跟随股票基金指数", "跟随重仓基金指数", "沪深300", "中证500"]
code_list = ["885001.WI", "PublicQuarter", "PublicQuarterHeavilyHeld", "000300.SH", "000905.SH"]
for i in range(len(code_list)):
code = code_list[i]
index_data = Index().get_index_factor(code, beg_date, end_date, ["PCT"])
if i == 0:
all_data = index_data
else:
all_data = pd.concat([all_data, index_data], axis=1)
all_data.columns = name_list
all_data["偏股基金总指数_满仓"] = all_data['偏股基金总指数'] / 0.90
all_data = all_data.dropna()
all_data = (all_data + 1.0).cumprod() - 1.0
all_data.to_csv(r"C:\Users\doufucheng\OneDrive\Desktop\index.csv")
def write_quant6(end_date, save_path):
# 参数
###########################################################################################
fund_name = '光大量化组合6号'
fund_code = fund_name
fund_type = "专户"
benchmark_code = "000905.SH"
benchmark_name = "中证500"
setup_date = "20151231"
date_array = np.array([["2019年", '20190101', end_date],
["2018年", "20180101", '20181231'],
["2017年", "20170101", '20171231'],
["2016年", '20160101', '20161231'],
["成立以来", 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 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)
# 一段时间内增减持额时间序列
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')
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=["T2", "R2"],
val_end_list=["T30", "R30"])
excel.close()
def update_data():
""" 更新净值数据"""
MfcData().load_mfc_public_fund_nav()
Index().load_index_factor_all()
def cal_barra_exposure_return(self):
""" 计算满仓 基金暴露、指数暴露、超额暴露、因子收益、基金超额暴露收益 """
# 参数
type_list = ["STYLE", "COUNTRY", "INDUSTRY"]
# 得到基金（满仓）相对于跟踪指数（满仓）超额暴露
exposure_fund = MfcData().get_mfc_holding_barra_exposure(
self.fund_name, self.beg_date_pre, self.end_date)
exposure_index = Index().get_index_exposure(self.index_code,
self.beg_date_pre,
self.end_date, type_list)
exposure_excess = exposure_fund.sub(exposure_index)
exposure_excess = exposure_excess.dropna()
# 添加 Country Factor = 1.0
factor_name = Barra().get_factor_name(type_list=["COUNTRY"])
factor_name = list(factor_name["NAME_EN"].values)
exposure_excess[factor_name] = 1.0
# 前一天的 Exposure 对应后一天的 Factor Return
exposure_excess.index = exposure_excess.index.map(
lambda x: Date().get_trade_date_offset(x, 1))
# 取得当日的 Factor Return
factor_return = Barra().get_factor_return(self.beg_date, self.end_date,
type_list)
# 计算超额暴露带来的收益部分
[exposure, factor_return] = FactorOperate().make_same_index_columns(
[exposure_excess, factor_return])
fund_risk_factor_return = exposure.mul(factor_return)
# 调整列的位置
factor_name = Barra().get_factor_name(type_list=type_list)
factor_name = list(factor_name["NAME_EN"].values)
fund_risk_factor_return = fund_risk_factor_return[factor_name]
# 分别计算 Style Industry RiskFactor = Style + Industry
factor_name = Barra().get_factor_name(type_list=['STYLE'])
factor_name = list(factor_name["NAME_EN"].values)
fund_risk_factor_return['Style'] = fund_risk_factor_return[
factor_name].sum(axis=1)
factor_name = Barra().get_factor_name(type_list=['INDUSTRY'])
factor_name = list(factor_name["NAME_EN"].values)
fund_risk_factor_return['Industry'] = fund_risk_factor_return[
factor_name].sum(axis=1)
factor_name = Barra().get_factor_name(type_list=type_list)
factor_name = list(factor_name["NAME_EN"].values)
fund_risk_factor_return['RiskFactor'] = fund_risk_factor_return[
factor_name].sum(axis=1)
# 整理返回区间内的所有数据=基金暴露+指数暴露+超额暴露+因子收益+基金超额暴露收益
fund_risk_factor_return = fund_risk_factor_return.loc[
self.beg_date:self.end_date, :]
exposure = exposure.loc[self.beg_date:self.end_date, :]
factor_return = factor_return.loc[self.beg_date:self.end_date, :]
fund_risk_factor_return /= 100.0
factor_return /= 100.0
self.save_file_excel(fund_risk_factor_return, "Barra", "基金风格收益",
"0.00%")
self.save_file_excel(factor_return, "Barra", "风格因子收益", "0.00%")
self.save_file_excel(exposure, "Barra", "基金超额暴露", "0.000")
self.save_file_excel(exposure_fund, "Barra", "基金暴露", "0.000")
self.save_file_excel(exposure_index, "Barra", "指数暴露", "0.000")
def holding_data_liuyang(today, project_path, out_path):
# 输入参数
##################################################################################
# project_path = 'E:\\4_代码\\pycharmprojects\\timer\\input_data\\'
# out_path = 'E:\\3_数据\\7_other_data\\0_mail_holding_all\\'
person_name = 'liuyang'
# today = datetime(2018, 6, 27).strftime("%Y%m%d")
before_trade_data = Date().get_trade_date_offset(today, -1)
today = Date().change_to_str(today)
# 基金列表
##################################################################################
fund = pd.read_excel(project_path + 'Manage_Fund_Name.xlsx',
encoding='gbk')
fund_val = fund.ix[:, person_name]
fund_val = fund_val.dropna()
fund_list = list(fund_val.values)
# 基金持仓证券
##################################################################################
fund_asset = MfcData().get_fund_security(before_trade_data)
fund_asset = fund_asset[[
'持仓日期', '序号', '基金名称', '证券代码', '证券名称', '证券类别', '市值', '市值比净值(%)', '持仓',
'净买量', '净买金额', '费用合计', '当日涨跌幅(%)', '持仓多空标志', '估值价格', '最新价'
]]
for i_fund in range(len(fund_list)):
fund_name = fund_list[i_fund]
fund_asset_fund = fund_asset[fund_asset['基金名称'] == fund_name]
out_sub_path = os.path.join(out_path, person_name, today,
"holding_data")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
out_file = os.path.join(out_sub_path, fund_name + '持仓.xlsx')
fund_asset_fund.to_excel(out_file, index=None)
# 股票库
##################################################################################
pool_path = Parameter().get_load_out_file("Mfc_Data")
pool_list = [
"改革动力股票库.xls", "改革动力禁止库.xls", "改革动力限制库.xls", "公司超五库.xls", "公司股票库.xls",
"公司关联库.xls", "公司禁止库.xls", "公司限制库.xls", "逆向股票库.xls", "逆向禁止库.xls",
"逆向限制库.xls", "同顺禁止库.xls", "量化限制库.xls"
]
out_sub_path = os.path.join(out_path, person_name, today, "holding_data")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for i_file in range(len(pool_list)):
file = pool_list[i_file]
src_file = os.path.join(pool_path, 'raw_file', today, file)
out_file = os.path.join(out_sub_path, file)
try:
shutil.copyfile(src_file, out_file)
except:
pd.DataFrame([]).to_excel(out_file)
# 指数权重 Axioma
##################################################################################
index_code_list = [
"000300.SH", "000905.SH", "000016.SH", "881001.WI",
'China_Index_Benchmark'
]
out_sub_path = os.path.join(out_path, person_name, today, "index_weight")
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
for index_code in index_code_list:
data = Index().get_weight(index_code, before_trade_data)
out_file = os.path.join(out_sub_path, index_code + '.csv')
data.index = data.index.map(lambda x: x[0:6] + '-CN')
data.to_csv(out_file, header=None)
def summary_market(excel, params, summary, date_array, sheet_name):
# 添加新的sheet
##############################################################################
worksheet = excel.add_worksheet(sheet_name)
params = pd.DataFrame(params, columns=["Name", 'Code'])
# 新的数据
##############################################################################
summary_index = summary.loc[params.Name, :]
index_code = params.iloc[0, 1]
index_name = params.iloc[0, 0]
index_data = Index().get_index_factor(index_code, attr=["CLOSE"])
# 写入最近表现
##############################################################################
col_number = 1
num_format_pd = pd.DataFrame([],
columns=summary_index.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
print(summary_index)
excel.write_pandas(summary_index,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="red",
fillna=True)
col_number += len(summary_index.columns) + 2
# 分日期区间写入主要指数（首个）累计收益率 和 时间序列图
##############################################################################
for i_date in range(0, len(date_array)):
# 整理数据
##############################################################################
label = date_array.loc[i_date, "label"]
bg_date = date_array.loc[i_date, "beg_date"]
ed_date = date_array.loc[i_date, "end_date"]
index_period = index_data.loc[bg_date:ed_date, :]
index_period['Pct'] = index_period['CLOSE'].pct_change()
index_period = index_period.dropna()
index_period["CumPct"] = (index_period['Pct'] + 1.0).cumprod() - 1.0
index_period_cum_pct = pd.DataFrame(index_period['CumPct'])
index_period_cum_pct.columns = [label + index_name]
print(index_period_cum_pct)
# 写入累计收益率
##############################################################################
num_format_pd = pd.DataFrame([],
columns=index_period_cum_pct.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00%'
excel.write_pandas(index_period_cum_pct,
worksheet,
begin_row_number=0,
begin_col_number=col_number,
num_format_pd=num_format_pd,
color="blue",
fillna=True)
# 时间序列图
##############################################################################
position = excel.change_row_col_to_cell(
len(summary_index) + 3 + i_date * 15, 1)
excel.line_chart_time_series_plot(worksheet, 0, col_number,
index_period_cum_pct, [index_name],
label + index_name + "累计收益率",
position, sheet_name)
col_number += len(index_period_cum_pct.columns) + 1
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)
# 一段时间内增减持额时间序列
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("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()
# 最近一个月增减持额最大的几个股票
stock = self.major_holder_deal_period_stock(beg_1m_date, end_date)
# 最近一个月增减持额行业排序
industry = self.major_holder_deal_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')
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="最近3个月每周大股东增减持总和",
insert_pos="O2",
cat_beg="D2",
cat_end="D14",
val_beg_list=["E2", "G2"],
val_end_list=["E14", "G14"])
num_format_pd = pd.DataFrame([],
columns=stock.columns,
index=['format'])
num_format_pd.ix['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.ix['format', :] = '0.00'
excel.write_pandas(industry,
worksheet,
begin_row_number=0,
begin_col_number=11,
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="O16",
cat_beg="L2",
cat_end="L30",
val_beg_list=["M2"],
val_end_list=["M30"])
excel.close()
if __name__ == '__main__':
# self = StockFundPortfolio()
# beg_date = "20180101"
# end_date = "20180909"
# i = 9
# fund_pool = pd.read_excel(self.fund_pool_file, index_col=[0])
# fund_pool = list(fund_pool.Code)
# fund = fund_pool[i]
date_list = Date().get_normal_date_series("20141230",
"20181001",
period="Q")
for date in date_list:
# FundPortfolio().opt_fund_portfolio_1(date)
StockFundPortfolio().opt_fund_portfolio_2(date)
from quant.source.wind_portfolio import WindPortUpLoad
WindPortUpLoad().upload_weight_period("行业风格平价FOF")
from quant.source.backtest_fund import PortBackTestFund
Index().load_index_factor("885001.WI")
backtest = PortBackTestFund()
backtest.set_info("行业风格平价FOF", "885001.WI")
backtest.set_weight_at_all_change_date()
backtest.cal_turnover()
backtest.set_weight_at_all_daily()
backtest.cal_port_return()
backtest.cal_summary()
def generate_excel(self, end_date, quarter_date, quarter_last_date):
""" 陆股通信息 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -400)
quarter_date_series = Date().get_normal_date_series(
beg_date, end_date, "Q")
# 最近4个季度重仓持股金额
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
result = pd.DataFrame()
for i in range(len(quarter_date_series) - 1):
quarter_last_date_temp = quarter_date_series[i]
quarter_date_temp = quarter_date_series[i + 1]
add = self.fund_holder_quarter_sum(quarter_date_temp,
quarter_last_date_temp)
quarter_trade_date = Date().get_trade_date_offset(
quarter_date_temp, 0)
add.loc[quarter_date_temp,
'沪深300'] = index_data.loc[quarter_trade_date, "CLOSE"]
result = pd.concat([result, add], axis=0)
# 最近一个月平均持股金额最大、最小的几个股票
stock = self.hk_inflow_period_stock(quarter_date, quarter_last_date)
# 最近一个月平均持股金额行业排序
industry = self.fund_holder_quarter_industry(quarter_date,
quarter_last_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'
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="B10",
cat_beg="B2",
cat_end="B6",
val_beg_list=["E2", "G2"],
val_end_list=["E6", "G6"])
num_format_pd = pd.DataFrame([],
columns=stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['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.ix['format', :] = '0.00'
num_format_pd.ix['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="基金季报重仓行业净流入金额(亿元)",
insert_pos="B26",
cat_beg="P2",
cat_end="P30",
val_beg_list=["S2", "T2"],
val_end_list=["S30", "T30"])
excel.close()
def concat_exposure(halfyear_date, out_path):
# 参数
##########################################################################################
# end_date = "20180715"
# out_path = 'E:\\3_数据\\4_fund_data\\6_index_enhanced_fund_snowball\\'
# 沪深300 指数、基金暴露拼接起来
##########################################################################################
index_name = '沪深300'
index_code = "000300.SH"
index_exposure = Index().get_index_exposure_date(index_code, halfyear_date)
file = os.path.join(out_path, 'filter_fund_pool\\',
'基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
fund_code_list = list(fund_code.index)
for i_fund in range(0, len(fund_code_list)):
fund = fund_code_list[i_fund]
exposure_add = Fund().get_fund_holder_exposure_date(
fund, halfyear_date)
if i_fund == 0:
exposure_fund = exposure_add
else:
exposure_fund = pd.concat([exposure_fund, exposure_add], axis=0)
exposure = pd.concat([exposure_fund, index_exposure], axis=0)
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
begin_row_number = 0
begin_col_number = 0
color = "red"
file_name = os.path.join(
out_path, "exposure", index_name, "BARRA风格暴露_" + index_name + "_" +
Date().get_normal_date_month_end_day(halfyear_date) + ".xlsx")
sheet_name = "BARRA风格暴露"
write_pandas(file_name, sheet_name, begin_row_number, begin_col_number,
exposure, num_format_pd, color)
##########################################################################################
index_name = '中证500'
index_code = "000905.SH"
index_exposure = Index().get_index_exposure_date(index_code, halfyear_date)
file = os.path.join(out_path, 'filter_fund_pool\\',
'基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
fund_code_list = list(fund_code.index)
for i_fund in range(0, len(fund_code_list)):
fund = fund_code_list[i_fund]
exposure_add = Fund().get_fund_holder_exposure_date(
fund, halfyear_date)
if i_fund == 0:
exposure_fund = exposure_add
else:
exposure_fund = pd.concat([exposure_fund, exposure_add], axis=0)
exposure = pd.concat([exposure_fund, index_exposure], axis=0)
num_format_pd = pd.DataFrame([],
columns=exposure.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
begin_row_number = 0
begin_col_number = 0
color = "red"
file_name = os.path.join(
out_path, "exposure", index_name, "BARRA风格暴露_" + index_name + "_" +
Date().get_normal_date_month_end_day(halfyear_date) + ".xlsx")
sheet_name = "BARRA风格暴露"
write_pandas(file_name, sheet_name, begin_row_number, begin_col_number,
exposure, num_format_pd, color)
def write_public_cfdp(end_date, save_path):
# 参数
###########################################################################################
fund_name = '泰达宏利中证财富大盘'
fund_code = '162213.OF'
fund_type = "公募"
benchmark_code = '000940.SH'
benchmark_name = '中证财富大盘指数'
benchmark_ratio = 0.95
setup_date = '20141003'
date_array = np.array(
[["转型(20180317)以来", '20180317', end_date, '20170930'],
["2019年", "20190101", end_date, "20181231"],
["2018年", "20180101", '20181231', "20170930"],
["2017年", "20170101", '20171231', "20160930"],
["2016年", "20160101", "20161231", "20150930"],
["2015年", "20150101", "20151231", "20150101"],
["2015年至转型", "20150101", "20180317", "20150101"],
["管理(20141003)以来", setup_date, end_date, setup_date]])
benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"],
["中证财富大盘指数", "000940.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 + '.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,
"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,
"指数型基金",
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, 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
# 写入增强基金表现
###########################################################################################
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 + "累计超额收益（20141003管理以来）"
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 = benchmark_name + "累计收益（20141003管理以来）"
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_zxjy(end_date, save_path):
# 参数
###########################################################################################
fund_name = '泰达宏利转型机遇'
fund_code = '000828.OF'
fund_type = "公募"
benchmark_code = '885012.WI'
benchmark_name = '股票型基金总指数'
benchmark_code_2 = "000300.SH"
benchmark_name_2 = "沪深300"
setup_date = '20151221' # 张勋开始管理
setup_date2 = '20171229' # 王鹏开始管理
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"],
["2015年", setup_date, '20151231', setup_date],
["张勋管理期间", setup_date, setup_date2, setup_date],
["王鹏管理以来", setup_date2, end_date, setup_date2],
["过去1年", before_1y, end_date, before_1y],
["过去2年", before_2y, end_date, before_2y],
["过去3年", before_3y, end_date, before_3y],
["过去5年", before_5y, end_date, before_5y]])
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,
"股票型基金",
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 cal_real_portfolio(self,
neutralize='Raw',
W_mat="Equal",
stock_pool='Astock'):
for i_date in range(len(self.change_date_series) - 1):
lamb = 1000.0
# date
####################################################################################################
date = self.change_date_series[i_date]
data = self.get_data_date(date, stock_pool)
alpha_date, industry_dummy_date, barra_style_date, free_mv_date, code_trade = data
fmp = self.get_fmp(neutralize, W_mat, stock_pool)
fmp_date = pd.DataFrame(fmp[date])
fmp_date.columns = ['FmpWeight']
fmp_date = fmp_date.dropna()
code_list = list(fmp_date.index)
# Barra().cal_stock_covariance(date)
stock_covriance = Barra().get_stock_covariance(date)
stock_covriance = stock_covriance.loc[code_list, code_list].values
stock_covriance = np.zeros(shape=(len(code_list), len(code_list)))
alpha_signal = np.dot(stock_covriance, fmp_date)
alpha_signal = fmp_date.values * 2
P = stock_covriance * lamb
Q = -np.row_stack(alpha_signal)
from quant.stock.index import Index
index_weight = Index().get_weight(index_code=stock_pool, date=date)
index_weight = index_weight.loc[code_list, :]
index_weight = index_weight.fillna(0.0)
index_weight['Max'] = 0.03
index_weight['Min'] = -index_weight['WEIGHT']
G_positive = np.diag(np.ones(shape=(len(index_weight))))
G_negative = -np.diag(np.ones(shape=(len(index_weight))))
G = np.row_stack((G_positive, G_negative))
h_positive = np.row_stack(index_weight['Max'].values)
h_negative = np.row_stack(index_weight['Min'].values)
h = np.row_stack((h_positive, h_negative))
A = np.ones(shape=(1, len(index_weight)))
b = np.array([[0.0]])
try:
P = matrix(P)
Q = matrix(Q)
G = matrix(G)
h = matrix(h)
A = matrix(A)
b = matrix(b)
result = sol.qp(P, q=Q, G=G, h=h, A=A, b=b)
result = sol.qp(P, q=Q)
stock_weight_active = pd.DataFrame(np.array(result['x'][0:]),
columns=['Active'],
index=code_list).T
weight = pd.concat(
[index_weight, stock_weight_active.T, fmp_date], axis=1)
weight['PortWeight'] = weight['WEIGHT'] + weight['Active']
weight['ImplyWeight'] = weight['WEIGHT'] + weight['FmpWeight']
print((weight['WEIGHT'] - weight['PortWeight']).abs().sum())
print(weight['Active'].sum())
print("Cal Portfolio %s %s %s %s " %
(date, stock_pool, neutralize, self.alpha_factor_name))
except Exception as e:
stock_weight = pd.DataFrame([],
columns=[date],
index=code_list).T
index_weight = pd.concat([index_weight, stock_weight.T],
axis=1)
print("QP Portfolio is InCorrect %s %s %s %s " %
(date, stock_pool, neutralize, self.alpha_factor_name))
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
