diff options
| -rw-r--r-- | python/intex/load_indicative.py | 37 | ||||
| -rw-r--r-- | python/intex/load_intex_collateral.py | 10 | ||||
| -rw-r--r-- | sql/et_tables.sql | 164 |
3 files changed, 108 insertions, 103 deletions
diff --git a/python/intex/load_indicative.py b/python/intex/load_indicative.py index 1c480729..c8134a42 100644 --- a/python/intex/load_indicative.py +++ b/python/intex/load_indicative.py @@ -14,12 +14,13 @@ def convertToNone(s): return None if s in ["", "-", "NR"] else s -def insert_new_cusip(conn, line): +def insert_new_tranche(conn, line): if line["Pari-Passu Tranches"]: line["Pari-Passu Tranches"] = line["Pari-Passu Tranches"].split(",") to_insert = ( line["CUSIP"], line["ISIN"], + line["FIGI"], line["Bloomberg Ticker"], line["dealname"], line["tranche"], @@ -47,22 +48,22 @@ def insert_new_cusip(conn, line): line["Type"], ) sqlstr = ( - "INSERT INTO cusip_ref(Cusip, ISIN, bloomberg_ticker, dealname, " + "INSERT INTO tranche_ref(Cusip, ISIN, figi, bloomberg_ticker, dealname, " "tranche, paripassu_tranches, Orig_Balance, Orig_Moody, Orig_Attach, " "Orig_Detach, Floater_Index, Spread, type) VALUES({0}) " - "RETURNING cusip_id".format(",".join(["%s"] * 13)) + "RETURNING id".format(",".join(["%s"] * 14)) ) with conn.cursor() as c: try: c.execute(sqlstr, to_insert) - (cusip_id,) = c.fetchone() - return cusip_id + (tranche_id,) = c.fetchone() + return tranche_id except psycopg2.DataError as e: logger.error(e) conn.commit() -def upload_cusip_data(conn, filename: pathlib.Path): +def upload_tranche_data(conn, filename: pathlib.Path): dealupdate = {} with filename.open("r") as fh: dr = csv.DictReader(fh, dialect="excel-tab") @@ -90,20 +91,20 @@ def upload_cusip_data(conn, filename: pathlib.Path): except TypeError: logging.error(f"deal:{dealname} not in database") continue - sqlstring = "SELECT cusip_id FROM cusip_ref WHERE cusip=%s and dealname=%s" + sqlstring = "SELECT id FROM tranche_ref WHERE figi=%s and dealname=%s" with conn.cursor() as c: - c.execute(sqlstring, (line["CUSIP"], dealname)) + c.execute(sqlstring, (line["FIGI"], dealname)) r = c.fetchone() if r is None: try: - cusip_id = insert_new_cusip(conn, line) + tranche_id = insert_new_tranche(conn, line) except ValueError: continue else: - cusip_id = r[0] - sqlstring = "SELECT max(updatedate) FROM cusip_update WHERE cusip_id = %s" + tranche_id = r[0] + sqlstring = "SELECT max(updatedate) FROM tranche_update WHERE id = %s" with conn.cursor() as c: - c.execute(sqlstring, (cusip_id,)) + c.execute(sqlstring, (tranche_id,)) (curr_date,) = c.fetchone() if curr_date is None or curr_date < dealupdate[dealname]: try: @@ -120,11 +121,11 @@ def upload_cusip_data(conn, filename: pathlib.Path): except ValueError: continue line["Curr Moody"] = line.get("Curr Moody") or line.get("Orig Moody") - sqlstring = "INSERT INTO cusip_update VALUES({0})".format( + sqlstring = "INSERT INTO tranche_update VALUES({0})".format( ",".join(["%s"] * 8) ) to_insert = ( - cusip_id, + tranche_id, line["Curr Balance"], line["Factor"], line["Coupon"], @@ -138,7 +139,7 @@ def upload_cusip_data(conn, filename: pathlib.Path): c.execute(sqlstring, to_insert) except (psycopg2.DataError, psycopg2.IntegrityError) as e: logger.error(e) - logger.debug("uploaded: {0}".format(line["CUSIP"])) + logger.debug("uploaded: {0}".format(line["FIGI"])) conn.commit() @@ -271,7 +272,7 @@ if __name__ == "__main__": parser.add_argument("workdate", nargs="?", default=str(datetime.date.today())) args = parser.parse_args() - cusip_files = [ + tranche_files = [ f for f in (DATA_DIR / "Indicative_").iterdir() if "TrInfo" in f.name ] deal_files = [ @@ -283,5 +284,5 @@ if __name__ == "__main__": for deal in deal_files: upload_deal_data(etdb, deal) # then load tranche data - for cusip in cusip_files: - upload_cusip_data(etdb, cusip) + for tranche in tranche_files: + upload_tranche_data(etdb, cusip) diff --git a/python/intex/load_intex_collateral.py b/python/intex/load_intex_collateral.py index 8e1c3253..78767aa5 100644 --- a/python/intex/load_intex_collateral.py +++ b/python/intex/load_intex_collateral.py @@ -4,7 +4,7 @@ import psycopg2 from .common import sanitize_float from serenitas.utils.env import DATA_DIR import uuid -from .load_indicative import upload_cusip_data, upload_deal_data +from .load_indicative import upload_tranche_data, upload_deal_data import logging logger = logging.getLogger(__name__) @@ -23,6 +23,7 @@ fields = [ "Second Lien", "LoanX ID", "CUSIP", + "FIGI", "Market Price", "Market Price Source", "Market Price Date", @@ -56,6 +57,7 @@ def upload_data(conn, workdate): "SecondLien", "LoanXID", "Cusip", + "figi", "IntexPrice", "IntexPriceSource", "IntexPriceDate", @@ -213,13 +215,13 @@ def upload_data(conn, workdate): def intex_data(conn, workdate): basedir = DATA_DIR / ("Indicative_" + workdate) - cusip_files = [f for f in basedir.iterdir() if "TrInfo" in f.name] + tranche_files = [f for f in basedir.iterdir() if "TrInfo" in f.name] deal_files = [f for f in basedir.iterdir() if "TrInfo" not in f.name] # first load deal data for deal_file in deal_files: upload_deal_data(conn, deal_file) # then load tranche data - for cusip_file in cusip_files: - upload_cusip_data(conn, cusip_file) + for tranche_file in tranche_files: + upload_tranche_data(conn, tranche_file) upload_data(conn, workdate) diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 9f109772..b71141b8 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -2,6 +2,7 @@ CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT'); CREATE TABLE et_collateral ( + id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dealname varchar(8) NOT NULL, updatedate date, Name text, @@ -17,7 +18,7 @@ CREATE TABLE et_collateral ( SecondLien boolean, LoanXID varchar(10), ET_LoanXID varchar(10) default NULL, - Cusip varchar(9), + figi varchar(12), ET_Cusip varchar(10) default NULL, IntexPrice float, IntexPriceSource text, @@ -35,7 +36,7 @@ CREATE TABLE et_collateral ( Country text, amort_schedule_dates date[] DEFAULT NULL, amort_schedule_amounts float[] DEFAULT NULL, - PRIMARY KEY (updatedate, Name, dealname) + UNIQUE (updatedate, Name, dealname) ); CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate); @@ -340,8 +341,8 @@ CREATE TABLE cusip_universe_old ( GRANT ALL ON cusip_universe TO et_user; -CREATE TABLE cusip_ref( - cusip_id serial PRIMARY KEY, +CREATE TABLE tranche_ref( + id serial PRIMARY KEY, Cusip varchar(9) NOT NULL, ISIN varchar(12), bloomberg_ticker text, @@ -355,11 +356,11 @@ CREATE TABLE cusip_ref( Floater_Index text, Spread float, type text, - figi text, + figi varchar(12), UNIQUE (Cusip, dealname)); -CREATE TABLE cusip_update( - cusip_id int REFERENCES cusip_ref, +CREATE TABLE tranche_update( + id int REFERENCES tranche_ref, curr_balance float, factor float, coupon float, @@ -367,16 +368,16 @@ CREATE TABLE cusip_update( Curr_Attach float, Curr_Detach float, updatedate date, - PRIMARY KEY(cusip_id, updatedate)); + PRIMARY KEY (id, updatedate)); -CREATE OR REPLACE VIEW cusip_universe AS - SELECT a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, +CREATE OR REPLACE VIEW tranche_universe AS + SELECT a.figi, a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody, a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type, b.curr_balance, b.factor, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, - b.updatedate FROM cusip_ref a - JOIN cusip_update b USING (cusip_id); + b.updatedate FROM tranche_ref a + JOIN tranche_update b USING (id); CREATE OR REPLACE VIEW latest_clo_universe AS SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal", @@ -391,14 +392,14 @@ CREATE OR REPLACE VIEW latest_clo_universe AS GRANT ALL ON latest_clo_universe TO et_user; -CREATE OR REPLACE VIEW latest_cusip_universe AS -SELECT DISTINCT ON (cusip_id) a.cusip, a.isin, a.bloomberg_ticker, a.dealname, +CREATE OR REPLACE VIEW latest_tranche_universe AS +SELECT DISTINCT ON (id) a.figi, a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody, a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, - b.curr_detach, b.updatedate from cusip_update b JOIN cusip_ref a USING (cusip_id) + b.curr_detach, b.updatedate from tranche_update b JOIN tranche_ref a USING (id) JOIN deal_indicative USING (dealname) -WHERE paid_down IS NULL ORDER BY cusip_id, updatedate DESC; +WHERE paid_down IS NULL ORDER BY id, updatedate DESC; CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_date date) @@ -420,33 +421,33 @@ CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_dat END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION historical_cusip_universe(p_cusip varchar(9), p_date date) - RETURNS SETOF cusip_universe AS $$ +CREATE OR REPLACE FUNCTION historical_tranche_universe(p_figi varchar(12), p_date date) + RETURNS SETOF tranche_universe AS $$ DECLARE latestdate date; BEGIN - SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe - WHERE cusip_universe.cusip = p_cusip AND cusip_universe.updatedate<=p_date; - RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.cusip=p_cusip + SELECT max(tranche_universe.updatedate) INTO latestdate FROM tranche_update + WHERE tranche_universe.figi = p_figi AND tranche_imoverse.updatedate<=p_date; + RETURN QUERY SELECT a.* FROM tranche_universe a WHERE a.figi=p_figi AND a.updatedate=latestdate; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION historical_dealname_universe(p_dealname varchar(10), p_date date) - RETURNS SETOF cusip_universe AS $$ + RETURNS SETOF tranche_universe AS $$ DECLARE latestdate date; BEGIN - SELECT max(cusip_universe.updatedate) INTO latestdate FROM cusip_universe - WHERE cusip_universe.dealname = p_dealname AND cusip_universe.updatedate<=p_date; - RETURN QUERY SELECT a.* FROM cusip_universe a WHERE a.dealname=p_dealname + SELECT max(tranche_universe.updatedate) INTO latestdate FROM tranche_universe + WHERE tranche_universe.dealname = p_dealname AND tranche_universe.updatedate<=p_date; + RETURN QUERY SELECT a.* FROM tranche_universe a WHERE a.dealname=p_dealname AND a.updatedate=latestdate; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE VIEW dealcusipmapping AS - select cusip, dealname from deal_indicative join cusip_ref using (dealname) where paid_down is null; +CREATE OR REPLACE VIEW dealfigimapping AS + select figi, dealname from deal_indicative join tranche_ref using (dealname) where paid_down is null; -GRANT ALL ON dealcusipmapping TO et_user; +GRANT ALL ON dealfigimapping TO et_user; CREATE VIEW latest_markit_prices AS SELECT b.* @@ -480,7 +481,7 @@ CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10)) CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) RETURNS TABLE(issuername text, currentbalance float, maturity date, fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float, - frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9), + frequency varchar(1), nextpaydate date, loanxid varchar(10), figi varchar(12), covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$ BEGIN RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS @@ -488,14 +489,14 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) avg(coalesce((b.bid+b.offer)/2, c.price, a.userprice, a.intexprice)) AS price, avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS spread, a.frequency, min(a.nextpaydate) AS nextpaydate, - a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or + a.loanxid, a.figi, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or 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_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; + a.frequency, a.figi ORDER BY issuername; END; $$ LANGUAGE plpgsql; @@ -560,7 +561,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ RETURNS TABLE(issuername text, currentbalance float, maturity date, fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float, frequency varchar(1), nextpaydate date, loanxid varchar(10), - cusip varchar(9), industry text, covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, + figi varchar(12), industry text, covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, et_loanxid varchar(10), et_cusip varchar(9)) AS $$ BEGIN RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS @@ -568,7 +569,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ avg(coalesce((b.bid+b.offer)/2, c.price, a.userprice, a.intexprice)) AS price, avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS spread, a.frequency, min(a.nextpaydate) AS nextpaydate, - a.loanxid, a.cusip, max(a.industry), (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or + a.loanxid, a.figi, max(a.industry), (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or 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, a.et_loanxid, a.et_cusip @@ -577,14 +578,14 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ 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, - a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername; + a.frequency, a.figi, a.et_loanxid, a.et_cusip ORDER BY issuername; END; $$ LANGUAGE plpgsql; ALTER FUNCTION et_aggdealinfo_historical(varchar(10), date) OWNER TO et_user; -CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION et_figi_details(p_figis VARIADIC varchar(12)[]) RETURNS TABLE(orig_moody text, curr_moody text, empty1 text, issuer text, manager text, empty2 text, spread float, coupon float, issuedate date, reinvenddate date, maturity date, stale_percentage float, @@ -599,20 +600,20 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, d.mvoc, d.mvcoverage, NULL, d.updatedate, d.delta, d.duration, d.wal, d.price - FROM latest_cusip_universe a LEFT JOIN latest_clo_universe b + FROM latest_tranche_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname LEFT JOIN latest_deal_model_numbers e ON a.dealname = e.dealname - RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c - ON c.cusip = a.cusip - LEFT JOIN latest_cusip_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; + RIGHT JOIN (SELECT unnest($1) AS figi, generate_subscripts(p_figis, 1) AS id) c + ON c.figi = a.figi + LEFT JOIN latest_tranche_model_numbers d ON d.figi = c.figi ORDER BY c.id; END; $$ LANGUAGE plpgsql; -ALTER FUNCTION et_cusip_details(varchar(10)[]) +ALTER FUNCTION et_tranche_details(varchar(12)[]) OWNER TO et_user; -CREATE OR REPLACE FUNCTION historical_cusip_details(p_date date, p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION historical_tranche_details(p_date date, p_figis VARIADIC varchar(12)[]) RETURNS TABLE(orig_moody text, curr_moody text, issuer text, manager text, spread float, coupon float, issuedate date, reinvenddate date, maturity date, stale_percentage float, @@ -621,57 +622,58 @@ CREATE OR REPLACE FUNCTION historical_cusip_details(p_date date, p_cusips VARIAD mvoc float, mvcoverage float, pricingdate date, delta float, duration float, wal float, price float) AS $$ BEGIN - RETURN QUERY SELECT cusip_ref.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager, - cusip_ref.spread/100, a.coupon/100, deal_issue_date, reinv_end_date, deal_indicative.maturity, + RETURN QUERY SELECT a.orig_moody, a.curr_moody, "Deal Name", deal_indicative.manager, + a.spread/100, a.coupon/100, deal_issue_date, d.reinv_end_date, d.maturity, stalepercentage, cdopercentage, wapbasis, dealspread5y, dealspread, curr_attach/100, (curr_detach-curr_attach)/100, b.mvoc, b.mvcoverage, b.updatedate, b.delta, b.duration, b.wal, b.price - FROM cusip_ref + FROM (SELECT DISTINCT ON (figi) * FROM tranche_ref LEFT JOIN tranche_update USING (id) + WHERE updatedate<=p_date ORDER by figi, updatedate DESC) a LEFT JOIN - (SELECT DISTINCT ON (cusip) * FROM cusip_update WHERE updatedate<=p_date ORDER by cusip, updatedate DESC) a - USING (cusip) - LEFT JOIN - (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date - ORDER by cusip, updatedate DESC) b - USING (cusip) - LEFT JOIN (SELECT DISTINCT ON (dealname) * FROM et_deal_model_numbers WHERE updatedate<=p_date - ORDER by dealname, updatedate DESC) c - USING (dealname) - LEFT JOIN deal_indicative USING (dealname) - RIGHT JOIN (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) d USING (cusip) ORDER BY d.id; + (SELECT DISTINCT ON (figi) * FROM et_tranche_model_numbers WHERE updatedate<=p_date + ORDER by figi, updatedate DESC) b + USING (figi) + LEFT JOIN ( + SELECT DISTINCT ON (dealname) * FROM et_deal_model_numbers WHERE updatedate<=p_date + ORDER by dealname, updatedate DESC) c USING (dealname) + LEFT JOIN deal_indicative USING (dealname) + LEFT JOIN ( + SELECT DISTINCT ON (dealname) * FROM clo_universe WHERE "Latest Update" <= p_date + ORDER by dealname, "Latest Update" DESC) d USING (dealname) + RIGHT JOIN (SELECT unnest(p_figis) AS figi, generate_subscripts(p_figis, 1) AS id) e USING (figi) ORDER BY e.id; END; $$ LANGUAGE plpgsql; -ALTER FUNCTION historical_cusip_details(varchar(10)[]) +ALTER FUNCTION historical_tranche_details(varchar(12)[]) OWNER TO et_user; -CREATE OR REPLACE FUNCTION historical_cusip_risk(p_date date, p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION historical_tranche_risk(p_date date, p_figis VARIADIC varchar(12)[]) RETURNS TABLE(duration float, delta float, price float, pricingdate date) AS $$ BEGIN RETURN QUERY SELECT b.duration, b.delta, b.price, b.updatedate FROM - (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) a - LEFT JOIN (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers WHERE updatedate<=p_date - ORDER BY cusip, updatedate DESC) - AS b USING (cusip) ORDER BY id; + (SELECT unnest(p_figis) AS figi, generate_subscripts(p_figis, 1) AS id) a + LEFT JOIN (SELECT DISTINCT ON (figi) * FROM et_tranche_model_numbers WHERE updatedate<=p_date + ORDER BY figi, updatedate DESC) + AS b USING (figi) ORDER BY id; END - $$ LANGUAGE plpgsql; +$$ LANGUAGE plpgsql; -ALTER FUNCTION historical_cusip_risk(date, varchar(10)[]) +ALTER FUNCTION historical_tranche_risk(date, varchar(12)[]) OWNER TO et_user; -CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusips VARIADIC varchar(10)[]) +CREATE OR REPLACE FUNCTION dealname_from_figi(p_figis VARIADIC varchar(12)[]) -- NOT USED anymore, wasn't working very well RETURNS TABLE(p_dealname varchar(10)) AS $$ BEGIN - RETURN QUERY SELECT dealname FROM dealcusipmapping a - RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) b - USING (cusip) ORDER BY b.id; + RETURN QUERY SELECT dealname FROM dealfigimapping a + RIGHT JOIN (SELECT unnest($1) AS figi, generate_subscripts(p_cusips, 1) AS id) b + USING (figi) ORDER BY b.id; END; $$ LANGUAGE plpgsql; -ALTER FUNCTION dealname_from_cusip(varchar(10)[]) +ALTER FUNCTION dealname_from_figi(varchar(12)[]) OWNER TO et_user; CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) @@ -759,8 +761,8 @@ CREATE TABLE et_deal_model_numbers( PRIMARY KEY(dealname, updatedate) ); -CREATE TABLE et_cusip_model_numbers( - Cusip varchar(9), +CREATE TABLE et_tranche_model_numbers( + figi varchar(12), price float, wal float, duration float, @@ -768,11 +770,11 @@ CREATE TABLE et_cusip_model_numbers( updatedate date, mvoc float, mvcoverage float, - PRIMARY KEY(Cusip, updatedate) + PRIMARY KEY(figi, updatedate) ); GRANT ALL ON et_deal_model_numbers TO et_user; -GRANT ALL ON et_cusip_model_numbers TO et_user; +GRANT ALL ON et_tranche_model_numbers TO et_user; CREATE VIEW latest_deal_model_numbers AS @@ -783,32 +785,32 @@ CREATE VIEW latest_deal_model_numbers AS GRANT ALL ON latest_deal_model_numbers TO et_user; -CREATE VIEW latest_cusip_model_numbers AS +CREATE VIEW latest_tranche_model_numbers AS SELECT b.* - FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM et_cusip_model_numbers GROUP BY cusip) a - JOIN et_cusip_model_numbers b ON a.cusip = b.cusip AND a.latestdate = b.updatedate - ORDER by cusip asc; + FROM (SELECT MAX(updatedate) AS latestdate, figi FROM et_tranche_model_numbers GROUP BY figi) a + JOIN et_tranche_model_numbers b ON a.figi = b.figi AND a.latestdate = b.updatedate + ORDER by figi asc; -GRANT ALL ON latest_cusip_model_numbers TO et_user; +GRANT ALL ON latest_tranche_model_numbers TO et_user; CREATE TABLE color( ListDate date, ListInfo text, - Cusip varchar(9), + figi varchar(12), Notional float, Indications text, Cover text, ListColor text, Bid text, Bid_note text, - PRIMARY KEY(Cusip, Notional, ListDate, ListInfo) + PRIMARY KEY(figi, Notional, ListDate, ListInfo) ); GRANT ALL ON color TO et_user; CREATE VIEW latest_color AS SELECT b.* - FROM (SELECT MAX(ListDate) AS latestdate, cusip FROM color GROUP BY cusip) a - JOIN color b ON a.cusip = b.cusip AND a.latestdate=b.listdate; + FROM (SELECT MAX(ListDate) AS latestdate, cusip FROM color GROUP BY figi) a + JOIN color b ON a.figi = b.figi AND a.latestdate=b.listdate; GRANT ALL ON latest_color TO et_user; |