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 csv
import datetime
import io
import pytz
import requests
from db import dbconn
params = {"username": "serenitasreports",
"password": "_m@rk1t_",
"reportUri": "/MarkitQuotes/CLIENTS/AllTrancheQuotes",
"exportType": "csv",
"START_TIME": "11/13/2017"}
r = requests.get("https://quotes.markit.com/reports/runReport",
params=params)
f = io.StringIO(r.text)
with open("quotes.csv", "w") as fh:
fh.write(r.text)
index_mapping = {'ITRAXX-Europe': 'EU',
'ITRAXX-Xover': 'XO',
'CDX-NAIG': 'IG',
'CDX-NAHY': 'HY'}
sql_str = f"""INSERT INTO tranche_quotes(quotedate, index, series, version, tenor, attach, detach,
trancheupfrontbid, trancheupfrontmid, trancheupfrontask,
trancherunningbid, trancherunningmid, trancherunningask,
indexrefprice, indexrefspread, tranchedelta, quotesource, markit_id)
VALUES({",".join(["%s"]*18)}) ON CONFLICT DO NOTHING"""
def get_latest_quote_id(db):
with db.cursor() as c:
c.execute("SELECT max(markit_id) FROM tranche_quotes")
markit_id, = c.fetchone()
return markit_id
def convert_float(s):
return float(s) if s else None
serenitasdb = dbconn('serenitasdb')
runningdict1 = {0: 500, 3:100, 7:100, 15: 25}
runningdict2 = {0: 500, 3:500, 7:500, 10:100, 15:100, 30:100}
markit_id = get_latest_quote_id(serenitasdb)
headers = [h.lower() for h in next(f).split(",")]
count = 0
for d in csv.DictReader(f, fieldnames=headers):
d['quotedate'] = datetime.datetime.strptime(d['time'], "%m/%d/%Y %H:%M:%S")
d['quotedate'] = d['quotedate'].replace(tzinfo=pytz.UTC)
d['index'] = index_mapping[d['ticker']]
d['tenor'] = d['tenor'] + 'yr'
for k1 in ['upfront', 'spread', 'price']:
for k2 in ['_bid', '_ask']:
d[k1 + k2] = convert_float(d[k1 + k2])
for k in ['upfront', 'spread', 'price']:
d[k+'_mid'] = 0.5 * (d[k+'_bid'] + d[k+'_ask']) \
if d[k + '_bid'] and d[k + '_ask'] else None
d['series'] = int(d['series'])
d['attachment'], d['detachment'] = int(d['attachment']), int(d['detachment'])
if d['ticker'] == 'CDX-NAHY':
d['indexrefprice'] = convert_float(d['reference'])
else:
d['indexrefspread'] = convert_float(d['reference'])
if d['ticker'] == 'CDX-NAHY':
for k in ['_bid', '_mid', '_ask']:
d['upfront' + k] = d['price' + k]
d['spread' + k] = 0 if d['series'] in [9, 10] and d['attachment'] == 10 else 500
if d['ticker'] == 'ITRAXX-Xover':
if int(d['attachment']) < 35:
for k in ['_bid', '_mid', '_ask']:
##d[f'upfront{k}'] = d[f'spread{k}']
d['spread' + k] = 500
if d['ticker'] == 'ITRAXX-Europe':
if d['attachment'] <= 3:
for k in ['_bid', '_mid', '_ask']:
#d[f'upfront{k}'] = d[f'spread{k}']
d['spread' + k] = 500 if d['series'] == 19 else 100
if d['ticker'] == 'CDX-NAIG':
for k in ['Bid', 'Mid', 'Ask']:
#d[f'upfront{k}'] = d[f'spread{k}']
if d['series'] < 19:
running = runningdict2[d['attachment']]
elif d['series'] < 25:
running = runningdict1[d['attachment']]
else:
running = 100
for k in ['_bid', '_mid', '_ask']:
d['spread' + k] = running
d['delta'] = convert_float(d['delta'])
with serenitasdb.cursor() as c:
c.execute(sql_str, (d['quotedate'], d['index'], d['series'], d['version'], d['tenor'],
d['attachment'], d['detachment'],
d['upfront_bid'], d.get('upfront_mid'), d['upfront_ask'],
d['spread_bid'], d.get('spread_mid'), d['spread_ask'],
d.get('indexrefprice'), d.get('indexrefspread'), d['delta'],
d['contributor'], d['quote_id']))
count +=1
serenitasdb.commit()
print(f"loaded {count} new quotes")
|
