# created by bb_bank_data_prelim_03.RMD
parent_sub <- fread("ticker_parentRSSD_subRSSD_2.csv")
bhc_ids <- parent_sub[!duplicated(parent_sub[,c("ID_RSSD_PARENT","ticker")])]
file_path <-"bb_bank_with_BAC.xlsx"
sheets <- excel_sheets(file_path)
sheets <- sheets[sheets !="tickers"]
sheet_list <- list()
sheet_list <- lapply(sheets, function(x) read_excel(file_path, sheet = x))
data_list <- list()
for(i in 1:length(sheet_list)) {
tmp <- data.table(sheet_list[[i]])
var_name <- as.character(tmp[1,2])
var_name <- substr(var_name, 1, regexpr("\\(FA_PERIOD_REFERENCE", var_name)-1)
# print(paste(i,var_name))
tmp <- tmp[-1]
names(tmp)[1] <- "date"
tmp <- tmp[!is.na(date)]
tmp <- melt(tmp,id.vars = "date")
tmp <- data.table(tmp)
tmp[,value:=as.numeric(value)]
tmp <- tmp[!is.na(value)]
tmp[,date:=as.Date(as.numeric(date),origin="1899-12-30")]
tmp[,date:=ceiling_date(date, "quarter")-1]
tmp[,var_name:=var_name]
data_list[[i]] <- copy(tmp)
}
data_list <- do.call(rbind,data_list)
data_list <- dcast(data_list,date+variable~var_name,value.var = "value",fun.aggregate = sum)
data_list <- data.table(data_list)
data_list[,variable:=as.character(variable)]
setnames(data_list,"variable","ticker")
data_list[,ticker:=substr(ticker, 1, regexpr(" US Equity", ticker)-1)]
data_list <- data_list[ticker!=""]
data_list <- data_list[date<="2022-12-31"]
data_periods <- apply(expand.grid(2017:2022,c("0331","0630","0930","1231")), 1, paste, collapse="")
bhc_y9c <- list()
i <- 1
for(dp in data_periods) {
temp <- fread(paste0("C:/Users/dratnadiwakara2/Documents/OneDrive - Louisiana State University/Raw Data/Call Reports/Bank Holding Companies/BHCF_data/BHCF",dp,".txt"),sep="^",
select =c("RSSD9001","BHCK2170","BHCK0081","BHCK0395","BHCK0397","BHCK1754","BHCK1773",
"BHDMB987","BHCKB989","BHCK5369","BHCKB529","BHCK3545","BHCK2145","BHCK2150","BHCK2130",
"BHCK3656","BHCK2160",
"BHDM6631","BHDM6636","BHFN6631","BHFN6636",
"BHDMB993","BHCKB995","BHCK3548",
"BHCK3190","BHCK4062","BHCKC699","BHCK2750",
"BHCK3283","BHCK3230","BHCK3240","BHCK3247","BHCKB530","BHCKA130","BHCKG105",
"BHCK1410","BHCK1771","BHCKB558","BHCKB559","BHCKB560","BHCKF161","BHCK4340",
"BHCK4107","BHCK4074") )
temp[,report_date:=dp]
bhc_y9c[[i]] <- temp
i=i+1
}
bhc_y9c <- do.call(rbind,bhc_y9c)
names(bhc_y9c) <- c("BHC_RSSD","bs_assets_total","bs_cash","bs_ib_assets_domestic","bs_ib_assets_foreign","bs_htm","bs_afs",
"bs_fed_fund_assets","bs_repo_assets","bs_loans_for_sale","bs_loans_total","bs_trading","bs_fixed_assets","bs_other_reo","bs_subsidiaries",
"bs_re_ventures","bs_other_assets",
"bs_deposits_domestic_non_int","bs_deposits_domestic_int","bs_deposits_foreign_non_int","bs_deposits_foreign_int",
"bs_fed_fund_liabilities","bs_repo_liabilities","bs_trading_liabilities",
"bs_other_borrowed_money","bs_subordinated_debt","bs_subordinated_notes","bs_other_liabilities",
"bs_preferred_stock","bs_equity_par","bs_equity_surplus","bs_retained_earnings","bs_other_comp_income","bs_other_equity","bs_equity_total",
"bs_loans_re","hcb_htm_value","bs_treasury_govt","bs_mbs", "bs_other_securities","bs_loans_cre","is_net_income",
"is_interest_income","is_net_interest_income",
"report_date"
)
bhc_y9c[,date:=as.Date(report_date,"%Y%m%d")]
bhc_y9c[,year:=year(date)]
bhc_y9c[,yr2022:=ifelse(date>"2022-01-30",1,0)]
bhc_y9c[,bs_liabilities_total:=bs_assets_total-bs_equity_total]
bhc_y9c[,assets_equity:=bs_assets_total/bs_equity_total]
bhc_y9c[,liabilities_equity:=bs_liabilities_total/bs_equity_total]
bhc_y9c <- merge(bhc_y9c,bhc_ids[,c("ID_RSSD_PARENT","ticker")],by.x="BHC_RSSD",by.y="ID_RSSD_PARENT")
bhc_y9c <- merge(bhc_y9c,data_list,by=c("ticker","date"))
bhc_y9c[,bs_total_deposits:=bs_deposits_domestic_int+bs_deposits_domestic_non_int+bs_deposits_foreign_int+bs_deposits_foreign_non_int]
# t(bhc_vars[BHC_RSSD==1073757])
bhc_y9c[,tier_1_share:=BS_TIER1_CAPITAL/(bs_equity_total*1000)]
bhc_y9c[,tier_1_assets:=BS_TIER1_CAPITAL/(bs_assets_total*1000)]
setorder(bhc_y9c,BHC_RSSD,date)
bhc_y9c[, lag_bs_total_deposits := shift(bs_total_deposits, n = 4, type = "lag"), by = .(BHC_RSSD)]
bhc_y9c[, lag_bs_assets_total := shift(bs_assets_total, n = 4, type = "lag"), by = .(BHC_RSSD)]
bhc_y9c[,asset_growth:=bs_assets_total/lag_bs_assets_total-1]
bhc_y9c[,deposit_growth:=bs_total_deposits/lag_bs_total_deposits-1]
bb_tickers <- data_list[,c("ticker")]
bb_tickers <- bb_tickers[!duplicated(bb_tickers)]
bb_tickers[,bb:=1]
bhc_tickers <- bhc_y9c[,c("ticker")]
bhc_tickers <- bhc_tickers[!duplicated(bhc_tickers)]
bhc_tickers[,bhc:=1]
bb_tickers <- merge(bb_tickers,bhc_tickers,by="ticker",all.x = T)
nrow(bb_tickers[is.na(bhc)]) #10 banks don't have Y9C Data
## [1] 10
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"), 2017:2022), 1, paste, collapse="")
# data_periods <- apply(expand.grid(c("1231"), 2022), 1, paste, collapse="")
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+RCON1460+RCONF158 secured_by_residential_real_estate,
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[,c("cash_2","cash_1","assets_maturity_more_than_15_yr_1","assets_maturity_5_to_15_yr_1","assets_maturity_3_to_5_yr_1","assets_maturity_1_to_3_yr_1","assets_maturity_3_mn_1yr_1","assets_maturity_3_mn_1yr_1","assets_maturity_less_than_3_months_1"):=list(NULL)]
call[[i]] <- call_2022
i=i+1
}
call <- rbindlist(call,fill=T)
dbDisconnect(con_call)
dbDisconnect(con_ubpr)
call[,date:=as.Date(data_period,"%m%d%Y")]
call <- merge(call,parent_sub,by.x="IDRSSD",by.y="ID_RSSD_OFFSPRING")
call[,deposits_uninsured_assets:=deposits_domestic_uninsured/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[,maturity_5_to_15_yr_assets:=assets_maturity_5_to_15_yr/assets_total]
# call[,maturity_more_than_15_yr_assets:=assets_maturity_more_than_15_yr/assets_total]
#
# call[,govt_agency_more_than_5_yr_assets:=(govt_agency_maturity_more_than_15_yr+govt_agency_maturity_5_to_15_yr)/assets_total]
# call[,mbs_more_than_5_yr_assets:=(mbs_maturity_more_than_15_yr+mbs_maturity_5_to_15_yr)/assets_total]
# call[,re_loans_more_than_5_yr_assets:=(re_loans_maturity_more_than_15_yr+re_loans_maturity_5_to_15_yr)/assets_total]
# call[,other_loans_more_than_5_yr_assets:=(other_loans_maturity_more_than_15_yr+other_loans_maturity_5_to_15_yr)/assets_total]
# call[,loans_5_to_15_yr:=(re_loans_maturity_more_than_15_yr+re_loans_maturity_5_to_15_yr)/assets_total]
# call[,other_loans_more_than_5_yr_assets:=(other_loans_maturity_more_than_15_yr+other_loans_maturity_5_to_15_yr)/assets_total]
deposit_betas <- fread("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)
f1 <- call
setorder(f1,IDRSSD,date)
f1[,year:=year(date)]
f1[, lag_net_int_income := shift(net_int_income, n = 1, type = "lag"), by = .(IDRSSD,year)]
f1[, lag_total_int_income := shift(total_int_income, n = 1, type = "lag"), by = .(IDRSSD,year)]
f1[,nii:=net_int_income-lag_net_int_income]
f1[,nii:=ifelse(is.na(nii),net_int_income,nii)]
f1[,nim:=nii*100/assets_total]
f1[,bank_size_category:=ifelse(assets_total>250e6,"4. more than 250b",
ifelse(assets_total>50e6,"3. 50b to 250b",
ifelse(assets_total>5e6,"2. 5b to 50b",
ifelse(assets_total>1e6,"1. 1b to 5b","0. less than 1b"))))]
f1 <- f1[date>"2018-12-31",.(nim=mean(nim,na.rm=T)),by=.(date,bank_size_category)]
ggplot(f1,aes(x=date,y=nim,color=bank_size_category))+geom_line()+geom_point()+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank())+
labs(y="Net interest income/Assets (%)",x="")
call_parent <- call[,.(assets_total=sum(assets_total,na.rm=T),
expbeta = mean(expbeta,na.rm=T),
depexpbeta = mean(depexpbeta,na.rm=T),
assets_mbs=sum(assets_mbs,na.rm=T),
assets_treasury_agency=sum(assets_treasury_agency,na.rm=T),
loans_total=sum(loans_total,na.rm=T),
deposits_total = sum(deposits,na.rm=T),
htm_cost = sum(htm_cost,na.rm=T),
htm_value = sum(htm_value,na.rm=T),
htm_cost_treasury = sum(htm_cost_treasury,na.rm = T),
htm_cost_treasury_plus_agency = sum(htm_cost_treasury_plus_agency,na.rm=T),
afs_cost = sum(afs_cost,na.rm=T),
afs_value = sum(afs_value,na.rm=T),
equity_total = sum(total_equity,na.rm=T),
trading_account_assets = sum(trading_account_assets,na.rm=T),
deposits_domestic = sum(deposits_domestic,na.rm=T),
deposits_domestic_uninsured = sum(deposits_domestic_uninsured,na.rm=T),
deposits_domestic_insured = sum(deposits_domestic_insured,na.rm=T),
non_transaction_deposits = sum(non_transaction_deposits,na.rm=T),
cash = sum(cash,na.rm=T),
loans_commercial_ind = sum(loans_commercial_ind,na.rm=T),
loans_real_estate = sum(loans_real_estate,na.rm=T),
loans_sfr = sum(secured_by_residential_real_estate,na.rm=T),
loans_cre = sum(secured_by_nonresidential_real_estate,na.rm=T),
loans_consumer = sum(consumer_loans,na.rm=T),
loans_securities_over_15_yrs=mean(loans_securities_over_15_yrs,na.rm=T),
loans_securities_over_3_yrs=mean(loans_securities_over_3_yrs,na.rm=T),
interest_on_domestic_deposits = mean(interest_on_domestic_deposits,na.rm=T),
htm_assets_ubpr=mean(htm_assets_ubpr,na.rm=T),
deposit_growth=mean(deposit_growth,na.rm=T),
premises_fixed_assets=sum(premises_fixed_assets,na.rm=T),
other_real_estate_owned = sum(other_real_estate_owned,na.rm = T),
unconsolidated_subsidiaries = sum(unconsolidated_subsidiaries,na.rm = T),
intangible_assets = sum(intangible_assets,na.rm=T),
deposits_foreign=sum(deposits_foreign,na.rm=T),
fed_funds_purchased=sum(fed_funds_purchased,na.rm=T),
repo_liabilities=sum(repo_liabilities,na.rm=T),
trading_liabilities=sum(trading_liabilities,na.rm=T),
other_borrowed_money=sum(other_borrowed_money,na.rm=T),
subordinated_debt_debentures=sum(subordinated_debt_debentures,na.rm=T),
other_liabilties=sum(other_liabilties,na.rm=T),
wa_maturiy_assets=mean(wa_maturiy_assets,na.rm=T),
re_loans_less_than_3_months_assets = sum(re_loans_maturity_less_than_3_months,na.rm=T)/sum(assets_total,na.rm=T),
other_loans_less_than_3_months_assets = sum(other_loans_maturity_less_than_3_months,na.rm=T)/sum(assets_total,na.rm=T),
re_loans_3_mn_1yr_assets = sum(re_loans_maturity_3_mn_1yr,na.rm=T)/sum(assets_total,na.rm=T),
re_loans_1_to_3_yr_assets = sum(re_loans_maturity_1_to_3_yr,na.rm=T)/sum(assets_total,na.rm=T),
re_loans_3_to_5_yr_assets = sum(re_loans_maturity_3_to_5_yr,na.rm=T)/sum(assets_total,na.rm=T),
re_loans_5_to_15_yr_assets = sum(re_loans_maturity_5_to_15_yr,na.rm=T)/sum(assets_total,na.rm=T),
re_loans_more_than_15_yr_assets = sum(re_loans_maturity_more_than_15_yr,na.rm=T)/sum(assets_total,na.rm=T),
other_loans_3_mn_1yr_assets = sum(other_loans_maturity_3_mn_1yr,na.rm=T)/sum(assets_total,na.rm=T),
other_loans_1_to_3_yr_assets = sum(other_loans_maturity_1_to_3_yr,na.rm=T)/sum(assets_total,na.rm=T),
other_loans_3_to_5_yr_assets = sum(other_loans_maturity_3_to_5_yr,na.rm=T)/sum(assets_total,na.rm=T),
other_loans_5_to_15_yr_assets = sum(other_loans_maturity_5_to_15_yr,na.rm=T)/sum(assets_total,na.rm=T),
other_loans_more_than_15_yr_assets = sum(other_loans_maturity_more_than_15_yr,na.rm=T)/sum(assets_total,na.rm=T),
tier_1_ratio=mean(tier_1_ratio,na.rm=T),
assets_maturity_3_to_5_yr = sum(assets_maturity_3_to_5_yr,na.rm=T),
assets_maturity_5_to_15_yr=sum(assets_maturity_5_to_15_yr,na.rm=T),
assets_maturity_more_than_15_yr=sum(assets_maturity_more_than_15_yr,na.rm=T)
),
by=.(ticker,date)]
call_parent[,deposits_domestic_insured_to_assets:=deposits_domestic_insured/assets_total]
call_parent[,deposits_domestic_uninsured_to_assets:=deposits_domestic_uninsured/assets_total]
# call_parent <- call_parent[,c("ticker","date","deposits_domestic_insured_to_assets","deposits_domestic_uninsured_to_assets","wa_maturiy_assets","re_loans_3_to_5_yr_assets","re_loans_5_to_15_yr_assets", "re_loans_more_than_15_yr_assets","other_loans_3_to_5_yr_assets","other_loans_5_to_15_yr_assets","other_loans_more_than_15_yr_assets","expbeta","depexpbeta")]
bhc_y9c <- merge(bhc_y9c,call_parent,by=c("ticker","date"),all.x=T)
crsp <- fread("crsp_price_shout_2016_2022.csv")
crsp[,date:=as.Date(date)]
crsp[,date:=ceiling_date(as.Date(date),unit="months")-days(1)]
crsp[,PRC:=abs(PRC)/CFACPR]
crsp[,SHROUT:=abs(SHROUT)/CFACSHR]
bhc_y9c <- merge(bhc_y9c,crsp[,c("TICKER","date","PRC","SHROUT")],by.x=c("ticker","date"),by.y=c("TICKER","date"),all.x=T)
bhc_y9c[,market_cap:=PRC*SHROUT]
bhc_y9c[,market_cap:=ifelse(is.na(market_cap),HISTORICAL_MARKET_CAP/1000,abs(market_cap))]
# montyly_price <- function(ticker) {
# tryCatch({
# t <- getSymbols(ticker, from = "2016-01-01", to = "2023-03-31", auto.assign = FALSE,periodicity="monthly")
# t <- t[,4]
# names(t) <- ticker
# return(t)
# },error=function(cond) {})
# }
#
# stock_prices <- lapply(unique(bhc_y9c$ticker),montyly_price)
# stock_prices <- do.call(cbind, stock_prices)
# stock_prices <- as.data.table(stock_prices)
# setnames(stock_prices,"index","date")
# stock_prices[,date:=ceiling_date(date,unit = "month")-days(1)]
#
# saveRDS(stock_prices,"bank_stock_prices_2016_2023_March.rds")
stock_prices <- readRDS("bank_stock_prices_2016_2023_March.rds")
stock_prices_copy <- copy(stock_prices)
stock_price_long <- melt(stock_prices,id.vars = "date")
stock_price_long <- data.table(stock_price_long)
stock_price_long[,ticker:=as.character(variable)]
stock_price_long[,stock_price:=value]
stock_price_long[,c("variable","value"):=list(NULL)]
bhc_y9c <- merge(bhc_y9c,stock_price_long,by=c("ticker","date"),all.x=T)
temp1 <- stock_prices[date=="2022-12-31"]
temp1[,date:=NULL]
temp1 <- melt(temp1)
setnames(temp1,"value","prc_20221231")
temp1 <- data.table(temp1)
temp1[,variable:=as.character(variable)]
temp3 <- stock_prices[date=="2023-03-31"]
temp3[,date:=NULL]
temp3 <- data.table(temp3)
temp3 <- melt(temp3)
setnames(temp3,"value","prc_20230331")
temp3 <- data.table(temp3)
temp3[,variable:=as.character(variable)]
march2023 <- bhc_y9c[date=="2022-12-31"]
march2023[,date:=as.Date("2023-03-31")]
march2023 <- merge(march2023,temp1,by.x="ticker",by.y="variable")
march2023 <- merge(march2023,temp3,by.x="ticker",by.y="variable")
march2023[,market_cap:=prc_20230331*market_cap/prc_20221231]
march2023[,c("prc_20230331","prc_20221231"):=list(NULL)]
bhc_y9c <- rbind(bhc_y9c[date<="2022-12-31"],march2023)
bhc_y9c[,htm_assets:=bs_htm/bs_assets_total]
bhc_y9c[,afs_assets:=bs_afs/bs_assets_total]
bhc_y9c[,trading_assets:=bs_trading/bs_assets_total]
bhc_y9c[,large_deposits_assets:=BS_LARGE_DPST/BS_TOT_ASSET]
bhc_y9c[,other_deposits_assets:=(bs_total_deposits*1000-BS_LARGE_DPST)/BS_TOT_ASSET]
bhc_y9c[,subordinated_debt_assets:= bs_subordinated_debt/bs_assets_total]
bhc_y9c[,total_deposit_assets:=bs_total_deposits/bs_assets_total]
bhc_y9c[,equity_assets:=bs_equity_total/bs_assets_total]
bhc_y9c[,loans_re_assets:=bs_loans_re/bs_assets_total]
bhc_y9c[,loans_other_assets:=(bs_loans_total-bs_loans_re)/bs_assets_total]
bhc_y9c[,unins_deposits_assets:=deposits_domestic_uninsured_to_assets]
bhc_y9c[,ins_deposits_assets:=deposits_domestic_insured_to_assets]
bhc_y9c[,unrealized_loss_htm:=hcb_htm_value-bs_htm]
bhc_y9c[,cre_assets:=bs_loans_cre/bs_assets_total]
bhc_y9c[,MV_e:=market_cap]#ifelse(!is.na(market_cap_20230503),market_cap_20230503,market_cap)]
bhc_y9c[,q_MV_L_A:=(MV_e+bs_liabilities_total)/bs_assets_total]
bhc_y9c[,q_MV_BV:=MV_e/bs_equity_total]
bhc_y9c[,ROE:=is_net_income/bs_equity_total]
bhc_y9c[,nim:=is_net_interest_income/is_interest_income]
temp_bhc_y9c <- copy(bhc_y9c)
bhc_y9c[,bank_size_cat:=ifelse(ticker %in% bhc_y9c[date=="2022-12-31" & BS_TOT_ASSET>250e9]$ticker,"3. more than 250b",
ifelse(ticker %in% bhc_y9c[date=="2022-12-31" & BS_TOT_ASSET>50e9]$ticker,"2. 50b to 250b","1. less than 50b"))]
fed_funds <- fread("FEDFUNDS.csv")
fed_funds[,date:=as.Date(date,"%m/%d/%Y")]
t <- fed_funds[date=="2022-12-31"]
t[,date:=as.Date("2023-03-31")]
t[,fed_funds_rate:=5]
fed_funds <- rbind(fed_funds,t)
bhc_y9c <- merge(bhc_y9c,fed_funds,by="date")
bhc_y9c[,unrealized_loss_estimated:= (assets_maturity_3_to_5_yr*3+assets_maturity_5_to_15_yr*5+assets_maturity_more_than_15_yr*10)*fed_funds_rate/100]
f2 <- bhc_y9c
f2[,market_cap_initial:=MV_e[date=="2019-03-31"],by=ticker]
f2[,mkt_val_change:=MV_e/market_cap_initial]
f2 <- f2[date>="2019-01-01" & is.finite(mkt_val_change),.(mkt_cap=mean(mkt_val_change,na.rm=T)),by=.(date,bank_size_cat)]
ggplot(f2,aes(x=date,y=mkt_cap,color=bank_size_cat))+geom_point()+geom_line()+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank())+
labs(y="Mean market cap change (Q1 2019=1)",x="")
q_summary <- bhc_y9c[,.(q=mean(q_MV_BV,na.rm=T),se=sd(q_MV_L_A,na.rm=T)),by=.(date,bank_size_cat)]
ggplot(q_summary,aes(x=date,y=q,color=bank_size_cat))+geom_point()+geom_line()+labs(x="",y="Price-to-book")+theme_minimal()+theme(legend.title = element_blank(),legend.position = "bottom")
g <- list()
mv_summary <- bhc_y9c[ date>"2019-01-01",
.(equity=sum(bs_equity_total,na.rm=T),
market_cap = sum(MV_e,na.rm=T),
unrealized_loss=sum(unrealized_loss_estimated,na.rm=T)),
by=date]
mv_summary_melt <- reshape2::melt(mv_summary, id.vars = "date")
g[[1]] <- ggplot(mv_summary_melt, aes(x = date, y = value/1e6, color = variable)) +
geom_line(size=1)+
geom_point(aes(shape=variable))+
theme_minimal()+
theme(legend.position = "none",legend.title = element_blank())+
#scale_color_manual(values=c("dodgerblue4","cornflowerblue","darkred"))+
labs(x="",y="$ billions")#+ggtitle("Panel A: All banks")
mv_summary <- bhc_y9c[ticker %in% bhc_y9c[date=="2022-12-31" & BS_TOT_ASSET>250e6]$ticker & date>"2019-01-01",
.(equity=sum(bs_equity_total,na.rm=T),
market_cap = sum(MV_e,na.rm=T),
unrealized_loss=sum(unrealized_loss_estimated,na.rm=T)),
by=date]
mv_summary_melt <- reshape2::melt(mv_summary, id.vars = "date")
g[[2]] <- ggplot(mv_summary_melt, aes(x = date, y = value/1e6, color = variable)) +
geom_line(size=1)+
geom_point(aes(shape=variable))+
theme_minimal()+
theme(legend.position = "none",legend.title = element_blank())+
#scale_color_manual(values=c("dodgerblue4","cornflowerblue","darkred"))+
labs(x="",y="")#+ggtitle("Panel B: Large banks (assets>= $ 100 billion)")
mv_summary <- bhc_y9c[ticker %in% bhc_y9c[date=="2022-12-31" & BS_TOT_ASSET<250e9 & BS_TOT_ASSET > 50e9]$ticker & date>"2019-01-01",
.(equity=sum(bs_equity_total,na.rm=T),
market_cap = sum(MV_e,na.rm=T),
unrealized_loss=sum(unrealized_loss_estimated,na.rm=T)),
by=date]
mv_summary_melt <- reshape2::melt(mv_summary, id.vars = "date")
g[[3]] <- ggplot(mv_summary_melt, aes(x = date, y = value/1e6, color = variable)) +
geom_line(size=1)+
geom_point(aes(shape=variable))+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank())+
#scale_color_manual(values=c("dodgerblue4","cornflowerblue","darkred"))+
labs(x="",y="$ billions")#+ggtitle("Panel C: Regional banks (assets < $ 100 bn and > $ 10 bn")
mv_summary <- bhc_y9c[ticker %in% bhc_y9c[date=="2022-12-31" & BS_TOT_ASSET <= 50e9]$ticker & date>"2019-01-01",
.(equity=sum(bs_equity_total,na.rm=T),
market_cap = sum(MV_e,na.rm=T),
unrealized_loss=sum(unrealized_loss_estimated,na.rm=T)),
by=date]
mv_summary_melt <- reshape2::melt(mv_summary, id.vars = "date")
g[[4]] <- ggplot(mv_summary_melt, aes(x = date, y = value/1e6, color = variable)) +
geom_line(size=1)+
geom_point(aes(shape=variable))+
theme_minimal()+
theme(legend.position = "bottom",legend.title = element_blank())+
#scale_color_manual(values=c("dodgerblue4","cornflowerblue","darkred"))+
labs(x="",y="")#+ggtitle("Panel D: Small banks (assets <= $ 10 bn)")
grid.arrange(g[[1]],g[[2]],g[[3]],g[[4]])
# destination = 'C:\\Users\\dratnadiwakara2\\Downloads\\mv_bv_ul.pdf'
# pdf(file=destination,width = 7,height = 5)
# for (i in 1:4) {
# print(g[[i]])
# }
# dev.off()
reg_sample <- bhc_y9c[ticker %in% bhc_y9c[date=="2022-12-31" & BS_TOT_ASSET<250e9 ]$ticker]
reg_sample[,year:=year(date)]
reg_sample[,unr_assets:=unrealized_loss_estimated/bs_assets_total]
reg_sample[,deposit_growth:=deposit_growth.x]
reg_sample[,re_loans_less_than_3_yr_assets:=re_loans_less_than_3_months_assets+re_loans_3_mn_1yr_assets+re_loans_1_to_3_yr_assets]
reg_sample[,qtrs:=((year(date) - 2021) * 4) + quarter(date) -4 ]
reg_sample[,qtrs:=as.factor(qtrs)]
reg_sample[,qtrs:=relevel(qtrs,ref = "0")]
cols <- c("q_MV_L_A","q_MV_BV","htm_assets","afs_assets","depexpbeta","unins_deposits_assets","tier_1_assets","year","bs_assets_total","ROE","deposit_growth","date","re_loans_less_than_3_yr_assets","re_loans_3_to_5_yr_assets","re_loans_5_to_15_yr_assets", "re_loans_more_than_15_yr_assets","other_loans_less_than_3_months_assets","other_loans_3_mn_1yr_assets","other_loans_1_to_3_yr_assets","other_loans_3_to_5_yr_assets","other_loans_5_to_15_yr_assets","other_loans_more_than_15_yr_assets","qtrs","ticker","DEPOSITS_TO_FUNDING")
reg_sample <- reg_sample[,..cols]
reg_sample <- reg_sample[complete.cases(reg_sample)]
reg_sample[,w_assets:=bs_assets_total/sum(bs_assets_total,na.rm = T),by=year]
stargazer(reg_sample[date=="2022-12-31"],summary.stat = c("mean","sd","p25","median","p75","N"),type="text")
##
## ==================================================================================================================
## Statistic Mean St. Dev. Pctl(25) Median Pctl(75) N
## ------------------------------------------------------------------------------------------------------------------
## q_MV_L_A 1.030 0.047 1.003 1.019 1.046 174
## q_MV_BV 1.325 0.523 1.027 1.210 1.461 174
## htm_assets 0.054 0.070 0.000 0.038 0.083 174
## afs_assets 0.151 0.091 0.091 0.129 0.189 174
## depexpbeta 0.391 0.094 0.336 0.380 0.447 174
## unins_deposits_assets 0.448 0.118 0.381 0.444 0.509 174
## tier_1_assets 0.086 0.030 0.084 0.091 0.101 174
## year 2,022.000 0.000 2,022 2,022 2,022 174
## bs_assets_total 26,098,620.000 43,995,603.000 5,154,224.000 9,686,066.000 23,606,877.000 174
## ROE 0.128 0.041 0.102 0.121 0.150 174
## deposit_growth 0.047 0.154 -0.040 0.014 0.084 174
## re_loans_less_than_3_yr_assets 0.019 0.019 0.007 0.015 0.026 174
## re_loans_3_to_5_yr_assets 0.015 0.016 0.005 0.012 0.020 174
## re_loans_5_to_15_yr_assets 0.035 0.028 0.014 0.028 0.051 174
## re_loans_more_than_15_yr_assets 0.051 0.049 0.015 0.041 0.068 174
## other_loans_less_than_3_months_assets 0.221 0.135 0.118 0.190 0.310 174
## other_loans_3_mn_1yr_assets 0.032 0.027 0.016 0.024 0.041 174
## other_loans_1_to_3_yr_assets 0.071 0.036 0.043 0.066 0.095 174
## other_loans_3_to_5_yr_assets 0.108 0.056 0.064 0.103 0.141 174
## other_loans_5_to_15_yr_assets 0.114 0.076 0.060 0.098 0.151 174
## other_loans_more_than_15_yr_assets 0.018 0.026 0.003 0.010 0.021 174
## DEPOSITS_TO_FUNDING 90.974 12.837 89.862 92.885 95.637 174
## w_assets 0.001 0.002 0.0003 0.001 0.001 174
## ------------------------------------------------------------------------------------------------------------------
dep_var = "q_MV_L_A~"
controls = c("re_loans_less_than_3_yr_assets","re_loans_3_to_5_yr_assets","re_loans_5_to_15_yr_assets","re_loans_more_than_15_yr_assets","other_loans_less_than_3_months_assets","other_loans_3_mn_1yr_assets","other_loans_1_to_3_yr_assets","other_loans_3_to_5_yr_assets","other_loans_5_to_15_yr_assets","other_loans_more_than_15_yr_assets","depexpbeta","unins_deposits_assets","htm_assets","tier_1_assets")
# +ROE+log(bs_assets_total)+deposit_growth
r <- list()
r[[1]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2022-03-31"],weights = reg_sample[date=="2022-03-31"]$w_assets )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2022-06-30"],weights = reg_sample[date=="2022-06-30"]$w_assets )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2022-09-30"],weights = reg_sample[date=="2022-09-30"]$w_assets )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2022-12-31"],weights = reg_sample[date=="2022-12-31"]$w_assets )
r[[5]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[year==2022],weights = reg_sample[year==2022]$w_assets )
r[[6]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2023-03-31"],weights = reg_sample[date=="2023-03-31"]$w_assets )
stargazer(r,type="text",no.space = T,omit.stat = "ser",
column.labels = c("22 Q1","22 Q2","22 Q3","22 Q4","2022","23 Q1"),
dep.var.labels = c("(MV+L)/A"))
##
## =================================================================================================
## Dependent variable:
## -----------------------------------------------------------
## (MV+L)/A
## 22 Q1 22 Q2 22 Q3 22 Q4 2022 23 Q1
## (1) (2) (3) (4) (5) (6)
## -------------------------------------------------------------------------------------------------
## re_loans_less_than_3_yr_assets -0.583* -0.554** -0.622* -0.328 -0.473*** -0.217
## (0.308) (0.278) (0.319) (0.296) (0.147) (0.285)
## re_loans_3_to_5_yr_assets 1.385* 1.596*** 1.401*** 0.303 0.898*** 0.099
## (0.748) (0.608) (0.529) (0.450) (0.271) (0.426)
## re_loans_5_to_15_yr_assets -0.630*** -0.528*** -0.324** 0.083 -0.280*** 0.003
## (0.206) (0.161) (0.147) (0.134) (0.079) (0.126)
## re_loans_more_than_15_yr_assets -0.043 -0.065 -0.109 -0.174** -0.119*** -0.199***
## (0.091) (0.070) (0.073) (0.069) (0.037) (0.064)
## other_loans_less_than_3_months_assets -0.064* -0.121*** -0.136*** -0.148*** -0.128*** -0.167***
## (0.039) (0.031) (0.036) (0.035) (0.017) (0.032)
## other_loans_3_mn_1yr_assets 0.234 0.268* 0.143 0.158 0.209*** 0.147
## (0.172) (0.137) (0.139) (0.129) (0.071) (0.119)
## other_loans_1_to_3_yr_assets -0.115 -0.400** -0.360* -0.264 -0.228** -0.249
## (0.218) (0.195) (0.196) (0.178) (0.097) (0.165)
## other_loans_3_to_5_yr_assets -0.164 -0.044 -0.092 -0.103 -0.116* -0.133
## (0.146) (0.120) (0.120) (0.115) (0.062) (0.106)
## other_loans_5_to_15_yr_assets -0.189** -0.171*** -0.156** -0.153** -0.171*** -0.155**
## (0.075) (0.062) (0.066) (0.064) (0.033) (0.060)
## other_loans_more_than_15_yr_assets -0.150 -0.151 -0.199 -0.254* -0.211*** -0.475***
## (0.165) (0.142) (0.148) (0.136) (0.074) (0.177)
## depexpbeta -0.109*** -0.118*** -0.118*** -0.123*** -0.116*** -0.108***
## (0.034) (0.030) (0.031) (0.030) (0.016) (0.028)
## unins_deposits_assets -0.054** -0.032 -0.039* -0.052** -0.038*** -0.109***
## (0.025) (0.022) (0.023) (0.023) (0.012) (0.021)
## htm_assets -0.042 -0.070** -0.154*** -0.183*** -0.119*** -0.247***
## (0.039) (0.035) (0.037) (0.038) (0.018) (0.035)
## tier_1_assets 0.633*** 0.792*** 0.701*** 0.602*** 0.672*** 0.495***
## (0.218) (0.179) (0.181) (0.166) (0.092) (0.153)
## Constant 1.122*** 1.103*** 1.129*** 1.142*** 1.123*** 1.169***
## (0.030) (0.026) (0.027) (0.028) (0.014) (0.026)
## -------------------------------------------------------------------------------------------------
## Observations 167 168 168 174 677 173
## R2 0.337 0.362 0.342 0.389 0.317 0.510
## Adjusted R2 0.276 0.304 0.282 0.335 0.303 0.467
## =================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
#,weights = reg_sample[date>=st_dt]$w_assets
st_dt <- as.Date("2020-03-31")
r <- list()
r[[1]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+htm_assets*qtrs+ROE+log(bs_assets_total)+deposit_growth","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+unins_deposits_assets*qtrs+ROE+log(bs_assets_total)+deposit_growth","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+depexpbeta*qtrs+ROE+log(bs_assets_total)+deposit_growth","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+other_loans_more_than_15_yr_assets*qtrs+ROE+log(bs_assets_total)+deposit_growth","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[5]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+re_loans_more_than_15_yr_assets*qtrs+ROE+log(bs_assets_total)+deposit_growth","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
# r[[6]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+other_loans_5_to_15_yr_assets*qtrs+ROE","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
# stargazer(r,type="text",no.space = T,omit.stat = "ser")
coef_plot_1reg(r[[1]],"htm_assets:qtrs",0)+ggtitle("htm_assets")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[2]],"unins_deposits_assets:qtrs",0)+ggtitle("unins_deposits_assets")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[3]],"depexpbeta:qtrs",0)+ggtitle("depexpbeta")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[4]],"other_loans_more_than_15_yr_assets:qtrs",0)+ggtitle("other_loans_more_than_15_yr_assets")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[5]],"re_loans_more_than_15_yr_assets:qtrs",0)+ggtitle("re_loans_more_than_15_yr_assets")+labs(x="quarters since Dec 2021")
# coef_plot_1reg(r[[6]],"tier_1_assets:qtrs",0)+ggtitle("tier_1_assets")+labs(x="quarters since Dec 2021")