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
|
import psycopg2
import os
import os.path
import datetime
from datetime import date
import csv
import pdb
import re
if os.name=='nt':
root = "//WDSENTINEL/share/CorpCDOs/"
elif os.name=='posix':
root = "/home/share/CorpCDOs/"
universe = "data/clo_universe_intex_2012-11-21.txt"
def convertToNone(s):
return None if s=="-" or s=="" else s
conn = psycopg2.connect(database="ET",
user="et_user",
password="Serenitas1",
host="192.168.1.108")
cursor = conn.cursor()
cursor.execute("select dealname, max(\"Latest Update\") from clo_universe group by dealname")
deallist = dict(cursor.fetchall())
with open( os.path.join(root, universe), "r") as fh:
dr = csv.DictReader(fh, dialect='excel-tab')
data = []
for line in dr:
if line['Deal Name'] == 'Unknown Security':
continue
if line['Latest Update']=='' or line['Latest Update'] is None:
break
for key in line.keys():
line[key] = convertToNone(line[key])
line["CDOpercent"] = line["CDO Pct of Assets that are Structured Finance Obligations"]
if line["CDOpercent"] == "NR":
line["CDOpercent"] = None
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] is not None:
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] = line[key].replace(",", "")
dealname = line['Deal,Tr/CUSIP/ISIN']
line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"]
if dealname not in deallist or line['Latest Update'] > deallist[dealname]:
sqlstring = \
"INSERT INTO clo_universe(dealname, \"Deal Name\", Manager, \"Orig Deal Bal\"," \
"\"Curr Deal Bal\", \"Orig Collat Bal\", \"Curr Collat Bal\", \"Tranche Factor\"," \
"\"Principal Bal\", \"Interest Bal\",\"CDO Percentage\", \"Defaulted Bal\", \"Curr Coupon\"," \
"\"Deal Issue Date\", Maturity, \"Deal Next Pay Date\", \"Reinv End Date\"," \
"\"Latest Update\", \"Deal Cusip List\", PaidDown)" \
"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, " \
"%(Curr 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:
cursor.execute(sqlstring, line)
except psycopg2.DataError:
pdb.set_trace()
conn.commit()
# cursor.execute("select dealname,\"Deal Cusip List\" from clo_universe");
# cursor2 = conn.cursor()
# for record in cursor:
# for cusip in record[1].split(","):
# tuple = (record[0], cusip)
# cursor2.execute("INSERT INTO dealcusipmapping(dealname, cusip) VALUES(%s, %s)", tuple)
# cursor2.close()
# conn.commit()
cursor.close()
conn.close()
|
