rm(list=ls())
library(RPostgres)
library(data.table)
library(getPass)
library(lfe)
library(stargazer)
library(ggplot2)
library(fst)
library(stringr)
library(stringi)
library(lubridate)
# 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"))))]
List of Mergers
Summary Statistics
Freddie
freddie <- list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Freddie/Acq",full.names = TRUE)
freddie = lapply(freddie, read_fst,as.data.table=T, columns = c("fico","dt_first_pi","cd_msa","ltv","dti","orig_upb","int_rt","prop_type","zipcode","id_loan","orig_loan_term","seller_name"))
freddie <- do.call(rbind , freddie)
freddie <- freddie[orig_loan_term==360 & prop_type=="SF"]
freddie[,loanyr:=year(dt_first_pi)]
freddie[,msa:=cd_msa]
freddie[,c("orig_loan_term","dt_first_pi","prop_type","cd_msa"):=list(NULL)]
freddie[,seller_name:= ifelse(seller_name %in% c("JPMORGAN CHASE BANK, NA","JPMORGAN CHASE BANK, NATIONAL ASSOCIATION","CHASE MANHATTAN MORTGAGE CORPORATION","CHASE HOME FINANCE LLC","CHASE HOME FINANCE","CHASE HOME FINANCE, LLC","JPMORGAN CHASE BANK, NATIONAL ASSOCIATION","JPMORGAN CHASE BANK, N.A."),"JPMORGAN CHASE BANK, NA",seller_name)]
freddie[,seller_name:= ifelse(seller_name %in% c("CITIMORTGAGE, INC.","ABN AMRO, NKA CITIMORTGAGE INC.","CITIMORTGAGE, INC."),"CITIMORTGAGE, INC.",seller_name)]
freddie[,seller_name:= ifelse(seller_name %in% c("WELLS FARGO HOME MORTGAGE, INC.","WELLS FARGO BANK, N.A."),"WELLS FARGO BANK, N.A.",seller_name)]
freddie[,bank:=seller_name]
freddie[,bank:=ifelse(bank %in% unique(cbsa_bnk$bank),bank,"other")]
vars <- c("fico","ltv","dti","orig_upb","int_rt","loanyr")
stargazer(freddie[,..vars],type="text",summary.stat = c("N","mean","sd","p25","median","p75"))
##
## ======================================================================
## Statistic N Mean St. Dev. Pctl(25) Median Pctl(75)
## ----------------------------------------------------------------------
## fico 16,245,111 748.509 395.911 695 741 776
## ltv 16,245,111 73.367 17.035 66 78 80
## dti 16,245,111 47.668 113.969 26 35 43
## orig_upb 16,245,111 189,852.800 107,056.600 111,000 165,000 245,000
## int_rt 16,245,111 5.699 1.234 4.750 5.875 6.625
## loanyr 16,245,111 2,007.242 5.630 2,003 2,006 2,012
## ----------------------------------------------------------------------
Fannie
fannie <- list.files(pattern="*.fst",path = "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Fannie/Acq",full.names = TRUE)
fannie = lapply(fannie, read_fst,as.data.table=T, columns = c("loan_identifier","seller_name","original_interest_rate","original_upb","original_loan_term","origination_date","original_ltv","original_dti","credit_score","property_type","property_state","zip_code","msa","original_loan_term"))
fannie <- do.call(rbind , fannie)
fannie <- fannie[original_loan_term==360 & property_type=="SF"]
fannie[,loanyr:=year(origination_date)]
fannie[,seller_name:= ifelse(seller_name %in% c("JPMORGAN CHASE BANK, NA","JPMORGAN CHASE BANK, NATIONAL ASSOCIATION","CHASE MANHATTAN MORTGAGE CORPORATION","CHASE HOME FINANCE LLC","CHASE HOME FINANCE","CHASE HOME FINANCE, LLC","JPMORGAN CHASE BANK, NATIONAL ASSOCIATION","JPMORGAN CHASE BANK, N.A."),"JPMORGAN CHASE BANK, NA",seller_name)]
fannie[,seller_name:= ifelse(seller_name %in% c("CITIMORTGAGE, INC.","ABN AMRO, NKA CITIMORTGAGE INC.","CITIMORTGAGE, INC."),"CITIMORTGAGE, INC.",seller_name)]
fannie[,seller_name:= ifelse(seller_name %in% c("WELLS FARGO HOME MORTGAGE, INC.","WELLS FARGO BANK, N.A."),"WELLS FARGO BANK, N.A.",seller_name)]
fannie[,int_rt:=original_interest_rate]
fannie[,dti:=original_dti]
fannie[,ltv:=original_ltv]
fannie[,fico:=credit_score]
fannie[,orig_upb:= original_upb]
fannie[,c("original_interest_rate","original_dti","original_ltv","credit_score","original_upb","original_loan_term"):=list(NULL)]
fannie[,bank:=seller_name]
fannie[,bank:=ifelse(bank %in% unique(cbsa_bnk$bank),bank,"other")]
vars <- c("fico","ltv","dti","orig_upb","int_rt","loanyr")
stargazer(fannie[,..vars],type="text",summary.stat = c("N","mean","sd","p25","median","p75"))
##
## ======================================================================
## Statistic N Mean St. Dev. Pctl(25) Median Pctl(75)
## ----------------------------------------------------------------------
## fico 20,265,085 736.414 54.521 700.000 747.000 781.000
## ltv 20,356,584 73.223 16.210 65.000 78.000 80.000
## dti 19,932,370 34.426 11.429 26.000 35.000 42.000
## orig_upb 20,356,594 197,387.300 111,665.400 115,000 172,000 254,000
## int_rt 20,356,591 5.543 1.240 4.500 5.625 6.375
## loanyr 20,356,594 2,007.757 5.679 2,003 2,007 2,013
## ----------------------------------------------------------------------
Moodys
moodys <- read_fst("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Moodys/0001/LoanChars.fst",as.data.table = TRUE, columns=c("loanid","loanoriginationdate","zipcode","originalloanbalance","originalcltv","state","originator","armflag","originalfico","originalterm","originalltv","documentationtype","originalinterestrate"))
cbsa <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Crosswalk Files/ZIP_CBSA.csv")
cbsa[,ZIP:=ifelse(nchar(ZIP)==3,paste0("00",ZIP),ifelse(nchar(ZIP)==4,paste0("0",ZIP),paste0(ZIP)))]
setorder(cbsa,ZIP,-RES_RATIO)
cbsa <- cbsa[!duplicated(cbsa[,c("ZIP")])]
cbsa[,c("RES_RATIO","BUS_RATIO","OTH_RATIO","TOT_RATIO"):=list(NULL)]
names(cbsa) <- c("zipcode","msa")
moodys <- merge(moodys,cbsa,by=c("zipcode"))
moodys <- moodys[originalterm==360 & armflag=="F"]
moodys[,loanyr:=as.numeric(substr(loanoriginationdate,1,4))]
moodys[,seller_name:= originator]
moodys[,seller_name:= ifelse(seller_name %in% c("JPMORGAN CHASE BANK, NA","JPMORGAN CHASE BANK, NATIONAL ASSOCIATION","CHASE MANHATTAN MORTGAGE CORPORATION","CHASE HOME FINANCE LLC","CHASE HOME FINANCE","CHASE HOME FINANCE, LLC","JPMORGAN CHASE BANK, NATIONAL ASSOCIATION","JPMORGAN CHASE BANK, N.A.","JP MORGAN CHASE BANK NA","CHASE MANHATTAN MORTGAGE CORP"),"JPMORGAN CHASE BANK, NA",seller_name)]
moodys[,seller_name:= ifelse(seller_name %in% c("B OF A"),"BANK OF AMERICA, N.A.",seller_name)]
moodys[,seller_name:= ifelse(seller_name %in% c("WELLS FARGO HOME MORTGAGE, INC.","WELLS FARGO BANK, N.A.","WELLS FARGO BANK N.A"," WELLS FARGO HOME MTG, INC"),"WELLS FARGO BANK, N.A.",seller_name)]
moodys[,int_rt:=originalinterestrate]
moodys[,dti:=0]
moodys[,ltv:=originalltv]
moodys[,fico:=originalfico]
moodys[,orig_upb:= originalloanbalance]
moodys[,c("originalinterestrate","originalltv","originalfico","originalloanbalance","msacode","csacode","divcode","loanoriginationdate","armflag","originalterm"):=list(NULL)]
moodys[,fulldocumentation:=ifelse(documentationtype=="FU",1,0)]
gc()
## used (Mb) gc trigger (Mb) max used (Mb)
## Ncells 18407461 983.1 50843052 2715.4 28778045 1537
## Vcells 477272546 3641.4 1444685132 11022.1 1224863526 9345
moodys[,bank:=seller_name]
moodys[,bank:=ifelse(bank %in% unique(cbsa_bnk$bank),bank,"other")]
vars <- c("fico","ltv","dti","orig_upb","int_rt","loanyr","fulldocumentation")
stargazer(moodys[,..vars],type="text",summary.stat = c("N","mean","sd","p25","median","p75"))
##
## ======================================================================================
## Statistic N Mean St. Dev. Pctl(25) Median Pctl(75)
## --------------------------------------------------------------------------------------
## fico 3,931,302 681.319 69.930 632.000 684.000 737.000
## ltv 4,322,951 70.640 23.984 63.600 79.700 80.000
## dti 4,399,462 0.000 0.000 0 0 0
## orig_upb 4,398,108 232,097.600 213,490.700 84,980.000 161,000.000 332,000.000
## int_rt 4,391,198 7.441 2.059 6.250 6.890 8.400
## loanyr 4,385,059 2,004.073 3.607 2,004.000 2,005.000 2,006.000
## fulldocumentation 4,399,462 0.397 0.489 0 0 1
## --------------------------------------------------------------------------------------
Descriptive Statistics: Market Share Changes
msinc13
print(ggplot(cbsa_bnk[msinc13>0.00001],aes(x=msinc13))+geom_histogram(fill="royalblue",alpha=0.3,color="royalblue")+theme_minimal())
msinc46
print(ggplot(cbsa_bnk[msinc46>0.00001],aes(x=msinc46))+geom_histogram(fill="royalblue",alpha=0.3,color="royalblue")+theme_minimal())
Dep. Var: Interest Increase by the Acquiring Bank
Market Share Bins
\[ interest rate = \Sigma_{bin} \beta_{bin} \times msinc13 \in bin+ \Sigma_{bin}\beta_{bin} \times msinc46 \in bin+fico+ltv+dti+log(loanamount) +BANK*MSA\text{ }FE+YR\text{ }FE\]
\(msinc13\) : Change in market share in last 3 years
\(msinc46\) : Change in market share 4-6 years
regsample <- merge(freddie,cbsa_bnk,by.x=c("bank","msa","loanyr"),by.y=c("bank","cbsa","acyr"))
# regsample[,msinccat13:=ifelse(msinc13<=0,"0",ifelse(msinc13<= 0.02 ,"Less than 2%",ifelse(msinc13<0.05,"Less than 5%","More than 5%")))]
regsample[,bank_msa:=paste(seller_name,msa)]
# regsample[,yr_msa:=paste(loanyr,msa)]
r <- list()
# r2 <- list()
r3 <- list()
r[[1]] <- felm(int_rt~msinc13+msinc46+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
# r2[[1]] <- felm(int_rt~msinc13+I(msinc13^2)+msinc46+I(msinc46^2)++fico+ltv+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
r3[[1]] <- felm(int_rt~factor(msinc13G)+factor(msinc46G)+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
regsample <- merge(fannie,cbsa_bnk,by.x=c("bank","msa","loanyr"),by.y=c("bank","cbsa","acyr"))
# regsample[,msinccat13:=ifelse(msinc13<=0,"0",ifelse(msinc13<= 0.02 ,"Less than 2%",ifelse(msinc13<0.05,"Less than 5%","More than 5%")))]
regsample[,bank_msa:=paste(seller_name,msa)]
# regsample[,yr_msa:=paste(loanyr,msa)]
r[[2]] <- felm(int_rt~msinc13+msinc46+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
# r2[[2]] <- felm(int_rt~msinc13+I(msinc13^2)+msinc46+I(msinc46^2)+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
r3[[2]] <- felm(int_rt~factor(msinc13G)+factor(msinc46G)+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
regsample <- merge(moodys,cbsa_bnk,by.x=c("bank","msa","loanyr"),by.y=c("bank","cbsa","acyr"))
# regsample[,msinccat13:=ifelse(msinc13<=0,"0",ifelse(msinc13<= 0.02 ,"Less than 2%",ifelse(msinc13<0.05,"Less than 5%","More than 5%")))]
regsample[,bank_msa:=paste(seller_name,msa)]
# regsample[,yr_msa:=paste(loanyr,msa)]
r[[3]] <- felm(int_rt~msinc13+msinc46+fico+ltv+fulldocumentation+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
# r2[[3]] <- felm(int_rt~msinc13+I(msinc13^2)+msinc46+I(msinc46^2)+fico+ltv+fulldocumentation+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
r3[[3]] <- felm(int_rt~factor(msinc13G)+fico+ltv+fulldocumentation+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
.printtable(r3,column.labels = c("Freddie Loans","Fannie Loans","Moody's Loans"),lines = list(c("Fixed Effects","MSA*Bank, Yr","MSA*Bank, Yr","MSA*Bank, Yr")))
##
## ===========================================================================
## Dependent variable:
## ----------------------------------------
## Freddie Loans Fannie Loans Moody's Loans
## (1) (2) (3)
## ---------------------------------------------------------------------------
## factor(msinc13G)1. Less than 1pct 0.035*** 0.062*** -0.160***
## (0.004) (0.004) (0.022)
## factor(msinc13G)2. 1 - 5pct 0.014*** 0.036*** 0.310***
## (0.004) (0.005) (0.082)
## factor(msinc13G)3. 5pct - 10pct 0.062*** 0.044*** 0.515***
## (0.006) (0.010) (0.142)
## factor(msinc13G)4. More than 10pct 0.103*** 0.118*** 1.952***
## (0.008) (0.011) (0.385)
## factor(msinc46G)1. Less than 1pct 0.014*** 0.009*
## (0.005) (0.006)
## factor(msinc46G)2. 1 - 5pct 0.016* 0.019**
## (0.008) (0.008)
## factor(msinc46G)3. 5pct - 10pct 0.030*** 0.016
## (0.008) (0.011)
## factor(msinc46G)4. More than 10pct 0.089*** 0.075***
## (0.009) (0.016)
## fico -0.00002*** -0.001*** -0.005***
## (0.00000) (0.00002) (0.0002)
## ltv 0.004*** 0.004*** -0.011***
## (0.0001) (0.0001) (0.001)
## dti 0.00003*** 0.002***
## (0.00000) (0.0001)
## fulldocumentation -0.227***
## (0.010)
## log(orig_upb) -0.221*** -0.222*** -1.051***
## (0.004) (0.008) (0.018)
## ---------------------------------------------------------------------------
## Fixed Effects MSA*Bank, Yr MSA*Bank, Yr MSA*Bank, Yr
## Observations 8,210,798 15,685,569 3,271,645
## Adjusted R2 0.897 0.902 0.511
## ===========================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
##
Linear
.printtable(r,column.labels = c("Freddie Loans","Fannie Loans","Moody's Loans"),lines = list(c("Fixed Effects","MSA*Bank, Yr","MSA*Bank, Yr","MSA*Bank, Yr")))
##
## ==========================================================
## Dependent variable:
## ----------------------------------------
## Freddie Loans Fannie Loans Moody's Loans
## (1) (2) (3)
## ----------------------------------------------------------
## msinc13 0.798*** 0.768*** 7.835***
## (0.061) (0.056) (2.110)
## msinc46 0.480*** 0.390***
## (0.074) (0.116) (0.000)
## fico -0.00002*** -0.001*** -0.005***
## (0.00000) (0.00002) (0.0002)
## ltv 0.004*** 0.004*** -0.011***
## (0.0001) (0.0001) (0.001)
## dti 0.00003*** 0.002***
## (0.00000) (0.0001)
## fulldocumentation -0.228***
## (0.010)
## log(orig_upb) -0.221*** -0.222*** -1.052***
## (0.004) (0.008) (0.018)
## ----------------------------------------------------------
## Fixed Effects MSA*Bank, Yr MSA*Bank, Yr MSA*Bank, Yr
## Observations 8,210,798 15,685,569 3,271,645
## Adjusted R2 0.897 0.902 0.511
## ==========================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
##
Other Lenders
r <- list()
regsample <- freddie[!seller_name %in% unique(cbsa_bnk$bank)]
regsample <- merge(regsample,cbsa_share,by.x=c("msa","loanyr"),by.y=c("cbsa","acyr"))
regsample[,bank_msa:=paste(seller_name,msa)]
r[[1]] <- felm(int_rt~factor(msinc13G)+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr,data=regsample)
regsample <- fannie[!seller_name %in% unique(cbsa_bnk$bank)]
regsample <- merge(regsample,cbsa_share,by.x=c("msa","loanyr"),by.y=c("cbsa","acyr"))
regsample[,bank_msa:=paste(seller_name,msa)]
r[[2]] <- felm(int_rt~factor(msinc13G)+fico+ltv+dti+log(orig_upb)|bank_msa+loanyr,data=regsample)
regsample <- moodys[!seller_name %in% unique(cbsa_bnk$bank)]
regsample <- merge(regsample,cbsa_share,by.x=c("msa","loanyr"),by.y=c("cbsa","acyr"))
regsample[,bank_msa:=paste(seller_name,msa)]
r[[3]] <- felm(int_rt~factor(msinc13G)+fico+fulldocumentation+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample[seller_name != ""])
.printtable(r)
##
## ==================================================================
## Dependent variable:
## -------------------------------
## (1) (2) (3)
## ------------------------------------------------------------------
## factor(msinc13G)1. Less than 1pct 0.032** 0.036*** 0.363***
## (0.016) (0.004) (0.028)
## factor(msinc13G)2. 1 - 5pct 0.050*** 0.043*** 0.311***
## (0.016) (0.004) (0.020)
## factor(msinc13G)3. 5pct - 10pct 0.068*** 0.069*** 0.247***
## (0.016) (0.004) (0.061)
## factor(msinc13G)4. More than 10pct 0.089*** 0.096*** 0.289***
## (0.016) (0.004) (0.047)
## fico -0.00003*** -0.001*** -0.004***
## (0.00000) (0.00000) (0.0002)
## ltv 0.004*** 0.004***
## (0.00001) (0.00001)
## dti 0.00002*** 0.003***
## (0.00000) (0.00001)
## fulldocumentation -0.298***
## (0.011)
## log(orig_upb) -0.206*** -0.205*** -1.229***
## (0.0004) (0.0003) (0.023)
## ------------------------------------------------------------------
##
## Observations 5,345,739 8,756,629 1,923,966
## Adjusted R2 0.907 0.914 0.482
## ==================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
##
Dep. Var: Loan-to-value
Market Share Bins
regsample <- merge(freddie,cbsa_bnk,by.x=c("bank","msa","loanyr"),by.y=c("bank","cbsa","acyr"))
# regsample[,msinccat13:=ifelse(msinc13<=0,"0",ifelse(msinc13<= 0.02 ,"Less than 2%",ifelse(msinc13<0.05,"Less than 5%","More than 5%")))]
regsample[,bank_msa:=paste(seller_name,msa)]
# regsample[,yr_msa:=paste(loanyr,msa)]
r <- list()
# r2 <- list()
r3 <- list()
r[[1]] <- felm(I(ltv/100)~msinc13+msinc46+fico|bank_msa+loanyr|0|msa,data=regsample)
# r2[[1msinc13]] <- felm(I(ltv/100)~msinc13+I(msinc13^2)+msinc46+I(msinc46^2)+fico+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
r3[[1]] <- felm(I(ltv/100)~factor(msinc13G)+factor(msinc46G)+fico+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
regsample <- merge(fannie,cbsa_bnk,by.x=c("bank","msa","loanyr"),by.y=c("bank","cbsa","acyr"))
# regsample[,msinccat13:=ifelse(msinc13<=0,"0",ifelse(msinc13<= 0.02 ,"Less than 2%",ifelse(msinc13<0.05,"Less than 5%","More than 5%")))]
regsample[,bank_msa:=paste(seller_name,msa)]
# regsample[,yr_msa:=paste(loanyr,msa)]
r[[2]] <- felm(I(ltv/100)~msinc13+msinc46+fico|bank_msa+loanyr|0|msa,data=regsample)
# r2[[2]] <- felm(I(ltv/100)~msinc13+I(msinc13^2)+msinc46+I(msinc46^2)+fico+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
r3[[2]] <- felm(I(ltv/100)~factor(msinc13G)+factor(msinc46G)+fico+dti+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
regsample <- merge(moodys,cbsa_bnk,by.x=c("bank","msa","loanyr"),by.y=c("bank","cbsa","acyr"))
# regsample[,msinccat13:=ifelse(msinc13<=0,"0",ifelse(msinc13<= 0.02 ,"Less than 2%",ifelse(msinc13<0.05,"Less than 5%","More than 5%")))]
regsample[,bank_msa:=paste(seller_name,msa)]
# regsample[,yr_msa:=paste(loanyr,msa)]
r[[3]] <- felm(I(ltv/100)~msinc13+msinc46+fico+fulldocumentation|bank_msa+loanyr|0|msa,data=regsample)
# r2[[3]] <- felm(I(ltv/100)~msinc13+I(msinc13^2)+msinc46+I(msinc46^2)+fico+fulldocumentation+log(orig_upb)|bank_msa+loanyr|0|msa,data=regsample)
r3[[3]] <- felm(I(ltv/100)~factor(msinc13G)+fico+fulldocumentation|bank_msa+loanyr|0|msa,data=regsample)
.printtable(r3,column.labels = c("Freddie Loans","Fannie Loans","Moody's Loans"),lines = list(c("Fixed Effects","MSA*Bank, Yr","MSA*Bank, Yr","MSA*Bank, Yr")))
##
## ===========================================================================
## Dependent variable:
## ----------------------------------------
## Freddie Loans Fannie Loans Moody's Loans
## (1) (2) (3)
## ---------------------------------------------------------------------------
## factor(msinc13G)1. Less than 1pct 0.015*** 0.003* -0.015***
## (0.002) (0.002) (0.003)
## factor(msinc13G)2. 1 - 5pct -0.013*** -0.007*** 0.030***
## (0.002) (0.002) (0.006)
## factor(msinc13G)3. 5pct - 10pct 0.0003 0.006 0.037***
## (0.003) (0.004) (0.014)
## factor(msinc13G)4. More than 10pct 0.020*** 0.011* 0.197***
## (0.006) (0.006) (0.005)
## factor(msinc46G)1. Less than 1pct 0.011*** -0.002
## (0.002) (0.002)
## factor(msinc46G)2. 1 - 5pct -0.014*** -0.007***
## (0.003) (0.002)
## factor(msinc46G)3. 5pct - 10pct -0.016*** -0.002
## (0.004) (0.006)
## factor(msinc46G)4. More than 10pct -0.023*** 0.034
## (0.006) (0.025)
## fico -0.00001*** -0.0003*** 0.00000
## (0.00000) (0.00002) (0.00001)
## dti -0.00001*** 0.001***
## (0.00000) (0.00004)
## log(orig_upb) 0.070*** 0.075***
## (0.004) (0.005)
## fulldocumentation 0.020***
## (0.001)
## ---------------------------------------------------------------------------
## Fixed Effects MSA*Bank, Yr MSA*Bank, Yr MSA*Bank, Yr
## Observations 8,210,798 15,685,571 3,272,885
## Adjusted R2 0.148 0.213 0.145
## ===========================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
##
Linear
.printtable(r,column.labels = c("Freddie Loans","Fannie Loans","Moody's Loans"),lines = list(c("Fixed Effects","MSA*Bank, Yr","MSA*Bank, Yr","MSA*Bank, Yr")))
##
## ==========================================================
## Dependent variable:
## ----------------------------------------
## Freddie Loans Fannie Loans Moody's Loans
## (1) (2) (3)
## ----------------------------------------------------------
## msinc13 0.007 0.114*** 0.642***
## (0.030) (0.031) (0.187)
## msinc46 -0.349*** 0.054
## (0.039) (0.047) (0.000)
## fico -0.00001*** -0.0003*** 0.00000
## (0.00000) (0.00002) (0.00001)
## fulldocumentation 0.020***
## (0.001)
## ----------------------------------------------------------
## Fixed Effects MSA*Bank, Yr MSA*Bank, Yr MSA*Bank, Yr
## Observations 8,210,798 16,053,448 3,272,885
## Adjusted R2 0.110 0.159 0.145
## ==========================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
##
Other Lenders
r <- list()
regsample <- freddie[!seller_name %in% unique(cbsa_bnk$bank)]
regsample <- merge(regsample,cbsa_share,by.x=c("msa","loanyr"),by.y=c("cbsa","acyr"))
regsample[,bank_msa:=paste(seller_name,msa)]
r[[1]] <- felm(I(ltv/100)~factor(msinc13G)+fico|bank_msa+loanyr,data=regsample)
regsample <- fannie[!seller_name %in% unique(cbsa_bnk$bank)]
regsample <- merge(regsample,cbsa_share,by.x=c("msa","loanyr"),by.y=c("cbsa","acyr"))
regsample[,bank_msa:=paste(seller_name,msa)]
r[[2]] <- felm(I(ltv/100)~factor(msinc13G)+fico|bank_msa+loanyr,data=regsample)
regsample <- moodys[!seller_name %in% unique(cbsa_bnk$bank)]
regsample <- merge(regsample,cbsa_share,by.x=c("msa","loanyr"),by.y=c("cbsa","acyr"))
regsample[,bank_msa:=paste(seller_name,msa)]
r[[3]] <- felm(I(ltv/100)~factor(msinc13G)+fico+fulldocumentation|bank_msa+loanyr|0|msa,data=regsample[seller_name != ""])
.printtable(r)
##
## ====================================================================
## Dependent variable:
## ---------------------------------
## (1) (2) (3)
## --------------------------------------------------------------------
## factor(msinc13G)1. Less than 1pct -0.011* -0.001 -0.044***
## (0.006) (0.002) (0.004)
## factor(msinc13G)2. 1 - 5pct -0.016*** -0.009*** -0.042***
## (0.006) (0.002) (0.003)
## factor(msinc13G)3. 5pct - 10pct -0.004 0.006*** -0.049***
## (0.006) (0.002) (0.003)
## factor(msinc13G)4. More than 10pct -0.001 0.007*** -0.057***
## (0.006) (0.002) (0.007)
## fico -0.00001*** -0.0003*** -0.0002***
## (0.00000) (0.00000) (0.00002)
## fulldocumentation 0.014***
## (0.001)
## --------------------------------------------------------------------
##
## Observations 5,345,739 8,927,435 1,923,773
## Adjusted R2 0.105 0.157 0.187
## ====================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
##
Parallel trends
# 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/OO_NP/",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","state"):=list(NULL)]
#
# 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.",2010)
# 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
#
#
# #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(9,"National City - PNC Bank 2007",
# c("1-0000000786"),
# c("1-0000001316","2-0000083311"),
# 2004,c("PNC BANK, N.A."),2007)
# mergers[[9]] <- temp
#
#
# temp <- list(10,"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[[10]] <- temp
#
#
#
# cbsa_bnk <- NULL
# for(i in c(1:10)) {
# # 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[,.(tot_loans=.N,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]
#
# cbsa_bnk <- rbind(cbsa_bnk,cw)
# }
#
# setorder(cbsa_bnk,cbsa,bank,-pred_share)
# cbsa_bnk <- cbsa_bnk[!duplicated(cbsa_bnk[,c("cbsa","bank")])]
#
# cbsa_share <- cbsa_bnk
# setorder(cbsa_share,cbsa,-pred_share)
# cbsa_share <- cbsa_share[!duplicated(cbsa_bnk[,c("cbsa")])]
#
# write_fst(cbsa_bnk,"C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_bnk_partrend.fst",compress = 100)
#
# write_fst(cbsa_share,"C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_share_partrend.fst",compress = 100)
cbsa_bnk_partrend <- read_fst("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_bnk_partrend.fst",as.data.table = T)
cbsa_share_partrend <- read_fst("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Projects/Bank Mergers/data/cbsa_share_partrend.fst",as.data.table = T)
Acquiring Bank
regsample <- merge(freddie,cbsa_share_partrend,by.x=c("seller_name","msa"),by.y=c("bank","cbsa"),all.x=T)
regsample[,yrsinceacq:=loanyr-acyr]
regsample[,pred_share:=pred_share+0.00001]
regsample <- regsample[!is.na(pred_share)]
regsample <- regsample[!is.na(pred_share) & yrsinceacq>= -4 & yrsinceacq<=2]
regsample[,yrsinceacq:=as.factor(yrsinceacq)]
regsample <- within(regsample, yrsinceacq <- relevel(yrsinceacq, ref = 4))
r <- list()
r[[1]] <- felm(int_rt~log(pred_share)*factor(yrsinceacq)+fico+ltv+dti+log(orig_upb)|msa+loanyr+seller_name|0|msa,data=regsample)
regsample <- merge(fannie,cbsa_share_partrend,by.x=c("seller_name","msa"),by.y=c("bank","cbsa"),all.x=T)
regsample[,yrsinceacq:=loanyr-acyr]
regsample[,pred_share:=pred_share+0.00001]
regsample <- regsample[!is.na(pred_share)]
regsample <- regsample[!is.na(pred_share) & yrsinceacq>= -4 & yrsinceacq<=2]
regsample[,yrsinceacq:=as.factor(yrsinceacq)]
regsample <- within(regsample, yrsinceacq <- relevel(yrsinceacq, ref = 4))
r[[2]] <- felm(int_rt~log(pred_share)*factor(yrsinceacq)+fico+ltv+dti+log(orig_upb)|msa+loanyr+seller_name|0|msa,data=regsample)
.coef_plot_2reg_line(r[[1]],"Freddie",r[[2]],"Fannie","log(pred_share):factor(yrsinceacq)",-1)
Other Banks
regsample <- merge(freddie,cbsa_share_partrend,by.x=c("msa"),by.y=c("cbsa"),all.x=T)
regsample[,yrsinceacq:=loanyr-acyr]
regsample[,pred_share:=pred_share+0.00001]
regsample <- regsample[!is.na(pred_share) & yrsinceacq>= -4 & yrsinceacq<=3 & !seller_name %in% unique(cbsa_bnk_partrend$bank)]
regsample[,yrsinceacq:=as.factor(yrsinceacq)]
regsample <- within(regsample, yrsinceacq <- relevel(yrsinceacq, ref = 4))
r <- list()
r[[1]] <- felm(int_rt~log(pred_share)*factor(yrsinceacq)+fico+ltv+dti+log(orig_upb)|msa+loanyr+seller_name|0|msa,data=regsample)
regsample <- merge(fannie,cbsa_share_partrend,by.x=c("msa"),by.y=c("cbsa"),all.x=T)
regsample[,yrsinceacq:=loanyr-acyr]
regsample[,pred_share:=pred_share+0.00001]
regsample <- regsample[!is.na(pred_share) & yrsinceacq>= -4 & yrsinceacq<=3 & !seller_name %in% unique(cbsa_bnk_partrend$bank)]
regsample[,yrsinceacq:=as.factor(yrsinceacq)]
regsample <- within(regsample, yrsinceacq <- relevel(yrsinceacq, ref = 4))
r[[2]] <- felm(int_rt~log(pred_share)*factor(yrsinceacq)+fico+ltv+dti+log(orig_upb)|msa+loanyr+seller_name|0|msa,data=regsample)
.coef_plot_2reg_line(r[[1]],"Freddie",r[[2]],"Fannie","log(pred_share):factor(yrsinceacq)",-1)
