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
|
import logging
from serenitas.analytics import Portfolio, BlackSwaption, DataError
from psycopg2 import sql
logger = logging.getLogger(__name__)
def get_swaption_portfolio(date, conn, fund="SERCGMAST", portfolio=None, **kwargs):
if portfolio is None:
params = (date, date, date, fund)
and_clause = ""
else:
params = (date, date, date, fund, portfolio)
and_clause = "AND portfolio=%s"
with conn.cursor() as c:
c.execute(
"SELECT swaptions.id, folder, dealid, (notional - terminated_amount) AS notional "
"FROM swaptions LEFT JOIN ("
"SELECT dealid, SUM(termination_amount) AS terminated_amount "
"FROM terminations WHERE termination_date <= %s GROUP BY dealid) b "
"USING (dealid) "
"WHERE notional IS DISTINCT FROM terminated_amount "
"AND expiration_date > %s AND trade_date <= %s "
"AND swap_type='CD_INDEX_OPTION' "
f"AND fund=%s {and_clause} "
"ORDER BY expiration_date, strike",
params,
)
try:
trade_ids, folders, deal_ids, notionals = zip(*c)
except ValueError:
return {}
portf = Portfolio(
[BlackSwaption.from_tradeid(t) for t in trade_ids], list(zip(folders, deal_ids))
)
for t, ntl in zip(portf.trades, notionals):
if ntl is not None:
t.notional = ntl
portf.value_date = date
portf.mark(interp_method="bivariate_linear", **kwargs)
return portf
def insert_swaption_portfolio(portf, conn, overwrite=True):
columns = ["market_value", "delta", "gamma", "vega", "theta"]
place_holders = sql.SQL(", ").join([sql.Placeholder()] * 7)
if overwrite:
update_str = sql.SQL("DO UPDATE SET {}").format(
sql.SQL(", ").join(
sql.SQL("{} = excluded.{}").format(
sql.Identifier(col), sql.Identifier(col)
)
for col in columns
)
)
else:
update_str = sql.SQL("DO NOTHING")
sql_str = sql.SQL(
"INSERT INTO swaption_marks VALUES({}) " "ON CONFLICT (dealid, date) {} "
).format(place_holders, update_str)
with conn.cursor() as c:
for id, trade in portf.items():
to_insert = (
id[1],
trade.value_date,
trade.pv,
trade.delta,
trade.gamma,
trade.vega,
trade.theta,
)
try:
c.execute(sql_str, to_insert)
except DataError as e:
logger.error(e)
finally:
logger.info("succesfully marked trade id: %s", id)
conn.commit()
|
