# -*- 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]
# fund_name = get_fund_name(fund_id).values[0][0]
chart_data = {
"xlabels": xlabels,
"index": {'name': '中证500', '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'))
print(fund_index_compare(fund_id='HF00002G4A'))