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
|
library(DBI)
serenitasdb <- dbConnect(RPostgres::Postgres(),
dbname="serenitasdb",
user = "serenitas_user",
host="debian")
sqlArray <- function(vec){
vec[is.na(vec)] <- "NULL"
return( sprintf("{%s}", paste(vec, collapse=",")) )
}
sqlQuote <- function(slist){
return( unlist(lapply(slist, function(s) sprintf("'%s'", s)) ) )
}
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, ncol=8){
arr <- unlist(lapply(arr, function(x)strsplit(substr(x,2,nchar(x)-1),",",fixed=TRUE)))
arr[arr=="NULL"] <- NA
arr <- matrix(as.numeric(arr), nrow=length(arr)/ncol, ncol=ncol, byrow=T)
colnames(arr) <- c("6m", "1y", "2y", "3y", "4y", "5y", "7y", "10y")
return(arr)
}
get.indexquotes <- function(indexname, date=Sys.Date()){
r <- dbGetQuery(serenitasdb, "SELECT * from curve_quotes($1, $2)",
params = list(indexname, date))
quotes <- list(tickers=r[,1],
spread_curve = arr.convert(r$spread_curve),
upfront_curve =arr.convert(r$upfront_curve),
recovery_curve = arr.convert(r$recovery_curve))
return( quotes )
}
indexsplit <- function(indexname){
return(list(index = toupper(substr(indexname, 0, 2)),
series=substr(indexname, 3, nchar(indexname))))
}
get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){
temp <- indexsplit(indexname)
## 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")
r <- dbGetQuery(serenitasdb, sqlstr,
params = list(temp$index, temp$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(temp$index, temp$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)){
if(temp$index == 'HY' && temp$series>=15){ ##don't want the tranchelets quoted by CITI
sqlstr <- paste("select * from tranche_quotes where index=$1",
"and series=$2 and tenor = $3 and quotedate=$4",
"and detach-attach!=5 and quotesource=$5 order by attach asc")
}else{
sqlstr <- paste("select * from tranche_quotes where index=$1",
"and series=$2 and tenor = $3 and quotedate=$4",
"and quotesource=$5 order by attach asc")
}
r <- dbGetQuery(serenitasdb, sqlstr,
params = list(temp$index, temp$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){
stop("no quotes for that day")
}
return( r )
}
couponfromindex <- function(indexname, tenor){
temp <- indexsplit(indexname)
sqlstr <- "select coupon from index_maturity where index=$1 and series=$2 and tenor=$3"
r <- dbGetQuery(serenitasdb, sqlstr, params = list(temp$index, temp$series, tenor))
r$coupon
}
getlastdate <- function(indexname, tenor){
temp <- indexsplit(indexname)
r <- dbGetQuery(serenitasdb,
paste("SELECT max(date)+1 AS date FROM risk_numbers",
"WHERE index=$1 and series=$2 and tenor=$3"),
params = list(temp$index, temp$series, tenor))
return(as.Date(r$date, format="%Y-%m-%d"))
}
|
