def getLastTradingDay(self):
oc = OracleConnector()
connection = oc.getConn()
sqls = "select * from (select trade_days from wind.asharecalendar where S_INFO_EXCHMARKET = 'SZSE' and trade_days < '%s' order by trade_days desc) where rownum <=1" % self.today
df = pd.read_sql(sql=sqls, con=connection)
tradeday = df['TRADE_DAYS'][0]
oc.closeConn()
return tradeday
def getDuration(self, start, end):
oc = OracleConnector()
connection = oc.getConn()
sqls = "select trade_days from wind.asharecalendar where S_INFO_EXCHMARKET = 'SZSE' and trade_days >= '%s' and trade_days <= '%s' order by trade_days" % (
start, end)
df = pd.read_sql(sql=sqls, con=connection)
tradedays = df['TRADE_DAYS']
oc.closeConn()
return tradedays
def tradeDayCollect(start, end):
m = OracleConnector()
connection = m.getConn()
sqls = "select trade_days from wind.asharecalendar where S_INFO_EXCHMARKET = 'SZSE' and trade_days >= '%s' and trade_days <= '%s' order by trade_days" % start, end
df = pd.read_sql(sql=sqls, con=connection)
tradedays = df['TRADE_DAYS']
print(tradedays)
return tradedays
m.closeConn()
def __init__(self):
config = GlobalConfig()
path = config.getConfig('SubConfigPath', 'StressTesting_win')
self.cp = configparser.ConfigParser()
self.cp.read(path, encoding='utf-8-sig')
# database init
self.oc = OracleConnector()
self.oracle_connection = self.oc.getConn()
self.ms = MySQLConnector()
self.mysql_connection = self.ms.getConn()
def portfolioVolatility(self):
td = TradingDay()
tradedays = td.getDuration(self.startdate, self.enddate)
df_pcntg = pd.DataFrame()
df_pcntg['TRADE_DT'] = tradedays
df_pcntg['portfolio'] = float(0)
# print(df_pcntg)
oc = OracleConnector()
oracle_connection = oc.getConn()
ms = MySQLConnector()
mysql_connection = ms.getConn()
for stock in self.stocklist:
if stock[-3:] == '.SH' or stock[-3:] == '.SZ':
sqls = """select TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.AShareEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (stock, stock, self.startdate, self.enddate)
df_stock = pd.read_sql(sql=sqls, con=oracle_connection)
# print(df_stock)
else:
sqls = """select L_DATE as TRADE_DT, round((EN_LAST_PRICE - EN_YESTERDAY_CLOSE_PRICE)/EN_YESTERDAY_CLOSE_PRICE, 4) as "%s"
from O32_THISSTOCKINFO where VC_REPORT_CODE = "%s" and L_DATE >= '%s' and L_DATE <= '%s' order by L_DATE""" \
% (stock, stock, self.startdate, self.enddate)
df_stock = pd.read_sql(sql=sqls, con=mysql_connection)
df_stock['TRADE_DT'] = df_stock['TRADE_DT'].astype(str)
df_stock[stock] = df_stock[stock] * 100
# print(df_stock)
weight = self.df_input.loc[self.df_input['VC_SCDM'] == stock,
'EN_SZZJZ'].values
# print(weight)
df_stock[stock] = df_stock[stock].astype(float) * weight / 100
# print(type(df_stock['TRADE_DT'][0]))
df_pcntg = pd.merge(df_pcntg, df_stock, how='outer', on='TRADE_DT')
df_pcntg = df_pcntg.fillna(0)
df_pcntg['portfolio'] += df_pcntg[stock]
# print(df_pcntg)
oc.closeConn()
ms.closeConn()
# print(list(df_pcntg['portfolio']))
# writer = pd.ExcelWriter("D:\PerformanceAnalysis/output.xlsx")
# print(df_pcntg)
# df_pcntg.to_excel(writer, 'Sheet2')
# writer.save()
vol = df_pcntg['portfolio'].std() * np.sqrt(20)
print("Stock Portfolio Volatility: %.4f" % vol)
return vol
def duration(self):
oc = OracleConnector()
oracle_connection = oc.getConn()
purebond_modidura_list = []
purebond_modidura_list_yield = []
for purebond in self.purebondlist:
sqls = """select b_anal_modidura_cnbd from wind.cbondanalysiscnbd
where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s'
and B_ANAL_YIELD_CNBD = (select min(B_ANAL_YIELD_CNBD) from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s')""" \
% (purebond, self.enddate, purebond, self.enddate)
sqls_yield = """select b_anal_modidura_cnbd from wind.cbondanalysiscnbd
where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s'
and B_ANAL_YIELD_CNBD = (select max(B_ANAL_YIELD_CNBD) from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s')""" \
% (purebond, self.enddate, purebond, self.enddate)
# print(sqls)
purebond_modidura_df = pd.read_sql(sql=sqls, con=oracle_connection)
purebond_modidura_df_yield = pd.read_sql(sql=sqls_yield,
con=oracle_connection)
# print(purebond_modidura_df)
purebond_modidura = purebond_modidura_df[
'B_ANAL_MODIDURA_CNBD'].values[0]
purebond_modidura_yield = purebond_modidura_df_yield[
'B_ANAL_MODIDURA_CNBD'].values[0]
# print(purebond_modidura)
purebond_modidura_list.append(purebond_modidura)
purebond_modidura_list_yield.append(purebond_modidura_yield)
oc.closeConn()
df = self.df_input
df['Modidura'] = purebond_modidura_list
df['Modidura_yield'] = purebond_modidura_list_yield
df['purebond_percentage'] = df['EN_SZ'] / df['EN_SZ'].sum()
portfolioDuration = (df['Modidura'] * df['purebond_percentage']).sum()
portfolioDuration_yield = (df['Modidura_yield'] *
df['purebond_percentage']).sum()
# print(self.df_input)
print("Portfolio Duration (pure bond): %.4f" % portfolioDuration)
print("Portfolio Yield Duration (pure bond): %.4f" %
portfolioDuration_yield)
duration_list = [
round(portfolioDuration, 4),
round(portfolioDuration_yield, 4)
]
return duration_list
def portfolioVolatility(self):
td = TradingDay()
tradedays = td.getDuration(self.startdate, self.enddate)
df_pcntg = pd.DataFrame()
df_pcntg['TRADE_DT'] = tradedays
df_pcntg['portfolio'] = float(0)
# print(df_pcntg)
oc = OracleConnector()
oracle_connection = oc.getConn()
ms = MySQLConnector()
mysql_connection = ms.getConn()
for pb in self.purebondlist:
sqls = """select TRADE_DT, b_anal_net_cnbd "%s" from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (pb, pb, self.startdate, self.enddate)
sqls_minus1day = """select TRADE_DT, b_anal_net_cnbd "%s" from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (pb, pb, self.startdate_minus1day, self.enddate_minus1day)
# print(sqls)
df_pb = pd.read_sql(sql=sqls, con=oracle_connection)
df_pb_minus1day = pd.read_sql(sql=sqls_minus1day,
con=oracle_connection)
# print(df_pb, df_pb_minus1day)
df_pb[pb] = (df_pb[pb] / df_pb_minus1day[pb] - 1) * 100
# print(df_pb)
weight = self.df_input.loc[self.df_input['VC_SCDM'] == pb,
'EN_SZZJZ'].values
# print(weight)
df_pb[pb] = df_pb[pb].astype(float) * weight / 100
# print(type(df_pb['TRADE_DT'][0]))
df_pcntg = pd.merge(df_pcntg, df_pb, how='outer', on='TRADE_DT')
# df_pcntg = df_pcntg.fillna(0)
df_pcntg['portfolio'] += df_pcntg[pb]
# print(df_pcntg)
oc.closeConn()
ms.closeConn()
# print(list(df_pcntg['portfolio']))
# writer = pd.ExcelWriter("D:\PerformanceAnalysis/output.xlsx")
# df_pcntg.to_excel(writer, 'Sheet2')
# writer.save()
vol = df_pcntg['portfolio'].std() * np.sqrt(20)
print("PB Portfolio Volatility: %.4f" % vol)
return round(vol, 4)
def portfolioVolatility(self):
td = TradingDay()
tradedays = td.getDuration(self.startdate, self.enddate)
df_pcntg = pd.DataFrame()
df_pcntg['TRADE_DT'] = tradedays
df_pcntg['portfolio'] = float(0)
# print(df_pcntg)
oc = OracleConnector()
oracle_connection = oc.getConn()
ms = MySQLConnector()
mysql_connection = ms.getConn()
for cb in self.cblist:
sqls = """select TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.CBondEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (cb, cb, self.startdate, self.enddate)
# print(sqls)
df_cb = pd.read_sql(sql=sqls, con=oracle_connection)
weight = self.df_input.loc[self.df_input['VC_SCDM'] == cb,
'EN_SZZJZ'].values
# print(weight)
df_cb[cb] = df_cb[cb].astype(float) * weight / 100
# print(type(df_cb['TRADE_DT'][0]))
df_pcntg = pd.merge(df_pcntg, df_cb, how='outer', on='TRADE_DT')
df_pcntg = df_pcntg.fillna(0)
df_pcntg['portfolio'] += df_pcntg[cb]
# print(df_pcntg)
oc.closeConn()
ms.closeConn()
# print(df_pcntg)
# print(list(df_pcntg['portfolio']))
# writer = pd.ExcelWriter("D:\PerformanceAnalysis/output.xlsx")
# df_pcntg.to_excel(writer, 'Sheet2')
# writer.save()
vol = df_pcntg['portfolio'].std() * np.sqrt(20)
print("CB Portfolio Volatility: %.4f" % vol)
return round(vol, 4)
def Category_future(self, df):
if df.empty:
return None
ms = MySQLConnector()
mysql_connection = ms.getConn()
oc = OracleConnector()
oracle_connection = oc.getConn()
futurelist = df['VC_SCDM'].values
enddate = df['D_YWRQ'].iloc[0]
enddate = enddate.strftime("%Y%m%d")
sqls_td_days_Ashare = """select TRADE_DAYS "TRADE_DT" from
(select distinct * from wind.CFuturesCalendar where s_info_exchmarket = 'CFFEX' and TRADE_DAYS <= '%s'order by TRADE_DAYS desc) where rownum <= 120"""\
% (enddate)
df_120td_future = pd.read_sql(sql=sqls_td_days_Ashare, con=oracle_connection)
df_120td_future['portfolio'] = float(0)
startdate = df_120td_future['TRADE_DT'].values[-1]
print("start date: %s | end date: %s" % (startdate, enddate))
wind_code_list = []
main_contract_code_list = []
multiplier_list = []
for f in futurelist:
# get wind code and main contract
sqls_code = """select S_INFO_WINDCODE "windcode", FS_INFO_SCCODE||substr(S_INFO_WINDCODE, -4) "maincontract" from wind.CFuturesDescription where S_INFO_CODE = '%s'""" % f
df_code = pd.read_sql(sql=sqls_code, con=oracle_connection)
wind_code = df_code.iat[0, 0]
main_contract_code = df_code.iat[0, 1]
# print(wind_code, main_contract_code)
wind_code_list.append(wind_code)
main_contract_code_list.append(main_contract_code)
# get multiplier
sqls_multiplier = """select case when s_info_cemultiplier is not null then s_info_cemultiplier else s_info_punit end from wind.CFuturescontpro where S_INFO_WINDCODE = '%s'""" % wind_code
df_multiplier = pd.read_sql(sql=sqls_multiplier, con=oracle_connection)
multiplier = df_multiplier.iat[0 ,0]
multiplier_list.append(multiplier)
df['WindCode'] = wind_code_list
df['MainContract'] = main_contract_code_list
df['Multiplier'] = multiplier_list
# print(df)
for index, row in df.iterrows():
# get market data of main contract
main_contract = row['MainContract']
if main_contract[-4:] == '.CFE':
if main_contract[0] == 'T':
sqls_future = """select TRADE_DT, S_DQ_SETTLE "%s" from wind.CBondFuturesEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (main_contract, main_contract, startdate, enddate)
else:
sqls_future = """select TRADE_DT, S_DQ_SETTLE "%s" from wind.CIndexFuturesEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (main_contract, main_contract, startdate, enddate)
else:
sqls_future = """select TRADE_DT, S_DQ_SETTLE "%s" from wind.CCommodityFuturesEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (main_contract, main_contract, startdate, enddate)
# print(sqls_future)
df_future = pd.read_sql(sql=sqls_future, con=oracle_connection)
quantity = row['L_SL']
marketvalue = row['EN_SZ']
multiplier = row['Multiplier']
if float(marketvalue) > 0:
longorshort = 1
elif float(marketvalue) < 0:
longorshort = -1
df_future[main_contract] = df_future[main_contract] * multiplier * quantity * longorshort
df_120td_future = pd.merge(df_120td_future, df_future, how='outer', on='TRADE_DT')
df_120td_future = df_120td_future.fillna(0)
df_120td_future['portfolio'] += df_120td_future[main_contract]
print(df)
print(df_120td_future)
df_120td_future['profit'] = df_120td_future['portfolio'].shift(1) - df_120td_future['portfolio']
# print(df_120td_future)
# writer = pd.ExcelWriter('D:\\PerformanceAnalysis\\futurevar.xlsx')
# df_120td_future.to_excel(writer, sheet_name='Sheet1', index=False)
profit = df_120td_future.loc[1:, 'profit']
# print(profit)
var_95 = np.percentile(profit, 5)
var_98 = np.percentile(profit, 2)
var_99 = np.percentile(profit, 1)
print("95 var: %.4f" % var_95)
print("98 var: %.4f" % var_98)
print("99 var: %.4f" % var_99)
df_temp = pd.DataFrame()
df_temp['date'] = df_120td_future.loc[1:, 'TRADE_DT']
df_temp['profit'] = df_120td_future.loc[1:, 'profit']
print(df_temp)
df_temp.plot(kind='line', grid='on', figsize=(20, 10), title="future portfolio")
plt.show()
return "finished"
def indexfuture(type):
df_index = pd.read_excel(
"E:\\风控合规\\20190319_2019上半年压力测试\\indexfuture.xlsx", sheetname=type)
beta_list = []
board_list = []
loss_mild_list = []
loss_moderate_list = []
loss_severe_list = []
for index, row in df_index.iterrows():
stockid = row['Code']
weight = row['Weight'] / 100
beta_temp = df_beta.loc[df_beta['Code'] == stockid, 'Beta']
if beta_temp.empty:
beta = 1
else:
beta = beta_temp.values[-1]
beta_list.append(beta)
sqls = """select s_info_listboard from wind.AShareDescription where S_INFO_WINDCODE = '%s'""" % stockid
oc = OracleConnector()
oracle_connection = oc.getConn()
df_board = pd.read_sql(sql=sqls, con=oracle_connection)
board = df_board.iat[0, 0]
board_list.append(board)
if board == '434004000':
loss_mild = beta * weight * -0.1499
loss_moderate = beta * weight * -0.218
loss_severe = beta * weight * -0.269
elif board == '434003000':
loss_mild = beta * weight * -0.1697
loss_moderate = beta * weight * -0.2453
loss_severe = beta * weight * -0.3013
elif board == '434001000':
loss_mild = beta * weight * -0.1693
loss_moderate = beta * weight * -0.2441
loss_severe = beta * weight * -0.3001
loss_mild_list.append(loss_mild)
loss_moderate_list.append(loss_moderate)
loss_severe_list.append(loss_severe)
df_index['beta'] = beta_list
df_index['board'] = board_list
df_index['loss_mild'] = loss_mild_list
df_index['loss_moderate'] = loss_moderate_list
df_index['loss_severe'] = loss_severe_list
sum_loss_mild = df_index['loss_mild'].sum()
sum_loss_moderate = df_index['loss_moderate'].sum()
sum_loss_severe = df_index['loss_severe'].sum()
#
# loss_by_board_mild = 0
# loss_by_board_moderate = 0
# loss_by_board_severe = 0
# for b in board_list:
# df_index_byboard = df_index.loc[df_index['board'] == b]
# # df_index_byboard['newweight'] = df_index_byboard['Weight'] / df_index_byboard['Weight'].sum()
# beta_by_index = np.average(df_index_byboard['beta'], weights=df_index_byboard['Weight'])
# value_by_index = df_index_byboard['Weight'].sum() / 100
# # print(beta_by_index, value_by_index)
# if b == '434004000':
# loss_mild = beta_by_index * value_by_index * -0.1499
# loss_moderate = beta_by_index * value_by_index * -0.218
# loss_severe = beta_by_index * value_by_index * -0.269
# elif b == '434003000':
# loss_mild = beta_by_index * value_by_index * -0.1697
# loss_moderate = beta_by_index * value_by_index * -0.2453
# loss_severe = beta_by_index * value_by_index * -0.3013
# elif b == '434001000':
# loss_mild = beta_by_index * value_by_index * -0.1693
# loss_moderate = beta_by_index * value_by_index * -0.2441
# loss_severe = beta_by_index * value_by_index * -0.3001
# loss_by_board_mild += loss_mild
# loss_by_board_moderate += loss_moderate
# loss_by_board_severe += loss_severe
# if type == 'IF':
# mild_pctg = loss_by_board_mild / 3669.37
# moderate_pctg = loss_by_board_moderate / 3669.37
# severe_pctg = loss_by_board_severe / 3669.37
# elif type == 'IC':
# mild_pctg = loss_by_board_mild / 5025.29
# moderate_pctg = loss_by_board_moderate / 5025.29
# severe_pctg = loss_by_board_severe / 5025.29
# elif type == 'IH':
# mild_pctg = loss_by_board_mild / 2743.97
# moderate_pctg = loss_by_board_moderate / 2743.97
# severe_pctg = loss_by_board_severe / 2743.97
# print("%s - 轻： %.4f | 中： %.4f | 重： %.4f" % (type, sum_loss_mild, sum_loss_moderate, sum_loss_severe))
return [sum_loss_mild, sum_loss_moderate, sum_loss_severe]
def CreditCategory_PB(self, df):
if df.empty:
return None
oc = OracleConnector()
oracle_connection = oc.getConn()
df.loc[df['L_SCLB'] == 1, 'VC_SCDM'] += '.SH'
df.loc[df['L_SCLB'] == 2, 'VC_SCDM'] += '.SZ'
df.loc[df['L_SCLB'] == 3, 'VC_SCDM'] += '.IB'
bondlist = df['VC_SCDM'].values
enddate = df['D_YWRQ'].iloc[0]
enddate = enddate.strftime("%Y%m%d")
# print(bondlist)
bond_modidura_list = []
bond_modidura_list_yield = []
ratingcollector = []
for b in bondlist:
# ------ get rating -------#
sqls = """select b_info_creditrating from (select * from wind.CBondRating where S_INFO_WINDCODE = '%s' order by ANN_DT DESC) where rownum =1""" % b
# print(sqls)
dfx = pd.read_sql(sql=sqls, con=oracle_connection)
# print(dfx)
if dfx.empty:
sqls1 = """select b_info_creditrating from (select * from wind.Cbondissuerrating
where s_info_compname = (select b_info_issuer from wind.cbonddescription
where S_INFO_WINDCODE = '%s')order by ANN_DT desc) where rownum=1""" % b
# print(sqls1)
dfx1 = pd.read_sql(sql=sqls1, con=oracle_connection)
# print(dfx1)
if dfx1.empty:
ratingunit = 'NoRating'
else:
ratingunit = dfx1['B_INFO_CREDITRATING'].values[0]
else:
ratingunit = dfx['B_INFO_CREDITRATING'].values[0]
ratingcollector.append(ratingunit)
# ------ get duration -------#
sqlsd = """select b_anal_modidura_cnbd from wind.cbondanalysiscnbd
where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s'
and B_ANAL_YIELD_CNBD = (select min(B_ANAL_YIELD_CNBD) from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s')""" \
% (b, enddate, b, enddate)
sqlsd_yield = """select b_anal_modidura_cnbd from wind.cbondanalysiscnbd
where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s'
and B_ANAL_YIELD_CNBD = (select max(B_ANAL_YIELD_CNBD) from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s' and TRADE_DT = '%s')""" \
% (b, enddate, b, enddate)
bond_modidura_df = pd.read_sql(sql=sqlsd, con=oracle_connection)
bond_modidura_df_yield = pd.read_sql(sql=sqlsd_yield,
con=oracle_connection)
# print(purebond_modidura_df)
bond_modidura = bond_modidura_df['B_ANAL_MODIDURA_CNBD'].values[0]
bond_modidura_yield = bond_modidura_df_yield[
'B_ANAL_MODIDURA_CNBD'].values[0]
# print(purebond_modidura)
bond_modidura_list.append(bond_modidura)
bond_modidura_list_yield.append(bond_modidura_yield)
oc.closeConn()
# ------ collect rating -------#
# print(ratingcollector)
df['Rating'] = ratingcollector
# CategoryResult = df.groupby(by=['Rating'])['EN_SZ'].sum()
CategoryResult = df.groupby('Rating')['EN_SZ'].sum()
# print(CategoryResult)
ratinglist = list(CategoryResult.index)
# print(ratinglist)
valuesumlist = list(CategoryResult)
# print(valuesumlist)
df_credit_sum_duration = pd.DataFrame()
df_credit_sum_duration['Rate'] = ratinglist
df_credit_sum_duration['ValueSum'] = valuesumlist
# print(df_credit_sum_duration)
# ------ duration by rating -------#
df['Duration_E'] = bond_modidura_list
df['Duration_M'] = bond_modidura_list_yield
ratingdurationcollector = []
for r in ratinglist:
# print(r)
df_temp = df.loc[df['Rating'] == r]
# print(df_temp)
df_temp['purebond_percentage'] = df_temp['EN_SZ'] / df_temp[
'EN_SZ'].sum()
CreditCategoryDuration = (df_temp['Duration_E'] *
df_temp['purebond_percentage']).sum()
# print(CreditCategoryDuration)
ratingdurationcollector.append(CreditCategoryDuration)
df_credit_sum_duration['RatingDuration'] = ratingdurationcollector
# print(df_credit_sum_duration)
return df_credit_sum_duration
def Category_CB(self, df):
if df.empty:
return None
ms = MySQLConnector()
mysql_connection = ms.getConn()
oc = OracleConnector()
oracle_connection = oc.getConn()
df.loc[df['L_SCLB'] == 1, 'VC_SCDM'] += '.SH'
df.loc[df['L_SCLB'] == 2, 'VC_SCDM'] += '.SZ'
df.loc[df['L_SCLB'] == 3, 'VC_SCDM'] += '.IB'
bondlist = df['VC_SCDM'].values
enddate = df['D_YWRQ'].iloc[0]
enddate = enddate.strftime("%Y%m%d")
sqls_td_days_Ashare = """select TRADE_DAYS "TRADE_DT" from
(select distinct * from wind.AShareCalendar where S_INFO_EXCHMARKET = 'SZSE' and TRADE_DAYS <= '%s'order by TRADE_DAYS desc) where rownum <= 120"""\
% (enddate)
df_120td_Ashare = pd.read_sql(sql=sqls_td_days_Ashare,
con=oracle_connection)
startdate = df_120td_Ashare['TRADE_DT'].values[-1]
cb_index = '000832.CSI'
sqls_index = """select distinct TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.AIndexEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (cb_index, cb_index, startdate, enddate)
df_index = pd.read_sql(sql=sqls_index, con=oracle_connection)
beta_list = []
for b in bondlist:
sqls = """select distinct TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.CBondEODPrices where S_INFO_WINDCODE = '%s' and TRADE_DT >= '%s' and TRADE_DT <= '%s' order by TRADE_DT""" \
% (b, b, startdate, enddate)
df_cbond = pd.read_sql(sql=sqls, con=oracle_connection)
sqls_suspend = """select distinct * from wind.CBondEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s' and TRADE_DT <= '%s' and S_DQ_TRADESTATUS = '停牌'""" \
% (b, startdate, enddate)
df_cbond_suspend = pd.read_sql(sql=sqls_suspend,
con=oracle_connection)
if len(df_cbond) != 120:
beta = 1
elif not df_cbond_suspend.empty:
beta = 1
else:
beta = df_cbond[b].cov(
df_index[cb_index]) / df_index[cb_index].var()
beta_list.append(beta)
df['Beta'] = beta_list
df['Beta_by_value'] = df['Beta'] * df['EN_SZ']
print(df)
df_result = pd.DataFrame()
Scenario_list = ["ConvertibleBond_loss"]
ValueSum_list = []
BetaSum_list = []
value_by_index = df['EN_SZ'].sum()
beta_by_index = np.average(df['Beta'], weights=df['EN_SZ'])
ValueSum_list.append(value_by_index)
BetaSum_list.append(beta_by_index)
df_result['Scenario'] = Scenario_list
df_result['value_by_index'] = ValueSum_list
df_result['beta_by_index'] = BetaSum_list
return df_result
class Stock_BetaCal(object):
def __init__(self):
config = GlobalConfig()
path = config.getConfig('SubConfigPath', 'StressTesting_win')
self.cp = configparser.ConfigParser()
self.cp.read(path, encoding='utf-8-sig')
# database init
self.oc = OracleConnector()
self.oracle_connection = self.oc.getConn()
self.ms = MySQLConnector()
self.mysql_connection = self.ms.getConn()
def getConfig(self, section, key):
return self.cp.get(section, key)
def Beta(self, enddate, index, df_list):
if not index:
return None
sqls_td_days_Ashare = """select TRADE_DAYS "TRADE_DT" from
(select distinct * from wind.AShareCalendar where S_INFO_EXCHMARKET = 'SZSE' and TRADE_DAYS <= '%s'order by TRADE_DAYS desc) where rownum <= 120"""\
% (enddate)
sqls_td_days_Hshare = """select TRADE_DAYS "TRADE_DT" from
(select distinct * from wind.AShareCalendar where S_INFO_EXCHMARKET = 'SZN' and TRADE_DAYS <= '%s'order by TRADE_DAYS desc) where rownum <= 120""" \
% (enddate)
df_120td_Ashare = pd.read_sql(sql=sqls_td_days_Ashare,
con=self.oracle_connection)
df_120td_Hshare = pd.read_sql(sql=sqls_td_days_Hshare,
con=self.oracle_connection)
# print(df_120td_Ashare)
# print("data df start")
# print(df_120td_Ashare, df_120td_Hshare)
# print("data df end")
startdate = df_120td_Ashare['TRADE_DT'].values[-1]
startdate_hk = df_120td_Hshare['TRADE_DT'].values[-1]
# print(startdate, startdate_hk)
df_result = pd.DataFrame()
df_result['Index'] = index
value_by_index_list = []
beta_by_index_list = []
for i, df in zip(index, df_list):
# get stock list
df.loc[df['L_SCLB'] == 1, 'VC_SCDM'] += '.SH'
df.loc[df['L_SCLB'] == 2, 'VC_SCDM'] += '.SZ'
stocklist = df['VC_SCDM'].values
# get index data
index = self.getConfig('StockIndex', i)
if i == "SZ_HK_connect_prefix":
sqls = """select distinct L_DATE as TRADE_DT, round((EN_LAST_PRICE - EN_YESTERDAY_CLOSE_PRICE)/EN_YESTERDAY_CLOSE_PRICE, 4) as "%s"
from O32_THISSTOCKINFO where VC_INTER_CODE = "%s" and L_DATE >= '%s' and L_DATE <= '%s' order by L_DATE""" \
% (index, index, startdate, enddate)
df_index = pd.read_sql(sql=sqls, con=self.mysql_connection)
else:
sqls = """select distinct TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.AIndexEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (index, index, startdate, enddate)
df_index = pd.read_sql(sql=sqls, con=self.oracle_connection)
# print(df_index)
beta_list = []
for stock in stocklist:
if stock[-3:] == '.SH' or stock[-3:] == '.SZ':
sqls = """select distinct TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.AShareEODPrices where S_INFO_WINDCODE = '%s' and TRADE_DT >= '%s' and TRADE_DT <= '%s' order by TRADE_DT""" \
% (stock, stock, startdate, enddate)
df_stock = pd.read_sql(sql=sqls,
con=self.oracle_connection)
sqls_suspend = """select distinct * from wind.AShareEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s' and TRADE_DT <= '%s' and S_DQ_TRADESTATUS = '停牌'""" \
% (stock, startdate, enddate)
df_stock_suspend = pd.read_sql(sql=sqls_suspend,
con=self.oracle_connection)
else:
sqls = """select distinct L_DATE as TRADE_DT, round((EN_LAST_PRICE - EN_YESTERDAY_CLOSE_PRICE)/EN_YESTERDAY_CLOSE_PRICE, 4) as "%s"
from O32_THISSTOCKINFO where VC_REPORT_CODE = "%s" and L_DATE >= '%s' and L_DATE <= '%s' order by L_DATE""" \
% (stock, stock, startdate, enddate)
df_stock = pd.read_sql(sql=sqls, con=self.mysql_connection)
df_stock['TRADE_DT'] = df_stock['TRADE_DT'].astype(str)
sqls_suspend = """select distinct * from O32_THISSTOCKINFO where L_DATE >= '%s' and L_DATE <= '%s' and VC_REPORT_CODE = "%s" and L_MARKET_DEAL_AMOUNT = 0""" \
% (startdate, enddate, stock)
df_stock_suspend = pd.read_sql(sql=sqls_suspend,
con=self.mysql_connection)
# print(df_stock)
# print(df_stock_suspend)
if len(df_stock) != 120:
beta = 1
elif not df_stock_suspend.empty:
beta = 1
else:
beta = df_stock[stock].cov(
df_index[index]) / df_index[index].var()
# print("Beta of %s is %s" % (stock, beta))
beta_list.append(beta)
df['Beta'] = beta_list
df['Beta_by_value'] = df['Beta'] * df['EN_SZ']
# print(df)
value_by_index = df['EN_SZ'].sum()
value_by_index_list.append(value_by_index)
beta_by_index = np.average(df['Beta'], weights=df['EN_SZ'])
beta_by_index_list.append(beta_by_index)
df_result['value_by_index'] = value_by_index_list
df_result['beta_by_index'] = beta_by_index_list
df_result['Index'] = df_result['Index'].apply(lambda x: x[:-7])
self.oc.closeConn()
self.ms.closeConn()
return df_result
def getBondInfo(self, bondcode):
oc = OracleConnector()