Federal Home Loan Bank Data
Contributed by Joe Eckert. Joe took NYC Data Science Academy 12 week full time Data Science Bootcamp program between Sept 23 to Dec 18, 2015. The post was based on his second class project(due at 4th week of the program).
Introduction:
The goal of project two with the NYC Data Science Academy was to build an application using Shiny to visualize a dataset. I chose to use the Federal Home Loan Bank's loan level mortgage data.
The purpose of this app is to examine the distribution of mortgages that make up the FHLB portfolio in order to analyze the system's effectiveness. The Federal Home Loan Bank system is made up of 11 U.S. government-sponsored banks that provide stable, on-demand, low-cost funding to American financial institutions. This funding is used for home mortgage, small business, rural, agricultural, and economic development lending. With their members, the FHLBanks represents the largest collective source of home mortgage and community credit in the United States.
Click here to view a demo of the shiny app!
Overview of Application:
After working with R during the first couple of weeks in the bootcamp it was time to make my first web application with Shiny. My first task was to gain an understanding of the structure of a basic shiny app. R Studio's Shiny documentation proved to be a great resource in my learning process: Shiny Tutorial
A shiny app has two basic requirements, a ui.R and server.R file. The ui.R file controls the user interface as well as all design aspects of the application, whereas the server.R file handles the data processing and back end of the application. I also chose to use a global.R file to prepare the raw data sourced from the FHLB to be used for the application.
Data Preparation
I began the development process with 6 raw data files from the FHLB which each contained loan level data for each year from 2009 to 2014 (Source Data).
Below you can see the code that was used to clean and organize the data into a set of dataframes to be used by the server file.
#Load required libraries
library(dplyr)
library(choroplethr)
library(choroplethrMaps)
library(stringr)
library(ggplot2)
library(ggthemes)
library(scales)
library(maps)
library(mapproj)
library(plotly)
#Import data from Federal Home Loan Banks
fhlb09 fhlb10 fhlb11 fhlb12 fhlb13 fhlb14
#Import FIPS data
#https://www.census.gov/geo/reference/codes/cou.html
fips
#Create vector for state abbreviations and state names
states state=c("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DC", "DE", "FL", "GA",
"HI", "IA", "ID", "IL", "IN", "KS", "KY", "LA", "MA", "MD", "ME",
"MI", "MN", "MO", "MS", "MT", "NC", "ND", "NE", "NH", "NJ", "NM",
"NV", "NY", "OH", "OK", "OR", "PA", "PR", "RI", "SC", "SD", "TN",
"TX", "UT", "VA", "VT", "WA", "WI", "WV", "WY"),
full=c("alaska","alabama","arkansas","arizona","california","colorado",
"connecticut","district of columbia","delaware","florida","georgia",
"hawaii","iowa","idaho","illinois","indiana","kansas","kentucky",
"louisiana","massachusetts","maryland","maine","michigan","minnesota",
"missouri","mississippi","montana","north carolina","north dakota",
"nebraska","new hampshire","new jersey","new mexico","nevada",
"new york","ohio","oklahoma","oregon","pennsylvania","puerto rico",
"rhode island","south carolina","south dakota","tennessee","texas",
"utah","virginia","vermont","washington","wisconsin",
"west virginia","wyoming")
)
#Correct column names to allow for rbind()
names(fhlb09)[2] <- "Loan.Number"
fhlb09$FHLBankID names(fhlb14)[65] <- "Co.Borrower.Credit.Score"
#Bind yearly data sets into a single data frame
fhlb
#Join state and county data from FIPS to FHLB data
fhlb
#Create region code
fhlb$region
fhlb$STATE states$state
#Join full state name from manual states vector
fhlb
#Some minor cleanup
names(fhlb)[names(fhlb) == 'full'] <- "STATENAME"
fhlb$STATENAME fhlb$Seller fhlb$Amount
#Prepare Interest for Weighted Interest
fhlb$annInt
#Select relevant variables for analysis
portfolio
#Factorize Variables
portfolio$Purpose portfolio$Purpose
portfolio$First portfolio$First
portfolio$BoRace portfolio$BoRace labels = c("NativeAmerican", "Asian", "AfricanAmerican", "PacificIslander", "White", "NotSpecified"))
portfolio$BoGender portfolio$BoGender labels = c("Male", "Female", "NotSpecified"))
portfolio$Borrower.Credit.Score portfolio$Borrower.Credit.Score levels = c(1, 2, 3, 4, 5, 9),
labels = c("< 620", "620 - 660", "660 - 700", "700 - 760", "> 760", "NotSpecified"))
portfolio$BoAge[portfolio$BoAge == 99]
#Prepare data frame for map
mortByState mortByState mortByState mortByState names(mortByState) mortByState$WgtRate mortByState$TotalInt
mortByState$hover State, '
', "Total Mortgage Value: $",
prettyNum(TotalMortgage, big.mark = ","),
'
', "Weighted Avg Rate: ",
formatC(WgtRate * 100, digits = 4), "%"))
#Calculate total mortgage portfolio by county - used for state map
countySum
#Save data frame as Rda file for future loading
save(fhlb, fips, states, portfolio, mortByState, countySum, file = "data/data.Rds")
#Clear working memory
rm(list = ls())
load("data/data.Rds")
Creating the User Interface:
The next step was to create the user interface. The app contains four main sections, which allow the user to explore various aspects of the data.
The first section uses the plotly package to display a state level heat map. There is a drop down that allows the user to select which metric to map; either the total mortgage value or weighted average interest rate. One interesting observation is that roughly 17% of the entire mortgage portfolio is made up of mortgages originated in Ohio.
The second section allows the user to graph a variety of variables against the total mortgage value in the portfolio (year, age, credit score, gender, race, etc.)
The third section deals with state level information. The user is able to select a state. I then used the choroplethr mapping package to map the total value of mortgages by county in each state. There are also data tables below that show the amount of mortgages originated based on gender, race and credit score.
The final section shows the loan level data, allowing the user to filter and search within the data set.
The Backend:
After creating all the inputs required for the user to make their selections in the ui.R file I then moved onto the server.R file. Here is where I used the inputs collected from the user interface to generate output objects (maps, graphs and tables) to be returned to the user.
Conclusion:
Shiny is a great package that allows for some pretty interesting applications. This being my first Shiny app could definitely be developed further. If time had permitted, it would have been great to add additional functionality to allow the user to cut the data in more granular ways. In addition, it would be interesting to combine the FHLB data with other data sets related to lending to compare the effectiveness of the FHLB compared to the private market. The issue here is that private market loan level origination data is proprietary and hard to collect.
You can view the full code for my application on my GitHub.
About Author
Joe Eckert
View all posts by Joe Eckert >Leave a Comment
