exchange_rates.R

library(httr); library(jsonlite); library(buffer); library(DBI)

# Make the request
req <- GET("https://openexchangerates.org/api/latest.json?app_id=xxxxxxxxxxxxxxxxxx")

# Get the status
http_status(req)

# Get the content in the request
content <- content(req)

# Create a data frame
date <- as.POSIXct(content$timestamp, origin="1970-01-01")
base <- content$base
currencies <- as.data.frame(content$rates)

rates <- as.data.frame(t(currencies))
rates$date <- date
rates$base <- base

colnames(rates) <- c('rate_to_usd', 'date', 'base')
rates$currency <- rownames(rates)
rownames(rates) <- NULL

###########################################################
## Get country codes
###########################################################

library(rvest); library(stringr); library(tidyr)

# We use the read_html function to read a web page.
url <- "http://www.nationsonline.org/oneworld/country_code_list.htm"
webpage <- read_html(url)

# Scrape the webpage
table <- webpage %>%
  html_nodes("#codelist td") %>%
  html_text()

# Create data frame
country_codes <- data.frame(matrix(unlist(table), nrow=247, byrow=T))
colnames(country_codes) <- c('drop','country','country_code','drop1','drop2')

# Drop unneeded columns
country_codes$drop <- NULL
country_codes$drop1 <- NULL
country_codes$drop2 <- NULL

# Change to character types
country_codes$country <- as.character(country_codes$country)
country_codes$country_code <- as.character(country_codes$country_code)

currency_url <- "http://www.science.co.il/International/Currency-codes.php"
currency_page <- read_html(currency_url)

# Scrape the webpage
currencies <- currency_page %>%
  html_nodes(".sortable td , th") %>%
  html_text()

currency_codes <- data.frame(matrix(unlist(currencies), nrow=246, byrow=T))
colnames(currency_codes) <- c('country','capital','currency_name','currency_code')

currency_codes <- currency_codes %>% select(country, currency_code)

# Change to character types
currency_codes$country <- as.character(currency_codes$country)
currency_codes$currency_code <- as.character(currency_codes$currency_code)

# Join the two
country_codes <- country_codes %>%
  left_join(currency_codes, by = 'country')

names(country_codes) <- c('country','country_code','currency')

###########################################################
## Join tables
###########################################################


# Join them into our rates data frame
rates <- rates %>% 
  left_join(country_codes, by = "currency")

# Put it in a redhift table
insert_batch(con, "exchange_rates", rates)