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
|
from bbg_helpers import init_bbg_session, BBG_IP, retrieve_data
import datetime
from db import dbconn
securities = {}
for series in range(9, 30):
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")
sql_str = (f"INSERT INTO bbg_index_quotes VALUES({','.join(['%s'] * 8)}) "
"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()}
for pcs in ["MSG1", "CMAN", "CBGN"]:
securities = [f"{e['TICKER']} {pcs} Curncy" for e in d.values()]
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 not v.empty:
c.executemany(sql_str,
[(t[0], ticker, index, series, tenor, version, t[1], pcs)
for t in v.itertuples()])
conn.commit()
|
