South 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:
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
The “short” plot
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.