diff options
| -rw-r--r-- | python/populate_tranche_cashflows.py | 87 |
1 files changed, 56 insertions, 31 deletions
diff --git a/python/populate_tranche_cashflows.py b/python/populate_tranche_cashflows.py index b92430bc..dc73b475 100644 --- a/python/populate_tranche_cashflows.py +++ b/python/populate_tranche_cashflows.py @@ -3,45 +3,70 @@ 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 +from typing import Tuple 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 +def get_default_information(auction_date: datetime.date, conn): + with conn.cursor() as c: + c.execute("SELECT * FROM defaulted WHERE auction_date=%s", (auction_date,)) + r = c.fetchone() 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, %s)", - (r.auction_date + bus_day, tranche_id, principal, accrued, "USD"), - ) - dawndb.commit() -serenitas_pool.putconn(conn) + return severity, acc -portf = get_tranche_portfolio(datetime.date(2020, 6, 19), dawndb) -with dawndb.cursor() as c: - for t in portf: + +def insert_tranche_cashflows( + auction_date: datetime.date, + severity: float, + acc: float, + tranche_ids: Tuple[int], + dawndb, +): + data = [] + with dawndb.cursor() as c: 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, - ), + "SELECT id, notional, protection, orig_attach, orig_detach, " + "fixed_rate / 100 AS coupon_rate FROM cds WHERE id IN %s", + (tranche_ids,), ) -dawndb.commit() + for r in c: + direction = 1.0 if r.protection == "Buyer" else -1.0 + notional = r.notional / (r.orig_detach - r.orig_attach) + accrued = -direction * severity * acc * r.coupon_rate / 360 * notional + principal = direction * notional * severity + data.append([r.id, principal, accrued]) + + with dawndb.cursor() as c: + for tranche_id, principal, accrued in data: + c.execute( + "INSERT INTO tranche_cashflows VALUES(%s, %s, %s, %s, %s)", + (auction_date + bus_day, tranche_id, principal, accrued, "USD"), + ) + dawndb.commit() + + +# 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() +if __name__ == "__main__": + conn = serenitas_pool.getconn() + dawndb = dbconn("dawndb") + auction_date = datetime.date(2020, 7, 7) + severity, acc = get_default_information(auction_date, conn) + insert_tranche_cashflows(auction_date, severity, acc, (1590, 1413, 1116), dawndb) + serenitas_pool.putconn(conn) |