1 files changed, 70 insertions, 43 deletions
diff --git a/python/recon_bowdst.py b/python/recon_bowdst.py
index a37d74ac..c8706d7e 100644
--- a/python/recon_bowdst.py
+++ b/python/recon_bowdst.py
@@ -1,58 +1,85 @@
 import pandas as pd
 from serenitas.utils.db import dbconn
 
-dawndb = dbconn("dawndb")
 
-df = pd.read_excel("/home/serenitas/flint/rec.xlsx")
+def difference(df):
+    if ("db_mv" in df.columns) and ("db_notional" in df.columns):
+        df["mv_difference"] = df["db_mv"] - df["admin_mv"]
+        df["notional_difference"] = df["db_notional"] - df["admin_notional"]
+    elif "db_mv" in df.columns:
+        df["mv_difference"] = df["db_mv"] - df["admin_mv"]
+    elif "db_notional" in df.columns:
+        df["notional_difference"] = df["db_notional"] - df["admin_notional"]
+    return df
 
-security_balance = df[df["Asset Type"] == "FIXED INCOME SECURITIES"][
-    "Base Market Value"
-].sum()
-print(f"The current security balance is {security_balance}")
 
-bowd_bond_trades = df[df["CUSIP"].notnull()]
+def recon(hierarchy_file, date):
+    df = pd.read_excel(hierarchy_file)
+    security_balance = df[df["Asset Type"] == "FIXED INCOME SECURITIES"][
+        "Base Market Value"
+    ].sum()
+    bowd_bond_trades = df[df["CUSIP"].notnull()]
+    bond_asset_classes = ["Subprime", "CRT", "CLO"]
 
-asset_classes = ["Subprime", "CRT", "CLO"]
+    for asset in bond_asset_classes:
+        db_bond_trades = pd.read_sql_query(
+            f"select * from risk_positions(%s, %s, 'BOWDST')",
+            dawndb,
+            params=(date, asset),
+        )
 
-for asset in asset_classes:
-    db_bond_trades = pd.read_sql_query(
-        f"select * from risk_positions('2021-02-26', %s, 'BOWDST')",
+        bond_trades = bowd_bond_trades.merge(
+            db_bond_trades,
+            left_on="Mellon Security ID",
+            right_on="identifier",
+            how="right",
+        )[
+            [
+                "description",
+                "identifier",
+                "notional",
+                "factor",
+                "Shares/Par",
+                "Base Market Value",
+                "usd_market_value",
+            ]
+        ]
+        bond_trades["db_notional"] = bond_trades["Shares/Par"] * bond_trades["factor"]
+        bond_trades.rename(
+            columns={
+                "usd_market_value": "db_mv",
+                "Shares/Par": "admin_notional",
+                "Base Market Value": "admin_mv",
+            },
+            inplace=True,
+        )
+
+    tranche_trades = pd.read_sql_query(
+        f"select security_desc, maturity, orig_attach, orig_detach, sum(notional * tranche_factor) as db_notional, sum(admin_notional) as admin_notional, sum(serenitas_clean_nav) as db_mv, sum(admin_clean_nav) as admin_mv from tranche_risk_bowdst where date=%s group by security_desc, maturity, orig_attach, orig_detach ;",
         dawndb,
-        params=(asset,),
+        params=(date,),
     )
 
-    new_df = bowd_bond_trades.merge(
-        db_bond_trades, left_on="Mellon Security ID", right_on="identifier", how="right"
-    )[
-        [
-            "description",
-            "identifier",
-            "notional",
-            "factor",
-            "Shares/Par",
-            "Base Market Value",
-            "usd_market_value",
-        ]
-    ]
-    new_df["db_notional"] = new_df["Shares/Par"] * new_df["factor"]
-#     print(new_df)
+    cdx_trades = pd.read_sql_query(
+        f"select security_id, security_desc, index, series, version, maturity, globeop_notional as admin_notional, notional * factor as db_notional, clean_nav as db_mv, globeop_nav as admin_mv from list_cds_marks(%s, null, 'BOWDST')",
+        dawndb,
+        params=(date,),
+    )
 
-date = "2021-02-28"
+    cdx_swaption_trades = pd.read_sql_query(
+        f"select security_id, option_type, strike, expiration_date, sum(serenitas_nav) as db_mv, sum(globeop_nav) as admin_mv from list_swaption_positions_and_risks(%s, 'BOWDST') group by security_id, option_type, strike, expiration_date;",
+        dawndb,
+        params=(date,),
+    )
 
-tranche_trades = pd.read_sql_query(
-    f"select security_desc, maturity, orig_attach, orig_detach, sum(notional * tranche_factor) as db_notional, sum(admin_notional) as admin_notional, sum(serenitas_clean_nav) as db_mv, sum(admin_clean_nav) as bowd_mv from tranche_risk_bowdst where date=%s group by security_desc, maturity, orig_attach, orig_detach ;",
-    dawndb,
-    params=(date,),
-)
+    kinds = [bond_trades, tranche_trades, cdx_trades, cdx_swaption_trades]
+    names = ["bond_trades", "tranche_trades", "cdx_trades", "cdx_swaption_trades"]
+    for kind, name in zip(kinds, names):
+        difference(kind).to_csv(f"/home/serenitas/flint/{name}_{date}.csv")
 
-cdx_trades = pd.read_sql_query(
-    f"select security_id, security_desc, index, series, version, maturity, globeop_notional as admin_notional, notional * factor as db_notional, clean_nav as db_nav, globeop_nav as admin_nav from list_cds_marks(%s, null, 'BOWDST')",
-    dawndb,
-    params=(date,),
-)
 
-cdx_swaption_trades = pd.read_sql_query(
-    f"select security_id, option_type, strike, expiration_date, sum(serenitas_nav) as db_mv, sum(globeop_nav) as admin_mv from list_swaption_positions_and_risks(%s, 'BOWDST') group by security_id, option_type, strike, expiration_date;",
-    dawndb,
-    params=(date,),
-)
+if __name__ == "__main__":
+    dawndb = dbconn("dawndb")
+    hierarchy_file = "/home/serenitas/flint/rec.xlsx"
+    date = "2021-02-28"
+    recon(hierarchy_file, date)