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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
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)))
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 <- paste("select * from tranche_quotes",
"where index=$1 and series=$2 and quotedate::date=$3",
"and tenor = $4 order by attach asc")
temp <- indexsplit(indexname)
indextype <- temp$indextype
series <- temp$series
r <- dbGetQuery(serenitasdb, sqlstr,
params = list(indextype, series, date, tenor))
## check if set is unique and complete
## hy9 and hy10 tranche is gone
if(tolower(indexname) %in% c("hy9", "hy10")){
lower.attach <- 10
}else{
lower.attach <- 0
}
if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(lower.attach, r$detach))){
return(r)
}
##else we work harder
##we get the list of distinct quotes
sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes",
"where index=$1 and series=$2 and quotedate::date=$3",
"and tenor = $4 order by quotedate desc")
distinct.quotes <- dbGetQuery(serenitasdb, sqlstr,
params = list(indextype, series, date, tenor))
flag <- FALSE
##we loop through the disctinct quotes until we find a complete set
if(nrow(distinct.quotes)==0){
return(NULL)
}
for(i in 1:nrow(distinct.quotes)){
sqlstr <- str_c("select * from tranche_quotes where index=$1 ",
"and series=$2 and tenor = $3 and quotedate=$4 ",
if(indextype == 'HY' && series>=15) "and detach-attach!=5 " else NULL,
"and quotesource=$5 order by attach asc")
r <- dbGetQuery(serenitasdb, sqlstr,
params = list(indextype, series, tenor,
distinct.quotes$quotedate[i],
distinct.quotes$quotesource[i]))
if(all(c(r$attach, 100)==c(lower.attach, r$detach))){#set is complete
flag <- TRUE
break
}
}
if(!flag){
return(NULL)
}
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",
"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(indexplit(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)] )
}
|
