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
|
import os
import os.path
import datetime
from datetime import date
import csv
import pdb
import re
import sys
import common
from common import query_db
def convertToNone(s):
return None if s=="-" or s=="" or s == "NR" else s
def sanitize_float(intex_float):
intex_float = intex_float.replace(",", "")
if "(" in intex_float:
intex_float = - float(intex_float[1:-1])
else:
intex_float = float(intex_float)
return intex_float
def upload_data(workdate, conn):
sqlstr = "select dealname, max(\"Latest Update\") from clo_universe group by dealname"
deallist = dict(query_db(sqlstr, one=False))
universe = os.path.join("data", "clo_universe_intex_" + workdate + ".txt")
with open( os.path.join(common.root, universe), "r") as fh:
dr = csv.DictReader(fh, dialect='excel-tab')
data = []
c = conn.cursor()
for line in dr:
if not line ['Deal Name'] or (line['Deal Name'] == 'Unknown Security'):
continue
if not line['Latest Update']:
break
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", \
"Deal Next Pay Date", "Reinv End Date", "Latest Update"]:
if line[field]:
try:
line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date()
except ValueError:
pdb.set_trace()
for key in ["Collection Account Principal Balance", "Collection Account Interest Balance",
"Orig Deal Bal", "Curr Deal Bal", "Tranche Curr Bal", "Tranche Orig Bal",
"CDO Pct of Assets that are Structured Finance Obligations",
"CDO Defaulted Security Balance (Reported)"]:
if line[key]:
line[key] = sanitize_float(line[key])
dealname = line['Deal,Tr/CUSIP/ISIN']
line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"]
line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",")
if dealname not in deallist or line['Latest Update'] > deallist[dealname]:
sqlstring = \
"INSERT INTO clo_universe " \
"VALUES (%(Deal,Tr/CUSIP/ISIN)s, %(Deal Name)s, %(Collateral Manager)s, %(Orig Deal Bal)s," \
"%(Curr Deal Bal)s, %(Tranche Orig 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, %(Deal Issue Date)s," \
"%(Deal Termination Date)s, %(Deal Next Pay Date)s," \
"%(Reinv End Date)s, %(Latest Update)s, %(Deal CUSIP List)s, %(Paid Down)s)"
try:
c.execute(sqlstring, line)
deallist[dealname] = line['Latest Update']
except psycopg2.DataError as detail:
print(detail)
pdb.set_trace()
except KeyError as detail:
print(detail)
pdb.set_trace()
conn.commit()
if __name__ == "__main__":
if len(sys.argv) > 1:
workdate = sys.argv[1]
else:
workdate = str(datetime.date.today())
upload_data(workdate, common.conn)
common.conn.close()
print("done")
|
