1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
import requests
from common import root
from db import conn, with_connection
import os
import datetime
import csv
import sys
import logging
@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)
header = next(reader)
if 'Authentication failed' in header:
logging.error("Couldn't authenticate")
raise SystemExit
with conn.cursor() as c:
for line in reader:
newline = tuple([v or None 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_prices VALUES( {0} )".format( ",".join([ "%s" ] * 5))
with open(marks_filename, "r") as fh:
reader = csv.DictReader(fh)
if 'Authentication failed' in reader.fieldnames[0]:
logging.error("Couldn't authenticate")
raise SystemExit
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_prices 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 = [v or None 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)
logging.basicConfig(filename = '/home/share/CorpCDOs/logs/markit_loans.log',
level = logging.INFO,
format = '%(asctime)s %(levelname)s %(message)s')
payload={'LEGALENTITY': 'lmcg',
'USERNAME': 'serecapuser',
'PASSWORD': 'Welcome1'}
download_facility(workdate, payload)
payload.update({'EOD':'Y'})
download_marks(workdate, payload)
payload.pop('EOD')
update_facility(workdate, payload)
|
