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
|
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
mlpdbCon <- dbConnect(drv, dbname="mlpdb", user="mlpdb_user", password="Serenitas1",
host="debian")
nameToBasketID <- function(name, date=Sys.Date()){
sqlstr <- "SELECT * from nametobasketid('%s', '%s')"
r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, name, date))
return(as.integer(r))
}
load.index <- function(name, date=Sys.Date(), tenor="5yr", Z, w, N){
id <- nameToBasketID(name, date)
sqlstr <- "SELECT indexfactor, cumulativeloss, maturity from index_desc where basketid=%s and tenor='%s'"
r <- as.list(dbGetQuery(mlpdbCon, sprintf(sqlstr, id, tenor)))
if(any(c(missing(Z), missing(w), missing(N)))){
return( list(tenor=tenor, factor=r$indexfactor/100, maturity=r$maturity,
loss=r$cumulativeloss/100, recovery=0.4, name=name))
}else{
return( list(tenor=tenor, factor=r$indexfactor/100, maturity=r$maturity,
loss=r$cumulativeloss/100, recovery=0.4, name=name, Z=Z, w=w, N=N))
}
}
cdslist <- function(indexname, date=Sys.Date()){
basketid <- nameToBasketID(indexname, date)
sqlstr <- "select * from CDS_Issuers where index_list @> '{%s}'"
return( dbGetQuery(mlpdbCon, 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(mlpdbCon, sprintf("select * from curve_quotes('%s', '%s')", 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){
r <- regexpr("(\\D*)(\\d*)", indexname, perl=T)
cs <- attr(r, "capture.start")
cl <- attr(r, "capture.length")
index <- substr(indexname, cs[1], cs[1]+cl[1]-1)
series <- substr(indexname, cs[2], cs[2]+cl[2]-1)
return(list(index=toupper(index), series=series))
}
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='%s' and series=%s and quotedate::date='%s'",
"and tenor = '%s' order by attach asc")
r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, date, tenor))
## check if set is unique and complete
if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(0, 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='%s' and series=%s and quotedate::date='%s'",
"and tenor = '%s' order by quotedate desc")
distinct.quotes <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, date, tenor))
flag <- FALSE
##we loop through the disctinct quotes until we find a complete set
for(i in seq_along(distinct.quotes)){
sqlstr <- paste("select * from tranche_quotes where index='%s'",
"and series=%s and tenor = '%s' and quotedate='%s'",
"and detach-attach!=5 and quotesource='%s' order by attach asc")
r <- dbGetQuery(mlpdbCon, sprintf(sqlstr, temp$index, temp$series, tenor,
distinct.quotes$quotedate[i],
distinct.quotes$quotesource[i]))
if(all(c(r$attach, 100)==c(0, r$detach))){#set is complete
flag <- TRUE
break
}
}
if(!flag){
stop("no quotes for that day")
}
return( r )
}
|
