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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
|
import psycopg2
import datetime
import csv
import pdb
import pathlib
from .common import sanitize_float
import logging
import re
logger = logging.getLogger(__name__)
def convertToNone(s):
return None if s in ["", "-", "NR"] else s
def insert_new_tranche(conn, line):
if line["Pari-Passu Tranches"]:
line["Pari-Passu Tranches"] = line["Pari-Passu Tranches"].split(",")
to_insert = (
line["CUSIP"],
line["ISIN"],
line["FIGI"],
line["Bloomberg Ticker"],
line["dealname"],
line["tranche"],
line["Pari-Passu Tranches"],
)
for key in (
"Orig Balance",
"Orig Attachment Point",
"Orig Detachment Point",
"Floater Spread/Margin",
"Coupon",
):
if key in line:
if line[key]:
line[key] = sanitize_float(line[key])
line[key] = convertToNone(line[key])
to_insert += (
line["Orig Balance"],
line.get("Orig Moody"),
line["Orig Attachment Point"],
line["Orig Detachment Point"],
line.get("Floater Index"),
line.get("Floater Spread/Margin", line["Coupon"]),
line["Type"],
)
sqlstr = (
"INSERT INTO tranche_ref(Cusip, ISIN, figi, bloomberg_ticker, dealname, "
"tranche, paripassu_tranches, Orig_Balance, Orig_Moody, Orig_Attach, "
"Orig_Detach, Floater_Index, Spread, type) VALUES({0}) "
"ON CONFLICT (cusip, dealname) DO UPDATE SET figi=EXCLUDED.figi "
"RETURNING id".format(",".join(["%s"] * 14))
)
with conn.cursor() as c:
try:
c.execute(sqlstr, to_insert)
(tranche_id,) = c.fetchone()
return tranche_id
except psycopg2.DataError as e:
logger.error(e)
conn.commit()
def upload_tranche_data(conn, filename: pathlib.Path):
dealupdate = {}
with filename.open("r") as fh:
dr = csv.DictReader(fh, dialect="excel-tab")
for line in dr:
if "ISIN" not in line:
line["ISIN"] = None
sp = line["Tranche"].split(",")
if len(sp) == 2:
line["dealname"], line["tranche"] = sp
else:
continue
line["dealname"] = line["dealname"].lower()
dealname = line["dealname"]
line = {k: convertToNone(v) for k, v in line.items()}
if dealname not in dealupdate:
with conn.cursor() as c:
c.execute(
'SELECT max("Latest Update") FROM clo_universe '
"WHERE dealname = %s",
(dealname,),
)
try:
(dealupdate[dealname],) = c.fetchone()
except TypeError:
logging.error(f"deal:{dealname} not in database")
continue
sqlstring = "SELECT id FROM tranche_ref WHERE figi=%s and dealname=%s"
with conn.cursor() as c:
c.execute(sqlstring, (line["FIGI"], dealname))
r = c.fetchone()
if r is None:
try:
tranche_id = insert_new_tranche(conn, line)
except ValueError:
continue
else:
tranche_id = r[0]
sqlstring = "SELECT max(updatedate) FROM tranche_update WHERE id = %s"
with conn.cursor() as c:
c.execute(sqlstring, (tranche_id,))
(curr_date,) = c.fetchone()
if curr_date is None or curr_date < dealupdate[dealname]:
try:
for key in [
"Curr Balance",
"Curr Attachment Point (def at MV)",
"Curr Detachment Point (def at MV)",
"Factor",
"Coupon",
]:
if line[key]:
line[key] = sanitize_float(line[key])
line[key] = convertToNone(line[key])
except ValueError:
continue
line["Curr Moody"] = line.get("Curr Moody") or line.get("Orig Moody")
sqlstring = "INSERT INTO tranche_update VALUES({0})".format(
",".join(["%s"] * 8)
)
to_insert = (
tranche_id,
line["Curr Balance"],
line["Factor"],
line["Coupon"],
line["Curr Moody"],
line["Curr Attachment Point (def at MV)"],
line["Curr Detachment Point (def at MV)"],
dealupdate[dealname],
)
with conn.cursor() as c:
try:
c.execute(sqlstring, to_insert)
except (psycopg2.DataError, psycopg2.IntegrityError) as e:
logger.error(e)
logger.debug("uploaded: {0}".format(line["FIGI"]))
conn.commit()
def upload_deal_data(conn, filename: pathlib.Path):
sqlstr = 'SELECT dealname, array_agg("Latest Update") FROM clo_universe GROUP BY dealname'
with conn.cursor() as c:
c.execute(sqlstr)
deallist1 = dict(c)
sqlstr = "SELECT dealname FROM deal_indicative"
with conn.cursor() as c:
c.execute(sqlstr)
deallist2 = set([d for d, in c])
conn.commit()
with filename.open("r") as fh:
dr = csv.DictReader(fh, dialect="excel-tab")
data = []
for line in dr:
if not line["Deal Name, Tranche Name"] or (
line["Deal Name, Tranche Name"] == "Unknown Security"
):
continue
if not line["Latest Update"]:
continue
for key in line.keys():
line[key] = convertToNone(line[key])
##simpler names
line["CDOpercent"] = line[
"CDO Pct of Assets that are Structured Finance Obligations"
]
line["defaultedbal"] = line["CDO Defaulted Security Balance (Reported)"]
line["Paid Down"] = None
if "Paid Down" in line["Latest Update"]:
line["Paid Down"] = re.sub("Paid Down: ", "", line["Latest Update"])
line["Latest Update"] = line["Paid Down"]
for field in [
"Deal Closing Date",
"Deal Termination Date",
"Reinv End Date",
"Latest Update",
"Pay Day",
"Deal First Pay Date",
"Paid Down",
"Deal Last Refi Date",
]:
if line[field]:
try:
line[field] = datetime.datetime.strptime(
line[field], "%b %d, %Y"
).date()
except ValueError:
logger.error("Can't parse date {}".format(line[field]))
pdb.set_trace()
if line["Pay Day"]:
line["Pay Day"] = line["Pay Day"].day
if "Principal Collection Account (Bal)" in line:
line["Principal Collection Account"] = line[
"Principal Collection Account (Bal)"
]
del line["Principal Collection Account (Bal)"]
for key in [
"Principal Collection Account",
"Interest Collection Account",
"Curr Deal Bal",
"Tranche Curr Bal",
"CDOpercent",
"defaultedbal",
"Orig Deal Bal",
"Tranche Orig Bal",
]:
if line[key]:
line[key] = sanitize_float(line[key])
line["Deal/Tranche ID"] = line["Deal/Tranche ID"].lower()
dealname = line["Deal/Tranche ID"]
if dealname not in deallist2:
sqlstr = (
"INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, "
"%(Deal Name)s, %(Collateral Manager)s, "
"%(Deal Closing Date)s, %(Pay Day)s, "
"%(Deal First Pay Date)s, %(Paid Down)s)"
)
else:
# we always update paid_down
sqlstr = (
"UPDATE deal_indicative SET paid_down=%(Paid Down)s "
"WHERE dealname=%(Deal/Tranche ID)s"
)
if line["Deal CUSIP List"]:
line["Deal CUSIP List"] = line["Deal CUSIP List"].split(",")
try:
with conn.cursor() as c:
c.execute(sqlstr, line)
except (psycopg2.DataError, KeyError) as detail:
logger.error(detail)
continue
finally:
# update deallist2
if dealname not in deallist2:
deallist2.add(dealname)
if (
dealname not in deallist1
or line["Latest Update"] not in deallist1[dealname]
):
sqlstring = (
"INSERT INTO clo_universe "
"VALUES (%(Deal/Tranche ID)s, %(Curr Deal Bal)s, %(Tranche Curr Bal)s, "
"%(Tranche Factor)s, %(Principal Collection Account)s, "
"%(Interest Collection Account)s, %(CDOpercent)s, %(defaultedbal)s, "
"%(Coupon)s, %(Latest Update)s, %(Deal Last Refi Date)s, "
"%(Deal CUSIP List)s, %(Deal Termination Date)s, %(Reinv End Date)s, "
"%(Orig Deal Bal)s, %(Tranche Orig Bal)s)"
)
try:
with conn.cursor() as c:
c.execute(sqlstring, line)
deallist1[dealname] = [line["Latest Update"]]
except (psycopg2.DataError, KeyError) as detail:
logger.error(detail)
pdb.set_trace()
conn.commit()
if __name__ == "__main__":
from serenitas.utils.db import dbconn
from serenitas.utils.env import DATA_DIR
from contextlib import closing
import argparse
parser = argparse.ArgumentParser()
parser.add_argument("workdate", nargs="?", default=str(datetime.date.today()))
args = parser.parse_args()
tranche_files = [
f for f in (DATA_DIR / "Indicative_").iterdir() if "TrInfo" in f.name
]
deal_files = [
f for f in (DATA_DIR / "Indicative_").iterdir() if "TrInfo" not in f.name
]
with closing(dbconn("etdb")) as etdb:
# first load deal data
for deal in deal_files:
upload_deal_data(etdb, deal)
# then load tranche data
for tranche in tranche_files:
upload_tranche_data(etdb, cusip)
|
