diff options
| -rw-r--r-- | R/bloomberg_cds.R | 62 | ||||
| -rw-r--r-- | R/build_cds_data.R (renamed from R/load_cds_data.R) | 26 |
2 files changed, 74 insertions, 14 deletions
diff --git a/R/bloomberg_cds.R b/R/bloomberg_cds.R new file mode 100644 index 00000000..79717a36 --- /dev/null +++ b/R/bloomberg_cds.R @@ -0,0 +1,62 @@ +bbgConn <- blpConnect(host='192.168.1.108', port='8194') + +nameToBasketID <- function(name){ + index <- substr(name, 1, 2) + series <- substr(name, 3, nchar(name)) + sqlstr <- "SELECT max(basketid) from index_desc where Index='%s' and Series=%s" + r <- dbGetQuery(dbCon, sprintf(sqlstr, index, series)) + return(as.integer(r)) +} + +newbasketID <- function(series, offset){ + maturities <- c("2017-06-20", "2019-06-20", "2021-06-20", "2024-06-20") + tenors <- c("Y3", "Y5", "Y7", "Y10") + sqlstr <- "INSERT INTO index_desc VALUES(%s, '%s', %s, '%s', '%s', 100, 0)" + indices <- c("HY", "IG") + for(i in seq_along(indices)){ + for(j in seq_along(tenors)){ + stmt <- sprintf(sqlstr, offset + 10*i, indices[i], series, + maturities[j], tenors[j]) + dbSendQuery(dbCon, stmt) + } + + } +} + +cdslist <- function(indexname){ + basketid <- nameToBasketID(indexname) + sqlstr <- "select * from CDS_Issuers where index_list @> '{%s}'" + return( dbGetQuery(dbCon, sprintf(sqlstr, basketid))) +} + +download.cdscurves <- function(date=Sys.Date()){ + tickers <- dbGetQuery(dbCon, "select cds_curve[6] from CDS_Issuers")$cds_curve + bbg.data <- bds(bbgConn, paste(tickers, "Curncy"), "cds_curve_info") + stmt <- "INSERT INTO cds_quotes VALUES('%s', '%s', 0, 0, %s, %s, %s, %s)" + for(i in 1:nrow(bbg.data)){ + Source <- bbg.data[i,7] + if(Source==""){ + Source <- "Null" + }else{ + Source <- paste0("'", Source, "'") + } + dbSendQuery(dbCon, sprintf(stmt, format(date,"%m/%d/%y"), + bbg.data[i,2], bbg.data[i,4], bbg.data[i,5], + Source, bbg.data[i,6])) + } +} + +write.tranchedata <- function(){ + data <- c() + for(index in c("ig21", "ig19", "hy21", "hy19")){ + df <- read.csv(paste0(index, "_tranches_bbgid.csv"), check.names=FALSE) + for(tenor in c("3Y", "5Y", "7Y")){ + if(tenor %in% names(df)){ + data <- rbind(data, bdp(bbgConn, paste(df[[tenor]], "Corp"), c("px_last", "tranche_delta", "underlying_reference_px_rt"))) + } + } + } + write.csv(data, file=paste0("tranche_data_", Sys.Date(), ".csv"), row.names=TRUE) +} + +blpDisconnect(bbgConn) diff --git a/R/load_cds_data.R b/R/build_cds_data.R index 8515f8e5..3ef4b10e 100644 --- a/R/load_cds_data.R +++ b/R/build_cds_data.R @@ -3,7 +3,7 @@ library(RPostgreSQL) drv <- dbDriver("PostgreSQL") dbCon <- dbConnect(drv, dbname="mlpdb", user="mlpdb_user", password="Serenitas1", host="debian") -setwd("/home/share/CorpCDOs/data/bloomberg/CDS") + bbgConn <- blpConnect(host='192.168.1.108', port='8194') ig.indices <- c("19", "21", "22") igpattern <- "CDXIG5%s Curncy" @@ -12,6 +12,7 @@ hypattern <- "CDXHY5%s Curncy" itraxx.indices <- c("19", "20", "21") itraxxpattern <- "ITXEB5%s Curncy" +##assign all the member to the variables IGx, HYx and EUx for(index in ig.indices){ assign(paste0("IG", index), bds(bbgConn, sprintf(igpattern, index), "INDX_MEMBERS")) } @@ -51,22 +52,19 @@ with(prep.data, paste0("{\"", paste(curves[i,2], collapse="\",\""), "\"}")) dbSendQuery(dbCon, stmt) }) -write.csv(cdscurves, file=paste0("single_names_", Sys.Date(), ".csv"), row.names=FALSE) -data <- c() -for(index in c("ig21", "ig19", "hy21", "hy19")){ - df <- read.csv(paste0(index, "_tranches_bbgid.csv"), check.names=FALSE) - for(tenor in c("3Y", "5Y", "7Y")){ - if(tenor %in% names(df)){ - data <- rbind(data, bdp(bbgConn, paste(df[[tenor]], "Corp"), c("px_last", "tranche_delta", "underlying_reference_px_rt"))) - } +##fill index_list column +ticker_company <- dbGetQuery(dbCon, "SELECT company_id, unnest(cds_curve) from CDS_Issuers") +for(index in c(paste0("IG", c(9, 19, 21, 22)), + paste0("EU", c(9, 19, 21)), + paste0("HY", c(15, 17, 19, 21, 22)))){ + indexmatch <- ticker_company[match(get(index)[get(index)[,2]>0,5], ticker_company[,2]),] + sqlstr <- "UPDATE CDS_Issuers SET index_list=index_list||%s where company_id='%s'" + basketid <- nameToBasketID(index) + for(j in 1:nrow(indexmatch)){ + dbSendQuery(dbCon, sprintf(sqlstr, basketid, indexmatch[j,1])) } } ##to get default data "default_data" - -write.csv(data, file=paste0("tranche_data_", Sys.Date(), ".csv"), row.names=TRUE) -blpDisconnect(bbgConn) - -ITXEB521 |