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
|
library(Rblpapi)
source("serenitasdb.R")
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)
}
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))
if(dbHasCompleted(r)){
dbClearResult(r)
}
}
}
##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)
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)
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)){
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)
}
}
|
