Posted in R

Oil Price Trend in South African Rands – R

Rplot.pngSouth Africa has recently being experiencing some hikes in the petrol price. We're paying more for petrol now that oil is under $50 a barrel then we were when it was over a $100 a barrel.

We haven’t been able to take advantage of the low oil prices because our currency has weakened considerably since last year.

Below is an R script for plotting the oil price in ZAR using the Quandl package for exchange rates and oil prices. The output shows that, even factoring in the weakened currency, our petrol price should look better than it did in 2014. This gives an indication of the effect of taxes and levies on the petrol price, and the risk that the country is in if the oil price goes back to 2014 levels.

First the packages

Quandl has a great repository of free and paid data sets, which can be consumed via Python, R, Excel, and even through your browser. I used this package to source historic exchange rates and oil prices.

The Lucr package accesses more recent exchange rate information. It uses the Open Exchange Rates site. You need a key. Luckily there’s a free option.

The ggplot2 package provides the graph plotting.


#install.packages("Quandl")
#install.packages("lucr")
#install.packages("ggplot2")

library(Quandl)
library(lucr)
library(ggplot2)

Get the exchange rates


lucr.key <- "GeTyOurOwNkEy"

###Get Historic exchange data
stg.daily.exchange.rates <- Quandl("FRED/DEXSFUS")

#Add latest exchange rate
stg.latest.exchange.rate <- currency_convert(1, from = "USD", to = "ZAR", key = lucr.key)

latest.exchange.rate <- data.frame(Sys.Date(),stg.latest.exchange.rate)
names(latest.exchange.rate) <- c("DATE","VALUE")

stg2.daily.exchange.rates <- rbind(stg.daily.exchange.rates, latest.exchange.rate)

daily.exchange.rates <- stg2.daily.exchange.rates[order(stg2.daily.exchange.rates$DATE),]

str(daily.exchange.rates)

remove(stg.daily.exchange.rates)
remove(stg2.daily.exchange.rates)

Get the oil price in USD

 
###Get historic oil prices
stg.oil.prices <- Quandl("CHRIS/CME_CL1") #Quandl("OPEC/ORB")
stg.oil.prices <- data.frame(stg.oil.prices$Date,stg.oil.prices$Last)
names(stg.oil.prices) <- c("Date","Value")

#Carry latest oil price to latest exchange rate

stg.oil.price.carryforward <- data.frame( latest.exchange.rate$DATE,
                                          stg.oil.prices[stg.oil.prices$Date == max(stg.oil.prices$Date),2]
                                          
)
names(stg.oil.price.carryforward) <- c("Date","Value")

oil.prices <- rbind(stg.oil.prices, stg.oil.price.carryforward)

remove(stg.oil.price.carryforward)
remove(stg.oil.prices)

Because Quandl exchange rates for South Africa are a bit behind (about a week),
I took the latest currency from lucr and applied that to the latest oil price
that I received from Quandl (the previous day).


###Oil Price in ZAR
stg.oil.prices.ZAR <- merge(x = oil.prices, y = daily.exchange.rates, by.x = "Date", by.y = "DATE")

names(stg.oil.prices.ZAR) <- c("Date", "Oil.Price","ZAR.per.Dollar")

oil.prices.ZAR <- data.frame(
  stg.oil.prices.ZAR$Date,
  stg.oil.prices.ZAR$Oil.Price * stg.oil.prices.ZAR$ZAR.per.Dollar,
  stg.oil.prices.ZAR$Oil.Price,
  stg.oil.prices.ZAR$ZAR.per.Dollar
)
remove(stg.oil.prices.ZAR)
names(oil.prices.ZAR) <- c("Date","Oil.Price.ZAR","Oil.Price.USD","Exchange.Rate")


Here’s a sample set of rows:
Capture
Then we’ll use the ggplot2 library to plot our graphs:

display.from.date <- "2005-01-01"

display.oil.prices.ZAR.long <- oil.prices.ZAR[oil.prices.ZAR$Date >= display.from.date,]

display.from.date <- "2013-01-01"

display.oil.prices.ZAR.short <- oil.prices.ZAR[oil.prices.ZAR$Date >= display.from.date,]


qplot(x = display.oil.prices.ZAR.long$Date, y = display.oil.prices.ZAR.long$Oil.Price.ZAR,
      xlab = "Date", ylab = "Oil Price per Barrel (ZAR)", main = "Oil Price Over Time",
      data = display.oil.prices.ZAR.long,
      colour = -Exchange.Rate,
      geom = "line")


qplot(x = display.oil.prices.ZAR.short$Date, y = display.oil.prices.ZAR.short$Oil.Price.ZAR,
      xlab = "Date", ylab = "Oil Price per Barrel (ZAR)", main = "Oil Price Over Time",
      data = display.oil.prices.ZAR.short,
      colour = -Exchange.Rate,
      geom = "line")

The “long” plot

Rplot

The “short” plot

Rplot01

It looks like levies and taxes are playing a large part on the petrol price. Even with the South African Rand’s weak rate, we’re in a better position than we were in 2014.

Next, I’ll take advantage of PowerBI’s R capabilites by using the R script as a data layer, and leveraging PowerBI’s visuals to communicate the information.

 

Advertisements

Author:

I'm a Business Intelligence Developer working for a financial services company. My focus is on the Microsoft suite of BI tools.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s