diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/markit_download.py | 146 |
1 files changed, 79 insertions, 67 deletions
diff --git a/python/markit_download.py b/python/markit_download.py index e690f779..47520c5a 100644 --- a/python/markit_download.py +++ b/python/markit_download.py @@ -1,84 +1,96 @@ import requests
from common import root
-from db import conn
+from db import conn, with_connection
import os
import datetime
import csv
-payload={'LEGALENTITY': 'serecap',
- 'USERNAME': 'serecapuser',
- 'PASSWORD': 'Welcome1'}
-
-workdate = str(datetime.date.today())
-
def convertToNone(v):
return v if v else None
-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)
-
-payload.update({'EOD':'Y'})
-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 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_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()
-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)
-#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))
-payload.pop('EOD')
-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))
-c = conn.cursor()
-try:
- with open(facility_diff_filename, "r") as fh:
+ 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 = [convertToNone(v) for v in line] + [workdate]
- newline.pop(9) # remove the spread to maturity value
+ newline = tuple([convertToNone(v) for v in line])
c.execute(sqlstring, newline)
- conn.commit()
-except FileNotFoundError:
- pass
-conn.close()
+ 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(datetime.date.today())
+
+ 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)
|