aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--R/load_cf.R772
1 files changed, 388 insertions, 384 deletions
diff --git a/R/load_cf.R b/R/load_cf.R
index 737e04ea..e37fdef0 100644
--- a/R/load_cf.R
+++ b/R/load_cf.R
@@ -1,384 +1,388 @@
-library(RQuantLib)
-library(yaml)
-library(hash)
-library(readr)
-library(dplyr)
-library(data.table)
-library(logging)
-basicConfig()
-args <- commandArgs(trailingOnly=TRUE)
-
-root.dir <- if(.Platform$OS.type == "unix"){
- "/home/share/CorpCDOs"
-}else{
- "//WDSENTINEL/share/CorpCDOs"
-}
-
-tradedate <- if(length(args) >= 1) as.Date(args[1]) else Sys.Date()
-
-source(file.path(root.dir, "code", "R", "yieldcurve.R"))
-source(file.path(root.dir, "code", "R", "cds_utils.R"))
-source(file.path(root.dir, "code", "R", "intex_deal_functions.R"), chdir=TRUE)
-source(file.path(root.dir, "code", "R", "optimization.R"))
-source(file.path(root.dir, "code", "R", "interpweights.R"))
-source(file.path(root.dir, "code", "R", "serenitasdb.R"))
-source(file.path(root.dir, "code", "R", "creditIndex.R"))
-source(file.path(root.dir, "code", "R", "tranche_functions.R"))
-index <- creditIndex("hy27")
-index <- set.index.desc(index, tradedate)
-
-calibration.date <- addBusDay(tradedate, -1)
-exportYC(calibration.date)
-cs <- couponSchedule(IMMDate(calibration.date, noadj=TRUE), index$maturity,
- "Q", "FLOAT", 0, 0.05, calibration.date)
-
-dm <- 0
-sanitize.column <- function(vec){
- vec <- gsub(",", "", vec)
- index <- grep("\\(", vec)
- vec[index] <- unlist(lapply(index, function(l)-as.numeric(substr(vec[l], 2, nchar(vec[l])-1))))
- return(as.numeric(vec))
-}
-
-processzipfiles <- function(tradedate=Sys.Date()){
- pricesdir <- file.path(root.dir, "Scenarios", paste0("Prices_", tradedate))
- zipfiles <- file.path(pricesdir, list.files(pricesdir, "*.zip"))
- zipfiles <- zipfiles[order(file.info(zipfiles)$ctime)]
- for(n in seq_along(zipfiles)){
- zip <- zipfiles[n]
- allfiles <- unzip(zip, list=TRUE)$Name
- dealnames <- grep("COLLAT.*Scen100", allfiles, value=TRUE)
- 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)
- dealnames <- tolower(dealnames)
- if(n==1){
- dealnames.hash <- hash(dealnames, 1)
- cusips.hash <- hash(cusips, 1)
- }else{
- for( c in cusips){
- cusips.hash[c] <- n
- }
- for(d in dealnames){
- dealnames.hash[d] <- n
- }
- }
- }
- return(list(dealnames=dealnames.hash, cusips=cusips.hash, zipfiles=zipfiles))
-}
-
-getconfig <- function(dealname, tradedate){
- configfile <- file.path(root.dir, "Scenarios", paste("Intex curves", tradedate, sep="_"),
- "csv", paste0(dealname, ".config"))
- if(file.exists(configfile)){
- return(yaml.load_file(configfile))
- }else{
- return(list(reinvflag=TRUE))
- }
-}
-
-getdealcf <- function(dealnames, zipfiles, tradedate=Sys.Date()){
- cfdata <- list()
- fields <- c("Cashflow", "Principal", "Interest")
- n.scenarios <- 100
- indextodealnames <- invert(dealnames)
- for(k in keys(indextodealnames)){
- zip <- zipfiles[as.numeric(k)]
- tmp <- tempfile(tmpdir="/tmp")
- file.copy(zip, tmp)
- zip <- tmp
- for(dealname in indextodealnames[[k]]){
- loginfo(paste("processing", dealname))
- dealdata <- getdealdata(dealname, tradedate)
- alldates <- getdealschedule(dealdata, "Monthly")
- config <- getconfig(dealname, tradedate)
- alldates_floored <- ifelse(alldates >= YC$referenceDate, alldates, YC$referenceDate)
- class(alldates_floored) <- "Date"
- df <- data.table(Date = alldates,
- Discounts = YC$discount(alldates_floored),
- key="Date")
- cfdata[[dealname]] <- list(mv = dealdata$mv,
- currbal = dealdata$"Curr Collat Bal",
- principalbal = dealdata$"Principal Bal")
-
- if(is.na(dealdata$reinv_end_date)||!config$reinvflag){
- tranches <- "COLLAT"
- }else{
- tranches <- c("COLLAT_INITIAL", "COLLAT_REINVEST")
- if(dealname=="octag11"){
- tranches <- c("COLLAT_USD_INITIAL", "COLLAT_USD_REINVEST")
- }
- }
- flag <- TRUE
- ct <- list(col_date("%b %d, %Y"),
- col_character(),
- col_character(),
- col_number(), col_skip(), col_skip(),
- col_skip(), col_skip(), col_skip(),
- col_skip(), col_skip(), col_skip())
- for(tranche in tranches){
- r <- matrix(0, n.scenarios, 3)
- colnames(r) <- fields
- for(i in 1:n.scenarios){
- 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)),
- error=function(e){
- logerror(conditionMessage(e))
- NULL})
- ## browser()
- ## if (!grepl("Missing column names filled in", warnings())) {
- ## data <- NULL
- ## }
- if(is.null(data)||nrow(data)<1){
- loginfo(paste(dealname, i, tranche))
- break
- flag <- FALSE
- }
- data <- data[-1,]
- data <- data.table(data, key="Date")
- data <- tryCatch({
- data[,`:=`(Cashflow = sanitize.column(Cashflow),
- Principal = sanitize.column(Principal))]
- }, warning=function(w){
- logwarn(conditionMessage(w))
- NULL})
-
- data <- df[data, roll=TRUE]
- data$Discounts[is.na(data$Discounts)] <- 1
- #data$T[is.na(data$T)] <- 0
- r[i,] <- as.numeric(data[,list(sum(Cashflow*Discounts),
- sum(Principal*Discounts),
- sum(Interest*Discounts))])
- }
- if(flag){
- cfdata[[dealname]][[tranche]] <- r
- }
- }
- if(length(cfdata[[dealname]])<2+length(tranches)){##meaning we existed early in the above loop
- cfdata[[dealname]] <- NULL
- next
- }
- cf <- rep(0,n.scenarios)
- for(tranche in tranches){
- cf <- cf+cfdata[[dealname]][[tranche]][,"Cashflow"]
- }
- cf <- cf-min(dealdata$"Principal Bal", 0)
- cfdata[[dealname]]$price <- cf/dealdata$mv
- cfdata[[dealname]]$wapbasis <- (mean(cf)- dealdata$mv)/dealdata$mv
- cfdata[[dealname]]$weight
- cfdata[[dealname]] <- tryCatch({cfdata[[dealname]]$weight <-
- KLfit(t(cf)/1e8, rep(1/n.scenarios, n.scenarios),
- dealdata$mv/1e8)$weight;
- loginfo(paste("done", dealname));
- cfdata[[dealname]]},
- error = function(e) {
- logerror(paste("error computing the weights for deal:", dealname))
- NULL
- })
- }
- unlink(tmp)
- }
- return( cfdata )
-}
-
-getcusip_indicdata <- function(Cusip, dealname, date){
- sqlstr <- "SELECT DISTINCT isin FROM cusip_universe WHERE cusip=$1"
- r <- tryCatch(dbGetQuery(etdb, sqlstr, params=list(Cusip)),
- error = function(w) logerror(w$message))
- if(length(r$isin)>1){
- stop("We have a problem")
- }
- isinval <- r$isin[1]
- 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(isinval)){
- return(r %>% group_by(isin) %>% slice(1) %>%
- summarize(cusip, curr_balance, orig_balance, spread, curr_attach) %>%
- arrange(desc(curr_attach)) %>%
- mutate(cum_bal = cumsum(curr_balance)) %>% filter(isin==isinval) )
- }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) )
- }
-}
-
-getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){
- cusipdata <- list()
- cusips <- keys(params$cusips)
- dealnames <- dealnamefromcusip(cusips)
- cusips <- cusips[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]]]
- dealname <- dealnames[i]
- dealdata <- getdealdata(dealname, tradedate)
- alldates <- getdealschedule(dealdata, "Monthly", bdc = "Following")
- alldates_floored <- ifelse(alldates >= YC$referenceDate, alldates, YC$referenceDate)
- class(alldates_floored) <- "Date"
- T <- yearFrac(YC$referenceDate, alldates)
- df <- data.table(Date=alldates_floored,
- Discounts=YC$discount(alldates_floored),
- T=T, key="Date")
- r <- matrix(0, n.scenarios, 5)
- colnames(r) <- fields
- indicdata <- getcusip_indicdata(cusip, dealname, tradedate)
- flag <- TRUE
- ct <- list(col_date("%b %d, %Y"),
- col_number(),
- col_number(),
- 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)
- conn <- unz(zip, filename)
- data <- tryCatch(suppressWarnings(read_tsv(conn, col_types=ct)),
- error=function(e){
- logerror(conditionMessage(e))
- NULL})
- if(is.null(data)||nrow(data)<=2){
- loginfo(paste(cusip, "Scenario", j))
- flag <- FALSE
- break
- }
- data <- data[-1,]
- data <- data.table(data, key="Date")
- data[,Balance:=pmax(Balance-`Accum Interest Shortfall`, 0)]
- r[j,] <- as.numeric(df[data, roll=TRUE][,list(Cashflow=temp <- sum(Cashflow*Discounts),
- Principal=sum(Principal*Discounts),
- Interest=sum(Interest*Discounts),
- wal=sum(-diff(Balance)*T[-1])/indicdata$curr_balance,
- duration=if(temp==0) 0 else sum(Cashflow * Discounts * T)/temp)])
- }
- if(indicdata$curr_balance==0){
- cusip.pv <- r[,"Cashflow"]/indicdata$orig_balance
- }else{
- cusip.pv <- 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))
- }
- }
-
- return(cusipdata)
-}
-
-
-compute.delta <- function(indexdist, dealdata, cusip.pv, K1=0, K2=1){
- dealweight <- dealdata$weight
- dealprice <- dealdata$price
- nT <- dim(indexdist$L)[2]
- Ngrid <- dim(indexdist$L)[1]
- scenariosl <- matrix(0, length(dealweight), nT)
- scenariosr <- matrix(0, length(dealweight), nT)
- for(t in 1:nT){
- scenariosl[,t] <- interpvalues(indexdist$L[,t], seq(0, 1, length=Ngrid), dealweight)
- ## numerical artefact, but we want scenariosr[i,] + scenariosl[i,] <= 1 at all times
- scenariosr[,t] <- pmin(interpvalues(indexdist$R[,t], seq(0, 1, length=Ngrid), dealweight),
- 1-scenariosl[,t])
- }
- ## we assume the index is fully funded - need to be changed depending
- ## on how we fund the swaps (hence floating coupon instead of fixed)
- indexpv <- c()
- for(i in 1:length(dealweight)){
- indexpv <- c(indexpv, funded.tranche.pv(scenariosl[i,], scenariosr[i,], cs, K1, K2, TRUE))
- }
- ## 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)
- return( model$coef[2] )
-}
-
-if(length(args)>=2){
- cusips <- args[-1]
- dealnames <- unique(dealnamefromcusip(cusips))
-}else{
- params <- processzipfiles(tradedate)
-}
-
-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)
-save.dir <- file.path(root.dir, "Scenarios", paste0("Prices_", tradedate))
-save(cusipdata, cfdata, file=file.path(save.dir, "cashflows.RData"),
- compress="xz")
-
-## upload wapbasis
-sqlstring <- paste0("UPDATE et_deal_model_numbers SET ",
- "wapbasis = $1 WHERE dealname= $2 AND updatedate = $3")
-for(dealname in names(cfdata)){
- tryCatch(dbGetQuery(etdb, sqlstring, params = list(cfdata[[dealname]]$wapbasis*100,
- dealname,
- tradedate)),
- error = function(w) {
- logerror(w$message)
- })
-}
-
-## 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)),
- error = function(w) logerror(w$message))
- columns <- c("price", "wal", "duration", "delta", "mvoc", "mvcoverage")
- values <- cusipdata[[cusip]][match(columns, names(cusipdata[[cusip]]))]
- 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")
-
- }else{
- params <- c(list(cusip=cusip, updatedate=as.character(tradedate)), values)
- sqlstring <- sprintf("INSERT INTO et_cusip_model_numbers(%s) VALUES(%s)",
- paste(names(params), collapse=","),
- paste(paste0("$", 1:length(params)), collapse=","))
- }
- tryCatch(dbGetQuery(etdb, sqlstring, params=params),
- error = function(e) logerror(e$message))
- identifier <- tryCatch(dbGetQuery(dawndb, "SELECT identifier from securities WHERE cusip=$1",
- params=list(cusip)),
- error = function(e) logerror(e$message))
- identifier <- if(nrow(identifier)>0) identifier$identifier else NULL
- if(!is.null(identifier)){
- if(nrow(sqldata) && (tradedate %in% as.Date(sqldata$updatedate))){
- params <- c(values[1:4], identifier, as.character(tradedate))
- sqlstring <- paste0("UPDATE risk_numbers SET model_price=$1, wal=$2, duration=$3,",
- "index_delta='HY',delta=$4 WHERE identifier=$5 and date=$6")
- }else{
- sqlstring <- paste0("INSERT INTO risk_numbers(identifier, date, index_delta,",
- "model_price, wal, duration, delta) ",
- "VALUES($1, $2, $3, $4, $5, $6, $7)")
- params <- c(identifier, as.character(tradedate), 'HY', values[1:4])
- }
-
- tryCatch(dbGetQuery(dawndb, sqlstring, params=params),
- error = function(w) logerror(w$message))
- }
-}
-dbDisconnect(dawndb)
+library(RQuantLib)
+library(yaml)
+library(hash)
+library(readr)
+library(dplyr)
+library(data.table)
+library(logging)
+basicConfig()
+args <- commandArgs(trailingOnly=TRUE)
+
+root.dir <- if(.Platform$OS.type == "unix"){
+ "/home/share/CorpCDOs"
+}else{
+ "//WDSENTINEL/share/CorpCDOs"
+}
+
+tradedate <- if(length(args) >= 1) as.Date(args[1]) else Sys.Date()
+
+source(file.path(root.dir, "code", "R", "yieldcurve.R"))
+source(file.path(root.dir, "code", "R", "cds_utils.R"))
+source(file.path(root.dir, "code", "R", "intex_deal_functions.R"), chdir=TRUE)
+source(file.path(root.dir, "code", "R", "optimization.R"))
+source(file.path(root.dir, "code", "R", "interpweights.R"))
+source(file.path(root.dir, "code", "R", "serenitasdb.R"))
+source(file.path(root.dir, "code", "R", "creditIndex.R"))
+source(file.path(root.dir, "code", "R", "tranche_functions.R"))
+index <- creditIndex("hy27")
+index <- set.index.desc(index, tradedate)
+
+calibration.date <- addBusDay(tradedate, -1)
+exportYC(calibration.date)
+cs <- couponSchedule(IMMDate(calibration.date, noadj=TRUE), index$maturity,
+ "Q", "FLOAT", 0, 0.05, calibration.date)
+
+dm <- 0
+sanitize.column <- function(vec){
+ vec <- gsub(",", "", vec)
+ index <- grep("\\(", vec)
+ vec[index] <- unlist(lapply(index, function(l)-as.numeric(substr(vec[l], 2, nchar(vec[l])-1))))
+ return(as.numeric(vec))
+}
+
+processzipfiles <- function(tradedate=Sys.Date()){
+ pricesdir <- file.path(root.dir, "Scenarios", paste0("Prices_", tradedate))
+ zipfiles <- file.path(pricesdir, list.files(pricesdir, "*.zip"))
+ zipfiles <- zipfiles[order(file.info(zipfiles)$ctime)]
+ for(n in seq_along(zipfiles)){
+ zip <- zipfiles[n]
+ allfiles <- unzip(zip, list=TRUE)$Name
+ dealnames <- grep("COLLAT.*Scen100", allfiles, value=TRUE)
+ 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)
+ dealnames <- tolower(dealnames)
+ if(n==1){
+ dealnames.hash <- hash(dealnames, 1)
+ cusips.hash <- hash(cusips, 1)
+ }else{
+ for( c in cusips){
+ cusips.hash[c] <- n
+ }
+ for(d in dealnames){
+ dealnames.hash[d] <- n
+ }
+ }
+ }
+ return(list(dealnames=dealnames.hash, cusips=cusips.hash, zipfiles=zipfiles))
+}
+
+getconfig <- function(dealname, tradedate){
+ configfile <- file.path(root.dir, "Scenarios", paste("Intex curves", tradedate, sep="_"),
+ "csv", paste0(dealname, ".config"))
+ if(file.exists(configfile)){
+ return(yaml.load_file(configfile))
+ }else{
+ return(list(reinvflag=TRUE))
+ }
+}
+
+getdealcf <- function(dealnames, zipfiles, tradedate=Sys.Date()){
+ cfdata <- list()
+ fields <- c("Cashflow", "Principal", "Interest")
+ n.scenarios <- 100
+ indextodealnames <- invert(dealnames)
+ for(k in keys(indextodealnames)){
+ zip <- zipfiles[as.numeric(k)]
+ tmp <- tempfile(tmpdir="/tmp")
+ file.copy(zip, tmp)
+ zip <- tmp
+ for(dealname in indextodealnames[[k]]){
+ loginfo(paste("processing", dealname))
+ dealdata <- getdealdata(dealname, tradedate)
+ alldates <- getdealschedule(dealdata, "Monthly")
+ config <- getconfig(dealname, tradedate)
+ alldates_floored <- ifelse(alldates >= YC$referenceDate, alldates, YC$referenceDate)
+ class(alldates_floored) <- "Date"
+ df <- data.table(Date = alldates,
+ Discounts = YC$discount(alldates_floored),
+ key="Date")
+ cfdata[[dealname]] <- list(mv = dealdata$mv,
+ currbal = dealdata$"Curr Collat Bal",
+ principalbal = dealdata$"Principal Bal")
+
+ if(is.na(dealdata$reinv_end_date)||!config$reinvflag){
+ tranches <- "COLLAT"
+ }else{
+ tranches <- c("COLLAT_INITIAL", "COLLAT_REINVEST")
+ if(dealname=="octag11"){
+ tranches <- c("COLLAT_USD_INITIAL", "COLLAT_USD_REINVEST")
+ }
+ }
+ flag <- TRUE
+ ct <- list(col_date("%b %d, %Y"),
+ col_character(),
+ col_character(),
+ col_number(), col_skip(), col_skip(),
+ col_skip(), col_skip(), col_skip(),
+ col_skip(), col_skip(), col_skip())
+ for(tranche in tranches){
+ r <- matrix(0, n.scenarios, 3)
+ colnames(r) <- fields
+ for(i in 1:n.scenarios){
+ 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)),
+ error=function(e){
+ logerror(conditionMessage(e))
+ NULL})
+ ## browser()
+ ## if (!grepl("Missing column names filled in", warnings())) {
+ ## data <- NULL
+ ## }
+ if(is.null(data)||nrow(data)<1){
+ loginfo(paste(dealname, i, tranche))
+ break
+ flag <- FALSE
+ }
+ data <- data[-1,]
+ data <- data.table(data, key="Date")
+ data <- tryCatch({
+ data[,`:=`(Cashflow = sanitize.column(Cashflow),
+ Principal = sanitize.column(Principal))]
+ }, warning=function(w){
+ logwarn(conditionMessage(w))
+ NULL})
+
+ data <- df[data, roll=TRUE]
+ data$Discounts[is.na(data$Discounts)] <- 1
+ #data$T[is.na(data$T)] <- 0
+ r[i,] <- as.numeric(data[,list(sum(Cashflow*Discounts),
+ sum(Principal*Discounts),
+ sum(Interest*Discounts))])
+ }
+ if(flag){
+ cfdata[[dealname]][[tranche]] <- r
+ }
+ }
+ if(length(cfdata[[dealname]])<2+length(tranches)){##meaning we existed early in the above loop
+ cfdata[[dealname]] <- NULL
+ next
+ }
+ cf <- rep(0,n.scenarios)
+ for(tranche in tranches){
+ cf <- cf+cfdata[[dealname]][[tranche]][,"Cashflow"]
+ }
+ cf <- cf-min(dealdata$"Principal Bal", 0)
+ cfdata[[dealname]]$price <- cf/dealdata$mv
+ cfdata[[dealname]]$wapbasis <- (mean(cf)- dealdata$mv)/dealdata$mv
+ cfdata[[dealname]]$weight
+ cfdata[[dealname]] <- tryCatch({cfdata[[dealname]]$weight <-
+ KLfit(t(cf)/1e8, rep(1/n.scenarios, n.scenarios),
+ dealdata$mv/1e8)$weight;
+ loginfo(paste("done", dealname));
+ cfdata[[dealname]]},
+ error = function(e) {
+ logerror(paste("error computing the weights for deal:", dealname))
+ NULL
+ })
+ }
+ unlink(tmp)
+ }
+ return( cfdata )
+}
+
+getcusip_indicdata <- function(Cusip, dealname, date){
+ sqlstr <- "SELECT DISTINCT isin FROM cusip_universe WHERE cusip=$1"
+ r <- tryCatch(dbGetQuery(etdb, sqlstr, params=list(Cusip)),
+ error = function(w) logerror(w$message))
+ if(length(r$isin)>1){
+ stop("We have a problem")
+ }
+ isinval <- r$isin[1]
+ 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(isinval)){
+ return(r %>% group_by(isin) %>% slice(1) %>%
+ summarize(cusip, curr_balance, orig_balance, spread, curr_attach) %>%
+ arrange(desc(curr_attach)) %>%
+ mutate(cum_bal = cumsum(curr_balance)) %>% filter(isin==isinval) )
+ }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) )
+ }
+}
+
+getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){
+ cusipdata <- list()
+ cusips <- keys(params$cusips)
+ dealnames <- dealnamefromcusip(cusips)
+ cusips <- cusips[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]]]
+ dealname <- dealnames[i]
+ dealdata <- getdealdata(dealname, tradedate)
+ alldates <- getdealschedule(dealdata, "Monthly", bdc = "Following")
+ alldates_floored <- ifelse(alldates >= YC$referenceDate, alldates, YC$referenceDate)
+ class(alldates_floored) <- "Date"
+ T <- yearFrac(YC$referenceDate, alldates)
+ df <- data.table(Date=alldates_floored,
+ Discounts=YC$discount(alldates_floored),
+ T=T, key="Date")
+ r <- matrix(0, n.scenarios, 5)
+ colnames(r) <- fields
+ indicdata <- getcusip_indicdata(cusip, dealname, tradedate)
+ flag <- TRUE
+ ct <- list(col_date("%b %d, %Y"),
+ col_number(),
+ col_number(),
+ 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)
+ conn <- unz(zip, filename)
+ data <- tryCatch(suppressWarnings(read_tsv(conn, col_types=ct)),
+ error=function(e){
+ logerror(conditionMessage(e))
+ NULL})
+ if(is.null(data)||nrow(data)<=2){
+ loginfo(paste(cusip, "Scenario", j))
+ flag <- FALSE
+ break
+ }
+ data <- data[-1,]
+ data <- data.table(data, key="Date")
+ data[,Balance:=pmax(Balance-`Accum Interest Shortfall`, 0)]
+ r[j,] <- as.numeric(df[data, roll=TRUE][,list(Cashflow=temp <- sum(Cashflow*Discounts),
+ Principal=sum(Principal*Discounts),
+ Interest=sum(Interest*Discounts),
+ wal=sum(-diff(Balance)*T[-1])/indicdata$curr_balance,
+ duration=if(temp==0) 0 else sum(Cashflow * Discounts * T)/temp)])
+ }
+ if(indicdata$curr_balance==0){
+ cusip.pv <- r[,"Cashflow"]/indicdata$orig_balance
+ }else{
+ cusip.pv <- 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))
+ }
+ }
+
+ return(cusipdata)
+}
+
+
+compute.delta <- function(indexdist, dealdata, cusip.pv, K1=0, K2=1){
+ dealweight <- dealdata$weight
+ dealprice <- dealdata$price
+ nT <- dim(indexdist$L)[2]
+ Ngrid <- dim(indexdist$L)[1]
+ scenariosl <- matrix(0, length(dealweight), nT)
+ scenariosr <- matrix(0, length(dealweight), nT)
+ for(t in 1:nT){
+ scenariosl[,t] <- interpvalues(indexdist$L[,t], seq(0, 1, length=Ngrid), dealweight)
+ ## numerical artefact, but we want scenariosr[i,] + scenariosl[i,] <= 1 at all times
+ scenariosr[,t] <- pmin(interpvalues(indexdist$R[,t], seq(0, 1, length=Ngrid), dealweight),
+ 1-scenariosl[,t])
+ }
+ ## we assume the index is fully funded - need to be changed depending
+ ## on how we fund the swaps (hence floating coupon instead of fixed)
+ indexpv <- c()
+ for(i in 1:length(dealweight)){
+ indexpv <- c(indexpv, funded.tranche.pv(scenariosl[i,], scenariosr[i,], cs, K1, K2, TRUE))
+ }
+ ## 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)
+ return( model$coef[2] )
+}
+
+if(length(args)>=2){
+ cusips <- args[-1]
+ dealnames <- unique(dealnamefromcusip(cusips))
+}else{
+ params <- processzipfiles(tradedate)
+}
+
+if(interactive()) {
+ options(error=recover)
+}
+
+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)
+save.dir <- file.path(root.dir, "Scenarios", paste0("Prices_", tradedate))
+save(cusipdata, cfdata, file=file.path(save.dir, "cashflows.RData"),
+ compress="xz")
+
+## upload wapbasis
+sqlstring <- paste0("UPDATE et_deal_model_numbers SET ",
+ "wapbasis = $1 WHERE dealname= $2 AND updatedate = $3")
+for(dealname in names(cfdata)){
+ tryCatch(dbGetQuery(etdb, sqlstring, params = list(cfdata[[dealname]]$wapbasis*100,
+ dealname,
+ tradedate)),
+ error = function(w) {
+ logerror(w$message)
+ })
+}
+
+## 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)),
+ error = function(w) logerror(w$message))
+ columns <- c("price", "wal", "duration", "delta", "mvoc", "mvcoverage")
+ values <- cusipdata[[cusip]][match(columns, names(cusipdata[[cusip]]))]
+ 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")
+
+ }else{
+ params <- c(list(cusip=cusip, updatedate=as.character(tradedate)), values)
+ sqlstring <- sprintf("INSERT INTO et_cusip_model_numbers(%s) VALUES(%s)",
+ paste(names(params), collapse=","),
+ paste(paste0("$", 1:length(params)), collapse=","))
+ }
+ tryCatch(dbGetQuery(etdb, sqlstring, params=params),
+ error = function(e) logerror(e$message))
+ identifier <- tryCatch(dbGetQuery(dawndb, "SELECT identifier from securities WHERE cusip=$1",
+ params=list(cusip)),
+ error = function(e) logerror(e$message))
+ identifier <- if(nrow(identifier)>0) identifier$identifier else NULL
+ if(!is.null(identifier)){
+ if(nrow(sqldata) && (tradedate %in% as.Date(sqldata$updatedate))){
+ params <- c(values[1:4], identifier, as.character(tradedate))
+ sqlstring <- paste0("UPDATE risk_numbers SET model_price=$1, wal=$2, duration=$3,",
+ "index_delta='HY',delta=$4 WHERE identifier=$5 and date=$6")
+ }else{
+ sqlstring <- paste0("INSERT INTO risk_numbers(identifier, date, index_delta,",
+ "model_price, wal, duration, delta) ",
+ "VALUES($1, $2, $3, $4, $5, $6, $7)")
+ params <- c(identifier, as.character(tradedate), 'HY', values[1:4])
+ }
+
+ tryCatch(dbGetQuery(dawndb, sqlstring, params=params),
+ error = function(w) logerror(w$message))
+ }
+}
+dbDisconnect(dawndb)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-21 22:52:02 +0000