Summary
Data is sometimes stored as an Excel spreadsheet. To work with it inR, it must be imported using a package. This project imports an excel spreadsheet of Freddie Mac mortgage rates using the rio and readxl packages.
Table of Contents
Overview
Much of the retrievable data on the web is either stored in tabular form, like a csv, or an Excel spreadsheet. Excel spreadsheets have the extensions “.xls” or “.xlsx”. Packages exist to import an Excel spreadsheets in R. This post illustrates readxl and rio for importing Freddie Mac historical mortgage rate data.
Freddie Mac, a home mortgage processor, provides historical data on mortgage rates in a file with an extension “.xls”. As of January 13, 2022, Freddie Mac’s take on the home market was that mortgage rates have risen over the past week and would “dampen demand in the near future.” They cited rising home prices and more restrictive monetary policy as factors. Freddie Mac, Mortgage Rates Increase Significantly (January 13, 2022).
Background
The readxl package is part of the tidyverse and its documentation may be found here. For additional information, see “R for Data Science” chapter 11: Data Import. The rio package documentation can be found here. The import function works in tandem with readxl so that read_xls arguments can be passed via ....
One challenge in using the readxl package was that the Excel file must be downloaded first to a temporary file. In other packages, a data file can be directly read from a url address. Jenny Bryan, a major contributor to the package, discussed direct import on github.
One workaround, which saved a couple lines of code, was the rio package.
Import Freddie Mac Data
library(readxl)
library(cellranger)
library(rio, quietly = T)
df <- rio::import(file = 'http://www.freddiemac.com/pmms/docs/historicalweeklydata.xls',
which ='Full History',
range = cell_limits(c(8, 1), c(NA, 7)),
col_names = c('date',
'fixed_30_rate',
'fixed_30_points',
'fixed_15_rate',
'fixed_15_points',
'float_arm_rate',
'float_arm_points'
),
col_types = c('date', rep('text', 6))
)Build Data Frame
# build
library(dplyr)
library(tibble)
df.1<-
df |>
dplyr::select(c(date, contains("rate"))) |>
dplyr::mutate(across(-date, as.numeric)) |>
dplyr::mutate(across(-date, ~ round(., 3))) |>
tidyr::pivot_longer(-date, names_to = 'mortgage') |>
dplyr::mutate(date = as.Date(date, format = "%Y-%m-%d")) |>
na.omit()Plot Mortgage Data
# plot
library(highcharter)
hc <- df.1 |>
hchart("line", hcaes(x = date,
y = value,
group = mortgage,
)) |>
hc_title(
text = "US Historical Mortgage Rates"
) |>
hc_subtitle(
text = "Source: Freddie Mac"
) |>
hc_xAxis(title = list(text = '')) |>
hc_yAxis(title = list(text = ''),
labels = list(format = "{value}%"))
hcConclusion
Excel data was successfully imported using the readxl and rio package. US bond markets have seen yields rise as the US Federal Reserve tightens monetary policy. Freddie Mac data show mortgage rates have risen as well, but the change is hardly noticeable when viewed from a long term perspective.
References
Disclaimer
The views, analysis and conclusions presented within this paper represent the author’s alone and not of any other person, organization or government entity. While I have made every reasonable effort to ensure that the information in this article was correct, it will nonetheless contain errors, inaccuracies and inconsistencies. It is a working paper subject to revision without notice as additional information becomes available. Any liability is disclaimed as to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause. The author(s) received no financial support for the research, authorship, and/or publication of this article.
Reproducibility
─ Session info 💛 🥏 👊🏽 ─────────────────────────────────────────────────────────────────────────────────────────
hash: yellow heart, flying disc, oncoming fist: medium skin tone
setting value
version R version 4.1.0 (2021-05-18)
os macOS Big Sur 10.16
system x86_64, darwin17.0
ui X11
language (EN)
collate en_US.UTF-8
ctype en_US.UTF-8
tz America/Chicago
date 2022-01-20
pandoc 2.14.1 @ /usr/local/bin/ (via rmarkdown)
─ Packages ───────────────────────────────────────────────────────────────────────────────────────────────────────────
package * version date (UTC) lib source
assertthat 0.2.1 2019-03-21 [1] CRAN (R 4.1.0)
blogdown * 1.4 2021-07-23 [1] CRAN (R 4.1.0)
bookdown 0.23 2021-08-13 [1] CRAN (R 4.1.0)
bslib 0.3.1.9000 2021-11-14 [1] Github (rstudio/bslib@cc24a62)
cachem 1.0.6 2021-08-19 [1] CRAN (R 4.1.0)
callr 3.7.0 2021-04-20 [1] CRAN (R 4.1.0)
cli 3.1.0 2021-10-27 [1] CRAN (R 4.1.0)
codetools 0.2-18 2020-11-04 [1] CRAN (R 4.1.0)
colorspace 2.0-2 2021-06-24 [1] CRAN (R 4.1.0)
crayon 1.4.2 2021-10-29 [1] CRAN (R 4.1.0)
DBI 1.1.1 2021-01-15 [1] CRAN (R 4.1.0)
desc 1.4.0 2021-09-28 [1] CRAN (R 4.1.0)
devtools * 2.4.2 2021-06-07 [1] CRAN (R 4.1.0)
digest 0.6.28 2021-09-23 [1] CRAN (R 4.1.0)
dplyr 1.0.7 2021-06-18 [1] CRAN (R 4.1.0)
ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.1.0)
evaluate 0.14 2019-05-28 [1] CRAN (R 4.1.0)
fansi 0.5.0 2021-05-25 [1] CRAN (R 4.1.0)
farver 2.1.0 2021-02-28 [1] CRAN (R 4.1.0)
fastmap 1.1.0 2021-01-25 [1] CRAN (R 4.1.0)
fs 1.5.0 2020-07-31 [1] CRAN (R 4.1.0)
generics 0.1.1 2021-10-25 [1] CRAN (R 4.1.0)
ggplot2 * 3.3.5 2021-06-25 [1] CRAN (R 4.1.0)
ggthemes * 4.2.4 2021-01-20 [1] CRAN (R 4.1.0)
glue 1.5.0 2021-11-07 [1] CRAN (R 4.1.0)
gtable 0.3.0 2019-03-25 [1] CRAN (R 4.1.0)
highr 0.9 2021-04-16 [1] CRAN (R 4.1.0)
htmltools 0.5.2 2021-08-25 [1] CRAN (R 4.1.0)
jquerylib 0.1.4 2021-04-26 [1] CRAN (R 4.1.0)
jsonlite 1.7.2 2020-12-09 [1] CRAN (R 4.1.0)
knitr 1.36 2021-09-29 [1] CRAN (R 4.1.0)
labeling 0.4.2 2020-10-20 [1] CRAN (R 4.1.0)
lifecycle 1.0.1 2021-09-24 [1] CRAN (R 4.1.0)
magrittr 2.0.1 2020-11-17 [1] CRAN (R 4.1.0)
memoise 2.0.0 2021-01-26 [1] CRAN (R 4.1.0)
munsell 0.5.0.9000 2021-10-19 [1] Github (cwickham/munsell@e539541)
pillar 1.6.4 2021-10-18 [1] CRAN (R 4.1.0)
pkgbuild 1.2.0 2020-12-15 [1] CRAN (R 4.1.0)
pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.1.0)
pkgload 1.2.3 2021-10-13 [1] CRAN (R 4.1.0)
prettyunits 1.1.1 2020-01-24 [1] CRAN (R 4.1.0)
processx 3.5.2 2021-04-30 [1] CRAN (R 4.1.0)
ps 1.6.0 2021-02-28 [1] CRAN (R 4.1.0)
purrr 0.3.4 2020-04-17 [1] CRAN (R 4.1.0)
R6 2.5.1 2021-08-19 [1] CRAN (R 4.1.0)
remotes 2.4.1 2021-09-29 [1] CRAN (R 4.1.0)
rlang 0.4.12 2021-10-18 [1] CRAN (R 4.1.0)
rmarkdown 2.11 2021-09-14 [1] CRAN (R 4.1.0)
rprojroot 2.0.2 2020-11-15 [1] CRAN (R 4.1.0)
rstudioapi 0.13 2020-11-12 [1] CRAN (R 4.1.0)
sass 0.4.0 2021-05-12 [1] CRAN (R 4.1.0)
scales 1.1.1 2020-05-11 [1] CRAN (R 4.1.0)
sessioninfo 1.2.1 2021-11-02 [1] CRAN (R 4.1.0)
stringi 1.7.5 2021-10-04 [1] CRAN (R 4.1.0)
stringr 1.4.0 2019-02-10 [1] CRAN (R 4.1.0)
testthat 3.1.0 2021-10-04 [1] CRAN (R 4.1.0)
tibble 3.1.6 2021-11-07 [1] CRAN (R 4.1.0)
tidyselect 1.1.1 2021-04-30 [1] CRAN (R 4.1.0)
usethis * 2.1.3 2021-10-27 [1] CRAN (R 4.1.0)
utf8 1.2.2 2021-07-24 [1] CRAN (R 4.1.0)
vctrs 0.3.8 2021-04-29 [1] CRAN (R 4.1.0)
withr 2.4.2 2021-04-18 [1] CRAN (R 4.1.0)
xfun 0.28 2021-11-04 [1] CRAN (R 4.1.0)
yaml 2.2.1 2020-02-01 [1] CRAN (R 4.1.0)
[1] /Library/Frameworks/R.framework/Versions/4.1/Resources/library
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────