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
119
120
|
library(stringr)
source("db.R")
serenitasdb <- dbConn("serenitasdb")
sqlArray <- function(vec){
vec[is.na(vec)] <- "NULL"
return( sprintf("{%s}", paste(vec, collapse=",")) )
}
sqlQuote <- function(svec){
return( str_c("'", svec, ",") )
}
nameToBasketID <- function(name, date=Sys.Date()){
sqlstr <- "SELECT * from nametobasketid($1, $2)"
r <- dbGetQuery(serenitasdb, sqlstr, params=list(name, date))
return(as.integer(r))
}
set.index.desc <- function(index, date=Sys.Date()){
## retrieve factor, loss, and maturity from the database for a given index
## depending on date
if(class(index) != "creditIndex"){
stop("need to pass a credit index")
}
id <- nameToBasketID(index$name, date)
sqlstr <- paste("SELECT indexfactor, cumulativeloss, maturity from index_desc",
"WHERE tenor=$1 and basketid=$2")
r <- as.list(dbGetQuery(serenitasdb, sqlstr, params=list(index$tenor, id)))
if(id %in% c(447L, 449L, 453L)) { #NSINO didn't trigger for XO22 in tranches
r$indexfactor <- r$indexfactor + 1 + 1/3
}
for(key in c("tradedate", "factor", "loss", "maturity")) {
index[[key]] <- NULL
}
return(c(index,
list(tradedate=date, factor=r$indexfactor/100,
loss=r$cumulativeloss/100, maturity=r$maturity)))
}
cdslist <- function(indexname, date=Sys.Date()){
basketid <- nameToBasketID(indexname, date)
sqlstr <- "select * from bbg_issuers where Array[$1::int] && index_list"
return( dbGetQuery(serenitasdb, sqlstr, params=list(basketid)))
}
arr.convert <- function(arr, col.names){
## convert into a sqlarray into an R arraay
## inverse of sqlArray
if(missing(col.names)){
ncol <- str_count(arr[1], ",") + 1
}else{
ncol <- length(col.names)
}
arr <- str_split_fixed(str_sub(arr, 2, -2), ",", ncol)
arr[arr=="NULL"] <- NA
storage.mode(arr) <- "numeric"
if(!missing(col.names)){
colnames(arr) <- col.names
}
return(arr)
}
get.singlenamesquotes <- function(indexname, date=Sys.Date()){
r <- dbGetQuery(serenitasdb, "SELECT * from curve_quotes($1, $2)",
params = list(indexname, date))
tenors <- c("6m", paste0(c(1, 2, 3, 4, 5, 7, 10), "y"))
quotes <- list(tickers=r[,1],
spread_curve = arr.convert(r$spread_curve, tenors),
upfront_curve =arr.convert(r$upfront_curve, tenors),
recovery_curve = arr.convert(r$recovery_curve, tenors))
return( quotes )
}
indexsplit <- function(indexname){
return(list(indextype = str_to_upper(str_sub(indexname, 0, 2)),
series=str_sub(indexname, 3, -1)))
}
get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){
## first try the easy way:
sqlstr <- "SELECT * from get_tranche_quotes($1, $2, $3, $4)"
r <- with(indexsplit(indexname),
suppressWarnings(
dbGetQuery(serenitasdb, sqlstr, params = list(indextype, series, tenor, date))
)
)
return( r )
}
couponfromindex <- function(indexname, tenor){
sqlstr <- "select coupon from index_maturity where index=$1 and series=$2 and tenor=$3"
r <- with(indexsplit(indexname),
dbGetQuery(serenitasdb, sqlstr, params = list(indextype, series, tenor)))
r$coupon
}
getlastdate <- function(indexname, tenor){
stmt <- paste("SELECT max(date)+1 AS date FROM risk_numbers_old",
"WHERE index=$1 and series=$2 and tenor=$3")
r <- with(indexsplit(indexname),
dbGetQuery(serenitasdb, stmt, params =
list(indextype, series, tenor)))
return( r$date )
}
get.indexquotes <- function(indexname, tenor) {
stmt <- "SELECT * from index_quotes WHERE index=$1 and series=$2 and tenor=$3 ORDER BY date"
return ( with(indexsplit(indexname),
dbGetQuery(serenitasdb, stmt, params=list(indextype, series, tenor))) )
}
get.skews <- function(indexname, tenor){
stmt <- "select date, skew from risk_numbers where index=$1 and series=$2 and tenor=$3 order by date"
arr <- with(indexsplit(indexname),
dbGetQuery(serenitasdb, stmt, params=list(indextype, series, tenor)))
dates <- arr$date
skews <- data.frame(dates, arr.convert(arr$skew))
return ( skews[,-ncol(skews)] )
}
|
