diff options
| -rw-r--r-- | R/cusip_numbers.R | 26 | ||||
| -rw-r--r-- | sql/et_tables.sql | 15 |
2 files changed, 30 insertions, 11 deletions
diff --git a/R/cusip_numbers.R b/R/cusip_numbers.R index b7491b38..f00c0709 100644 --- a/R/cusip_numbers.R +++ b/R/cusip_numbers.R @@ -1,4 +1,9 @@ -root.dir <- "//WDSENTINEL/share/CorpCDOs"
+if(.Platform$OS.type == "unix"){
+ root.dir <- "/home/share/CorpCDOs"
+}else{
+ root.dir <- "//WDSENTINEL/share/CorpCDOs"
+}
+
source(file.path(root.dir, "code", "R", "intex_deal_functions.R"))
source(file.path(root.dir, "code", "R", "index_definitions.R"))
source(file.path(root.dir, "code", "R", "etdb.R"))
@@ -9,29 +14,32 @@ getdealdata <- function(dealnames){ return( dbGetQuery(dbCon, sqlstring) )
}
-workdate <- "2013-01-22"
+workdate <- as.Date("2013-01-23")
files <- list.files(path=file.path(root.dir, "Scenarios", paste("Portfolios", workdate, sep="_")), pattern="*.RData")
-
+currdealnames <- dbGetQuery(dbCon, "select updatedate, dealname from latest_deal_model_numbers")
for(file in files){
load(file.path(root.dir, "Scenarios", paste("Portfolios", workdate, sep="_"), file))
- currdealnames <- dbGetQuery(dbCon, "select dealname from et_deal_model_numbers")$dealname
dealname <- strsplit(file, "\\.")[[1]][1]
- if(dealname %in% currdealnames){
+ dealupdatedate <- currdealnames$updatedate[currdealnames$dealname %in% dealname]
+ if(length(dealupdatedate) && dealupdatedate == workdate){
sqlstring <- sprintf(paste0("UPDATE et_deal_model_numbers SET dealspread = %s, cdopercentage = %s,",
- "stalepercentage= %s WHERE dealname= '%s'"),
+ "stalepercentage= %s WHERE dealname= '%s' and updatedate = '%s'"),
deal.spread,
deal.portfolio$cdopercentage,
deal.portfolio$stale,
- dealname)
+ dealname,
+ as.Date(workdate))
dbGetQuery(dbCon, sqlstring)
}else{
sqlstring <- sprintf(paste0("INSERT INTO et_deal_model_numbers ",
- "VALUES('%s', %s, %s, %s)"),
+ "VALUES('%s', %s, %s, %s, %s, '%s')"),
dealname,
deal.portfolio$cdopercentage,
deal.portfolio$stale,
- deal.spread)
+ deal.spread,
+ crossprod(deal.portfolio$notional, deal.portfolio$price)/100,
+ workdate)
dbGetQuery(dbCon, sqlstring)
currdealnames <- c(currdealnames, dealname)
}
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 999120ab..7503521d 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -355,7 +355,7 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) d.subordination, d.thickness FROM cusip_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname - LEFT JOIN et_deal_model_numbers e + 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 et_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; @@ -434,7 +434,9 @@ CREATE TABLE et_deal_model_numbers( cdopercentage float, stalepercentage float, dealspread float, - PRIMARY KEY(dealname) + marketvalue float, + updatedate date, + PRIMARY KEY(dealname, updatedate) ); CREATE TABLE et_cusip_model_numbers( @@ -447,3 +449,12 @@ CREATE TABLE et_cusip_model_numbers( GRANT ALL ON et_deal_model_numbers TO et_user; GRANT ALL ON et_cusip_model_numbers TO et_user; + + +CREATE VIEW latest_deal_model_numbers AS + SELECT b.* + FROM (SELECT MAX(updatedate) AS latestdate, dealname FROM et_deal_model_numbers GROUP BY dealname) a + JOIN et_deal_model_numbers b ON a.dealname = b.dealname AND a.latestdate= b.updatedate + ORDER by dealname asc; + +GRANT ALL ON latest_deal_model_numbers TO et_user; |