path: root/R/load_cf.R

library(RPostgreSQL)
library(RQuantLib)
library(yaml)

args <- commandArgs(trailingOnly=TRUE)

if(.Platform$OS.type == "unix"){
  root.dir <- "/home/share/CorpCDOs"
}else{
  root.dir <- "//WDSENTINEL/share/CorpCDOs"
}

if(length(args) >= 1){
  workdate <- as.Date(args[1])
}else{
  workdate <- Sys.Date()
}

source(file.path(root.dir, "code", "R", "etdb.R"))
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"))
source(file.path(root.dir, "code", "R", "optimization.R"))
source(file.path(root.dir, "code", "R", "interpweights.R"))

calibration.date <- prevBusDay(workdate)
MarkitData <- getMarkitIRData(calibration.date)
L1m <- buildMarkitYC(MarkitData, dt = 1/12)
L2m <- buildMarkitYC(MarkitData, dt = 1/6)
L3m <-  buildMarkitYC(MarkitData)
L6m <- buildMarkitYC(MarkitData, dt = 1/2)
L12m <- buildMarkitYC(MarkitData, dt = 1)
setEvaluationDate(as.Date(MarkitData$effectiveasof))

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))
}

if(length(args)>=2){
  cusips <- args[-1]
  dealnames <- unique(dealnamefromcusip(cusips))
}else{
  pricesdir <- file.path(root.dir, "Scenarios", paste0("Prices_", workdate))
  allfiles <- list.files(pricesdir, "*.txt")
  allfiles <- unique(vapply(strsplit(allfiles, "-"), function(x) x[1], character(1)))
  allfiles <- allfiles[!(allfiles=="Total")]
  dealnames <- list.files(pricesdir, "*COLLAT_INITIAL-CF-Scen100")
  dealnames <- c(dealnames, list.files(pricesdir, "*COLLAT-CF-Scen100"))
  dealnames <- unique(vapply(strsplit(dealnames, "-"), function(x) x[1], character(1)))
  cusips <- setdiff(allfiles, dealnames)
  dealnames <- tolower(dealnames)
}

getdealcf <- function(dealnames, workdate=Sys.Date()){
    cfdata <- list()
    fields <-  c("Cashflow", "Principal", "Interest")
    flag <- FALSE
    n.scenarios <- 100
    for(dealname in dealnames){
        cfdata[[dealname]] <- list()
        r <- matrix(0, n.scenarios, 3)
        colnames(r) <- fields
        sqlstring <- sprintf("select marketvalue from latest_deal_model_numbers where dealname='%s'", dealname)
        mv <- dbGetQuery(dbCon, sqlstring)$marketvalue
        sqlstring <- sprintf(paste0("select \"Curr Collat Bal\", \"Reinv End Date\" from ",
                                    "latest_clo_universe where dealname='%s'"), dealname)
        sqldata <- dbGetQuery(dbCon, sqlstring)
        cfdata[[dealname]]$mv <- mv
        cfdata[[dealname]]$currbal <- sqldata$"Curr Collat Bal"
        config <- file.path(file.path(root.dir, "Scenarios", paste("Intex curves", workdate, sep="_"), "csv"),
                            paste0(dealname, ".config"))
        config <- yaml.load_file(config)
        if(is.na(sqldata$"Reinv End Date")||!config$reinvflag){
            tranches <- "COLLAT"
        }else{
            tranches <- c("COLLAT_INITIAL", "COLLAT_REINVEST")
        }
        for(tranche in tranches){
            for(i in 1:n.scenarios){
                filename <- paste0(paste(toupper(dealname), tranche, "CF", paste0("Scen", i), sep="-"), ".txt")
                ## we catch the error if there is an error reading the file
                ## happen if the tranche is missing in intex
                data <- tryCatch(
                        read.table(file.path(root.dir, "Scenarios", paste0("Prices_", workdate), filename),
                                   sep="\t", header=F, skip=3, colClasses="character", comment.char=""),
                        error = function(e) e)
                if(inherits(data, "error")){
                    cat(sprintf("file: %s, tranche: %s can't be loaded", dealname, tranche), "\n")
                    flag <- TRUE
                    break
                }
                data <- data[,1:4]
                colnames(data) <- c("Date", "Cashflow", "Principal", "Interest")
                data$Date <- as.Date(data$Date, "%b %d, %Y")
                if(any(is.na(data$Date))){
                    cat(sprintf("file: %s is messed up", filename), "\n")
                    flag <- TRUE
                    break
                }
                futuredates <- data$Date[data$Date>=L3m$params$tradeDate]
                pastdates <- data$Date[data$Date<L3m$params$tradeDate]
                if(length(futuredates)>0 &&
                   (i==1||length(futuredates)>length(DC$times))){
                    DC <- DiscountCurve(L3m$params, L3m$tsQuotes, yearFrac(L3m$params$tradeDate, futuredates))
                }
                pv <- c()
                for(field in fields){
                    cleanfield <- tryCatch(sanitize.column(data[,field]),
                                           warning = function(w) w)
                    if(inherits(cleanfield, "warning")){
                        cat("garbled", dealname, i, "\n")
                        flag <- TRUE
                        break
                    }else{
                        data[,field] <- cleanfield
                    }
                    if(length(futuredates) == 0){
                        df <- rep(1, length(pastdates))
                    }else{
                        df <- c(rep(1, length(pastdates)), DC$discounts[1:length(futuredates)])
                    }
                    if(nrow(data)>0){
                        pv <- c(pv, crossprod(df, data[,field]))
                    }else{
                        pv <- c(pv, 0)
                    }
                }
                if(flag){
                    break
                }else{
                    r[i,] <- pv
                }
            }
            if(flag){
                cfdata[[dealname]] <- NULL
                flag <- FALSE
                break
            }else{
                cfdata[[dealname]][[tranche]]<- r
            }
        }
        if(is.null(cfdata[[dealname]])){
            next
        }
        if(length(tranches)==2){
            cf <- cfdata[[dealname]][[tranches[1]]][,"Cashflow"] +
                cfdata[[dealname]][[tranches[2]]][,"Cashflow"]
        }else{
            cf <- cfdata[[dealname]][[tranches]][,"Cashflow"]
        }
        cfdata[[dealname]]$wapbasis <- (mean(cf)- cfdata[[dealname]]$mv)/cfdata[[dealname]]$mv
        cat(dealname, "\n")
        program <- KLfit(t(cf)/1e8, rep(1/n.scenarios, n.scenarios),
                         cfdata[[dealname]]$mv/1e8)
        cfdata[[dealname]]$weight <- program$weight
    }
    return( cfdata )
}

getcusipcf <- function(cusips, cfdata, dist, workdate=Sys.Date()){
    flag <- FALSE
    cusipdata <- list()
    dealnames <- dealnamefromcusip(cusips)
    n.scenarios <- 100
    intexfields <-  c("Cashflow", "Principal", "Interest", "Balance",
                      "Accum Interest Shortfall")
    fields <-  c("Cashflow", "Principal", "Interest")
    for(i in 1:length(cusips)){
        cusip <- cusips[i]
        dealdata <- getdealdata(dealnames[i])
        schedule <- getdealschedule(dealdata)
        r <- matrix(0, n.scenarios, 5)
        colnames(r) <- c(fields, "wal", "duration")
        sqlstring <- sprintf("select curr_balance, spread from latest_cusip_universe where cusip = '%s'", cusip)
        indicdata <- dbGetQuery(dbCon, sqlstring)
        cusipdata[[cusip]]$currbal <- indicdata$curr_balance
        cusipdata[[cusip]]$spread <- indicdata$spread
        for(j in 1:n.scenarios){
            filename <- sprintf("%s-CF-Scen%s.txt", cusip, j)
            if(!file.exists(file.path(root.dir, "Scenarios", paste0("Prices_", workdate), filename))){
                next
            }
            data <- read.table(file.path(root.dir, "Scenarios", paste0("Prices_", workdate), filename),
                               sep = "\t", header=F, colClasses="character", skip = 3, comment.char="")
            data <- data[, 1:6]
            colnames(data) <- c("Date", intexfields)
            data$Date <- as.Date(data$Date, "%b %d, %Y")
            if(any(is.na(data$Date))){
                cat(sprintf("file: %s is messed up", filename), "\n")
                flag <- TRUE
                break
            }
            futuredates <- data$Date[data$Date >= L3m$params$tradeDate]
            pastdates <- data$Date[data$Date < L3m$params$tradeDate]
            if(i==1||length(futuredates)>length(DC$times)){
                DC <- DiscountCurve(L3m$params, L3m$tsQuotes, yearFrac(L3m$params$tradeDate, futuredates))
            }
            if(length(futuredates) == 0){
                df <- rep(1, length(pastdates))
            }else{
                df <- c(rep(1, length(pastdates)), DC$discounts[1:length(futuredates)])
            }
            pv <- c()
            for(field in fields){
                cleanfield <- tryCatch(sanitize.column(data[,field]),
                                       warning = function(w)w)
                if(inherits(cleanfield, "warning")||any(is.na(cleanfield))){
                    cat(sprintf("file: %s is messed up", filename), "\n")
                    flag <- TRUE
                    break
                }else{
                    data[,field] <- cleanfield
                }
                if(nrow(data)>0){
                    pv <- c(pv, crossprod(df, data[,field]))
                }else{
                    pv <- c(pv, 0)
                }
            }
            if(flag){
                break
            }else{
                data[,"Balance" ] <- tryCatch(sanitize.column(data[,"Balance"]),
                                              warning = function(w){cat("garbled", dealname, i)})
                data[,"Accum Interest Shortfall"] <-
                    tryCatch(sanitize.column(data[,"Accum Interest Shortfall"]),
                             warning = function(w){cat("garbled", dealname, i)})
                data[,"Balance"] <- pmax(data[,"Balance"]-data[,"Accum Interest Shortfall"], 0)
                wal <- crossprod(yearFrac(workdate, data$Date)[-1], -diff(data[,"Balance"]))/
                    indicdata$curr_balance
                if(pv[1] == 0){
                    duration <- 0
                }else{
                    duration <- crossprod(data[,"Cashflow"], df * yearFrac(workdate, data$Date))/pv[1]
                }
                tryCatch(r[j,] <- c(pv, wal, duration),
                         error =function(e) browser())
            }
        }

        if(flag){
            cusipdata[[cusip]] <- NULL
            flag <- FALSE
        }else{
            cusipdata[[cusip]]$fields <- r
            cusipdata[[cusip]]$price <- 100 * crossprod(cfdata[[dealnames[i]]]$weight,
                                                        cusipdata[[cusip]]$fields[,"Cashflow"])/cusipdata[[cusip]]$currbal
            cusipdata[[cusip]]$wal <- crossprod(cfdata[[dealnames[i]]]$weight,
                                                cusipdata[[cusip]]$fields[,"wal"])
            cusipdata[[cusip]]$duration <- crossprod(cfdata[[dealnames[i]]]$weight,
                                                     cusipdata[[cusip]]$fields[,"duration"])
            cusipdata[[cusip]]$delta <-
                compute.delta(dist, cfdata[[dealnames[i]]]$weight,
                              cusipdata[[cusip]]$fields[,"Cashflow"]/cusipdata[[cusip]]$currbal,
                              workdate)
            cat("done", cusip, "\n")
        }
    }
    return(cusipdata)
}

compute.delta <- function(indexdist, dealweight, cusip.pv, workdate=Sys.Date()){
    calibration.date <- prevBusDay(workdate)
    ## we assume the index is fully funded - need to be changed depending
    ## on how we fund the swaps (hence floating coupon instead of fixed)
    cs <- couponSchedule(nextIMMDate(workdate), as.Date("2017-12-20"), "Q",
                         "FLOAT", 0.05, 0, calibration.date)
    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)
        scenariosr[,t] <- interpvalues(indexdist$R[,t], seq(0, 1, length=Ngrid), dealweight)
    }

    indexpv <- c()
    for(i in 1:length(dealweight)){
        indexpv <- c(indexpv, funded.tranche.pv(scenariosl[i,], scenariosr[i,], cs, 0, 1))
    }

    model <- lm(cusip.pv~indexpv, weights=dealweight)
    return(model$coef[2])
}

cfdata <- getdealcf(dealnames, workdate)
availablecusips <- cusips[dealnamefromcusip(cusips) %in% names(cfdata)]
## load dist into the namespace
load(file.path(root.dir, "Scenarios", "Calibration", sprintf("marketdata-%s.RData", calibration.date)))
cusipdata <- getcusipcf(availablecusips, cfdata, dist, workdate)
save.dir <- file.path(root.dir, "Scenarios", paste0("Prices_", workdate))
save(cusipdata, cfdata, file=file.path(save.dir, "cashflows.RData"),
     compress="xz")

## 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)){
    sqlstring <- sprintf(paste0("SELECT updatedate from et_cusip_model_numbers",
                                " WHERE cusip='%s'"), cusip)
    sqldata <- dbGetQuery(dbCon, sqlstring)
    if(nrow(sqldata)&& (workdate %in% sqldata$updatedate)){
        columns <- c("price", "wal", "duration", "delta")
        values <- c(cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
                    cusipdata[[cusip]]$duration, cusipdata[[cusip]]$delta)
        index <- which(!is.na(values))
        setstring <- character(length(index))
        for(i in 1:length(index)){
            setstring[i] <- sprintf("%s = %s", columns[index[i]], values[index[i]])
        }
        setstring <- paste(setstring, collapse=",")
        sqlstring <- sprintf(paste0("UPDATE et_cusip_model_numbers SET ",
                                    "%s WHERE cusip='%s' and updatedate='%s'"),
                             setstring,
                             cusip,
                             strftime(workdate))
    }else{
        columns <- c("Cusip", "price", "wal", "duration", "delta", "updatedate")
        values <- c(cusip, cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
                    cusipdata[[cusip]]$duration, cusipdata[[cusip]]$delta,
                    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",
                                    "(%s) VALUES('%s')"),
                             colstring, valstring)
    }
    dbSendQuery(dbCon, sqlstring)
}