diff options
Diffstat (limited to 'R')
| -rw-r--r-- | R/serenitasdb.R | 61 |
1 files changed, 40 insertions, 21 deletions
diff --git a/R/serenitasdb.R b/R/serenitasdb.R index 736d2250..04a6224a 100644 --- a/R/serenitasdb.R +++ b/R/serenitasdb.R @@ -1,4 +1,5 @@ library(DBI) +library(stringr) serenitasdb <- dbConnect(RPostgres::Postgres(), dbname="serenitasdb", user = "serenitas_user", @@ -9,8 +10,8 @@ sqlArray <- function(vec){ return( sprintf("{%s}", paste(vec, collapse=",")) ) } -sqlQuote <- function(slist){ - return( unlist(lapply(slist, function(s) sprintf("'%s'", s)) ) ) +sqlQuote <- function(svec){ + return( str_c("'", svec, ",") ) } nameToBasketID <- function(name, date=Sys.Date()){ @@ -41,6 +42,8 @@ cdslist <- function(indexname, date=Sys.Date()){ } arr.convert <- function(arr, col.names){ + ## convert into a sqlarray into an R arraay + ## inverse of sqlArray if(missing(col.names)){ ncol <- str_count(arr[1], ",") + 1 }else{ @@ -55,7 +58,7 @@ arr.convert <- function(arr, col.names){ return(arr) } -get.indexquotes <- function(indexname, date=Sys.Date()){ +get.singlenamesquotes <- function(indexname, date=Sys.Date()){ r <- dbGetQuery(serenitasdb, "SELECT * from curve_quotes($1, $2)", params = list(indexname, date)) tenors <- c("6m", paste0(c(1, 2, 3, 4, 5, 7, 10), "y")) @@ -67,18 +70,18 @@ get.indexquotes <- function(indexname, date=Sys.Date()){ } indexsplit <- function(indexname){ - return(list(index = str_to_upper(str_sub(indexname, 0, 2)), + return(list(indextype = str_to_upper(str_sub(indexname, 0, 2)), series=str_sub(indexname, 3, -1))) } get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ - temp <- indexsplit(indexname) ## first try the easy way: sqlstr <- paste("select * from tranche_quotes", "where index=$1 and series=$2 and quotedate::date=$3", "and tenor = $4 order by attach asc") - r <- dbGetQuery(serenitasdb, sqlstr, - params = list(temp$index, temp$series, date, tenor)) + r <- with(indexsplit(indexname), + dbGetQuery(serenitasdb, sqlstr, + params = list(indextype, series, date, tenor))) ## check if set is unique and complete ## hy9 and hy10 tranche is gone if(tolower(indexname) %in% c("hy9", "hy10")){ @@ -94,8 +97,9 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes", "where index=$1 and series=$2 and quotedate::date=$3", "and tenor = $4 order by quotedate desc") - distinct.quotes <- dbGetQuery(serenitasdb, sqlstr, - params = list(temp$index, temp$series, date, tenor)) + distinct.quotes <- with(indexsplit(indexname), + dbGetQuery(serenitasdb, sqlstr, + params = list(indextype, series, date, tenor))) flag <- FALSE ##we loop through the disctinct quotes until we find a complete set if(nrow(distinct.quotes)==0){ @@ -111,10 +115,11 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ "and series=$2 and tenor = $3 and quotedate=$4", "and quotesource=$5 order by attach asc") } - r <- dbGetQuery(serenitasdb, sqlstr, - params = list(temp$index, temp$series, tenor, - distinct.quotes$quotedate[i], - distinct.quotes$quotesource[i])) + r <- with(indexsplit(indexname), + dbGetQuery(serenitasdb, sqlstr, + params = list(indextype, series, tenor, + distinct.quotes$quotedate[i], + distinct.quotes$quotesource[i]))) if(all(c(r$attach, 100)==c(lower.attach, r$detach))){#set is complete flag <- TRUE break @@ -127,17 +132,31 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ } couponfromindex <- function(indexname, tenor){ - temp <- indexsplit(indexname) sqlstr <- "select coupon from index_maturity where index=$1 and series=$2 and tenor=$3" - r <- dbGetQuery(serenitasdb, sqlstr, params = list(temp$index, temp$series, tenor)) + r <- with(indexsplit(indexname), + dbGetQuery(serenitasdb, sqlstr, params = list(indextype, series, tenor))) r$coupon } getlastdate <- function(indexname, tenor){ - temp <- indexsplit(indexname) - r <- dbGetQuery(serenitasdb, - paste("SELECT max(date)+1 AS date FROM risk_numbers", - "WHERE index=$1 and series=$2 and tenor=$3"), - params = list(temp$index, temp$series, tenor)) - return(as.Date(r$date, format="%Y-%m-%d")) + stmt <- paste("SELECT max(date)+1 AS date FROM risk_numbers", + "WHERE index=$1 and series=$2 and tenor=$3") + r <- with(indexsplit(indexname), + dbGetQuery(serenitasdb, stmt, params = + list(indextype, series, tenor))) + return( r$date ) +} + +get.indexquotes <- function(indexname, tenor){ + stmt <- "select * from index_quotes where index=$1 and series=$2 and tenor=$3 order by date" + return ( with(indexplit(indexname), + dbGetQuery(serenitasdb, stmt, params=list(indextype, series, tenor))) ) +} + +get.skews <- function(indexname, tenor){ + stmt <- "select skew from risk_numbers where index=$1 and series=$2 and tenor=$3 order by date" + arr <- with(indexplit(indexname), + dbGetQuery(serenitasdb, stmt, params=list(indextype, series, tenor))) + arr <- arr.convert(arr$skew) + return ( arr[,-ncol(arr)] ) } |