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 calculate_fund_factor_date(date, index_code, index_name, out_path):
# 参数
########################################################################################################
# index_code = '000905.SH'
# index_name = '中证500'
# date = '2015-12-31'
# min_period = 200
# out_path = 'E:\\4_代码\\pycharmprojects\\31_雪球优选增强基金\\output_data\\cal_fund_factor\\zz500\\'
# 日期数据
########################################################################################################
min_period = 200
date_cur = datetime.strptime(date, "%Y%m%d")
date_cur_int = date_cur.strftime('%Y%m%d')
date_bef_1y = datetime(year=date_cur.year-1, month=date_cur.month, day=date_cur.day).strftime("%Y-%m-%d")
date_aft_hy = (date_cur + pd.tseries.offsets.DateOffset(months=6, days=0)).strftime("%Y-%m-%d")
# 读取数据 基金池 基金净值数据 指数收盘价数据
########################################################################################################
path = os.path.join(out_path, 'filter_fund_pool\\')
file = os.path.join(path, '基金最终筛选池_' + index_name + '.xlsx')
fund_code = pd.read_excel(file, index_col=[1], encoding='gbk')
fund_nav = Fund().get_fund_factor("Repair_Nav")
index_close = Index().get_index_factor(index_code, None, None, attr=['CLOSE'])
index_close.columns = [index_code]
# 筛选新基金 并下载基金规模
#######################################################################################################
fund_code = fund_code.ix[:, ['上市日期', '基金简称']]
fund_code = fund_code[fund_code['上市日期'] < date_bef_1y]
fund_code_str = ','.join(fund_code.index)
fund_asset = w.wss(fund_code_str, "netasset_total", "unit=1;tradeDate=" + str(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.5]
fund_info = pd.concat([fund_code, fund_asset], axis=1)
fund_info = fund_info.dropna()
# 计算最近1年 各项指标
########################################################################################################
result = pd.DataFrame([], index=fund_code.index, columns=['最近1年跟踪误差'])
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.ix[date_bef_1y:date, :]
fund_nav_period = fund_nav.ix[date_bef_1y:date, :]
index_close_prioed = index_close.ix[date_bef_1y:date, :]
result.ix[:, "最近1年数据长度"] = fund_excess_pct_period.count()
result.ix[:, "最近1年跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
# last_date_nav = fund_nav_period.iloc[len(fund_nav_period)-1, :]
# first_date_nav = fund_nav_period.iloc[0, :]
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_prioed.iloc[len(fund_nav_period)-1, :]
first_date_close = index_close_prioed.iloc[0, :]
result.ix[:, "最近1年基金涨跌"] = fund_return
result.ix[:, "最近1年指数涨跌"] = (last_date_close / first_date_close - 1.0).values[0]
result.ix[:, "最近1年超额收益"] = result.ix[:, "最近1年基金涨跌"] - result.ix[:, "最近1年指数涨跌"]
result.ix[:, "最近1年信息比率"] = result.ix[:, "最近1年超额收益"] / result.ix[:, "最近1年跟踪误差"]
result = result[result['最近1年数据长度'] > min_period]
# 计算之后半年 各项指标
########################################################################################################
fund_excess_pct_period = fund_excess_pct.ix[date:date_aft_hy, :]
fund_nav_period = fund_nav.ix[date:date_aft_hy, :]
index_close_prioed = index_close.ix[date:date_aft_hy, :]
result.ix[:, "之后半年数据长度"] = fund_excess_pct_period.count()
result.ix[:, "之后半年跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
try:
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
result.ix[:, "之后半年基金涨跌"] = fund_return
except:
result.ix[:, "之后半年基金涨跌"] = np.nan
try:
last_date_close = index_close_prioed.iloc[len(fund_nav_period) - 1, :]
first_date_close = index_close_prioed.iloc[0, :]
result.ix[:, "之后半年指数涨跌"] = (last_date_close / first_date_close - 1.0).values[0]
except:
result.ix[:, "之后半年指数涨跌"] = np.nan
result.ix[:, "之后半年超额收益"] = result.ix[:, "之后半年基金涨跌"] - result.ix[:, "之后半年指数涨跌"]
result.ix[:, "之后半年信息比率"] = result.ix[:, "之后半年超额收益"] / result.ix[:, "之后半年跟踪误差"]
result = pd.concat([fund_info, result], axis=1)
result = result.dropna(subset=["基金规模"])
result = result.fillna("")
# 写到EXCEL表
################################################################################################
out_path = os.path.join(out_path, "cal_fund_factor\\" + index_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'
num_format_pd.ix['format', '之后半年信息比率'] = '0.00'
num_format_pd.ix['format', '基金规模'] = '0.00'
num_format_pd.ix['format', '最近1年信息比率'] = '0.00'
num_format_pd.ix['format', '最近1年数据长度'] = '0.00'
begin_row_number = 0
begin_col_number = 0
color = "red"
file_name = os.path.join(out_path, '基金指标_' + index_name + '_' + date_cur_int + '.xlsx')
sheet_name = "基金指标"
write_pandas(file_name, sheet_name, begin_row_number, begin_col_number, result, num_format_pd, color)
################################################################################################################
return 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 filter_fund_pool(index_code, begin_date, end_date,
min_period, ipo_date, track_error_up,
index_name, out_path):
#############################################################################################
# begin_date = '2017-05-31'
# end_date = '2018-05-31'
# ipo_date = '2017-05-31'
# min_period = 200
# index_name = '沪深300'
# index_code = '000300.SH'
# 读取数据
#############################################################################################
fund_nav = Fund().get_fund_factor("Repair_Nav")
index_close = Index().get_index_factor(index_code, None, None, attr=['CLOSE'])
index_close.columns = [index_code]
result = pd.DataFrame([], index=fund_nav.columns, columns=['最近1年跟踪误差', '有效数据长度'])
# 计算最近1年跟踪误差数据
#############################################################################################
fund_nav = fund_nav.ix[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.ix[begin_date: end_date]
result.ix[:, "有效数据长度"] = fund_excess_pct_period.count()
result.ix[:, "最近1年跟踪误差"] = fund_excess_pct_period.std() * np.sqrt(250)
# 筛选
#############################################################################################
result = result.dropna()
result = result[result['有效数据长度'] > min_period]
result = result[result['最近1年跟踪误差'] < track_error_up]
code_str = ','.join(result.index)
data = w.wss(code_str, "fund_benchmark,fund_fullname,fund_setupdate,fund_investtype")
data_pd = pd.DataFrame(data.Data, index=data.Fields, columns=data.Codes).T
data_pd.columns = ['基金基准', '基金全称', '上市日期', '基金类型']
data_pd['上市日期'] = data_pd['上市日期'].map(lambda x: x.strftime('%Y-%m-%d'))
result = pd.concat([data_pd, result], axis=1)
result = result[result["基金基准"].map(lambda x: index_name in x)]
result = result[result["上市日期"] < ipo_date]
result = result[result["基金全称"].map(lambda x: "交易型开放式指数" not in x)]
result = result[result["基金全称"].map(lambda x: "联接" not in x)]
# 输出结果
############################################################################################
out_path = os.path.join(out_path, "filter_fund_pool")
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.ix['format', :] = '0.00'
begin_row_number = 0
begin_col_number = 1
color = "red"
file_name = os.path.join(out_path, '基金初次筛选池_' + index_name + '.xlsx')
sheet_name = "基金筛选池"
write_pandas(file_name, sheet_name, begin_row_number, begin_col_number, result, num_format_pd, color)
