Refinances
yearrank <- hmda[purposeofloan!="1",.N,by=.(asofdate,unique_id)]
yearrank[,rank:=frankv(N,order=-1),by=asofdate]
yearrank[,rank:=ifelse(rank<=5,"top5","other")]
yearrank[,N:=NULL]
msarank <- hmda[purposeofloan!="1",.N,by=.(asofdate,cbsa,unique_id)]
msarank <- merge(msarank,yearrank,by=c("asofdate","unique_id"))
msarank <- msarank[!is.na(cbsa)]
msarank <- msarank[,.(N=sum(N)),by=.(asofdate,cbsa,rank)]
msarank <- dcast(msarank,asofdate+cbsa~rank)
msarank[is.na(msarank)] <- 0
msarank[,top5:=top5/(top5+other)]
msarank[,top5:=top5*100]
msarank <- msarank[,.(P25=quantile(top5,0.25,na.rm = T),P50=quantile(top5,0.5,na.rm = T),P75=quantile(top5,0.75,na.rm = T)),by=asofdate]
msarank <- melt(msarank,id.vars = c("asofdate"))
msarank[,variable:=as.character(variable)]
ggplot(msarank)+geom_line(aes(x=asofdate,y=value,color=variable),size=1)+theme_minimal()+scale_color_manual(values=c('royalblue','darkgreen',"darkred"))+theme(legend.position = "bottom", legend.title = element_blank(),
panel.border = element_blank(), axis.line.x = element_line(color = "gray80"),
axis.text.x = element_text(angle = 90, hjust = 1))+labs(x="",y="% of loans") +scale_x_continuous(breaks = 1990:2019)
files = paste0("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/Ultimate Panel Data/",as.character(2000:2016),".fst")
panel = lapply(files, read_fst, as.data.table = TRUE,columns=c("respondentid","agencycode","reportername","asofdate","parentname","parentidentifier","reporterhomecity","reporterhomestate","rssd"))
panel <- do.call(rbind , panel)
panel[,asofdate:=as.integer(asofdate)]
panel <- panel[!duplicated(panel[,c("respondentid","agencycode","asofdate")])]
panel[,parentidentifier:=stri_trim(parentidentifier)]
panel[,rssd:=as.numeric(rssd)]
panel[,hmda_id:=paste0(agencycode,"-",respondentid)]
files <- NULL
files <- list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/pre2004/OO_NP/",full.names = TRUE)
files <- c(files,list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/pre2004/OO_RF/",full.names = TRUE))
files <- c(files,list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/OO_NP/",full.names = TRUE))
files <- c(files,list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/OO_RF/",full.names = TRUE))
hmda = lapply(files, read_fst, as.data.table = TRUE,
columns=c("asofdate","respondentid","agencycode","state","countycode","msa"))
hmda <- do.call(rbind , hmda)
hmda[,lender:=paste0(agencycode,"-",respondentid)]
hmda[,countycode:=paste0(state,countycode)]
cbsa_fips <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Crosswalk Files/cbsa_countyfips.csv")
cbsa_fips[,fips:=ifelse(nchar(fips)==4,paste0("0",fips),paste0(fips))]
hmda <- merge(hmda,cbsa_fips,by.x="countycode",by.y="fips",all.x=T)
hmda[,c("agencycode","respondentid"):=list(NULL)]
gc()
## used (Mb) gc trigger (Mb) max used (Mb)
## Ncells 2197739 117.4 4105467 219.3 4105467 219.3
## Vcells 2224100639 16968.6 6822701357 52053.1 7327129911 55901.6
mergers <- list()
temp <- list(1,"BANK ONE - JPMORGAN CHASE 2004",
c("1-0000000008","1-0000007621","1-0000003106","1-0000011230","1-0000013655","1-0000013759","1-0000013914","1-0000014320","1-0000015184","1-0000018785","1-0000021969","1-0000023237","2-0000331647","3-0000002487"),
unique(c(unique(panel[parentidentifier %in% c("0000002370","0000000008","0001039502","0000852218","0001040795"),]$hmda_id),"2-0000852218","1-0000023160","2-0000043557","1-22-1092200","1-0000000008")),
2000,"JPMORGAN CHASE BANK, NA",2004)
mergers[[1]] <- temp
temp <- list(2,"COUNTRYWIDE - BANK OF AMERIC 2009" ,c("1-0000024141","2-0001644643","2-0003267484","7-20-2241771","1-0000024141","4-0000018039"),c("1-0000013044"),
2005,"BANK OF AMERICA, N.A.",2009)
mergers[[2]] <- temp
temp <- list(3,"FLEET NA - BANK OF AMERICA 2005",c("1-0000000200"),c("1-0000013044"),
2003,"BANK OF AMERICA, N.A.",2005)
mergers[[3]] <- temp
temp <- list(4,"WACHOVIA BK NA - WELLS FARGO 2010", c("1-0000000001","1-0000022559","1-56-0811711"), panel[substr(reportername,1,7)=="WELLS F"]$hmda_id,
2005,"WELLS FARGO BANK, N.A.",2009)
mergers[[4]] <- temp
temp <- list(5,"ABN AMRO MTG GROUP - CITI BANK 2007",c("1-36-3744610"),unique(panel[parentidentifier=="0001951350"]$hmda_id),
2004,"CITIMORTGAGE, INC.",2007)
mergers[[5]] <- temp
temp <- list(6,"UNION PLANTERS BANK - REGIONS FINANCIAL CORP 2004",
c("1-0000013349"),
c("9-0000233031","2-0000233031"),
2002,c("REGIONS BANK"),2004)
mergers[[6]] <- temp
temp <- list(7,"AmSouth Bancorporation - REGIONS FINANCIAL CORP 2006",
c("2-0000245333"),
c("9-0000233031","2-0000233031"),
2004,c("REGIONS BANK"),2006)
mergers[[7]] <- temp
temp <- list(8,"Washington Mutual - JPMORGAN CHASE 2008",
c("4-0000008551","4-0000011905"),
unique(c(unique(panel[parentidentifier %in% c("0000002370","0000000008","0001039502","0000852218","0001040795"),]$hmda_id),"2-0000852218","1-0000023160","2-0000043557","1-22-1092200","1-0000000008")),
2005,"JPMORGAN CHASE BANK, NA",
2008)
mergers[[8]] <- temp
temp <- list(9,"FIRST INTERSTATE BK CA - Wells Fargo 1996",c("2-0000669667"),panel[substr(reportername,1,7)=="WELLS F"]$hmda_id,
1994,"WELLS FARGO BANK, N.A.",1996)
mergers[[9]] <- temp
cbsas <- unique(hmda$cbsa)
yrs <- 2000:2016
acqbanks <- NULL
for(i in 1:length(mergers)) {
acqbanks <- c(acqbanks,mergers[[i]][6][[1]])
}
acqbanks <- c(unique(acqbanks),"other")
cbsas1 <- merge(cbsas,yrs)
cbsas2 <- merge(cbsas,acqbanks)
cbsas <- merge(cbsas1,cbsas2,by="x")
names(cbsas) <- c("cbsa","acyr","bank")
cbsas <- data.table(cbsas)
cbsas[,bank:=as.character(bank)]
cbsas[,acqbank:=0]
cbsas[,pred_share:=0]
cbsa_bnk <- NULL
lender_bank <- NULL
sumtable <- NULL
for(i in 1:length(mergers)) {
# print(i)
mid=mergers[[i]][1][[1]]
mname=mergers[[i]][2][[1]]
pred_hmda_id=mergers[[i]][3][[1]]
suc_hmda_id=mergers[[i]][4][[1]]
yr=mergers[[i]][5][[1]]
acname = mergers[[i]][6][[1]]
acyr = mergers[[i]][7][[1]]
temp <- hmda[asofdate == yr ]
temp[,pred:=ifelse(lender %in% pred_hmda_id,1,0)]
cw <- temp[,.(pred_share=mean(pred)),by=.(cbsa)]
temp1 <- hmda[asofdate == (acyr-1) ]
temp1[,suc:=ifelse(lender %in% suc_hmda_id,1,0)]
cw1 <- temp1[,.(suc_share=mean(suc)),by=.(cbsa)]
cw <- merge(cw,cw1,by="cbsa",all.x=T)
cw <- cw[!is.na(cbsa)]
cw[is.na(cw)] <- 0
cw[,joint_share:=pred_share+suc_share]
cw[,bank:=acname]
cw[,acyr:=acyr]
cw[,c("suc_share","joint_share"):=list(NULL)]
cbsa_bnk <- rbind(cbsa_bnk,cw)
}
cbsa_bnk[,acqbank:=1]
cbsa_bnk <- rbind(cbsa_bnk,cbsas)
cbsa_bnk <- cbsa_bnk[!duplicated(cbsa_bnk[,c("cbsa","acyr","bank")])]
cbsa_bnk <- cbsa_bnk[!is.na(cbsa)]
cbsa_bnk_1 <- cbsa_bnk[,c("cbsa","pred_share","bank","acyr")]
names(cbsa_bnk_1) <- c("cbsa","pred_share_1","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_2","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_3","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_4","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_5","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_6","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_7","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_8","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
names(cbsa_bnk_1) <- c("cbsa","pred_share_9","bank","acyr")
cbsa_bnk_1[,acyr:=acyr+1]
cbsa_bnk <- merge(cbsa_bnk,cbsa_bnk_1,by=c("cbsa","bank","acyr"),all.x = T)
cbsa_bnk[,pred_share_1:=ifelse(is.na(pred_share_1),0,pred_share_1)]
cbsa_bnk[,pred_share_2:=ifelse(is.na(pred_share_2),0,pred_share_2)]
cbsa_bnk[,pred_share_3:=ifelse(is.na(pred_share_3),0,pred_share_3)]
cbsa_bnk[,pred_share_4:=ifelse(is.na(pred_share_4),0,pred_share_4)]
cbsa_bnk[,pred_share_5:=ifelse(is.na(pred_share_5),0,pred_share_5)]
cbsa_bnk[,pred_share_6:=ifelse(is.na(pred_share_6),0,pred_share_6)]
cbsa_bnk[,pred_share_7:=ifelse(is.na(pred_share_7),0,pred_share_7)]
cbsa_bnk[,pred_share_8:=ifelse(is.na(pred_share_8),0,pred_share_8)]
cbsa_bnk[,pred_share_9:=ifelse(is.na(pred_share_9),0,pred_share_9)]
cbsa_bnk[,msinc13:=pred_share_1+pred_share_2+pred_share_3+0.00001]
cbsa_bnk[,msinc46:=pred_share_4+pred_share_5+pred_share_6+0.00001]
cbsa_bnk[,msinc79:=pred_share_7+pred_share_8+pred_share_9+0.00001]
# cbsa_share <- cbsa_bnk[,.(msinc13=sum(msinc13,na.rm=T)),by=.(cbsa,acyr)]
cbsa_bnk[,msinc13G:=ifelse(msinc13<=0.0001,"0. 0",
ifelse(msinc13<0.01,"1. Less than 1pct",
ifelse(msinc13<0.05,"2. 1 - 5pct",
ifelse(msinc13<0.1,"3. 5pct - 10pct", "4. More than 10pct"))))]
cbsa_bnk[,msinc46G:=ifelse(msinc46<=0.0001,"0. 0",
ifelse(msinc46<0.01,"1. Less than 1pct",
ifelse(msinc46<0.05,"2. 1 - 5pct",
ifelse(msinc46<0.1,"3. 5pct - 10pct", "4. More than 10pct"))))]
cbsa_bnk[,msinc79G:=ifelse(msinc79<=0.0001,"0. 0",
ifelse(msinc79<0.01,"1. Less than 1pct",
ifelse(msinc79<0.05,"2. 1 - 5pct",
ifelse(msinc79<0.1,"3. 5pct - 10pct", "4. More than 10pct"))))]
#
# cbsa_share<- read_fst("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_share.fst",as.data.table = T)
# cbsa_share[,msinc13G:=ifelse(msinc13<=0.0001,"0. 0",
# ifelse(msinc13<0.01,"1. Less than 1pct",
# ifelse(msinc13<0.05,"2. 1 - 5pct",
# ifelse(msinc13<0.1,"3. 5pct - 10pct", "4. More than 10pct"))))]
#
# cbsa_share[,msinc46G:=ifelse(msinc46<=0.0001,"0. 0",
# ifelse(msinc46<0.01,"1. Less than 1pct",
# ifelse(msinc46<0.05,"2. 1 - 5pct",
# ifelse(msinc46<0.1,"3. 5pct - 10pct", "4. More than 10pct"))))]
