aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--R/build_SC.R14
-rw-r--r--R/load_cf.R63
-rw-r--r--python/intex_scenarios.py108
3 files changed, 115 insertions, 70 deletions
diff --git a/R/build_SC.R b/R/build_SC.R
index 631325aa..b763f3a0 100644
--- a/R/build_SC.R
+++ b/R/build_SC.R
@@ -200,9 +200,17 @@ generate_portfolio_helper <- function(cusips){
r <- c()
s <- c()
for(dealname in uniqdealnames){
- r <- c(r, paste(toupper(dealname), "COLLAT_INITIAL", sep=","))
- r <- c(r, paste(toupper(dealname), "COLLAT_REINVEST", sep=","))
- s <- c(s, rep(paste0(dealname, ".sss"), 2))
+ sqlstring <- sprintf(paste0("SELECT \"Reinv End Date\" FROM latest_clo_universe",
+ " WHERE dealname='%s'"), dealname)
+ data <- dbGetQuery(dbCon, sqlstring)
+ if(!is.na(data$"Reinv End Date")){
+ r <- c(r, paste(toupper(dealname), "COLLAT_INITIAL", sep=","))
+ r <- c(r, paste(toupper(dealname), "COLLAT_REINVEST", sep=","))
+ s <- c(s, rep(paste0(dealname, ".sss"), 2))
+ }else{
+ r <- c(r, paste(toupper(dealname), "COLLAT", sep=","))
+ s <- c(s, rep(paste0(dealname, ".sss"), 1))
+ }
}
for(i in 1:length(cusips)){
diff --git a/R/load_cf.R b/R/load_cf.R
index 22c5386a..383d7649 100644
--- a/R/load_cf.R
+++ b/R/load_cf.R
@@ -12,7 +12,7 @@ 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"))
-workdate <- as.Date("2013-02-11")
+workdate <- as.Date("2013-02-14")
MarkitData <- getMarkitIRData(workdate)
L1m <- buildMarkitYC(MarkitData, dt = 1/12)
@@ -34,14 +34,14 @@ sanitize.column <- function(vec){
allfiles <- list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), "*.txt")
allfiles <- unique(sapply(strsplit(allfiles, "-"), function(x) x[1]))
allfiles <- allfiles[!(allfiles=="Total")]
-dealnames <- list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), "*COLLAT_INITIAL-CF-Scen1*")
+dealnames <- list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), "*COLLAT_INITIAL-CF-Scen100")
+dealnames <- c(dealnames, list.files(file.path(root.dir, "Scenarios", paste0("Prices_", workdate)), "*COLLAT-CF-Scen100"))
dealnames <- unique(sapply(strsplit(dealnames, "-"), function(x) x[1]))
cusips <- setdiff(allfiles, dealnames)
dealnames <- tolower(dealnames)
getdealcf <- function(dealnames, workdate=Sys.Date()){
cfdata <- list()
- tranches <- c("COLLAT_REINVEST", "COLLAT_INITIAL")
fields <- c("Cashflow", "Principal", "Interest")
flag <- FALSE
n.scenarios <- 100
@@ -51,10 +51,16 @@ getdealcf <- function(dealnames, workdate=Sys.Date()){
colnames(r) <- fields
sqlstring <- sprintf("select marketvalue from latest_deal_model_numbers where dealname='%s'", dealname)
mv <- dbGetQuery(dbCon, sqlstring)$marketvalue
- sqlstring <- sprintf("select \"Curr Collat Bal\" from latest_clo_universe where dealname='%s'", dealname)
- currbal <- dbGetQuery(dbCon, sqlstring)$"Curr Collat Bal"
+ 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 <- currbal
+ cfdata[[dealname]]$currbal <- sqldata$"Curr Collat Bal"
+ if(is.na(sqldata$"Reinv End Date")){
+ tranches <- "COLLAT"
+ }else{
+ tranches <- c("COLLAT_REINVEST", "COLLAT_INITIAL")
+ }
for(tranche in tranches){
for(i in 1:n.scenarios){
filename <- paste0(paste(toupper(dealname), tranche, "CF", paste0("Scen", i), sep="-"), ".txt")
@@ -121,8 +127,12 @@ getdealcf <- function(dealnames, workdate=Sys.Date()){
if(is.null(cfdata[[dealname]])){
next
}
- cf <- cfdata[[dealname]][["COLLAT_REINVEST"]][,"Cashflow"] +
- cfdata[[dealname]][["COLLAT_INITIAL"]][,"Cashflow"]
+ 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),
@@ -245,13 +255,34 @@ for(dealname in names(cfdata)){
## upload model data
for(cusip in names(cusipdata)){
- columns <- c("Cusip", "price", "wal", "duration", "updatedate")
- values <- c(cusip, cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
- cusipdata[[cusip]]$duration, 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)
+ 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")
+ values <- c(cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
+ cusipdata[[cusip]]$duration)
+ 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", "updatedate")
+ values <- c(cusip, cusipdata[[cusip]]$price, cusipdata[[cusip]]$wal,
+ cusipdata[[cusip]]$duration, 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)
}
+
diff --git a/python/intex_scenarios.py b/python/intex_scenarios.py
index c43caee7..331bc31a 100644
--- a/python/intex_scenarios.py
+++ b/python/intex_scenarios.py
@@ -74,9 +74,10 @@ def generate_scenarios(workdate, dealnames, conn, cursor):
reinvenddate = cursor.fetchone()[0]
if reinvenddate:
reinvenddate = reinvenddate.strftime("%Y%m%d")
+ reinvflag=True
else:
print "missing reinvestment end date"
- pdb.set_trace()
+ reinvflag=False
reinv_assets = get_reinv_assets(dealname)
perct_reinv_assets = convert_reinvtoperct(reinv_assets)
basedir = os.path.join(common.root, "Scenarios", "Intex curves_" + workdate)
@@ -112,56 +113,61 @@ def generate_scenarios(workdate, dealnames, conn, cursor):
newline = "DEAL_NAME=" + dealname.upper() + "\n"
fh2.write(newline)
continue
- if pattern11.match(line):
- line = re.sub(pattern11, r"\1{0}\2", line).format(dealname.upper()).rstrip()
- if reinv_assets["REINV_TBA1"] == "Float":
- line = line + "COUP_SPR=2.5|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
- elif reinv_assets["REINV_TBA1"] == "Fixed":
- line = line + "COUP_SPR=7|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
- fh2.write(line)
- continue
- if pattern12.match(line):
- line = re.sub(pattern12, r"\1{0}\2", line).format(dealname.upper()).rstrip()
- if reinv_assets["REINV_TBA2"] == "Float":
- line = line + "COUP_SPR=2.5|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
- elif reinv_assets["REINV_TBA2"] == "Fixed":
- line = line + "COUP_SPR=7|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
- fh2.write(line)
- continue
- if pattern2.match(line):
- line = re.sub(pattern2, r"\1{0}\2{1}", line).format(dealname.upper(), reinvenddate)
- fh2.write(line)
- continue
- if pattern3.match(line):
- if reinv_assets['REINV_TBA1'] == 'Fixed':
- line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(fixedreinvprices[i-2]))
- elif reinv_assets['REINV_TBA1'] == 'Float':
- line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(floatreinvprices[i-2]))
- fh2.write(line)
- continue
- if pattern4.match(line):
- if reinv_assets['REINV_TBA2'] == 'Fixed':
- line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(fixedreinvprices[i-2]))
- elif reinv_assets['REINV_TBA2'] == 'Float':
- line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(floatreinvprices[i-2]))
- fh2.write(line)
- continue
- if pattern5.match(line):
- if reinv_assets['REINV_TBA1']:
- line = re.sub(pattern5, r"\1{0}\2{1}", line).format(
- dealname.upper(), perct_reinv_assets['REINV_TBA1'])
- fh2.write(line)
- continue
- if pattern6.match(line):
- if reinv_assets['REINV_TBA2']:
- line = re.sub(pattern6, r"\1{0}\2{1}", line).format(
- dealname.upper(), perct_reinv_assets['REINV_TBA2'])
- fh2.write(line)
- continue
- if pattern7.search(line):
- line = re.sub(pattern7, dealname.upper(), line)
- fh2.write(line)
- continue
+ if reinvflag:
+ if pattern11.match(line):
+ line = re.sub(pattern11, r"\1{0}\2", line).format(dealname.upper()).rstrip()
+ if reinv_assets["REINV_TBA1"] == "Float":
+ line = line + "COUP_SPR=2.5|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
+ elif reinv_assets["REINV_TBA1"] == "Fixed":
+ line = line + "COUP_SPR=7|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
+ fh2.write(line)
+ continue
+ if pattern12.match(line):
+ line = re.sub(pattern12, r"\1{0}\2", line).format(dealname.upper()).rstrip()
+ if reinv_assets["REINV_TBA2"] == "Float":
+ line = line + "COUP_SPR=2.5|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
+ elif reinv_assets["REINV_TBA2"] == "Fixed":
+ line = line + "COUP_SPR=7|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n"
+ fh2.write(line)
+ continue
+ if pattern2.match(line):
+ line = re.sub(pattern2, r"\1{0}\2{1}", line).format(dealname.upper(), reinvenddate)
+ fh2.write(line)
+ continue
+ if pattern3.match(line):
+ if reinv_assets['REINV_TBA1'] == 'Fixed':
+ line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(),
+ " ".join(fixedreinvprices[i-2]))
+ elif reinv_assets['REINV_TBA1'] == 'Float':
+ line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(),
+ " ".join(floatreinvprices[i-2]))
+ fh2.write(line)
+ continue
+ if pattern4.match(line):
+ if reinv_assets['REINV_TBA2'] == 'Fixed':
+ line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(),
+ " ".join(fixedreinvprices[i-2]))
+ elif reinv_assets['REINV_TBA2'] == 'Float':
+ line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(),
+ " ".join(floatreinvprices[i-2]))
+ fh2.write(line)
+ continue
+ if pattern5.match(line):
+ if reinv_assets['REINV_TBA1']:
+ line = re.sub(pattern5, r"\1{0}\2{1}", line).format(
+ dealname.upper(), perct_reinv_assets['REINV_TBA1'])
+ fh2.write(line)
+ continue
+ if pattern6.match(line):
+ if reinv_assets['REINV_TBA2']:
+ line = re.sub(pattern6, r"\1{0}\2{1}", line).format(
+ dealname.upper(), perct_reinv_assets['REINV_TBA2'])
+ fh2.write(line)
+ continue
+ if pattern7.search(line):
+ line = re.sub(pattern7, dealname.upper(), line)
+ fh2.write(line)
+ continue
# if "STANDARD_VAR" in line:
# newline = "STANDARD_VAR[REINVEST_PRICE,1]=" + " ".join(reinvprices)
# fh2.write(newline)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2026-01-01 00:01:57 +0000