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
|
import argparse
import logging
import os
import pandas as pd
from pandas.tseries.offsets import BDay
from analytics import Portfolio, BlackSwaption
from db import dbconn, DataError
logging.basicConfig(filename=os.path.join(os.getenv("LOG_DIR"),
'swaption_marks.log'),
level=logging.INFO,
format='%(asctime)s %(message)s')
def get_swaption_portfolio(date, conn):
with conn.cursor() as c:
c.execute("SELECT id, dealid FROM swaptions "
"WHERE (termination_date is NULL or termination_date > %s) "
"AND expiration_date > %s AND trade_date <= %s "
"AND swap_type='CD_INDEX_OPTION'", (date, date, date))
try:
trade_ids, deal_ids = zip(*c)
except ValueError:
return {}
portf = Portfolio([BlackSwaption.from_tradeid(t) for t in trade_ids],
trade_ids=deal_ids)
portf.value_date = date
for t in portf.trades:
try:
t.mark(interp_method='bivariate_spline')
except Exception as e:
logging.info(e)
t.mark(interp_method='bivariate_linear')
return portf
def insert_swaption_portfolio(portf, conn):
sql_str = "INSERT INTO swaption_marks VALUES(%s, %s, %s, %s, %s, %s, %s)"
with conn.cursor() as c:
for id, trade in portf.items():
to_insert = (id, trade.value_date, trade.pv, trade.delta,
trade.gamma, trade.vega, trade.theta)
try:
c.execute(sql_str, to_insert)
except DataError as e:
logging.error(e)
finally:
logging.info("succesfully marked trade id: %s", id)
conn.commit()
# def get_trades(date, engine):
# return pd.read_sql_query(
# "SELECT dealid, buysell, swaption_type, notional, strike, " \
# "expiration_date, index, series FROM swaptions " \
# "JOIN index_version ON (swaptions.security_id = index_version.redindexcode) " \
# "WHERE termination_date is NULL AND expiration_date > %s AND trade_date <= %s",
# engine, 'dealid', params=(date, date), parse_dates=['expiration_date'])
# def get_swaptionvol(index, series, strike, expiry, date, engine):
# r = engine.execute(
# "SELECT ref, vol_payer AS payer, vol_receiver AS receiver, " \
# "vol_payer_black AS payer_black, vol_receiver_black AS receiver_black " \
# "FROM swaption_calib " \
# "LEFT JOIN swaption_quotes USING (quote_id) " \
# "LEFT JOIN swaption_ref_quotes USING (ref_id) "\
# "WHERE index = %s " \
# "AND series = %s AND strike=%s AND expiry = %s AND quotedate::date=%s " \
# "ORDER BY quotedate desc",
# (index, series, strike, expiry, date))
# try:
# result = r.fetchone()
# if result is None:
# logging.error("No data for {}{} {}K {} expiry on date {}".
# format(index, series, strike, expiry, date))
# return result
# except exc.DataError as e:
# logging.error(e)
# except e:
# logging.error(e)
# def mark_trades(date, engine):
# df = get_trades(date, engine)
# result = []
# for trade in df.itertuples():
# try:
# r = engine.execute("SELECT closespread, closeprice FROM index_quotes " \
# "WHERE index=%s AND series=%s AND tenor='5yr' AND date=%s",
# (trade.index, trade.series, date))
# closespread, closeprice = r.fetchone()
# except exc.DataError as e:
# logging.eror(e)
# except TypeError:
# logging.error("no quote for {}{} {} on date {}".format(
# trade.index, trade.series, '5yr', date))
# underlying = Index.from_name(trade.index, trade.series, '5yr', date,
# trade.notional)
# quote = get_swaptionvol(trade.index, trade.series, trade.strike, trade.expiration_date.date(),
# date, serenitas_engine)
# if quote is None:
# continue
# underlying.ref = quote.ref
# swaption = Swaption(underlying, trade.expiration_date.date(),
# trade.strike, trade.swaption_type.lower())
# swaption.sigma = getattr(quote, swaption.option_type)
# if swaption.sigma is None:
# swaption.sigma = quote.receiver if swaption.option_type == "payer" else quote.payer
# direction = 1. if trade.buysell else -1.
# market_value = swaption.pv * trade.notional * direction
# if trade.index in ["HY", "XO"]:
# market_value += (quote.ref - closeprice) / 100 * swaption.delta * trade.notional * direction
# else:
# market_value += swaption.DV01 * (closespread - quote.ref) * trade.notional * direction
# #compute the greeks at index mark
# swaption.index.spread = closespread
# swaption._update()
# try:
# to_insert = (trade.Index, date, market_value, swaption.delta, swaption.gamma,
# swaption.vega)
# engine.execute("INSERT INTO swaption_marks VALUES(%s, %s, %s, %s, %s, %s)",
# to_insert)
# except exc.DataError as e:
# logging.error(e)
# finally:
# logging.info("succesfully marked trade id: %s", trade.Index)
# return result
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument('workdate', nargs='?',
type=lambda s: pd.datetime.strptime(s, "%Y-%m-%d").date()),
args = parser.parse_args()
if args.workdate is None:
workdate = (pd.Timestamp.today()-BDay()).date()
else:
workdate = args.workdate
with dbconn('dawndb') as conn:
portf = get_swaption_portfolio(workdate, conn)
insert_swaption_portfolio(portf, conn)
|
