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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
from serenitas.analytics.api import Portfolio, DualCorrTranche
import logging
logger = logging.getLogger(__name__)
def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST", **kwargs):
if by_strat:
sql_string = "SELECT * from list_tranche_positions_by_strat(%s, %s)"
else:
sql_string = (
"SELECT * FROM list_cds(%s, %s) "
"WHERE orig_attach IS NOT NULL "
"ORDER BY security_desc, attach"
)
with conn.cursor() as c:
c.execute(sql_string, (date, fund))
trade_ids = list(c)
portf = Portfolio(
[
DualCorrTranche(
redcode=t.security_id,
maturity=t.maturity,
notional=t.notional,
tranche_running=t.fixed_rate * 100,
attach=t.orig_attach,
detach=t.orig_detach,
corr_attach=None,
corr_detach=None,
value_date=t.trade_date,
trade_id=t.id,
)
for t in trade_ids
]
)
if by_strat:
portf.trade_ids = [
(tid.folder, f"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}")
for tid, t in zip(trade_ids, portf.trades)
]
else:
portf.trade_ids = [(t.folder, t.id) for t in trade_ids]
portf.value_date = date
portf.mark(**kwargs)
return portf
def insert_tranche_portfolio(portf, conn):
cols = [
"date",
"tranche_id",
"notional",
"clean_nav",
"accrued",
"duration",
"delta",
"gamma",
"theta",
"theta_amount",
"corr01",
"tranche_factor",
"upfront",
"running",
"corr_attach",
"corr_detach",
"index_refprice",
"index_refspread",
"index_duration",
"hy_equiv",
]
update_str = ",".join(f"{c} = EXCLUDED.{c}" for c in cols[2:])
sql_str = (
f"INSERT INTO tranche_risk({','.join(cols)}) "
f"VALUES({','.join(['%s'] * len(cols))}) "
" ON CONFLICT (date, tranche_id) DO UPDATE "
f"SET {update_str}"
)
with conn.cursor() as c:
for (strat, trade_id), trade in portf.items():
logger.info(f"marking tranche {trade_id} in {strat}")
try:
theta = trade.theta(method="TLP")
except (ValueError, RuntimeError) as e:
# when there is less than one year left we computed the theta to maturity
logger.info(str(e))
theta = (
trade.clean_pv
/ trade.notional
/ trade.tranche_factor
/ trade._index._fx
+ trade.tranche_running * 1e-4 * trade.duration
)
c.execute(
sql_str,
(
trade.value_date,
trade_id,
trade.notional,
trade.clean_pv,
trade.accrued,
trade.duration,
trade.delta,
trade.gamma,
theta,
-theta * trade.notional * trade.tranche_factor * trade._index._fx,
trade.corr01,
trade.tranche_factor,
trade.upfront,
trade.tranche_running,
trade.rho[0],
trade.rho[1],
100 - float(trade._index.pv()) * 100,
trade._index._snacspread(
trade._index.coupon(), trade._index.recovery, trade.maturity
)
* 10000,
float(trade._index.duration()),
trade.hy_equiv,
),
)
conn.commit()
|
