def Data_Insert(self):
stock_stress_result = self.Stock_Stress_Data()
bond_stress_result_list = self.Bond_Stress_Data()
# print(stock_stress_result)
# print(bond_stress_result_list)
if stock_stress_result is None and bond_stress_result_list is None:
print("No Stress Testing Data....")
return
bond_stress_result_list.append(stock_stress_result)
ms = MySQLConnector()
msc = ms.getConn()
cursor = msc.cursor()
for bsr in bond_stress_result_list:
if bsr is not None:
DGSSC_daily_data = (self.enddate, self.product, bsr[0], bsr[1],
bsr[2], bsr[3])
print(DGSSC_daily_data)
# compile sql statement
insert_statement = """insert into Stress_Testing_Data values('%s','%s','%s',%f,%f,%f)""" % DGSSC_daily_data
insert_result = cursor.execute(insert_statement)
msc.commit()
if insert_result:
print("[Success] Insert completed!")
else:
print("[Error] Insert failed!")
cursor.close()
ms.closeConn()
def getdf(self):
prefix_alias_list = self.getConfig('StockCategory', 'prefix_list')
SZ_SME_prefix = self.getConfig('StockCategory', 'SZ_SME_prefix')
prefix_alias_list = prefix_alias_list.split(", ")
df_collector = []
df_collector_index = []
for p in prefix_alias_list:
prefix = self.getConfig('StockCategory', p)
if p == 'SZ_mainboard_prefix':
sqls = self.prefix2sql_filterout(prefix, SZ_SME_prefix)
else:
sqls = self.prefix2sql_general(prefix)
ms = MySQLConnector()
connection = ms.getConn()
df = pd.read_sql(sql=sqls, con=connection)
# print(df)
if not df.empty:
df_collector.append(df)
df_collector_index.append(p)
if df_collector_index:
return self.EndDate, df_collector_index, df_collector
else:
return self.EndDate, None, None
def BPI_data_lookup(self):
# get BPI data
bpih = BPIH()
sqls = bpih.setBPIH()
ms = MySQLConnector()
connection = ms.getConn()
df = pd.read_sql(sql=sqls, con=connection)
ms.closeConn()
collector = []
# nav data
nav_monitor_list = self.getConfig('NAV_threshold', 'nav_monitor_list')
nav_monitor_list = nav_monitor_list.split(", ")
for n in nav_monitor_list:
nav_threshold = self.getConfig('NAV_threshold', n)
nav_threshold = nav_threshold.split(", ")
nav_warning = nav_threshold[1]
nav_stoploss = nav_threshold[0]
distance_to_warning = df.loc[df['Product_ID'] == n, 'Unit_NAV'] - nav_warning
distance_to_stoploss = df.loc[df['Product_ID'] == n, 'Unit_NAV'] - nav_stoploss
nav_monitor_result = '[Normal] Unit NAV monitoring of %s is ok' % n
if distance_to_warning <= 0:
nav_monitor_result = '[Warning]Unit NAV monitoring of %s is Touching Warning Line' % n
if distance_to_stoploss <= 0:
nav_monitor_result = '[Warning]Unit NAV monitoring of %s is Touching Stop-Loss Line' % n
collector.append(nav_monitor_result)
def Bond_Stress_Data(self):
bf = BondFilter(self.product, self.enddate)
sqls_cb = bf.get_CBond_sqls()
sqls_pb = bf.get_PBond_sqls()
ms = MySQLConnector()
mysql_connection = ms.getConn()
df_cb = pd.read_sql(sql=sqls_cb, con=mysql_connection)
df_pb = pd.read_sql(sql=sqls_pb, con=mysql_connection)
bcs = Bond_CategorySum()
cb = bcs.Category_CB(df_cb)
pb = bcs.CreditCategory_PB(df_pb)
bsc = Bond_StressCalculate()
pbb = bsc.PureBondBreak(pb)
pbl = bsc.PureBondLoss(pb)
cbl = bsc.CBondLoss(cb)
result_list = []
result_list.append(pbb)
result_list.append(pbl)
result_list.append(cbl)
# print(pbb)
# print(pbl)
# print(cbl)
return result_list
def API_data_lookup(self):
# get API data
apih = APIH()
sqls = apih.setAPIH()
ms = MySQLConnector()
connection = ms.getConn()
df = pd.read_sql(sql=sqls, con=connection)
ms.closeConn()
def __init__(self):
config = GlobalConfig()
self.table = config.getConfig('mysql_tables', 'product')
self.sqlStatement = "select * from %s" % self.table
# print(self.sqlStatement)
self.df = pd.DataFrame()
self.mysql = MySQLConnector()
self.conn = self.mysql.getConn()
def __init__(self):
self.bpis = BPIS()
datatype_tmp = self.bpis.getConfig('BPI_table', 'columns')
self.datatype = datatype_tmp.split(", ")
# print(self.datatype)
self.BIPS_daily_data = []
self.ms = MySQLConnector()
self.msc = self.ms.getConn()
self.cursor = self.msc.cursor()
def insertArray(self):
# Compose Array
dgapi = DGAPI(self.productID, self.EndDate)
ar_list = dgapi.annualized_return()
lg = dgapi.leverage()
vl = dgapi.volatility()
md_list = dgapi.max_drawdown()
sp = dgapi.sharpe()
svol = dgapi.stockPortfolioVolatility()
cbvol = dgapi.convertablebondVolatility()
pbd = dgapi.purebondPortfolioDuration()
if pbd[0] == 0 or pbd[0] is None or pbd[1] == 0 or pbd[1] is None:
dv01 = 0
else:
dv01 = float(pbd[0]) * float(lg)
# dv01 = "%.4f" % dv01
# AIPS_daily_data = ["'%s','%s',%d,%d,%.4f,%.4f,%.4f,%.4f,'%s',%.4f,%.4f,%.4f,%.4f,%.4f,%.4f,%.4f"
AIPS_daily_data = (
self.EndDate, # occur date
self.productID, # product id
ar_list[0], # natural days
ar_list[1], # trading days
ar_list[2], # annualized return
lg, # leverage
vl, # volatility
md_list[0], # max draw down
md_list[1], # max draw down period
sp, # sharpe
svol, # stock portfolio volatility
cbvol, # convertable bond portfolio volatility
pbd[2], # pure bond volatility
pbd[0], # pure bond duration (exercise)
pbd[1], # pure bond duration (mature)
round(dv01, 4)) # dv01
print(AIPS_daily_data)
# print(type(AIPS_daily_data))
# format
insert_values = AIPS_daily_data
# start mysql connection
ms = MySQLConnector()
msc = ms.getConn()
cursor = msc.cursor()
# compile sql statement
insert_statement = """insert into Advanced_Performance_Info values('%s','%s',%d,%d,%f,%f,%f,%f,'%s',%f,%f,%f,%f,%f,%f,%f)""" % insert_values
insert_result = cursor.execute(insert_statement)
msc.commit()
if insert_result:
print("[Success] Insert completed!")
else:
print("[Error] Insert failed!")
cursor.close()
ms.closeConn()
def bond(product, date):
bf = BondFilter(product, date)
sqls = bf.get_allBond_sqls()
ms = MySQLConnector()
mysql_connection = ms.getConn()
df = pd.read_sql(sql=sqls, con=mysql_connection)
bcs = Bond_CategorySum()
allbond = bcs.CreditCategory_AllBond(df)
print(allbond)
class DGBPI(object):
def __init__(self):
self.bpis = BPIS()
datatype_tmp = self.bpis.getConfig('BPI_table', 'columns')
self.datatype = datatype_tmp.split(", ")
# print(self.datatype)
self.BIPS_daily_data = []
self.ms = MySQLConnector()
self.msc = self.ms.getConn()
self.cursor = self.msc.cursor()
def composeArray(self, pid, bizdate):
self.BIPS_daily_data = [bizdate, pid]
# ms = MySQLConnector()
# msc = ms.getConn()
# cursor = msc.cursor()
for dt in self.datatype:
sqls = self.bpis.setBPIS(pid, bizdate, dt)
# print(sqls)
self.cursor.execute(sqls)
value = self.cursor.fetchone()
# print(value)
if value is not None:
append_value = float(value[0])
else:
append_value = 0
# print(append_value)
self.BIPS_daily_data.append(append_value)
print(self.BIPS_daily_data)
# cursor.close()
# ms.closeConn()
# return self.BIPS_daily_data
def insertArray(self):
# format list to tuple
insert_values = tuple(self.BIPS_daily_data)
# start mysql connection
# ms = MySQLConnector()
# msc = ms.getConn()
# cursor = msc.cursor()
# compile sql statement
insert_statement = """insert into Basic_Performance_Info values('%s','%s',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f)""" % insert_values
insert_result = self.cursor.execute(insert_statement)
self.msc.commit()
if insert_result:
print("[Success] Insert completed!")
else:
print("[Error] Insert failed!")
self.cursor.close()
self.ms.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 future(product, date):
ff = FutureFilter(product, date)
futuresqls = ff.get_sqls()
ms = MySQLConnector()
ms_connection = ms.getConn()
df = pd.read_sql(sql=futuresqls, con=ms_connection)
bondfuture_sum = 0
if_sum = 0
ic_sum = 0
ih_sum = 0
for index, row in df.iterrows():
future_code = row['VC_SCDM']
if future_code[0] == 'T':
bondfuture_sum += row['EN_SZ']
if future_code[0:1] == 'IF':
if_sum += row['EN_SZ']
if future_code[0:1] == 'IC':
ic_sum += row['EN_SZ']
if future_code[0:1] == 'IH':
ih_sum += row['EN_SZ']
bondfuture_mild = bondfuture_sum * -0.0151
bondfuture_moderate = bondfuture_sum * -0.0215
bondfuture_severe = bondfuture_sum * -0.0272
print("Bond Future result: %.4f, %.4f, %.4f" %
(bondfuture_mild, bondfuture_moderate, bondfuture_severe))
if_case = indexfuture('IF')
ih_case = indexfuture('IH')
ic_case = indexfuture('IC')
indexfuture_if_mild = if_sum * if_case[0]
indexfuture_if_moderate = if_sum * if_case[1]
indexfuture_if_severe = if_sum * if_case[2]
indexfuture_ih_mild = ih_sum * ih_case[0]
indexfuture_ih_moderate = ih_sum * ih_case[1]
indexfuture_ih_severe = ih_sum * ih_case[2]
indexfuture_ic_mild = ic_sum * ic_case[0]
indexfuture_ic_moderate = ic_sum * ic_case[1]
indexfuture_ic_severe = ic_sum * ic_case[2]
loss_mild = bondfuture_mild + indexfuture_if_mild + indexfuture_ih_mild + indexfuture_ic_mild
loss_moderate = bondfuture_moderate + indexfuture_if_moderate + indexfuture_ih_moderate + indexfuture_ic_moderate
loss_severe = bondfuture_severe + indexfuture_if_severe + indexfuture_ih_severe + indexfuture_ic_severe
print("轻： %.4f | 中： %.4f | 重： %.4f" %
(loss_mild, loss_moderate, loss_severe))
class ProductList(object):
def __init__(self):
config = GlobalConfig()
self.table = config.getConfig('mysql_tables', 'product')
self.sqlStatement = "select * from %s" % self.table
# print(self.sqlStatement)
self.df = pd.DataFrame()
self.mysql = MySQLConnector()
self.conn = self.mysql.getConn()
def getProductTable(self):
self.df = pd.read_sql(sql=self.sqlStatement, con=self.conn)
self.mysql.closeConn()
return self.df
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 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 __init__(self, productID, EndDate=None):
m = APIS()
sqls = m.setAPIS(productID, EndDate)
sqls_stock = m.stock(productID, EndDate)
sqls_purebond = m.pureBond(productID, EndDate)
sqls_convertablebond = m.convertableBond(productID, EndDate)
ms = MySQLConnector()
connection = ms.getConn()
self.df = pd.read_sql(sql=sqls, con=connection)
self.df_stock = pd.read_sql(sql=sqls_stock, con=connection)
self.df_purebond = pd.read_sql(sql=sqls_purebond, con=connection)
self.df_convertablebond = pd.read_sql(sql=sqls_convertablebond,
con=connection)
ms.closeConn()
# print(self.df)
td = TradingDay()
self.checker_naturaldays = td.getProductNaturalDayCounts(
productID, EndDate)
self.checker_tradedays = td.getProductTradingDayCounts(
productID, EndDate)
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)
'beta_by_index'].astype(float) * df_temp['Severe'].astype(float)
cb_result = ["ConvertibleBondLoss"]
list = ['Mild', 'Moderate', 'Severe']
for i in list:
cb_result.append(round(df_temp[i].sum(), 2))
return cb_result
if __name__ == '__main__':
product = "FB0009"
date = "20190211"
bf = BondFilter(product, date)
sqls_cb = bf.get_CBond_sqls()
sqls_pb = bf.get_PBond_sqls()
ms = MySQLConnector()
mysql_connection = ms.getConn()
df_cb = pd.read_sql(sql=sqls_cb, con=mysql_connection)
df_pb = pd.read_sql(sql=sqls_pb, con=mysql_connection)
bcs = Bond_CategorySum()
cb = bcs.Category_CB(df_cb)
pb = bcs.CreditCategory_PB(df_pb)
bsc = Bond_StressCalculate()
pbb = bsc.PureBondBreak(pb)
pbl = bsc.PureBondLoss(pb)
cbl = bsc.CBondLoss(cb)
print(pbb)
print(pbl)
print(cbl)
if future != prefixlist[-1]:
ffc = "%s like '%s_%%' or" % (column_kmdm, future)
else:
ffc = "%s like '%s_%%'" % (column_kmdm, future)
ffc_List.append(ffc)
FuzzyCondition = ' '.join(ffc_List)
sqls = "select distinct * from %s where %s = '%s' and left(%s,10) = '%s' and (%s)" \
% (valuation_table, column_productid, self.productID, column_date, td, FuzzyCondition)
return sqls
def get_sqls(self):
FuturePrefixList = self.getConfig('DerivativeCategory',
'Finacial_Future_prefix_List')
FuturePrefixList = FuturePrefixList.split(", ")
FutureSqls = self.prefix2sql(FuturePrefixList)
return FutureSqls
if __name__ == '__main__':
product = "FB0001"
date = "20190228"
ff = FutureFilter(product, date)
futuresqls = ff.get_sqls()
# print(futuresqls)
ms = MySQLConnector()
ms_connection = ms.getConn()
df_future = pd.read_sql(sql=futuresqls, con=ms_connection)
print(df_future)
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 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
