Louisiana Banking Statistics
Department of Finance - Louisiana State University
2023-06-01
density_plot_options <- list(
theme_minimal(),
theme(axis.text.y = element_blank())
)la_banks <- fread("la_banks.csv")
# la_banks <- paste(unique(la_banks$rssd),collapse = ",")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"), 2020:2022), 1, paste, collapse="")
data_periods <- c(data_periods,"03312023") # replace 12312013 with post SVB call reports when available
call <- 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
UBPR2170 assets_total,
UBPRB559 assets_mbs,
UBPRB558 assets_treasury_agency,
UBPR2122 loans_total,
UBPR3123 allowance_loan_loss,
UBPRHR48 interest_on_domestic_deposits,
UBPR2200 deposits,
UBPRE209 deposit_growth,
UBPR1754 htm_cost,
UBPR0211 htm_cost_treasury,
UBPRB558 htm_cost_treasury_plus_agency,
UBPR1771 htm_value,
UBPR1772 afs_cost,
UBPR1773 afs_value,
UBPR3545 trading_account_assets,
UBPR2145 premises_fixed_assets,
UBPRD125 loans_commercial_ind,
UBPR1410 loans_real_estate,
UBPRB538 loans_consumer,
UBPRE630 roe,
UBPR3210 total_equity,
UBPRD486 leverage_ratio,
UBPRD487 tier_1_ratio,
UBPRD488 total_capital_ratio,
UBPRM011 nontransacion_deposit_pct,
UBPRE559 loans_securities_over_15_yrs,
UBPRE570 loans_securities_over_3_yrs,
UBPRE566 htm_assets_ubpr,
UBPRE088 efficiency_ratio,
UBPRE091 yeild_on_loans,
UBPR4074 net_int_income,
UBPR4107 total_int_income,
UBPR2150 other_real_estate_owned,
UBPR2130 unconsolidated_subsidiaries,
UBPR2143 intangible_assets,
UBPRB993 fed_funds_purchased,
UBPRB995 repo_liabilities,
UBPR3548 trading_liabilities,
UBPR3190 other_borrowed_money,
UBPR3200 subordinated_debt_debentures,
UBPR2930 other_liabilties,
UBPRA549+UBPRA555+UBPRA564+UBPRA570 assets_maturity_less_than_3_months,
UBPRA550+UBPRA556+UBPRA248+UBPRA565+UBPRA571+UBPRA247 assets_maturity_3_mn_1yr,
UBPRA551+UBPRA557+UBPRA561+UBPRA566+UBPRA572 assets_maturity_1_to_3_yr,
UBPRA552+UBPRA558+UBPRA562+UBPRA567+UBPRA573 assets_maturity_3_to_5_yr,
UBPRA553+UBPRA559+UBPRA568+UBPRA574 assets_maturity_5_to_15_yr,
UBPRA553 govt_agency_maturity_5_to_15_yr,
UBPRA559 mbs_maturity_5_to_15_yr,
UBPRA564 re_loans_maturity_less_than_3_months,
UBPRA570 other_loans_maturity_less_than_3_months,
UBPRA565 re_loans_maturity_3_mn_1yr,
UBPRA571 other_loans_maturity_3_mn_1yr,
UBPRA566 re_loans_maturity_1_to_3_yr,
UBPRA572 other_loans_maturity_1_to_3_yr,
UBPRA567 re_loans_maturity_3_to_5_yr,
UBPRA573 other_loans_maturity_3_to_5_yr,
UBPRA568 re_loans_maturity_5_to_15_yr,
UBPRA574 other_loans_maturity_5_to_15_yr,
UBPRA554+UBPRA560+UBPRA569+UBPRA575 assets_maturity_more_than_15_yr,
UBPRA554 govt_agency_maturity_more_than_15_yr,
UBPRA560 mbs_maturity_more_than_15_yr,
UBPRA569 re_loans_maturity_more_than_15_yr,
UBPRA575 other_loans_maturity_more_than_15_yr,
UBPR0081+UBPR0071 cash,
UBPR4340 net_income,
IDRSSD
from ubpr_",ubpr_yr,"
where
data_period=",as.numeric(ubpr_dp)," "))
call_1 <- dbGetQuery(con_call,paste0("select
RCON2200 deposits_domestic,
RCON2385 non_transaction_deposits,
RCONF049 deposits_domestic_insured,
RCONF051 deposits_domestic_uninsured,
RCONF050 deposits_domestic_no_insured,
RCONF052 deposits_domestic_no_uninsured,
RCON6631 deposits_domestic_non_interest,
RCON1797+RCON5367+RCON5368 secured_by_real_estate_loans,
RCON1460+RCONF160+RCONF161 cre_loans,
RCONF160+RCONF161+RCONF159 secured_by_nonresidential_real_estate,
RCONB538+RCONB539+RCONK137+RCONK207 consumer_loans,
IDRSSD
from call_1_",dp," "))
call_2 <- dbGetQuery(con_call,paste0("select
RCFN2200 deposits_foreign,
FDIC_Certificate_Number,
Financial_Institution_Name,
Financial_Institution_State,
data_period,
IDRSSD
from call_2_",dp,""))
call_2022 <- merge(call_1,call_2,by="IDRSSD")
call_2022 <- merge(call_2022,ubpr_data,by="IDRSSD")
call_2022 <- data.table(call_2022)
call_2022[,deposits_foreign:=ifelse(is.na(deposits_foreign),0,deposits_foreign)]
call_2022[,deposits:=deposits_domestic+deposits_foreign]
call[[i]] <- call_2022
i=i+1
}
call <- rbindlist(call,fill=T)
#### comment the following line when 03312023 data available
# call[,data_period:=ifelse(data_period=="12312013","03312023",data_period)]
call[,year:=as.numeric(substr(data_period,5,8))]
call[,htm_assets:=htm_cost/assets_total]
call[,uninsured_dep_frac:=deposits_domestic_uninsured/assets_total]
call[,unrealized_loss_htm:=htm_cost-htm_value]
call[,unrealized_loss_htm_equity:=unrealized_loss_htm*100/total_equity]
call[,deposits_assets:=deposits/assets_total]
call[,wa_maturiy_assets:=(assets_maturity_less_than_3_months*0.25+assets_maturity_3_mn_1yr*0.75+assets_maturity_1_to_3_yr*2+assets_maturity_3_to_5_yr*4+assets_maturity_5_to_15_yr*10+assets_maturity_more_than_15_yr*20)/(cash+assets_maturity_less_than_3_months+assets_maturity_3_mn_1yr+assets_maturity_1_to_3_yr+assets_maturity_3_to_5_yr+assets_maturity_5_to_15_yr+assets_maturity_more_than_15_yr)]
call[,date:=as.Date(data_period,"%m%d%Y")]
call[,unins_deposits_assets:=deposits_domestic_uninsured/assets_total]
dbDisconnect(con_call)
dbDisconnect(con_ubpr)
call[,loans_securities_over_15_yrs_bin:=floor(loans_securities_over_15_yrs/10)*10]
call[,htm_assets_ubpr_bin:=floor(htm_assets_ubpr/10)*10]
call[,unins_deposits_assets_bin:=floor(unins_deposits_assets*10)*10]
call[,state:=ifelse(IDRSSD %in% unique(la_banks$rssd),"Louisiana","Other states")]
setorder(call,-state)
call[,unrealized_loss_estimated:= (assets_maturity_3_to_5_yr*2+assets_maturity_5_to_15_yr*3+assets_maturity_more_than_15_yr*7)*4/100]
call[,unrealized_loss_equity:=unrealized_loss_estimated*100/total_equity]
call[,unrealized_loss_equity:=ifelse(unrealized_loss_equity<0,0,unrealized_loss_equity)]
call[,unrealized_loss_equity:=ifelse(unrealized_loss_equity>200,200,unrealized_loss_equity)]
call[,unrealized_loss_equity_bin:=floor(unrealized_loss_equity/10)*10]
call[,unrealized_loss_htm_equity_bin:=floor(unrealized_loss_htm_equity/10)*10]
deposit_betas <- fread("../Post SVB/RN/deposit_betas_pschnabl.csv")
deposit_betas <- deposit_betas[,c("rssdid","expbeta","depexpbeta")]
call <- merge(call,deposit_betas,by.x ="IDRSSD",by.y="rssdid",all.x=T)
call[,other_loans_matuity_less_than_5_yrs_assets:= (other_loans_maturity_1_to_3_yr+other_loans_maturity_3_mn_1yr+other_loans_maturity_3_to_5_yr)/assets_total]
call[,other_loans_matuity_less_than_5_yrs_assets:=other_loans_matuity_less_than_5_yrs_assets*100]
call[,other_loans_matuity_less_than_5_yrs_assets_bin:=floor(other_loans_matuity_less_than_5_yrs_assets/10)*10]
call[,cre_assets:=cre_loans*100/assets_total]
call[,cre_assets_bin:=floor(cre_assets/10)*10]1 Loans/Securities over 15 years/Assets (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,loans_securities_over_15_yrs_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=loans_securities_over_15_yrs_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31"], aes(loans_securities_over_15_yrs,y=..scaled..*25000,fill=state),bw=4,alpha=0.4)+
geom_jitter(data=df[loans_securities_over_15_yrs_bin>=0], aes(loans_securities_over_15_yrs_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Loans/Securities over 15 years/Assets (%) on 2023-03-31",y="")
2 HTM/Assets (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,htm_assets_ubpr_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=htm_assets_ubpr_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31"], aes(htm_assets_ubpr,y=..scaled..*35000,fill=state),bw=4,alpha=0.4)+
geom_jitter(data=df[htm_assets_ubpr_bin>=0], aes(htm_assets_ubpr_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="HTM/Assets (%) on 2023-03-31",y="")
3 Unrealized Estimated Loss/Equity (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,unrealized_loss_equity_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=unrealized_loss_equity_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31" & unrealized_loss_equity_bin<200], aes(unrealized_loss_equity,y=..scaled..*9000,fill=state),bw=10,alpha=0.4)+
geom_jitter(data=df[unrealized_loss_equity_bin>=0 & unrealized_loss_equity_bin<200], aes(unrealized_loss_equity_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Unrealized **Estimated** Loss/Equity (%) on 2023-03-31",y="")
4 Unrealized Loss (HTM)/Equity (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,unrealized_loss_htm_equity_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=unrealized_loss_htm_equity_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31" & unrealized_loss_htm_equity_bin<200 & unrealized_loss_htm_equity_bin>0], aes(unrealized_loss_htm_equity,y=..scaled..*31000,fill=state),bw=10,alpha=0.4)+
geom_jitter(data=df[unrealized_loss_htm_equity_bin>=0 & unrealized_loss_htm_equity_bin<200], aes(unrealized_loss_htm_equity_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Unrealized Loss (HTM)/Equity (%) on 2023-03-31",y="")
5 Uninsured Deposits/Assets (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,unins_deposits_assets_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=unins_deposits_assets_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31"], aes(unins_deposits_assets*100,y=..scaled..*15000,fill=state),bw=4,alpha=0.4)+
geom_jitter(data=df[unins_deposits_assets_bin>=0], aes(unins_deposits_assets_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Uninsured Deposits/Assets (%) on 2023-03-31",y="")
6 Unrealized Loss/Equity vs. Uninsured Deposits/Assets (%) on 2023-03-31
ggplot() +
geom_point(data= call[date=="2023-03-31" & state=="Louisiana" & unrealized_loss_equity<200],
aes(x=unrealized_loss_equity,y=unins_deposits_assets*100,size=assets_total),alpha=0.6,color="purple4")+
theme_minimal()+
theme(legend.position = "none")+
labs(x="Unrealized **Estimated** Loss/Equity (%)",y="Uninsured Deposits/Assets (%)")
ggplot() +
geom_point(data= call[date=="2023-03-31" & state=="Louisiana" & unrealized_loss_equity<200],
aes(x=unrealized_loss_equity,y=unins_deposits_assets*100,size=assets_total/1e3,color=depexpbeta),alpha=0.9)+
scale_size_continuous(guide = guide_legend(title = "Bank size ($ mn)")) +
scale_color_gradient(guide = guide_colorbar(title = "Deposit beta"),low = "green4", high = "red3") +
theme_minimal()+
labs(x="Unrealized **Estimated** Loss/Equity (%)",y="Uninsured Deposits/Assets (%)")
7 CRE Loans/Assets (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,cre_assets_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=cre_assets_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31"], aes(cre_assets,y=..scaled..*14000,fill=state),bw=4,alpha=0.4)+
geom_jitter(data=df[cre_assets_bin>=0], aes(cre_assets_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="CRE Loans/Assets (%) on 2023-03-31",y="")
8 Other Loans less than 5 year maturity/Assets (%) on 2023-03-31
df <- call[date=="2023-03-31" & IDRSSD %in% unique(la_banks$rssd)]
setorder(df,other_loans_matuity_less_than_5_yrs_assets_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=other_loans_matuity_less_than_5_yrs_assets_bin]
ggplot() +
geom_density(data= call[date=="2023-03-31"], aes(other_loans_matuity_less_than_5_yrs_assets,y=..scaled..*16000,fill=state),bw=4,alpha=0.4)+
geom_jitter(data=df[other_loans_matuity_less_than_5_yrs_assets_bin>=0], aes(other_loans_matuity_less_than_5_yrs_assets_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Other Loans less than 5 year maturity/Assets (%) on 2023-03-31",y="")
y_vars <- c("deposits","deposits_domestic_insured","deposits_domestic_uninsured") #"deposits_domestic_uninsured","cash"
pre_sample <- call[data_period=="12312022"]
post_sample <- call[data_period=="03312023"]
names(post_sample) <- paste0(names(post_sample),"_post")
setnames(post_sample,"IDRSSD_post","IDRSSD")
delta <- merge(pre_sample,post_sample,by="IDRSSD")
season_cols <- c("IDRSSD",y_vars)
season_2021 <- call[data_period=="03312021",..season_cols]
temp <- call[data_period=="12312020",..season_cols]
names(temp) <- paste0(names(temp),"_dec")
setnames(temp,"IDRSSD_dec","IDRSSD")
season_2021 <- merge(season_2021,temp)
season_2022 <- call[data_period=="03312022",..season_cols]
temp <- call[data_period=="12312021",..season_cols]
names(temp) <- paste0(names(temp),"_dec")
setnames(temp,"IDRSSD_dec","IDRSSD")
season_2022 <- merge(season_2022,temp)
for(y_var in y_vars) {
eval(parse(text=paste0("delta[,chg_",y_var,":=",y_var,"_post/(",y_var,"+1)-1]")))
eval(parse(text=paste0("season_2021[,prev_2021_",y_var,":=",y_var,"/(",y_var,"_dec+1)-1]")))
eval(parse(text=paste0("season_2022[,prev_2022_",y_var,":=",y_var,"/(",y_var,"_dec+1)-1]")))
}
delta[,chg_deposits:=chg_deposits*100]
delta[,chg_deposits_bin:=floor(chg_deposits/5)*5]
delta[,chg_deposits_domestic_uninsured:=chg_deposits_domestic_uninsured*100]
delta[,chg_deposits_domestic_uninsured_bin:=floor(chg_deposits_domestic_uninsured/5)*5]
delta[,chg_deposits_domestic_insured:=chg_deposits_domestic_insured*100]
delta[,chg_deposits_domestic_insured_bin:=floor(chg_deposits_domestic_insured/5)*5]9 Percentage change in total deposits from Q4 2022 to Q1 2023.
Red line is the mean change.
df <- delta[ IDRSSD %in% unique(la_banks$rssd)]
setorder(df,chg_deposits_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=chg_deposits_bin]
ggplot() +
geom_density(data= df, aes(chg_deposits,y=..scaled..*18000),bw=3,alpha=0.3,fill="purple4")+
geom_jitter(data=df, aes(chg_deposits_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
geom_vline(xintercept = mean(delta[ IDRSSD %in% unique(la_banks$rssd)]$chg_deposits),size=1,color="darkred")+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Percentage change in total deposits from Q4 2022 to Q1 2023",y="")
10 Percentage change in uninsured deposits from Q4 2022 to Q1 2023.
Red line is the mean change.
df <- delta[ IDRSSD %in% unique(la_banks$rssd)]
setorder(df,chg_deposits_domestic_uninsured_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=chg_deposits_domestic_uninsured_bin]
ggplot() +
geom_density(data= df, aes(chg_deposits_domestic_uninsured,y=..scaled..*13000),bw=3,alpha=0.3,fill="purple4")+
geom_jitter(data=df, aes(chg_deposits_domestic_uninsured_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
geom_vline(xintercept = mean(delta[ IDRSSD %in% unique(la_banks$rssd)]$chg_deposits_domestic_uninsured),size=1,color="darkred")+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Percentage change in uninsured deposits from Q4 2022 to Q1 2023",y="")
11 Percentage change in insured deposits from Q4 2022 to Q1 2023.
df <- delta[ IDRSSD %in% unique(la_banks$rssd)]
setorder(df,chg_deposits_domestic_insured_bin,-assets_total)
df[,y:=cumsum(sqrt(assets_total)/2),by=chg_deposits_domestic_insured_bin]
ggplot() +
geom_density(data= df, aes(chg_deposits_domestic_insured,y=..scaled..*21000),bw=3,alpha=0.3,fill="purple4")+
geom_jitter(data=df, aes(chg_deposits_domestic_insured_bin, y,size=assets_total*4),color="purple4",alpha=0.7,width =2)+
geom_vline(xintercept = mean(delta[ IDRSSD %in% unique(la_banks$rssd)]$chg_deposits_domestic_insured),size=1,color="darkred")+
scale_fill_manual(values=c("purple4","wheat"))+
guides(size="none")+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank(),axis.text.y = element_blank())+
labs(x="Percentage change in insured deposits from Q4 2022 to Q1 2023",y="")