# -*- encoding: utf-8 -*- # ----------------------------------------------------------------------------- # @File Name : fund_service.py # @Time : 2021/1/14 下午5:31 # @Author : X. Peng # @Email : acepengxiong@163.com # @Software : PyCharm # ----------------------------------------------------------------------------- from app.service.portfolio_diagnose import * from app.utils.draw import draw_index_combination_chart def get_tamp_nav(fund, start_date, rollback=False, invest_type=2): """获取基金ID为fund, 起始日期为start_date, 终止日期为当前日期的基金净值表 Args: fund[str]:基金ID start_date[date]:起始日期 rollback[bool]:当起始日期不在净值公布日历中，是否往前取最近的净值公布日 invest_type[num]:0:公募 1:私募 2:优选 Returns:df[DataFrame]: 索引为净值公布日， 列为复权净值的净值表; 查询失败则返回None """ with TAMP_SQL(tamp_product_engine) as tamp_product, TAMP_SQL(tamp_fund_engine) as tamp_fund: tamp_product_session = tamp_product.session tamp_fund_session = tamp_fund.session # if invest_type == "private": # sql = "SELECT fund_id, price_date, cumulative_nav FROM fund_nav " \ # "WHERE fund_id='{}'".format(fund) # # df = pd.read_sql(sql, con).dropna(how='any') # cur = tamp_product_session.execute(sql) if invest_type == 0: sql = """select distinct `id`, `end_date`, `accum_nav` from `public_fund_nav` where `id`='{}' order by `end_date` ASC""".format( fund) cur = tamp_fund_session.execute(sql) elif invest_type == 1: sql = """select distinct `fund_id`, `price_date`,`cumulative_nav` from `fund_nav` where `fund_id`='{}' order by `price_date` ASC""".format( fund) cur = tamp_fund_session.execute(sql) elif invest_type == 2: sql = """select distinct `fund_id`,`price_date`,`cumulative_nav` from `fund_nav` where `fund_id`='{}' order by `price_date` ASC""".format( fund) cur = tamp_product_session.execute(sql) data = cur.fetchall() df = pd.DataFrame(data, columns=['fund_id', 'price_date', 'cumulative_nav']).dropna(how='any') df.rename({'price_date': 'end_date', 'cumulative_nav': 'adj_nav'}, axis=1, inplace=True) df['end_date'] = pd.to_datetime(df['end_date']) if rollback and df['end_date'].min() < start_date < df['end_date'].max(): while start_date not in list(df['end_date']): start_date -= datetime.timedelta(days=1) df = df[df['end_date'] >= start_date] df.drop_duplicates(subset='end_date', inplace=True, keep='first') df.set_index('end_date', inplace=True) df.sort_index(inplace=True, ascending=True) return df def single_evaluation(fund_id, invest_type=2, index_id='000905.SH'): """ 1、该基金整体表现优秀/良好/一般，收益能力优秀/良好/合格/较差，回撤控制能力优秀/良好/合格/较差，风险收益比例较高/一般/较低； 2、在收益方面，该基金年化收益能力高于/持平/低于同类基金平均水平，有x%区间跑赢大盘/指数，绝对收益能力优秀/一般； 3、在风险方面，该基金抵御风险能力优秀/良好/一般，在同类基金中处于高/中/低等水平，最大回撤为x%，高于/持平/低于同类基金平均水平； 4、该基金收益较好/较差的同时回撤较大/较小，也就是说，该基金在用较大/较小风险换取较大/较小收益，存在较高/较低风险； 5、基金经理，投资年限5.23年，经验丰富；投资能力较强，生涯中共管理过X只基金，历任的X只基金平均业绩在同类中处于上游水平，其中x只排名在前x%；生涯年化回报率x%，同期大盘只有x%； :param fund_id: 基金ID :param index_id: 指数ID :param invest_type: 投资类型：0：公募 1：私募 2：探普优选 """ end_date = datetime.datetime(datetime.date.today().year, datetime.date.today().month, 1) - datetime.timedelta(1) start_date = cal_date(end_date, 'Y', 1) z_score = search_rank(fund_rank, fund_id, metric='z_score') total_level = np.select([z_score >= 80, 70 <= z_score < 80, z_score < 70], [0, 1, 2]).item() index_return_monthly = get_index_monthly(index_id, start_date) fund_nav = get_tamp_nav(fund_id, start_date, invest_type=invest_type) fund_nav_monthly = fund_nav.groupby([fund_nav.index.year, fund_nav.index.month]).tail(1) fund_nav_monthly = rename_col(fund_nav_monthly, fund_id) fund_return_monthly = simple_return(fund_nav_monthly[fund_id].astype(float)) index_return_monthly.index = index_return_monthly.index.strftime('%Y-%m') fund_return_monthly.index = fund_return_monthly.index.strftime('%Y-%m') compare = pd.merge(index_return_monthly, fund_return_monthly, how='inner', left_index=True, right_index=True) fund_win_rate = ((compare[fund_id] - compare['pct_chg']) > 0).sum() / compare[fund_id].count() return_rank = search_rank(fund_rank, fund_id, metric='annual_return_rank') return_level = np.select([return_rank >= 0.8, 0.7 <= return_rank < 0.8, 0.6 <= return_rank < 0.7, return_rank < 0.6], [0, 1, 2, 3]).item() return_bool = 1 if return_level > 2 else 0 return_triple = return_level - 1 if return_level >= 2 else return_level drawdown_rank = search_rank(fund_rank, fund_id, metric='max_drawdown_rank') drawdown_value = search_rank(fund_rank, fund_id, metric='max_drawdown') drawdown_level = np.select([drawdown_rank >= 0.8, 0.7 <= drawdown_rank < 0.8, 0.6 <= drawdown_rank < 0.7, drawdown_rank < 0.6], [0, 1, 2, 3]).item() drawdown_bool = 1 if drawdown_level > 2 else 0 drawdown_triple = drawdown_level - 1 if drawdown_level >= 2 else drawdown_level sharp_rank = search_rank(fund_rank, fund_id, metric='sharp_ratio_rank') sharp_level = np.select([sharp_rank >= 0.8, 0.6 <= sharp_rank < 0.8, sharp_rank < 0.6], [0, 1, 2]).item() data = {1: [total_level, return_level, drawdown_level, sharp_level], 2: [return_triple, format(fund_win_rate, '.2%'), return_bool], 3: [drawdown_triple, drawdown_triple, format(drawdown_value, '.2%'), drawdown_triple], 4: [return_bool, drawdown_bool, drawdown_bool, return_bool, drawdown_bool]} x = '30%' content = { # 第一个评价 1: [["优秀", "良好", "一般"], ["优秀", "良好", "合格", "较差"], ["优秀", "良好", "合格", "较差"], ["高", "一般", "较低"]], # 第二个评价 2: [["高于", "持平", "低于"], x, ["优秀", "一般"]], # 第三个评价 3: [["优秀", "良好", "一般"], ["高", "中", "低"], x, ["高于", "持平", "低于"]], # 第四个评价 4: [["较好", "较差"], ["较小", "较大"], ["较小", "较小"], ["较大", "较小"], ["较低", "较高"]]} sentence = { 1: "该基金整体表现%s，收益能力%s，回撤控制能力%s，风险收益比例%s；\n", 2: "在收益方面，该基金年化收益能力%s同类基金平均水平，有%s区间跑赢指数，绝对收益能力%s；\n", 3: "在风险方面，该基金抵御风险能力%s，在同类基金中处于%s等水平，最大回撤为%s，%s同类基金平均水平；\n", 4: "该基金收益%s的同时回撤%s，也就是说，该基金在用%s风险换取%s收益，存在%s风险；\n"} ret = [] fund_name = get_fund_name(fund_id).values[0][0] i = 1 for k, v in data.items(): single_sentence = str(i) + "、" + sentence[k] % translate_single(content, k, v) ret.append(single_sentence) i += 1 evaluation_dict = {'fund_name': fund_name, 'status': '', 'evaluation': ret, 'radar_chart_path': fund_index_compare(fund_id=fund_id, invest_type=invest_type)} # if objective: # if fund_id in self.abandon_fund_score + self.abandon_fund_corr: # evaluation_dict['status'] = "换仓" # elif fund_id in self.portfolio: # evaluation_dict['status'] = "保留" # else: # evaluation_dict['status'] = "" return evaluation_dict def fund_index_compare(fund_id, invest_type=2, index_id='000905.SH'): start_date = datetime.datetime(2000, 1, 1) index_daily = get_index_daily(index_id, start_date) fund_nav = get_tamp_nav(fund_id, start_date, invest_type=invest_type) fund_nav = rename_col(fund_nav, fund_id) compare = pd.merge(index_daily, fund_nav, how='inner', left_index=True, right_index=True) compare[index_id + '_return_ratio'] = (compare[index_id] / compare[index_id].iloc[0] - 1) * 100 compare[fund_id + '_return_ratio'] = (compare[fund_id] / compare[fund_id].iloc[0] - 1) * 100 xlabels = ["" for i in range(len(compare))] index_name = get_index_name(index_id).values[0][0] chart_data = { "xlabels": xlabels, "index": {'name': index_name, 'data': compare[index_id + "_return_ratio"].values}, "fund": {'name': fund_id, 'data': compare[fund_id + "_return_ratio"].values}, } r = draw_index_combination_chart(chart_data) return r if __name__ == '__main__': print(single_evaluation(fund_id='HF00005AFK'))