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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
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 <- paste0("INSERT INTO index_version",
"(index, series, version, indexfactor, cumulativeloss, lastdate) ",
"VALUES($1, $2, $3, 100, 0, 'infinity') RETURNING basketid")
basketid <- as.integer(dbGetQuery(serenitasdb, sqlstr, params=list(indextype, series, 1))$basketid)
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(serenitasdb, sqlstr,
params=list(indextype, series, tenor[i], maturity[i], coupon))
if(dbHasCompleted(r)){
dbClearResult(r)
}
}
return(basketid)
}
newcurves <- function(indextype, series, basketid){
## indextype is EUR, IG, HY or XOVER
if(indextype=="EU"){
indextype <- "EUR"
indexname <- 'ITRX'
}else if(indextype=="XO"){
indextype <- "XOVER"
indexname <- "ITRX"
}else{
indexname <- 'CDX'
}
index <- bds(bbgConn, sprintf("%s %s CDSI S%s 5Y Corp", indexname, indextype, series),
"INDX_MEMBERS")
tickers <- index[,5]
cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority")
df <- bdp(bbgConn, paste(tickers, "Corp"), cds.fields)
df <- cbind(tickers, df)
prep.data <- with(df, data.frame(Name=index[,1], company_id=cds_company_id,
ticker=cds_corp_tkr, currency=index[,3],
seniority=sw_seniority, doc_clause=cds_restructuring_type,
curve_ticker=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 <- data.frame()
for(ticker in prep.data$curve_ticker){
cdscurves <- rbind(cdscurves,
data.frame(ticker=ticker,
bds(bbgConn, paste(ticker, "Curncy"), "CDS_CURVE_INFO")))
}
prep.data$cds_curve <- aggregate(Ticker~ticker, data=cdscurves, FUN=sqlArray)$Ticker
levels(prep.data$doc_clause) <- gsub("-", " ", levels(prep.data$doc_clause))
sqlstr <- paste0("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], cds_curve[i]))
if(dbHasCompleted(r)){
dbClearResult(r)
}
})
sqlstr <- "UPDATE bbg_issuers SET index_list=index_list||$1::int where company_id=$2"
for(id in df$cds_company_id){
r <- dbSendQuery(serenitasdb, sqlstr, params=list(basketid, id))
if(dbHasCompleted(r)){
dbClearResult(r)
}
}
return(prep.data)
}
## company_id markit_ticker seniority coupon
## 1 100104 APA SNRFOR 100
## 2 109584 ENB SNRFOR 100
## mappings <- data.frame(company_id=df$company_id,
## markit_ticker=c("FIATCHR","ZIGGBON"),
## seniority="SNRFOR",coupon=500)
markit.mappings <- function(df){
sqlstr <- "INSERT INTO bbg_markit_mapping VALUES('infinity', $1, $2, $3, $4)"
with(df,
for(i in 1:nrow(df)){
r <- dbSendQuery(serenitasdb, sqlstr,
params=list(company_id[i],
markit_ticker[i],
seniority[i],
coupon[i]))
if(dbHasCompleted(r)){
dbClearResult(r)
}
})
}
blpDisconnect(bbgConn)
|
