def cal_summary_table(fund_name, fund_code, fund_type, date_array, benchmark_array): """ 主动股票型基金表现总结 分区间计算 基金表现(累计收益 年化收益 年化波动 最大回撤 收益波动比) 分区间计算 分基准 计算基准表现(累计收益 年化收益 年化波动 最大回撤 收益波动比) """ # 分类读取基金数据 fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type) performance_table = pd.DataFrame([], columns=date_array[:, 0]) fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame([], columns=['nav'])) for i_date in range(date_array.shape[0]): label = date_array[i_date, 0] bd = Date().change_to_str(date_array[i_date, 1]) ed = Date().change_to_str(date_array[i_date, 2]) performance_table.ix[fund_name + "累计收益", label] = fs.get_interval_return(bd, ed) performance_table.ix[fund_name + "年化收益", label] = fs.get_interval_return_annual( bd, ed) performance_table.ix[fund_name + "年化波动", label] = fs.get_interval_std_annual(bd, ed) performance_table.ix[fund_name + "最大回撤", label] = fs.get_interval_max_drawdown(bd, ed) performance_table.ix[fund_name + "收益波动比", label] = fs.get_interval_return_std_ratio( bd, ed) for i_benchmark in range(benchmark_array.shape[0]): benchmark_name = benchmark_array[i_benchmark, 0] benchmark_code = benchmark_array[i_benchmark, 1] benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"]) fs = FinancialSeries(pd.DataFrame(benchmark_data), pd.DataFrame([], columns=['nav'])) for i_date in range(date_array.shape[0]): label = date_array[i_date, 0] bd = Date().change_to_str(date_array[i_date, 1]) ed = Date().change_to_str(date_array[i_date, 2]) performance_table.loc[benchmark_name + "累计收益", label] = fs.get_interval_return(bd, ed) performance_table.loc[benchmark_name + "年化收益", label] = fs.get_interval_return_annual( bd, ed) performance_table.loc[benchmark_name + "年化波动", label] = fs.get_interval_std_annual( bd, ed) performance_table.loc[benchmark_name + "最大回撤", label] = fs.get_interval_max_drawdown( bd, ed) return performance_table
def get_fund_last_performance(self): """ 基金和对比基准最近业绩表现 """ date_array = self.get_fund_performace_date() fund_nav = self.fund_nav result_fund = pd.DataFrame([], columns=['收益率', '超额收益率', '年化跟踪误差']) # fund bench_series = Index().get_index_factor(self.bench_code, attr=['CLOSE']) fund_series = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ']), pd.DataFrame(bench_series)) for i in range(len(date_array)): label = date_array[i][0] beg_date = date_array[i][1] end_date = date_array[i][2] pct = fund_series.get_interval_return(beg_date, end_date, short_handled=True) bench_pct = fund_series.get_interval_return_benchmark(beg_date, end_date) te = fund_series.get_interval_tracking_error(beg_date, end_date) result_fund.loc[label, "收益率"] = pct try: result_fund.loc[label, "超额收益率"] = pct - bench_pct * 0.95 except Exception as e: result_fund.loc[label, "超额收益率"] = "" result_fund.loc[label, "年化跟踪误差"] = te columns = list(result_fund.columns) result_fund['最近表现'] = result_fund.index columns.insert(0, '最近表现') result_fund = result_fund[columns] return result_fund
def cal_summary_table_enhanced_fund(fund_name, fund_code, fund_type, date_array, benchmark_code, benchmark_name, benchmark_ratio=1.0): """ 指数型基金表现总结 分区间计算 基金和基准表现(累计收益 年化收益 超额收益 跟踪误差 信息比率 超额收益最大回撤等) """ # 分类读取基金数据 fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type) benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"]) enhanced_table = pd.DataFrame([], columns=date_array[:, 0]) fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame(benchmark_data), benchmark_ratio) for i_date in range(date_array.shape[0]): label = date_array[i_date, 0] bd = Date().change_to_str(date_array[i_date, 1]) ed = Date().change_to_str(date_array[i_date, 2]) enhanced_table.loc[fund_name + "累计收益", label] = fs.get_interval_return(bd, ed) enhanced_table.loc[benchmark_name + "累计收益", label] = fs.get_interval_return_benchmark( bd, ed) bench_return = fs.get_interval_return_benchmark_ratio(bd, ed) enhanced_table.loc[benchmark_name + "*%s累计收益" % benchmark_ratio, label] = bench_return enhanced_table.loc[fund_name + "超额收益", label] = fs.get_interval_excess_return(bd, ed) enhanced_table.loc[fund_name + "超额年化收益", label] = fs.get_interval_excess_return_annual( bd, ed) enhanced_table.loc[fund_name + "跟踪误差", label] = fs.get_interval_tracking_error(bd, ed) enhanced_table.loc[fund_name + "信息比率", label] = fs.get_interval_mean_ir(bd, ed) enhanced_table.loc[ fund_name + "超额收益最大回撤", label] = fs.get_interval_excess_return_max_drawdown(bd, ed) enhanced_table.loc[fund_name + "标准差", label] = fs.get_interval_std_annual(bd, ed) enhanced_table.loc[fund_name + "夏普比率", label] = fs.get_interval_shape_ratio( bd, ed, 0.03) enhanced_table.loc[fund_name + "最大回撤", label] = fs.get_interval_max_drawdown(bd, ed) enhanced_table.loc[fund_name + "年化收益", label] = fs.get_interval_return_annual(bd, ed) enhanced_table.loc[ benchmark_name + "年化收益", label] = fs.get_interval_return_annual_benchmark(bd, ed) return enhanced_table
def mfcteda_fund_excess_return(self, end_date, fund_type, fund_code, fund_name, benchmark_code, benchmark_ratio): """ 计算1个基金今年所有月份超额收益 """ date_pd = self.get_date_pd(end_date) if fund_type == "公募": fund_data = MfcData().get_mfc_public_fund_nav(fund_code) fund_data = fund_data['NAV_ADJ'] else: fund_data = MfcData().get_mfc_private_fund_nav(fund_name) fund_data = fund_data['累计复权净值'] benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"]) return_pd = pd.DataFrame([], columns=date_pd.columns, index=[fund_name]) for i in date_pd.columns: beg_date = date_pd.ix["beg_date", i] end_date = date_pd.ix["end_date", i] fs = FinancialSeries( pd.DataFrame(fund_data), pd.DataFrame(benchmark_data) * benchmark_ratio) try: return_pd.ix[fund_name, i] = fs.get_interval_excess_return( beg_date, end_date) except Exception as e: pass return return_pd
def rank_excess_fund(self, fund_pool_name, ge_index_code, my_index_code, my_fund_code, beg_date, end_date): """ 计算某只基金在基金池的超额收益排名 这只基金指定基准 其他默认为windqa """ fund_pool = FundPool().get_fund_pool_all(date="20181231", name=fund_pool_name) fund_pool = fund_pool[fund_pool['setupdate'] < beg_date] fund_pool = list(fund_pool['wind_code'].values) fund_pool.append(my_fund_code) result = pd.DataFrame([], index=fund_pool) data = FundFactor().get_fund_factor("Repair_Nav") for i in range(0, len(fund_pool)): fund_code = fund_pool[i] if fund_code == my_fund_code: index_code = my_index_code else: index_code = ge_index_code try: print(fund_code, index_code, beg_date, end_date) fund = pd.DataFrame(data[fund_code]) index = Index().get_index_factor(index_code, attr=["CLOSE"]) fs = FinancialSeries(pd.DataFrame(fund), pd.DataFrame(index)) fund_return = fs.get_interval_return(beg_date, end_date) bench_return = fs.get_interval_return_benchmark( beg_date, end_date) result.loc[fund_code, "基准收益"] = bench_return result.loc[fund_code, "基金收益"] = fund_return result.loc[fund_code, "超额收益"] = -bench_return + fund_return except Exception as e: print(e) result = result.dropna() result = result[~result.index.duplicated()] result = result.sort_values(by=['超额收益'], ascending=False) result['收益名次'] = range(1, len(result) + 1) result['收益排名'] = result['收益名次'].map( lambda x: str(x) + '/' + str(len(result))) result['收益排名百分比'] = result['收益名次'].map(lambda x: x / len(result)) excess_return = result.loc[my_fund_code, "超额收益"] pct = result.loc[my_fund_code, "收益排名百分比"] rank_str = result.loc[my_fund_code, "收益排名"] result.to_csv( os.path.join( self.data_path, "超额收益_%s_%s_%s.csv" % (my_fund_code, beg_date, end_date))) return excess_return, pct, rank_str
def get_fund_last_performance(self): """ 基金和对比基准最近业绩表现 """ date_array = self.get_fund_performace_date() fund_nav = self.fund_nav result_fund = pd.DataFrame([], columns=['收益率']) # fund fund_series = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ'])) for i in range(len(date_array)): label = date_array[i][0] beg_date = date_array[i][1] end_date = date_array[i][2] pct = fund_series.get_interval_return(beg_date, end_date, short_handled=True) result_fund.loc[label, "收益率"] = pct # benchmark for i_bench in range(len(self.comparsion_bench_list)): bench_name = self.comparsion_bench_list[i_bench][0] bench_code = self.comparsion_bench_list[i_bench][1] index_close = Index().get_index_factor(bench_code, attr=['CLOSE']) index_series = FinancialSeries(pd.DataFrame(index_close['CLOSE'])) for i in range(len(date_array)): label = date_array[i][0] beg_date = date_array[i][1] end_date = date_array[i][2] pct = index_series.get_interval_return(beg_date, end_date, short_handled=True) result_fund.loc[label, bench_name] = pct columns = list(result_fund.columns) result_fund['最近表现'] = result_fund.index columns.insert(0, '最近表现') result_fund = result_fund[columns] return result_fund
def return_index(end_date, index_code, index_name, index_ratio, mage_date): """ 某个指数收益率 """ 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") before_10y = datetime(year=today.year - 10, 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年", "20150101", '20151231', "20140930"], ["2014年", "20140101", '20141231', "20130930"], ["2013年", "20130101", '20131231', "20120930"], ["2012年", "20120101", '20121231', "20110930"], ["2011年", "20110101", '20111231', "20100930"], ["2010年", "20100101", '20101231', "20090930"], ["2009年", "20090101", '20091231', "20080930"], ["2008年", "20080101", '20081231', "20070930"], ["2007年", "20070101", '20071231', "20060930"], ["成立以来", mage_date, end_date, mage_date], ["过去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], ["过去10年", before_10y, end_date, before_10y], ]) performance_table = pd.DataFrame([], index=[index_name]) for i_date in range(len(date_array)): label = date_array[i_date, 0] beg_date = date_array[i_date, 1] end_date = date_array[i_date, 2] if beg_date >= str(int(mage_date)): index_close = Index().get_index_factor(index_code, attr=['CLOSE']) print(index_close.tail()) fs = FinancialSeries(pd.DataFrame(index_close)) pct = fs.get_interval_return(beg_date, end_date) print(pct, index_name) if type(pct) == np.float64: pct *= float(index_ratio) performance_table.loc[index_name, label] = pct else: performance_table.ix[index_name, label] = "NAN" print(performance_table) return performance_table
def cal_fund_index(self, fund_pool_name, my_index_code, my_fund_code, beg_date, end_date): """ 计算某只基金所在基金池的各项指标(包括基金收益、基金基准收益、超额收益、跟踪误差及信息比率) 剔除新基金 """ fund_pool = Fund().get_fund_pool_all(date="20181231", name=fund_pool_name) fund_pool = fund_pool[fund_pool['setupdate'] < beg_date] fund_pool = list(fund_pool['wind_code'].values) fund_pool.append(my_fund_code) result = pd.DataFrame([], index=fund_pool) data = Fund().get_fund_factor("Repair_Nav") for i in range(0, len(fund_pool)): fund_code = fund_pool[i] if fund_code == my_fund_code: index_code = my_index_code else: index_code = "881001.WI" print(fund_code, index_code, beg_date, end_date) try: fund = pd.DataFrame(data[fund_code]) index = Index().get_index_factor(index_code, attr=["CLOSE"]) fs = FinancialSeries(pd.DataFrame(fund), pd.DataFrame(index)) fund_return = fs.get_interval_return_annual(beg_date, end_date) bench_return = fs.get_interval_return_benchmark(beg_date, end_date) excess_return = fs.get_interval_excess_return(beg_date, end_date) tracking_error = fs.get_interval_tracking_error(beg_date, end_date) ir = excess_return / tracking_error result.loc[fund_code, "基准收益"] = bench_return result.loc[fund_code, "基金收益"] = fund_return result.loc[fund_code, "超额收益"] = - bench_return + fund_return result.loc[fund_code, "跟踪误差"] = tracking_error result.loc[fund_code, "信息比率"] = ir except Exception as e: print(e) result = result.dropna() result = result[~result.index.duplicated()] result = result.sort_values(by=['基金收益'], ascending=False) result['收益名次'] = range(1, len(result) + 1) result['收益排名'] = result['收益名次'].map(lambda x: str(x) + '/' + str(len(result))) file = "%s_%s_%s_%s.csv" % (fund_pool_name, my_fund_code, beg_date, end_date) file = os.path.join(self.data_path, 'data', file) result.to_csv(file)
def cal_summary_table_sample(fund_name, fund_code, fund_type, date_array, benchmark_array): """ 主动股票型基金表现总结(简单版) 分区间计算 基金表现 累计收益 分区间计算 分基准 计算基准累计收益 """ fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type) performance_table = pd.DataFrame([], columns=date_array[:, 0]) fs = FinancialSeries(pd.DataFrame(fund_data), pd.DataFrame([], columns=['nav'])) for i_date in range(date_array.shape[0]): label = date_array[i_date, 0] bd = Date().change_to_str(date_array[i_date, 1]) ed = Date().change_to_str(date_array[i_date, 2]) print("Cal Interval Return ", bd, ed) performance_table.ix[fund_name + "累计收益", label] = fs.get_interval_return(bd, ed) for i_benchmark in range(benchmark_array.shape[0]): benchmark_name = benchmark_array[i_benchmark, 0] benchmark_code = benchmark_array[i_benchmark, 1] benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"]) fs = FinancialSeries(pd.DataFrame(benchmark_data), pd.DataFrame([], columns=['nav'])) for i_date in range(date_array.shape[0]): label = date_array[i_date, 0] bd = Date().change_to_str(date_array[i_date, 1]) ed = Date().change_to_str(date_array[i_date, 2]) performance_table.ix[benchmark_name + "累计收益", label] = fs.get_interval_return(bd, ed) return performance_table
def cal_summary_period(self, beg_date=None, end_date=None): """ 计算组合在区间内 收益率、波动率等表现情况 并画图并存储图片 """ self.get_port_return() if beg_date is None: beg_date = self.port_return.index[0] if end_date is None: end_date = self.port_return.index[-1] fs = FinancialSeries( pd.DataFrame(self.port_return["CumPortReturn"] + 1.0), pd.DataFrame(self.port_return["CumIndexReturn"] + 1.0)) port_return = self.port_return.loc[beg_date:end_date, :] result = pd.DataFrame([]) beg_date = port_return.index[0] end_date = port_return.index[-1] label = str(beg_date)[0:4] + '年' result.loc[label, '开始时间'] = beg_date result.loc[label, '结束时间'] = end_date result.loc[label, "收益率"] = fs.get_interval_return(beg_date, end_date) result.loc[label, "年化收益率"] = fs.get_interval_return_annual( beg_date, end_date) result.loc[label, "年化波动率"] = fs.get_interval_std_annual(beg_date, end_date) result.loc[label, "年化跟踪误差"] = fs.get_interval_tracking_error( beg_date, end_date) result.loc[label, "最大回撤率"] = fs.get_interval_max_drawdown(beg_date, end_date) result.loc[label, "超额收益率"] = fs.get_interval_excess_return( beg_date, end_date) result.loc[label, "年化超额收益率"] = fs.get_interval_excess_return_annual( beg_date, end_date) result.loc[label, "超额收益率最大回撤"] = fs.get_interval_excess_return_max_drawdown( beg_date, end_date) result.loc[label, "基准收益率"] = fs.get_interval_return_benchmark( beg_date, end_date) result.loc[label, "基准年化收益率"] = fs.get_interval_return_annual_benchmark( beg_date, end_date) result.loc[label, "基准年化波动率"] = fs.get_interval_std_annual_benchmark( beg_date, end_date) result.loc[label, "基准最大回撤率"] = fs.get_interval_max_drawdown_benchmark( beg_date, end_date) self.plot_port_return_period(beg_date, end_date) return result.T
def return_fund(end_date, fund_code, fund_name, mage_date): """ 某个基金收益率 """ 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") before_10y = datetime(year=today.year - 10, 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年", "20150101", '20151231', "20140930"], ["2014年", "20140101", '20141231', "20130930"], ["2013年", "20130101", '20131231', "20120930"], ["2012年", "20120101", '20121231', "20110930"], ["2011年", "20110101", '20111231', "20100930"], ["2010年", "20100101", '20101231', "20090930"], ["2009年", "20090101", '20091231', "20080930"], ["2008年", "20080101", '20081231', "20070930"], ["2007年", "20070101", '20071231', "20060930"], ["成立以来", mage_date, end_date, mage_date], ["过去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], ["过去10年", before_10y, end_date, before_10y], ]) performance_table = pd.DataFrame([], index=[fund_name]) for i_date in range(len(date_array)): label = date_array[i_date, 0] beg_date = date_array[i_date, 1] end_date = date_array[i_date, 2] if beg_date >= str(int(mage_date)): fund_nav = MfcData().get_mfc_public_fund_nav(fund_code) fs = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ'])) performance_table.ix[fund_name, label] = fs.get_interval_return(beg_date, end_date) else: performance_table.ix[fund_name, label] = "NAN" print(performance_table) return performance_table
def mfcteda_fund_return(self, end_date, fund_type, fund_code, fund_name): """ 计算1个基金今年所有月份收益 """ date_pd = self.get_date_pd(end_date) fund_data = MfcData().get_mfc_nav(fund_code, fund_name, fund_type) return_pd = pd.DataFrame([], columns=date_pd.columns, index=[fund_name]) for i in date_pd.columns: beg_date = date_pd.ix["beg_date", i] end_date = date_pd.ix["end_date", i] fs = FinancialSeries(pd.DataFrame(fund_data)) try: return_pd.ix[fund_name, i] = fs.get_interval_return(beg_date, end_date) except Exception as e: pass return return_pd
def mfcteda_benchmark_return(self, end_date, benchmark_code, benchmark_name): """ 计算1个指数今年所有月份收益 """ date_pd = self.get_date_pd(end_date) benchmark_data = Index().get_index_factor(benchmark_code, attr=["CLOSE"]) return_pd = pd.DataFrame([], columns=date_pd.columns, index=[benchmark_name]) for i in date_pd.columns: beg_date = date_pd.ix["beg_date", i] end_date = date_pd.ix["end_date", i] fs = FinancialSeries(pd.DataFrame(benchmark_data)) try: return_pd.ix[benchmark_name, i] = fs.get_interval_return(beg_date, end_date) except Exception as e: pass return return_pd
def write_public_qf(end_date, save_path): # 参数 ########################################################################################### fund_name = '泰达宏利启富' fund_code = '003912.OF' fund_type = "公募" benchmark_code = '885003.WI' benchmark_name = '偏债混合型基金总指数' benchmark_code_2 = "881001.WI" benchmark_name_2 = "WIND全A" benchmark_ratio = 0.95 setup_date = '20170315' date_array = np.array([["2019年", '20190101', end_date, '20180930'], ["2018年", "20180101", '20181231', "20170930"], ["2017年", setup_date, '20171231', setup_date], ["成立以来", setup_date, end_date, setup_date]]) benchmark_array = np.array([["沪深300", "000300.SH"], ["WIND全A", "881001.WI"], ["中证全债", "H11001.CSI"], ["偏债混合基金指数", '885003.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(fund_name, fund_code, fund_type, date_array, benchmark_array) rank0 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "灵活配置型基金_30", excess=False) rank1 = FundRank().rank_fund_array2(fund_pct, bench_pct, fund_code, date_array, "wind", excess=False) performance_table = pd.concat([performance_table, rank0, rank1], 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 fund_score(self, fund_code, fund_name, end_date, rank_pool, mg_date, fund_type, my_index_code): """ 计算基金得分 """ # index_code = "881001.WI" # fund_code = "162208.OF" # end_date = "20181231" # rank_pool = "普通股票型基金" # mg_date = "20141121" # fund_type = "行业基金" # my_index_code = "FTSE成长" end_date = Date().change_to_datetime(end_date) before_1y = datetime(year=end_date.year, month=1, day=1).strftime("%Y%m%d") before_3y = datetime(year=end_date.year-2, month=1, day=1).strftime("%Y%m%d") before_3y = max(before_3y, "20160101") before_5y = datetime(year=end_date.year-4, month=1, day=1).strftime("%Y%m%d") before_5y = max(before_5y, "20160101") mg_date = max(mg_date, "20160101") end_date = Date().change_to_str(end_date) result = pd.DataFrame([], columns=["名称", "1年收益", "1年排名", "1年排名百分比", "1年得分", "3年收益", "3年排名", "3年排名百分比", "3年得分", "5年收益", "5年排名", "5年排名百分比", "5年得分" ]) result.loc[fund_code, "名称"] = fund_name beg_date = before_1y fund_nav = MfcData().get_mfc_public_fund_nav(fund_code) fs = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ'])) result.loc[fund_code, "1年收益"] = fs.get_interval_return(beg_date, end_date) str_rank, pct = FundRank().rank_fund(fund_code, rank_pool, beg_date, end_date, beg_date, excess=False) result.loc[fund_code, "1年排名百分比"] = pct result.loc[fund_code, "1年排名"] = str_rank result.loc[fund_code, "1年得分"] = self.score(pct) beg_date = before_3y fund_nav = MfcData().get_mfc_public_fund_nav(fund_code) fs = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ'])) result.loc[fund_code, "3年收益"] = fs.get_interval_return(beg_date, end_date) str_rank, pct = FundRank().rank_fund(fund_code, rank_pool, beg_date, end_date, beg_date, excess=False) result.loc[fund_code, "3年排名百分比"] = pct result.loc[fund_code, "3年排名"] = str_rank result.loc[fund_code, "3年得分"] = self.score(pct) beg_date = before_5y fund_nav = MfcData().get_mfc_public_fund_nav(fund_code) fs = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ'])) result.loc[fund_code, "5年收益"] = fs.get_interval_return(beg_date, end_date) str_rank, pct = FundRank().rank_fund(fund_code, rank_pool, beg_date, end_date, beg_date, excess=False) result.loc[fund_code, "5年排名百分比"] = pct result.loc[fund_code, "5年排名"] = str_rank result.loc[fund_code, "5年得分"] = self.score(pct) beg_date = mg_date fund_nav = MfcData().get_mfc_public_fund_nav(fund_code) fs = FinancialSeries(pd.DataFrame(fund_nav['NAV_ADJ'])) result.loc[fund_code, "管理以来收益"] = fs.get_interval_return(beg_date, end_date) str_rank, pct = FundRank().rank_fund(fund_code, rank_pool, beg_date, end_date, beg_date, excess=False) result.loc[fund_code, "管理以来排名"] = str_rank result.loc[fund_code, "管理以来排名百分比"] = pct result.loc[fund_code, "管理以来得分"] = self.score(pct) print(result) return result
def write_public_zz500_adjust(end_date, save_path): # 参数 ########################################################################################### fund_name_adjust = '泰达宏利中证500_adjust' fund_code_adjust = '162216.OF_adjust' fund_name = '泰达宏利中证500' fund_code = '162216.OF' fund_type = "公募" benchmark_code = '000905.SH' benchmark_name = '中证500' benchmark_ratio = 0.95 setup_date = '20141003' 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") date_array = np.array([ ["2019年", '20190101', end_date, '20180930'], ["2018年", "20180101", '20181231', "20170930"], ["2017年", "20170101", '20171231', "20160930"], ["2016年", "20160101", "20161231", "20150930"], ["2015年", "20150101", "20151231", "20150101"], ["管理(20141003)以来", setup_date, end_date, setup_date], ["2015年以来", "20150101", end_date, setup_date], ["过去1年", before_1y, end_date, before_1y], ["过去2年", before_2y, end_date, before_2y], ["过去3年", before_3y, end_date, before_3y], ]) benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.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_adjust + '.xlsx') sheet_name = fund_name_adjust 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, "中证500基金", excess=False) performance_table = pd.concat([performance_table, rank2], 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("20150101", 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 + "累计超额收益(2015年以来)" 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( "20150101", 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 + "累计收益(2015年以来)" 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_lh(end_date, save_path): # 参数 ########################################################################################### fund_name = '泰达宏利量化增强' fund_code = '001733.OF' fund_type = "公募" benchmark_code = '000905.SH' benchmark_name = '中证500' benchmark_ratio = 0.95 setup_date = '20160830' date_array = np.array( [["2019年", '20190101', end_date, '20180930'], ["2018年", "20180101", '20181231', "20170930"], ["2017年", "20170101", '20171231', "20160930"], ["2016年", setup_date, "20161231", setup_date], ["成立(20160830)以来", setup_date, end_date, setup_date]]) benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"], ["股票型基金", '885012.WI'], ["创业板指", '399006.SZ'], ["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, "中证500基金", excess=False) 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, "中证500基金", 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], 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 + "累计超额收益(成立以来)" insert_pos = 'B16' 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 = 'B32' 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_quant12(end_date, save_path): # 参数 ########################################################################################### fund_name = '光大量化组合12号' fund_code = fund_name fund_type = "专户" benchmark_code = "000905.SH" benchmark_name = "中证500" setup_date = "20160714" date_array = np.array([["2019年", '20190101', end_date], ["2018年", "20180101", '20181231'], ["2017年", "20170101", '20171231'], ["成立(20160714)至2016年末", setup_date, '20161231'], ["成立(20160714)以来", 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 write_zlhl2018(end_date, save_path): # 参数 ########################################################################################### fund_name = '广州农商行梓霖惠利1号' fund_code = fund_name fund_type = "专户" benchmark_code = '885007.WI' benchmark_name = "混合债券二级基金指数" benchmark_code_2 = "H11001.CSI" benchmark_name_2 = "中证全债指数" setup_date = '20180628' date_array = np.array([["2019年", '20190101', end_date], ["2018年", "20180101", '20181231'], ["20180628以来", '20180628', end_date]]) benchmark_array = np.array([["沪深300", "000300.SH"], ["中证500", "000905.SH"], ["股票型基金", '885012.WI'], ["混合债券二级基金指数", '885007.WI'], ["中证全债指数", "H11001.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 + '2018年.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 # 写入基金绝对表现 ########################################################################################### performance_table = MfcTable().cal_summary_table(fund_name, fund_code, fund_type, date_array, benchmark_array) 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 + "相对" + benchmark_name + " 累计超额收益(成立以来)" 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 # 写入基金和基准收益时间序列 相对指数 ########################################################################################### 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 + "相对" + benchmark_name_2 + " 累计超额收益(成立以来)" insert_pos = 'B32' 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_fxwy(end_date, save_path): # 参数 ########################################################################################### fund_name = '泰达宏利复兴伟业' fund_code = '001170.OF' fund_type = "公募" benchmark_code = '885001.WI' benchmark_name = '偏股混合基金总指数' benchmark_code_2 = "000300.SH" benchmark_name_2 = "沪深300" benchmark_ratio = 0.95 setup_date = '20150421' # 吴华开始管理 也是成立日 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"], ["成立以来(吴华管理)", setup_date, end_date, setup_date], ["过去1年", before_1y, end_date, before_1y], ["过去2年", before_2y, end_date, before_2y], ["过去3年", before_3y, end_date, before_3y], ]) 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, "灵活配置型基金_60", 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 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