diff options
| -rw-r--r-- | R/mlpdb.R | 38 |
1 files changed, 25 insertions, 13 deletions
@@ -54,22 +54,34 @@ get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){ "where index='%s' and series=%s and quotedate::date='%s'", "and tenor = '%s' order by quotedate desc") temp <- indexsplit(indexname) + ## first try the easy way: + sqlstr <- paste("select * from tranche_quotes", + "where index='%s' and series=%s and quotedate::date='%s'", + "and tenor = '%s' order by attach asc") + r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, date, tenor)) + ## check if set is unique and complete + if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(0, r$detach))){ + return(r) + } + + ##else we work harder ##we get the list of distinct quotes distinct.quotes <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, date, tenor)) - if(nrow(distinct.quotes)>0){ - #we loop through the disctinct quotes until we find a complete set - for(i in 1:nrow(distinct.quotes)){ - sqlstr <- paste("select * from tranche_quotes where index='%s'", - "and series=%s and tenor = '%s' and quotedate='%s'", - "and detach-attach!=5 and quotesource='%s' order by attach asc") - r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, tenor, - distinct.quotes$quotedate[i], - distinct.quotes$quotesource[i])) - if(all(c(r$attach, 100)==c(0, r$detach))){#set is complete - break - } + flag <- FALSE + ##we loop through the disctinct quotes until we find a complete set + for(i in seq_along(distinct.quotes)){ + sqlstr <- paste("select * from tranche_quotes where index='%s'", + "and series=%s and tenor = '%s' and quotedate='%s'", + "and detach-attach!=5 and quotesource='%s' order by attach asc") + r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, tenor, + distinct.quotes$quotedate[i], + distinct.quotes$quotesource[i])) + if(all(c(r$attach, 100)==c(0, r$detach))){#set is complete + flag <- TRUE + break + } } - }else{ + if(!flag){ stop("no quotes for that day") } return( r ) |