Crude price is at an all-time low. It has been falling since 2012 but the car owners perception is that petrol prices don’t always accompany this fall.
For this analysis, I used crude and dollar CSV data sets you can find with a simple google search and my car’s consumption data set that I have used in other blog posts.
By the way, you can learn how to use CSV files in this post.
Let’s load the data in Viur and let’s see what we can do with it.
The first thing that I want to see the Crude Price evolution.
Impressive rise and fall! Now I’m going to check the price per liter to a regular car owner just like me. This is a bit more tricky because it requires calculations, so I’m going to jump in from the Drag & Drop to the SQL code editor.
SELECT date_trunc('MONTH', "Date"), AVG("Total price"/"Quantity") AS "€/l" FROM "Fuelings" GROUP BY date_trunc('MONTH', "Date")
For us Europeans one of the things that has an impact on gas prices is the currency value. The crude price is in Dollars and the Diesel price per liter is in Euro. I have to normalize this to compare, so in the next chart, I’ll take the exchange rate into consideration to put everything in dollars. To have a tighter chart and knowing that a barrel of oil has approximately 159 liters I’ll also reduce the price of a barrel to the price of a liter.
SELECT date_trunc('MONTH', e."Date") AS "Date", AVG(e." ECB reference exchange rate (US dollar/Euro)") AS "(1€ in $US )", AVG(c." Dollars per Barrel")/159 AS "Crude $/l" , AVG( f."Total price" / f."Quantity") * AVG(e." ECB reference exchange rate (US dollar/Euro)") as "Diesel $/l" FROM "Dollar to Euro" e INNER JOIN "Crude Price" c ON (e."Date" = c."DATE") INNER JOIN "Fuelings" f ON ( e."Date" = f."Date" ) GROUP BY date_trunc('MONTH', e."Date") ORDER BY date_trunc('MONTH', e."Date") ASC
Let’s zoom in on the chart to have a closer analysis.
The crude price is more steady and the diesel price accompanies it even though it has more abrupt raises, maybe amplified by the dollar valuation. Another interesting insight is that the Dollar to Euro exchange rate mimics the crude price evolution.
Any thoughts on this? Let us know what you think in the comment box.
Viur is Business Intelligence made easy.