def price_diff_IB_IB(bondCode1, bondCode2, start_time, end_time, cf=1): sql = """ SELECT A.bondCode bondCode1, A.last_bid_cleanprice bondCode1_last_bid_cleanprice, A.last_ofr_cleanprice bondCode1_last_ofr_cleanprice, A.last_bid bondCode1_last_bid, A.last_ofr bondCode1_last_ofr, B.bondCode bondCode2, B.last_bid_cleanprice bondCode2_last_bid_cleanprice, B.last_ofr_cleanprice bondCode2_last_ofr_cleanprice, B.last_bid bondCode2_last_bid, B.last_ofr bondCode2_last_ofr, --价差取bid,ofr和sell,buy的中间价 (A.last_bid_cleanprice+A.last_ofr_cleanprice)/2*%d - (B.last_bid_cleanprice+B.last_ofr_cleanprice)/2 as cleanprice_mid_diff, (A.last_bid+A.last_ofr)/2 - (B.last_bid+B.last_ofr)/2 as yield_mid_diff, A.createDateTime FROM QBBBO_history_reg1 A LEFT JOIN QBBBO_history_reg1 B ON A.createDateTime = B.createDateTime WHERE A.bondCode = '%s' --债券名 AND B.bondCode = '%s' --债券名 AND A.createDateTime > '%s' --起始时间 AND A.createDateTime <= '%s' --结束时间 ORDER BY A.createDateTime """ % (cf, bondCode1, bondCode2, start_time, end_time) df = pd.read_sql_query(sql, engine_103) ## 数据清洗 df.loc[is_outlier(df['cleanprice_mid_diff'].values) == 1, 'cleanprice_mid_diff'] = np.NaN df.loc[is_outlier(df['yield_mid_diff'].values) == 1, 'yield_mid_diff'] = np.NaN return df
def price_diff_ZJ_IB(Scode, bondCode, start_time, end_time, cf=1): sql = """ SELECT A.SCode, B.bondCode, CASE WHEN A.BuyPrice1 = 0 THEN NULL ELSE A.BuyPrice1 END AS BuyPrice1, CASE WHEN A.SellPrice1 = 0 THEN NULL ELSE A.SellPrice1 END AS SellPrice1, B.last_bid_cleanprice, B.last_ofr_cleanprice, (CASE WHEN A.BuyPrice1 = 0 THEN NULL ELSE A.BuyPrice1 END +CASE WHEN A.SellPrice1 = 0 THEN NULL ELSE A.SellPrice1 END )/2*%d - (B.last_bid_cleanprice+b.last_ofr_cleanprice)/2 as mid_diff, A.updatetime FROM FutureHQ_reg A LEFT JOIN QBBBO_history_reg1 B ON A.updatetime = B.createDateTime WHERE A.SCode = '%s' AND B.bondCode = '%s' AND A.updatetime > '%s' AND A.updatetime <= '%s' ORDER BY A.updatetime """ % (cf, Scode, bondCode, start_time, end_time) df = pd.read_sql_query(sql, engine_103) # 数据清洗,去除掉三个标准差以外的数值 df.loc[is_outlier(df['mid_diff'].values) == 1, 'mid_diff'] = np.NaN return df
def price_diff_ZJ_ZJ(SCode1, Scode2, start_time, end_time, cf=1): sql = """ SELECT A.SCode SCode1, CASE WHEN A.BuyPrice1 = 0 THEN NULL ELSE A.BuyPrice1 END AS SCode1_BuyPrice1, CASE WHEN A.SellPrice1 = 0 THEN NULL ELSE A.SellPrice1 END AS SCode1_SellPrice1, B.SCode Scode2, CASE WHEN B.BuyPrice1 = 0 THEN NULL ELSE B.BuyPrice1 END AS SCode2_BuyPrice1, CASE WHEN B.SellPrice1 = 0 THEN NULL ELSE B.SellPrice1 END AS SCode2_SellPrice1, --价差取bid,ofr和sell,buy的中间价 (CASE WHEN A.BuyPrice1 = 0 THEN NULL ELSE A.BuyPrice1 END +CASE WHEN A.SellPrice1 = 0 THEN NULL ELSE A.SellPrice1 END )/2*%d - (CASE WHEN B.BuyPrice1 = 0 THEN NULL ELSE B.BuyPrice1 END +CASE WHEN B.SellPrice1 = 0 THEN NULL ELSE B.SellPrice1 END )/2 as mid_diff, A.updatetime FROM FutureHQ_reg A LEFT JOIN FutureHQ_reg B ON A.updatetime = B.updatetime WHERE A.SCode = '%s' --期货代码 AND B.SCode = '%s' --债券名 AND A.updatetime > '%s' --起始时间 AND A.updatetime <= '%s' --结束时间 ORDER BY A.updatetime """ (cf, SCode1, Scode2, start_time, end_time) df = pd.read_sql_query(sql, engine_103) # 去除掉3个标准差以外的数值 df.loc[is_outlier(df['mid_diff'].values) == 1, 'mid_diff'] = np.NaN return df