diff options
Diffstat (limited to 'R/mapping.R')
| -rw-r--r-- | R/mapping.R | 140 |
1 files changed, 140 insertions, 0 deletions
diff --git a/R/mapping.R b/R/mapping.R new file mode 100644 index 00000000..2dfafe64 --- /dev/null +++ b/R/mapping.R @@ -0,0 +1,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")]
+
|