path: root/R/load_cf.R

library(RPostgreSQL)
library(RQuantLib)
library(yaml)
library(hash)
library(data.table)
options(stringsAsFactors = FALSE)
args <- commandArgs(trailingOnly=TRUE)

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

if(length(args) >= 1){
  tradedate <- as.Date(args[1])
}else{
  tradedate <- 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"))
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("hy23")
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)

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]]){
            dealdata <- getdealdata(dealname, tradedate)
            alldates <- getdealschedule(dealdata, "1 month")
            config <- getconfig(dealname, tradedate)

            T <- ifelse(alldates>=L3m$params$tradeDate, yearFrac(L3m$params$tradeDate, alldates), 0)
            DC <- DiscountCurve(L3m$params, L3m$tsQuotes, T)
            df <- data.table(Date=alldates,
                             Discounts=DC$discounts,
                             T=T, key="Date")
            cfdata[[dealname]] <- list(mv = dealdata$mv, currbal = dealdata$"Curr Collat Bal")

            if(is.na(dealdata$reinv_end_date)||!config$reinvflag){
                tranches <- "COLLAT"
            }else{
                tranches <- c("COLLAT_INITIAL", "COLLAT_REINVEST")
            }
            for(tranche in tranches){
                cfdata[[dealname]][[tranche]] <- callCC(function(k){
                    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 <- read.table(conn, sep="\t", header=TRUE, colClasses="character", comment.char="")
                        data <- data.table(data)
                        if("Error" %in% names(data)){
                            k(NULL)
                        }
                        ## data <- fread(file.path(root.dir, "Scenarios", paste0("Prices_", tradedate), filename),
                        ##               sep="\t", colClasses="character")
                        data <- data[-c(1,2),][,`:=`(Date=as.Date(Date, "%b %d, %Y"),
                                                     Cashflow = sanitize.column(Cashflow),
                                                     Principal = sanitize.column(Principal),
                                                     Interest = sanitize.column(Interest))]
                        setkey(data, "Date")
                        r[i,] <- as.numeric(df[data, roll=TRUE][,list(sum(Cashflow*Discounts),
                                                         sum(Principal*Discounts),
                                                         sum(Interest*Discounts))])
                    }
                    k(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
            program <- tryCatch(KLfit(t(cf)/1e8, rep(1/n.scenarios, n.scenarios),
                                      dealdata$mv/1e8), error=function(e) e)
            if(inherits(program, "error")){
                cat("error computing the weights for deal:", dealname,"\n")
                cfdata[[dealname]] <- NULL
                next
            }else{
                cfdata[[dealname]]$weight <- program$weight
                cat(dealname, "\n")
            }
        }
        unlink(tmp)
    }
    return( cfdata )
}

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]
        zip <- params$zipfiles[params$cusips[[cusip]]]
        dealname <- dealnames[i]
        dealdata <- getdealdata(dealname, tradedate)
        alldates <- getdealschedule(dealdata, "1 month", adjust=TRUE)
        T <- ifelse(alldates>=L3m$params$tradeDate, yearFrac(L3m$params$tradeDate, alldates), 0)
        DC <- DiscountCurve(L3m$params, L3m$tsQuotes, T)
        df <- data.table(Date=alldates,
                         Discounts=DC$discounts,
                         T=T, key="Date")
        r <- matrix(0, n.scenarios, 5)
        colnames(r) <- fields
        sqlstring <- sprintf("select curr_balance, spread from historical_cusip_universe('%s', '%s')",
                             cusip, tradedate)
        indicdata <- dbGetQuery(dbCon, sqlstring)
        cusipdata[[cusip]] <- callCC(function(k){
            for(j in 1:n.scenarios){
                filename <- sprintf("%s-CF-Scen%s.txt", cusip, j)
                conn <- unz(zip, filename)
                data <- data.table(read.table(conn, sep="\t", header=TRUE, colClasses="character",
                                              check.names=FALSE))
                data <- data[-c(1,2),]
                if("Error" %in% names(data)){
                    k(NULL)
                }
                data[,`:=`(Date=as.Date(Date, "%b %d, %Y"),
                           Cashflow=sanitize.column(Cashflow),
                           Principal=sanitize.column(Principal),
                           Interest=sanitize.column(Interest),
                           Balance=sanitize.column(Balance),
                           `Accum Interest Shortfall`=sanitize.column(`Accum Interest Shortfall`))]
                data[,Balance:=pmax(Balance-`Accum Interest Shortfall`, 0)]
                setkey(data, "Date")
                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)])
            }
            k(list(currbal=indicdata$curr_balance,
                   spread=indicdata$spread,
                   Cashflow=temp <- crossprod(cfdata[[dealname]]$weight, r[,"Cashflow"]),
                   wal = crossprod(cfdata[[dealname]]$weight, r[,"wal"]),
                   duration = crossprod(cfdata[[dealname]]$weight, r[,"duration"]),
                   price = 100 * temp/indicdata$curr_balance,
                   delta = compute.delta(dist, cfdata[[dealname]], r[,"Cashflow"]/indicdata$curr_balance),
                   fields=r))
        })
        cat("done", cusip, "\n")
    }

    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)
        scenariosr[,t] <- interpvalues(indexdist$R[,t], seq(0, 1, length=Ngrid), dealweight)
    }
    ## 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
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(tradedate))
    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)&& (tradedate %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(tradedate))
    }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(tradedate))
        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)
}