In bertcarnell/fry9c: Interacting with the Federal Reserve Consolidated Financial Statements for Holding Companies (FR Y-9c)

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
require(fry9c)
require(RColorBrewer)
require(ggplot2)
require(scales)

FR Y-9c

From the U.S. Federal Reserve website, the description and purpose of the FR Y-9c is:

Description

"This report collects basic financial data from a domestic bank holding company (BHC), a savings and loan holding company (SLHC), a U.S intermediate holding company (IHC) and a securities holding company (SHC) on a consolidated basis in the form of a balance sheet, an income statement, and detailed supporting schedules, including a schedule of off balance-sheet items." (1)

Purpose

"The information is used to assess and monitor the financial condition of holding company organizations, which may include parent, bank, and nonbank entities. The FR Y-9C is a primary analytical tool used to monitor financial institutions between on-site inspections. The form contains more schedules than any of the FR Y-9 series of reports and is the most widely requested and reviewed report at the holding company level." (1)

Dowloading Data

The first step is to download the FR Y-9c data from the Federal Reserve.

fry9c_data_list <- list(
  read.csv(system.file(file.path("extdata", "ex_BHCF1712.csv"), package = "fry9c")),
  read.csv(system.file(file.path("extdata", "ex_BHCF1812.csv"), package = "fry9c")))
bank_meta_data <- read.csv(system.file(file.path("extdata", "ex_bank_meta_data.csv"), 
                                       package = "fry9c"))

fry9c_data_list <- list(get_fry9c_data(2017, 4, verbose = FALSE),
                        get_fry9c_data(2016, 4, verbose = FALSE))
bank_meta_data <- get_bank_meta_data()

Create a fry9c Object and Add Data

Create objects

my_fry9c_group <- Fry9c_group(years = c(2017, 2016),
                      quarters = c(4, 4))

Select banks

Selecting a subset of banks is not required, but allows for a more focused inspection.

# These names must match those in the bank_meta_data
#   For example, search for PNC
bank_meta_data[grep("PNC", bank_meta_data$Name),]

targets <- c("PNC BANK CORP",
             "U.S. BANCORP")

# It is also helpfult to have shorter names like stock tickers
targets_short <- c("PNC", "USB")
assertthat::assert_that(length(targets) == length(targets_short))

# Get the target ids
target_ids <- get_bank_ids(targets, bank_meta_data)

Initialize Objects

# add the schedule and component definitions
my_fry9c_group$parse_fry9c(
  system.file(file.path("extdata", c("FR_Y-9C20171231.xml", "FR_Y-9C20161231.xml")), 
              package = "fry9c"))

# create a dataset with only the target banks
fry9c_data_list_small <- lapply(fry9c_data_list, function(z) {
  z[match(target_ids, z$RSSD9001),]})

# initialize the data
my_fry9c_group$initializeData(fry9c_data_list_small, targets_short)

# common size the Income Statements (HI and HI_Memo)
my_fry9c_group$commonSize("HC-K", "BHCK3368", "HI")
my_fry9c_group$commonSize("HC-K", "BHCK3368", "HI_Memo")

Plotting

Setting up the color palette

pal <- brewer.pal(3, "Set1")
stock_cols <- c("PNC" = pal[1],
                "USB" = pal[2])
assertthat::assert_that(length(targets) == length(stock_cols))
stock_sizes <- c("PNC" = 1,
                 "USB" = 2)
assertthat::assert_that(length(targets) == length(stock_sizes))

Plotting a time series

avg_asset_data <- my_fry9c_group$get_plot_data("HC-K", "BHCK3368")

ggplot(avg_asset_data, aes(x = x, y = value, group = bank, col = bank)) +
  geom_point(aes(size = bank)) +
  geom_line() +
  scale_y_log10(name = "Total Average Assets (000s) (log scale)", labels = scales::dollar) +
  xlab("") +
  scale_color_manual(values = stock_cols) +
  scale_size_manual(values = stock_sizes) +
  theme(legend.title = element_blank(),
        axis.text.x = element_text(angle = 90, hjust = 1, vjust = 0.5))

interest_income_data <- my_fry9c_group$get_plot_data("HI", "BHCK4107")

ggplot(interest_income_data, aes(x = x, y = common_value, group = bank, col = bank)) +
  geom_point(aes(size = bank)) +
  geom_line() +
  scale_y_continuous(name = "Interest Income / Average Assets", labels = scales::percent) +
  xlab("Quarter") +
  scale_color_manual(values = stock_cols) +
  scale_size_manual(values = stock_sizes) +
  theme(legend.title = element_blank())

Plotting the Distribution of Bank Performance

targets <- substring(bank_meta_data$Name[bank_meta_data$Entity_Type == "FHD"], 1, 20)
target_ids <- get_bank_ids(targets, bank_meta_data)

fry9c_data_list_FHD <- lapply(fry9c_data_list, function(z) {
  z[match(target_ids, z$RSSD9001),]})

my_fry9c_group$initializeData(fry9c_data_list_FHD, targets)

my_fry9c_group$commonSize("HC-K", "BHCK3368", "HI")
my_fry9c_group$commonSize("HC-K", "BHCK3368", "HI_Memo")

avg_asset_data <- my_fry9c_group$get_plot_data("HC-K", "BHCK3368")

ggplot(avg_asset_data, aes(x=value, group=year)) + 
  geom_histogram(bins=20) +
  facet_grid(.~year) +
  scale_x_log10(name="Average Assets (000s) (log scale)", label = scales::dollar) + 
  theme(axis.text.x = element_text(angle = 90, hjust = 1, vjust = 0.5)) +
  ylab("Count (Example Data)")

interest_income_data <- my_fry9c_group$get_plot_data("HI", "BHCK4107")
interest_income_data$year <- factor(interest_income_data$year)

ggplot(interest_income_data, aes(x = year, y = common_value, group = year)) + 
  geom_boxplot() +
  scale_y_continuous(name="Interest Income / Average Assets", label = scales::percent) +
  xlab("")

Note

Care must be taken to ensure that a bank is not included in the data twice. See J.P. Morgan below.

print(subset(interest_income_data,
             select=c("year","quarter","bank","value","common_value")))

bertcarnell/fry9c documentation built on April 18, 2022, 10:05 p.m.