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
|
import csv
import datetime
import io
import pytz
import requests
from db import dbconn
index_mapping = {'ITRAXX-Europe': 'EU',
'ITRAXX-Xover': 'XO',
'CDX-NAIG': 'IG',
'CDX-NAHY': 'HY'}
sql_str = f"""INSERT INTO tranche_quotes_csv(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 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}
runningdict3 = {0: 500, 3: 500, 6: 300}
f = open("/home/serenitas/CorpCDOs/data/GetTrancheQuotesByTime.csv")
headers = [h.lower() for h in next(f).strip().split(",")]
l = []
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)
try:
d['index'] = index_mapping[d['ticker']]
except KeyError:
continue
if d['tenor'] in ('15', '157', '25', '30', '1994'):
continue
d['tenor'] = d['tenor'] + 'yr'
for k1 in ['upfront', 'spread']:
for k2 in ['bid', 'ask']:
d[k1 + k2] = convert_float(d[k1 + k2])
for k in ['upfront', 'spread']:
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['spread' + 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['series'] in [4, 6, 7, 8, 9, 15]:
if int(d['attachment']) <= 6 :
for k in ['bid', 'mid', 'ask']:
d[f'upfront{k}'] = d[f'spread{k}']
d['spread'+k] = runningdict3[int(d['attachment'])]
else:
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:
try:
running = runningdict2[d['attachment']]
except KeyError:
continue
elif d['series'] < 25:
try:
running = runningdict1[d['attachment']]
except KeyError:
continue
else:
running = 100
for k in ['bid', 'mid', 'ask']:
d['spread' + k] = running
d['delta'] = convert_float(d['delta'])
l.append(d)
f.close()
count = 0
with serenitasdb.cursor() as c:
for d in l:
if d['contributor'] == 'BROWNSTONE':
d['contributor'] = 'BIG'
if d['version'] == '' and d['ticker'] in ('ITRAXX-Europe', 'CDX-NAIG'):
d['version'] = 1
elif d['version'] == '':
d['version'] = 0
d['contributor'] = d['contributor'][:4]
c.execute(sql_str, (d['quotedate'], d['index'], d['series'], d['version'], d['tenor'],
d['attachment'], d['detachment'],
d['upfrontbid'], d.get('upfrontmid'), d['upfrontask'],
d['spreadbid'], d.get('spreadmid'), d['spreadask'],
d.get('indexrefprice'), d.get('indexrefspread'), d['delta'],
d['contributor'], d['quoteid']))
count += 1
serenitasdb.commit()
f.close()
print(f"loaded {count} new quotes")
|
