1 files changed, 59 insertions, 0 deletions
diff --git a/python/load_markit_data.py b/python/load_markit_data.py
new file mode 100644
index 00000000..a58341ac
--- /dev/null
+++ b/python/load_markit_data.py
@@ -0,0 +1,59 @@
+import psycopg2
+import os
+import os.path
+import datetime
+from datetime import date
+import pdb
+import csv
+
+if os.name =='nt':
+    root = "//WDsentinel/share/CorpCDOs/data/markit"
+elif os.name == 'posix':
+    root = '/home/share/CorpCDOS/data/markit'
+
+def convertToNone(s):
+    return None if (s=='' or s=='NA') else s
+
+conn = psycopg2.connect(database="ET",
+                        user="et_user",
+                        password="Serenitas1",
+                        host="192.168.1.108")
+cursor = conn.cursor()
+
+fields = ["LoanX ID", "Issuer", "Deal Name", "Facility", "Industry", \
+          "Current SP Rating", "Current Moodys Rating", "Initial Amount",\
+          "Final Maturity", "Initial Spread", "Bid", "Offer", "Depth", \
+          "Spread To Maturity"]
+
+latestdate = cursor.execute("SELECT MAX(pricingdate) from markit_prices")
+latestdate = cursor.fetchone()[0]
+
+for filename in os.listdir(root):
+    date = datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
+    # only update the new data
+    if latestdate is None or date > latestdate:
+        with open( os.path.join(root, filename) ) as fh:
+            dr = csv.DictReader(fh)
+            data = []
+            for line in dr:
+                try:
+                    line['Final Maturity'] = \
+                        datetime.datetime.strptime(line['Final Maturity'], '%d-%b-%y')
+                except ValueError:
+                    if line['Final Maturity'] !='':
+                        print 'Maturity non empty'
+                if line['Depth'] == 'implied':
+                    line['Depth']=0
+                try:
+                    temp = [convertToNone(line[key]) for key in fields]
+                except KeyError:
+                    pdb.set_trace()
+                data.append(temp)
+
+        for row in data:
+            cursor.execute("INSERT INTO markit_prices(LoanXID, Issuer, DealName,"
+                           "Facility, Industry, SP, Moodys, Amount, Maturity,"
+                           "Spread, Bid, Offer, Depth, STM, PricingDate)"
+                           "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                           tuple(row) + (date,))
+        conn.commit()