1 files changed, 24 insertions, 0 deletions
diff --git a/python/populate_tranche_cashflows.py b/python/populate_tranche_cashflows.py
new file mode 100644
index 00000000..56f794b8
--- /dev/null
+++ b/python/populate_tranche_cashflows.py
@@ -0,0 +1,24 @@
+import datetime
+from pyisda.date import previous_twentieth
+from utils.db import serenitas_pool, dbconn
+
+def accrual_days(event_date, auction_date):
+    return (event_date - previous_twentieth(auction_date)).days + 1
+
+dawndb = dbconn("dawndb")
+conn = serenitas_pool.getconn()
+with conn.cursor() as c:
+    c.execute("SELECT * FROM defaulted ORDER BY auction_date")
+    for r in c:
+        if r.auction_date < datetime.date(2020, 5, 1):
+            continue
+        acc = accrual_days(r.event_date, r.auction_date)
+        severity = 1 - r.recovery
+        principal = -1e6 * severity
+        accrued = severity * acc*0.05/360 * 1e6
+        with dawndb.cursor() as c:
+            for tranche_id in (1590, 1413, 1116):
+                c.execute("INSERT INTO tranche_cashflows VALUES(%s, %s, %s, %s)",
+                          (r.auction_date, tranche_id, principal, accrued))
+        dawndb.commit()
+serenitas_pool.putconn(conn)