1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
# -*- 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 = {'name': fund_name, 'data': ret}
# 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_return_ratio"].values},
"fund": {'name': fund_id, 'data': compare[fund_id + "_return_ratio"].values},
}
r = draw_index_combination_chart(chart_data)
return r
def get_fund_evaluation(args):
"""返回个基点评和雷达图"""
fund_id = args.get('fund_id')
invest_type = args.get('invest_type')
print(single_evaluation(fund_id='HF00005AFK'))
if __name__ == '__main__':
# print(single_evaluation(fund_id='HF00005AFK'))
# print(get_radar_data('HF00005AFK'))
test = fund_index_compare(fund_id="HF00005AFK")