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
|
library(RPostgreSQL)
options(stringsAsFactors=FALSE)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="ET", user="et_user", password="Serenitas;1", host="192.168.1.108")
prices <- dbGetQuery(dbCon, "select * from latest_markit_prices")
collat <- dbGetQuery(dbCon, "select * from et_collateral")
userLoanxid <- dbGetQuery(dbCon, "select * from loanx_user_mapping")
userCUSIP <- dbGetQuery(dbCon, "select * from cusip_user_mapping")
bbgmap <- read.csv(file="Z:/CorpCDOs/data/lx-bbgmap.csv", header = TRUE,colClasses = "character")
RMBSmap <- read.csv(file="Z:/CorpCDOs/data/MTGECusips.csv", header = FALSE,colClasses = "character")
prices$mid <-(prices$bid + prices$offer)/2
prices$spread <- prices$spread/100
from <- c(' LLC.?$', ', INC.?$', ' Inc.?$',' GMBH.?$', ' SA$', ' LTD$', ' PLC$',
' CORP$', ' S A.?$', ' S.A.?$', ' BV$', ' AB$', ' LP$', ' N.V.?$',
' AG$', ' CO.?$', ' HLDG.?$', ' HLDGS?$', ' S P A$', ' S.P.A.?$', ' A/S$', ' Holdings$'
' B.V.$', '(', ')')
prices$scrubedissuer <- prices$issuer
collat$scrubedissuer <- collat$issuername
for (i in 1:length(from)){
prices$scrubedissuer <- gsub(from[i], '', prices$scrubedissuer, ignore.case = TRUE)
collat$scrubedissuer <- gsub(from[i], '', collat$scrubedissuer, ignore.case = TRUE)
}
#collat1 <- collat
collat1 <- collat[collat$dealname == "abrlf",]
#collat1$src[!is.na(collat1$price)] <- "IDMatch"
#collat1$src[collat1$cusip %in% RMBSmap$V1] <- "RMBS"
#collat1$iscdo[collat1$cusip %in% CLOmap$cusip] <- TRUE
for (i in 1:length(collat1$issuername)){
#Match CUSIP vs. LoanxID
matched0 <- grep(collat1$cusip[i], bbgmap$cusip, ignore.case=TRUE)
if (is.na(collat1$loanxid[i])&!is.na(collat1$cusip[i])&length(matched0)==1) {
collat1$loanxid[i] <- bbgmap$loanxid[matched0]
matched0a <- grep(collat1$loanxid[i], prices$loanxid, ignore.case=TRUE)
if(length(matched0a)==1) collat1$price[i] <- prices$mid[matched0a]
collat1$src[i]<- "CUSIPMapped"
}
if (is.na(collat1$loanxid[i])){
collat1$et_loanxid[i] <- userLoanxid$loanxid[collat1$scrubedissuer[i] == userLoanxid$issuername & collat1$maturity[i] == userLoanxid$maturity & collat1$spread[i] == userLoanxid$spread]
collat1$src[i]<- "UserMapped"
}
if (is.na(collat1$loanxid[i])&is.na(collat1$et_loanxid[i])) {
#matchN1 <- grep(collat1$scrubedissuer[i], prices$scrubedissuer, ignore.case=TRUE)
#matchN1 <- prices$scrubedissuer %in% matchN1
maxchange <- as.integer(nchar(collat1$scrubedissuer[i])*.25)#25% of string length
z <- data.frame(x=as.numeric(adist(collat1$scrubedissuer[i], prices$scrubedissuer, ignore.case = TRUE)),y=prices$scrubedissuer)
z <- z[order(z$x),]
z <- z[z$x<=maxchange,]$y
matchN1 <- prices$scrubedissuer %in% z
matchS1 <- prices$spread == collat1$spread[i] #Match Spread
matchS2 <- abs(prices$spread - collat1$spread[i]) <=.5 #Match Spread+/-50bps
matchS3 <- abs(prices$spread - collat1$spread[i]) <=1 #Match Spread+/-100bps
matchD1 <- abs(prices$maturity - collat1$maturity[i]) <=2 #Match Maturity 2 day diff
matchD2 <- abs(prices$maturity - collat1$maturity[i]) <=30 #Match Maturity <=30 days diff
matchD3 <- abs(prices$maturity - collat1$maturity[i]) <=200 #Match Maturity <=200 days diff
CompMatchV1NA <- matchN1 & matchS1 & matchD1
CompMatchV2NA <- matchN1 & matchS2 & matchD1
CompMatchV3NA <- matchN1 & matchS2 & matchD2
CompMatchV4NA <- matchN1 & matchS3 & matchD1
CompMatchV5NA <- matchN1 & matchS1 & matchD3
CompMatchV6NA <- matchN1 & matchS1 & matchD3
CompMatchV1NA[is.na(CompMatchV1NA)] <- FALSE
CompMatchV2NA[is.na(CompMatchV2NA)] <- FALSE
CompMatchV3NA[is.na(CompMatchV3NA)] <- FALSE
CompMatchV4NA[is.na(CompMatchV4NA)] <- FALSE
CompMatchV5NA[is.na(CompMatchV5NA)] <- FALSE
CompMatchV1 <- CompMatchV1NA
CompMatchV2 <- CompMatchV2NA
CompMatchV3 <- CompMatchV3NA
CompMatchV4 <- CompMatchV4NA
CompMatchV5 <- CompMatchV5NA
updatetable <- FALSE
#Matches Found, Randomly picks the first one
if (any(CompMatchV1)){
collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV1)][1]
updatetable <- TRUE
collat1$price[i] <- prices$mid[which(CompMatchV1)][1]
collat1$src[i]<- "MatchedNameSpreadDate"
} else if (any(CompMatchV2)) {
collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV2)][1]
updatetable <- TRUE
collat1$price[i] <- prices$mid[which(CompMatchV2)][1]
collat1$src[i]<- "MatchedNameSpread+-50bpsDate"
} else if (any(CompMatchV3)) {
collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV3)][1]
updatetable <- TRUE
collat1$price[i] <- prices$mid[which(CompMatchV3)][1]
collat1$src[i]<- "MatchedNameSpread+-50bpsDate+-30Days"
} else if (any(CompMatchV4)) {
collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV4)]
updatetable <- TRUE
collat1$price[i] <- prices$mid[which(CompMatchV4)][1]
collat1$src[i]<- "MatchedNameSpread+-100bpsDate"
} else if (any(CompMatchV5)) {
collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV5)]
updatetable <- TRUE
collat1$price[i] <- prices$mid[which(CompMatchV5)][1]
collat1$src[i]<- "MatchedNameSpreadDate+-200Days"
}
if (updatetable == true) {
str <- paste0("a.dealname = '", collat1$dealname[i], "' and a.name = '", collat1$name[i], "' and a.maturity = '", collat1$maturity[i], "'")
dbGetQuery(dbCon, paste0("UPDATE et_collateral a SET et_loanxid = '", collat1$et_loanxid[i],"' WHERE ",str))
}
}
}
#collat1$loanxid[collat1$cusip[i] %in% bbgmap$cusip]
#rm(collat)
#gc()
#write.csv(collat, file="Z:/edwin/collat.csv")
write.csv(collat1, file="Z:/edwin/collat1.csv")
write.csv(prices, file="Z:/edwin/prices.csv")
#scrubed <- prices[,c("scrubedissuer", "issuer")]
#scrubed1 <- collat[,c("scrubedissuer", "issuername")]
#write.csv(scrubed, file="Z:/edwin/scrubedissusername.csv")
#write.table(scrubed,"clipboard", sep = "\t")
#pD1 <- merge(collat, prices, by.x=c("loanxid"), by.y=c("loanxid"))
#pD1$src <- "LXID"
pD2 <- merge(pD1, prices, by.x=c("scrubedissuer.x", "spread.x","maturity.x"),
by.y=c("scrubedissuer", "spread","maturity"))
pD2$src <- "ExactMatch"
pD3[1:50,c("issuername", "loanxid.y", "loanxid.x")]
## pD2 <- pD1[c("issuername","loanxid","mid")]
|
