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