1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
from bbg_helpers import init_bbg_session, BBG_IP, retrieve_data
import datetime
from utils.db import dbconn
securities = {}
for series in range(9, 34):
for index_type in ["IG", "HY"]:
for t in [3, 5, 7, 10]:
securities[f"CDX {index_type} CDSI S{series} {t}Y Corp"] = (
series,
index_type,
f"{t}yr",
)
for series in range(10, 33):
for index_type in ["EUR", "XOVER"]:
for t in [3, 5, 7, 10]:
securities[f"ITRX {index_type} CDSI S{series} {t}Y Corp"] = (
series,
index_type,
f"{t}yr",
)
place_holders = ",".join(["%s"] * 7)
sql_str_price = (
"INSERT INTO index_quotes_pre"
"(date, index, series, version, tenor, close_price, source)"
f" VALUES({place_holders}) ON CONFLICT DO NOTHING"
)
sql_str_spread = (
"INSERT INTO index_quotes_pre"
"(date, index, series, version, tenor, close_spread, source)"
f" VALUES({place_holders}) ON CONFLICT DO NOTHING"
)
start_date = datetime.date.today() - datetime.timedelta(days=7) # one weeek of overlap
conn = dbconn("serenitasdb")
with init_bbg_session(BBG_IP) as session:
d = retrieve_data(session, securities.keys(), fields=["TICKER", "VERSION"])
ticker_mapping = {
v["TICKER"]: securities[k] + (v["VERSION"],) for k, v in d.items() if v
}
for pcs in ["MSG1", "CMAN", "CBGN"]:
securities = [f"{e['TICKER']} {pcs} Curncy" for e in d.values() if e]
d2 = retrieve_data(
session, securities, fields=["PX_LAST"], start_date=start_date
)
with conn.cursor() as c:
for k, v in d2.items():
ticker = k.split()[0]
series, index, tenor, version = ticker_mapping[ticker]
if index == "EUR":
index = "EU"
if index == "XOVER":
index = "XO"
if index == "HY":
sql_str = sql_str_price
else:
sql_str = sql_str_spread
if not v.empty:
c.executemany(
sql_str,
[
(t[0], index, series, version, tenor, t[1], pcs)
for t in v.itertuples()
],
)
conn.commit()
|
