1 files changed, 73 insertions, 0 deletions
diff --git a/python/globeop_ce_tranche.py b/python/globeop_ce_tranche.py
new file mode 100644
index 00000000..4214737b
--- /dev/null
+++ b/python/globeop_ce_tranche.py
@@ -0,0 +1,73 @@
+import datetime
+
+from serenitas.utils.db import dbconn
+from serenitas.ops.trade_dataclasses import CDSDeal
+from serenitas.ops.funds import Service
+
+
+def calculate_attach_detach(redcode, orig_attach, orig_detach, conn):
+    with conn.cursor() as c:
+        c.execute(
+            "SELECT indexfactor, cumulativeloss FROM index_factors WHERE redindexcode=%s",
+            (redcode,),
+        )
+        (factor, cumulativeloss) = c.fetchone()
+        detach = factor * min(max((orig_detach - cumulativeloss) / factor, 0), 1)
+        attach = factor * min(max((orig_attach - cumulativeloss) / factor, 0.0), 1.0)
+    return attach, detach
+
+
+def upload_new_trades(index, affected_series, fund, event_date, conn, event_name):
+    NEW_TRADES_QUERY = (
+        "SELECT trg.*, b.security_id, cds.currency, cds.fixed_rate, cds.cp_code as custodian_cpty, cds.cp_code, cds.protection, cds.account_code, cds.frequency, "
+        "cds.dealid, cds.portfolio, cds.folder, cds.swap_type, cds.clearing_facility, a.custodian, a.cash_account "
+        "FROM tranche_risk_globeop trg "
+        "LEFT JOIN LATERAL (SELECT redindexcode as security_id FROM index_desc WHERE index_desc.lastdate >%s AND index_desc.index=trg.index AND index_desc.series=trg.series LIMIT 1) b ON TRUE "
+        "LEFT JOIN cds ON trg.trade_id=cds.id "
+        "LEFT JOIN accounts a ON cds.account_code=a.code "
+        "WHERE date=%s AND trg.fund=%s AND index=%s AND series in %s"
+    )
+
+    with conn.cursor() as c:
+        c.execute(
+            SQL_QUERY,
+            (
+                event_date,
+                event_date,
+                fund,
+                index,
+                affected_series,
+            ),
+        )
+        service = Service[fund]
+        for row in c:
+            d = row._asdict()
+            d["notional"] = abs(d["notional"])
+            d["traded_level"] = None
+            d["upfront"] = (d["serenitas_clean_nav"] + d["serenitas_accrued"]) * -1
+            d["trade_date"] = event_date
+            d["dealid"] = f'{d["dealid"]}_{event_name}'
+            d["attach"], d["detach"] = calculate_attach_detach(
+                d["security_id"], d["orig_attach"], d["orig_detach"], conn
+            )
+            trade = CDSDeal.from_dict(**d)
+            service.push_trade(trade, "NEW")
+        service.build_buffer(trade_type="cds")
+
+
+if __name__ == "__main__":
+    index = "HY"
+    affected_series = (
+        34,
+        35,
+        36,
+        37,
+        38,
+        39,
+    )
+    fund = "BOWDST"
+    event_date = datetime.date(2023, 4, 13)
+    event_name = "DIAMOND"
+
+    conn = dbconn("dawndb")
+    upload_new_trades(index, affected_series, fund, event_date, conn, event_name)