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