diff options
| -rw-r--r-- | R/load_cds_data.R | 60 | ||||
| -rw-r--r-- | sql/mlpdb.sql | 18 |
2 files changed, 69 insertions, 9 deletions
diff --git a/R/load_cds_data.R b/R/load_cds_data.R index 4a687cc5..8515f8e5 100644 --- a/R/load_cds_data.R +++ b/R/load_cds_data.R @@ -1,19 +1,56 @@ library(Rbbg) +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("21", "22", "19") -hy.indices <- c("21", "22", "19") -allnames <- c() +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" + for(index in ig.indices){ - allnames <- rbind(allnames, bds(bbgConn, sprintf("CDXIG5%s Curncy", index), "INDX_MEMBERS")) + assign(paste0("IG", index), bds(bbgConn, sprintf(igpattern, index), "INDX_MEMBERS")) } +assign("IG9", bds(bbgConn, "IBOXUG09 Curncy", "INDX_MEMBERS")) + for(index in hy.indices){ - allnames <- rbind(allnames, bds(bbgConn, sprintf("CDXHY5%s Curncy", index), "INDX_MEMBERS")) + 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)) } -allnames <- unique(allnames) -rownames(allnames) <- NULL -cdscurves <- bds(bbgConn, paste(allnames[,5], "Curncy"), "CDS_CURVE_INFO") -cdscurves <- cdscurves[,-c(1,8)] + +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) + }) write.csv(cdscurves, file=paste0("single_names_", Sys.Date(), ".csv"), row.names=FALSE) data <- c() @@ -26,5 +63,10 @@ for(index in c("ig21", "ig19", "hy21", "hy19")){ } } +##to get default data +"default_data" + write.csv(data, file=paste0("tranche_data_", Sys.Date(), ".csv"), row.names=TRUE) blpDisconnect(bbgConn) + +ITXEB521 diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql index ffb67ae9..81380e86 100644 --- a/sql/mlpdb.sql +++ b/sql/mlpdb.sql @@ -97,3 +97,21 @@ GRANT ALL ON quotes TO mlpdb_user; -- 162308 U+500
-- All other tranches should have a unique trancheid for a given basketid, attach, detach combination
+
+CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
+CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
+CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN');
+CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');
+CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
+
+CREATE TABLE CDS_Issuers(
+ Name text,
+ company_id integer,
+ ticker text,
+ currency curr,
+ seniority sen,
+ doc_clause DocClause,
+ cds_curve text[8],
+ index_list integer[]);
+
+GRANT ALL ON CDS_Issuers to mlpdb_user;
|