aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-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
-rw-r--r--sql/et_tables.sql36
5 files changed, 187 insertions, 25 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()
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
index 7503521d..d842249a 100644
--- a/sql/et_tables.sql
+++ b/sql/et_tables.sql
@@ -73,6 +73,17 @@ CREATE TABLE markit_prices (
ALTER TABLE markit_prices OWNER TO et_user;
+CREATE TABLE markit_prices2 (
+ LoanXID varchar(8),
+ Bid float,
+ Offer float,
+ Depth int,
+ PricingDate date,
+ PRIMARY KEY (LoanXID, PricingDate)
+);
+
+ALTER TABLE markit_prices2 OWNER TO et_user;
+
CREATE TABLE markit_facility (
LoanXID varchar(8),
PMDID float(10),
@@ -80,14 +91,33 @@ CREATE TABLE markit_facility (
dealname varchar(60),
facility_type varchar(40),
loanx_facility_type varchar(50),
- industry varchar(50),
initial_amount float(15),
initial_spread float(7),
maturity date,
+ industry varchar(50),
+ LoanXstatus varchar(1),
created_time date,
- modified_time date
+ modified_time date,
+ PRIMARY KEY (LoanXID, modified_time)
);
+ALTER TABLE markit_facility OWNER TO et_user;
+
+CREATE VIEW latest_markit_facility AS
+ SELECT b.*
+ FROM (SELECT MAX(modified_time) AS latestdate, loanxid FROM markit_facility GROUP BY loanxid) a
+ JOIN markit_facility b ON a.loanxid = b.loanxid AND a.latestdate = b.modified_time;
+
+GRANT ALL ON latest_markit_facility TO et_user;
+
+CREATE VIEW latest_markit_prices2 AS
+ SELECT c.loanxid, c.issuername, c.dealname, c.facility_type, c.loanx_facility_type,
+ c.initial_amount, c.initial_spread, c.maturity, c.industry, b.bid, b.Offer, b.depth, a.latestdate
+ FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices2 GROUP BY loanxid) a
+ JOIN markit_prices2 b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate
+ JOIN latest_markit_facility c ON a.loanxid = c.loanxid;
+
+GRANT ALL ON latest_markit_prices2 TO et_user;
CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV');
@@ -240,7 +270,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10))
bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
bool_or(a.defaultedflag) AS defaultedflag from et_collateral a
- left join latest_markit_prices b on a.loanxid=b.loanxid left
+ left join latest_markit_prices2 b on a.loanxid=b.loanxid left
join bloomberg_corp c on a.cusip=c.cusip where a.dealname = p_dealname
group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
a.frequency, a.cusip ORDER BY issuername;
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 15:20:12 +0000