aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--python/facility_download.py2
-rw-r--r--python/markit_loans.py4
-rw-r--r--sql/et_tables.sql38
3 files changed, 20 insertions, 24 deletions
diff --git a/python/facility_download.py b/python/facility_download.py
index 141f6e4f..58a0603c 100644
--- a/python/facility_download.py
+++ b/python/facility_download.py
@@ -24,7 +24,7 @@ flag = False
# flag = True
-sqlstring = "select loanxid from markit_prices2 except (select loanxid from latest_markit_prices2)"
+sqlstring = "select loanxid from markit_prices except (select loanxid from latest_markit_prices2)"
loan_ids = query_db(sqlstring, one=False)
with open( os.path.join(root, "data", "Facility files",
"facility_test.csv"), "wb") as fh:
diff --git a/python/markit_loans.py b/python/markit_loans.py
index b61a680f..4853ec1d 100644
--- a/python/markit_loans.py
+++ b/python/markit_loans.py
@@ -32,7 +32,7 @@ def download_marks(conn, workdate, 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))
+ sqlstring = "INSERT INTO markit_prices VALUES( {0} )".format( ",".join([ "%s" ] * 5))
with open(marks_filename, "r") as fh:
reader = csv.DictReader(fh)
with conn.cursor() as c:
@@ -45,7 +45,7 @@ def download_marks(conn, workdate, payload):
@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";
+ 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:
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
index 85761980..c0970c26 100644
--- a/sql/et_tables.sql
+++ b/sql/et_tables.sql
@@ -57,8 +57,8 @@ ALTER TABLE cusip_user_mapping OWNER TO et_user;
ALTER TABLE loanx_user_mapping OWNER TO et_user;
-CREATE TABLE markit_prices (
- -- DEPRECATED (use markit_prices2)
+CREATE TABLE markit_prices_old (
+ -- DEPRECATED (use markit_prices)
LoanXID varchar(8),
Issuer text,
DealName text,
@@ -77,9 +77,9 @@ CREATE TABLE markit_prices (
PRIMARY KEY (LoanXID, PricingDate)
);
-ALTER TABLE markit_prices OWNER TO et_user;
+ALTER TABLE markit_prices_old OWNER TO et_user;
-CREATE TABLE markit_prices2 (
+CREATE TABLE markit_prices (
LoanXID varchar(8),
Bid float,
Offer float,
@@ -88,7 +88,7 @@ CREATE TABLE markit_prices2 (
PRIMARY KEY (LoanXID, PricingDate)
);
-ALTER TABLE markit_prices2 OWNER TO et_user;
+ALTER TABLE markit_prices OWNER TO et_user;
CREATE TABLE markit_facility (
LoanXID varchar(8),
@@ -133,16 +133,16 @@ CREATE OR REPLACE FUNCTION historical_facility(p_date date)
ALTER FUNCTION historical_facility(p_date date)
OWNER TO et_user;
-CREATE VIEW latest_markit_prices2 AS
+CREATE OR REPLACE VIEW latest_markit_prices 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
+ FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a
+ JOIN markit_prices 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;
+GRANT ALL ON latest_markit_prices TO et_user;
-CREATE OR REPLACE FUNCTION historical_markit_prices2(p_date date)
+CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date)
RETURNS TABLE(loanxid varchar(8), issuername varchar(100), dealname varchar(60),
facility_type varchar(40), loanx_facility_type varchar(50), initial_amount float(15),
initial_spread float(7), maturity date, industry varchar(5),
@@ -152,14 +152,14 @@ CREATE OR REPLACE FUNCTION historical_markit_prices2(p_date date)
RETURN QUERY SELECT c.p_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, markit_prices2.loanxid FROM markit_prices2
- WHERE pricingdate <= p_date GROUP BY markit_prices2.loanxid) a
- JOIN markit_prices2 b ON a.loanxid = b.loanxid AND a.latestdate = b.pricingdate
+ FROM (SELECT MAX(pricingdate) AS latestdate, markit_prices.loanxid FROM markit_prices
+ WHERE pricingdate <= p_date GROUP BY markit_prices.loanxid) a
+ JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate = b.pricingdate
JOIN historical_facility(p_date) c ON c.p_loanxid = a.loanxid;
END;
$$ LANGUAGE plpgsql;
-ALTER FUNCTION historical_markit_prices2(p_date date)
+ALTER FUNCTION historical_markit_prices(p_date date)
OWNER TO et_user;
CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV');
@@ -325,8 +325,6 @@ CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_dat
END;
$$ LANGUAGE plpgsql;
-GRANT ALL ON historical_clo_universe TO et_user;
-
CREATE OR REPLACE FUNCTION historical_cusip_universe(p_cusip varchar(9), p_date date)
RETURNS TABLE(cusip varchar(9), isin varchar(12), dealname varchar(10), tranche text,
coupon float, orig_balance float, curr_balance float, factor float, orig_moody text,
@@ -342,7 +340,6 @@ CREATE OR REPLACE FUNCTION historical_cusip_universe(p_cusip varchar(9), p_date
END;
$$ LANGUAGE plpgsql;
-GRANT ALL ON historical_cusip_universe TO et_user;
CREATE VIEW dealcusipmapping AS
SELECT dealname, cusip from latest_cusip_universe;
@@ -350,7 +347,6 @@ CREATE VIEW dealcusipmapping AS
GRANT ALL ON dealcusipmapping TO et_user;
CREATE VIEW latest_markit_prices AS
- -- DEPRECATED (use latest_market_prices2)
SELECT b.*
FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a
JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;
@@ -403,8 +399,8 @@ 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_latestdealinfo(p_dealname) a
- LEFT JOIN latest_markit_prices2 b ON coalesce(a.ET_LoanXID, a.loanxid) = b.loanxid
- LEFT JOIN bloomberg_corp c ON coalesce(a.cusip, a.ET_cusip)=c.cusip WHERE a.ReinvFlag IS NOT TRUE
+ LEFT JOIN latest_markit_prices b ON coalesce(a.ET_LoanXID, a.loanxid) = b.loanxid
+ LEFT JOIN latest_bloomberg_corp c ON coalesce(a.cusip, a.ET_cusip)=c.cusip WHERE a.ReinvFlag IS NOT TRUE
GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
a.frequency, a.cusip ORDER BY issuername;
END;
@@ -467,7 +463,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_
bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip
FROM et_historicaldealinfo(p_dealname, p_date) a
- LEFT JOIN historical_markit_prices2(p_date) b ON coalesce(a.ET_LoanXID, a.loanxid)=b.loanxid
+ LEFT JOIN historical_markit_prices(p_date) b ON coalesce(a.ET_LoanXID, a.loanxid)=b.loanxid
LEFT JOIN historical_bloomberg_corp(p_date) c ON coalesce(a.cusip, a.ET_cusip)=c.cusip
WHERE a.ReinvFlag IS NOT TRUE
GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 15:38:50 +0000