1 files changed, 92 insertions, 0 deletions
diff --git a/python/clo_universe.py b/python/clo_universe.py
new file mode 100644
index 00000000..ae3f0988
--- /dev/null
+++ b/python/clo_universe.py
@@ -0,0 +1,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()