aboutsummaryrefslogtreecommitdiffstats
path: root/R
diff options
context:
space:
mode:
Diffstat (limited to 'R')
-rw-r--r--R/build_portfolios.R56
-rw-r--r--R/calibrate_tranches_BC.R6
-rw-r--r--R/intex_deal_functions.R22
-rw-r--r--R/load_cf.R61
-rw-r--r--R/thetas-durations.R30
5 files changed, 92 insertions, 83 deletions
diff --git a/R/build_portfolios.R b/R/build_portfolios.R
index 4e2f1584..a4465521 100644
--- a/R/build_portfolios.R
+++ b/R/build_portfolios.R
@@ -2,13 +2,16 @@ library("RQuantLib")
library("yaml")
args <- commandArgs(trailingOnly=TRUE)
-code.dir <- Sys.getenv("CODE_DIR")
if(.Platform$OS.type == "unix"){
root.dir <- "/home/share/CorpCDOs"
}else{
root.dir <- "//WDSENTINEL/share/CorpCDOs"
}
+code.dir <- Sys.getenv("CODE_DIR")
+if(code.dir==""){
+ code.dir <- root.dir
+}
source(file.path(code.dir, "code", "R", "intex_deal_functions.R"), chdir=TRUE)
source(file.path(code.dir, "code", "R", "yieldcurve.R"))
@@ -76,33 +79,32 @@ for(i in seq_along(dealnames)){
cat(deal.name, "... done\n")
dealupdatedate <- currdealnames$updatedate[currdealnames$dealname %in% deal.name]
if(length(dealupdatedate) && dealupdatedate == workdate){
- sqlstring <- sprintf(
- paste0("UPDATE et_deal_model_numbers SET dealspread5y = %s,",
- "dealspread = %s, cdopercentage = %s, stalepercentage= %s, ",
- "marketvalue = %s",
- "WHERE dealname= '%s' and updatedate = '%s'"),
- deal.spread5y,
- deal.spreadatmaturity,
- deal.portfolio$cdopercentage,
- deal.portfolio$stale,
- mv,
- deal.name,
- as.Date(workdate))
- dbSendQuery(dbCon, sqlstring)
+ sqlstring <- paste0("UPDATE et_deal_model_numbers SET dealspread5y = $1,",
+ "dealspread = $2, cdopercentage = $3, stalepercentage= $4, ",
+ "marketvalue = $5",
+ "WHERE dealname= $6 and updatedate = $7")
+ r <- dbSendQuery(sqlstring,params = list(deal.spread5y,
+ deal.spreadatmaturity,
+ deal.portfolio$cdopercentage,
+ deal.portfolio$stale,
+ mv,
+ deal.name,
+ workdate))
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
+ }
}else{
- sqlstring <- sprintf(paste0("INSERT INTO et_deal_model_numbers",
- "(dealname, cdopercentage, stalepercentage,",
- "dealspread5y, marketvalue, updatedate, dealspread) ",
- "VALUES('%s', %s, %s, %s, %s, '%s', %s)"),
- deal.name,
- deal.portfolio$cdopercentage,
- deal.portfolio$stale,
- deal.spread5y,
- mv,
- workdate,
- deal.spreadatmaturity
- )
- dbSendQuery(dbCon, sqlstring)
+ sqlstring <- paste0("INSERT INTO et_deal_model_numbers",
+ "(dealname, cdopercentage, stalepercentage,",
+ "dealspread5y, marketvalue, updatedate, dealspread) ",
+ "VALUES($1, $2, $3, $4, $5, $6, $7)")
+ r <- dbSendQuery(dbCon, sqlstring, params = list(deal.name,
+ deal.portfolio$cdopercentage,
+ deal.portfolio$stale,
+ deal.spread5y,mv,workdate,deal.spreadatmaturity))
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
+ }
currdealnames <- c(currdealnames, deal.name)
}
}
diff --git a/R/calibrate_tranches_BC.R b/R/calibrate_tranches_BC.R
index 38294f0f..345cca70 100644
--- a/R/calibrate_tranches_BC.R
+++ b/R/calibrate_tranches_BC.R
@@ -118,8 +118,10 @@ for(i in seq_along(runs$name)){
cat(csvheaders(index), sep="\n", file=filename)
}
cat(tocsv(index), sep="\n", file=filename, append=TRUE)
- dbSendQuery(serenitasdb, tosql(index))
+ r <- dbSendQuery(serenitasdb, tosql(index))
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
+ }
loginfo("done")
}
}
-dbDisconnect(serenitasdb)
diff --git a/R/intex_deal_functions.R b/R/intex_deal_functions.R
index f340edeb..37f388f4 100644
--- a/R/intex_deal_functions.R
+++ b/R/intex_deal_functions.R
@@ -15,16 +15,20 @@ source("cds_functions_generic.R")
source("etdb.R")
getdealdata <- function(dealname, workdate){
- sqlstring <- paste0("select marketvalue from et_deal_model_numbers where dealname='%s' and ",
+ sqlstring <- paste0("select marketvalue from et_deal_model_numbers where dealname=$1 and ",
"updatedate in (select max(updatedate) from et_deal_model_numbers where ",
- "dealname = '%s' and updatedate<='%s')")
- sqlstring <- sprintf(sqlstring, dealname, dealname, workdate)
- mv <- dbGetQuery(dbCon, sqlstring)$marketvalue
- sqlstring <- sprintf(paste0("select \"Curr Collat Bal\", reinv_end_date, ",
- "first_pay_date , maturity, \"Principal Bal\" , pay_day from ",
- "historical_clo_universe('%s', '%s')"), dealname, workdate)
- dealdata <- dbGetQuery(dbCon, sqlstring)
+ "dealname = $2 and updatedate<=$3)")
+ mv <- dbGetQuery(dbCon, sqlstring,
+ params = list(dealname, dealname, workdate))$marketvalue
+
+ sqlstring <- paste0("select \"Curr Collat Bal\", reinv_end_date, ",
+ "first_pay_date , maturity, \"Principal Bal\" , pay_day from ",
+ "historical_clo_universe($1, $2)")
+ dealdata <- dbGetQuery(dbCon, sqlstring, params=list(dealname, workdate))
dealdata$mv <- mv
+ dealdata$maturity <- as.Date(dealdata$maturity)
+ dealdata$first_pay_date <- as.Date(dealdata$first_pay_date)
+ dealdata$reinv_end_date <- as.Date(dealdata$reinv_end_date)
return(dealdata)
}
@@ -246,6 +250,8 @@ buildSC <- function(line.item, reinvdate, dealmaturity, global.params, startdate
buildSC.portfolio <- function(dealname, dealdata, cusipdata, global.params, startdate = Sys.Date()) {
collatdata <- data.table(getcollateral(dealname, startdate))
setkey(collatdata, "cusip")
+ collatdata$nextpaydate <- as.Date(collatdata$nextpaydate)
+ collatdata$maturity <- as.Date(collatdata$maturity)
## replace the cdo fields by bloomberg data
collatdata[cusipdata,
`:=`(maturity=i.maturity, fixedorfloat=i.fixedorfloat,
diff --git a/R/load_cf.R b/R/load_cf.R
index a3aec9c7..47291658 100644
--- a/R/load_cf.R
+++ b/R/load_cf.R
@@ -5,7 +5,7 @@ library(hash)
library(data.table)
options(stringsAsFactors = FALSE)
args <- commandArgs(trailingOnly=TRUE)
-options(warn=2)
+##options(warn=2)
if(.Platform$OS.type == "unix"){
root.dir <- "/home/share/CorpCDOs"
@@ -286,45 +286,36 @@ 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)){
- 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)
+ r <- dbSendQuery(dbCon, sqlstring, params = list(cfdata[[dealname]]$wapbasis*100,
+ dealname,
+ tradedate))
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
+ }
}
## 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))
+ sqlstring <- paste0("SELECT updatedate from et_cusip_model_numbers",
+ " WHERE cusip=$1")
+ sqldata <- dbGetQuery(dbCon, sqlstring, params=list(cusip))
+ columns <- c("price", "wal", "duration", "delta")
+ 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 WHERE cusip=$5 and updatedate=$6")
+
}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)
+ sqlstring <- paste0("INSERT INTO et_cusip_model_numbers ",
+ "VALUES($1, $2, $3, $4, $5, $6)")
+ params <- c(cusip, values, as.character(tradedate))
+ }
+ r <- dbSendQuery(dbCon, sqlstring, params=params)
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
}
- dbSendQuery(dbCon, sqlstring)
}
diff --git a/R/thetas-durations.R b/R/thetas-durations.R
index 98ddd822..c441140d 100644
--- a/R/thetas-durations.R
+++ b/R/thetas-durations.R
@@ -31,6 +31,7 @@ get.indexquotes <- function(index, series, tenors=c("3yr", "5yr", "7yr"), onlymi
"AS ct(date date, %s)")
stmt <- sprintf(sqlstr, index, series, arraystring1, arraystring2)
df <- dbGetQuery(serenitasdb, stmt)
+ df$date <- as.Date(df$date)
return( df )
}
@@ -39,6 +40,7 @@ get.indexmaturity <- function(index, series){
"from index_maturity where index=$1",
"and series=$2 order by maturity")
df <- dbGetQuery(serenitasdb, sqlstr, params=list(index, series))
+ df$maturity <- as.Date(df$maturity)
return( df )
}
@@ -95,10 +97,10 @@ convertNA <- function(x){
index <- 'HY'
tenors <- c("3yr", "5yr", "7yr")
recov <- 0.3
-sqlstr.duration <- paste("UPDATE index_quotes set duration=%s where date='%s' and index='%s'",
- "and series=%s and tenor='%s'")
-sqlstr.theta <- paste("UPDATE index_quotes set theta=%s where date='%s' and index='%s'",
- "and series=%s and tenor='%s'")
+sqlstr.duration <- paste("UPDATE index_quotes set duration=$1 where date=$2 and index=$3",
+ "and series=$4 and tenor=$5")
+sqlstr.theta <- paste("UPDATE index_quotes set theta=$1 where date=$2 and index=$3",
+ "and series=$4 and tenor=$5")
for(series in c(16, 17, 18, 19, 20, 21, 22, 23)){
indexquotes <- get.indexquotes(index, series)
@@ -135,15 +137,21 @@ for(series in c(16, 17, 18, 19, 20, 21, 22, 23)){
colnames(df.thetas) <- c("date", tenors)
for(i in 1:nrow(df.durations)){
for(tenor in tenors){
- if(!is.na(df.durations[i,tenor])){
- stmt <- sprintf(sqlstr.duration, df.durations[i,tenor],
- df.durations[i,"date"], index, series, tenor)
- dbSendQuery(serenitasdb, stmt)
+ if(!is.na(df.durations[i, tenor])){
+ r <- dbSendQuery(serenitasdb, sqlstr.duration,
+ params = list(df.durations[i, tenor],
+ df.durations[i,"date"], index, series, tenor))
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
+ }
}
if(!is.na(df.thetas[i,tenor])){
- stmt <- sprintf(sqlstr.theta, df.thetas[i,tenor],
- df.thetas[i,"date"], index, series, tenor)
- dbSendQuery(serenitasdb, stmt)
+ r <- dbSendQuery(serenitasdb, sqlstr.theta,
+ params = list(df.thetas[i, tenor],
+ df.thetas[i, "date"], index, series, tenor))
+ if(dbHasCompleted(r)){
+ dbClearResult(r)
+ }
}
}
}
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2026-01-09 00:55:16 +0000