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
107
108
109
110
111
112
113
114
115
116
117
118
|
library(Rblpapi)
source("serenitasdb.R")
blpConnect(host='192.168.9.61')
## This script helps adding the necessary data for a new index series
bbgname <- function(indextype, series, tenor="5Y"){
## 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'
}
return( sprintf("%s %s CDSI S%s %s Corp", indexname, indextype, series, tenor) )
}
newbasketID <- function(indextype='IG', series, version=1, mat_5yr, coupon, red){
sqlstr <- paste0("INSERT INTO index_version",
"(index, series, version, indexfactor, cumulativeloss, lastdate, redindexcode) ",
"VALUES($1, $2, $3, 100, 0, 'infinity', $4) RETURNING basketid")
if(missing(red)) {
red <- bdp(bbgname(indextype, series), "CDS_RED_PAIR_CODE")$CDS_RED_PAIR_CODE
}
basketid <- as.integer(dbGetQuery(serenitasdb, sqlstr,
params=list(indextype, series, version, red))$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)"
## TODO: add issue date
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){
index <- bds(bbgname(indextype, series), "INDX_MEMBERS")
tickers <- index[,5]
cds.fields <- c("cds_company_id", "cds_restructuring_type", "cds_corp_tkr", "sw_seniority")
df <- bdp(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,]
if(nrow(prep.data) > 0){
cdscurves <- data.frame()
for(ticker in prep.data$curve_ticker){
cdscurves <- rbind(cdscurves,
data.frame(ticker=ticker,
bds(paste(ticker, "Curncy"), "CDS_CURVE_INFO")))
}
temp <- aggregate(Ticker~ticker, data=cdscurves, FUN=sqlArray)
colnames(temp) <- c("curve_ticker", "cds_curve")
prep.data <- merge(prep.data, temp)
prep.data$doc_clause <- gsub("-", " ", 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) ON CONFLICT DO NOTHING")
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)
}
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)
}
})
}
if(interactive()) {
basket.id <- newbasketID('XO', 29, 1, as.Date("2023-06-20"), 100)
df <- newcurves('XO', 29, basket.id)
mappings <- data.frame(company_id = df$company_id,
markit_ticker = c("EUROP", "CCK-CEH", "PUBLIPO", "WINTRE", "ADLEREA",
"ALGEGLO", "BEVCLUX","DUFRONE", "NIDDAH", "PICABON",
"STEIEUR", "VERIMID"),
seniority = rep("SNRFOR",12), coupon=500)
markit.mappings(mappings)
## then update index_list in markit/import_quotes.py and rerun insert_cds
}
|
