#!/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()