diff options
| -rw-r--r-- | R/intex_deal_functions.R | 47 | ||||
| -rw-r--r-- | sql/et_tables.sql | 23 |
2 files changed, 45 insertions, 25 deletions
diff --git a/R/intex_deal_functions.R b/R/intex_deal_functions.R index 29573b24..f6fa2c2c 100644 --- a/R/intex_deal_functions.R +++ b/R/intex_deal_functions.R @@ -12,17 +12,31 @@ source(file.path(root.dir, "code", "R", "cds_functions_generic.R")) source(file.path(root.dir, "code", "R", "etdb.R"))
source(file.path(root.dir, "code", "R", "tranche_functions.R"))
-getdealdata <- function(dealnames){
- sqlstring <- sprintf("select * from latest_clo_universe where dealname in ('%s')",
- paste(dealnames, collapse="','"))
+getdealdata <- function(dealnames, date){
+ if(missing(date)){
+ sqlstring <- sprintf("select * from latest_clo_universe where dealname in ('%s')",
+ paste(dealnames, collapse="','"))
+ }else{
+ sqlstring <- paste("SELECT b.* FROM (SELECT MAX(\"Latest Update\") AS latestdate,",
+ "dealname FROM clo_universe WHERE \"Latest Update\"<='%s' AND dealname in ('%s')",
+ "GROUP BY dealname) a JOIN clo_universe b ON a.dealname = b.dealname",
+ "AND a.latestdate= b.\"Latest Update\" ORDER by dealname asc")
+ sqlstring <- sprintf(sqlstring, date, paste(dealnames, collapse="','"))
+ }
+ data <- dbGetQuery(dbCon, sqlstring)
data <- dbGetQuery(dbCon, sqlstring)
data <- data[match(dealnames, data$dealname),]
rownames(data) <- c()
return( data )
}
-getcollateral <- function(dealname, date=Sys.Date()){
- sqlstring <- sprintf("select * from et_aggdealinfo_historical('%s', '%s')", dealname, date)
+
+getcollateral <- function(dealname, date){
+ if(missing(date)){
+ sqlstring <- sprintf("select * from et_aggdealinfo('%s')", dealname, date)
+ }else{
+ sqlstring <- sprintf("select * from et_aggdealinfo_historical('%s', '%s')", dealname, date)
+ }
collatdata <- dbGetQuery(dbCon, sqlstring)
return(collatdata)
}
@@ -32,10 +46,13 @@ listdealnames <- function(){ return( dbGetQuery(dbCon, sqlstring))
}
-cusip.data <- function(){
- sqlstring <- "SELECT a.cusip, b.maturity, a.coupon AS grosscoupon, a.spread,
+cusip.data <- function(date){
+ if(missing(date)){
+ sqlstring <- "SELECT a.cusip, b.maturity, a.coupon AS grosscoupon, a.spread,
CASE WHEN a.floater_index like 'LIBOR%' THEN 'FLOAT' ELSE 'FIXED' END
AS fixedorfloat, a.orig_moody from cusip_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname"
+ }else{
+ }
data <- dbGetQuery(dbCon, sqlstring)
return( data )
}
@@ -61,13 +78,11 @@ recovery <- function(collateral) { }
dealnamefromcusip <- function(cusips){
- ## wrapper around the sql procedure, not the fastest probably
- r <- NULL
- for(i in 1:length(cusips)){
- sqlstr <- sprintf("select * from dealname_from_cusip('%s')", cusips[i])
- r <- c(r, as.character(dbGetQuery(dbCon, sqlstr)))
- }
- return( r )
+ ## wrapper around the sql function
+ sqlstr <- sprintf("select * from dealname_from_cusip('%s')",
+ paste(cusips, collapse="','"))
+ r <- dbGetQuery(dbCon, sqlstr)
+ return( r$p_dealname )
}
cusipsfromdealnames <- function(dealnames){
@@ -239,8 +254,8 @@ buildSC <- function(line.item, reinvdate, dealmaturity, global.params, startdate return( list(SC=SC, notional=line.item$currentbalance, price = line.item$price) )
}
-buildSC.portfolio <- function(dealname, dealdata, cusipdata, global.params, startdate=today()) {
- collatdata <- getcollateral(dealname)
+buildSC.portfolio <- function(dealname, dealdata, cusipdata, global.params, startdate=Sys.Date()) {
+ collatdata <- getcollateral(dealname, startdate)
index <- hash(cusipdata$cusip, 1:length(cusipdata$cusip))
notionalvec <- c()
SCvec <- c()
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 78d660b6..e1647940 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -271,9 +271,8 @@ CREATE VIEW latest_clo_universe AS JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update" ORDER by dealname asc; --- CREATE OR REPLACE FUNCTION historical_clo_universe(p_date date) --- RETURNS TABLE(dealname varchar(8), "Deal Name" text, varchar --- GRANT ALL ON latest_clo_universe TO et_user; +GRANT ALL ON latest_clo_universe TO et_user; + CREATE VIEW dealcusipmapping AS SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe; @@ -359,12 +358,6 @@ CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_s END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusip varchar(9), OUT p_dealname varchar(8)) AS $$ - BEGIN - SELECT dealname INTO p_dealname from dealcusipmapping where cusip=p_cusip; - END; - $$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) RETURNS TABLE(loanxid varchar(8), issuer text, dealname text, facility text, industry text, sp varchar(4), moodys varchar(4), amount float, maturity date, @@ -449,6 +442,18 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) ALTER FUNCTION et_cusip_details(varchar(10)[]) OWNER TO et_user; +CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusips VARIADIC varchar(10)[]) + 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 + ON b.cusip = a.cusip ORDER BY b.id; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION dealname_from_cusip(varchar(10)[]) + OWNER TO et_user; + CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float, p_orig_thickness float, p_curr_thickness float) AS $$ |