def cal_risk_all(p, d, b):
df_nav = db_query(p, d, d, "nav")
df_nav_increase = db_query(p, d, d, "nav_increase")
# print(df_nav)
# print(df_nav_increase)
df_temp = pd.merge(df_nav, df_nav_increase, how='outer')
# print(df_temp)
w.start()
bmk_wind_data1 = w.wsd(b, "close", d, d, "")
w.close()
# print(bmk_wind_data1.Data[0])
df_temp['Benchmark'] = bmk_wind_data1.Data[0]
# print(df_temp)
# -----> | date | nav | nav_increase | benchmark | product
df_temp['Product_ID'] = p
# print(df_temp)
if p in ['GZFB0001', 'GZFB0002']:
startdate_nav = '2017-01-18'
elif p == 'GZFB0003':
startdate_nav = '2017-05-26'
elif p == 'GZFB0004':
startdate_nav = '2017-06-29'
# ave
df_temp["Average Increase"] = average_increase(p, startdate_nav, d)
# probs up
df_temp["Probability of UP"] = probs_up(p, startdate_nav, d)
# an
df_temp["Annualized Return"] = annualized_return(p, startdate_nav, d)
# sharpe
df_temp["Sharpe"] = Cal_Sharpe(p, startdate_nav, d)
# vol
df_temp["Volatility"] = volatility(p, startdate_nav, d)
# beta
df_temp["Beta"] = cal_beta(p, startdate_nav, d)
# info rate
df_temp["Info Rate"] = cal_info_rate(p, startdate_nav, d)
# mdd
df_temp["Max Drawdown Rate"] = Cal_MaxDrawdown(p, startdate_nav, d)[0]
df_temp["Max Drawdown Start Date"] = Cal_MaxDrawdown(p, startdate_nav,
d)[1]
df_temp["Max Drawdown End Date"] = Cal_MaxDrawdown(p, startdate_nav, d)[2]
# print(df_temp)
# process NaN inf nan
df_temp = df_temp.replace(float('inf'), '') # inf
# df_temp = df_temp.replace(float('nan'), 'null')
df_temp = df_temp.replace('nan', '') # nan
df_temp = df_temp.replace('NaN', '') # NaN (str)
df_temp = df_temp.fillna('') # NaN (float)
# print(df_temp)
db_insert(df_temp, 'daily_risk_cal')
def cal_info_rate(product_id, startdate, enddate):
# get nav increase data by day
df = db_query(product_id, startdate, enddate, "nav_increase")
df['Date'] = df['Date'].astype(str)
df['NAV_Increase'] = df['NAV_Increase'].astype(float)
w.start()
bmk_wind_data1 = w.wsd("M1001654", "close", startdate, enddate,
"Fill=Previous")
w.close()
date_raw = bmk_wind_data1.Times
date_count = bmk_wind_data1.Times.__len__()
index_dates = []
for i in range(date_count):
date_format = str(date_raw[i])[:10]
index_dates.append(date_format)
df_bm = pd.DataFrame()
df_bm['Date'] = index_dates
df_bm['Benchmark'] = bmk_wind_data1.Data[0]
df_bm['Benchmark'] = df_bm['Benchmark'].astype(float)
df_temp = pd.merge(df_bm, df, how='left', on='Date')
# df_temp = df_temp.fillna(0)
df_temp['diff'] = df_temp['NAV_Increase'] - (df_temp['Benchmark'] / 365)
annual_mean = df_temp['diff'].mean() * 250
annual_std = df_temp['diff'].std() * np.sqrt(250)
info = annual_mean / annual_std
return "%.4f" % info
def cal_beta(product_id, startdate, enddate):
# get nav increase data by day
df = db_query(product_id, startdate, enddate, "nav_increase")
df['Date'] = df['Date'].astype(str)
df['NAV_Increase'] = df['NAV_Increase'].astype(float)
w.start()
bmk_wind_data1 = w.wsd("M1001654", "close", startdate, enddate,
"Fill=Previous")
w.close()
date_raw = bmk_wind_data1.Times
date_count = bmk_wind_data1.Times.__len__()
index_dates = []
for i in range(date_count):
date_format = str(date_raw[i])[:10]
index_dates.append(date_format)
df_bm = pd.DataFrame()
df_bm['Date'] = index_dates
df_bm['Benchmark'] = bmk_wind_data1.Data[0]
df_bm['Benchmark'] = df_bm['Benchmark'].astype(float)
df_temp = pd.merge(df_bm, df, how='left', on='Date')
# df_temp = df_temp.fillna(0)
bt = df_temp['NAV_Increase'].cov(
df_temp['Benchmark']) / df_temp['Benchmark'].var()
return "%.4f" % bt
def volatility(product_id, startdate, enddate):
# get nav increase data by day
# df = Query_NAV(product_id, startdate, enddate)
df = db_query(product_id, startdate, enddate, "nav_increase")
# print(df.dtypes) # DEBUG
# if startdate == enddate:
# return 'NaN'
df['NAV_Increase'] = df['NAV_Increase'].astype(float)
vol = df['NAV_Increase'].std() * np.sqrt(250)
return "%.4f" % vol
def annualized_return(product_id, startdate, enddate):
# get nav increase data by day
# df = Query_NAV(product_id, startdate, enddate)
df = db_query(product_id, startdate, enddate, "nav")
# print(df.dtypes) # DEBUG
df['NAV'] = df['NAV'].astype(float)
df['Date'] = df['Date'].astype(date)
mg = pd.period_range(df['Date'].iloc[0], df['Date'].iloc[-1], freq='D')
an = pow(df.ix[len(df.index) - 1, 'NAV'] / df.ix[0, 'NAV'],
250 / len(mg)) - 1
return "%.4f" % an
def probs_up(product_id, startdate, enddate):
# get nav increase data by day
# df = Query_NAV(product_id, startdate, enddate)
df = db_query(product_id, startdate, enddate, "nav_increase")
# print(df.dtypes) # DEBUG
if startdate == enddate:
return 'NaN'
df['NAV_Increase'] = df['NAV_Increase'].astype(float)
df.ix[df['NAV_Increase'] > 0, 'NAV_Increase'] = 1
df.ix[df['NAV_Increase'] <= 0, 'NAV_Increase'] = 0
count = df['NAV_Increase'].value_counts()
p_up = count.loc[1] / len(df.index)
return "%.4f" % p_up
def Cal_MaxDrawdown(product_id, startdate, enddate):
# get nav increase data by day
# df = Query_NAV(product_id, startdate, enddate)
df = db_query(product_id, startdate, enddate, "nav")
# print(df.dtypes) # DEBUG
df['NAV'] = df['NAV'].astype(float)
df['Date'] = df['Date'].astype(date)
df['max2here'] = pd.expanding_max(df['NAV'])
df['dd2here'] = df['NAV'] / df['max2here'] - 1
# print(df)
temp = df.sort_values(by='dd2here').iloc[0][['Date', 'dd2here']]
max_dd = temp['dd2here']
end_date = temp['Date']
df2 = df[df['Date'] <= end_date]
start_date = df2.sort_values(by='NAV', ascending=False).iloc[0]['Date']
# print("MaxDrawdown : %f, Start from : %s, End at : %s" % (max_dd, start_date, end_date))
return max_dd, start_date, end_date
def Cal_Sharpe(product_id, startdate, enddate, product_type=None):
# get nav increase data by day
# df = Query_MySQL(product_id, startdate, enddate)
df = db_query(product_id, startdate, enddate, "nav_increase")
# print(df.dtypes) # DEBUG
# format data, column 1 = Date, Column 2 = NAV_Increase
df['NAV_Increase'] = df['NAV_Increase'].astype(float)
df['Date'] = df['Date'].astype(date)
# for plot use
# df_index = df.set_index('Date')
# df_index['NAV_Increase'].plot()
# plt.show() # display plot
# calculate mean and std
nav_mean_daily = df['NAV_Increase'].mean()
nav_std_daily = df['NAV_Increase'].std()
# use different benchmark rate for different types of products
if product_type == "EQ":
benchmark_stock = "M1001654"
elif product_type == "FI":
benchmark_stock = "M1001648"
elif product_type is None:
# print("No designated product type, will set as Equity product....")
benchmark_stock = "M1001654"
else:
# print("Unsupported product type, will set as Equity product....")
benchmark_stock = "M1001654"
bmr_daily = Cal_Benchmark_Rate(benchmark_stock, startdate, enddate)
if product_type == "ZB":
bmr_daily = 0
sharpe_value = sharpe_formula(nav_mean_daily, nav_std_daily, bmr_daily)
# print(sharpe_value) # DEBUG
return sharpe_value
# startdate_nav_increase = '2017-01-19' # startdate_sharpe = '2017-01-23' # startdate_nav = '2017-01-23' # startdate_nav_increase = '2017-01-23' # startdate_sharpe = '2017-01-23' enddate = '2017-04-05' # benchmark code bmc = "CGB10Y.WI" # product product = 'GZFB0002' #--------------- manage risk table ----------------# # ----> | date | nav | nav_increase | df_nav = db_query(product, startdate_nav, enddate, "nav") df_nav_increase = db_query(product, startdate_nav, enddate, "nav_increase") # print(df_nav) # print(df_nav_increase) df_temp = pd.merge(df_nav, df_nav_increase, how='outer') # print(df_temp) # -----> | date | nav | nav_increase | benchmark | w.start() bmk_wind_data1 = w.wsd(bmc, "close", startdate_nav, enddate, "") w.close() # print(bmk_wind_data1.Data[0]) df_temp['Benchmark'] = bmk_wind_data1.Data[0] # print(df_temp) # -----> | date | nav | nav_increase | benchmark | product df_temp['Product_ID'] = product