1 files changed, 72 insertions, 10 deletions
diff --git a/python/new_index_series.py b/python/new_index_series.py
index 18580657..5171694d 100644
--- a/python/new_index_series.py
+++ b/python/new_index_series.py
@@ -68,24 +68,86 @@ def get_index_members(index_type: str, series: int) -> pd.DataFrame:
     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"]
+    df = df.rename(columns={"column   4": "ticker"})
+    df = df.set_index("ticker").join(company_ids)
+    df = df.drop("column   5", axis=1)
+    df.columns = ["name", "weight", "currency", "seniority", "company_id"]
+    df.seniority = df.seniority.replace("LAC", "SLA")
     return df
 
 
-def new_index_series(conn, basketid, index_type, series):
-    df = get_index_members(index_type, series)
+def new_index_series(conn, basketid, df):
     with conn.cursor() as c:
         c.executemany(
             "INSERT INTO basket_constituents VALUES(%s, %s, %s, %s)",
             [
-                (
-                    t.company_id,
-                    "SLA" if t.seniority == "LAC" else t.seniority,
-                    basketid,
-                    1.0,
-                )
+                (t.company_id, t.seniority, basketid, 1.0,)
                 for t in df.itertuples(index=False)
             ],
         )
     conn.commit()
+
+
+def add_missing_issuers(conn, df):
+    all_company_ids = pd.read_sql_query(
+        "SELECT company_id, seniority FROM bbg_issuers",
+        conn,
+        index_col=["company_id", "seniority"],
+    )
+    df = df.reset_index().set_index(["company_id", "seniority"])
+    df = df.loc[df.index.difference(all_company_ids.index)]
+    with init_bbg_session(BBG_IP) as session:
+        tickers = [f"{t} Corp" for t in df.ticker]
+        bbg_data = retrieve_data(
+            session,
+            tickers,
+            [
+                "cds_restructuring_type",
+                "cds_corp_tkr",
+                "restructuring_type_short_code",
+                "cds_curve_info",
+            ],
+        )
+    sql_str = (
+        "INSERT INTO bbg_issuers "
+        "(name, company_id, ticker, currency, seniority, doc_clause, cds_curve, short_code)"
+        "VALUES(%s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING"
+    )
+    df = df.reset_index()
+    with conn.cursor() as c:
+        for r in df.itertuples(index=False):
+            data = bbg_data[f"{r.ticker} Corp"]
+            c.execute(
+                sql_str,
+                (
+                    r.name,
+                    r.company_id,
+                    data["cds_corp_tkr"],
+                    r.currency,
+                    r.seniority,
+                    data["cds_restructuring_type"].replace("-", " "),
+                    data["cds_curve_info"].Ticker.to_list(),
+                    data["restructuring_type_short_code"],
+                ),
+            )
+    conn.commit()
+    return df
+
+
+def markit_mappings(conn, df):
+    sql_str = "INSERT INTO bbg_markit_mapping VALUES('infinity', %s, %s, %s, %s, %s)"
+    with conn.cursor() as c:
+        for r in df.itertuples(index=False):
+            c.execute(sql_str, r)
+    conn.commit()
+
+
+if __name__ == "__main__":
+    from utils.db import serenitas_pool
+
+    conn = serenitas_pool.getconn()
+    df = get_index_members("XO", 34)
+    missing = add_missing_issuers(conn, df)
+    basketid = new_basket_id(conn, "XO", 34)
+    new_index_series(conn, basketid, df)
+    serenitas_pool.putconn(conn)