aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--R/intex_deal_functions.R10
-rw-r--r--python/intex/intex_scenarios.py8
-rw-r--r--python/intex/load_indicative.py33
-rw-r--r--python/intex/load_intex_collateral.py5
-rw-r--r--sql/et_tables.sql41
5 files changed, 54 insertions, 43 deletions
diff --git a/R/intex_deal_functions.R b/R/intex_deal_functions.R
index a128c960..6fd34e9e 100644
--- a/R/intex_deal_functions.R
+++ b/R/intex_deal_functions.R
@@ -28,7 +28,7 @@ getdealdata <- function(dealname, workdate){
return(dealdata)
}
-getcollateral <- function(dealname, date){
+getcollateral <- function(dealname, date) {
if(missing(date)){
collatdata <- suppressWarnings(
dbGetQuery(etdb, "select * from et_aggdealinfo($1)",
@@ -42,15 +42,17 @@ getcollateral <- function(dealname, date){
return(collatdata)
}
-listdealnames <- function(){
+listdealnames <- function() {
sqlstring <- "select distinct dealname from clo_universe order by dealname"
return( dbGetQuery(etdb, sqlstring))
}
-cusip.data <- function(workdate = Sys.Date()){
+cusip.data <- function(workdate = Sys.Date()) {
sqlstring <- "SELECT DISTINCT ON (cusip) cusip, maturity, coupon AS grosscoupon,
spread, CASE WHEN floater_index like 'LIBOR%' THEN 'FLOAT' ELSE 'FIXED' END
-AS fixedorfloat, orig_moody FROM cusip_universe JOIN deal_indicative USING (dealname)
+ AS fixedorfloat, orig_moody FROM cusip_universe JOIN clo_universe
+ ON clo_universe.\"Latest Update\" = cusip_universe.updatedate
+ AND clo_universe.dealname = cusip_universe.dealname
WHERE updatedate<=$1 ORDER BY cusip, updatedate DESC"
data <- dbGetQuery(etdb, sqlstring, workdate)
data <- data.table(data)
diff --git a/python/intex/intex_scenarios.py b/python/intex/intex_scenarios.py
index 0613a1e2..576e275e 100644
--- a/python/intex/intex_scenarios.py
+++ b/python/intex/intex_scenarios.py
@@ -55,10 +55,10 @@ def get_recovery(conn, dealname, workdate, defaultrecovery = 50):
conn.commit()
return float(recovery)
-def get_reinvenddate(conn, dealname):
- sqlstr = 'SELECT reinv_end_date from deal_indicative where dealname=%s'
+def get_reinvenddate(conn, dealname, workdate):
+ sqlstr = 'SELECT reinv_end_date FROM historical_clo_universe(%s, %s)'
with conn.cursor() as c:
- c.execute(sqlstr, (dealname,))
+ c.execute(sqlstr, (dealname, workdate))
reinvenddate, = c.fetchone()
conn.commit()
if reinvenddate:
@@ -80,7 +80,7 @@ def generate_scenarios(workdate, dealname, conn):
return
reinvflag = config['reinvflag']
if reinvflag:
- reinvenddate = get_reinvenddate(conn, dealname)
+ reinvenddate = get_reinvenddate(conn, dealname, workdate)
reinv_assets = get_reinv_assets(conn, dealname, workdate)
n_float_assets = len([v for v in reinv_assets.values() if v == 'FLOAT'])
n_fixed_assets = len([v for v in reinv_assets.values() if v == 'FIXED'])
diff --git a/python/intex/load_indicative.py b/python/intex/load_indicative.py
index 89e87e1e..3686aa85 100644
--- a/python/intex/load_indicative.py
+++ b/python/intex/load_indicative.py
@@ -98,11 +98,11 @@ def upload_cusip_data(conn, filename):
conn.commit()
def upload_deal_data(conn, filename):
- sqlstr = "select dealname, array_agg(\"Latest Update\") from clo_universe group by dealname"
+ sqlstr = 'SELECT dealname, array_agg("Latest Update") FROM clo_universe GROUP BY dealname'
with conn.cursor() as c:
c.execute(sqlstr)
deallist1 = dict(c)
- sqlstr = "select dealname from deal_indicative"
+ sqlstr = "SELECT dealname FROM deal_indicative"
with conn.cursor() as c:
c.execute(sqlstr)
deallist2 = [d[0] for d in c]
@@ -125,7 +125,8 @@ def upload_deal_data(conn, filename):
line["Paid Down"] = re.sub("Paid Down: ","", line["Latest Update"])
line["Latest Update"] = line["Paid Down"]
for field in ["Deal Closing Date", "Deal Termination Date", "Reinv End Date", \
- "Latest Update", "Pay Day", "Deal First Pay Date", "Paid Down"]:
+ "Latest Update", "Pay Day", "Deal First Pay Date", "Paid Down",
+ "Deal's Last Refi Date"]:
if line[field]:
try:
line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date()
@@ -135,24 +136,22 @@ def upload_deal_data(conn, filename):
if line["Pay Day"]:
line["Pay Day"] = line["Pay Day"].day
for key in ["Principal Collection Account", "Interest Collection Account",
- "Curr Deal Bal", "Tranche Curr Bal", "CDOpercent", "defaultedbal"]:
+ "Curr Deal Bal", "Tranche Curr Bal", "CDOpercent", "defaultedbal",
+ "Orig Deal Bal", "Tranche Orig Bal"]:
if line[key]:
line[key] = sanitize_float(line[key])
line['Deal/Tranche ID'] = line['Deal/Tranche ID'].lower()
dealname = line['Deal/Tranche ID']
if dealname not in deallist2:
- for key in ["Orig Deal Bal", "Tranche Orig Bal"]:
- if line[key]:
- line[key] = sanitize_float(line[key])
-
- sqlstr = "INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, %(Deal Name)s, " \
- "%(Collateral Manager)s, %(Deal Closing Date)s, %(Deal Termination Date)s, " \
- "%(Pay Day)s, %(Reinv End Date)s, %(Deal First Pay Date)s, %(Orig Deal Bal)s, " \
- "%(Tranche Orig Bal)s, %(Deal CUSIP List)s, %(Paid Down)s)"
+ sqlstr = "INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, " \
+ "%(Deal Name)s, %(Collateral Manager)s, " \
+ "%(Deal Closing Date)s, %(Pay Day)s, " \
+ "%(Deal First Pay Date)s, %(Paid Down)s)"
else:
- #we always update Deal Cusip List and paid_down
- sqlstr = 'UPDATE deal_indicative SET "Deal Cusip List"=%(Deal CUSIP List)s, ' \
- 'paid_down=%(Paid Down)s WHERE dealname=%(Deal/Tranche ID)s'
+ #we always update paid_down
+ sqlstr = 'UPDATE deal_indicative SET paid_down=%(Paid Down)s ' \
+ 'WHERE dealname=%(Deal/Tranche ID)s'
+
if line['Deal CUSIP List']:
line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",")
try:
@@ -167,7 +166,9 @@ def upload_deal_data(conn, filename):
"VALUES (%(Deal/Tranche ID)s, %(Curr Deal Bal)s, %(Tranche Curr Bal)s, " \
"%(Tranche Factor)s, %(Principal Collection Account)s, " \
"%(Interest Collection Account)s, %(CDOpercent)s, %(defaultedbal)s, " \
- "%(Coupon)s, %(Latest Update)s)"
+ "%(Coupon)s, %(Latest Update)s, %(Deal's Last Refi Date)s, " \
+ "%(Deal CUSIP List)s, %(Deal Termination Date)s, %(Reinv End Date)s, " \
+ "%(Orig Deal Bal)s, %(Tranche Orig Bal)s)"
try:
with conn.cursor() as c:
c.execute(sqlstring, line)
diff --git a/python/intex/load_intex_collateral.py b/python/intex/load_intex_collateral.py
index cb736240..e6963ed3 100644
--- a/python/intex/load_intex_collateral.py
+++ b/python/intex/load_intex_collateral.py
@@ -89,7 +89,10 @@ def upload_data(conn, workdate):
temp = line["Amortization Schedule"].split("; ")
temp = [e.split(" @ ") for e in temp]
amounts, dates = zip(*temp)
- amounts = [float(e.replace(",", "")) for e in amounts]
+ if '%' in amounts[0]: #for reinvestment assets amort is in percentage.
+ amounts = [float(e.replace("%", "")) / 100 for e in amounts]
+ else:
+ amounts = [float(e.replace(",", "")) for e in amounts]
dates = [datetime.datetime.strptime(e, "%b %d, %Y").date() for e in dates]
r[-1] = dates
r.append(amounts)
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
index 0ad3f255..3713d535 100644
--- a/sql/et_tables.sql
+++ b/sql/et_tables.sql
@@ -284,14 +284,9 @@ CREATE TABLE deal_indicative (
"Deal Name" text,
Manager text,
deal_issue_date date,
- maturity date,
pay_day smallint,
- reinv_end_date date,
first_pay_date date,
- orig_deal_bal float,
- orig_collat_bal float,
- "Deal Cusip List" text[],
- paid_down date
+ paid_down date,
);
GRANT ALL ON deal_indicative TO et_user;
@@ -307,7 +302,13 @@ CREATE TABLE clo_universe (
"Defaulted Bal" float,
"Curr Coupon" float,
"Latest Update" date,
- PRIMARY KEY (dealname, "Latest Update")
+ latest_refi_date date,
+ "Deal Cusip List" text[],
+ maturity date,
+ reinv_end_date date,
+ orig_deal_bal float,
+ orig_collat_bal float,
+ PRIMARY KEY (dealname, "Latest Update"),
);
GRANT ALL ON clo_universe TO et_user;
@@ -371,14 +372,15 @@ CREATE OR REPLACE VIEW cusip_universe AS
JOIN cusip_update b USING (cusip);
CREATE OR REPLACE VIEW latest_clo_universe AS
- SELECT b.dealname, "Deal Name", Manager, orig_deal_bal, b."Curr Deal Bal", orig_collat_bal,
- b."Curr Collat Bal", b."Tranche Factor", b."Principal Bal", b."Interest Bal", b."CDO Percentage",
- b."Defaulted Bal", b."Curr Coupon", deal_issue_date, maturity, reinv_end_date, b."Latest Update",
- "Deal Cusip List", pay_day, first_pay_date, paid_down
+ SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal",
+ b.orig_collat_bal, b."Curr Collat Bal", b."Tranche Factor", b."Principal Bal",
+ b."Interest Bal", b."CDO Percentage", b."Defaulted Bal", b."Curr Coupon",
+ deal_issue_date, b.maturity, b.reinv_end_date, b."Latest Update",
+ b.last_refi_date, b."Deal Cusip List", pay_day, first_pay_date, paid_down
FROM (SELECT MAX("Latest Update") AS latestdate, dealname FROM clo_universe GROUP BY dealname) a
JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update"
JOIN deal_indicative ON b.dealname = deal_indicative.dealname
- ORDER BY dealname asc;
+ ORDER BY dealname ASC;
GRANT ALL ON latest_clo_universe TO et_user;
@@ -399,12 +401,15 @@ CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_dat
SELECT max(clo_universe."Latest Update") INTO latestdate FROM clo_universe
WHERE clo_universe.dealname = p_dealname AND clo_universe."Latest Update"<=p_date;
- RETURN QUERY SELECT a.dealname, "Deal Name", Manager, orig_deal_bal, a."Curr Deal Bal", orig_collat_bal,
- a."Curr Collat Bal", a."Tranche Factor", a."Principal Bal", a."Interest Bal", a."CDO Percentage",
- a."Defaulted Bal", a."Curr Coupon", deal_issue_date, maturity, reinv_end_date, a."Latest Update",
- "Deal Cusip List", pay_day, first_pay_date, paid_down FROM clo_universe a
- JOIN deal_indicative ON a.dealname = deal_indicative.dealname WHERE a.dealname = p_dealname
- AND a."Latest Update" = latestdate;
+ RETURN QUERY SELECT a.dealname, "Deal Name", Manager, a.orig_deal_bal, a."Curr Deal Bal",
+ a.orig_collat_bal, a."Curr Collat Bal", a."Tranche Factor", a."Principal Bal",
+ a."Interest Bal", a."CDO Percentage", a."Defaulted Bal", a."Curr Coupon",
+ deal_issue_date, a.maturity, a.reinv_end_date, a."Latest Update",
+ a.last_refi_date, a."Deal Cusip List", pay_day, first_pay_date, paid_down
+ FROM clo_universe a
+ JOIN deal_indicative ON a.dealname = deal_indicative.dealname
+ WHERE a.dealname = p_dealname
+ AND a."Latest Update" = latestdate;
END;
$$ LANGUAGE plpgsql;
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 13:11:00 +0000