#!/usr/bin/python3.6 # -*- coding: utf-8 -*- # @Time : 2020/12/25 13:16 # @Author : Jie. Z # @Email : zhaojiestudy@163.com # @File : fund_performance.py # @Software: PyCharm import pandas as pd # 数据处理 入库操作 def dataprepare(fund_id , fav_freq , index_code, enddate, strategy, data_source, status): #银行利率，暂时写死，后期改为动态获取 BANK_RATE = 0.015 # 同时读取基金净值和指数，条件是日期同时存在 ''' global trade_df if trade_df.empty: trade_df = gettradecal('2001-01-01', datetime.date.strftime(datetime.date.today(), '%Y-%m-%d')) ''' df1 = gettradecal('2001-01-01', datetime.date.strftime(datetime.date.today(), '%Y-%m-%d')) global current_env ,origion_env pool = PooledDB(pymysql, maxconnections=100, blocking= True, host=origion_env[current_env]['host'], port=int(origion_env[current_env]['port']) ,user=origion_env[current_env]['user'], password=origion_env[current_env]['password'],database=origion_env[current_env]['database'], charset="utf8") # 5为连接池里的最少连接数 conn = pool.connection() # 以后每次需要数据库连接就是用connection（）函数获取连接就好了 cur = conn.cursor() if index_code == '000000': handle_indexes = True else: handle_indexes = False if handle_indexes: #待修改 sql = """SELECT distinct a.price_date as datetime,a.close as net_worth, a.close as benchmark, a.close as net FROM fund_market_indexes a WHERE a.index_id= '%s' and a.price_date<= '%s' order by a.price_date ASC""" % (fund_id, enddate) else: sql = """SELECT distinct a.price_date as datetime,a.cumulative_nav as net_worth,b.close as benchmark, a.nav as net FROM fund_nav a ,fund_market_indexes b WHERE a.price_date=b.price_date and b.index_id= '%s' and a.fund_id= '%s' and a.price_date<= '%s' order by a.price_date ASC""" % (index_code, fund_id, enddate) print(sql) cur.execute(sql) # 5.获取数据 data = cur.fetchall() if len(data)==0: return # 处理数据 col_result = cur.description columns = [] for i in range(len(col_result)): columns.append(col_result[i][0]) # 设置dataframe的columns df = pd.DataFrame(columns=columns) # 插入数据，把查询结果存入dataframe for i in range(len(data)): df.loc[i] = list(data[i]) df['datetime'] = pd.to_datetime(df['datetime'], format='%Y/%m/%d') # 6.关闭cursor cur.close() # 7.关闭connection conn.close() # 字符串转化为float df['net_worth'] = handle_data(df['net_worth']) df['net'] = handle_data(df['net']) df['benchmark'] = handle_data(df['benchmark']) if len(df) < 1: return print('重采样前：',len(df)) print(df) # 判断基金净值更新频率，进行dataframe处理对齐 # 如果是日更，则按照交易日补齐，没有数据的用昨日数据；如果是周更，取当周最后一个更新，如果没有数据则取前一周数据；如果是月更，取当月最后一个数据，如果没有数据则取前一个月数据； # 收益率类型，1: 天；2: 周；3: 半月；4: 月；5: 季【fund_profit_rate，天/周/月】 df = df.drop_duplicates(['datetime']) df.set_index('datetime', inplace=True) df.dropna(axis=0, how='any', inplace=True) # 删除表中含有任何NaN的行 #把df重采样 #df = resample(df=df, trading_cal=df1, freq=fav_freq) print('重采样后：',len(df)) print(df) #保留原始dataframe origin_df = df # 获取序列中的开始和结束时间 datestart = df.index[0] dateend = df.index[-1] strstartdate = datestart.strftime("%Y%m%d") strenddate = dateend.strftime("%Y%m%d") #避免出现指数还没更新，但净值已更新的情况 if dateend < enddate: return print('---Step1: 开始准备数据---') #最近净值日期、最近净值 price_date = origin_df.index[-1] cumulative_nav = origin_df.iloc[-1,0] cumulative_index = origin_df.iloc[-1,1] cumulative_ori_nav = origin_df.iloc[-1,2] print('最近净值日期/值: %s and %s ' %(price_date,cumulative_nav)) # 根据基金更新频率产生对应的N,数据类型， 周（52）， 月（12）， 日（250) if fav_freq == 1: N = 250 # 日更新 elif fav_freq == 2: N = 52 # 周更新 elif fav_freq == 3: N = 24 # 半周更新 elif fav_freq == 4: N = 12 # 月更新 elif fav_freq == 5: N = 3 # 季度更新 else: N = 250 #没有设置freq默认是天更 print(N) print('开始计算:') # 区间收益 # 前1/3/6收益率 print('当前日期', dateend) m = [1, 3, 6] range_return_m = [] b_range_return_m = [] cum_returns_m = [] b_cum_returns_m = [] for i in range(len(m)): pre_result , pre_date = dfmonth_minus(df,dateend, m[i]) print('pre_date:' , pre_date, 'nowdate:' , dateend) print('.....近N月时间.', pre_date) if pre_result == 1: now_value = df.loc[df.index == dateend]['net_worth'][0] old_value = df.loc[df.index == pre_date]['net_worth'][0] r = '%.6f' % (((now_value/old_value)-1)) range_return_m.append(r) print (now_value,old_value,r) now_value = df.loc[df.index == dateend]['benchmark'][0] old_value = df.loc[df.index == pre_date]['benchmark'][0] b_r = '%.6f' % (((now_value/old_value)-1)) print (now_value,old_value,b_r) b_range_return_m.append(b_r) # 简单收益率 # print(y_time['net_worth']) y_time = df.loc[(df.index >= pre_date) & (df.index <= dateend)] y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth']) y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark']) # 计算累积收益率 y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1 y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1 print(y_time) cum_returns_m.append(y_time['cum_returns'].iloc[-1]) b_cum_returns_m.append(y_time['b_cum_returns'].iloc[-1]) # 最近1/2/3/4/5/10年、今年以来、成立以来、收益率，波动率，夏普，回撤 y = [1, 2, 3, 4, 5, 10] range_return_y = [] #收益率 b_range_return_y = [] annual_return_y = [] #年化收益率 b_annual_return_y = [] date_y = [] #日期 range_md_y = [] #回撤 b_range_md_y = [] range_md_y_begin = [] range_md_y_end = [] b_range_md_y_begin = [] b_range_md_y_end = [] sigma_y = [] b_sigma_y = [] sharpe_y = [] b_sharpe_y = [] alpha_y = [] beta_y = [] sortino_y = [] b_sortino_y = [] sortion_ratio_y = [] b_sortion_ratio_y = [] cum_returns_y = [] b_cum_returns_y = [] for i in range(len(y)): pre_result , pre_date = dfyear_minus(df,dateend, y[i]) print('.....近N年时间.', 'pre_date:' , pre_date, 'nowdate:' , dateend) if pre_result == -1: r = b_r = np.nan n_m = b_m = np.nan else: y_time = df.loc[(df.index >= pre_date) & (df.index <= dateend)] #基础收益率、基准收益率 r , b_r = simple_2days_return(y_time , pre_date ,dateend ) print ('base 基准率：' , pre_date,dateend,r, b_r) range_return_y.append(r) b_range_return_y.append(b_r) #最大回撤、基准最大回撤（回撤值，开始位置，结束位置） n_m, n_m_begin, n_m_end = max_drawdown(list(y_time['net_worth'])) range_md_y.append('%.6f' % (n_m)) range_md_y_begin.append(y_time.index[n_m_begin]) range_md_y_end.append(y_time.index[n_m_end]) b_m, b_n_m_begin, b_n_m_end = max_drawdown(list(y_time['benchmark'])) b_range_md_y.append('%.6f' % (b_m )) b_range_md_y_begin.append(y_time.index[b_n_m_begin]) b_range_md_y_end.append(y_time.index[b_n_m_end]) date_y.append(pre_date) # 简单收益率 # print(y_time['net_worth']) y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth']) y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark']) # 计算累积收益率 y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1 y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1 cum_returns_y.append(y_time['cum_returns'].iloc[-1]) b_cum_returns_y.append(y_time['b_cum_returns'].iloc[-1]) #计算超额收益率 y_time.loc[:, 'excess_returns'] = excess_return(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE / N) y_time.loc[:, 'b_excess_returns'] = excess_return(y_time.loc[:, 'b_returns'].astype("float64"), BANK_RATE / N) #（年化）区间收益率、基准收益率 print('test_0:', r ,b_r) an_r = annual_return(float(r) , y_time ,N) b_an_r = annual_return(float(b_r) , y_time , N) print('test:', an_r , b_an_r) annual_return_y.append('%.6f' % (an_r)) b_annual_return_y.append('%.6f' % (b_an_r)) # 夏普比率 sharpe_y.append('%.6f' %sharpe_ratio(y_time.loc[:, 'excess_returns'], y_time.loc[:, 'returns'].astype("float64"), N)) b_sharpe_y.append('%.6f' %sharpe_ratio(y_time.loc[:, 'b_excess_returns'], y_time.loc[:, 'b_returns'].astype("float64"), N)) # 波动率 sigma_y.append('%.6f' %volatility(y_time.loc[:, 'returns'].astype("float64"), N)) b_sigma_y.append('%.6f' %volatility(y_time.loc[:, 'b_returns'].astype("float64"), N)) print(sharpe_y) print(sigma_y) #alpha/beta alpha , beta = alpha_beta (y_time.loc[:, 'returns'].astype("float64") , y_time.loc[:, 'b_returns'].astype("float64") , N) alpha_y.append('%.6f' %alpha) beta_y.append(('%.6f' % beta)) #索提诺比率 n_downside = downside_risk(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE, N) b_downside = downside_risk(y_time.loc[:, 'b_returns'].astype("float64"), BANK_RATE, N) n_sortino_ratio = sortino_ratio(y_time.loc[:, 'excess_returns'].astype("float64"), n_downside, N) b_sortino_ratio = sortino_ratio(y_time.loc[:, 'b_excess_returns'].astype("float64"), b_downside, N) sortino_y.append('%.6f' %n_downside) b_sortino_y.append('%.6f' %b_downside) sortion_ratio_y.append('%.6f' %n_sortino_ratio) b_sortion_ratio_y.append('%.6f' % b_sortino_ratio) #日/周、收益率/基准收益率 y_time = df print(y_time.loc[:, 'net_worth']) y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth']) y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark']) print('len(y_time):') print(fav_freq,len(y_time)) if fav_freq in [1, 2] and len(y_time)>0 : return_1 = '%.6f' % (y_time['returns'].iloc[-1]) # 日收益 b_return_1 = '%.6f' % (y_time['b_returns'].iloc[-1] ) #日基准收益 if fav_freq == 1: return_d = return_1 b_return_d = b_return_1 #计算周收益 pre_result, pre_date = dfweek_minus(df, dateend, 1) if pre_result == 1: now_value = df.loc[df.index == dateend]['net_worth'][0] old_value = df.loc[df.index == pre_date]['net_worth'][0] return_w = '%.6f' % (((now_value / old_value) - 1) ) print(now_value, old_value, r) now_value = df.loc[df.index == dateend]['benchmark'][0] old_value = df.loc[df.index == pre_date]['benchmark'][0] b_return_w = '%.6f' % (((now_value / old_value) - 1) ) else: return_w = np.nan b_return_w = np.nan else: return_d = np.nan b_return_d = np.nan return_w = return_1 b_return_w = b_return_1 else: return_d = np.nan b_return_d = np.nan return_w = np.nan b_return_w = np.nan #今年以来收益率/基准收益率、成立以来收益率/基准收益率/年化收益率/年化基准收益率 #今年以来 pre_date = datetime.datetime(dateend.year, 1, 1) private_count = len(origin_df.loc[origin_df.index>=pre_date]) location = len(origin_df) - private_count if location<0: pre_date = origin_df.index[0] else: pre_date = origin_df.index[location] print(pre_date,dateend) short_r, b_short_r = simple_2days_return(origin_df, pre_date, dateend) y_time = df.loc[(df.index >= pre_date) & (df.index <= dateend)] # 简单收益率 # print(y_time['net_worth']) y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth']) y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark']) # 计算累积收益率 y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1 y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1 short_cum_returns = y_time['cum_returns'].iloc[-1] short_cum_returns_b = y_time['b_cum_returns'].iloc[-1] #波动率 short_sigma = '%.6f' % volatility(origin_df.loc[pre_date:origin_df.index[-1], 'returns'].astype("float64"), N) #alpha、beta if len(origin_df.loc[pre_date:origin_df.index[-1]]) > 1 : short_alpha, short_beta = alpha_beta(origin_df.loc[pre_date:origin_df.index[-1],'returns'].astype("float64"), origin_df.loc[pre_date:origin_df.index[-1],'b_returns'].astype("float64"),N) else: short_alpha = 0 short_beta = 0 #成立以来 y_time = origin_df long_r, b_long_r = simple_2days_return(y_time, y_time.index[0], y_time.index[-1]) # （年化）基础收益率、基准收益率 an_long_r = annual_return(float(long_r), y_time, N) b_an_long_r = annual_return(float(b_long_r), y_time, N) #波动率 long_sigma = '%.6f' % volatility(y_time.loc[:, 'returns'].astype("float64"), N) #alpha、beta long_alpha, long_beta = alpha_beta (y_time.loc[:, 'returns'].astype("float64") , y_time.loc[:, 'b_returns'].astype("float64") , N) # 简单收益率 # print(y_time['net_worth']) y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth']) y_time.loc[:, 'b_returns'] = simple_return(y_time.loc[:, 'benchmark']) # 计算累积收益率 y_time.loc[:, 'cum_returns'] = np.cumprod(1 + y_time.loc[:, 'returns'].astype("float64")) - 1 y_time.loc[:, 'b_cum_returns'] = np.cumprod(1 + y_time.loc[:, 'b_returns'].astype("float64")) - 1 long_cum_returns = y_time['cum_returns'].iloc[-1] long_cum_returns_b = y_time['b_cum_returns'].iloc[-1] #sharperatio_incep fund_riskadjret_stats 成立以来夏普比例 y_time.loc[:, 'returns'] = simple_return(y_time.loc[:, 'net_worth']) y_time.loc[:, 'excess_returns'] = excess_return(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE / N) long_sharpe = '%.6f'%sharpe_ratio(y_time.loc[:, 'excess_returns'].astype("float64"), y_time.loc[:, 'returns'].astype("float64"), N) # long_downside = downside_risk(y_time.loc[:, 'returns'].astype("float64"), BANK_RATE, N) #b_downside = downside_risk(y_time.loc[:, 'b_returns'], BANK_RATE, N) long_sortino_ratio = sortino_ratio(y_time.loc[:, 'excess_returns'].astype("float64"), long_downside, N) #b_sortino_ratio = sortino_ratio(y_time.loc[:, 'b_excess_returns'], b_downside, N) #成立以来最大回撤 long_n_m, long_n_m_begin, long_n_m_end = max_drawdown(list(y_time['net_worth'])) long_n_m_begin_date = y_time.index[long_n_m_begin] long_n_m_end_date = y_time.index[long_n_m_end] print('---Step2：数据准备结束，把数据存入DB---') #结果存入fund_performance print('结果存入fund_performance') #str_id = str(int(time.mktime(datetime.datetime.now().timetuple()))) #Table ID，时间戳，10位 #str_id = now_to_timestamp(13) str_id = xuehuaid() str_fund_id = fund_id #基金ID dt_end_date = dateend.strftime('%Y-%m-%d') #截至日期 dt_price_date = dateend.strftime('%Y-%m-%d') #最近累计净值日期 print(dt_end_date) dec_cumulative_nav = cumulative_nav #最近累计净值 ts_create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") print(ts_create_time) #开始数据库操作 pool = PooledDB(pymysql, maxconnections=100, blocking=True, host=origion_env[current_env]['host'], port=int(origion_env[current_env]['port']), user=origion_env[current_env]['user'], password=origion_env[current_env]['password'], database=origion_env[current_env]['database'], charset="utf8") # 5为连接池里的最少连接数 conn = pool.connection() # 以后每次需要数据库连接就是用connection（）函数获取连接就好了 cur = conn.cursor() #如果是指数，则把数据插入fund_nav sql ="select id from fund_nav where fund_id='%s' and price_date='%s'" % (fund_id, enddate) fetch_count = cur.execute(sql) data = cur.fetchall() if fetch_count == 0: str_id = xuehuaid() sql = "INSERT INTO fund_nav(id, fund_id, price_date, nav, cumulative_nav, cumulative_nav_withdrawal, create_time,delete_tag) VALUES ('%s', '%s', str_to_date('%s','%%Y-%%m-%%d'), %s, %s, %s , '%s', %d)" % ( str_id, str_fund_id, dt_end_date, dec_cumulative_nav, dec_cumulative_nav,dec_cumulative_nav, ts_create_time, 0) print(sql) try: cur.execute(sql) conn.commit() except Exception as e: print(e.message) else: #获取id str_id = data[0][0] #sql = "update fund_nav set fund_id='%s', price_date='%s', nav='%s',cumulative_nav='%s', cumulative_nav_withdrawal='%s' where id='%s'" % (str_fund_id, dt_price_date, dec_cumulative_nav, dec_cumulative_nav,dec_cumulative_nav, str_id) #更新fund_performance sql ="select id from fund_performance where fund_id='%s' and end_date='%s'" % (fund_id, enddate) print(sql) fetch_count = cur.execute(sql) data = cur.fetchall() if fetch_count == 0: str_id = xuehuaid() sql = "INSERT INTO fund_performance(id, fund_id, end_date, price_date, cumulative_nav, ret_ytd, ret_ytd_bm1 , ret_incep, ret_incep_bm1, ret_incep_a, ret_incep_bm1_a, ret_cum_ytd, ret_cum_ytd_bm1, ret_cum_incep, ret_cum_incep_bm1, create_time, delete_tag) VALUES ('%s', '%s', str_to_date('%s','%%Y-%%m-%%d'), str_to_date('%s','%%Y-%%m-%%d'), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '%s', %d)" % (str_id, str_fund_id, dt_end_date, dt_price_date, dec_cumulative_nav, short_r, b_short_r, long_r, b_long_r,an_long_r, b_an_long_r, short_cum_returns, short_cum_returns_b, long_cum_returns, long_cum_returns_b, ts_create_time, 0) else: #获取id str_id = data[0][0] sql = "update fund_performance set fund_id='%s', end_date='%s', price_date='%s', cumulative_nav='%s', ret_ytd='%s', ret_ytd_bm1='%s' , ret_incep='%s', ret_incep_bm1='%s', ret_incep_a='%s', ret_incep_bm1_a='%s',ret_cum_ytd='%s',ret_cum_ytd_bm1='%s',ret_cum_incep='%s',ret_cum_incep_bm1='%s' where id='%s'" % (str_fund_id, dt_end_date, dt_price_date, dec_cumulative_nav, short_r, b_short_r, long_r, b_long_r,an_long_r, b_an_long_r, short_cum_returns, short_cum_returns_b, long_cum_returns, long_cum_returns_b,str_id) print(sql) try: cur.execute(sql) conn.commit() except Exception as e: print(e.message) #更新日数据和周数据 if fav_freq == 1: sql = "update fund_performance set ret_1day='%s', ret_1day_bm1 = '%s', ret_1w='%s', ret_1w_bm1='%s' where id='%s'" % (return_d, b_return_d, return_w, b_return_w, str_id) print(sql) cur.execute(sql) conn.commit() elif fav_freq == 2: sql = "update fund_performance set ret_1w='%s',ret_1w_bm1='%s' where id='%s'" % (return_w, b_return_w, str_id) print(sql) cur.execute(sql) conn.commit() #处理月份数据 print('开始月份数据处理：',range_return_m, cum_returns_m) if len(range_return_m) >= 1: sql = "update fund_performance set ret_1m='%s', ret_1m_bm1= '%s',ret_cum_1m ='%s', ret_cum_1m_bm1='%s' where id='%s'" % (range_return_m[0],b_range_return_m[0],cum_returns_m[0],b_cum_returns_m[0],str_id) print(sql) cur.execute(sql) if len(range_return_m) >= 2: sql = "update fund_performance set ret_3m='%s', ret_3m_bm1= '%s' ,ret_cum_3m ='%s', ret_cum_3m_bm1='%s' where id='%s'" % (range_return_m[1],b_range_return_m[1],cum_returns_m[1],b_cum_returns_m[1], str_id) print(sql) cur.execute(sql) if len(range_return_m) >= 3: sql = "update fund_performance set ret_6m=%s, ret_6m_bm1= %s ,ret_cum_6m ='%s', ret_cum_6m_bm1='%s' where id='%s'" % (range_return_m[2], b_range_return_m[2],cum_returns_m[2],b_cum_returns_m[2], str_id) print(sql) cur.execute(sql) #处理年份数据 if len(date_y) >= 1: sql = "update fund_performance set ret_1y='%s', ret_1y_bm1= '%s', ret_cum_1y='%s', ret_cum_1y_bm1='%s' where id='%s'" % (range_return_y[0],b_range_return_y[0],cum_returns_y[0],b_cum_returns_y[0],str_id) print(sql) cur.execute(sql) if len(date_y) >= 2: sql = "update fund_performance set ret_2y='%s', ret_2y_bm1= '%s' , ret_2y_a='%s' , ret_2y_bm1_a= '%s',ret_cum_2y='%s', ret_cum_2y_bm1='%s' where id='%s'" % (range_return_y[1],b_range_return_y[1], annual_return_y[1], b_annual_return_y[1], cum_returns_m[1],b_cum_returns_m[1],str_id) print(sql) cur.execute(sql) if len(date_y) >= 3: sql = "update fund_performance set ret_3y='%s', ret_3y_bm1= '%s' , ret_3y_a='%s' , ret_3y_bm1_a= '%s',ret_cum_3y='%s', ret_cum_3y_bm1='%s' where id='%s'" % (range_return_y[2],b_range_return_y[2], annual_return_y[2], b_annual_return_y[2], cum_returns_m[2],b_cum_returns_m[2],str_id) print(sql) cur.execute(sql) if len(date_y) >= 4: sql = "update fund_performance set ret_4y='%s', ret_4y_bm1= '%s' , ret_4y_a='%s' , ret_4y_bm1_a= '%s',ret_cum_4y='%s', ret_cum_4y_bm1='%s' where id='%s'" % (range_return_y[3],b_range_return_y[3], annual_return_y[3], b_annual_return_y[3], cum_returns_y[3],b_cum_returns_y[3],str_id) print(sql) cur.execute(sql) if len(date_y) >= 5: sql = "update fund_performance set ret_5y='%s', ret_5y_bm1= '%s' , ret_5y_a='%s' , ret_5y_bm1_a= '%s',ret_cum_5y='%s', ret_cum_5y_bm1='%s' where id='%s'" % (range_return_y[4],b_range_return_y[4], annual_return_y[4], b_annual_return_y[4], cum_returns_y[4],b_cum_returns_y[4], str_id) print(sql) cur.execute(sql) conn.commit() #结果存入fund_risk_stats print('结果存入fund_risk_stats') sql ="select id from fund_risk_stats where fund_id='%s' and end_date='%s'" % (fund_id, enddate) print(sql) fetch_count = cur.execute(sql) data = cur.fetchall() if fetch_count == 0: str_id = xuehuaid() sql = "INSERT INTO fund_risk_stats(id, fund_id, end_date, create_time, delete_tag) VALUES ('%s', '%s', str_to_date('%s','%%Y-%%m-%%d'), '%s', %d)" % (str_id, str_fund_id, dt_end_date, ts_create_time, 0) else: #获取id str_id = data[0][0] sql = "update fund_risk_stats set fund_id='%s', end_date='%s' where id='%s'" % (str_fund_id, dt_end_date, str_id) print(sql) try: cur.execute(sql) conn.commit() except Exception as e: print(e.message) #处理年份数据 if len(sigma_y) >= 1: int_range_month = month_differ(range_md_y_begin[0],range_md_y_end[0]) sql = "update fund_risk_stats set stddev_1y='%s', alpha_1y = '%s' , beta_1y = '%s' , maxdrawdown_1y= '%s' , maxdrawdown_months_1y = %s, maxdrawdown_peakdate_1y = '%s' , maxdrawdown_valleydate_1y = '%s' where id='%s'" % (sigma_y[0],alpha_y[0],beta_y[0], range_md_y[0],int_range_month,range_md_y_begin[0], range_md_y_end[0],str_id) print(sql) cur.execute(sql) if len(sigma_y) >= 2: int_range_month = month_differ(range_md_y_begin[1],range_md_y_end[1]) sql = "update fund_risk_stats set stddev_2y='%s', alpha_2y = '%s' , beta_2y = '%s' , maxdrawdown_2y= '%s' , maxdrawdown_months_2y = %s, maxdrawdown_peakdate_2y = '%s' , maxdrawdown_valleydate_2y = '%s' where id='%s'" % (sigma_y[1],alpha_y[1],beta_y[1], range_md_y[1],int_range_month,range_md_y_begin[1], range_md_y_end[1],str_id) print(sql) cur.execute(sql) if len(sigma_y) >= 3: int_range_month = month_differ(range_md_y_begin[2],range_md_y_end[2]) sql = "update fund_risk_stats set stddev_3y='%s', alpha_3y = '%s' , beta_3y = '%s' , maxdrawdown_3y= '%s' , maxdrawdown_months_3y = %s, maxdrawdown_peakdate_3y = '%s' , maxdrawdown_valleydate_3y = '%s' where id='%s'" % (sigma_y[2],alpha_y[2],beta_y[2], range_md_y[2],int_range_month,range_md_y_begin[2], range_md_y_end[2],str_id) print(sql) cur.execute(sql) if len(sigma_y) >= 4: int_range_month = month_differ(range_md_y_begin[3],range_md_y_end[3]) sql = "update fund_risk_stats set stddev_4y='%s', alpha_4y = '%s' , beta_4y = '%s' , maxdrawdown_4y= '%s' , maxdrawdown_months_4y = %s, maxdrawdown_peakdate_4y = '%s' , maxdrawdown_valleydate_4y = '%s' where id='%s'" % (sigma_y[3],alpha_y[3],beta_y[3], range_md_y[3],int_range_month,range_md_y_begin[3], range_md_y_end[3],str_id) print(sql) cur.execute(sql) if len(sigma_y) >= 5: int_range_month = month_differ(range_md_y_begin[4],range_md_y_end[4]) sql = "update fund_risk_stats set stddev_5y='%s', alpha_5y = '%s' , beta_5y = '%s' , maxdrawdown_5y= '%s' , maxdrawdown_months_5y = %s, maxdrawdown_peakdate_5y = '%s' , maxdrawdown_valleydate_5y = '%s' where id='%s'" % (sigma_y[4],alpha_y[4],beta_y[4], range_md_y[4],int_range_month,range_md_y_begin[4], range_md_y_end[4],str_id) print(sql) cur.execute(sql) if len(sigma_y) >= 6: int_range_month = month_differ(range_md_y_begin[5],range_md_y_end[5]) sql = "update fund_risk_stats set stddev_10y='%s', alpha_10y = '%s' , beta_10y = '%s' , maxdrawdown_10y= '%s' , maxdrawdown_months_10y = %s, maxdrawdown_peakdate_10y = '%s' , maxdrawdown_valleydate_10y = '%s' where id='%s'" % (sigma_y[5],alpha_y[5],beta_y[5], range_md_y[5],int_range_month,range_md_y_begin[5], range_md_y_end[5],str_id) print(sql) cur.execute(sql) #成立以来最大回撤 int_range_month = month_differ(long_n_m_begin_date, long_n_m_end_date) sql = "update fund_risk_stats set maxdrawdown_incep= '%s', maxdrawdown_months_incep = '%s' , maxdrawdown_peakdate_incep = '%s', maxdrawdown_valleydate_incep = '%s' where id='%s'" % ( long_n_m, int_range_month, long_n_m_begin_date, long_n_m_end_date, str_id) print(sql) cur.execute(sql) #成立以来/今年回来，波动率 sql = "update fund_risk_stats set stddev_ytd= '%s', stddev_incep = '%s' where id='%s'" % ( short_sigma, long_sigma, str_id) print(sql) cur.execute(sql) #成立以来/今年以来，alpha/beta sql = "update fund_risk_stats set alpha_ytd= '%s', alpha_incep = '%s', beta_ytd= '%s', beta_incep = '%s' where id='%s'" % ( short_alpha, long_alpha, short_beta, long_beta, str_id) print(sql) cur.execute(sql) conn.commit() #结果存入fund_riskadjret_stats print('结果存入fund_riskadjret_stats') sql ="select id from fund_riskadjret_stats where fund_id='%s' and end_date='%s'" % (fund_id, enddate) fetch_count = cur.execute(sql) data = cur.fetchall() if fetch_count == 0: str_id = xuehuaid() sql = "INSERT INTO fund_riskadjret_stats(id, fund_id, end_date, sharperatio_incep, create_time, delete_tag) VALUES ('%s', '%s', str_to_date('%s','%%Y-%%m-%%d'), '%s' ,'%s', %d)" % (str_id, str_fund_id, dt_end_date, long_sharpe, ts_create_time, 0) else: #获取id str_id = data[0][0] sql = "update fund_riskadjret_stats set fund_id='%s', end_date='%s' ,sharperatio_incep ='%s' where id='%s'" % (str_fund_id, dt_end_date, long_sharpe , str_id) print(sql) try: cur.execute(sql) conn.commit() except Exception as e: print(e.message) #处理年份数据 if len(sharpe_y) >= 1: sql = "update fund_riskadjret_stats set sharperatio_1y='%s', sortinoratio_1y='%s' , sortinoratio_MAR_1y='%s' where id='%s'" % (sharpe_y[0],sortino_y[0],sortion_ratio_y[0],str_id) print(sql) cur.execute(sql) if len(sharpe_y) >= 2: sql = "update fund_riskadjret_stats set sharperatio_2y='%s' , sortinoratio_2y='%s', sortinoratio_MAR_2y='%s' where id='%s'" % (sharpe_y[1],sortino_y[1],sortion_ratio_y[1],str_id) print(sql) cur.execute(sql) if len(sharpe_y) >= 3: sql = "update fund_riskadjret_stats set sharperatio_3y='%s', sortinoratio_3y='%s' ,sortinoratio_MAR_3y='%s' where id='%s'" % (sharpe_y[2],sortino_y[2],sortion_ratio_y[2],str_id) print(sql) cur.execute(sql) if len(sharpe_y) >= 4: sql = "update fund_riskadjret_stats set sharperatio_4y='%s', sortinoratio_4y='%s', sortinoratio_MAR_4y='%s' where id='%s'" % (sharpe_y[3],sortino_y[3],sortion_ratio_y[3],str_id) print(sql) cur.execute(sql) if len(sharpe_y) >= 5: sql = "update fund_riskadjret_stats set sharperatio_5y='%s', sortinoratio_5y='%s', sortinoratio_MAR_5y='%s' where id='%s'" % (sharpe_y[4],sortino_y[4],sortion_ratio_y[4],str_id) print(sql) cur.execute(sql) #成立以来夏普，索提诺 int_range_month = month_differ(long_n_m_begin_date, long_n_m_end_date) sql = "update fund_riskadjret_stats set sharperatio_incep= '%s', sortinoratio_incep = '%s' , sortinoratio_MAR_incep = '%s' where id='%s'" % (long_sharpe, long_downside, long_sortino_ratio, str_id) print(sql) cur.execute(sql) conn.commit() #更新fund_count sql ="select id , price_date from fund_count where fund_id='%s' " % (fund_id) fetch_count = cur.execute(sql) data = cur.fetchall() if fetch_count == 0: str_id = xuehuaid() sql = "insert into fund_count(id, fund_id , strategy, data_sources, status, create_time, delete_tag) values('%s','%s', '%s', '%s', '%s', '%s', 0)" % (str_id, str_fund_id, strategy, data_source, status, ts_create_time) print(sql) cur.execute(sql) conn.commit() fund_count_date = datetime.datetime.strptime("1900-01-01", "%Y-%m-%d").date() else: #更新收益率 fund_count_date = data[0][1] if fund_count_date is None: fund_count_date = datetime.datetime.strptime("1900-01-01", "%Y-%m-%d").date() print('当前日期：',enddate) print('之前日期：',fund_count_date) dt_end_date = datetime.datetime.strptime(strenddate, "%Y%m%d").date() if dt_end_date >= fund_count_date: #更新单位净值 sql = "update fund_count set net_nav='%s' , data_sources ='%s', end_date = '%s', price_date='%s',status ='%s' where fund_id = '%s'" % (cumulative_ori_nav, data_source, enddate, enddate, status, fund_id ) print(sql) sql1 ="update fund_count a inner join fund_performance b on a.fund_id=b.fund_id set a.end_date=b.end_date, a.price_date=b.price_date, a.cumulative_nav=b.cumulative_nav,a.ret_1day=b.ret_1day, a.ret_1day_bm1=b.ret_1day_bm1, a.ret_1w=b.ret_1w, a.ret_1w_bm1=b.ret_1w_bm1, a.ret_1m=b.ret_1m, a.ret_1m_bm1=b.ret_1m_bm1, a.ret_3m=b.ret_3m, a.ret_3m_bm1=b.ret_3m_bm1, a.ret_6m=b.ret_6m, a.ret_6m_bm1=b.ret_6m_bm1, a.ret_1y=b.ret_1y, a.ret_1y_bm1=b.ret_1y_bm1, a.ret_2y=b.ret_2y, a.ret_2y_bm1=b.ret_2y_bm1, a.ret_2y_a=b.ret_2y_a, a.ret_2y_bm1_a=b.ret_2y_bm1_a, a.ret_3y=b.ret_3y, a.ret_3y_bm1=b.ret_3y_bm1, a.ret_3y_a=b.ret_3y_a, a.ret_3y_bm1_a=b.ret_3y_bm1_a,a.ret_4y=b.ret_4y, a.ret_4y_bm1=b.ret_4y_bm1, a.ret_4y_a=b.ret_4y_a, a.ret_4y_bm1_a=b.ret_4y_bm1_a,a.ret_5y=b.ret_5y, a.ret_5y_bm1=b.ret_5y_bm1, a.ret_5y_a=b.ret_5y_a, a.ret_5y_bm1_a=b.ret_5y_bm1_a, a.ret_ytd=b.ret_ytd, a.ret_ytd_bm1=b.ret_ytd_bm1, a.ret_incep=b.ret_incep, a.ret_incep_bm1=b.ret_incep_bm1, a.ret_incep_a=b.ret_incep_a, a.ret_incep_bm1_a=b.ret_incep_bm1_a where b.fund_id ='%s' and b.price_date='%s'" % (fund_id, enddate) print(sql1) try: cur.execute(sql) cur.execute(sql1) conn.commit() except Exception as e: print(e.message) #更新年化波动和最大回撤 sql ="UPDATE fund_count fc inner join fund_risk_stats t1 on fc.fund_id=t1.fund_id and fc.end_date=t1.end_date and t1.fund_id='%s' and t1.end_date='%s' SET fc.stddev_1y = t1.stddev_1y, fc.stddev_2y = t1.stddev_2y, fc.stddev_3y = t1.stddev_3y, fc.stddev_4y = t1.stddev_4y, fc.stddev_5y = t1.stddev_5y, fc.stddev_10y = t1.stddev_10y, fc.stddev_ytd = t1.stddev_ytd, fc.stddev_incep = t1.stddev_incep, fc.maxdrawdown_1y = t1.maxdrawdown_1y, fc.maxdrawdown_2y = t1.maxdrawdown_2y, fc.maxdrawdown_3y = t1.maxdrawdown_3y, fc.maxdrawdown_4y = t1.maxdrawdown_4y, fc.maxdrawdown_5y = t1.maxdrawdown_5y, fc.maxdrawdown_10y = t1.maxdrawdown_10y, fc.maxdrawdown_incep = t1.maxdrawdown_incep" % (fund_id, enddate) print(sql) #更新夏普比例 sql1 = "UPDATE fund_count fc inner join fund_riskadjret_stats t2 on fc.fund_id=t2.fund_id and fc.end_date=t2.end_date and t2.fund_id='%s' and t2.end_date='%s' SET fc.sharperatio_1y=t2.sharperatio_1y, fc.sharperatio_2y=t2.sharperatio_2y, fc.sharperatio_3y=t2.sharperatio_3y , fc.sharperatio_4y=t2.sharperatio_4y, fc.sharperatio_5y=t2.sharperatio_5y , fc.sharperatio_incep=t2.sharperatio_incep " % (fund_id, enddate) print(sql1) try: cur.execute(sql) cur.execute(sql1) conn.commit() print("1条已更新,ID:", cur.lastrowid) except Exception as e: print(e.message) cur.close() conn.close()