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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
import datetime
import logging
import pandas as pd
from serenitas.analytics.api import Portfolio, CreditIndex
from serenitas.analytics.index_data import on_the_run, hist_spreads
from math import sqrt
from psycopg import Connection
from typing import Iterable, Tuple, Union
logger = logging.getLogger(__name__)
def get_index_portfolio(
d: datetime.date,
conn: Connection,
fund: str = "SERCGMAST",
strategies: Union[Tuple[str], str] = (),
include_strategies: Union[str, None] = None,
exclude_strategies: Union[str, None] = None,
exclude_redcode: Iterable[str] = (),
by_strat: bool = True,
**kwargs,
):
select_cols = [
"security_id AS redcode",
"security_desc",
"sum(notional) AS notional" if not by_strat else "notional",
"maturity",
]
if by_strat:
select_cols += ["folder"]
sql_str = f"SELECT {','.join(select_cols)} FROM list_cds_positions_by_strat(%s, %s)"
params = (d, fund)
folder_filter = []
if strategies != ():
if isinstance(strategies, tuple):
folder_filter.append("folder::text = ANY(%s)")
strategies = list(strategies)
else:
folder_filter.append("folder = %s")
params += (strategies,)
if include_strategies is not None:
folder_filter.append("folder::text LIKE %s")
params += (include_strategies,)
if exclude_strategies is not None:
folder_filter.append("folder::text NOT LIKE %s")
params += (exclude_strategies,)
if folder_filter:
sql_str += " WHERE " + " AND ".join(folder_filter)
if not by_strat:
sql_str += " GROUP BY security_id, security_desc, maturity"
with conn.cursor() as c:
c.execute(sql_str, params)
trades = [
(
CreditIndex(
redcode=rec.redcode,
maturity=rec.maturity,
notional=rec.notional,
value_date=d,
freeze_version=True,
),
(rec.folder if by_strat else "", rec.security_desc),
)
for rec in c
if (rec.redcode not in exclude_redcode and abs(rec.notional) > 0.1)
]
if trades:
portf = Portfolio(*zip(*trades))
portf.mark()
else:
portf = Portfolio([])
return portf
def VaR(portf: Portfolio, quantile=0.05, years: int = 5, period="monthly"):
index_types = tuple(set(t.index_type for t in portf))
returns = hist_spreads(
portf.value_date, index_types, ["3yr", "5yr", "7yr", "10yr"], years
)
portf.reset_pv()
spreads = pd.DataFrame(
{
"spread": portf.spread,
"tenor": [ind.tenor for ind in portf.indices],
"index": [ind.index_type for ind in portf.indices],
"dist_on_the_run": [
on_the_run(ind.index_type, portf.value_date) - ind.series
for ind in portf.indices
],
}
)
spreads = spreads.set_index(["index", "dist_on_the_run", "tenor"])
r = []
for k, g in returns.groupby(level="date", as_index=False):
shocks = g.reset_index("date", drop=True).stack(["tenor"])
shocks.name = "shocks"
portf.spread = spreads.spread * (1 + spreads.join(shocks).shocks).values
r.append((k, portf.pnl))
pnl = pd.DataFrame.from_records(r, columns=["date", "pnl"], index=["date"])
if period == "daily":
return float(pnl.quantile(quantile))
elif period == "monthly":
return float(pnl.quantile(quantile)) * sqrt(20)
else:
raise ValueError("period needs to be either 'daily' or 'monthly'")
def insert_curve_risk(
d: datetime.date,
conn: Connection,
fund: str = "SERCGMAST",
strategies: Tuple[str] = ("SER_IGCURVE",),
):
sql_str = (
"INSERT INTO curve_risk VALUES(%s, %s, %s, %s, %s) "
"ON CONFLICT (date, strategy, fund) DO UPDATE SET "
'"VaR"=excluded."VaR", currency=excluded.currency'
)
# add a portfolio with all strategies
strategies = (*strategies, strategies)
with conn.cursor() as c:
for strat in strategies:
logger.info(f"running {strat=} for {fund=}")
portf = get_index_portfolio(
d, conn, fund, strat, exclude_redcode=("2I65BYDU6",)
)
if portf:
var = VaR(portf, period="daily")
strat_name = "*" if isinstance(strat, tuple) else strat
c.execute(sql_str, (d, strat_name, var, "USD", fund))
conn.commit()
def insert_index_risk(d: datetime.date, conn: Connection, fund: str = "SERCGMAST"):
df = pd.read_sql_query(
"SELECT * FROM list_cds_positions_by_strat(%s, %s)",
conn,
params=(d, fund),
)
to_insert = []
for t in df.itertuples(index=False):
ind = CreditIndex(
redcode=t.security_id,
maturity=t.maturity,
notional=t.notional,
value_date=d,
)
ind.mark()
to_insert.append(
(
d,
fund,
t.security_id,
t.maturity,
t.folder,
t.notional,
ind.factor,
ind.hy_equiv,
)
)
with conn.cursor() as c:
c.executemany(
"INSERT INTO index_risk VALUES (%s, %s, %s, %s, %s, %s, %s, %s) "
"ON CONFLICT (date, fund, security_id, maturity, folder) DO UPDATE "
"SET notional=EXCLUDED.notional, index_factor=EXCLUDED.index_factor, hy_equiv=EXCLUDED.hy_equiv",
to_insert,
)
|
