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
|
library(Rbbg)
library(RPostgreSQL)
library(lubridate)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="serenitasdb", user="serenitas_user", password="Serenitas1",
host="debian")
bbgConn <- blpConnect(host='192.168.1.108', port='8194')
## 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('%s', %s, %s, 100, 0, 'infinity')")
stmt <- sprintf(sqlstr, indextype, series, 1)
dbSendQuery(dbCon, stmt)
maturity <- mat_5yr + years(c(-2, 0, 2 ,5))
tenor <- c("3yr", "5yr", "7yr", "10yr")
sqlstr <- "INSERT INTO index_maturity VALUES('%s', %s, '%s', '%s', %s)"
for(i in seq_along(tenor)){
stmt <- sprintf(sqlstr, indextype, series, tenor[i], maturity[i], coupon)
dbSendQuery(dbCon, stmt)
}
}
##TODO: fill the index
indexname <- 'ITRX' ## or CDX
indextype <- 'XOVER' ## or IG or HY
oldseries <- 22
newseries <- 23
newbasketID('IG', 23, as.Date("2019-12-20"))
oldindex <- bds(bbgConn, sprintf("%s %s CDSI S%s 5Y Corp", indexname, indextype, oldseries), "INDX_MEMBERS")
newindex <- bds(bbgConn, sprintf("%s %s CDSI S%s 5Y Corp", indexname, indextype, newseries), "INDX_MEMBERS")
oldtickers <- oldindex[,5]
tickers <- newindex[,5]
data <- bdp(bbgConn, paste(tickers, "Corp"), c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr"))
olddata <- bdp(bbgConn, paste(oldtickers, "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")
curves <- aggregate(cdscurves[,2], by=list(cdscurves$ticker), FUN=function(x)x)
prep.data <- data.frame(Name=newindex[,1], company_id=data[,2], ticker=data[,4],
currency=newindex[,3], seniority=temp1[,4], doc_clause=data[,3])
newrowids <- which(!data[,'cds_company_id'] %in% olddata[,'cds_company_id'], 'cds_company_id')
newids <- data[!data[,'cds_company_id'] %in% olddata[,'cds_company_id'], 'cds_company_id']
sqlstr <- "INSERT INTO CDS_Issuers VALUES('%s', %s, '%s', '%s', '%s', '%s', '%s')"
with(prep.data,
for(i in newids){
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
sqlstr <- "UPDATE CDS_Issuers SET index_list=index_list||%s where company_id=%s"
for(id in prep.data[,2]){
dbSendQuery(dbCon, sprintf(sqlstr, newid, id))
}
|
