diff options
| -rw-r--r-- | python/bespoke_utils.py | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/python/bespoke_utils.py b/python/bespoke_utils.py index b9c5b1ce..8cf9d4b1 100644 --- a/python/bespoke_utils.py +++ b/python/bespoke_utils.py @@ -156,6 +156,51 @@ def add_basket_constituents( conn.commit() +def bbg_name(index_type: str, series: int, tenor="5yr"): + if index_type == "EU": + index_type = "EUR" + index_name = "ITRX" + elif index_type == "XO": + index_type = "XOVER" + index_name = "ITRX" + elif index_type == "HYBB": + index_name = "" + else: + index_name = "CDX" + return f"{index_name} {index_type} CDSI S{series} {tenor[:2].upper()} Corp".lstrip() + + +def get_index_members(index_type: str, series: int): + name = bbg_name(index_type, series) + with init_bbg_session(BBG_IP) as session: + df = retrieve_data(session, [name], ["INDX_MEMBERS"]) + df = df[name]["INDX_MEMBERS"] + tickers = df["column 4"] + company_ids = retrieve_data( + session, [t + " Corp" for t in tickers], ["cds_company_id"] + ) + company_ids = pd.DataFrame.from_dict(company_ids, orient="index") + company_ids = company_ids.reset_index() + company_ids["index"] = company_ids["index"].str.replace(" Corp", "") + company_ids = company_ids.set_index("index") + df = df.set_index("column 4").join(company_ids)[["cds_company_id", "column 3"]] + df.columns = ["company_id", "seniority"] + return df + + +def new_index_series(conn, basketid, index_type, series): + df = get_index_members(index_type, series) + with conn.cursor() as c: + c.executemany( + "INSERT INTO basket_constituents VALUES(%s, %s, %s, %s)", + [ + (t.company_id, t.seniority, basketid, 1.0) + for t in df.itertuples(index=False) + ], + ) + conn.commit() + + if __name__ == "__main__": df = pd.read_clipboard(header=None) from utils.db import serenitas_pool |