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
|
import argparse
import datetime
import logging
import os
import pandas as pd
from pandas.tseries.offsets import BDay
from analytics import Index, Swaption
from db import dbengine
from sqlalchemy import exc
logging.basicConfig(filename=os.path.join(os.getenv("LOG_DIR"), 'swaption_marks.log'),
level=logging.INFO,
format='%(asctime)s %(message)s')
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_ref_quotes " \
"USING (quotedate, expiry, index, series) 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)
sigma = get_swaptionvol(trade.index, trade.series, trade.strike, trade.expiration_date.date(),
date, serenitas_engine)
if sigma is None:
continue
if trade.index == "IG":
underlying.spread = sigma.ref
elif trade.index == "HY":
underlying.price = sigma.ref
swaption = Swaption(underlying, trade.expiration_date.date(),
trade.strike, trade.swaption_type.lower())
swaption.sigma = getattr(sigma, swaption.option_type)
direction = 1. if trade.buysell else -1.
market_value = swaption.pv * trade.notional * direction
market_value += swaption.DV01 * (closespread-sigma.ref) * trade.notional * direction
#compute the greeks at index mark
swaption.index.spread = closespread
swaption._update()
try:
test = (trade.Index, date, market_value, swaption.delta, swaption.gamma,
swaption.vega)
engine.execute("INSERT INTO swaption_marks VALUES(%s, %s, %s, %s, %s, %s)",
(trade.Index, date, market_value, swaption.delta, swaption.gamma,
swaption.vega))
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()),
dawn_engine = dbengine('dawndb')
serenitas_engine = dbengine('serenitasdb')
args = parser.parse_args()
if args.workdate is None:
workdate = (pd.Timestamp.today()-BDay()).date()
else:
workdate = args.workdate
r = mark_trades(workdate, dawn_engine)
|
