# 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<="2023-03-31"]
data_periods <- apply(expand.grid(2017:2022,c("0331","0630","0930","1231")), 1, paste, collapse="")
data_periods <- c(data_periods,"20230331")
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 <- c(data_periods,"03312023")
# 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[,re_loans_more_than_5_assets:=re_loans_5_to_15_yr_assets+re_loans_more_than_15_yr_assets]
reg_sample[,other_loans_less_than_3_yr_assets:=other_loans_less_than_3_months_assets+other_loans_3_mn_1yr_assets+other_loans_1_to_3_yr_assets]
reg_sample[,other_loans_more_than_5_assets:=other_loans_5_to_15_yr_assets+other_loans_more_than_15_yr_assets]
# reg_sample[,qtrs:=relevel(qtrs,ref = "0")]
cols <- c("q_MV_L_A","q_MV_BV","htm_assets","afs_assets","depexpbeta","unins_deposits_assets","ins_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_more_than_5_assets","other_loans_less_than_3_yr_assets","other_loans_3_to_5_yr_assets","other_loans_more_than_5_assets","qtrs","ticker","DEPOSITS_TO_FUNDING","deposits_domestic_uninsured","wa_maturiy_assets")
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]
reg_sample[,q_below_1:=ifelse(q_MV_L_A<1,1,0)]
reg_sample[,mb_below_1:=ifelse(q_MV_BV<1,1,0)]
t <- reg_sample[,.(q=mean(q_MV_L_A,na.rm=T),
mb = mean(q_MV_BV,na.rm=T),
q_below_1=mean(q_below_1,na.rm=T),
mb_below_1=mean(mb_below_1,na.rm=T)),
by=date]
# reg_sample[,deposits_assets:=depo]
ggplot(reg_sample[,.(q_MV_L_A=mean(q_MV_BV,na.rm=T)),by=date],aes(x=date,y=q_MV_L_A))+geom_line()
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
## ins_deposits_assets 0.373 0.113 0.310 0.380 0.429 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_more_than_5_assets 0.086 0.063 0.038 0.080 0.116 174
## other_loans_less_than_3_yr_assets 0.324 0.133 0.234 0.305 0.391 174
## other_loans_3_to_5_yr_assets 0.108 0.056 0.064 0.103 0.141 174
## other_loans_more_than_5_assets 0.132 0.084 0.071 0.118 0.179 174
## DEPOSITS_TO_FUNDING 90.974 12.837 89.862 92.885 95.637 174
## deposits_domestic_uninsured 11,824,832.000 21,458,934.000 2,126,017.0 3,856,086 10,866,970 174
## wa_maturiy_assets 5.467 2.049 4.196 5.430 6.745 174
## w_assets 0.001 0.002 0.0003 0.001 0.001 174
## q_below_1 0.218 0.414 0 0 0 174
## mb_below_1 0.218 0.414 0 0 0 174
## --------------------------------------------------------------------------------------------------------------
dep_var = "q_MV_L_A~"
controls = c("depexpbeta","unins_deposits_assets","htm_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=="2020-09-30"],weights = reg_sample[date=="2020-09-30"]$w_assets )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2020-12-31"],weights = reg_sample[date=="2020-12-31"]$w_assets )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-03-31"],weights = reg_sample[date=="2021-03-31"]$w_assets )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-06-30"],weights = reg_sample[date=="2021-06-30"]$w_assets )
r[[5]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-09-30"],weights = reg_sample[date=="2021-09-30"]$w_assets )
r[[6]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-12-31"],weights = reg_sample[date=="2021-12-31"]$w_assets )
r[[7]] <- 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[[8]] <- 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[[9]] <- 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[[10]] <- 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[[11]] <- 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("20 Q3","20 Q4","21 Q1","21 Q2","21 Q3","21 Q4","22 Q1","22 Q2","22 Q3","22 Q4","23 Q1"),
dep.var.labels = c("(MV+L)/A"))
##
## ===================================================================================================================================
## Dependent variable:
## -------------------------------------------------------------------------------------------------------------
## (MV+L)/A
## 20 Q3 20 Q4 21 Q1 21 Q2 21 Q3 21 Q4 22 Q1 22 Q2 22 Q3 22 Q4 23 Q1
## (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)
## -----------------------------------------------------------------------------------------------------------------------------------
## depexpbeta -0.059*** -0.108*** -0.093*** -0.125*** -0.170*** -0.161*** -0.160*** -0.130*** -0.137*** -0.149*** -0.120***
## (0.022) (0.027) (0.029) (0.031) (0.032) (0.032) (0.028) (0.025) (0.025) (0.024) (0.023)
## unins_deposits_assets -0.023 -0.022 -0.013 -0.038 -0.070** -0.068** -0.057** -0.033 -0.033 -0.053** -0.105***
## (0.019) (0.024) (0.026) (0.026) (0.028) (0.028) (0.026) (0.023) (0.023) (0.024) (0.023)
## htm_assets 0.228*** 0.266*** 0.199*** 0.176*** 0.179*** 0.100** 0.007 0.004 -0.066* -0.088** -0.136***
## (0.069) (0.083) (0.062) (0.050) (0.045) (0.040) (0.037) (0.034) (0.034) (0.034) (0.033)
## Constant 1.006*** 1.057*** 1.074*** 1.095*** 1.134*** 1.135*** 1.132*** 1.090*** 1.100*** 1.114*** 1.108***
## (0.014) (0.017) (0.018) (0.019) (0.020) (0.019) (0.018) (0.015) (0.015) (0.014) (0.014)
## -----------------------------------------------------------------------------------------------------------------------------------
## Observations 161 163 164 165 165 165 167 168 168 174 173
## R2 0.112 0.151 0.128 0.167 0.238 0.189 0.179 0.153 0.173 0.238 0.335
## Adjusted R2 0.095 0.135 0.111 0.151 0.224 0.174 0.164 0.137 0.158 0.224 0.323
## ===================================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
dep_var = "q_MV_L_A~"
controls = c("re_loans_less_than_3_yr_assets","re_loans_3_to_5_yr_assets","re_loans_more_than_5_assets","other_loans_less_than_3_yr_assets","other_loans_3_to_5_yr_assets","other_loans_more_than_5_assets","depexpbeta","unins_deposits_assets","htm_assets","tier_1_assets","ROE")
# +ROE+log(bs_assets_total)+deposit_growth
r <- list()
r[[1]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2020-09-30"],weights = reg_sample[date=="2020-09-30"]$w_assets )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2020-12-31"],weights = reg_sample[date=="2020-12-31"]$w_assets )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-03-31"],weights = reg_sample[date=="2021-03-31"]$w_assets )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-06-30"],weights = reg_sample[date=="2021-06-30"]$w_assets )
r[[5]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-09-30"],weights = reg_sample[date=="2021-09-30"]$w_assets )
r[[6]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-12-31"],weights = reg_sample[date=="2021-12-31"]$w_assets )
r[[7]] <- 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[[8]] <- 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[[9]] <- 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[[10]] <- 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[[11]] <- 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("20 Q3","20 Q4","21 Q1","21 Q2","21 Q3","21 Q4","22 Q1","22 Q2","22 Q3","22 Q4","23 Q1"),
dep.var.labels = c("(MV+L)/A"))
##
## ==============================================================================================================================================
## Dependent variable:
## ------------------------------------------------------------------------------------------------------------
## (MV+L)/A
## 20 Q3 20 Q4 21 Q1 21 Q2 21 Q3 21 Q4 22 Q1 22 Q2 22 Q3 22 Q4 23 Q1
## (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)
## ----------------------------------------------------------------------------------------------------------------------------------------------
## re_loans_less_than_3_yr_assets -0.296* -0.142 -0.153 -0.202 -0.434* -0.347 -0.140 -0.276 -0.572** -0.199 -0.178
## (0.159) (0.191) (0.184) (0.171) (0.235) (0.257) (0.253) (0.233) (0.273) (0.249) (0.227)
## re_loans_3_to_5_yr_assets 0.547 0.515 1.088** 1.250*** 1.810*** 1.483** 0.802 1.367*** 1.659*** 0.739* 0.664*
## (0.368) (0.480) (0.464) (0.448) (0.574) (0.609) (0.580) (0.491) (0.466) (0.398) (0.363)
## re_loans_more_than_5_assets -0.392*** -0.369*** -0.343*** -0.227*** -0.111 -0.168** -0.184*** -0.175*** -0.150*** -0.119** -0.162***
## (0.064) (0.082) (0.079) (0.068) (0.068) (0.067) (0.061) (0.050) (0.055) (0.052) (0.047)
## other_loans_less_than_3_yr_assets -0.091*** -0.028 -0.126*** -0.113*** -0.046 -0.074** -0.069** -0.094*** -0.094*** -0.124*** -0.144***
## (0.026) (0.031) (0.033) (0.030) (0.033) (0.032) (0.029) (0.024) (0.028) (0.028) (0.025)
## other_loans_3_to_5_yr_assets -0.034 0.027 0.055 -0.021 -0.083 -0.005 -0.115 -0.145** -0.162** -0.128* -0.130**
## (0.083) (0.105) (0.086) (0.075) (0.088) (0.091) (0.081) (0.071) (0.073) (0.066) (0.061)
## other_loans_more_than_5_assets -0.069 0.032 -0.059 -0.077 -0.049 -0.120** -0.143** -0.115** -0.096* -0.125** -0.148***
## (0.057) (0.069) (0.061) (0.055) (0.062) (0.060) (0.055) (0.046) (0.053) (0.050) (0.047)
## depexpbeta -0.058** -0.090*** -0.064** -0.056** -0.075** -0.117*** -0.105*** -0.079*** -0.078*** -0.101*** -0.075***
## (0.024) (0.032) (0.029) (0.027) (0.031) (0.032) (0.028) (0.024) (0.027) (0.025) (0.023)
## unins_deposits_assets -0.027 -0.014 0.070*** 0.047** 0.019 -0.008 -0.020 -0.017 -0.031 -0.058*** -0.114***
## (0.019) (0.023) (0.025) (0.022) (0.025) (0.025) (0.023) (0.019) (0.021) (0.021) (0.019)
## htm_assets 0.132* 0.225*** 0.112* 0.131*** 0.193*** 0.099*** -0.057* -0.038 -0.100*** -0.129*** -0.191***
## (0.070) (0.084) (0.058) (0.043) (0.040) (0.037) (0.034) (0.030) (0.036) (0.036) (0.033)
## tier_1_assets 0.086 -0.436** 0.310 0.315* 0.364* 0.525*** 0.222 0.247* 0.337** 0.307** 0.192
## (0.161) (0.168) (0.190) (0.173) (0.192) (0.181) (0.168) (0.144) (0.155) (0.139) (0.126)
## ROE 0.038 0.105** 1.410*** 1.019*** 0.767*** 0.688*** 1.616*** 0.743*** 0.371*** 0.317*** 0.342***
## (0.035) (0.045) (0.208) (0.102) (0.089) (0.080) (0.229) (0.101) (0.078) (0.062) (0.056)
## Constant 1.072*** 1.104*** 1.010*** 0.987*** 0.971*** 0.998*** 1.085*** 1.061*** 1.073*** 1.105*** 1.123***
## (0.025) (0.027) (0.030) (0.027) (0.029) (0.031) (0.027) (0.024) (0.027) (0.027) (0.025)
## ----------------------------------------------------------------------------------------------------------------------------------------------
## Observations 161 163 164 165 165 165 167 168 168 174 173
## R2 0.334 0.352 0.458 0.577 0.556 0.487 0.463 0.483 0.406 0.448 0.572
## Adjusted R2 0.285 0.304 0.419 0.547 0.524 0.450 0.425 0.447 0.364 0.411 0.543
## ==============================================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
dep_var = "q_MV_BV~"
controls = c("depexpbeta","unins_deposits_assets","htm_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=="2020-09-30"],weights = reg_sample[date=="2020-09-30"]$w_assets )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2020-12-31"],weights = reg_sample[date=="2020-12-31"]$w_assets )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-03-31"],weights = reg_sample[date=="2021-03-31"]$w_assets )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-06-30"],weights = reg_sample[date=="2021-06-30"]$w_assets )
r[[5]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-09-30"],weights = reg_sample[date=="2021-09-30"]$w_assets )
r[[6]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-12-31"],weights = reg_sample[date=="2021-12-31"]$w_assets )
r[[7]] <- 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[[8]] <- 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[[9]] <- 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[[10]] <- 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[[11]] <- 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("20 Q3","20 Q4","21 Q1","21 Q2","21 Q3","21 Q4","22 Q1","22 Q2","22 Q3","22 Q4","23 Q1"),
dep.var.labels = c("(MV+L)/A"))
##
## ==================================================================================================================================
## Dependent variable:
## ------------------------------------------------------------------------------------------------------------
## (MV+L)/A
## 20 Q3 20 Q4 21 Q1 21 Q2 21 Q3 21 Q4 22 Q1 22 Q2 22 Q3 22 Q4 23 Q1
## (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)
## ----------------------------------------------------------------------------------------------------------------------------------
## depexpbeta -0.797*** -1.102*** -0.735** -0.932*** -1.254*** -1.152*** -1.228*** -1.195*** -1.386*** -1.527*** -1.218***
## (0.199) (0.285) (0.302) (0.298) (0.309) (0.309) (0.303) (0.270) (0.301) (0.268) (0.252)
## unins_deposits_assets -0.342* -0.156 -0.076 -0.268 -0.414 -0.549** -0.348 -0.130 0.049 -0.204 -0.984***
## (0.178) (0.251) (0.268) (0.254) (0.266) (0.269) (0.274) (0.247) (0.282) (0.267) (0.251)
## htm_assets 2.813*** 4.097*** 3.531*** 2.982*** 2.750*** 1.755*** 0.517 0.246 -0.796* -1.029*** -1.638***
## (0.638) (0.857) (0.637) (0.487) (0.426) (0.393) (0.400) (0.371) (0.412) (0.385) (0.362)
## Constant 1.213*** 1.538*** 1.606*** 1.736*** 1.982*** 2.066*** 2.064*** 1.774*** 1.875*** 2.041*** 2.077***
## (0.126) (0.180) (0.187) (0.183) (0.191) (0.189) (0.189) (0.164) (0.186) (0.162) (0.152)
## ----------------------------------------------------------------------------------------------------------------------------------
## Observations 161 163 164 165 165 165 167 168 168 174 173
## R2 0.200 0.207 0.211 0.251 0.297 0.197 0.109 0.116 0.123 0.184 0.310
## Adjusted R2 0.185 0.192 0.197 0.237 0.284 0.182 0.093 0.100 0.107 0.170 0.298
## ==================================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
dep_var = "q_MV_BV~"
controls = c("re_loans_less_than_3_yr_assets","re_loans_3_to_5_yr_assets","re_loans_more_than_5_assets","other_loans_less_than_3_yr_assets","other_loans_3_to_5_yr_assets","other_loans_more_than_5_assets","depexpbeta","unins_deposits_assets","htm_assets","tier_1_assets","ROE")
# +ROE+log(bs_assets_total)+deposit_growth
r <- list()
r[[1]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2020-09-30"],weights = reg_sample[date=="2020-09-30"]$w_assets )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2020-12-31"],weights = reg_sample[date=="2020-12-31"]$w_assets )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-03-31"],weights = reg_sample[date=="2021-03-31"]$w_assets )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-06-30"],weights = reg_sample[date=="2021-06-30"]$w_assets )
r[[5]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-09-30"],weights = reg_sample[date=="2021-09-30"]$w_assets )
r[[6]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"))),data=reg_sample[date=="2021-12-31"],weights = reg_sample[date=="2021-12-31"]$w_assets )
r[[7]] <- 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[[8]] <- 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[[9]] <- 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[[10]] <- 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[[11]] <- 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("20 Q3","20 Q4","21 Q1","21 Q2","21 Q3","21 Q4","22 Q1","22 Q2","22 Q3","22 Q4","23 Q1"),
dep.var.labels = c("(MV+L)/A"))
##
## ===============================================================================================================================================
## Dependent variable:
## -------------------------------------------------------------------------------------------------------------
## (MV+L)/A
## 20 Q3 20 Q4 21 Q1 21 Q2 21 Q3 21 Q4 22 Q1 22 Q2 22 Q3 22 Q4 23 Q1
## (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)
## -----------------------------------------------------------------------------------------------------------------------------------------------
## re_loans_less_than_3_yr_assets -2.145 -1.665 -2.000 -2.332 -4.288* -3.170 -1.454 -3.136 -6.175* -2.286 -1.533
## (1.447) (1.972) (1.881) (1.668) (2.276) (2.537) (2.660) (2.499) (3.153) (2.720) (2.505)
## re_loans_3_to_5_yr_assets 4.702 5.476 11.557** 12.866*** 17.603*** 13.068** 7.256 13.889*** 16.636*** 7.290* 6.813*
## (3.347) (4.958) (4.742) (4.371) (5.571) (6.009) (6.103) (5.268) (5.389) (4.339) (4.010)
## re_loans_more_than_5_assets -3.770*** -3.743*** -3.198*** -1.984*** -0.869 -1.597** -1.910*** -1.755*** -1.829*** -1.560*** -2.010***
## (0.583) (0.842) (0.811) (0.659) (0.660) (0.664) (0.644) (0.534) (0.633) (0.567) (0.521)
## other_loans_less_than_3_yr_assets -0.780*** -0.194 -1.191*** -1.105*** -0.514 -0.839*** -1.049*** -1.301*** -1.516*** -1.666*** -1.799***
## (0.234) (0.319) (0.333) (0.292) (0.322) (0.314) (0.310) (0.258) (0.329) (0.303) (0.279)
## other_loans_3_to_5_yr_assets -0.597 -0.316 0.153 -0.597 -0.981 -0.228 -1.576* -1.880** -2.220*** -1.855** -1.762***
## (0.759) (1.082) (0.881) (0.735) (0.856) (0.896) (0.854) (0.766) (0.843) (0.720) (0.670)
## other_loans_more_than_5_assets -0.604 0.265 -0.716 -0.989* -0.674 -1.585*** -1.974*** -1.611*** -1.508** -1.511*** -1.601***
## (0.523) (0.713) (0.626) (0.540) (0.599) (0.587) (0.582) (0.498) (0.610) (0.546) (0.520)
## depexpbeta -0.720*** -0.756** -0.293 -0.175 -0.310 -0.660** -0.652** -0.629** -0.712** -0.984*** -0.789***
## (0.219) (0.325) (0.297) (0.265) (0.301) (0.313) (0.294) (0.258) (0.307) (0.276) (0.254)
## unins_deposits_assets -0.379** -0.122 0.758*** 0.480** 0.372 -0.039 -0.057 -0.0004 0.026 -0.297 -1.121***
## (0.171) (0.238) (0.255) (0.213) (0.240) (0.244) (0.242) (0.205) (0.243) (0.230) (0.211)
## htm_assets 2.074*** 3.595*** 2.451*** 2.355*** 2.757*** 1.619*** -0.374 -0.380 -1.425*** -1.680*** -2.447***
## (0.642) (0.869) (0.594) (0.417) (0.383) (0.362) (0.363) (0.327) (0.411) (0.395) (0.363)
## tier_1_assets 1.866 -4.708*** 1.781 1.893 2.477 4.460** 0.986 2.246 3.592** 2.364 1.718
## (1.467) (1.738) (1.945) (1.691) (1.867) (1.788) (1.766) (1.547) (1.787) (1.515) (1.392)
## ROE 0.319 1.040** 15.609*** 9.930*** 7.434*** 6.382*** 17.511*** 8.225*** 4.873*** 3.959*** 3.452***
## (0.319) (0.469) (2.128) (0.998) (0.859) (0.788) (2.414) (1.088) (0.899) (0.676) (0.621)
## Constant 1.701*** 2.012*** 0.966*** 0.828*** 0.565** 0.943*** 1.917*** 1.675*** 1.813*** 2.174*** 2.493***
## (0.225) (0.275) (0.310) (0.263) (0.285) (0.303) (0.280) (0.254) (0.315) (0.299) (0.275)
## -----------------------------------------------------------------------------------------------------------------------------------------------
## Observations 161 163 164 165 165 165 167 168 168 174 173
## R2 0.409 0.400 0.515 0.612 0.579 0.478 0.439 0.484 0.418 0.455 0.559
## Adjusted R2 0.365 0.356 0.480 0.584 0.549 0.440 0.399 0.447 0.377 0.418 0.529
## ===============================================================================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01
#,weights = reg_sample[date>=st_dt]$w_assets
controls2 <- c("tier_1_assets","log(1+bs_assets_total)","DEPOSITS_TO_FUNDING","wa_maturiy_assets")
st_dt <- as.Date("2020-03-31")
r <- list()
r[[1]] <- felm(as.formula(paste0(dep_var,paste(controls2,collapse = "+"),"+log(1+htm_assets)*qtrs","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[2]] <- felm(as.formula(paste0(dep_var,paste(controls2,collapse = "+"),"+unins_deposits_assets*qtrs","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[3]] <- felm(as.formula(paste0(dep_var,paste(controls2,collapse = "+"),"+depexpbeta*qtrs","|date+ticker|0|date")),data=reg_sample[date>=st_dt] )
r[[4]] <- felm(as.formula(paste0(dep_var,paste(controls,collapse = "+"),"+log(wa_maturiy_assets)*qtrs","|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]],"log(1 + htm_assets):qtrs",-7)+ggtitle("htm_assets")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[2]],"unins_deposits_assets:qtrs",-7)+ggtitle("unins_deposits_assets")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[3]],"depexpbeta:qtrs",-7)+ggtitle("depexpbeta")+labs(x="quarters since Dec 2021")
coef_plot_1reg(r[[4]],"log(wa_maturiy_assets):qtrs",-7)+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",-7)+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")