diff options
| -rw-r--r-- | python/populate_tranche_cashflows.py | 29 | ||||
| -rw-r--r-- | sql/dawn.sql | 1 |
2 files changed, 27 insertions, 3 deletions
diff --git a/python/populate_tranche_cashflows.py b/python/populate_tranche_cashflows.py index 56f794b8..b92430bc 100644 --- a/python/populate_tranche_cashflows.py +++ b/python/populate_tranche_cashflows.py @@ -1,24 +1,47 @@ import datetime from pyisda.date import previous_twentieth from utils.db import serenitas_pool, dbconn +from risk.tranches import get_tranche_portfolio +from dates import bus_day + 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.recovery is None: + continue 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 + 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)) + c.execute( + "INSERT INTO tranche_cashflows VALUES(%s, %s, %s, %s, %s)", + (r.auction_date + bus_day, tranche_id, principal, accrued, "USD"), + ) dawndb.commit() serenitas_pool.putconn(conn) + +portf = get_tranche_portfolio(datetime.date(2020, 6, 19), dawndb) +with dawndb.cursor() as c: + for t in portf: + c.execute( + "INSERT INTO tranche_cashflows(date, tranche_id, accrued, currency) " + "VALUES(%s, %s, %s, %s)", + ( + datetime.date(2020, 6, 22), + t.trade_id, + -t.cs["coupons"][0] * t.tranche_running * t.notional * 1e-4, + t.currency, + ), + ) +dawndb.commit() diff --git a/sql/dawn.sql b/sql/dawn.sql index d62618b1..b5871b8d 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2196,5 +2196,6 @@ CREATE TABLE tranche_cashflows( tranche_id integer NOT NULL, principal float, accrued float, + currency currency, PRIMARY KEY (date, tranche_id) ); |