1 files changed, 101 insertions, 0 deletions
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py
new file mode 100644
index 00000000..1b1a0861
--- /dev/null
+++ b/python/load_intex_collateral.py
@@ -0,0 +1,101 @@
+import psycopg2
+import os.path
+import re
+import datetime
+import csv
+from datetime import date
+import pdb
+
+if os.name=='nt':
+    root = "//WDSENTINEL/share/CorpCDOs/"
+elif os.name=='posix':
+    root = "/home/share/CorpCDOs/"
+
+root = root + "Collaterals_2012-11-20"
+
+fields = ['Asset Name', 'Issuer', 'Current Balance', 'Maturity Date', 'Asset Subtype', \
+          'Asset Type', 'Gross Coupon', 'Spread', 'Frequency', 'Next Paydate', 'Second Lien', \
+          'LoanX ID', 'CUSIP', 'Market Price', 'Market Price Source', 'Price Date', 'Fixed or Float', \
+          'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security']
+
+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("\((.*)\)")
+
+for dealname in os.listdir(root):
+    #dealname, updatedate = line.rstrip().split()
+    # updatedate = datetime.datetime.strptime(updatedate, '%m/%d/%Y')
+    #dealname = dealname.upper() + ",AD.txt"
+    with open( os.path.join(root, dealname), "r") as fh:
+        dr = csv.DictReader(fh, dialect = 'excel-tab')
+        dealname , ending = dealname.split(',')
+        dealname = dealname.lower()
+        data = []
+        for line in dr:
+            missingfields = list(set(fields) - set(dr.fieldnames))
+            for field in missingfields:
+                line[field] = None
+            if line['Fixed or Float'] is not None:
+                line['Fixed or Float'] = line['Fixed or Float'].upper()
+            if 'LoanX ID' in dr.fieldnames and len(line['LoanX ID']) >8:
+                print "dubious id found: {0}".format(line['LoanX ID'])
+                line['LoanX ID'] = line['LoanX ID'][:8]
+            try:
+                # make sure the string is utf8 safe
+                line['Issuer']= line['Issuer'].decode('windows-1252')
+                if line['Issuer']=='' or line['Issuer'].lower() == 'dummy':
+                    break
+            except AttributeError as detail:
+                print dealname
+            #convert (x) to -x
+            for field in ['Spread', 'Gross Coupon']:
+                if line[field] and prog.match(line[field]):
+                    line[field] = - float(prog.match(line[field]).group(1))
+            if line['Market Price'] != '' and isinstance(line['Market Price'], str):
+                line['Market Price'] = float(line['Market Price'].replace(',',''))
+            if isinstance(line['Current Balance'], str):
+                line['Current Balance'] = float(line['Current Balance'].replace(',',''))
+            try:
+                r = [convertToNone(line[field]) for field in fields]
+            except KeyError as detail:
+                print detail
+                pdb.set_trace()
+            data.append(r)
+    cursor.execute( "select \"Latest Update\" from clo_universe "
+                    "where dealname='{0}'".format(dealname))
+    try:
+        updatedate = cursor.fetchone()[0]
+    except TypeError:
+        pdb.set_trace()
+    cursor.execute( "select max(updatedate) from et_collateral where dealname='{0}'".format(dealname))
+    updatedate2 = cursor.fetchone()[0]
+    if updatedate2 is None:
+        pdb.set_trace()
+    if updatedate>updatedate2:
+        for row in data:
+            # print dealname, row
+            sqlstr = "INSERT INTO ET_COLLATERAL(dealname, updatedate, name, IssuerName," \
+                "CurrentBalance, Maturity, AssetSubtype, AssetType, GrossCoupon," \
+                "Spread, Frequency, NextPaydate, SecondLien, LoanXID, Cusip, IntexPrice," \
+                "IntexPriceSource, IntexPriceDate, FixedOrFloat, DefaultedFlag, CovLite, isCDO)" \
+                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
+                "%s, %s, %s)"
+            try:
+                cursor.execute(sqlstr, (dealname, updatedate) + tuple(row))
+            except psycopg2.DataError:
+                pdb.set_trace()
+            except psycopg2.IntegrityError:
+                # crazy hack intex unique id is not really unique
+                if dealname in ["caval1", "octag11", "windriv2"]:
+                    conn.rollback()
+                    row[0] = "Affinion Group - Tranche B Term Loan - 18"
+                    cursor.execute(sqlstr, (dealname, updatedate) + tuple(row))
+                else:
+                    pdb.set_trace()
+            conn.commit()
+
+cursor.close()
+conn.close()