1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
library(Rbbg)
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="serenitasdb", user="serenitas_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("9", "19", "21", "22")
igpattern <- "CDX IG CDSI S%s 7Y Corp"
hy.indices <- c("10", "15", "17", "19", "21", "22")
hypattern <- "CDX HY CDSI S%s 7Y PRC Corp"
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"))
}
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])
tickers <- tickers[-which(tickers=="")]
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(10, 15, 17, 19, 21, 22)))){
indexmatch <- ticker_company[match(get(index)[,5], ticker_company[,2]),]
sqlstr <- "UPDATE CDS_Issuers SET index_list=index_list||%s where company_id='%s'"
basketids <- allBasketID(index)
for(j in 1:nrow(indexmatch)){
dbSendQuery(dbCon, sprintf(sqlstr, 182910, indexmatch[j,1]))
}
}
##to get default data
"default_data"
|
