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