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