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
|
library(DBI)
serenitasdb <- dbConnect(RPostgres::Postgres(),
dbname="serenitasdb",
user = "serenitas_user",
host="debian")
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)))
r$maturity <- as.Date(r$maturity)
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 CDS_Issuers where index_list @> '{%s}'"
return( dbGetQuery(serenitasdb, sprintf(sqlstr, basketid)))
}
arr.convert <- function(arr){
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)/8, ncol=8, 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"))
}
|
