# # 2000 data: NAICS Based Measure
#
# # NAICS is available in CBP data post 1997.
#
#
# # cbp employment raw data
# cbp <- fread(paste0("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/CBP/county/cbp",substr(st_yr,3,4),"co.txt"))
# cbp[,naics:=as.numeric(naics)]
# cbp <- cbp[!is.na(naics)]
#
# cbp[,imp_emp:=n1_4*2.5+n5_9*7+n10_19*15+n20_49*35+n50_99*75+n100_249*175+n250_499*375+n500_999*750+n1000_1*1250+n1000_2*2000+n1000_3*3750+n1000_4*10000]
#
# cbp[,imp_emp:=ifelse(!is.na(emp) & emp>0,emp,imp_emp)]
#
# cbp[,fips:=paste0(str_pad(fipstate,2,"left","0"),str_pad(fipscty,3,"left","0"))]
#
# cbp <- cbp[,c("fips","naics","imp_emp")]
#
# naics_sic <- fread("David Dorn/NAICS97_6_to_SIC87_4.csv")
# cbp <- merge(cbp,naics_sic,by.x="naics",by.y="naics6",allow.cartesian = T)
# cbp[,imp_emp:=imp_emp*weight]
#
# cbp[,emp_sic_n:=sum(imp_emp),by=sic4]
# cbp[,emp_fips:=sum(imp_emp),by=fips]
#
#
#
#
# us_imports <- fread("David Dorn/Imports_from_China_by_sic87dd_1991_2014.csv")
# us_imports <- us_imports[,c("sic87dd","l_import_usch_2000","l_import_usch_2007")]
# us_imports[,chg_st_ed:=l_import_usch_2007-l_import_usch_2000]
#
#
#
# emp_naics <- merge(cbp,us_imports[,c("chg_st_ed","sic87dd")],by.x="sic4",by.y="sic87dd",all.x=T)
#
# emp_naics[,chg_per_employee:=chg_st_ed/emp_sic_n]
#
# emp_naics[,w_chg_per_employee:=chg_per_employee*imp_emp/emp_fips]
#
# # d_tradeusch_pw_dr is our approximation of d_tradeusch_pw in ADH
# emp_czone <- emp_naics[,.(import_sensitivity=sum(w_chg_per_employee,na.rm=T)),by=fips]
#
#
#
#
# # Other country IV
# imports_oth <- fread("David Dorn/Imports_from_China_by_sic87dd_1991_2014.csv")
# imports_oth <- imports_oth[,c("sic87dd","l_import_otch_2000","l_import_otch_2007")]
# imports_oth[,chg_st_ed:=l_import_otch_2007-l_import_otch_2000]
#
# cbp <- fread(paste0("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/CBP/county/cbp",substr(st_yr-2,3,4),"co.txt"))
# cbp[,naics:=as.numeric(naics)]
# cbp <- cbp[!is.na(naics)]
#
# cbp[,imp_emp:=n1_4*2.5+n5_9*7+n10_19*15+n20_49*35+n50_99*75+n100_249*175+n250_499*375+n500_999*750+n1000_1*1250+n1000_2*2000+n1000_3*3750+n1000_4*10000]
#
# cbp[,imp_emp:=ifelse(!is.na(emp) & emp>0,emp,imp_emp)]
#
# cbp[,fips:=paste0(str_pad(fipstate,2,"left","0"),str_pad(fipscty,3,"left","0"))]
#
# cbp <- cbp[,c("fips","naics","imp_emp")]
#
# naics_sic <- fread("David Dorn/NAICS97_6_to_SIC87_4.csv")
# cbp <- merge(cbp,naics_sic,by.x="naics",by.y="naics6",allow.cartesian = T)
# cbp[,imp_emp:=imp_emp*weight]
#
# cbp[,emp_sic_n:=sum(imp_emp),by=sic4]
# cbp[,emp_fips:=sum(imp_emp),by=fips]
#
#
# emp_naics_oc <- merge(cbp,imports_oth[,c("chg_st_ed","sic87dd")],by.x="sic4",by.y="sic87dd",all.x=T)
#
# emp_naics_oc[,chg_per_employee:=chg_st_ed/emp_sic_n]
#
# emp_naics_oc[,w_chg_per_employee:=chg_per_employee*imp_emp/emp_fips]
#
# # tradeotch_pw_lag_dr is our approximation of tradeotch_pw_lag in ADH
# emp_naics_oc <- emp_naics_oc[,.(iv_import_sensitivity=sum(w_chg_per_employee,na.rm=T)),by=fips]
#
# measures <- merge(emp_czone,emp_naics_oc,by="fips")
#
# measures[,x_bin:=ntile(import_sensitivity,3)]
# measures[,iv_bin:=ntile(iv_import_sensitivity,100)]
#
# saveRDS(measures,"measures_county_levels_sic87dd.rds")
# pop_data <- list()
#
# pop_2000 <- get_decennial(geography = "county", variables = "P001001",year = 2000)
# pop_2000 <- pop_2000[,c("GEOID","value")]
# names(pop_2000) <- c("fips","population")
# pop_2000 <- data.table(pop_2000)
# pop_2000[,fips:=as.numeric(fips)]
# pop_2000[,year:=2000]
# pop_data[[1]] <- pop_2000
#
# #https://repository.duke.edu/catalog/f49b199b-1496-4636-91f3-36226c8e7f80
# pop_csv <- fread("county_population_2000_2010.csv")
# pop_csv <- pop_csv[year %in% 2001:2009, c("fips","tot_pop","year")]
# names(pop_csv) <- c("fips","population","year")
# pop_data[[2]] <- pop_csv
#
# i=3
# for(yr in 2010:2019){
# pop <- get_acs(geography = "county", variables = c("B01003_001"), year = yr)
# pop <- data.table(pop)
# pop <- dcast(pop,GEOID~variable,value.var = "estimate",fun.aggregate = sum)
# names(pop) <- c("fips","population")
# pop[,year:=yr]
# pop_data[[i]] <- pop
# i=i+1
# }
#
# pop_data <- do.call(rbind,pop_data)
#
# pop_data[,fips:=str_pad(as.character(fips),width = 5,side = "left",pad = "0")]
#
#
#
# #https://apps.bea.gov/regional/downloadzip.cfm
# county_data <- fread("bea_data_1969_2021.csv")
#
# county_data[,variable:=ifelse(Description=="Personal income (thousands of dollars)","total_income",
# ifelse(Description=="Population (persons) 1/","population",
# ifelse(Description=="Per capita personal income (dollars) 2/","income_per_capita","")))]
#
# county_data <- county_data[variable %in% c("income_per_capita")]
# county_data[,c("GeoName","Region","TableName","LineCode","IndustryClassification","Unit","Description"):=list(NULL)]
# setnames(county_data,"GeoFIPS","fips")
#
# county_data <- melt(county_data,id.vars = c("fips","variable"))
# county_data[,value:=as.numeric(value)]
# county_data <- dcast(county_data,fips+variable.1~variable,value.var = "value",fun.aggregate = sum)
# county_data[,variable.1:=as.numeric(as.character(variable.1))]
#
# setnames(county_data,"variable.1","year")
#
# unemp <- fread("la.data.64.County.txt")
# unemp <- unemp[substr(series_id,1,5)=="LAUCN"]
# unemp[,fips:=substr(series_id,6,10)]
# unemp <- unemp[substr(series_id,19,20)=="03"]
# unemp[,value:=as.numeric(value)]
# unemp <- unemp[,.(unemp_rate=mean(value,na.rm=T)),by=.(fips,year)]
# unemp[,c("series_id","period","footnote_codes"):=list(NULL)]
#
# county_data <- merge(county_data,unemp,by=c("fips","year"))
# county_data <- merge(county_data,pop_data,by=c("fips","year"))
#
# saveRDS(county_data,"county_data.rds")
# sod <- list()
# i=1
# for(fl in list.files(path="C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/SOD/data",full.names = T)){
# sod[[i]] <- fread(fl,select = c("YEAR","CERT","DEPSUMBR","DEPSUM","RSSDHCR","RSSDID","STCNTYBR","ASSET","UNINUMBR","SIMS_ESTABLISHED_DATE","SIMS_LATITUDE","SIMS_LONGITUDE","ADDRESBR"))
# i=i+1
# }
#
# sod <- rbindlist(sod,fill=T)
#
# sod[,DEPSUMBR:=str_remove_all(DEPSUMBR,",")]
# sod[,DEPSUM:=str_remove_all(DEPSUM,",")]
# sod[,ASSET:=str_remove_all(ASSET,",")]
#
# sod[,DEPSUMBR:= as.numeric(DEPSUMBR)]
# sod[,DEPSUM:= as.numeric(DEPSUM)]
# sod[,ASSET:= as.numeric(ASSET)]
#
# sod[,fips:=str_pad(STCNTYBR,5,"left","0")]
# sod[,SIMS_ESTABLISHED_DATE:=as.Date(SIMS_ESTABLISHED_DATE,"%m/%d/%Y")]
#
# sod[,br_id:=paste0(tolower(ADDRESBR),round(SIMS_LATITUDE,1),round(SIMS_LONGITUDE,1))]
#
#
#
#
# br <- list()
# i <- 1
# for(yr in 2001:2020){
# temp <- sod[YEAR==yr]
# temp_p <- sod[YEAR==(yr-1)]
# temp_a <- sod[YEAR==(yr+1)]
#
# # openings <- temp[year(SIMS_ESTABLISHED_DATE)==yr]
# openings <- temp[DEPSUMBR> 0 & !UNINUMBR %in% temp_p[DEPSUMBR> 0]$UNINUMBR]
# openings <- rbind(openings,temp[DEPSUMBR> 0 & !br_id %in% temp_p[DEPSUMBR> 0]$br_id])
# openings <- openings[!duplicated(openings)]
# openings <- openings[,.(openings=.N),by=fips]
#
#
# closings <- temp[DEPSUMBR> 0 & !UNINUMBR %in% temp_a[DEPSUMBR> 0]$UNINUMBR]
# closings <- rbind(closings,temp[DEPSUMBR> 0 & !br_id %in% temp_a[DEPSUMBR> 0]$br_id])
# closings <- closings[!duplicated(closings)]
# closings <- closings[,.(closings=.N),by=fips]
#
# temp<- data.table(fips=unique(sod$fips),YEAR=yr)
# temp <- merge(temp,openings,by="fips",all.x = T)
# temp <- merge(temp,closings,by="fips",all.x = T)
# temp[is.na(temp)] <- 0
#
# br[[i]] <- temp
# i=i+1
# }
#
# br <- do.call(rbind,br)
# setorder(br,fips,YEAR)
#
#
# saveRDS(sod,"sod_sum.rds")
# saveRDS(br,"br_open_close.rds")
measures <- readRDS("measures_county_levels_sic87dd.rds")
measures[,import_sensitivity:=(import_sensitivity-min(measures$import_sensitivity,na.rm=T)+1)]
measures[,iv_import_sensitivity:=(iv_import_sensitivity-min(measures$iv_import_sensitivity,na.rm=T)+1)]
measures[,high_x:=ntile(import_sensitivity,2)]
county_data <- readRDS("county_data.rds")
sod <- readRDS("sod_sum.rds")
br_open_close <- readRDS("br_open_close.rds")
bank_failures <- fread("failed_banklist.csv")
bank_failures[,failed_date:=as.Date(`Closing Date`,"%d-%b-%y")]
bank_failures <- bank_failures[failed_date>"2008-01-01"]
bank_failures[,year:=year(failed_date)]
library(DBI)
library(RSQLite)
con_call <- dbConnect(RSQLite::SQLite(), "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Call Reports/call_reports.db")
data_periods <- apply(expand.grid(c("1231"), 2001:2022), 1, paste, collapse="")
call <- list()
i=1
for(dp in data_periods) {
call_1 <- dbGetQuery(con_call,paste0("select
RCON6631+RCON6636 deposits_domestic,
RCON2170 assets_total_2,
RCON1754 htm_2,
RCON1773 afs_2,
RCON3545 trading_assets_2,
RCON3210 equity_total_2,
RCON2122 loans_total_2,
IDRSSD
from call_1_",dp," "))
call_2 <- dbGetQuery(con_call,paste0("select
RIAD4074 net_interest_income,
RIAD4230 provision_for_loans,
RIAD4107 total_interest_income,
RIAD4340 net_income,
RCFD2170 assets_total_1,
RCFD1754 htm_1,
RCFD1773 afs_1,
RCFD3545 trading_assets_1,
RCFD3210 equity_total_1,
RCFD2122 loans_total_1,
FDIC_Certificate_Number,
IDRSSD
from call_2_",dp," "))
calls <- merge(call_1,call_2,by="IDRSSD")
calls <- data.table(calls)
calls[,date:=dp]
calls[,assets_total:= ifelse(!is.na(assets_total_2),assets_total_2,assets_total_1)]
calls[,htm:= ifelse(!is.na(htm_2),htm_2,htm_1)]
calls[,afs:= ifelse(!is.na(afs_2),afs_2,afs_1)]
calls[,trading_assets:= ifelse(!is.na(trading_assets_2),trading_assets_2,trading_assets_1)]
calls[,equity_total:= ifelse(!is.na(equity_total_2),equity_total_2,equity_total_1)]
calls[,loans_total:= ifelse(!is.na(loans_total_2),loans_total_2,loans_total_2)]
calls[,c("assets_total_2","htm_2","afs_2","trading_assets_2","equity_total_2","loans_total_2",
"assets_total_1","htm_1","afs_1","trading_assets_1","equity_total_1","loans_total_1"):=list(NULL)]
call[[i]] <- calls
i=i+1
}
call <- rbindlist(call,fill=T)
call[,nii:=net_interest_income/total_interest_income]
call[,roe:=net_income/equity_total]
call[,equity_assets:=equity_total/assets_total]
call[,roa:=net_income/assets_total]
call[,deposits_assets:=deposits_domestic/assets_total]
call[,provisions_assets:=provision_for_loans/assets_total]
call[,provisions_loans:=provision_for_loans/loans_total]
call[,securities_assets:=(trading_assets+htm+afs)/assets_total]
call[,loans_assets:=loans_total/assets_total]
call[,year:=year(as.Date(date,"%m%d%Y"))]
call[,max_year:=max(year),by=FDIC_Certificate_Number]
# call[,disc:=ifelse(max_year<2022 & year==max_year & FDIC_Certificate_Number %in% bank_failures$Cert,1,ifelse(max_year<2022 & year>max_year & FDIC_Certificate_Number %in% bank_failures$Cert,NA,0))]
bank_import_exposure <- sod[YEAR==2007]
branches_count <- sod[,.(no_of_branches=.N),by=.(CERT,YEAR)]
bank_import_exposure <- bank_import_exposure[,.(county_deposits=sum(DEPSUMBR,na.rm=T)),by=.(CERT,fips)]
bank_import_exposure <- merge( bank_import_exposure,measures,by="fips")
bank_import_exposure[,total_deposits:=sum(county_deposits,na.rm=T),by=CERT]
bank_import_exposure[,bin_w_deposit:=county_deposits*x_bin/total_deposits]
bank_import_exposure[,cont_w_deposit:=county_deposits*import_sensitivity/total_deposits]
bank_import_exposure <- bank_import_exposure[,.(bin_w_deposit=sum(bin_w_deposit,na.rm=T),cont_w_deposit=sum(cont_w_deposit,na.rm=T)),by=CERT]
# bank_import_exposure <- bank_import_exposure[bin_w_deposit>quantile(bin_w_deposit,0.001) & bin_w_deposit <quantile(bin_w_deposit,0.999)& cont_w_deposit>quantile(cont_w_deposit,0.001) & cont_w_deposit <quantile(cont_w_deposit,0.999)]
#
# bank_import_exposure[,bin_w_deposit_bin:=ntile(bin_w_deposit,10)]
# bank_import_exposure[,cont_w_deposit_bin:=ntile(cont_w_deposit,10)]
# ggplot(bank_import_exposure,aes(x=cont_w_deposit))+geom_density()
reg_sample <- merge(call,bank_import_exposure,by.x="FDIC_Certificate_Number",by.y="CERT")
reg_sample <- merge(reg_sample,branches_count,by.x=c("FDIC_Certificate_Number","year"),by.y=c("CERT","YEAR"),all.x=T)
Dependent Variable:
log(assets)
Regression \[ log(assets) = log(import\text{
}sensitivity_{bank})*Year+YearFE\]
r <- list()
r[[1]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)|FDIC_Certificate_Number+year|0|FDIC_Certificate_Number,data=reg_sample)
r[[2]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total<1e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
r[[3]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>1e6 & assets_total<10e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
r[[4]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>10e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
# coef_plot_1reg(r[[1]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets)")
coef_plot_1reg(r[[2]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets) - small banks (<1b)")
coef_plot_1reg(r[[3]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets) - med banks (1 - 10 b)")
coef_plot_1reg(r[[4]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets) - large banks (> 10b)")
Regression \[ log(assets) = log(import\text{
}sensitivity_{bank})*Year+YearFE + BankFE\]
r <- list()
r[[1]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|FDIC_Certificate_Number+year|0|FDIC_Certificate_Number,data=reg_sample)
r[[2]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year+FDIC_Certificate_Number|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total<1e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
r[[3]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year+FDIC_Certificate_Number|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>1e6 & assets_total<10e6 & year==2007]$FDIC_Certificate_Number])
r[[4]] <- felm(log(assets_total)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year+FDIC_Certificate_Number|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>10e6 & year==2007]$FDIC_Certificate_Number])
# coef_plot_1reg(r[[1]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets)")
coef_plot_1reg(r[[2]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets) - small banks (<1b)")
coef_plot_1reg(r[[3]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets) - med banks (1 - 10 b)")
coef_plot_1reg(r[[4]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets) - large banks (> 10b)")
Dependent Variable:
log(no branches)
Regression \[ log(no\text{ }branches) = log(import\text{
}sensitivity_{bank})*Year+YearFE\]
r <- list()
r[[1]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)|FDIC_Certificate_Number+year|0|FDIC_Certificate_Number,data=reg_sample)
r[[2]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total<1e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
r[[3]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>1e6 & assets_total<10e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
r[[4]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)+deposits_assets+roe+equity_assets+loans_assets|year|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>10e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
# coef_plot_1reg(r[[1]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets)")
coef_plot_1reg(r[[2]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(branches) - small banks (<1b)")
coef_plot_1reg(r[[3]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(branches) - med banks (1 - 10 b)")
coef_plot_1reg(r[[4]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(branches) - large banks (> 10b)")
Regression \[ log(no\text{ }branches) = log(import\text{
}sensitivity_{bank})*Year+YearFE+BankFE\]
r <- list()
r[[1]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)|FDIC_Certificate_Number+year|0|FDIC_Certificate_Number,data=reg_sample)
r[[2]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)|year+FDIC_Certificate_Number|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total<1e6 & year==2007]$FDIC_Certificate_Number & cont_w_deposit<50])
r[[3]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)|year+FDIC_Certificate_Number|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>1e6 & assets_total<10e6 & year==2007]$FDIC_Certificate_Number])
r[[4]] <- felm(log(1+no_of_branches)~log(1 + cont_w_deposit)*factor(year)|year+FDIC_Certificate_Number|0|FDIC_Certificate_Number,data=reg_sample[FDIC_Certificate_Number %in% reg_sample[assets_total>10e6 & year==2007]$FDIC_Certificate_Number])
# coef_plot_1reg(r[[1]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(assets)")
coef_plot_1reg(r[[2]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(branches) - small banks (<1b)")
coef_plot_1reg(r[[3]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(branches) - med banks (1 - 10 b)")
coef_plot_1reg(r[[4]],"log(1 + cont_w_deposit):factor(year)",2001)+ggtitle("log(branches) - large banks (> 10b)")
