diff options
| -rw-r--r-- | R/load_cf.R | 139 |
1 files changed, 65 insertions, 74 deletions
diff --git a/R/load_cf.R b/R/load_cf.R index 225e16ff..c8b98a16 100644 --- a/R/load_cf.R +++ b/R/load_cf.R @@ -46,21 +46,21 @@ processzipfiles <- function(tradedate=Sys.Date()){ dealnames <- unique(unlist(lapply(strsplit(dealnames, "-"), function(x)x[1]))) allfiles <- unique(unlist(lapply(strsplit(allfiles, "-"), function(x)x[1]))) allfiles <- allfiles[!(allfiles=="Total")] - cusips <- setdiff(allfiles, dealnames) + figis <- setdiff(allfiles, dealnames) dealnames <- tolower(dealnames) if(n==1){ dealnames.hash <- hash(dealnames, 1) - cusips.hash <- hash(cusips, 1) + figis.hash <- hash(figis, 1) }else{ - for( c in cusips){ - cusips.hash[c] <- n + for( f in figis){ + figis.hash[f] <- n } for(d in dealnames){ dealnames.hash[d] <- n } } } - return(list(dealnames=dealnames.hash, cusips=cusips.hash, zipfiles=zipfiles)) + return(list(dealnames=dealnames.hash, figis=figis.hash, zipfiles=zipfiles)) } getconfig <- function(dealname, tradedate){ @@ -119,7 +119,7 @@ getdealcf <- function(dealnames, zipfiles, tradedate=Sys.Date()){ filename <- paste0(paste(toupper(dealname), tranche, "CF", paste0("Scen", i), sep="-"), ".txt") conn <- unz(zip, filename) - data <- tryCatch(suppressWarnings(read_tsv(conn, col_types= ct)), + data <- tryCatch(suppressWarnings(read_tsv(conn, col_types= ct, name_repair="minimal")), error=function(e){ logerror(conditionMessage(e)) NULL}) @@ -178,44 +178,35 @@ getdealcf <- function(dealnames, zipfiles, tradedate=Sys.Date()){ return( cfdata ) } -getcusip_indicdata <- function(cusip, dealname, date) { - cusip_universe <- tbl(etdb, "cusip_universe") - r <- cusip_universe %>% - filter(cusip == !!cusip) %>% - distinct(isin) %>% collect() - if(length(r$isin) > 1) { - stop("We have a problem") - } - isin <- r$isin +gettranche_indicdata <- function(figi, dealname, date) { + tranche_universe <- tbl(etdb, "tranche_universe") + r <- tranche_universe %>% + filter(figi == !!figi) %>% + collect() sqlstr <- "SELECT * FROM historical_dealname_universe($1, $2)" r <- tryCatch(dbGetQuery(etdb, sqlstr, params = list(dealname, date)), error = function(w) logerror(w$message)) - if(!is.na(isin)) { - return(r %>% group_by(isin) %>% slice(1) %>% ungroup() %>% - arrange(desc(curr_attach)) %>% - mutate(cum_bal = cumsum(curr_balance)) %>% filter(isin == !!isin) %>% - select(cusip, curr_balance, orig_balance, spread, curr_attach, cum_bal)) - } else { - return(r %>% select(cusip, curr_balance, orig_balance, spread, curr_attach) %>% - arrange(desc(curr_attach)) %>% - mutate(cum_bal = cumsum(curr_balance)) %>% filter(cusip == !!cusip) ) - } + return(r %>% + arrange(desc(curr_attach)) %>% + mutate(cum_bal = cumsum(curr_balance)) %>% + filter(figi == !!figi) %>% + select(figi, curr_balance, orig_balance, spread, curr_attach, cum_bal)) } -getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){ - cusipdata <- list() - cusips <- keys(params$cusips) - dealnames <- dealnamefromcusip(cusips) - cusips <- cusips[dealnames %in% names(cfdata)] +gettranchecf <- function(params, cfdata, dist, tradedate=Sys.Date()){ + tranchedata <- list() + figis <- keys(params$figis) + dealnames <- dealnamefromfigi(figis) + figis <- figis[dealnames %in% names(cfdata)] dealnames <- dealnames[dealnames %in% names(cfdata)] n.scenarios <- 100 intexfields <- c("Cashflow", "Principal", "Interest", "Balance", "Accum Interest Shortfall") fields <- c("Cashflow", "Principal", "Interest", "wal", "duration") - for(i in 1:length(cusips)){ - cusip <- cusips[i] - loginfo(paste("processing", cusip)) - zip <- params$zipfiles[params$cusips[[cusip]]] + for(i in 1:length(figis)){ + figi <- figis[i] + loginfo(paste("processing", figi)) + zip <- params$zipfiles[params$figis[[figi]]] dealname <- dealnames[i] dealdata <- getdealdata(dealname, tradedate) alldates <- getdealschedule(dealdata, "Monthly", bdc = "Following") @@ -227,7 +218,7 @@ getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){ T=T, key="Date") r <- matrix(0, n.scenarios, 5) colnames(r) <- fields - indicdata <- getcusip_indicdata(cusip, dealname, tradedate) + indicdata <- gettranche_indicdata(figi, dealname, tradedate) flag <- TRUE ct <- list(col_date("%b %d, %Y"), col_number(), @@ -235,14 +226,14 @@ getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){ col_number(), col_number(), col_number(), col_skip(), col_skip()) for(j in 1:n.scenarios){ - filename <- sprintf("%s-CF-Scen%s.txt", cusip, j) + filename <- sprintf("%s-CF-Scen%s.txt", figi, j) conn <- unz(zip, filename) - data <- tryCatch(suppressWarnings(read_tsv(conn, col_types=ct)), + data <- tryCatch(suppressWarnings(read_tsv(conn, col_types=ct, name_repair="minimal")), error=function(e){ logerror(conditionMessage(e)) NULL}) if(is.null(data)||nrow(data)<=2){ - loginfo(paste(cusip, "Scenario", j)) + loginfo(paste(figi, "Scenario", j)) flag <- FALSE break } @@ -255,31 +246,31 @@ getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){ wal=sum(-diff(Balance)*T[-1])/indicdata$curr_balance, duration=if(temp==0) 0 else sum(Cashflow * Discounts * T)/temp)]) } - cusip.pv <- if(indicdata$curr_balance == 0) { - r[,"Cashflow"] / indicdata$orig_balance - } else { - r[,"Cashflow"] / indicdata$curr_balance - } + tranche.pv <- if(indicdata$curr_balance == 0) { + r[,"Cashflow"] / indicdata$orig_balance + } else { + r[,"Cashflow"] / indicdata$curr_balance + } if(flag) { - cusipdata[[cusip]] <- list(currbal=indicdata$curr_balance, - spread=indicdata$spread, - Cashflow=crossprod(cfdata[[dealname]]$weight, r[,"Cashflow"]), - wal = crossprod(cfdata[[dealname]]$weight, r[,"wal"]), - duration = crossprod(cfdata[[dealname]]$weight, r[,"duration"]), - price = 100 * crossprod(cusip.pv, cfdata[[dealname]]$weight), - delta = compute.delta(dist, cfdata[[dealname]], cusip.pv), - mvoc = (cfdata[[dealname]]$mv+cfdata[[dealname]]$principalbal)/ - indicdata$cum_bal-1, - mvcoverage = 1+(cfdata[[dealname]]$mv+cfdata[[dealname]]$principalbal-indicdata$cum_bal)/indicdata$curr_balance, - fields=r) - loginfo(paste("done", cusip)) + tranchedata[[figi]] <- list(currbal=indicdata$curr_balance, + spread=indicdata$spread, + Cashflow=crossprod(cfdata[[dealname]]$weight, r[,"Cashflow"]), + wal = crossprod(cfdata[[dealname]]$weight, r[,"wal"]), + duration = crossprod(cfdata[[dealname]]$weight, r[,"duration"]), + price = 100 * crossprod(tranche.pv, cfdata[[dealname]]$weight), + delta = compute.delta(dist, cfdata[[dealname]], tranche.pv), + mvoc = (cfdata[[dealname]]$mv+cfdata[[dealname]]$principalbal)/ + indicdata$cum_bal-1, + mvcoverage = 1+(cfdata[[dealname]]$mv+cfdata[[dealname]]$principalbal-indicdata$cum_bal)/indicdata$curr_balance, + fields=r) + loginfo(paste("done", figi)) } } - return(cusipdata) + return(tranchedata) } -compute.delta <- function(indexdist, dealdata, cusip.pv, K1=0, K2=1){ +compute.delta <- function(indexdist, dealdata, tranche.pv, K1=0, K2=1){ dealweight <- dealdata$weight dealprice <- dealdata$price nT <- dim(indexdist$L)[2] @@ -301,13 +292,13 @@ compute.delta <- function(indexdist, dealdata, cusip.pv, K1=0, K2=1){ ## model1 <- lm(cusip.pv~dealprice, weights=dealweight) ## model2 <- lm(dealprice~indexpv, weights=dealweight) ## return(model1$coef[2]/model2$coef[2]) - model <- lm(cusip.pv~indexpv, weights=dealweight) + model <- lm(tranche.pv~indexpv, weights=dealweight) return( model$coef[2] ) } if(length(args)>=2){ - cusips <- args[-1] - dealnames <- unique(dealnamefromcusip(cusips)) + figis <- args[-1] + dealnames <- unique(dealnamefromfigi(figis)) }else{ params <- processzipfiles(tradedate) } @@ -320,9 +311,9 @@ cfdata <- getdealcf(params$dealnames, params$zipfiles, tradedate) ## load dist into the environment load(file.path(root.dir, "Scenarios", "Calibration", sprintf("marketdata-%s.RData", calibration.date))) -cusipdata <- getcusipcf(params, cfdata, dist, tradedate) +tranchedata <- gettranchecf(params, cfdata, dist, tradedate) save.dir <- file.path(root.dir, "Scenarios", paste0("Prices_", tradedate)) -save(cusipdata, cfdata, file=file.path(save.dir, "cashflows.RData"), +save(tranchedata, cfdata, file=file.path(save.dir, "cashflows.RData"), compress="xz") ## upload wapbasis @@ -340,21 +331,21 @@ for(dealname in names(cfdata)){ ## upload model data dawndb <- dbConn("dawndb") -for(cusip in names(cusipdata)){ - sqlstring <- paste0("SELECT updatedate from et_cusip_model_numbers", - " WHERE cusip=$1") - sqldata <- tryCatch(dbGetQuery(etdb, sqlstring, params=list(cusip)), +for(figi in names(tranchedata)){ + sqlstring <- paste0("SELECT updatedate from et_tranche_model_numbers", + " WHERE figi=$1") + sqldata <- tryCatch(dbGetQuery(etdb, sqlstring, params=list(figi)), error = function(w) logerror(w$message)) columns <- c("price", "wal", "duration", "delta", "mvoc", "mvcoverage") - values <- cusipdata[[cusip]][match(columns, names(cusipdata[[cusip]]))] + values <- tranchedata[[figi]][match(columns, names(tranchedata[[figi]]))] if(nrow(sqldata) && (tradedate %in% as.Date(sqldata$updatedate))) { - params <- c(values, cusip, as.character(tradedate)) - sqlstring <- paste0("UPDATE et_cusip_model_numbers SET price=$1, wal=$2, duration=$3,", - "delta=$4, mvoc=$5, mvcoverage=$6 WHERE cusip=$7 and updatedate=$8") + params <- c(values, figi, as.character(tradedate)) + sqlstring <- paste0("UPDATE et_tranche_model_numbers SET price=$1, wal=$2, duration=$3,", + "delta=$4, mvoc=$5, mvcoverage=$6 WHERE figi=$7 and updatedate=$8") }else{ - params <- c(list(cusip=cusip, updatedate=as.character(tradedate)), values) - sqlstring <- sprintf("INSERT INTO et_cusip_model_numbers(%s) VALUES(%s)", + params <- c(list(figi=figi, updatedate=as.character(tradedate)), values) + sqlstring <- sprintf("INSERT INTO et_tranche_model_numbers(%s) VALUES(%s)", paste(names(params), collapse=","), paste(paste0("$", 1:length(params)), collapse=",")) } @@ -362,8 +353,8 @@ for(cusip in names(cusipdata)){ r <- tryCatch(dbSendQuery(etdb, sqlstring, params=params), error = function(e) logerror(e$message)) dbClearResult(r) - identifier <- tryCatch(dbGetQuery(dawndb, "SELECT identifier from securities WHERE cusip=$1", - params=list(cusip)), + identifier <- tryCatch(dbGetQuery(dawndb, "SELECT identifier from securities WHERE figi=$1", + params=list(figi)), error = function(e) logerror(e$message)) identifier <- if(nrow(identifier)>0) identifier$identifier else NULL if(!is.null(identifier)) { |