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
|
from exchange import get_msgs
from pytz import timezone
from parse_emails import write_todb
import datetime
import logging
import pandas as pd
import re
class ParseError(Exception):
pass
def parse_email(email, fwd_index):
m = re.search("(IG|HY)(\d{2}) 5y (?:.*)SWAPTION (?:UPDATE|CLOSES|CLOSE) - Ref\D+(.+)$",
email.subject)
if m:
indextype, series, ref = m.groups()
series = int(series)
if indextype == 'HY':
refprice, refspread = map(float,
re.match(r"([\S]+)\s+\(([^)]+)\)", ref).
groups())
else:
refspread = float(ref)
else:
raise ParseError(f"can't parse subject line: {email.subject}")
quotedate = datetime.datetime.fromtimestamp(email.datetime_sent.timestamp(),
timezone('America/New_York'))
flag = False
masterdf = {}
for line in email.body.split("\r\n"):
if line.startswith("Expiry"):
m = re.match("Expiry (\d{2}\w{3}\d{2}) \((?:([\S]+) )?([\S]+)\)", line)
if m:
date, fwprice, fwspread = m.groups()
date = pd.to_datetime(date, format='%d%b%y')
continue
if line.startswith("Stk"):
flag = True
r = []
continue
if flag:
if line:
vals = re.sub(" +", " ", line).split(" ")
if indextype == 'HY':
vals.pop(2)
vals.pop(9)
else:
vals.pop(1)
vals.pop(8)
r.append(vals)
continue
else:
if indextype == 'HY':
cols = ['Strike', 'Sprd', 'Pay', 'DeltaPay', 'Rec', 'Vol',
'VolChg', 'VolBpd', 'Tail']
else:
cols = ['Strike', 'Pay', 'DeltaPay', 'Rec', 'Vol',
'VolChg', 'VolBpd', 'Tail']
df = pd.DataFrame.from_records(r, columns=cols)
df[['PayBid', 'PayOffer']] = df.Pay.str.split('/', expand=True)
df[['RecBid', 'RecOffer']] = df.Rec.str.split('/', expand=True)
df.drop(['Pay', 'Rec'], axis=1, inplace=True)
for col in df:
df[col] = pd.to_numeric(df[col], errors='coerce')
df.set_index('Strike', inplace=True)
d = {'quotedate': quotedate,
'expiry': date,
'index': indextype,
'series': series,
'ref': refspread if indextype == "IG" else refprice}
if indextype == "IG":
d['fwdspread'] = float(fwspread)
else:
d['fwdprice'] = float(fwprice)
fwd_index.append(d)
masterdf[date] = df
flag = False
r = []
continue
return quotedate, indextype, series, pd.concat(masterdf, names=['expiry'])
def clean_df(all_df):
all_df = pd.concat(all_df, names=['quotedate', 'index', 'series'], sort=True)
all_df['DeltaPay'] = - all_df['DeltaPay']/100
all_df['Vol'] /= 100
all_df.reset_index(inplace=True)
all_df = all_df.rename(columns={'Strike':'strike',
'Vol': 'vol',
'PayOffer': 'pay_offer',
'PayBid': 'pay_bid',
'RecOffer': 'rec_offer',
'RecBid': 'rec_bid',
'Tail': 'tail',
'DeltaPay': 'delta_pay'})
del all_df['VolBpd'], all_df['VolChg']
if 'Sprd' in all_df:
del all_df['Sprd']
return all_df
if __name__ == "__main__":
fwd_index = []
swaption_quotes = {}
for email in get_msgs(count=20):
try:
quotedate, indextype, series, df = parse_email(email, fwd_index)
except ParseError as e:
logging.exception(e)
swaption_quotes[(quotedate, indextype, series)] = df
index_df = pd.DataFrame(fwd_index)
index_df = index_df.drop_duplicates(['quotedate', 'index', 'series', 'expiry'])
index_df['quote_source'] = 'GS'
swaption_quotes = clean_df(swaption_quotes)
swaption_quotes = swaption_quotes.set_index(['quotedate', 'index', 'series', 'expiry'])
write_todb(swaption_quotes, index_df)
|
