1 files changed, 85 insertions, 0 deletions
diff --git a/python/recon_bowdst.py b/python/recon_bowdst.py
new file mode 100644
index 00000000..c8706d7e
--- /dev/null
+++ b/python/recon_bowdst.py
@@ -0,0 +1,85 @@
+import pandas as pd
+from serenitas.utils.db import dbconn
+
+
+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
+
+
+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"]
+
+    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),
+        )
+
+        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=(date,),
+    )
+
+    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,),
+    )
+
+    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,),
+    )
+
+    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")
+
+
+if __name__ == "__main__":
+    dawndb = dbconn("dawndb")
+    hierarchy_file = "/home/serenitas/flint/rec.xlsx"
+    date = "2021-02-28"
+    recon(hierarchy_file, date)