Introduction

The purpose of this blog post is to demonstrate how to web mine the bankrate.com, primarily focusing on extracting and graphing with the R programming language the APY and minimum deposits for 1-year¹, 3-year², and 5-year³ CD Rates.

Setup

Before the analysis, some necessary libraries will be loaded. First, tidyverse⁴ and magrittr⁵ for their data management functions; second, flextable⁶ for table formatting; third, rvest⁷ for web mining; and fourth, plotly⁸ to display interactive graphs.

libs <- c('tidyverse', 'magrittr', 'flextable', 
          'rvest', 'plotly')

# If the library has not been installed,
## install and load it.
for (i in libs) {
  
  if (!require(i, character.only = TRUE)) {
    
    install.packages(i)
    library(i, character.only = TRUE)
    
  }
  
}

Web Scraping

Now, we can begin the web scraping by setting up a data frame of URLs from which to scrape.

links <- data.frame(category = c('1-Year CD Rates', 
                                 '3-Year CD Rates', 
                                 '5-Year CD Rates'),
                    url = c('https://www.bankrate.com/banking/cds/best-1-year-cd-rates/',
                            'https://www.bankrate.com/banking/cds/best-3-year-cd-rates/',
                            'https://www.bankrate.com/banking/cds/best-5-year-cd-rates/'),
                    stringsAsFactors = FALSE)

To scrape the websites, I use the rvest functions read_html(), html_node(), and html_table(). The first imports the URL into R; the second searches for a specific node (in this case, a table); and the third converts the information into a data frame. Then I use purrr’s set_names() to rename the key columns for easier referencing, followed by creating a new column based on the category column in the links data frame.

links_df <- with(links, map2_df(url, category, ~ {
  
  read_html(.x) %>%
    html_node('table') %>% 
    html_table() %>%
    .[, 1:3] %>% # Only these three columns are necessary.
    set_names(c('Institution', 'APY', 'Min_Deposit')) %>% 
    mutate(Category = .y)
  
}))

To know what the dataset looks like at this point, we employ the flextable() function from flextable.

links_df %>%
  arrange(Category, Institution) %>%
  flextable() %>%
  set_caption(caption = 'Table 1: Initial Dataset') %>%
  flextable::footnote(part = 'header', 
                      value = as_paragraph('Source: bankrate.com')) %>%
  autofit()

Data Cleaning

Because the non-numeric symbols in APY and Min_Deposit make those columns character instead of numeric, we need to conduct some data cleaning so that they are numeric for the plotting.

# Specific hard-coding for this particular institution.
links_df$Min_Deposit[links_df$Min_Deposit == '$20,000 minimum for this APY'] <- 20000

# Make APY and Min_Deposit numeric
links_df[, c('APY', 'Min_Deposit')] %<>%
  map_df(~ as.numeric(gsub('%|\\$|,', '', .x)))

## Warning in .f(.x[[i]], ...): NAs introduced by coercion

The following table is our new, cleaned dataset.

links_df %>%
  arrange(Category, Institution) %>%
  flextable() %>%
  set_caption(caption = 'Table 2: Cleaned Dataset') %>%
  flextable::footnote(part = 'header', 
                      value = as_paragraph('Source: bankrate.com')) %>%
  autofit()

Graphs

Before we plot the results, a function will be written to generalize the desired graphing procedure: APY on the x-axis, minimum deposit on the y-axis, Institutions labeled on the graph, and colors being noted by their CD rates category (1-, 3-, and 5-year). I aim for a more minimalistic type graph with theme_light() and removing specific gridlines with panel.grid.minor and panel.grid.major.x within theme().

apy_v_md <- function(data, title) {
  
  ggplot(data) +
  aes(x = APY, 
      y = Min_Deposit, 
      label = Institution, 
      col = Category) + 
  geom_text(position = position_jitter()) + 
  labs(x = 'APY', 
       y = 'Minimum Deposit', 
       col = '',
       title = title,
       caption = 'Source: bankrate.com') +
  theme_light() + 
  theme(panel.grid.minor   = element_blank(),
        panel.grid.major.x = element_blank())
  
}

We make the graph interactive with ggplotly() from plotly.

g1 <- apy_v_md(links_df, title = 'APY vs. Minimum Deposits')

ggplotly(g1)

The outliers–i.e., those with a minimum deposit of 10,000 or above–seem to be masking the underlying relationship between APY and minimum deposits. Filtering them out of the graph reveals a downward trend, as well as the observation that the 3- and 5-year CD rates tend to have higher APY and lower minimum deposits

g2 <- links_df %>%
  filter(Min_Deposit < 10^4) %>%
  apy_v_md(title = 'APY vs. Minimum Deposits (Outliers Removed)')

ggplotly(g2)

Conclusion

In conclusion, we web mined bankrate.com using the rvest functions read_html(), html_node(), and html_table(). The results were presented in tables via flextable and graphs via ggplot2 and plotly. I have observed that there is a general inverse relationship between APY and minimum deposits, where 3- and 5-year CD rates tend to have higher of the former and lower of the latter.

Feel free to modify and expand the code presented for your own projects!