aboutsummaryrefslogtreecommitdiffstats
path: root/python
diff options
context:
space:
mode:
Diffstat (limited to 'python')
-rw-r--r--python/facility_download.py39
-rw-r--r--python/facility_update.py61
-rw-r--r--python/load_markit_data.py27
-rw-r--r--python/markit_download.py49
4 files changed, 154 insertions, 22 deletions
diff --git a/python/facility_download.py b/python/facility_download.py
new file mode 100644
index 00000000..b189f536
--- /dev/null
+++ b/python/facility_download.py
@@ -0,0 +1,39 @@
+import requests
+import os
+import common
+
+legal = 'serecap'
+username = 'serecapuser'
+password = 'Welcome1'
+
+# sqlstring = "SELECT DISTINCT LoanXID from markit_prices";
+# common.cursor.execute(sqlstring)
+
+flag = False
+
+# with open( os.path.join(common.root, "data", "Facility files",
+# "facility_latest.csv"), "wb") as fh:
+# for loanxid in common.cursor.fetchall():
+# r = requests.get('https://loans.markit.com/loanx/LoanXOneFacility.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&LOANXID={3}'.format(legal, username, password, loanxid[0]))
+# 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 = "select loanxid from markit_prices2 except (select loanxid from latest_markit_prices2)"
+common.cursor.execute(sqlstring)
+with open( os.path.join(common.root, "data", "Facility files",
+ "facility_test.csv"), "wb") as fh:
+ for loanxid in common.cursor.fetchall():
+ r = requests.get('https://loans.markit.com/loanx/LoanXOneFacility.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&LOANXID={3}'.format(legal, username, password, loanxid[0]))
+ 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
+
+common.cursor.close()
+common.conn.close()
diff --git a/python/facility_update.py b/python/facility_update.py
new file mode 100644
index 00000000..9d2f234a
--- /dev/null
+++ b/python/facility_update.py
@@ -0,0 +1,61 @@
+import requests
+import os
+import os.path
+import datetime
+import common
+import csv
+import pdb
+import psycopg2
+
+legal = 'serecap'
+username = 'serecapuser'
+password = 'Welcome1'
+
+r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}'.format(legal, username, password))
+
+def convertToNone(v):
+ return v if v else None
+
+date = str(datetime.date.today())
+filename = os.path.join(common.root, "data", "Facility files", "facility_{0}.csv".format(date))
+with open( filename, "wb") as fh:
+ fh.write(r.content)
+
+# filename = os.path.join(common.root, "data", "Facility files", "facility_latest.csv")
+# with open(filename, "r") as fh:
+# reader = csv.reader(fh)
+# reader.next()
+# 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))
+# for line in reader:
+# newline = [convertToNone(v) for v in line] + [date]
+# newline.pop(9) # remove the spread to maturity value
+# common.cursor.execute(sqlstring, newline)
+# common.conn.commit()
+
+# common.cursor.execute("SELECT DISTINCT LoanXID from markit_facility")
+# cusips = [c[0] for c in common.cursor.fetchall()]
+# filename = os.path.join(common.root, "data", "Facility files", "facility_update_" + date + ".csv")
+# with open( filename, "r") as fh:
+# reader = csv.reader(fh)
+# reader.next()
+# sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
+# for line in reader:
+# newline = [convertToNone(v) for v in line]
+# if newline[0] in cusips:
+# common.cursor.execute("DELETE FROM markit_facility WHERE LoanXID = %s", (newline[0],))
+# common.cursor.execute(sqlstring, newline)
+# else:
+# common.cursor.execute(sqlstring, newline)
+
+with open( filename, "r") as fh:
+ reader = csv.reader(fh)
+ reader.next()
+ sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
+ for line in reader:
+ newline = [convertToNone(v) for v in line]
+ common.cursor.execute(sqlstring, newline)
+common.conn.commit()
+common.cursor.close()
+common.conn.close()
diff --git a/python/load_markit_data.py b/python/load_markit_data.py
index d2188952..e048e8d0 100644
--- a/python/load_markit_data.py
+++ b/python/load_markit_data.py
@@ -1,42 +1,31 @@
-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'
+import common
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]
+latestdate = common.cursor.execute("SELECT MAX(pricingdate) from markit_prices")
+latestdate = common.cursor.fetchone()[0]
sql_fields = ["LoanXID", "Issuer", "DealName", "Facility", "Industry", "SP",
"Moodys", "Amount", "Maturity", "Spread", "Bid", "Offer", "Depth",
"STM", "PricingDate"]
-for filename in os.listdir(root):
+for filename in os.listdir(os.path.join(root, "data", "markit")):
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:
+ with open( os.path.join(root, "data", "markit", filename) ) as fh:
dr = csv.DictReader(fh)
data = []
for line in dr:
@@ -57,6 +46,8 @@ for filename in os.listdir(root):
for row in data:
sqlstring = "INSERT INTO markit_prices({0}) " \
"VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields)))
- cursor.execute(sqlstring, tuple(row) + (date,))
- conn.commit()
+ common.cursor.execute(sqlstring, tuple(row) + (date,))
+ common.conn.commit()
+common.cursor.close()
+common.conn.close()
print "done"
diff --git a/python/markit_download.py b/python/markit_download.py
index b4145c22..eb3e41b9 100644
--- a/python/markit_download.py
+++ b/python/markit_download.py
@@ -1,6 +1,47 @@
import requests
+import common
+import os
+import datetime
+import csv
-# r=requests.get('https://loans.markit.com/loanx/LoanXMarks.csv?LEGALENTITY=serecap&USERNAME=serecapuser&PASSWORD=Welcome1&EOD=Y')
-r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY=serecap&USERNAME=serecapuser&PASSWORD=Welcome1')
-for line in r.text.split('\n'):
- print line
+legal = '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/LoanXMarks.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&EOD=Y'.format(legal, username, password))
+marks_filename = os.path.join(common.root, "data", "markit", "markit_data_{0}.csv".format(workdate))
+with open(marks_filename, "wb") as fh:
+ fh.write(r.content)
+
+r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}'.format(legal, username, password))
+
+facility_filename = os.path.join(common.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.reader(fh)
+ reader.next() # we skip the headers
+ for line in reader:
+ if line[4] == "implied":
+ line[4] = 0
+ common.cursor.execute(sqlstring, (line[0], line[2], line[3], line[4], line[1]))
+common.conn.commit()
+
+sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
+with open( facility_filename, "r") as fh:
+ reader = csv.reader(fh) # we skip the headers
+ reader.next()
+ for line in reader:
+ newline = [convertToNone(v) for v in line]
+ common.cursor.execute(sqlstring, newline)
+common.conn.commit()
+
+common.cursor.close()
+common.conn.close()
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 19:13:33 +0000