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 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 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 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 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)
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"
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 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