1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
import datetime
from pyisda.date import previous_twentieth
from serenitas.utils.db import dbconn, serenitas_pool
from risk.tranches import get_tranche_portfolio
from serenitas.analytics.dates import bus_day
from typing import Tuple
def accrual_days(event_date, auction_date):
return (event_date - previous_twentieth(auction_date)).days + 1
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
return severity, acc
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(
"SELECT id, notional, protection, orig_attach, orig_detach, "
"fixed_rate / 100 AS coupon_rate FROM cds WHERE id IN %s",
(tranche_ids,),
)
for r in c:
direction = 1.0 if r.protection == "Buyer" else -1.0
notional = r.notional / (r.orig_detach - r.orig_attach)
if r.orig_detach == 100:
accrued = (
-direction * (1 - severity) * acc * r.coupon_rate / 360 * notional
)
principal = 0
else:
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) "
"ON CONFLICT (date, tranche_id) DO NOTHING",
(auction_date + bus_day * 3, tranche_id, principal, accrued, "USD"),
)
dawndb.commit()
def insert_tranche_accrued(d: datetime.date, dawndb, funds=("SERCGMAST",)):
portf = get_tranche_portfolio(d, dawndb, funds=funds)
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) "
"ON CONFLICT (date, tranche_id) DO NOTHING ",
(
t.cs["payment_dates"][0],
t.trade_id,
-t.cs["coupons"][0]
* t.tranche_running
* t.notional
* t.tranche_factor
* 1e-4,
t.currency,
),
)
dawndb.commit()
if __name__ == "__main__":
dawndb = dbconn("dawndb")
# conn = serenitas_pool.getconn()
# auction_date = datetime.date(2022, 6, 7)
# severity, acc = get_default_information(auction_date, conn)
# insert_tranche_cashflows(auction_date, severity, acc, (3157,
# 3154,
# 3096,
# 3078,
# 3715,
# 3156,
# 3153,
# 3126,
# 3102,
# 3088,
# 3070,
# 3030,
# 2993,
# 3400), dawndb)
# serenitas_pool.putconn(conn)
import serenitas.analytics
serenitas.analytics._include_todays_cashflows = True
funds = ("SERCGMAST", "BOWDST", "BRINKER", "ISOSEL")
insert_tranche_accrued(datetime.date(2023, 3, 15), dawndb, funds)
|
