diff options
| -rw-r--r-- | R/build_portfolios.R | 28 | ||||
| -rw-r--r-- | R/db.R | 17 | ||||
| -rw-r--r-- | R/etdb.R | 5 | ||||
| -rw-r--r-- | R/intex_deal_functions.R | 21 | ||||
| -rw-r--r-- | R/load_cf.R | 9 | ||||
| -rw-r--r-- | R/serenitasdb.R | 6 |
6 files changed, 48 insertions, 38 deletions
diff --git a/R/build_portfolios.R b/R/build_portfolios.R index 1081688c..1bf2bc68 100644 --- a/R/build_portfolios.R +++ b/R/build_portfolios.R @@ -43,7 +43,7 @@ global.params <- yaml.load_file(file.path(root.dir, "code", "etc", "params.yml") cusipdata <- cusip.data()
cashspread.discount <- 0
-currdealnames <- dbGetQuery(dbCon, "select updatedate, dealname from latest_deal_model_numbers")
+currdealnames <- dbGetQuery(etdb, "select updatedate, dealname from latest_deal_model_numbers")
## build portfolio data
for(i in seq_along(dealnames)){
deal.name <- dealnames[i]
@@ -83,13 +83,13 @@ for(i in seq_along(dealnames)){ "dealspread = $2, cdopercentage = $3, stalepercentage= $4, ",
"marketvalue = $5",
"WHERE dealname= $6 and updatedate = $7")
- r <- dbSendQuery(dbCon, sqlstring,params = list(deal.spread5y,
- deal.spreadatmaturity,
- deal.portfolio$cdopercentage,
- deal.portfolio$stale,
- mv,
- deal.name,
- workdate))
+ r <- dbSendQuery(etdb, sqlstring,params = list(deal.spread5y,
+ deal.spreadatmaturity,
+ deal.portfolio$cdopercentage,
+ deal.portfolio$stale,
+ mv,
+ deal.name,
+ workdate))
if(dbHasCompleted(r)){
dbClearResult(r)
}
@@ -98,12 +98,12 @@ for(i in seq_along(dealnames)){ "(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))
+ r <- dbSendQuery(etdb, sqlstring, params = list(deal.name,
+ deal.portfolio$cdopercentage,
+ deal.portfolio$stale,
+ deal.spread5y, mv,
+ workdate,
+ deal.spreadatmaturity))
if(dbHasCompleted(r)){
dbClearResult(r)
}
@@ -0,0 +1,17 @@ +library(DBI)
+
+dbConn <- function(dbname){
+ switch(dbname,
+ ET = dbConnect(RPostgres::Postgres(),
+ dbname="ET",
+ user="et_user",
+ host="debian"),
+ serenitasdb = dbConnect(RPostgres::Postgres(),
+ dbname="serenitasdb",
+ user = "serenitas_user",
+ host="debian"),
+ dawndb = dbConnect(RPostgres::Postgres(),
+ dbname="dawndb",
+ user = "dawn_user",
+ host="debian"))
+}
diff --git a/R/etdb.R b/R/etdb.R deleted file mode 100644 index 6c650e23..00000000 --- a/R/etdb.R +++ /dev/null @@ -1,5 +0,0 @@ -library(DBI)
-dbCon <- dbConnect(RPostgres::Postgres(),
- dbname="ET",
- user="et_user",
- host="debian")
diff --git a/R/intex_deal_functions.R b/R/intex_deal_functions.R index f984b494..d5c7294e 100644 --- a/R/intex_deal_functions.R +++ b/R/intex_deal_functions.R @@ -12,19 +12,20 @@ if(hostname=="debian"){ }
source("cds_functions_generic.R")
-source("etdb.R")
+source("db.R")
+etdb <- dbConn("ET")
getdealdata <- function(dealname, workdate){
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 = $2 and updatedate<=$3)")
- mv <- dbGetQuery(dbCon, sqlstring,
+ mv <- dbGetQuery(etdb, 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 <- dbGetQuery(etdb, sqlstring, params=list(dealname, workdate))
if(!length(mv)){
dealdata$mv <- NA
}else{
@@ -35,10 +36,10 @@ getdealdata <- function(dealname, workdate){ getcollateral <- function(dealname, date){
if(missing(date)){
- collatdata <- dbGetQuery(dbCon, "select * from et_aggdealinfo($1)",
+ collatdata <- dbGetQuery(etdb, "select * from et_aggdealinfo($1)",
params=list(dealname))
}else{
- collatdata <- dbGetQuery(dbCon,
+ collatdata <- dbGetQuery(etdb,
"select * from et_aggdealinfo_historical($1, $2)",
params=list(dealname, date))
}
@@ -47,7 +48,7 @@ getcollateral <- function(dealname, date){ listdealnames <- function(){
sqlstring <- "select distinct dealname from clo_universe order by dealname"
- return( dbGetQuery(dbCon, sqlstring))
+ return( dbGetQuery(etdb, sqlstring))
}
cusip.data <- function(){
@@ -56,7 +57,7 @@ cusip.data <- function(){ CASE WHEN a.floater_index like 'LIBOR%' THEN 'FLOAT' ELSE 'FIXED' END
AS fixedorfloat, a.orig_moody from latest_cusip_universe a
LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname"
- data <- dbGetQuery(dbCon, sqlstring)
+ data <- dbGetQuery(etdb, sqlstring)
data <- data.table(data)
setkey(data, "cusip")
return( data )
@@ -86,7 +87,7 @@ dealnamefromcusip <- function(cusips){ ## wrapper around the sql function
sqlstr <- sprintf("select * from dealname_from_cusip('%s')",
paste(cusips, collapse="','"))
- r <- dbGetQuery(dbCon, sqlstr)
+ r <- dbGetQuery(etdb, sqlstr)
return( r$p_dealname )
}
@@ -94,7 +95,7 @@ cusipsfromdealnames <- function(dealnames){ sqlstring <-
sprintf("select unnest(\"Deal Cusip List\") from deal_indicative where dealname in ('%s')",
paste(dealnames, collapse="','"))
- return( dbGetQuery(dbCon, sqlstring)$unnest )
+ return( dbGetQuery(etdb, sqlstring)$unnest )
}
fithazardrate.fast <- function(collateral, eps=1e-6){
@@ -340,7 +341,7 @@ severityfromscenarios <- function(scenariosd, scenariosr){ get.reinvassets <- function(dealname, tradedate){
r <- list()
sqlstr <- "select * from et_historicaldealinfo($1, $2) where ReinvFlag Is true"
- data <- dbGetQuery(dbCon, sqlstr, params=list(dealname, tradedate))
+ data <- dbGetQuery(etdb, sqlstr, params=list(dealname, tradedate))
if(nrow(data)>0){
for(i in 1:nrow(data)){
r[[data$issuername[i]]] <- list(coupontype=data$fixedorfloat[i], liborfloor=data$liborfloor[i])
diff --git a/R/load_cf.R b/R/load_cf.R index d95eb3b6..8fd7fb25 100644 --- a/R/load_cf.R +++ b/R/load_cf.R @@ -13,7 +13,6 @@ root.dir <- if(.Platform$OS.type == "unix"){ tradedate <- if(length(args) >= 1) as.Date(args[1]) else 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"))
@@ -200,7 +199,7 @@ getcusipcf <- function(params, cfdata, dist, tradedate=Sys.Date()){ sqlstring <- sprintf(paste("select curr_balance, orig_balance, spread",
"from historical_cusip_universe('%s', '%s')"),
cusip, tradedate)
- indicdata <- dbGetQuery(dbCon, sqlstring)
+ indicdata <- dbGetQuery(etdb, sqlstring)
flag <- TRUE
for(j in 1:n.scenarios){
filename <- sprintf("%s-CF-Scen%s.txt", cusip, j)
@@ -295,7 +294,7 @@ save(cusipdata, cfdata, file=file.path(save.dir, "cashflows.RData"), sqlstring <- paste0("UPDATE et_deal_model_numbers SET ",
"wapbasis = $1 WHERE dealname= $2 AND updatedate = $3")
for(dealname in names(cfdata)){
- r <- dbSendQuery(dbCon, sqlstring, params = list(cfdata[[dealname]]$wapbasis*100,
+ r <- dbSendQuery(etdb, sqlstring, params = list(cfdata[[dealname]]$wapbasis*100,
dealname,
tradedate))
if(dbHasCompleted(r)){
@@ -307,7 +306,7 @@ for(dealname in names(cfdata)){ for(cusip in names(cusipdata)){
sqlstring <- paste0("SELECT updatedate from et_cusip_model_numbers",
" WHERE cusip=$1")
- sqldata <- dbGetQuery(dbCon, sqlstring, params=list(cusip))
+ sqldata <- dbGetQuery(etdb, 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))){
@@ -320,7 +319,7 @@ for(cusip in names(cusipdata)){ "VALUES($1, $2, $3, $4, $5, $6)")
params <- c(cusip, values, as.character(tradedate))
}
- r <- dbSendQuery(dbCon, sqlstring, params=params)
+ r <- dbSendQuery(etdb, sqlstring, params=params)
if(dbHasCompleted(r)){
dbClearResult(r)
}
diff --git a/R/serenitasdb.R b/R/serenitasdb.R index 798c55f8..5cde8d41 100644 --- a/R/serenitasdb.R +++ b/R/serenitasdb.R @@ -1,9 +1,7 @@ library(DBI) library(stringr) -serenitasdb <- dbConnect(RPostgres::Postgres(), - dbname="serenitasdb", - user = "serenitas_user", - host="debian") +source("db.R") +serenitasdb <- dbConn("serenitasdb") sqlArray <- function(vec){ vec[is.na(vec)] <- "NULL" |