def stock_ratio_10(beg_date, end_date):
factor_name = "Stock_Ratio_10"
fund_holder = Fund().get_fund_holding_all()
quarter_date = Date().get_last_fund_quarter_date(end_date)
position_all = Fund().get_fund_factor("Stock_Ratio", date_list=[quarter_date], fund_pool=None).T
position_all.columns = ['Stock_Weight']
position_all = position_all[position_all['Stock_Weight'] > 65]
code_list = list(position_all.index)
date_list = Date().get_normal_date_series(beg_date=beg_date, end_date=end_date, period="Q")
code_list.sort()
date_list.sort()
new_data = pd.DataFrame([], index=code_list, columns=date_list)
for i_date in range(len(date_list)):
for i_fund in range(len(code_list)):
fund_code = code_list[i_fund]
date = date_list[i_date]
holder = fund_holder[fund_holder.FundCode == fund_code]
holder = holder[holder.Date == date]
holder = holder.sort_values(by=['Weight'], ascending=False)
holder = holder.reset_index(drop=True)
if len(holder) >= 10:
holder = holder.ix[0:10, :]
new_data.ix[fund_code, date] = holder.Weight.sum()
print("计算 %s 在 %s 的前10大重仓股票为 %s" % (fund_code, date, holder.Weight.sum()))
out_file = Parameter().get_read_file(factor_name)
if os.path.exists(out_file):
data = pd.read_csv(out_file, encoding='gbk', index_col=[0])
data.index = data.index.map(str)
data = pandas_add_row(data, new_data)
else:
print(" File No Exist ", factor_name)
data = new_data
data.to_csv(out_file)
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_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)
