clear all
setpath_windows

start_year = 1980;
end_year = 2024;


%% Average equity/asset ratio
% import bank data from H.8 form (in billions of dollars)
% https://banks.data.fdic.gov/explore/failures

data_bank_h8 = readtable('Commercial_banks.xlsx','Sheet',"Monthly");

data_bank_h8 = data_bank_h8(year(data_bank_h8.observation_date)>=start_year & year(data_bank_h8.observation_date)<=end_year,:);


display(['Average equity/asset ratio for all commercial banks in the U.S., ',num2str(start_year),'-',num2str(end_year)])
gamma_mean = mean( (data_bank_h8.TLAACBM027SBOG-data_bank_h8.TLBACBM027SBOG)./data_bank_h8.TLAACBM027SBOG*100 )


figure

plot(data_bank_h8.observation_date,(data_bank_h8.TLAACBM027SBOG-data_bank_h8.TLBACBM027SBOG)./data_bank_h8.TLAACBM027SBOG*100)
hold on; plot(data_bank_h8.observation_date,0*data_bank_h8.TLAACBM027SBOG+gamma_mean,'r--')
title('Equity to Asset Ratio')
ylabel('percent')

%% Average bank failure rate
% import assets of failed banks (in thousands of dollars)
data_failures = readtable('bank-data_failure.xlsx');

data_failures.year = year(data_failures.FAILDATE);
data_failures.quarter = quarter(data_failures.FAILDATE);

% Select the range of years
data_failures = data_failures(data_failures.year>=start_year & data_failures.year<=end_year,:);

% Summary: The sum (over months) of total assets for each quarter
data_failures_summary = groupsummary(data_failures,["year" "quarter"],{"sum","sum"},"QBFASSET");

% Convert total assets of the banking sector to billions of dollars
data_failures_summary.sum_QBFASSET = data_failures_summary.sum_QBFASSET/1000000;
%data_failures_summary.sum_QBFASSET = cellfun(@str2num,data_failures_summary.sum_QBFASSET)/1000000;

% Working with bank data from H.8 form
data_bank_h8.year = year(data_bank_h8.observation_date);
data_bank_h8.quarter = quarter(data_bank_h8.observation_date);
% Summary: The average (over months) of total assets for each quarter
data_bank_h8_summary = groupsummary(data_bank_h8,["year" "quarter"],{"mean" "mean"},"TLAACBM027SBOG");
% Merging data
data_all_bank = outerjoin(data_failures_summary, data_bank_h8_summary, 'Keys',["year" "quarter"]);
data_all_bank.FailureRate = 100*data_all_bank.sum_QBFASSET./data_all_bank.mean_TLAACBM027SBOG;

% Filling missing values of failure rates (meaning that there were no failures) with zeros
%data_all_bank.FailureRate = fillmissing(data_all_bank.FailureRate,'constant',0);

% find missing rate in data_failures and set it to 0 --- these are years
% with no bank failures
data_all_bank.FailureRate(isnan(data_all_bank.FailureRate))=0;

display(['Average quarterly bank failure rate in the U.S., ',num2str(start_year),'-',num2str(end_year)])
mean(data_all_bank.FailureRate)

%% Plotting
figure
plot(data_all_bank.year_data_bank_h8_summary+(data_all_bank.quarter_data_bank_h8_summary-1)/4,data_all_bank.FailureRate,'LineWidth',2,'Color','red');
%title('Bank Failure Rate (assets of failed banks/banking sector assets')
xlabel('Year')
ylabel('Percent, annualized rate')
grid on

print -depsc2 figure_failure_rate_data.eps


% probability of default rate exceeding 1 percent
length(find(data_all_bank.FailureRate>1))/length(data_all_bank.FailureRate)

%% Average GDP gap 

data_nipa = readtable('Commercial_banks.xlsx','Sheet','Quarterly');
data_nipa = data_nipa(year(data_nipa.observation_date)>=start_year & year(data_nipa.observation_date)<=end_year,:);

data_nipa.year = year(data_nipa.observation_date);
data_nipa.quarter = quarter(data_nipa.observation_date);

% add failure rates to nipa data
%data_failures = data_all_bank(:,{'FailureRate','year'});
data_failures = data_all_bank(:,{'year_data_bank_h8_summary' 'quarter_data_bank_h8_summary' 'FailureRate'});
data_failures = renamevars(data_failures,["year_data_bank_h8_summary" "quarter_data_bank_h8_summary"], ["year" "quarter"]);

data_all = join(data_nipa,data_failures,"Keys",["year" "quarter"]);

data_all.gdp_detrended = 100*bpass(log(data_all.A191RA3Q086SBEA),6,32);
data_all.c_detrended = 100*bpass(log(data_all.PCECC96),6,32);


mean(data_all(data_all.FailureRate>1/4,'gdp_detrended'))

mean(data_all(data_all.FailureRate>1.5/4,'gdp_detrended'))

mean(data_all(data_all.FailureRate>2/4,'gdp_detrended'))

mean(data_all(data_all.FailureRate>2.5/4,'gdp_detrended'))



%% compute deposit rate from FDIC aggregate data from Call Report
income = readtable('quarterly-income.xlsx');
balancesheet = readtable('balance-sheet.xlsx');

row_balancesheet_deposits = find(strcmp('Deposits',balancesheet.Var1));
row_domestic_office_deposit_expense = find(strcmp('Domestic office deposits',income.Var1));
row_foreign_office_deposit_expense = find(strcmp('Foreign office deposits',income.Var1));

row_noninterest_expense = find(strcmp('Total noninterest expense',income.Var1));
row_noninterest_income = find(strcmp('Total noninterest income',income.Var1));

dates_interest_expense_rate = 1984:.25:2024;
deposit_interest_expense_rate = 400*(income{row_domestic_office_deposit_expense,2:end} + ...
                                     income{row_foreign_office_deposit_expense,2:end} ...
                                    )./balancesheet{row_balancesheet_deposits,2:end};

adj_deposit_interest_expense_rate = 400*(income{row_domestic_office_deposit_expense,2:end} + ...
                                        income{row_foreign_office_deposit_expense,2:end} + ...
                                        income{row_noninterest_expense,2:end} - income{row_noninterest_income,2:end} ...
                                    )./balancesheet{row_balancesheet_deposits,2:end};

row_total_interest_expense = find(strcmp('Total interest expense',income.Var1));
row_total_liabilities = find(strcmp('Total liabilities',balancesheet.Var1));

begenau_deposit_interest_expense_rate = 400*income{row_total_interest_expense,2:end}  ...
                                        ./balancesheet{row_total_liabilities,2:end};

row_balancesheet_subordinated_debt = find(strcmp('Subordinated debt',balancesheet.Var1));
row_subordinated_debt_expense = find(strcmp('Subordinated notes and debentures',income.Var1));
return_on_bank_equity = 400*income{row_subordinated_debt_expense,2:end}./balancesheet{row_balancesheet_subordinated_debt,2:end};

mean(return_on_bank_equity-adj_deposit_interest_expense_rate)

mean(return_on_bank_equity)

% get 3 month Treasury rate
treasury = readtable('TB3MS.xlsx','Sheet','Quarterly');

% figure
% plot(dates_interest_expense_rate,deposit_interest_expense_rate,'k--')

display('The average (annualized) premium on deposits is')
mean(treasury.TB3MS-deposit_interest_expense_rate')


%% Compute the average real risk-free interest rate (use average ex-post real rate on Treasury securities)

data_table = readtable('Commercial_banks.xlsx','Sheet',"quarterly");
price_level = data_table.JCXFE;
infl = 400*[nan; log(price_level(2:end))-log(price_level(1:end-1))];
data_table.infl = infl;
data_table = data_table(year(data_table.observation_date)>1979 &...
    year(data_table.observation_date)<2025,:);

data_table_m = readtable('Commercial_banks.xlsx','Sheet',"monthly");
data_table_m = table2timetable(data_table_m);
data_table_q = retime(data_table_m,'quarterly','mean');
data_table_q = timetable2table(data_table_q);

data_table_q = data_table_q(year(data_table_q.observation_date)>1979 &...
    year(data_table_q.observation_date)<2025,:);

display('Average real risk-free rate')
mean(data_table_q.TB3MS-data_table.infl)

% figure
% plot(data_table_q.TB3MS,'r-')


%1/betap = 1+(1.2831)/400;
betap = 1/(1+1.2831/400);