diff options
| -rw-r--r-- | python/populate_tranche_cashflows.py | 24 | ||||
| -rw-r--r-- | sql/dawn.sql | 8 |
2 files changed, 32 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) diff --git a/sql/dawn.sql b/sql/dawn.sql index 253e516c..d62618b1 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2190,3 +2190,11 @@ create table id_mapping( full_globeop_id text GENERATED ALWAYS AS (trade_type||lpad(globeop_id::text, 6, '0')|| 'K00SCLMA') stored, PRIMARY KEY (date, trade_type, serenitas_id) ); + +CREATE TABLE tranche_cashflows( + date date NOT NULL, + tranche_id integer NOT NULL, + principal float, + accrued float, + PRIMARY KEY (date, tranche_id) +); |