diff options
| -rw-r--r-- | R/new_index_series.R | 80 |
1 files changed, 47 insertions, 33 deletions
diff --git a/R/new_index_series.R b/R/new_index_series.R index 9c5a3d20..406f1039 100644 --- a/R/new_index_series.R +++ b/R/new_index_series.R @@ -1,32 +1,35 @@ library(Rbbg) -library(RPostgreSQL) -library(lubridate) -drv <- dbDriver("PostgreSQL") -dbCon <- dbConnect(drv, dbname="serenitasdb", user="serenitas_user", password="Serenitas1", - host="debian") +source("serenitasdb.R") bbgConn <- blpConnect(host='192.168.1.108', port='8194') ## This script helps adding the necessary data for a new index series newbasketID <- function(indextype='IG', series, mat_5yr, coupon){ sqlstr <- paste("INSERT INTO index_version(index, series, version, indexfactor, cumulativeloss, lastdate)", - "VALUES('%s', %s, %s, 100, 0, 'infinity')") - stmt <- sprintf(sqlstr, indextype, series, 1) - dbSendQuery(dbCon, stmt) - maturity <- mat_5yr + years(c(-2, 0, 2 ,5)) + "VALUES($1, $2, $3, 100, 0, 'infinity')") + r <- dbSendQuery(dbCon, sqlstr, params=list(indextype, series, 1)) + if(dbHasCompleted(r)){ + dbClearResult(r) + } + maturity <- mat_5yr + lubridate::years(c(-2, 0, 2 ,5)) tenor <- c("3yr", "5yr", "7yr", "10yr") - sqlstr <- "INSERT INTO index_maturity VALUES('%s', %s, '%s', '%s', %s)" + sqlstr <- "INSERT INTO index_maturity VALUES($1, $2, $3, $4, $5)" for(i in seq_along(tenor)){ - stmt <- sprintf(sqlstr, indextype, series, tenor[i], maturity[i], coupon) - dbSendQuery(dbCon, stmt) + r <- dbSendQuery(dbCon, sqlstr, + params=list(indextype, series, tenor[i], maturity[i], coupon)) + if(dbHasCompleted(r)){ + dbClearResult(r) + } } } ##TODO: fill the index -indexname <- 'ITRX' ## or CDX -indextype <- 'EUR' ## or IG or HY or EUR -series <- 22 -newbasketID('EU', 22, as.Date("2019-12-20")) +##indexname <- 'ITRX' ## or CDX +indexname <- 'CDX' +##indextype <- 'EUR' ## or IG or HY or EUR +indextype <- "IG" +newseries <- 16 +##newbasketID('EU', 22, as.Date("2019-12-20")) index <- bds(bbgConn, sprintf("%s %s CDSI S%s 5Y Corp", indexname, indextype, newseries), "INDX_MEMBERS") tickers <- index[,5] @@ -34,30 +37,41 @@ tickers <- index[,5] cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority") data <- bdp(bbgConn, paste(tickers, "Corp"), cds.fields) data <- cbind(tickers, data) -unique.tickers <- aggregate(data, by=list(data$cds_company_id), FUN=function(x)x[1])$tickers -cdscurves <- bds(bbgConn, paste(unique.tickers, "Curncy"), "CDS_CURVE_INFO") -curves <- aggregate(cdscurves[,2], by=list(cdscurves$ticker), FUN=function(x)x) prep.data <- data.frame(Name=index[,1], company_id=data$cds_company_id, ticker=data$cds_corp_tkr, currency=index[,3], seniority=data$sw_seniority, - doc_clause=data$cds_restructuring_type) + doc_clause=data$cds_restructuring_type, curve_ticker=data$tickers) -all.companyids <- dbGetQuery(dbCon, "SELECT company_id from cds_issuers") -curves <- curves[!prep.data$company_id %in% all.companyids$company_id,] + +all.companyids <- dbGetQuery(serenitasdb, "SELECT company_id FROM bbg_issuers") prep.data <- prep.data[!prep.data$company_id %in% all.companyids$company_id,] + +cdscurves <- bds(bbgConn, paste(prep.data$curve_ticker, "Curncy"), "CDS_CURVE_INFO") +curves <- aggregate(cdscurves[,2], by=list(cdscurves$ticker), FUN=function(x)x) + + +curves <- curves[!prep.data$company_id %in% all.companyids$company_id,] prep.data[prep.data$doc_clause=="Modified-Modified Restructurin","doc_clause"] <- "Modified Modified Restructurin" -sqlstr <- "INSERT INTO bbg_issuers VALUES('%s', %s, '%s', '%s', '%s', '%s', '%s')" +sqlstr <- paste("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(curves)){ - stmt <- sprintf(sqlstr, gsub("'","''", Name[i]), company_id[i], ticker[i], currency[i], - seniority[i], doc_clause[i], - paste0("{\"", paste(curves[i,2], collapse="\",\""), "\"}")) - dbSendQuery(dbCon, stmt) + 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], + sqlArray(curves[i,2]))) + if(dbHasCompleted(r)){ + dbClearResult(r) + } }) -sqlstr <- "SELECT basketid from index_version where index='%s' and series=%s" -basketid <- as.integer(dbGetQuery(dbCon, sprintf(sqlstr, 'EU', series))) +sqlstr <- "SELECT basketid FROM index_version WHERE index=$1 and series=$2" +#basketid.list <- dbGetQuery(serenitasdb, sqlstr, params=list('HY', 16)) ##fill index_list column -sqlstr <- "UPDATE CDS_Issuers SET index_list=index_list||%s where company_id=%s" -for(id in data$cds_company_id){ - dbSendQuery(dbCon, sprintf(sqlstr, basketid, id)) +##basketid <- nameToBasketID("HY20", as.Date("2014-05-23")) +sqlstr <- "UPDATE bbg_issuers SET index_list=index_list||$1::int where company_id=$2" +basketid <- 391 +for(id in prep.data$company_id){ + r <- dbSendQuery(serenitasdb, sqlstr, params=list(basketid, id)) + if(dbHasCompleted(r)){ + dbClearResult(r) + } } |