1 files changed, 96 insertions, 0 deletions
diff --git a/python/markit_loans.py b/python/markit_loans.py
new file mode 100644
index 00000000..b61a680f
--- /dev/null
+++ b/python/markit_loans.py
@@ -0,0 +1,96 @@
+import requests
+from common import root
+from db import conn, with_connection
+import os
+import datetime
+import csv
+import sys
+
+def convertToNone(v):
+    return v if v else None
+
+@with_connection
+def download_facility(conn, workdate, payload):
+    r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv', params=payload)
+    facility_filename = os.path.join(root, "data", "Facility files", "facility_{0}.csv".format(workdate))
+    with open( facility_filename, "wb") as fh:
+        fh.write(r.content)
+
+    sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
+    with open( facility_filename, "r") as fh:
+        reader = csv.reader(fh)
+        next(reader)
+        with conn.cursor() as c:
+            for line in reader:
+                newline = tuple([convertToNone(v) for v in line])
+                c.execute(sqlstring, newline)
+        conn.commit()
+
+@with_connection
+def download_marks(conn, workdate, payload):
+    r = requests.get('https://loans.markit.com/loanx/LoanXMarks.csv', params=payload)
+    marks_filename = os.path.join(root, "data", "markit", "markit_data_{0}.csv".format(workdate))
+    with open(marks_filename, "wb") as fh:
+        fh.write(r.content)
+    sqlstring = "INSERT INTO markit_prices2 VALUES( {0} )".format( ",".join([ "%s" ] * 5))
+    with open(marks_filename, "r") as fh:
+        reader = csv.DictReader(fh)
+        with conn.cursor() as c:
+            for line in reader:
+                if line['Depth']=='implied':
+                    line['Depth']=0
+                c.execute(sqlstring, (line['LoanX ID'], line['Bid'], line['Offer'], line['Depth'], line['Mark Date']))
+        conn.commit()
+
+@with_connection
+def update_facility(conn, workdate, payload):
+    #we update the missing facility loanxids
+    sqlstring = "SELECT loanxid FROM markit_prices2 EXCEPT SELECT loanxid FROM markit_facility";
+    facility_diff_filename = os.path.join(root, "data", "Facility files",
+                                          "facility_diff_{0}.csv".format(workdate))
+    with open( facility_diff_filename, "wb") as fh:
+        flag = False
+        with conn.cursor() as c:
+            c.execute(sqlstring)
+            for loanxid in c:
+                payload.update({'LOANXID':loanxid[0]})
+                r = requests.get('https://loans.markit.com/loanx/LoanXOneFacility.csv', params=payload)
+                if flag:
+                    fh.write(r.content.split('\n')[1] + "\n")
+                else:
+                    fh.write(r.content.split('\n')[0] + "\n")
+                    fh.write(r.content.split('\n')[1] + "\n")
+                    flag = True
+
+    sqlstring = "INSERT INTO markit_facility(LoanXID, PMDID, IssuerName, dealname, facility_type," \
+        "loanx_facility_type, initial_amount, initial_spread, maturity, industry, modified_time)" \
+        "VALUES( {0} )".format( ",".join( ["%s"] * 11))
+    try:
+        with open(facility_diff_filename, "r") as fh:
+            reader = csv.reader(fh)
+            next(reader)
+            with conn.cursor() as c:
+                for line in reader:
+                    newline = [convertToNone(v) for v in line] + [workdate]
+                    newline.pop(9)          # remove the spread to maturity value
+                    c.execute(sqlstring, newline)
+        conn.commit()
+    except StopIteration:
+        pass
+    conn.close()
+
+if __name__=="__main__":
+    if len(sys.argv) > 1:
+        workdate = datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d").date()
+    else:
+        workdate = datetime.date.today()
+    workdate = str(workdate)
+
+    payload={'LEGALENTITY': 'serecap',
+             'USERNAME': 'serecapuser',
+             'PASSWORD': 'Welcome1'}
+    download_facility(workdate, payload)
+    payload.update({'EOD':'Y'})
+    download_marks(workdate, payload)
+    payload.pop('EOD')
+    update_facility(workdate, payload)