houses_lending.R

library(readxl)
library(lubridate)
library(dplyr)
houses <- read.csv("~/Downloads/raw_data/AU.csv", stringsAsFactors = FALSE)
houses$Date <- ymd(houses$Date)
hs <- houses %>% arrange(CODE, Date) %>% group_by(CODE) %>%
  mutate(new_houses = Stock - lag(Stock)) %>% ungroup() %>%
  mutate(new_value = new_houses * Median_SP) %>% group_by(Date) %>%
  summarise(amount_spent = sum(new_value, na.rm=TRUE))
# amount_spent is an esitmated value of the amount of new housing for
# which there is no exisiting borrowing to pay for it

# Reserve bank lending to housing by banks and other institutions
# http://www.rbnz.govt.nz/statistics C5
lending <- read_excel("~/Downloads/hc5.xls", skip=4)
lending$month <- month(lending$`Series Id`)
mort <- lending %>% filter(month %in% c(3,6,9,12)) %>%
  select(`Series Id`, CRDS.MALP1) %>%
  mutate(amount_borrowed = (CRDS.MALP1 - lag(CRDS.MALP1))*1000000)

# now we compare the added value in houses with the amount of lending
# from banks and other institutions to pay for it

hs$amount_borrowed <- mort$amount_borrowed[10:106]
hs$not_borrowed <- hs$amount_spent - hs$amount_borrowed


plot(hs$Date, hs$not_borrowed, type="l", frame.plot=FALSE, ylab="Dollars", xlab="Date",
     main="Quarters with the black line above red\nare impossible for NZ economy to have bought the houses")
abline(h=0, col="red")
legend("topright", lwd=1, col=c("red","black"),
       legend=c("borrowing equals lending", "acutal above/below"), bty="n")