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
|
import psycopg2
import os
import datetime
from datetime import date
import csv, sys, re
import pdb
from common import root, sanitize_float
from db import conn
import logging
def convertToNone(s):
return None if s in ["", "-", "NR"] else s
def upload_cusip_data(conn, filename):
dealupdate = {}
with open( filename, "r") as fh:
dr = csv.DictReader(fh, dialect='excel-tab')
data = []
deals_to_update = []
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 \"Latest Update\" FROM clo_universe " \
"WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,))
dealupdate[dealname] = c.fetchone()[0]
sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s"
with conn.cursor() as c:
c.execute(sqlstring, (line['CUSIP'],))
curr_date = c.fetchone()
conn.commit()
if not curr_date or curr_date[0] < dealupdate[dealname]:
if dealname not in deals_to_update:
deals_to_update.append(dealname)
line['updatedate'] = dealupdate[dealname]
try:
for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point',
'Curr Attachment Point (def at MV)', 'Orig Detachment Point',
'Curr Detachment Point (def at MV)', 'Factor', 'Coupon',
'Floater Spread/Margin']:
if line[key]:
line[key] = sanitize_float(line[key])
line[key] = convertToNone(line[key])
except ValueError:
continue
line['Curr Attachment Point'] = line['Curr Attachment Point (def at MV)']
line['Curr Detachment Point'] = line['Curr Detachment Point (def at MV)']
if "Curr Moody" not in line:
line['Curr Moody'] = line['Orig Moody']
sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, \"Bloomberg Ticker\", dealname, tranche, " \
"Coupon, Orig_Balance, Curr_Balance, Factor, Orig_Moody, Curr_Moody, " \
"Orig_Attach, Orig_Detach, Curr_Attach, Curr_Detach, Floater_Index, " \
"Spread, updatedate) " \
"VALUES(%(CUSIP)s, %(ISIN)s, %(Bloomberg Ticker)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \
"%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \
"%(Orig Attachment Point)s, %(Orig Detachment Point)s, "\
"%(Curr Attachment Point)s, %(Curr Detachment Point)s, " \
"%(Floater Index)s, %(Floater Spread/Margin)s, %(updatedate)s)"
try:
with conn.cursor() as c:
c.execute(sqlstring, line)
except psycopg2.DataError as e:
logging.error(e)
logging.debug("uploaded: {0}".format(line['CUSIP']))
conn.commit()
# for dealname in deals_to_update:
# with conn.cursor() as c:
# c.execute("SELECT p_cusip, p_curr_subordination, "\
# "p_curr_thickness from et_deal_subordination(%s)",
# (dealname,))
# data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in c]
# c.executemany("UPDATE cusip_universe SET subordination = %s, "
# "thickness = %s WHERE cusip = %s AND "
# "updatedate = %s", data)
# conn.commit()
def upload_deal_data(conn, filename):
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 = [d[0] for d in c]
conn.commit()
with open( filename, "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])
line["CDOpercent"] = line["CDO Pct of Assets that are Structured Finance Obligations"]
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 Issue Date", "Deal Termination Date", "Reinv End Date", \
"Latest Update", "Pay Day", "Deal First Pay Date", "Paid Down"]:
if line[field]:
try:
line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date()
except ValueError:
pdb.set_trace()
if line["Pay Day"]:
line["Pay Day"] = line["Pay Day"].day
for key in ["Collection Account Principal Balance", "Collection Account Interest Balance",
"Curr Deal Bal", "Tranche Curr Bal",
"CDO Pct of Assets that are Structured Finance Obligations",
"CDO Defaulted Security Balance (Reported)"]:
if line[key]:
line[key] = sanitize_float(line[key])
line['Deal/Tranche ID'] = line['Deal/Tranche ID'].lower()
dealname = line['Deal/Tranche ID']
line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"]
if dealname not in deallist2:
for key in ["Orig Deal Bal", "Tranche Orig Bal"]:
if line[key]:
line[key] = sanitize_float(line[key])
if line['Deal CUSIP List']:
line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",")
sqlstr = "INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, %(Deal Name)s, " \
"%(Collateral Manager)s, %(Deal Issue Date)s, %(Deal Termination Date)s, " \
"%(Pay Day)s, %(Reinv End Date)s, %(Deal First Pay Date)s, %(Orig Deal Bal)s, " \
"%(Tranche Orig Bal)s, %(Deal CUSIP List)s, %(Paid Down)s)"
try:
with conn.cursor() as c:
c.execute(sqlstr, line)
except (psycopg2.DataError, KeyError) as detail:
logging.error(detail)
pdb.set_trace()
with conn.cursor() as c:
if line['Paid Down']:
c.execute("UPDATE deal_indicative SET paid_down=%s WHERE dealname=%s",
(line['Paid Down'], 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, %(Collection Account Principal Balance)s, " \
"%(Collection Account Interest Balance)s, %(CDOpercent)s, %(defaultedbal)s, " \
"%(Coupon)s, %(Latest Update)s)"
try:
with conn.cursor() as c:
c.execute(sqlstring, line)
deallist1[dealname] = [line['Latest Update']]
except (psycopg2.DataError, KeyError) as detail:
logging.error(detail)
pdb.set_trace()
conn.commit()
if __name__=="__main__":
if len(sys.argv) > 1:
workdate = sys.argv[1]
else:
workdate = str(datetime.date.today())
files = [os.path.join(root, "data", "Indicative_" + workdate, f) for f in
os.listdir(os.path.join(root, "data", "Indicative_" + workdate))]
cusip_files = [f for f in files if "TrInfo" in f]
deal_files = [f for f in files if "TrInfo" not in f]
#first load deal data
for deal in deal_files:
upload_deal_data(conn, deal)
#then load tranche data
for cusip in cusip_files:
upload_cusip_data(conn, cusip)
conn.close()
|
