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 = list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/Ultimate Panel Data/",full.names = TRUE)
#
# panel = lapply(files, read_fst, as.data.table = TRUE)
# panel <- do.call(rbind , panel)
#
#
# panel <- panel[,c("respondentid","agencycode","reportername","asofdate","parentname","parentidentifier","reporterhomecity","reporterhomestate","rssd")]
# 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()
#
#
#
# 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"),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,"LASALLE BK - BANK OF AMERICA 2008",panel[substr(reportername,1,7)=="LASALLE" & asofdate<=2005]$hmda_id,c("1-0000013044"),
# 2005,"BANK OF AMERICA, N.A.",2008)
# mergers[[5]] <- temp
#
#
# temp <- list(6,"ABN AMRO MTG GROUP - CITI BANK 2007",c("1-36-3744610"),unique(panel[parentidentifier=="0001951350"]$hmda_id),
# 2004,"CITIMORTGAGE, INC.",2007)
# mergers[[6]] <- temp
#
# temp <- list(7,"UNION PLANTERS BANK - REGIONS FINANCIAL CORP 2004",
# c("1-0000013349"),
# c("9-0000233031","2-0000233031"),
# 2002,c("REGIONS BANK"),2004)
# mergers[[7]] <- temp
#
# temp <- list(8,"AmSouth Bancorporation - REGIONS FINANCIAL CORP 2006",
# c("2-0000245333"),
# c("9-0000233031","2-0000233031"),
# 2004,c("REGIONS BANK"),2006)
# mergers[[8]] <- temp
#
#
# temp <- list(9,"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[[9]] <- temp
#
#
# ## target operated in only 2 counties
# temp <- list(10,"Century Bancshares - Wells Fargo 2007",
# c("1-0000005636"),
# panel[substr(reportername,1,7)=="WELLS F"]$hmda_id,
# 2000,c("WELLS FARGO BANK, N.A."),2007)
# mergers[[10]] <- temp
#
#
# ## target operated in 5 msas; small share.
# temp <- list(11,"Placer Sierra Bank - Wells Fargo 2007",
# c("2-0000033772"),
# panel[substr(reportername,1,7)=="WELLS F"]$hmda_id,
# 2002,c("WELLS FARGO BANK, N.A."),2007)
# mergers[[11]] <- temp
#
#
# ## target operated in 5 msas; small share.
# temp <- list(12,"Greater Bay Bank - Wells Fargo 2007",
# c("1-0000024489"),
# panel[substr(reportername,1,7)=="WELLS F"]$hmda_id,
# 2005,c("WELLS FARGO BANK, N.A."),2007)
# mergers[[12]] <- temp
#
#
# temp <- list(13,"MBNA NA - BANK OF AMERICA 2005",c("1-0000024095"),c("1-0000013044"),
# 2003,"BANK OF AMERICA, N.A.",2005)
# mergers[[13]] <- temp
#
# temp <- list(14,"Merrill Lynch - BANK OF AMERICA 2008",c("2-0000421203","7-13-3403204","3-13-3098068","3-13-3399559"),c("1-0000013044"),
# 2005,"BANK OF AMERICA, N.A.",2008)
# mergers[[14]] <- temp
#
#
# #This is the 2nd mortgage division to be named PNC Mortgage. In 2001, PNC sold the original PNC Mortgage to Washington Mutual due to volatility in the market.[7] In 2005, PNC began outsourcing mortgages to Wells Fargo until the National City dea
# temp <- list(15,"National City - PNC Bank 2007",
# c("1-0000000786"),
# c("1-0000001316","2-0000083311"),
# 2004,c("PNC BANK, N.A."),2007)
# mergers[[15]] <- 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
# 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)]
# temp[,suc:=ifelse(lender %in% suc_hmda_id,1,0)]
#
# cw <- temp[,.(pred_share=mean(pred),suc_share=mean(suc)),by=.(cbsa)]
# cw <- cw[!is.na(cbsa)]
# cw[,joint_share:=pred_share+suc_share]
# cw[,bank:=acname]
# cw[,acyr:=acyr]
#
# # sumtable1 <- c(mname,
# # sum(temp$pred)/nrow(temp),
# # nrow(cw[pred_share>0]),
# # length(unique(temp[pred==1]$state)),
# # quantile(cw$pred_share,0.01,na.rm=T),
# # quantile(cw$pred_share,0.1,na.rm=T),
# # quantile(cw$pred_share,0.25,na.rm=T),
# # quantile(cw$pred_share,0.5,na.rm=T),
# # quantile(cw$pred_share,0.75,na.rm=T),
# # quantile(cw$pred_share,0.90,na.rm=T),
# # quantile(cw$pred_share,0.99,na.rm=T))
# # sumtable2 <- c("",
# # sum(temp$suc)/nrow(temp),
# # nrow(cw[suc_share>0]),
# # length(unique(temp[suc==1]$state)),
# # quantile(cw$suc_share,0.01,na.rm=T),
# # quantile(cw$suc_share,0.1,na.rm=T),
# # quantile(cw$suc_share,0.25,na.rm=T),
# # quantile(cw$suc_share,0.5,na.rm=T),
# # quantile(cw$suc_share,0.75,na.rm=T),
# # quantile(cw$suc_share,0.90,na.rm=T),
# # quantile(cw$suc_share,0.99,na.rm=T))
# # sumtable3 <- c("",
# # "",
# # "",
# # length(unique(temp[suc==1 | pred==1]$state)),
# # quantile(cw$joint_share,0.01,na.rm=T),
# # quantile(cw$joint_share,0.1,na.rm=T),
# # quantile(cw$joint_share,0.25,na.rm=T),
# # quantile(cw$joint_share,0.5,na.rm=T),
# # quantile(cw$joint_share,0.75,na.rm=T),
# # quantile(cw$joint_share,0.90,na.rm=T),
# # quantile(cw$joint_share,0.99,na.rm=T))
# #
# # sumtable <- rbind(sumtable,sumtable1)
# # sumtable <- rbind(sumtable,sumtable2)
# # sumtable <- rbind(sumtable,sumtable3)
#
# cw[,c("suc_share","joint_share"):=list(NULL)]
# cbsa_bnk <- rbind(cbsa_bnk,cw)
#
# a <- data.frame(lender=mergers[[i]][4][[1]],bank=rep(mergers[[i]][6][[1]],length(mergers[[i]][4][[1]])))
# if(is.null(lender_bank)) {
# lender_bank <- a
# } else {
# lender_bank <- rbind(lender_bank,a)
# }
# }
#
# lender_bank <- lender_bank[!duplicated(lender_bank),]
# write_fst(lender_bank,"C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/lender_bank.fst",compress = 100)
#
# # write.csv(sumtable,file="C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/merger_summaries.csv",row.names = F)
# 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)
#
# 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[,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_share <- cbsa_bnk[,.(msinc13=sum(msinc13,na.rm=T)),by=.(cbsa,acyr)]
#
# write_fst(cbsa_bnk,"C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_bnk.fst",compress = 100)
#
# write_fst(cbsa_share,"C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_share.fst",compress = 100)
cbsa_bnk <- read_fst("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_bnk.fst",as.data.table = T)
cbsa_bnk[,msinc13Q:=ifelse(msinc13<=0.0001,"Q0",
ifelse(msinc13<0.0027235,"Q1",
ifelse(msinc13<0.0112596,"Q2",
ifelse(msinc13<0.0366354,"Q3","Q4"))))]
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[,msinc46Q:=ifelse(msinc46<=0.0001,"Q0",
ifelse(msinc46<0.0027235,"Q1",
ifelse(msinc46<0.0112596,"Q2",
ifelse(msinc46<0.0366354,"Q3","Q4"))))]
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_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"))))]
