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)