rm(list=ls())
library(data.table)
library(fst)
library(RSQLite)
library(DBI)
library(dplyr)
library(tidyr)
library(lfe)
library(stargazer)
library(stringr)
library(ggplot2)
library(readxl)
library(reshape2)
library(stringi)
library(zoo)
source('C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/functions.R')
fit_felm <- function(formula, data, sample_frac,drop_dv_outliers=TRUE) {
dep_var_name <- as.character(formula[[2]])
if(drop_dv_outliers==TRUE) {
quantiles <- quantile(data[[dep_var_name]], c(0.01, 0.99), na.rm = TRUE)
subset_data <- data[data[[dep_var_name]] > quantiles[1] & data[[dep_var_name]] < quantiles[2], ]
} else {
subset_data = data
}
subset_data <- subset_data[sample(nrow(subset_data), size = nrow(subset_data)*sample_frac), ]
felm(formula, data = subset_data)
}
hmda_link_entity <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/HMDA lender/hmdapan2017.csv",select=c("code","hmprid",paste0("RSSD",12:17)))#,paste0("ENTITY",15:16)
hmda_link_entity[,hmda_id:=paste0(code,"-",hmprid)]
hmda_link_entity[,c("code","hmprid"):=list(NULL)]
hmda_link_entity <- melt(hmda_link_entity,id.vars = c("hmda_id"))
hmda_link_entity <- data.table(hmda_link_entity)
hmda_link_entity <- hmda_link_entity[!is.na(value)]
hmda_link_entity[,year:=as.character(variable)]
hmda_link_entity[,year:=substr(year,5,6)]
setnames(hmda_link_entity,"value","RSSD")
hmda_link_entity[,variable:=NULL]
hmda_link_entity[,year:=as.numeric(year)]
hmda_link_entity[,year:=ifelse(year>85,1900+year,2000+year)]
hmda_link_2021 <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/HMDA/HMDA lender/hmdapan2021.csv",select=c("LEI","RSSD21","CODE21","HMPRID"))
hmda_link_2021[,hmda_id:=paste0(CODE21,"-",HMPRID)]
hmda_link_2021 <- hmda_link_2021[,c("LEI","RSSD21","hmda_id")]
gse_limit_files <- list.files("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/GSE Limits",full.names = T,pattern = ".xls")
gse_limits <- list()
i=1
for(fl in gse_limit_files) {
yr <- substr(fl,117,120)
if(yr<=2014) {
temp <- read_xls(fl,sheet = 1,skip=2,col_names = F)
} else{
temp <- read_xlsx(fl,sheet = 1,skip=2,col_names = F)
}
names(temp) <- c("statefips","countyfips","countyname","cbsa","statecode","gse_limit","gse_limit_2","gse_limit_3","gse_limit_4")
temp <- data.table(temp)
temp[,year:=yr]
gse_limits[[i]] <- temp
i=i+1
}
gse_limits <- rbindlist(gse_limits)
gse_limits[,gse_limit:=floor(gse_limit/1000)]
gse_limits[,year:=as.numeric(year)]
gse_limits[,county:=paste0(statefips,countyfips)]
gse_limits <- gse_limits[,c("county","year","gse_limit")]
setorder(gse_limits,county,year)
gse_limits[,gse_limit_1:=lag(gse_limit),by=county]
gse_limits[,gse_limit_2:=lead(gse_limit),by=county]
gse_limits[,limit_change:=gse_limit-gse_limit_1]
zrd <- fread("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Zillow Research Data/County_zhvi_uc_sfrcondo_tier_0.67_1.0_sm_sa_month.csv")
zrd[,fips:=paste0(str_pad(StateCodeFIPS,2,pad="0"),str_pad(MunicipalCodeFIPS,3,pad="0"))]
zrd[,c("RegionID","RegionName","SizeRank","StateName","RegionType","State","Metro","StateCodeFIPS","MunicipalCodeFIPS")] <- NULL
zrd <- melt(zrd,id.vars = c("fips"))
zrd$month <- as.Date(as.character(zrd$variable),origin = "1970-01-01")
zrd$variable <- NULL
names(zrd) <- c("county","zhvi","month")
zrd <- data.table(zrd)
zrd[,asofdate:=year(month)]
zrd <- zrd[,.(zhvi=mean(zhvi,na.rm=T)),by=.(county,asofdate)]
zrd <- merge(zrd,gse_limits,by.x=c("county","asofdate"),by.y=c("county","year"))
zrd[,zhvi:=zhvi/1000]
zrd[,above_conforming_limit:=ifelse(zhvi>gse_limit,1,0)]
above_conforming_limit <- zrd[,.(above_conforming_limit=sum(above_conforming_limit,na.rm=T)),by=asofdate]
gse_limits_yr <- gse_limits[,.(
gse_limit=median(gse_limit,na.rm=T),
limit_change=median(limit_change,na.rm=T)),
by=year]
gse_limits_yr <- merge(gse_limits_yr,above_conforming_limit,by.x="year",by.y="asofdate")
setnames(gse_limits_yr,"gse_limit","gse_limit_year")
data <- gse_limits[year %in% c(2016:2023),.(.N),by=.(gse_limit,year)]
years <- unique(data$year)
# Initialize an empty list to store the data.tables
data_tables_list <- list()
# Loop through each year, filter the data, and store in the list
for (yr in years) {
# Filter data for the current year
dt_year <- data[year == yr, .(gse_limit, N)]
# Add an 'id' column that is simply the row number
# Rearrange columns to have 'id' as the first column
setorder(dt_year,gse_limit)
other <- dt_year[N==1]
dt_year <- dt_year[N>1]
new_row <- setNames(as.list(rep(NA, ncol(dt_year))), names(dt_year))
new_row[["gse_limit"]] <- -1
new_row[["N"]] <- nrow(other)
# Convert the list to a data.table and append
dt_year <- rbind(dt_year, as.data.table((new_row)))
dt_year[, id := .I]
setnames(dt_year, "gse_limit", paste0("gse_limit_", yr))
setnames(dt_year, "N", paste0("N_", yr))
# Add the data.table to the list, with the name being the year
data_tables_list[[as.character(yr)]] <- dt_year
}
merged_data <- Reduce(function(x, y) merge(x, y, by = "id", all = TRUE), data_tables_list)
print(merged_data)
## id gse_limit_2016 N_2016 gse_limit_2017 N_2017 gse_limit_2018 N_2018
## 1: 1 417 3000 424 2996 453 3014
## 2: 2 426 6 426 6 458 17
## 3: 3 437 20 433 3 483 2
## 4: 4 458 27 437 6 494 14
## 5: 5 474 5 458 17 517 11
## 6: 6 483 2 466 14 529 13
## 7: 7 517 7 483 2 535 20
## 8: 8 523 7 488 4 600 2
## 9: 9 529 4 493 10 603 7
## 10: 10 535 20 517 7 615 2
## 11: 11 540 3 529 4 625 11
## 12: 12 600 3 535 20 667 3
## 13: 13 625 112 592 3 679 103
## 14: 14 657 2 598 7 -1 15
## 15: 15 -1 16 600 2 NA NA
## 16: 16 NA NA 625 11 NA NA
## 17: 17 NA NA 636 103 NA NA
## 18: 18 NA NA 657 2 NA NA
## 19: 19 NA NA -1 17 NA NA
## gse_limit_2019 N_2019 gse_limit_2020 N_2020 gse_limit_2021 N_2021
## 1: 484 3035 510 3030 548 3062
## 2: 517 7 520 7 586 13
## 3: 529 3 529 2 596 10
## 4: 534 14 535 20 598 4
## 5: 535 20 563 13 600 2
## 6: 552 4 569 4 625 7
## 7: 561 10 575 10 646 4
## 8: 600 2 600 2 724 7
## 9: 625 11 625 10 726 2
## 10: 688 7 646 4 739 2
## 11: 718 2 690 8 765 2
## 12: 726 106 726 2 776 3
## 13: -1 13 741 3 817 2
## 14: NA NA 762 2 822 102
## 15: NA NA 765 102 -1 11
## 16: NA NA -1 14 NA NA
## 17: NA NA NA NA NA NA
## 18: NA NA NA NA NA NA
## 19: NA NA NA NA NA NA
## gse_limit_2022 N_2022 gse_limit_2023 N_2023
## 1: 647 3074 726 3073
## 2: 648 2 740 2
## 3: 675 4 744 4
## 4: 684 10 763 4
## 5: 694 13 787 10
## 6: 726 2 828 7
## 7: 770 7 890 13
## 8: 856 2 948 3
## 9: 891 3 977 4
## 10: 970 102 1089 103
## 11: -1 14 -1 11
## 12: NA NA NA NA
## 13: NA NA NA NA
## 14: NA NA NA NA
## 15: NA NA NA NA
## 16: NA NA NA NA
## 17: NA NA NA NA
## 18: NA NA NA NA
## 19: NA NA NA NA
stargazer(merged_data,summary = F,type="text")
##
## =====================================================================================================================================================================================
## id gse_limit_2016 N_2016 gse_limit_2017 N_2017 gse_limit_2018 N_2018 gse_limit_2019 N_2019 gse_limit_2020 N_2020 gse_limit_2021 N_2021 gse_limit_2022 N_2022 gse_limit_2023 N_2023
## -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
## 1 1 417 3,000 424 2,996 453 3,014 484 3,035 510 3,030 548 3,062 647 3,074 726 3,073
## 2 2 426 6 426 6 458 17 517 7 520 7 586 13 648 2 740 2
## 3 3 437 20 433 3 483 2 529 3 529 2 596 10 675 4 744 4
## 4 4 458 27 437 6 494 14 534 14 535 20 598 4 684 10 763 4
## 5 5 474 5 458 17 517 11 535 20 563 13 600 2 694 13 787 10
## 6 6 483 2 466 14 529 13 552 4 569 4 625 7 726 2 828 7
## 7 7 517 7 483 2 535 20 561 10 575 10 646 4 770 7 890 13
## 8 8 523 7 488 4 600 2 600 2 600 2 724 7 856 2 948 3
## 9 9 529 4 493 10 603 7 625 11 625 10 726 2 891 3 977 4
## 10 10 535 20 517 7 615 2 688 7 646 4 739 2 970 102 1,089 103
## 11 11 540 3 529 4 625 11 718 2 690 8 765 2 -1 14 -1 11
## 12 12 600 3 535 20 667 3 726 106 726 2 776 3
## 13 13 625 112 592 3 679 103 -1 13 741 3 817 2
## 14 14 657 2 598 7 -1 15 762 2 822 102
## 15 15 -1 16 600 2 765 102 -1 11
## 16 16 625 11 -1 14
## 17 17 636 103
## 18 18 657 2
## 19 19 -1 17
## -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ggplot(gse_limits_yr, aes(x=year)) +
geom_col(aes(y=gse_limit_year),fill="dodgerblue",alpha=0.5) +
scale_x_continuous(breaks = unique(gse_limits_yr$year))+
labs(x="",y="GSE limit ($'000)")+
theme_minimal()
ggplot(gse_limits_yr, aes(x=year)) +
geom_col(aes(y=above_conforming_limit),fill="dodgerblue",alpha=0.5) +
scale_x_continuous(breaks = unique(gse_limits_yr$year))+
labs(x="",y="Number of counties")+
theme_minimal()
ggplot(gse_limits_yr, aes(x=year, y=limit_change)) +
geom_col(filll="dodgerblue",size=2) +
# geom_point(size=3,color="dodgerblue4")+
scale_x_continuous(breaks = unique(gse_limits_yr$year))+
labs(x="",y="GSE limit Change ($'000)")+
theme_minimal()
hmda_con <- dbConnect(RSQLite::SQLite(), "C:/Users/dratnadiwakara2/Downloads/HMDA/hmda.db")
yrs <- as.character(2012:2017)
hmda <- list()
i=1
for(yr in yrs) {
print(yr)
hmda[[i]] <- data.table(dbGetQuery(hmda_con,
paste0("select
agencycode,
respondentid,
asofdate,
purposeofloan,
typeofpurchaser,
amountofloan,
censustract,
applicantincome,
typeofloan,
actiontaken,
denialreason1,
denialreason2,
denialreason3
from lar_",yr,"
where
actiontaken in (1,3,6) and
propertytype==1")))
i=i+1
}
## [1] "2012"
## [1] "2013"
## [1] "2014"
## [1] "2015"
## [1] "2016"
## [1] "2017"
hmda <- rbindlist(hmda)
hmda <- data.table(hmda)
hmda[,county:=substr(censustract,1,5)]
hmda <- merge(hmda,gse_limits,by.x=c("asofdate","county"),by.y=c("year","county"),all.x=T)
hmda <- merge(hmda,gse_limits_yr,by.x="asofdate",by.y="year")
hmda[,gse_limit:=ifelse(is.na(gse_limit),gse_limit_year,gse_limit)]
hmda[,amountofloan:=as.numeric(amountofloan)]
hmda[,dist_gse_limit:=amountofloan-gse_limit]
hmda[,jumbo_this_year:=ifelse(amountofloan>gse_limit,1,0)]
hmda[,jumbo_last_year:=ifelse(amountofloan>gse_limit_1,1,0)]
hmda[,jumbo_next_year:=ifelse(amountofloan>gse_limit_2,1,0)]
hmda[,hmda_id:=paste0(agencycode,"-",respondentid)]
hmda <- merge(hmda,hmda_link_entity,by.x=c("asofdate","hmda_id"),by.y=c("year","hmda_id"),all.x=T)
# hmda <- hmda[RSSD>0 & !is.na(RSSD)]
gc()
## used (Mb) gc trigger (Mb) max used (Mb)
## Ncells 2725020 145.6 4749042 253.7 4749042 253.7
## Vcells 1935613623 14767.6 4822978522 36796.5 4014010836 30624.5
yrs <- 2018:2022
hmda_post <- list()
i=1
for(yr in yrs) {
print(yr)
hmda_post[[i]] <- data.table(dbGetQuery(hmda_con,
paste0("select
lei,
asofdate,
purposeofloan,
typeofpurchaser,
amountofloan,
censustract,
actiontaken,
conforming_loan_limit,
combined_loan_to_value_ratio,
interest_rate,
rate_spread,
total_loan_costs,
debt_to_income_ratio,
typeofloan,
applicant_age,
applicantincome,
denialreason1,
denialreason2,
denialreason3
from lar_",yr,"
where
actiontaken in (1,3,6) and
derived_dwelling_category='Single Family (1-4 Units):Site-Built'")))
i=i+1
}
## [1] 2018
## [1] 2019
## [1] 2020
## [1] 2021
## [1] 2022
hmda_post <- rbindlist(hmda_post)
hmda_post <- data.table(hmda_post)
hmda_post[,county:=substr(censustract,1,5)]
hmda_post <- merge(hmda_post,gse_limits,by.x=c("asofdate","county"),by.y=c("year","county"),all.x=T)
hmda_post <- merge(hmda_post,gse_limits_yr,by.x="asofdate",by.y="year")
hmda_post[,gse_limit:=ifelse(is.na(gse_limit),gse_limit_year,gse_limit)]
hmda_post[,amountofloan:=as.numeric(amountofloan)]
hmda_post[,dist_gse_limit:=amountofloan-gse_limit]
hmda_post[,jumbo_this_year:=ifelse(amountofloan>gse_limit,1,0)]
hmda_post[,jumbo_last_year:=ifelse(amountofloan>gse_limit_1,1,0)]
hmda_post[,jumbo_next_year:=ifelse(amountofloan>gse_limit_2,1,0)]
hmda_post <- merge(hmda_post,hmda_link_2021,by.x=c("lei"),by.y=c("LEI"),all.x=T)
# hmda_post <- hmda_post[RSSD21>0 & !is.na(RSSD21)]
hmda_post[,purposeofloan:=ifelse(purposeofloan>10,3,purposeofloan)]
setnames(hmda_post,"RSSD21","RSSD")
gc()
## used (Mb) gc trigger (Mb) max used (Mb)
## Ncells 2969956 158.7 4749042 253.7 4749042 253.7
## Vcells 4132029407 31524.9 6945265071 52988.2 6944420612 52981.8
# hmda_1_summary <- hmda[actiontaken==1,.(no_loans=.N,total_loan_amt=sum(amountofloan)),
# by=.(RSSD,asofdate)]
#
# hmda_2_summary <- hmda_post[actiontaken==1,.(no_loans=.N,total_loan_amt=sum(amountofloan)),
# by=.(RSSD,asofdate)]
jumbo_100k_by_lender <- hmda[ actiontaken==1 & typeofloan==1 &
dist_gse_limit>10 & typeofpurchaser==0 &
asofdate %in% 2012:2016,
.(no_of_jumbo_loans=.N,
total_jumbo_loan_amt=sum(amountofloan)),
by=.(asofdate,RSSD)]
# all_by_lender <- hmda[asofdate %in% 2012:2016 & actiontaken==1 & typeofloan==1 ,
# .(all_no_of_loans=.N,
# all_total_loan_amt=sum(amountofloan)),
# by=RSSD]
# jumbo_100k_by_lender <- merge(jumbo_100k_by_lender,all_by_lender,by="RSSD")
# jumbo_100k_by_lender[,no_of_jumbo_loans_pct:=no_of_jumbo_loans*100/all_no_of_loans]
# jumbo_100k_by_lender[,total_jumbo_loan_amt_pct:=total_jumbo_loan_amt*100/all_total_loan_amt]
# HHI <- hmda[ actiontaken==1 & typeofloan==1 &
# asofdate %in% 2012:2016,.(amountofloan=sum(amountofloan,na.rm=T)),
# by=.(RSSD,county)]
#
# HHI[, total_loan_by_rssd_asofdate := sum(amountofloan), by = .(RSSD)]
# HHI[, market_share := amountofloan / total_loan_by_rssd_asofdate]
# HHI[, squared_market_share := market_share^2, by = .(RSSD, county)]
#
#
# HHI <- HHI[market_share>0, .(HHI = sum(squared_market_share),.N), by = .(RSSD)]
#
# jumbo_100k_by_lender <- merge(jumbo_100k_by_lender,HHI,by="RSSD",all.x=T)
con_call <- dbConnect(RSQLite::SQLite(), "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Call Reports/call_reports.db")
con_ubpr <- dbConnect(RSQLite::SQLite(), "C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Call Reports/ubpr.db")
data_periods <- apply(expand.grid(c("0331","0630","0930","1231"), 2012:2023), 1, paste, collapse="")
# data_periods <- c(data_periods,"03312023")
# data_periods <- apply(expand.grid(c("1231"), 2022), 1, paste, collapse="")
upbr <- list()
i=1
for (dp in data_periods) {
ubpr_dp <- as.Date(dp,"%m%d%Y")
ubpr_yr <- year(ubpr_dp)
ubpr_data <- dbGetQuery(con_ubpr,paste0("select
UBPRD387 net_income_current_q,
UBPR3368 assets_quarterly_avg,
UBPRK434 core_deposits,
UBPRE566 htm_assets_ubpr,
UBPRE630 roe,
UBPRKX40 nii_assets,
UBPRE002 interest_expense_assets_1,
UBPRE678 interest_income_assets,
UBPRE023 nii_earnings_assets,
UBPRE565 afs_assets_upbr,
UBPR7316 asset_growth_yearly,
UBPRE153 asset_growth_quarterly,
UBPR2122 total_loans,
UBPRE141 loan_growth_quarterly,
UBPR1766 commercial_industrial_loans,
UBPRD214 re_loans_sfr,
UBPR1410-UBPRD214 re_loans_other,
UBPRD175 individual_loans,
UBPR2170 total_assets,
UBPR2746 cre_construction_land_dev_loans,
UBPRE006 provisions_to_assets,
UBPRE397 re_loans_net_loss,
UBPR3210 total_equity,
UBPR3792 tier_1_capital,
UBPRD488 total_capital_ratio,
UBPR0081+UBPR0071 cash,
UBPRD588 total_securities,
UBPR2200 total_deposits,
UBPRE679 interest_expense_assets_2,
UBPR7400 personnel_expense_to_assets,
UBPRE090 assets_per_employee,
UBPRD667+UBPRD669 pastdue_nonaccrual_loans,
UBPRE485+UBPRE486 mtg_delinq_pct,
UBPRE487+UBPRE488 mtg_delinq_pct_2,
UBPR2365 brokered_deposits,
UBPRE390 gross_loss_pct_of_avg_loans,
UBPRE544 gross_loans_30_89_past_due_pct,
UBPR7414 gross_loans_90_past_due_pct,
UBPRE595 brokers_deposits_pct_of_total_deposits,
UBPRE559 lns_securities_over_15_pct_of_assets,
UBPR3200 subordinated_debt,
IDRSSD
from ubpr_",ubpr_yr,"
where
data_period=",as.numeric(ubpr_dp),""))
call_1 <- dbGetQuery(con_call,paste0("select
RCON5597 uninsured_deposits,
RCON6631 noninterest_deposits,
RCON2200 deposits_domestic,
RCONF049 deposits_domestic_insured,
RCONF051 deposits_domestic_uninsured,
RCON6631 deposits_domestic_non_interest,
RCON2210 demand_deposits,
RCONF160+RCONF161 cre_mortgages,
RCONF159 cre_construction,
RCONF165 cre_commitments,
IDRSSD
from call_1_",dp," "))
call_2 <- dbGetQuery(con_call,paste0("select
FDIC_Certificate_Number,
Financial_Institution_Name,
Financial_Institution_State,
data_period,
RIADC017 data_processing_expenses,
RIAD4135 salaries_employee_benefits,
RIAD4150 no_of_full_time_employees,
IDRSSD
from call_2_",dp," "))
ubpr_data <- merge(ubpr_data,call_2,by="IDRSSD")
ubpr_data <- merge(ubpr_data,call_1,by="IDRSSD")
ubpr_data <- data.table(ubpr_data)
upbr[[i]] <- ubpr_data
i=i+1
}
upbr <- rbindlist(upbr,fill=T)
dbDisconnect(con_call)
dbDisconnect(con_ubpr)
summary_stats <- function(x) {
return(c(
p25 = quantile(x, 0.25,na.rm=T),
median = median(x,na.rm=T),
mean = mean(x,na.rm=T),
p75 = quantile(x, 0.75,na.rm=T)
))
}
Assets
upbr[,total_loans_assets:=total_loans*100/total_assets]
upbr[,re_loans_sfr_assets:=re_loans_sfr*100/total_assets]
upbr[,individual_loans_assets:=individual_loans*100/total_assets]
upbr[,ci_assets:=commercial_industrial_loans*100/total_assets]
upbr[,cre_assets:=(cre_construction+cre_mortgages)*100/total_assets]
upbr[,re_loans_other_assets:=(re_loans_other-cre_construction-cre_mortgages)*100/total_assets]
upbr[,total_securities_assets:=total_securities*100/total_assets]
upbr[,cash_assets:=cash*100/total_assets]
selected_vars <- c("total_loans_assets","total_securities_assets","cash_assets",
"re_loans_sfr_assets","re_loans_other_assets",
"individual_loans_assets","ci_assets","cre_assets",
"lns_securities_over_15_pct_of_assets")
summary_table <- upbr[data_period=="12312016", lapply(.SD, summary_stats), .SDcols = selected_vars]
summary_table <- t(summary_table)
colnames(summary_table) <- c("p25", "Median", "Mean", "p75")
print(summary_table)
## p25 Median Mean p75
## total_loans_assets 54.2037033 67.470314 63.788662 77.073627
## total_securities_assets 8.9196590 17.884995 20.965163 29.547528
## cash_assets 3.4662511 6.568186 9.617156 12.133087
## re_loans_sfr_assets 8.8814929 16.498242 19.630517 26.287325
## re_loans_other_assets 3.5092542 6.920259 8.877155 12.324487
## individual_loans_assets 0.6565521 1.795101 3.361293 3.771453
## ci_assets 3.5379536 6.541537 8.023301 10.685372
## cre_assets 6.9759628 16.180601 18.248613 27.173007
## lns_securities_over_15_pct_of_assets 1.7100000 5.595000 9.643334 13.447500
Liabilities
upbr[,deposits_assets:=total_deposits*100/total_assets]
upbr[,core_deposits_assets:=core_deposits*100/total_assets]
upbr[,noncore_deposits_assets:=deposits_assets-core_deposits_assets]
upbr[,brokered_deposits_assets:=brokered_deposits*100/total_assets]
upbr[,subordinated_debt_assets:=subordinated_debt*100/total_assets]
selected_vars <- c("deposits_assets","core_deposits_assets","noncore_deposits_assets","brokered_deposits_assets","subordinated_debt_assets")
summary_table <- upbr[data_period=="12312016", lapply(.SD, summary_stats), .SDcols = selected_vars]
summary_table <- t(summary_table)
colnames(summary_table) <- c("p25", "Median", "Mean", "p75")
print(summary_table)
## p25 Median Mean p75
## deposits_assets 80.432638 85.170426 82.53593935 88.309289
## core_deposits_assets 71.883036 78.849222 75.80722415 83.927673
## noncore_deposits_assets 2.111248 4.601868 6.72871520 8.796528
## brokered_deposits_assets 0.000000 0.000000 2.67725274 2.768671
## subordinated_debt_assets 0.000000 0.000000 0.01720113 0.000000
Capital
upbr[,equity_assets:=total_equity*100/total_assets]
upbr[,tier_1_assets:=tier_1_capital*100/total_assets]
selected_vars <- c("equity_assets","tier_1_assets","total_capital_ratio")
summary_table <- upbr[data_period=="12312016", lapply(.SD, summary_stats), .SDcols = selected_vars]
summary_table <- t(summary_table)
colnames(summary_table) <- c("p25", "Median", "Mean", "p75")
print(summary_table)
## p25 Median Mean p75
## equity_assets 9.348856 10.69248 12.50207 12.62727
## tier_1_assets 9.934104 11.20698 12.99992 13.14708
## total_capital_ratio 13.491425 16.18925 29.29905 20.74730
Asset Quality
selected_vars <- c("gross_loans_30_89_past_due_pct","gross_loans_90_past_due_pct","provisions_to_assets","mtg_delinq_pct_2")
summary_table <- upbr[data_period=="12312016", lapply(.SD, summary_stats), .SDcols = selected_vars]
summary_table <- t(summary_table)
colnames(summary_table) <- c("p25", "Median", "Mean", "p75")
print(summary_table)
## p25 Median Mean p75
## gross_loans_30_89_past_due_pct 0.16 0.46 0.8638441 1.12
## gross_loans_90_past_due_pct 0.21 0.64 1.1190449 1.39
## provisions_to_assets 0.00 0.07 0.1399782 0.17
## mtg_delinq_pct_2 0.51 1.53 2.5327390 3.20
Profitability
upbr[,roa:=net_income_current_q*400/assets_quarterly_avg]
selected_vars <- c("roa","roe","nii_assets","interest_expense_assets_1","interest_income_assets")
summary_table <- upbr[data_period=="12312016", lapply(.SD, summary_stats), .SDcols = selected_vars]
summary_table <- t(summary_table)
colnames(summary_table) <- c("p25", "Median", "Mean", "p75")
print(summary_table)
## p25 Median Mean p75
## roa 0.4994433 0.8474808 1.049686 1.213127
## roe 4.9600000 7.9500000 8.275409 11.290000
## nii_assets 2.9700000 3.3500000 3.379330 3.750000
## interest_expense_assets_1 0.2400000 0.3700000 0.408121 0.530000
## interest_income_assets 3.6700000 4.1200000 4.154072 4.560000
Scale
selected_vars <- c("asset_growth_quarterly","asset_growth_yearly","no_of_full_time_employees","assets_per_employee")
summary_table <- upbr[data_period=="12312016", lapply(.SD, summary_stats), .SDcols = selected_vars]
summary_table <- t(summary_table)
colnames(summary_table) <- c("p25", "Median", "Mean", "p75")
print(summary_table)
## p25 Median Mean p75
## asset_growth_quarterly -0.13 1.21 1.775089 2.79
## asset_growth_yearly -0.17 3.50 5.854573 8.11
## no_of_full_time_employees 21.00 44.00 344.898424 99.00
## assets_per_employee 3.71 4.59 6.106662 6.00
upbr_2016 <- upbr[data_period %in% c("12312012","12312013","12312014","12312015","12312016"),
c("IDRSSD","data_period","total_assets")]
upbr_2016[,asofdate:=as.numeric(substr(data_period,5,8))]
upbr_2016[,c("data_period"):=list(NULL)]
upbr_2016 <- merge(upbr_2016,jumbo_100k_by_lender,
by.x=c("IDRSSD","asofdate"),
by.y=c("RSSD","asofdate"))
# upbr_2016[,re_loan_frac_assets:=sfr_first_lein_ma*100/total_assets]
upbr_2016[,jumbo_frac_assets:=total_jumbo_loan_amt*100/(total_assets)]
upbr_2016 <- upbr_2016[,.(jumbo_frac_assets=mean(jumbo_frac_assets,na.rm=T),
total_assets = mean(total_assets)
),
by=IDRSSD]
# upbr_2016[,re_loan_dependance:=ntile(re_loan_frac_assets,100)]
upbr_2016[,jumbo_dependance:=ntile(jumbo_frac_assets,4)]
upbr_2016[,jumbo_7590:=ntile(jumbo_frac_assets,100)]
upbr_2016[,jumbo_7590:=ifelse(jumbo_7590<75,1,ifelse(jumbo_7590<90,2,3))]
# ggplot(upbr_2016,aes(x=jumbo_frac_assets,fill=factor(jumbo_7590)))+geom_histogram(alpha=0.5,bins=60) #[jumbo_frac_assets<50]
ggplot(upbr_2016[jumbo_frac_assets < 20], aes(x = jumbo_frac_assets, fill = factor(jumbo_7590))) +
geom_histogram(alpha = 0.75, bins = 60) +
scale_fill_manual(values = c("skyblue", "dodgerblue", "dodgerblue4"),
labels = c("Less than 75th %tile", "75-90th %tile", "Greater than 90th %tile"),
name = "")+
labs(x="Jumbo loans 2012-2016/Assets",y="Number of banks")+
theme_minimal()+
theme(legend.position = "bottom")
upbr_2016_desc <- merge(upbr_2016,
upbr[data_period %in% c("12312016"), c('Financial_Institution_Name',"total_loans_assets","total_securities_assets","cash_assets",
"re_loans_sfr_assets","re_loans_other_assets",
"individual_loans_assets","ci_assets","cre_assets",
"lns_securities_over_15_pct_of_assets","deposits_assets","core_deposits_assets","noncore_deposits_assets","brokered_deposits_assets","subordinated_debt_assets","equity_assets","tier_1_assets","total_capital_ratio","gross_loans_30_89_past_due_pct","gross_loans_90_past_due_pct","provisions_to_assets","mtg_delinq_pct_2","roa","roe","nii_assets","interest_expense_assets_1","interest_income_assets","no_of_full_time_employees","assets_per_employee",'IDRSSD')],by="IDRSSD")
bank_list <- upbr_2016_desc[,c("IDRSSD","total_assets","jumbo_frac_assets","Financial_Institution_Name")]
setorder(bank_list,-jumbo_frac_assets)
# write.csv(bank_list,"C:/Users/dratnadiwakara2/Downloads/bank_list_2.csv")
library(DescTools)
round_values <- function(x) {
ifelse(x > 100, round(x, 0), ifelse(x > 10, round(x, 1), round(x, 2)))
}
upbr_2016_desc[!is.na(jumbo_7590) ,.N,by=jumbo_7590]
## jumbo_7590 N
## 1: 1 2686
## 2: 2 521
## 3: 3 398
columns_to_include <- c('total_assets',"total_loans_assets","total_securities_assets","cash_assets",
"re_loans_sfr_assets","re_loans_other_assets",
"individual_loans_assets","ci_assets","cre_assets",
"lns_securities_over_15_pct_of_assets","deposits_assets","core_deposits_assets","noncore_deposits_assets","brokered_deposits_assets","subordinated_debt_assets","equity_assets","tier_1_assets","total_capital_ratio","gross_loans_30_89_past_due_pct","gross_loans_90_past_due_pct","provisions_to_assets","mtg_delinq_pct_2","roa","roe","nii_assets","interest_expense_assets_1","interest_income_assets","no_of_full_time_employees","assets_per_employee") #,'deposits_state_hhi','deposits_college_hhi','deposits_income_hhi'
# result_table <- bank_wa_mean_data[!is.na(bank_size), lapply(.SD, function(x) list(Mean = mean(x, na.rm = TRUE))), by = bank_size, .SDcols = columns_to_include]
result_table <- upbr_2016_desc[!is.na(jumbo_7590) , lapply(.SD, function(x) {
x_winsorized <- Winsorize(x, probs = c(0.05, 0.95), na.rm = TRUE)
list(Winsorized_Mean = mean(x_winsorized, na.rm = TRUE))
}), by = jumbo_7590, .SDcols = columns_to_include]
setorder(result_table,jumbo_7590)
transposed_result_table <- t(result_table)
colnames(transposed_result_table) <- unlist(transposed_result_table[1,])
transposed_result_table <- transposed_result_table[-1,]
transposed_result_table <- cbind(rownames(transposed_result_table), transposed_result_table)
transposed_result_table <- as.data.table(transposed_result_table)
transposed_result_table <- data.frame(lapply(transposed_result_table, function(x) unlist(x, use.names = FALSE)))
transposed_result_table <- data.table(transposed_result_table)
numeric_cols <- names(transposed_result_table)[sapply(transposed_result_table, is.numeric)]
transposed_result_table[, (numeric_cols) := lapply(.SD, round_values), .SDcols = numeric_cols]
# print(transposed_result_table)
stargazer(transposed_result_table,summary=F,type="text",rownames = F)
##
## ============================================================
## V1 X1 X2 X3
## ------------------------------------------------------------
## total_assets 680,822 559,815 858,454
## total_loans_assets 66.700 73.300 76.400
## total_securities_assets 20 13.600 11.100
## cash_assets 7.430 7.400 7.590
## re_loans_sfr_assets 19.200 23.700 32.400
## re_loans_other_assets 7.770 9.080 9.570
## individual_loans_assets 2.530 1.720 1.200
## ci_assets 8.500 8.610 6.960
## cre_assets 22.600 25.200 21.200
## lns_securities_over_15_pct_of_assets 9.670 9.310 12.600
## deposits_assets 84 83.800 81.900
## core_deposits_assets 77.200 76.600 72.900
## noncore_deposits_assets 6.440 7.020 8.720
## brokered_deposits_assets 2.380 2.760 3.510
## subordinated_debt_assets 0 0 0
## equity_assets 10.900 10.800 10.900
## tier_1_assets 11.300 11.300 11.300
## total_capital_ratio 16.600 15.700 16.200
## gross_loans_30_89_past_due_pct 0.670 0.490 0.400
## gross_loans_90_past_due_pct 0.960 0.810 0.660
## provisions_to_assets 0.110 0.110 0.090
## mtg_delinq_pct_2 2.060 1.550 1.230
## roa 0.880 0.880 0.840
## roe 8.480 8.450 8.540
## nii_assets 3.380 3.470 3.350
## interest_expense_assets_1 0.400 0.450 0.520
## interest_income_assets 4.130 4.250 4.130
## no_of_full_time_employees 148 119 144
## assets_per_employee 5.110 5.190 6.550
## ------------------------------------------------------------
correlation_data <- upbr_2016_desc
correlation_data[,log_jumbo_frac_assets:=log(jumbo_frac_assets)]
selected_columns <- correlation_data[, c("log_jumbo_frac_assets", "total_loans_assets","total_securities_assets","cash_assets",
"re_loans_sfr_assets","re_loans_other_assets",
"individual_loans_assets","ci_assets","cre_assets",
"lns_securities_over_15_pct_of_assets","deposits_assets","core_deposits_assets","noncore_deposits_assets","brokered_deposits_assets","subordinated_debt_assets","equity_assets","tier_1_assets","total_capital_ratio","gross_loans_30_89_past_due_pct","gross_loans_90_past_due_pct","provisions_to_assets","mtg_delinq_pct_2","roa","roe","nii_assets","interest_expense_assets_1","interest_income_assets","no_of_full_time_employees","assets_per_employee")]
# Compute the correlation matrix
correlation_matrix <- cor(selected_columns,use="complete.obs")
print(correlation_matrix)
## log_jumbo_frac_assets total_loans_assets
## log_jumbo_frac_assets 1.000000000 0.32823969
## total_loans_assets 0.328239694 1.00000000
## total_securities_assets -0.335275694 -0.85533362
## cash_assets 0.010398912 -0.31570645
## re_loans_sfr_assets 0.303663795 0.25960095
## re_loans_other_assets 0.125201468 0.28656075
## individual_loans_assets -0.151015255 0.03631946
## ci_assets -0.053380737 0.29560933
## cre_assets 0.060844318 0.43374529
## lns_securities_over_15_pct_of_assets 0.041236667 -0.03043938
## deposits_assets -0.025599340 -0.10202783
## core_deposits_assets -0.082246415 -0.22094482
## noncore_deposits_assets 0.091245917 0.21509771
## brokered_deposits_assets 0.036678945 0.20246256
## subordinated_debt_assets 0.008269786 0.02217238
## equity_assets -0.041789832 -0.07938580
## tier_1_assets -0.023871472 -0.06004252
## total_capital_ratio -0.084697123 -0.45984847
## gross_loans_30_89_past_due_pct -0.099926599 -0.13933256
## gross_loans_90_past_due_pct -0.073616191 -0.06238488
## provisions_to_assets -0.039559981 0.12810216
## mtg_delinq_pct_2 -0.129090086 -0.11003189
## roa 0.004133838 0.03374172
## roe -0.003398685 0.08130056
## nii_assets 0.085917827 0.47048763
## interest_expense_assets_1 0.178635678 0.32937455
## interest_income_assets 0.089170234 0.46371923
## no_of_full_time_employees -0.002341440 -0.05272674
## assets_per_employee 0.035622652 -0.07048383
## total_securities_assets cash_assets
## log_jumbo_frac_assets -0.335275694 0.010398912
## total_loans_assets -0.855333616 -0.315706447
## total_securities_assets 1.000000000 -0.166420941
## cash_assets -0.166420941 1.000000000
## re_loans_sfr_assets -0.225336010 -0.084723717
## re_loans_other_assets -0.220289335 -0.117373413
## individual_loans_assets -0.025318641 -0.020957769
## ci_assets -0.249583451 -0.099672709
## cre_assets -0.397612549 -0.091136457
## lns_securities_over_15_pct_of_assets 0.094582020 -0.125562185
## deposits_assets 0.021329863 0.207015782
## core_deposits_assets 0.140505369 0.187471148
## noncore_deposits_assets -0.176103094 -0.073780796
## brokered_deposits_assets -0.155969112 -0.086762964
## subordinated_debt_assets -0.039254518 -0.008351437
## equity_assets 0.039943604 0.054620546
## tier_1_assets 0.039766697 0.081054446
## total_capital_ratio 0.364136147 0.255635200
## gross_loans_30_89_past_due_pct 0.090522429 0.063880169
## gross_loans_90_past_due_pct 0.001651902 0.050226366
## provisions_to_assets -0.103376712 -0.039968294
## mtg_delinq_pct_2 0.047631279 0.053630023
## roa -0.001521614 -0.039660834
## roe -0.015005550 -0.075953046
## nii_assets -0.412059378 -0.111611409
## interest_expense_assets_1 -0.257293347 -0.146573336
## interest_income_assets -0.396608565 -0.174874823
## no_of_full_time_employees 0.011078899 0.012589841
## assets_per_employee 0.055574363 0.049528862
## re_loans_sfr_assets re_loans_other_assets
## log_jumbo_frac_assets 0.30366379 0.1252014684
## total_loans_assets 0.25960095 0.2865607482
## total_securities_assets -0.22533601 -0.2202893353
## cash_assets -0.08472372 -0.1173734134
## re_loans_sfr_assets 1.00000000 -0.1886809717
## re_loans_other_assets -0.18868097 1.0000000000
## individual_loans_assets -0.07434206 -0.1204190335
## ci_assets -0.40935790 -0.0676447171
## cre_assets -0.35704158 -0.1035056189
## lns_securities_over_15_pct_of_assets 0.52559636 -0.1650784532
## deposits_assets -0.20262308 -0.0240082878
## core_deposits_assets -0.06447163 -0.0412082527
## noncore_deposits_assets -0.09327183 0.0356229051
## brokered_deposits_assets -0.10059714 0.0376433878
## subordinated_debt_assets -0.06257577 -0.0294474519
## equity_assets 0.07549835 -0.0117908578
## tier_1_assets 0.09409469 0.0019946711
## total_capital_ratio 0.21173582 -0.1703724425
## gross_loans_30_89_past_due_pct 0.12055471 -0.1062783960
## gross_loans_90_past_due_pct 0.05210543 -0.0896012886
## provisions_to_assets -0.10348965 0.0008886754
## mtg_delinq_pct_2 0.02350269 -0.0729107705
## roa -0.05058138 0.0763077889
## roe -0.08556764 0.1214285392
## nii_assets -0.11517709 0.1972217888
## interest_expense_assets_1 0.27614828 0.1342490506
## interest_income_assets -0.05797704 0.2019216592
## no_of_full_time_employees -0.02698704 -0.0491564870
## assets_per_employee -0.04444001 -0.0403158261
## individual_loans_assets ci_assets
## log_jumbo_frac_assets -0.1510152549 -0.053380737
## total_loans_assets 0.0363194622 0.295609334
## total_securities_assets -0.0253186406 -0.249583451
## cash_assets -0.0209577688 -0.099672709
## re_loans_sfr_assets -0.0743420579 -0.409357899
## re_loans_other_assets -0.1204190335 -0.067644717
## individual_loans_assets 1.0000000000 0.007567894
## ci_assets 0.0075678935 1.000000000
## cre_assets -0.2018797690 0.239414717
## lns_securities_over_15_pct_of_assets -0.0467309752 -0.308425365
## deposits_assets 0.0500464927 0.091056599
## core_deposits_assets -0.0088059185 -0.108736762
## noncore_deposits_assets 0.0574219347 0.233386223
## brokered_deposits_assets 0.0704768382 0.242831335
## subordinated_debt_assets 0.0370448081 0.124397495
## equity_assets -0.0325735577 -0.121658401
## tier_1_assets -0.0376009379 -0.144526941
## total_capital_ratio -0.0604618794 -0.350095945
## gross_loans_30_89_past_due_pct 0.1710449147 -0.106195955
## gross_loans_90_past_due_pct 0.0265039444 -0.011480962
## provisions_to_assets 0.2523943489 0.190270865
## mtg_delinq_pct_2 0.0481613540 -0.029099256
## roa -0.0006128742 0.004259741
## roe 0.0276492880 0.063913672
## nii_assets 0.1977537916 0.224882700
## interest_expense_assets_1 -0.0070363566 -0.042235731
## interest_income_assets 0.1891709017 0.176618060
## no_of_full_time_employees 0.0680112123 0.038032547
## assets_per_employee 0.0467462520 0.057475743
## cre_assets
## log_jumbo_frac_assets 0.060844318
## total_loans_assets 0.433745291
## total_securities_assets -0.397612549
## cash_assets -0.091136457
## re_loans_sfr_assets -0.357041582
## re_loans_other_assets -0.103505619
## individual_loans_assets -0.201879769
## ci_assets 0.239414717
## cre_assets 1.000000000
## lns_securities_over_15_pct_of_assets -0.271431642
## deposits_assets 0.056130555
## core_deposits_assets -0.074301149
## noncore_deposits_assets 0.153978473
## brokered_deposits_assets 0.125264864
## subordinated_debt_assets 0.014946516
## equity_assets -0.065312247
## tier_1_assets -0.051549928
## total_capital_ratio -0.365803071
## gross_loans_30_89_past_due_pct -0.219635248
## gross_loans_90_past_due_pct -0.060291045
## provisions_to_assets 0.026970904
## mtg_delinq_pct_2 -0.095167234
## roa 0.020356939
## roe 0.017324893
## nii_assets 0.299351604
## interest_expense_assets_1 0.002731164
## interest_income_assets 0.253916175
## no_of_full_time_employees -0.065668936
## assets_per_employee -0.055563249
## lns_securities_over_15_pct_of_assets
## log_jumbo_frac_assets 0.041236667
## total_loans_assets -0.030439375
## total_securities_assets 0.094582020
## cash_assets -0.125562185
## re_loans_sfr_assets 0.525596358
## re_loans_other_assets -0.165078453
## individual_loans_assets -0.046730975
## ci_assets -0.308425365
## cre_assets -0.271431642
## lns_securities_over_15_pct_of_assets 1.000000000
## deposits_assets -0.187021352
## core_deposits_assets -0.081340350
## noncore_deposits_assets -0.055733562
## brokered_deposits_assets -0.063424593
## subordinated_debt_assets -0.029178686
## equity_assets 0.076777140
## tier_1_assets 0.079595981
## total_capital_ratio 0.236969694
## gross_loans_30_89_past_due_pct 0.084242573
## gross_loans_90_past_due_pct 0.036610325
## provisions_to_assets -0.066027709
## mtg_delinq_pct_2 0.029712698
## roa -0.043134341
## roe -0.075322578
## nii_assets -0.173783061
## interest_expense_assets_1 0.198464537
## interest_income_assets -0.097852327
## no_of_full_time_employees 0.017534818
## assets_per_employee -0.009690563
## deposits_assets core_deposits_assets
## log_jumbo_frac_assets -0.025599340 -0.082246415
## total_loans_assets -0.102027827 -0.220944820
## total_securities_assets 0.021329863 0.140505369
## cash_assets 0.207015782 0.187471148
## re_loans_sfr_assets -0.202623077 -0.064471627
## re_loans_other_assets -0.024008288 -0.041208253
## individual_loans_assets 0.050046493 -0.008805918
## ci_assets 0.091056599 -0.108736762
## cre_assets 0.056130555 -0.074301149
## lns_securities_over_15_pct_of_assets -0.187021352 -0.081340350
## deposits_assets 1.000000000 0.696378925
## core_deposits_assets 0.696378925 1.000000000
## noncore_deposits_assets -0.065517405 -0.761757259
## brokered_deposits_assets -0.103157040 -0.637903088
## subordinated_debt_assets -0.077687421 -0.129217190
## equity_assets -0.416008330 -0.243664199
## tier_1_assets -0.341970379 -0.195863861
## total_capital_ratio -0.225341610 -0.049299019
## gross_loans_30_89_past_due_pct 0.039468895 0.060907442
## gross_loans_90_past_due_pct -0.015413318 0.014488441
## provisions_to_assets 0.032195279 -0.039779523
## mtg_delinq_pct_2 -0.037149984 -0.013542919
## roa -0.036196239 -0.038607221
## roe 0.009719526 -0.039843877
## nii_assets 0.142030239 0.056908732
## interest_expense_assets_1 -0.329418233 -0.461758121
## interest_income_assets 0.035878288 -0.066328859
## no_of_full_time_employees -0.073135777 -0.090054523
## assets_per_employee -0.070957307 -0.144943176
## noncore_deposits_assets
## log_jumbo_frac_assets 0.09124592
## total_loans_assets 0.21509771
## total_securities_assets -0.17610309
## cash_assets -0.07378080
## re_loans_sfr_assets -0.09327183
## re_loans_other_assets 0.03562291
## individual_loans_assets 0.05742193
## ci_assets 0.23338622
## cre_assets 0.15397847
## lns_securities_over_15_pct_of_assets -0.05573356
## deposits_assets -0.06551741
## core_deposits_assets -0.76175726
## noncore_deposits_assets 1.00000000
## brokered_deposits_assets 0.79381542
## subordinated_debt_assets 0.10953268
## equity_assets -0.03675142
## tier_1_assets -0.03637690
## total_capital_ratio -0.13487636
## gross_loans_30_89_past_due_pct -0.04905590
## gross_loans_90_past_due_pct -0.03405866
## provisions_to_assets 0.08437277
## mtg_delinq_pct_2 -0.01470620
## roa 0.02100407
## roe 0.06417282
## nii_assets 0.04908858
## interest_expense_assets_1 0.34465253
## interest_income_assets 0.12461161
## no_of_full_time_employees 0.05918995
## assets_per_employee 0.13747347
## brokered_deposits_assets
## log_jumbo_frac_assets 0.036678945
## total_loans_assets 0.202462560
## total_securities_assets -0.155969112
## cash_assets -0.086762964
## re_loans_sfr_assets -0.100597144
## re_loans_other_assets 0.037643388
## individual_loans_assets 0.070476838
## ci_assets 0.242831335
## cre_assets 0.125264864
## lns_securities_over_15_pct_of_assets -0.063424593
## deposits_assets -0.103157040
## core_deposits_assets -0.637903088
## noncore_deposits_assets 0.793815423
## brokered_deposits_assets 1.000000000
## subordinated_debt_assets 0.080024120
## equity_assets -0.052896581
## tier_1_assets -0.064852351
## total_capital_ratio -0.155355736
## gross_loans_30_89_past_due_pct -0.063085058
## gross_loans_90_past_due_pct -0.016925039
## provisions_to_assets 0.075785845
## mtg_delinq_pct_2 -0.008355164
## roa 0.027935969
## roe 0.050715431
## nii_assets 0.051573385
## interest_expense_assets_1 0.226503178
## interest_income_assets 0.094535903
## no_of_full_time_employees 0.013615994
## assets_per_employee 0.164897039
## subordinated_debt_assets equity_assets
## log_jumbo_frac_assets 0.0082697862 -0.041789832
## total_loans_assets 0.0221723752 -0.079385802
## total_securities_assets -0.0392545180 0.039943604
## cash_assets -0.0083514373 0.054620546
## re_loans_sfr_assets -0.0625757702 0.075498355
## re_loans_other_assets -0.0294474519 -0.011790858
## individual_loans_assets 0.0370448081 -0.032573558
## ci_assets 0.1243974947 -0.121658401
## cre_assets 0.0149465157 -0.065312247
## lns_securities_over_15_pct_of_assets -0.0291786856 0.076777140
## deposits_assets -0.0776874214 -0.416008330
## core_deposits_assets -0.1292171903 -0.243664199
## noncore_deposits_assets 0.1095326776 -0.036751416
## brokered_deposits_assets 0.0800241202 -0.052896581
## subordinated_debt_assets 1.0000000000 -0.037001598
## equity_assets -0.0370015984 1.000000000
## tier_1_assets -0.0840203821 0.920092952
## total_capital_ratio -0.0633103702 0.711706522
## gross_loans_30_89_past_due_pct -0.0240412849 0.047492123
## gross_loans_90_past_due_pct 0.0003282945 0.034616193
## provisions_to_assets 0.0241235397 -0.035904225
## mtg_delinq_pct_2 0.0178787318 0.077769144
## roa -0.0191116323 0.123518637
## roe -0.0073779255 0.042073947
## nii_assets -0.0957602838 0.022689725
## interest_expense_assets_1 0.0576187086 -0.001734922
## interest_income_assets -0.0635318678 0.028598682
## no_of_full_time_employees 0.1876104676 -0.001894592
## assets_per_employee 0.0987405115 0.023659741
## tier_1_assets total_capital_ratio
## log_jumbo_frac_assets -0.023871472 -0.08469712
## total_loans_assets -0.060042523 -0.45984847
## total_securities_assets 0.039766697 0.36413615
## cash_assets 0.081054446 0.25563520
## re_loans_sfr_assets 0.094094692 0.21173582
## re_loans_other_assets 0.001994671 -0.17037244
## individual_loans_assets -0.037600938 -0.06046188
## ci_assets -0.144526941 -0.35009594
## cre_assets -0.051549928 -0.36580307
## lns_securities_over_15_pct_of_assets 0.079595981 0.23696969
## deposits_assets -0.341970379 -0.22534161
## core_deposits_assets -0.195863861 -0.04929902
## noncore_deposits_assets -0.036376902 -0.13487636
## brokered_deposits_assets -0.064852351 -0.15535574
## subordinated_debt_assets -0.084020382 -0.06331037
## equity_assets 0.920092952 0.71170652
## tier_1_assets 1.000000000 0.75496367
## total_capital_ratio 0.754963667 1.00000000
## gross_loans_30_89_past_due_pct 0.089408561 0.16975066
## gross_loans_90_past_due_pct 0.075654492 0.08370215
## provisions_to_assets 0.009682098 -0.10384536
## mtg_delinq_pct_2 0.100327201 0.11891955
## roa 0.069484591 0.01018571
## roe 0.020955621 -0.05371665
## nii_assets 0.092994027 -0.24114418
## interest_expense_assets_1 0.041174448 -0.03056783
## interest_income_assets 0.077109362 -0.23868331
## no_of_full_time_employees -0.042648385 -0.02985175
## assets_per_employee 0.001960020 0.06921024
## gross_loans_30_89_past_due_pct
## log_jumbo_frac_assets -0.099926599
## total_loans_assets -0.139332564
## total_securities_assets 0.090522429
## cash_assets 0.063880169
## re_loans_sfr_assets 0.120554713
## re_loans_other_assets -0.106278396
## individual_loans_assets 0.171044915
## ci_assets -0.106195955
## cre_assets -0.219635248
## lns_securities_over_15_pct_of_assets 0.084242573
## deposits_assets 0.039468895
## core_deposits_assets 0.060907442
## noncore_deposits_assets -0.049055901
## brokered_deposits_assets -0.063085058
## subordinated_debt_assets -0.024041285
## equity_assets 0.047492123
## tier_1_assets 0.089408561
## total_capital_ratio 0.169750657
## gross_loans_30_89_past_due_pct 1.000000000
## gross_loans_90_past_due_pct 0.383436196
## provisions_to_assets 0.134016219
## mtg_delinq_pct_2 0.551049490
## roa -0.113108228
## roe -0.151238885
## nii_assets 0.087131515
## interest_expense_assets_1 0.061544760
## interest_income_assets 0.116599666
## no_of_full_time_employees 0.001817061
## assets_per_employee -0.069176645
## gross_loans_90_past_due_pct
## log_jumbo_frac_assets -0.0736161913
## total_loans_assets -0.0623848753
## total_securities_assets 0.0016519021
## cash_assets 0.0502263658
## re_loans_sfr_assets 0.0521054330
## re_loans_other_assets -0.0896012886
## individual_loans_assets 0.0265039444
## ci_assets -0.0114809625
## cre_assets -0.0602910446
## lns_securities_over_15_pct_of_assets 0.0366103253
## deposits_assets -0.0154133180
## core_deposits_assets 0.0144884408
## noncore_deposits_assets -0.0340586571
## brokered_deposits_assets -0.0169250393
## subordinated_debt_assets 0.0003282945
## equity_assets 0.0346161925
## tier_1_assets 0.0756544918
## total_capital_ratio 0.0837021467
## gross_loans_30_89_past_due_pct 0.3834361964
## gross_loans_90_past_due_pct 1.0000000000
## provisions_to_assets 0.1860457520
## mtg_delinq_pct_2 0.6212072570
## roa -0.2217034443
## roe -0.2536611029
## nii_assets -0.0073334322
## interest_expense_assets_1 0.0821822317
## interest_income_assets 0.0134460579
## no_of_full_time_employees 0.0213685539
## assets_per_employee -0.0416490806
## provisions_to_assets mtg_delinq_pct_2
## log_jumbo_frac_assets -0.0395599813 -0.129090086
## total_loans_assets 0.1281021570 -0.110031891
## total_securities_assets -0.1033767121 0.047631279
## cash_assets -0.0399682936 0.053630023
## re_loans_sfr_assets -0.1034896548 0.023502690
## re_loans_other_assets 0.0008886754 -0.072910771
## individual_loans_assets 0.2523943489 0.048161354
## ci_assets 0.1902708648 -0.029099256
## cre_assets 0.0269709038 -0.095167234
## lns_securities_over_15_pct_of_assets -0.0660277087 0.029712698
## deposits_assets 0.0321952793 -0.037149984
## core_deposits_assets -0.0397795230 -0.013542919
## noncore_deposits_assets 0.0843727716 -0.014706200
## brokered_deposits_assets 0.0757858451 -0.008355164
## subordinated_debt_assets 0.0241235397 0.017878732
## equity_assets -0.0359042251 0.077769144
## tier_1_assets 0.0096820979 0.100327201
## total_capital_ratio -0.1038453567 0.118919552
## gross_loans_30_89_past_due_pct 0.1340162194 0.551049490
## gross_loans_90_past_due_pct 0.1860457520 0.621207257
## provisions_to_assets 1.0000000000 0.070016621
## mtg_delinq_pct_2 0.0700166208 1.000000000
## roa -0.2579984699 -0.123707332
## roe -0.1800933330 -0.161297889
## nii_assets 0.3612490680 0.029747255
## interest_expense_assets_1 0.1017948815 0.050688771
## interest_income_assets 0.3702444774 0.053637809
## no_of_full_time_employees 0.0193650506 0.062286465
## assets_per_employee -0.0133956343 -0.046078307
## roa roe nii_assets
## log_jumbo_frac_assets 0.0041338380 -0.003398685 0.085917827
## total_loans_assets 0.0337417198 0.081300563 0.470487633
## total_securities_assets -0.0015216142 -0.015005550 -0.412059378
## cash_assets -0.0396608338 -0.075953046 -0.111611409
## re_loans_sfr_assets -0.0505813809 -0.085567641 -0.115177094
## re_loans_other_assets 0.0763077889 0.121428539 0.197221789
## individual_loans_assets -0.0006128742 0.027649288 0.197753792
## ci_assets 0.0042597413 0.063913672 0.224882700
## cre_assets 0.0203569392 0.017324893 0.299351604
## lns_securities_over_15_pct_of_assets -0.0431343406 -0.075322578 -0.173783061
## deposits_assets -0.0361962390 0.009719526 0.142030239
## core_deposits_assets -0.0386072207 -0.039843877 0.056908732
## noncore_deposits_assets 0.0210040722 0.064172824 0.049088580
## brokered_deposits_assets 0.0279359686 0.050715431 0.051573385
## subordinated_debt_assets -0.0191116323 -0.007377926 -0.095760284
## equity_assets 0.1235186368 0.042073947 0.022689725
## tier_1_assets 0.0694845912 0.020955621 0.092994027
## total_capital_ratio 0.0101857131 -0.053716653 -0.241144178
## gross_loans_30_89_past_due_pct -0.1131082280 -0.151238885 0.087131515
## gross_loans_90_past_due_pct -0.2217034443 -0.253661103 -0.007333432
## provisions_to_assets -0.2579984699 -0.180093333 0.361249068
## mtg_delinq_pct_2 -0.1237073316 -0.161297889 0.029747255
## roa 1.0000000000 0.742199594 0.122317919
## roe 0.7421995940 1.000000000 0.214126675
## nii_assets 0.1223179192 0.214126675 1.000000000
## interest_expense_assets_1 -0.0610375048 -0.060244844 -0.034885233
## interest_income_assets 0.0894742409 0.157026549 0.869899334
## no_of_full_time_employees 0.0063398608 0.008737317 -0.069189965
## assets_per_employee 0.0189907596 0.026833473 -0.192370351
## interest_expense_assets_1
## log_jumbo_frac_assets 0.178635678
## total_loans_assets 0.329374549
## total_securities_assets -0.257293347
## cash_assets -0.146573336
## re_loans_sfr_assets 0.276148285
## re_loans_other_assets 0.134249051
## individual_loans_assets -0.007036357
## ci_assets -0.042235731
## cre_assets 0.002731164
## lns_securities_over_15_pct_of_assets 0.198464537
## deposits_assets -0.329418233
## core_deposits_assets -0.461758121
## noncore_deposits_assets 0.344652535
## brokered_deposits_assets 0.226503178
## subordinated_debt_assets 0.057618709
## equity_assets -0.001734922
## tier_1_assets 0.041174448
## total_capital_ratio -0.030567830
## gross_loans_30_89_past_due_pct 0.061544760
## gross_loans_90_past_due_pct 0.082182232
## provisions_to_assets 0.101794881
## mtg_delinq_pct_2 0.050688771
## roa -0.061037505
## roe -0.060244844
## nii_assets -0.034885233
## interest_expense_assets_1 1.000000000
## interest_income_assets 0.226236970
## no_of_full_time_employees -0.036983071
## assets_per_employee 0.055400290
## interest_income_assets
## log_jumbo_frac_assets 0.08917023
## total_loans_assets 0.46371923
## total_securities_assets -0.39660857
## cash_assets -0.17487482
## re_loans_sfr_assets -0.05797704
## re_loans_other_assets 0.20192166
## individual_loans_assets 0.18917090
## ci_assets 0.17661806
## cre_assets 0.25391618
## lns_securities_over_15_pct_of_assets -0.09785233
## deposits_assets 0.03587829
## core_deposits_assets -0.06632886
## noncore_deposits_assets 0.12461161
## brokered_deposits_assets 0.09453590
## subordinated_debt_assets -0.06353187
## equity_assets 0.02859868
## tier_1_assets 0.07710936
## total_capital_ratio -0.23868331
## gross_loans_30_89_past_due_pct 0.11659967
## gross_loans_90_past_due_pct 0.01344606
## provisions_to_assets 0.37024448
## mtg_delinq_pct_2 0.05363781
## roa 0.08947424
## roe 0.15702655
## nii_assets 0.86989933
## interest_expense_assets_1 0.22623697
## interest_income_assets 1.00000000
## no_of_full_time_employees -0.06696518
## assets_per_employee -0.18017269
## no_of_full_time_employees
## log_jumbo_frac_assets -0.002341440
## total_loans_assets -0.052726738
## total_securities_assets 0.011078899
## cash_assets 0.012589841
## re_loans_sfr_assets -0.026987038
## re_loans_other_assets -0.049156487
## individual_loans_assets 0.068011212
## ci_assets 0.038032547
## cre_assets -0.065668936
## lns_securities_over_15_pct_of_assets 0.017534818
## deposits_assets -0.073135777
## core_deposits_assets -0.090054523
## noncore_deposits_assets 0.059189947
## brokered_deposits_assets 0.013615994
## subordinated_debt_assets 0.187610468
## equity_assets -0.001894592
## tier_1_assets -0.042648385
## total_capital_ratio -0.029851745
## gross_loans_30_89_past_due_pct 0.001817061
## gross_loans_90_past_due_pct 0.021368554
## provisions_to_assets 0.019365051
## mtg_delinq_pct_2 0.062286465
## roa 0.006339861
## roe 0.008737317
## nii_assets -0.069189965
## interest_expense_assets_1 -0.036983071
## interest_income_assets -0.066965176
## no_of_full_time_employees 1.000000000
## assets_per_employee 0.024414890
## assets_per_employee
## log_jumbo_frac_assets 0.035622652
## total_loans_assets -0.070483832
## total_securities_assets 0.055574363
## cash_assets 0.049528862
## re_loans_sfr_assets -0.044440010
## re_loans_other_assets -0.040315826
## individual_loans_assets 0.046746252
## ci_assets 0.057475743
## cre_assets -0.055563249
## lns_securities_over_15_pct_of_assets -0.009690563
## deposits_assets -0.070957307
## core_deposits_assets -0.144943176
## noncore_deposits_assets 0.137473467
## brokered_deposits_assets 0.164897039
## subordinated_debt_assets 0.098740511
## equity_assets 0.023659741
## tier_1_assets 0.001960020
## total_capital_ratio 0.069210242
## gross_loans_30_89_past_due_pct -0.069176645
## gross_loans_90_past_due_pct -0.041649081
## provisions_to_assets -0.013395634
## mtg_delinq_pct_2 -0.046078307
## roa 0.018990760
## roe 0.026833473
## nii_assets -0.192370351
## interest_expense_assets_1 0.055400290
## interest_income_assets -0.180172691
## no_of_full_time_employees 0.024414890
## assets_per_employee 1.000000000
melted_corr <- melt(correlation_matrix)
# melted_corr <- melted_corr[melted_corr$Var1 != melted_corr$Var2, ]
# melted_corr <- melted_corr[melted_corr$Var1 < melted_corr$Var2, ]
# Create a heatmap using ggplot2
ggplot(melted_corr, aes(x = Var1, y = Var2, fill = value)) +
geom_tile(color = "white") + # Adds the colored tiles
geom_text(aes(label = sprintf("%.2f", value)), color = "black", size = 3) + # Adds text labels
scale_fill_gradient2(low = "dodgerblue4", high = "darkorange", mid = "white", midpoint = 0, limit = c(-1, 1)) +
theme_minimal() +
labs(x = "", y = "", fill = "Correlation") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Adjust the x-axis text angle
hmda[,jumbo:=ifelse(dist_gse_limit>1,1,ifelse(dist_gse_limit< -1,0,NA))]
hmda_post[,jumbo:=ifelse(dist_gse_limit>1,1,ifelse(dist_gse_limit< -1,0,NA))]
hmda_1_summary <- hmda[actiontaken==1 & purposeofloan %in% c(1,3) & typeofloan==1 ,.(no_loans=.N,total_loan_amt=sum(amountofloan)),
by=.(RSSD,asofdate,jumbo)]
hmda_2_summary <- hmda_post[actiontaken==1 & purposeofloan %in% c(1,3) & typeofloan==1 ,.(no_loans=.N,total_loan_amt=sum(amountofloan)),
by=.(RSSD,asofdate,jumbo)]
hmda_summary <- rbind(hmda_1_summary,hmda_2_summary)
hmda_summary <- merge(hmda_summary,upbr_2016[,c("IDRSSD","jumbo_dependance","jumbo_frac_assets","total_assets","jumbo_7590")],
by.x="RSSD",by.y="IDRSSD",all.x=T)
hmda_summary[,new_loans_assets:=total_loan_amt/total_assets]
hmda_summary[,log_total_loan_amt:=log(1+total_loan_amt)]
hmda_summary[,high_jumbo_assets:=ifelse(jumbo_dependance %in% 4,1,0)]
r <- list()
r[[1]] <- felm(log_total_loan_amt~log(jumbo_frac_assets)*I(asofdate>=2018)|RSSD+asofdate|0|RSSD,data=hmda_summary[asofdate %in% 2012:2021 & jumbo==1 ])
r[[2]] <- felm(log_total_loan_amt~factor(jumbo_7590)*I(asofdate>=2018)|RSSD+asofdate|0|RSSD,data=hmda_summary[asofdate %in% 2012:2021 & jumbo==1 ])
r[[3]] <- felm(log_total_loan_amt~log(jumbo_frac_assets)*I(asofdate>=2018)|RSSD+asofdate|0|RSSD,data=hmda_summary[asofdate %in% 2012:2021 & jumbo==0 ])
r[[4]] <- felm(log_total_loan_amt~factor(jumbo_7590)*I(asofdate>=2018)|RSSD+asofdate|0|RSSD,data=hmda_summary[asofdate %in% 2012:2021 & jumbo==0 ])
stargazer(r,type="text",no.space = T,column.labels = c("Jumbo","Conforming","Jumbo-previous year"),column.separate = c(2,2,2),omit.stat = "ser",
add.lines = list(c("Bank FE",rep("Y",6)),c("Year FE",rep("Y",6))))
##
## ================================================================================
## Dependent variable:
## ------------------------------------
## log_total_loan_amt
## Jumbo Conforming
## (1) (2) (3) (4)
## --------------------------------------------------------------------------------
## log(jumbo_frac_assets)
## (0.000) (0.000)
## factor(jumbo_7590)2
## (0.000) (0.000)
## factor(jumbo_7590)3
## (0.000) (0.000)
## I(asofdate > = 2018)
## (0.000) (0.000) (0.000) (0.000)
## log(jumbo_frac_assets):I(asofdate > = 2018) -0.193*** -0.045**
## (0.027) (0.021)
## factor(jumbo_7590)2:I(asofdate > = 2018) -0.217*** -0.064
## (0.060) (0.054)
## factor(jumbo_7590)3:I(asofdate > = 2018) -0.225*** -0.104*
## (0.062) (0.054)
## --------------------------------------------------------------------------------
## Bank FE Y Y Y Y
## Year FE Y Y Y Y
## Observations 25,089 25,089 29,022 29,022
## R2 0.852 0.851 0.913 0.913
## Adjusted R2 0.823 0.821 0.899 0.899
## ================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
library(gridExtra)
r <- list()
r[[1]] <- felm(log_total_loan_amt~high_jumbo_assets*factor(asofdate)|RSSD+asofdate|0|RSSD,data=hmda_summary[jumbo==1 & asofdate %in% 2014:2021])
r[[2]] <- felm(log_total_loan_amt~high_jumbo_assets*factor(asofdate)|RSSD+asofdate|0|RSSD,data=hmda_summary[jumbo==0 & asofdate %in% 2014:2021])
# r[[3]] <- felm(log_total_loan_amt~high_jumbo_assets*factor(asofdate)|RSSD+asofdate|0|RSSD,data=hmda_summary2[ asofdate %in% 2014:2021])
g1 <- coef_plot_1reg_10ci(r[[1]],"high_jumbo_assets:factor(asofdate)",2014)+ggtitle("log(Total loan volume), Jumbo")
g2 <- coef_plot_1reg_10ci(r[[2]],"high_jumbo_assets:factor(asofdate)",2014)+ggtitle("log(Total loan volume), Non-jumbo")
grid.arrange(g1,g2,nrow=1)
hmda_merged <- rbind(hmda[actiontaken %in% c(1,3) & asofdate %in% 2012:2022,
c("actiontaken","amountofloan","applicantincome","asofdate","purposeofloan","dist_gse_limit","RSSD","county","typeofloan")],
hmda_post[actiontaken %in% c(1,3) & asofdate %in% 2012:2022,
c("actiontaken","amountofloan","applicantincome","asofdate","purposeofloan","dist_gse_limit","RSSD","county","typeofloan")])
hmda_merged <- merge(hmda_merged,
upbr_2016[,c("IDRSSD","jumbo_dependance","jumbo_frac_assets","total_assets","jumbo_7590")],
by.x="RSSD",by.y="IDRSSD",all.x=T)
hmda_merged[,county_year:=paste(county,asofdate)]
# hmda_merged[,bank_county:=paste(county,RSSD)]
hmda_merged <- hmda_merged[applicantincome>0 & amountofloan>0]
hmda_merged[,loan_to_income:=amountofloan/applicantincome]
hmda_merged[,high_jumbo_assets:=ifelse(jumbo_dependance %in% 4,1,0)]
# hmda_merged[,high_jumbo_assets2:=ifelse(jumbo_dependance %in% 5:6,1,0)]
hmda_merged[,approved:=ifelse(actiontaken==1,1,ifelse(actiontaken==3,0,NA))]
# hmda_merged[,jumbo_dependance_cat:=ifelse(jumbo_dependance<4,1,jumbo_dependance)]
hmda_merged_jumbo <- hmda_merged[dist_gse_limit>10] # & dist_gse_limit<750
hmda_merged_jumbo[,loan_amount_bin:=ntile(amountofloan,10),by=county_year]
hmda_merged_jumbo[,county_year_amt_bin:=paste(county_year,loan_amount_bin)]
hmda_merged_jumbo[,bank_county:=paste(county,RSSD)]
hmda_merged_conforming <- hmda_merged[dist_gse_limit< -10 & dist_gse_limit> -250] # & dist_gse_limit> -150
hmda_merged_conforming[,loan_amount_bin:=ntile(amountofloan,10),by=county_year]
hmda_merged_conforming[,county_year_amt_bin:=paste(county_year,loan_amount_bin)]
r <- list()
r[[1]] <- felm(approved~log(jumbo_frac_assets)*I(asofdate>=2018)+log(applicantincome)+log(amountofloan)+I(purposeofloan==1)|county_year_amt_bin+RSSD|0|county, data=hmda_merged_jumbo)
r[[2]] <- felm(approved~factor(jumbo_7590)*I(asofdate>=2018)+log(applicantincome)+log(amountofloan)+I(purposeofloan==1)|county_year_amt_bin+RSSD|0|county, data=hmda_merged_jumbo)
r[[3]] <- felm(approved~log(jumbo_frac_assets)*I(asofdate>=2018)+log(applicantincome)+log(amountofloan)+I(purposeofloan==1)|county_year_amt_bin+RSSD|0|county, data=hmda_merged_conforming)
r[[4]] <- felm(approved~factor(jumbo_7590)*I(asofdate>=2018)+log(applicantincome)+log(amountofloan)+I(purposeofloan==1)|county_year_amt_bin+RSSD|0|county, data=hmda_merged_conforming)
stargazer(r,type="text",no.space = T,column.labels = c("Jumbo","Conforming"),column.separate = c(2,2),omit.stat = "ser",
add.lines = list(c("Bank FE",rep("Y",4)),c("County-Yr-LoanAmt Decile FE",rep("Y",4))))
##
## =====================================================================================
## Dependent variable:
## -----------------------------------------
## approved
## Jumbo Conforming
## (1) (2) (3) (4)
## -------------------------------------------------------------------------------------
## log(jumbo_frac_assets)
## (0.000) (0.000)
## factor(jumbo_7590)2
## (0.000) (0.000)
## factor(jumbo_7590)3
## (0.000) (0.000)
## I(asofdate > = 2018)
## (0.000) (0.000) (0.000) (0.000)
## log(applicantincome) 0.121*** 0.121*** 0.103*** 0.102***
## (0.003) (0.003) (0.001) (0.001)
## log(amountofloan) -0.166*** -0.166*** -0.100*** -0.100***
## (0.004) (0.004) (0.005) (0.005)
## I(purposeofloan == 1) 0.104*** 0.105*** 0.106*** 0.107***
## (0.002) (0.002) (0.001) (0.001)
## log(jumbo_frac_assets):I(asofdate > = 2018) -0.018*** -0.017***
## (0.001) (0.001)
## factor(jumbo_7590)2:I(asofdate > = 2018) -0.055*** -0.043***
## (0.002) (0.002)
## factor(jumbo_7590)3:I(asofdate > = 2018) -0.033*** -0.027***
## (0.003) (0.003)
## -------------------------------------------------------------------------------------
## Bank FE Y Y Y Y
## County-Yr-LoanAmt Decile FE Y Y Y Y
## Observations 3,200,672 3,200,672 11,665,202 11,665,202
## R2 0.150 0.151 0.117 0.117
## Adjusted R2 0.112 0.112 0.095 0.095
## =====================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
r <- list()
r[[1]] <- felm(approved~high_jumbo_assets*factor(asofdate)+log(applicantincome)+log(amountofloan)+I(purposeofloan == 1)|county_year_amt_bin+RSSD|0|county, data=hmda_merged_jumbo[asofdate %in% 2014:2021])
r[[2]] <- felm(approved~high_jumbo_assets*factor(asofdate)+log(applicantincome)+log(amountofloan)+I(purposeofloan == 1)|county_year_amt_bin+RSSD|0|county, data=hmda_merged_conforming[asofdate %in% 2014:2021])
g1 <- coef_plot_1reg_10ci(r[[1]],"high_jumbo_assets:factor(asofdate)",2014)+ggtitle("Jumbo")
g2 <- coef_plot_1reg_10ci(r[[2]],"high_jumbo_assets:factor(asofdate)",2014)+ggtitle("Conforming")
grid.arrange(g1,g2,nrow=1)
upbr_merged <- merge(upbr,upbr_2016[,c("IDRSSD","jumbo_dependance",
"jumbo_frac_assets","jumbo_7590")],
by.x="IDRSSD",by.y="IDRSSD")
upbr_merged[,data_period:=as.Date(data_period,format="%m%d%Y")]
upbr_merged[,qtrs:=((year(data_period) - 2017) * 4) + quarter(data_period) -4 ]
upbr_merged[,high_jumbo_assets:=ifelse(jumbo_dependance %in% 4,1,0)]
upbr_merged[,log_no_of_full_time_employees:=log(no_of_full_time_employees)]
upbr_merged[,log_assets_per_employee:=log(assets_per_employee)]
upbr_merged[,roa_sd_4q := frollapply(roa, 4, sd, align = "right", fill = NA), by = IDRSSD]
upbr_merged[,zscore:=(roa+equity_assets)/roa_sd_4q]
lower_bound <- quantile(upbr_merged$mtg_delinq_pct, probs = 0.01, na.rm = TRUE)
upper_bound <- quantile(upbr_merged$mtg_delinq_pct, probs = 0.99, na.rm = TRUE)
r <- list()
r[[1]] <- felm(mtg_delinq_pct~high_jumbo_assets *I(qtrs %in% 1:8)+high_jumbo_assets *I(qtrs %in% 9:16) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD, data=upbr_merged[(mtg_delinq_pct > lower_bound & mtg_delinq_pct < upper_bound) & qtrs <= 16 & qtrs>= -12])
r[[2]] <- felm(mtg_delinq_pct~high_jumbo_assets *I(qtrs %in% 1:8)+high_jumbo_assets *I(qtrs %in% 9:16) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD, data=upbr_merged[(mtg_delinq_pct > lower_bound & mtg_delinq_pct < upper_bound) & qtrs <= 16 & qtrs>= -12 & total_assets<1e6 ])
r[[3]] <- felm(mtg_delinq_pct~high_jumbo_assets *I(qtrs %in% 1:8)+high_jumbo_assets *I(qtrs %in% 9:16) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD, data=upbr_merged[(mtg_delinq_pct > lower_bound & mtg_delinq_pct < upper_bound) & qtrs <= 16 & qtrs>= -12 & total_assets>1e6 & total_assets<1e7])
r[[4]] <- felm(mtg_delinq_pct~high_jumbo_assets *I(qtrs %in% 1:8)+high_jumbo_assets *I(qtrs %in% 9:16) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD, data=upbr_merged[(mtg_delinq_pct > lower_bound & mtg_delinq_pct < upper_bound) & qtrs <= 16 & qtrs>= -12 & total_assets>1e7])
stargazer(r,type="text",no.space = T,column.labels = c("All","<1b","1-10b",">10b"),omit.stat = "ser", add.lines = list(c("Bank FE",rep("Y",4)),c("Quarter",rep("Y",4))))
##
## ================================================================
## Dependent variable:
## ---------------------------------
## mtg_delinq_pct
## All <1b 1-10b >10b
## (1) (2) (3) (4)
## ----------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs 1:8)
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs 9:16)
## (0.000) (0.000) (0.000) (0.000)
## log(total_assets) 0.108 0.016 0.110 0.085
## (0.069) (0.120) (0.106) (0.281)
## equity_assets 0.011 0.002 0.013 0.054
## (0.011) (0.014) (0.014) (0.046)
## high_jumbo_assets:I(qtrs 1:8) 0.078* 0.052 0.188*** -0.078
## (0.041) (0.052) (0.063) (0.202)
## high_jumbo_assets:I(qtrs 9:16) 0.187*** 0.125* 0.367*** -0.012
## (0.053) (0.072) (0.079) (0.261)
## ----------------------------------------------------------------
## Bank FE Y Y Y Y
## Quarter Y Y Y Y
## Observations 77,620 57,640 16,792 3,188
## R2 0.638 0.615 0.740 0.792
## Adjusted R2 0.619 0.592 0.724 0.778
## ================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
dep_var <- "mtg_delinq_pct"
formula <- as.formula(paste( dep_var,"~ high_jumbo_assets * factor(qtrs) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD"))
lower_bound <- quantile(upbr_merged$mtg_delinq_pct, probs = 0.01, na.rm = TRUE)
upper_bound <- quantile(upbr_merged$mtg_delinq_pct, probs = 0.99, na.rm = TRUE)
filtered_data <- upbr_merged %>%
filter((mtg_delinq_pct > lower_bound & mtg_delinq_pct < upper_bound) & qtrs <= 16 & qtrs>= -12)
coef_plot_1reg_10ci(felm(formula, data = filtered_data[qtrs <= 16 & qtrs>= -12]),"high_jumbo_assets:factor(qtrs)",-12)+ggtitle(dep_var)
table_bank_fe_regression <- function(dep_vars, min_qt, max_qt) {
results <- list() # Initialize an empty list to store the results
# Loop through each dependent variable
for (dep_var in dep_vars) {
# Construct the formula
formula <- as.formula(paste(dep_var, "~ high_jumbo_assets * I(qtrs %in% 0:4) + high_jumbo_assets * I(qtrs >4 ) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD"))
lower_bound <- quantile(upbr_merged[[dep_var]], probs = 0.01, na.rm = TRUE)
upper_bound <- quantile(upbr_merged[[dep_var]], probs = 0.99, na.rm = TRUE)
filtered_data <- upbr_merged %>%
filter((.data[[dep_var]] > lower_bound & .data[[dep_var]] < upper_bound) & qtrs <= 8 & qtrs >= -12)
# Run the regression
reg_result <- felm(formula, data = filtered_data)
# Store the result in the list
results[[dep_var]] <- reg_result
}
# return(results)
stargazer(results,type="text",no.space = T,omit.stat = "ser", add.lines = list(c("Bank FE",rep("Y",length(dep_vars))),c("Quarter",rep("Y",length(dep_vars)))))
}
library(gridExtra)
plot_bank_fe_regression <- function(dep_vars,min_qt,max_qt,sample_frac) {
g <- list()
i=0
for(dep_var_name in dep_vars) {
i=i+1
formula <- as.formula(paste(dep_var_name, "~ high_jumbo_assets * factor(qtrs) + log(total_assets) + equity_assets | IDRSSD + data_period | 0 | IDRSSD"))
lower_bound <- quantile(upbr_merged[[dep_var_name]], probs = 0.05, na.rm = TRUE)
upper_bound <- quantile(upbr_merged[[dep_var_name]], probs = 0.95, na.rm = TRUE)
filtered_data <- upbr_merged %>%
filter((.data[[dep_var_name]] > lower_bound & .data[[dep_var_name]] < upper_bound) & qtrs <= max_qt & qtrs >= min_qt)
rssd_list <- unique(filtered_data$IDRSSD)
rssd_sample <- sample(rssd_list,size=length(rssd_list)*sample_frac,replace = F)
g[[i]] <- coef_plot_1reg_10ci(felm(formula, data = filtered_data[IDRSSD %in% rssd_sample]), "high_jumbo_assets:factor(qtrs)", min_qt) + ggtitle(dep_var_name)
}
return(do.call(grid.arrange,g))
}
Assets
dependent_vars <- c("total_loans_assets","total_securities_assets",
"re_loans_sfr_assets","re_loans_other_assets",
"individual_loans_assets","ci_assets","cre_assets")
# ,"cash_assets","lns_securities_over_15_pct_of_assets"
table_bank_fe_regression(dependent_vars,-12,8)
##
## ===============================================================================================================================================================
## Dependent variable:
## ---------------------------------------------------------------------------------------------------------------------------------
## total_loans_assets total_securities_assets re_loans_sfr_assets re_loans_other_assets individual_loans_assets ci_assets cre_assets
## (1) (2) (3) (4) (5) (6) (7)
## ---------------------------------------------------------------------------------------------------------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000) (0.000) (0.000) (0.000) (0.000) (0.000)
## I(qtrs 0:4)
## (0.000) (0.000) (0.000) (0.000) (0.000) (0.000) (0.000)
## I(qtrs > 4)
## (0.000) (0.000) (0.000) (0.000) (0.000) (0.000) (0.000)
## log(total_assets) -5.133*** 1.835*** -2.561*** -1.047*** -0.093 -0.580** -1.236***
## (0.506) (0.598) (0.393) (0.193) (0.126) (0.261) (0.422)
## equity_assets -0.056 0.156** -0.127** 0.005 -0.017 0.004 0.062
## (0.072) (0.065) (0.053) (0.020) (0.014) (0.025) (0.046)
## high_jumbo_assets:I(qtrs 0:4) -0.160 0.502*** 0.293* 0.071 -0.101** -0.164* -0.101
## (0.188) (0.162) (0.156) (0.086) (0.042) (0.090) (0.141)
## high_jumbo_assets:I(qtrs > 4) -0.935*** 0.997*** -0.192 -0.082 -0.125** -0.163 -0.248
## (0.249) (0.219) (0.206) (0.114) (0.057) (0.121) (0.191)
## ---------------------------------------------------------------------------------------------------------------------------------------------------------------
## Bank FE Y Y Y Y Y Y Y
## Quarter Y Y Y Y Y Y Y
## Observations 72,195 71,460 72,233 72,231 71,988 71,256 72,174
## R2 0.919 0.934 0.967 0.939 0.934 0.931 0.957
## Adjusted R2 0.915 0.930 0.966 0.936 0.930 0.927 0.954
## ===============================================================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
plot_bank_fe_regression(dependent_vars,-12,8,1)
Liabilities
dependent_vars <- c("deposits_assets","core_deposits_assets","noncore_deposits_assets","brokered_deposits_assets")
table_bank_fe_regression(dependent_vars,-12,8)
##
## ===================================================================================================================
## Dependent variable:
## -------------------------------------------------------------------------------------
## deposits_assets core_deposits_assets noncore_deposits_assets brokered_deposits_assets
## (1) (2) (3) (4)
## -------------------------------------------------------------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs 0:4)
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs > 4)
## (0.000) (0.000) (0.000) (0.000)
## log(total_assets) -1.070*** -2.915*** 1.529*** 1.071***
## (0.237) (0.437) (0.367) (0.411)
## equity_assets -0.708*** -0.501*** -0.208*** -0.295***
## (0.026) (0.043) (0.036) (0.047)
## high_jumbo_assets:I(qtrs 0:4) -0.169 -0.615*** 0.393*** 0.290*
## (0.106) (0.165) (0.129) (0.168)
## high_jumbo_assets:I(qtrs > 4) 0.120 -0.685*** 0.775*** 0.422*
## (0.133) (0.228) (0.186) (0.255)
## -------------------------------------------------------------------------------------------------------------------
## Bank FE Y Y Y Y
## Quarter Y Y Y Y
## Observations 72,771 72,687 72,203 36,461
## R2 0.857 0.870 0.822 0.774
## Adjusted R2 0.849 0.863 0.812 0.757
## ===================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
plot_bank_fe_regression(dependent_vars,-12,8,1)
Capital
dependent_vars <- c("tier_1_assets","total_capital_ratio")
table_bank_fe_regression(dependent_vars,-12,8)
##
## ===============================================================
## Dependent variable:
## ---------------------------------
## tier_1_assets total_capital_ratio
## (1) (2)
## ---------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000)
## I(qtrs 0:4)
## (0.000) (0.000)
## I(qtrs > 4)
## (0.000) (0.000)
## log(total_assets) -1.452*** -0.895***
## (0.180) (0.174)
## equity_assets 0.727*** 1.021***
## (0.021) (0.030)
## high_jumbo_assets:I(qtrs 0:4) -0.031 0.038
## (0.026) (0.064)
## high_jumbo_assets:I(qtrs > 4) 0.111*** 0.251***
## (0.033) (0.080)
## ---------------------------------------------------------------
## Bank FE Y Y
## Quarter Y Y
## Observations 72,407 72,497
## R2 0.963 0.948
## Adjusted R2 0.961 0.945
## ===============================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
plot_bank_fe_regression(dependent_vars,-12,8,1)
Asset Quality
dependent_vars <- c("gross_loans_30_89_past_due_pct","gross_loans_90_past_due_pct","provisions_to_assets","mtg_delinq_pct_2")
table_bank_fe_regression(dependent_vars,-12,8)
##
## ==============================================================================================================================
## Dependent variable:
## ------------------------------------------------------------------------------------------------
## gross_loans_30_89_past_due_pct gross_loans_90_past_due_pct provisions_to_assets mtg_delinq_pct_2
## (1) (2) (3) (4)
## ------------------------------------------------------------------------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs 0:4)
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs > 4)
## (0.000) (0.000) (0.000) (0.000)
## log(total_assets) 0.083*** 0.129** 0.068*** 0.250**
## (0.029) (0.065) (0.010) (0.114)
## equity_assets 0.014*** -0.011 -0.001 0.027**
## (0.004) (0.010) (0.001) (0.014)
## high_jumbo_assets:I(qtrs 0:4) 0.021 0.054* -0.003 0.101**
## (0.014) (0.031) (0.004) (0.049)
## high_jumbo_assets:I(qtrs > 4) 0.059*** 0.115*** -0.013** 0.189***
## (0.018) (0.039) (0.006) (0.059)
## ------------------------------------------------------------------------------------------------------------------------------
## Bank FE Y Y Y Y
## Quarter Y Y Y Y
## Observations 66,799 68,054 72,516 65,231
## R2 0.604 0.684 0.489 0.708
## Adjusted R2 0.579 0.664 0.459 0.689
## ==============================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
plot_bank_fe_regression(dependent_vars,-12,8,1)
Profitability
dependent_vars <- c("roa","roe","nii_assets","interest_expense_assets_1","interest_income_assets")
table_bank_fe_regression(dependent_vars,-12,8)
##
## =============================================================================================================
## Dependent variable:
## -------------------------------------------------------------------------------
## roa roe nii_assets interest_expense_assets_1 interest_income_assets
## (1) (2) (3) (4) (5)
## -------------------------------------------------------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000) (0.000) (0.000) (0.000)
## I(qtrs 0:4)
## (0.000) (0.000) (0.000) (0.000) (0.000)
## I(qtrs > 4)
## (0.000) (0.000) (0.000) (0.000) (0.000)
## log(total_assets) 0.061** 0.370 -0.292*** 0.220*** 0.060**
## (0.030) (0.284) (0.028) (0.019) (0.030)
## equity_assets 0.023*** -0.163*** 0.020*** -0.011*** 0.017***
## (0.004) (0.035) (0.004) (0.002) (0.004)
## high_jumbo_assets:I(qtrs 0:4) -0.038*** -0.236** -0.028*** 0.031*** 0.007
## (0.012) (0.118) (0.010) (0.005) (0.010)
## high_jumbo_assets:I(qtrs > 4) -0.052*** -0.534*** -0.086*** 0.087*** -0.005
## (0.015) (0.157) (0.015) (0.010) (0.014)
## -------------------------------------------------------------------------------------------------------------
## Bank FE Y Y Y Y Y
## Quarter Y Y Y Y Y
## Observations 72,530 72,844 72,563 72,923 72,723
## R2 0.661 0.753 0.882 0.891 0.884
## Adjusted R2 0.641 0.739 0.876 0.885 0.877
## =============================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
plot_bank_fe_regression(dependent_vars,-12,7,1)
Scale
dependent_vars <- c("asset_growth_quarterly","asset_growth_yearly","log_no_of_full_time_employees","log_assets_per_employee")
table_bank_fe_regression(dependent_vars,-12,8)
##
## ==============================================================================================================================
## Dependent variable:
## ------------------------------------------------------------------------------------------------
## asset_growth_quarterly asset_growth_yearly log_no_of_full_time_employees log_assets_per_employee
## (1) (2) (3) (4)
## ------------------------------------------------------------------------------------------------------------------------------
## high_jumbo_assets
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs 0:4)
## (0.000) (0.000) (0.000) (0.000)
## I(qtrs > 4)
## (0.000) (0.000) (0.000) (0.000)
## log(total_assets) 2.005*** 14.870*** 0.716*** 0.258***
## (0.194) (1.051) (0.030) (0.015)
## equity_assets -0.256*** -0.686*** 0.009*** -0.009***
## (0.029) (0.118) (0.002) (0.002)
## high_jumbo_assets:I(qtrs 0:4) -0.286*** -1.298*** -0.005 0.009**
## (0.070) (0.309) (0.005) (0.005)
## high_jumbo_assets:I(qtrs > 4) -0.604*** -2.946*** -0.015** 0.017***
## (0.088) (0.401) (0.007) (0.006)
## ------------------------------------------------------------------------------------------------------------------------------
## Bank FE Y Y Y Y
## Quarter Y Y Y Y
## Observations 72,551 72,686 72,294 72,478
## R2 0.241 0.478 0.994 0.958
## Adjusted R2 0.197 0.448 0.994 0.955
## ==============================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
plot_bank_fe_regression(dependent_vars,-12,8,1)
