diff options
| -rw-r--r-- | R/load_cf.R | 15 | ||||
| -rw-r--r-- | sql/et_tables.sql | 13 |
2 files changed, 21 insertions, 7 deletions
diff --git a/R/load_cf.R b/R/load_cf.R index 7703e87f..22c5386a 100644 --- a/R/load_cf.R +++ b/R/load_cf.R @@ -178,7 +178,8 @@ getcusipcf <- function(cusips, cfdata, workdate=Sys.Date()){ for(field in fields){
cleanfield <- tryCatch(sanitize.column(data[,field]),
warning = function(w)w)
- if(inherits(cleanfield, "warning")){
+ if(inherits(cleanfield, "warning")||any(is.na(cleanfield))){
+ cat(sprintf("file: %s is messed up", filename), "\n")
flag <- TRUE
break
}else{
@@ -232,11 +233,21 @@ getcusipcf <- function(cusips, cfdata, workdate=Sys.Date()){ cfdata <- getdealcf(dealnames, workdate)
availablecusips <- cusips[dealnamefromcusip(cusips) %in% names(cfdata)]
cusipdata <- getcusipcf(availablecusips, cfdata, workdate)
+## upload wapbasis
+for(dealname in names(cfdata)){
+ sqlstring <- sprintf(paste0("UPDATE et_deal_model_numbers SET ",
+ "wapbasis = '%s' WHERE dealname= '%s' AND updatedate = '%s'"),
+ cfdata[[dealname]]$wapbasis*100,
+ dealname,
+ strftime(workdate))
+ dbSendQuery(dbCon, sqlstring)
+}
+## upload model data
for(cusip in names(cusipdata)){
columns <- c("Cusip", "price", "wal", "duration", "updatedate")
values <- c(cusip, cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
- cusipdata[[cusip]]$duration, workdate)
+ cusipdata[[cusip]]$duration, strftime(workdate))
colstring <- paste(columns[!is.na(values)], collapse=",")
valstring <- paste(values[!is.na(values)], collapse="','")
sqlstring <- sprintf(paste0("INSERT INTO et_cusip_model_numbers",
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index a26ad542..069f0c66 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -452,18 +452,20 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) manager text, empty2 unknown, spread float, issuedate date, reinvenddate date, maturity date, stale_percentage float, cdo_percentage float, wap_basis float, portfolio_spread float, - subordination float, thickness float ) AS $$ + subordination float, thickness float, empty3 unknown, pricingdate date, + delta float, duration float, wal float, price float) AS $$ BEGIN RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager, NULL, a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity, - e.stalepercentage, greatest(b."CDO Percentage"/100, e.cdopercentage), CAST(NULL AS float), e.dealspread, - d.subordination, d.thickness - FROM cusip_universe a LEFT JOIN latest_clo_universe b + e.stalepercentage, greatest(b."CDO Percentage"/100, e.cdopercentage), e.wapbasis, e.dealspread, + a.subordination, a.thickness, NULL, d.updatedate, d.delta, d.duration, d.wal, d.price + FROM latest_cusip_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 et_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; + ON c.cusip = a.cusip + LEFT JOIN latest_cusip_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; END; $$ LANGUAGE plpgsql; @@ -553,6 +555,7 @@ CREATE TABLE et_deal_model_numbers( dealspread float, marketvalue float, updatedate date, + wapbasis float, PRIMARY KEY(dealname, updatedate) ); |