diff options
| -rw-r--r-- | python/bbg_index_quotes.py | 32 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 9 |
2 files changed, 41 insertions, 0 deletions
diff --git a/python/bbg_index_quotes.py b/python/bbg_index_quotes.py new file mode 100644 index 00000000..e16bc22a --- /dev/null +++ b/python/bbg_index_quotes.py @@ -0,0 +1,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() - 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() diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 90decbe0..42ff5338 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -391,6 +391,15 @@ CREATE TABLE index_quotes( theta2 float,
PRIMARY KEY(date, index, series, tenor, version));
+CREATE TABLE bbg_index_quotes(
+ date date,
+ ticker text,
+ version smallint,
+ tenor tenor,
+ last_price float,
+ source bbgSource,
+ PRIMARY KEY(date, ticker, version));
+
CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series integer, pg_tenor text,
pg_date date)
RETURNS SETOF tranche_quotes AS
|