diff options
| -rw-r--r-- | R/new_index_series.R | 130 |
1 files changed, 79 insertions, 51 deletions
diff --git a/R/new_index_series.R b/R/new_index_series.R index 06f0c36e..d292a403 100644 --- a/R/new_index_series.R +++ b/R/new_index_series.R @@ -5,74 +5,102 @@ 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){ - sqlstr <- paste("INSERT INTO index_version(index, series, version, indexfactor, cumulativeloss, lastdate)", - "VALUES($1, $2, $3, 100, 0, 'infinity')") - r <- dbSendQuery(dbCon, sqlstr, params=list(indextype, series, 1)) - if(dbHasCompleted(r)){ - dbClearResult(r) - } + 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) 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)" for(i in seq_along(tenor)){ - r <- dbSendQuery(dbCon, sqlstr, - params=list(indextype, series, tenor[i], maturity[i], coupon)) + r <- dbSendQuery(serenitasdb, sqlstr, + params=list(indextype, series, tenor[i], maturity[i], coupon)) if(dbHasCompleted(r)){ dbClearResult(r) } } + return(basketid) } -##TODO: fill the index -##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] - -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) -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, curve_ticker=data$tickers) +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") + tickers <- index[,5] + cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority") + df <- bdp(bbgConn, paste(tickers, "Corp"), cds.fields) + df <- cbind(tickers, df) + prep.data <- with(df, data.frame(Name=index[,1], company_id=cds_company_id, + ticker=cds_corp_tkr, currency=index[,3], + seniority=sw_seniority, doc_clause=cds_restructuring_type, + curve_ticker=tickers)) -all.companyids <- dbGetQuery(serenitasdb, "SELECT company_id FROM bbg_issuers") -prep.data <- prep.data[!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) + 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)") -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 <- 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(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)){ + 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) } - }) + }) -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 -##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) + 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)) + if(dbHasCompleted(r)){ + dbClearResult(r) + } } + return(prep.data) +} + +## company_id markit_ticker seniority coupon +## 1 100104 APA SNRFOR 100 +## 2 109584 ENB SNRFOR 100 +## mappings <- data.frame(company_id=df$company_id, +## markit_ticker=c("FIATCHR","ZIGGBON"), +## seniority="SNRFOR",coupon=500) + +markit.mappings <- function(df){ + sqlstr <- "INSERT INTO bbg_markit_mapping VALUES('infinity', $1, $2, $3, $4)" + with(df, + for(i in 1:nrow(df)){ + r <- dbSendQuery(serenitasdb, sqlstr, + params=list(company_id[i], + markit_ticker[i], + seniority[i], + coupon[i])) + if(dbHasCompleted(r)){ + dbClearResult(r) + } + }) } + +blpDisconnect(bbgConn) |