1 files changed, 88 insertions, 0 deletions
diff --git a/R/build_cds_database.R b/R/build_cds_database.R
new file mode 100644
index 00000000..2d9e30e9
--- /dev/null
+++ b/R/build_cds_database.R
@@ -0,0 +1,88 @@
+library(Rbbg)
+library(RPostgreSQL)
+drv <- dbDriver("PostgreSQL")
+dbCon <- dbConnect(drv, dbname="mlpdb", user="mlpdb_user", password="Serenitas1",
+                   host="debian")
+
+bbgConn <- blpConnect(host='192.168.1.108', port='8194')
+
+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)
+        }
+
+    }
+}
+
+ig.indices <- c("19", "21", "22")
+igpattern <- "CDXIG5%s Curncy"
+hy.indices <- c("15", "17", "19", "21", "22")
+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"))
+}
+assign("IG9", bds(bbgConn, "IBOXUG09 Curncy", "INDX_MEMBERS"))
+
+for(index in hy.indices){
+    assign(paste0("HY", index), bds(bbgConn, sprintf(hypattern, index), "INDX_MEMBERS"))
+}
+
+for(index in itraxx.indices){
+    assign(paste0("EU", index), bds(bbgConn, sprintf(itraxxpattern, index), "INDX_MEMBERS"))
+}
+assign("EU9", bds(bbgConn, "ITRXEB09 Curncy", "INDX_MEMBERS"))
+
+all.names <- c()
+all.indices <- c(paste0("HY", hy.indices), paste0("IG", ig.indices), paste0("EU", itraxx.indices), "IG9", "EU9")
+for(index in all.indices){
+    all.names <- rbind(all.names, get(index))
+}
+
+tickers <- unique(all.names[,5])
+data <- bdp(bbgConn, paste(tickers, "Corp"), c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr"))
+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")
+temp1 <- all.names[match(unique.tickers, all.names[,5]),]
+temp2 <- data[match(unique.tickers, data$tickers),]
+curves <- aggregate(cdscurves[,2], by=list(cdscurves$ticker), FUN=function(x)x)
+prep.data <- data.frame(Name=temp1[,1], company_id=temp2[,2], ticker=temp2[,4],
+                   currency=temp1[,3], seniority=temp1[,4], doc_clause=temp2[,3])
+
+sqlstr <- "INSERT INTO CDS_Issuers VALUES('%s', %s, '%s', '%s', '%s', '%s', '%s')"
+with(prep.data,
+     for(i in 1:nrow(prep.data)){
+         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)
+     })
+
+##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"