1 files changed, 74 insertions, 0 deletions
diff --git a/python/cusip_universe.py b/python/cusip_universe.py
new file mode 100644
index 00000000..d88a44e0
--- /dev/null
+++ b/python/cusip_universe.py
@@ -0,0 +1,74 @@
+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/"
+
+def convertToNone(s):
+    return None if s=="" else s
+
+conn = psycopg2.connect(database="ET",
+                        user="et_user",
+                        password="Serenitas1",
+                        host="192.168.1.108")
+cursor = conn.cursor()
+
+prog = re.compile("\((.*)\)")
+
+cursor.execute("delete from cusip_universe")
+count = 0
+for i in xrange(9):
+    cusip_universe_file = "TrInfo{0}.txt".format(i+1)
+    with open( os.path.join(root, "data", cusip_universe_file), "r") as fh:
+        dr = csv.DictReader(fh, dialect='excel-tab')
+        data = []
+        for line in dr:
+            count += 1
+            if "," in line['Tranche']:
+                line["dealname"], line["tranche"] = line["Tranche"].split(",")
+            else:
+                continue
+            line["dealname"] = line["dealname"].lower()
+            for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point',
+                        'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']:
+                line[key] = line[key].replace(",", "")
+                if prog.match(line[key]):
+                    try:
+                        line[key] = float(prog.match(line[key]).group(1))
+                    except ValueError:
+                        pdb.set_trace()
+                line[key] = convertToNone(line[key])
+            if "," in str(line['Floater Spread']):
+                line['Floater Spread'] = line['Floater Spread'].split(",")[0]
+            for key in ['Floater Spread', 'Floater Index']:
+                if prog.match(line[key]):
+                    try:
+                        line[key] = float(prog.match(line[key]).group(1))
+                    except ValueError:
+                        pdb.set_trace()
+                line[key] = convertToNone(line[key])
+            sqlstring = "INSERT INTO cusip_universe(cusip, ISIN, dealname, tranche," \
+                "coupon, Orig_Balance, Curr_Balance, Factor, Orig_moody, Curr_moody, " \
+                "Orig_attach, Orig_detach, Curr_attach, Curr_detach, Floater_Index," \
+                "Spread) " \
+                "VALUES (%(CUSIP)s, %(ISIN)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)s)"
+            try:
+                cursor.execute(sqlstring, line)
+            except psycopg2.DataError as error:
+                pdb.set_trace()
+        print count
+    conn.commit()
+
+cursor.close()
+conn.close()