diff options
| -rw-r--r-- | R/new_index_series.R | 80 |
1 files changed, 43 insertions, 37 deletions
diff --git a/R/new_index_series.R b/R/new_index_series.R index d292a403..afe8d132 100644 --- a/R/new_index_series.R +++ b/R/new_index_series.R @@ -4,11 +4,28 @@ source("serenitasdb.R") bbgConn <- blpConnect(host='192.168.1.108') ## This script helps adding the necessary data for a new index series -newbasketID <- function(indextype='IG', series, mat_5yr, coupon){ + +bbgname <- function(indextype, series, tenor="5Y"){ + ## indextype is EUR, IG, HY or XOVER + if(indextype=="EU"){ + indextype <- "EUR" + indexname <- 'ITRX' + }else if(indextype=="XO"){ + indextype <- "XOVER" + indexname <- "ITRX" + }else{ + indexname <- 'CDX' + } + return( sprintf("%s %s CDSI S%s %s Corp", indexname, indextype, series, tenor) ) +} + +newbasketID <- function(indextype='IG', series, version=1, mat_5yr, coupon){ sqlstr <- paste0("INSERT INTO index_version", - "(index, series, version, indexfactor, cumulativeloss, lastdate) ", - "VALUES($1, $2, $3, 100, 0, 'infinity') RETURNING basketid") - basketid <- as.integer(dbGetQuery(serenitasdb, sqlstr, params=list(indextype, series, 1))$basketid) + "(index, series, version, indexfactor, cumulativeloss, lastdate, redindexcode) ", + "VALUES($1, $2, $3, 100, 0, 'infinity', $4) RETURNING basketid") + red <- bdp(bbgConn, bbgname("XO", 23), "CDS_RED_PAIR_CODE")$CDS_RED_PAIR_CODE + basketid <- as.integer(dbGetQuery(serenitasdb, sqlstr, + params=list(indextype, series, version, red))$basketid) maturity <- mat_5yr + lubridate::years(c(-2, 0, 2 ,5)) tenor <- c("3yr", "5yr", "7yr", "10yr") sqlstr <- "INSERT INTO index_maturity VALUES($1, $2, $3, $4, $5)" @@ -23,18 +40,7 @@ newbasketID <- function(indextype='IG', series, mat_5yr, coupon){ } newcurves <- function(indextype, series, basketid){ - ## indextype is EUR, IG, HY or XOVER - if(indextype=="EU"){ - indextype <- "EUR" - indexname <- 'ITRX' - }else if(indextype=="XO"){ - indextype <- "XOVER" - indexname <- "ITRX" - }else{ - indexname <- 'CDX' - } - index <- bds(bbgConn, sprintf("%s %s CDSI S%s 5Y Corp", indexname, indextype, series), - "INDX_MEMBERS") + index <- bds(bbgConn, bbgname(indextype, series), "INDX_MEMBERS") tickers <- index[,5] cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority") @@ -47,30 +53,30 @@ newcurves <- function(indextype, series, basketid){ all.companyids <- dbGetQuery(serenitasdb, "SELECT company_id FROM bbg_issuers") prep.data <- prep.data[!prep.data$company_id %in% all.companyids$company_id,] + if(nrow(prep.data)>0){ + cdscurves <- data.frame() + for(ticker in prep.data$curve_ticker){ + cdscurves <- rbind(cdscurves, + data.frame(ticker=ticker, + bds(bbgConn, paste(ticker, "Curncy"), "CDS_CURVE_INFO"))) + } - cdscurves <- data.frame() - for(ticker in prep.data$curve_ticker){ - cdscurves <- rbind(cdscurves, - data.frame(ticker=ticker, - bds(bbgConn, paste(ticker, "Curncy"), "CDS_CURVE_INFO"))) - } - - prep.data$cds_curve <- aggregate(Ticker~ticker, data=cdscurves, FUN=sqlArray)$Ticker - levels(prep.data$doc_clause) <- gsub("-", " ", levels(prep.data$doc_clause)) - sqlstr <- paste0("INSERT INTO bbg_issuers", - "(name, company_id, ticker, currency, seniority, doc_clause, cds_curve)", - "VALUES($1, $2, $3, $4, $5, $6, $7)") + prep.data$cds_curve <- aggregate(Ticker~ticker, data=cdscurves, FUN=sqlArray)$Ticker + levels(prep.data$doc_clause) <- gsub("-", " ", levels(prep.data$doc_clause)) + sqlstr <- paste0("INSERT INTO bbg_issuers", + "(name, company_id, ticker, currency, seniority, doc_clause, cds_curve)", + "VALUES($1, $2, $3, $4, $5, $6, $7)") - with(prep.data, - for(i in 1:nrow(prep.data)){ - r <- dbSendQuery(serenitasdb, sqlstr, - params = list(Name[i], company_id[i], ticker[i], + with(prep.data, + for(i in 1:nrow(prep.data)){ + r <- dbSendQuery(serenitasdb, sqlstr, + params = list(Name[i], company_id[i], ticker[i], currency[i], seniority[i], doc_clause[i], cds_curve[i])) - if(dbHasCompleted(r)){ - dbClearResult(r) - } - }) - + if(dbHasCompleted(r)){ + dbClearResult(r) + } + }) + } sqlstr <- "UPDATE bbg_issuers SET index_list=index_list||$1::int where company_id=$2" for(id in df$cds_company_id){ r <- dbSendQuery(serenitasdb, sqlstr, params=list(basketid, id)) |