In this post we provide an example of how we can use Power BI to analyze real-world datasets. Most of the data an analyst will see is not report-ready, unlike what is often shared in tutorials. So, instead of presenting another “how to” post, we wanted to share a “what is possible” post.
Those who work in energy (oil, gas, power, coal, nuclear or renewables) are most likely familiar with the U.S. Energy Information Administration (“EIA”). It is an incredible source of information – data, prices and reports. Speaking from experience, working with the data can be time consuming. Thanks to the capability of Power BI, we can now connect directly to the Excel files on the website. Full disclosure – we did have to go directly into the website code, rather than using a straight forward web query. If you use a web query, you will be able to pick up what is currently posted but will miss the historical data.
To begin, we have presented a small report for a couple of reasons. First, and most importantly, given the amount of data available, it is easy to want to add one more element, then another, and so on. Knowing myself, I would keep going and never publish anything. The second reason is to demonstrate (in upcoming posts) the ease of building upon an existing report.
Power BI Report
To begin, we will look at U.S. production, as it has been getting considerable attention. Currently, the U.S. is producing 11.9 million barrels per day (“MM bpd”) as can be seen in Figure 1.
At the top left, we have added current production, as this is the item of interest. Below this we have calculated (via DAX expressions) the percentage change for the week, month and year. As recent production changes are minor, we have included the actual amounts (again using DAX expressions) on the bottom of the page.
Most of the acreage on this page is dedicated to the trend in U.S. field production. From it we can easily see the dramatic reversal of decades of production declines, beginning in 2011. From the graph, it is easy to see when the nut was cracked on fracking oil, the 2015-2016 industry slowdown and the rapid increase to new historical production heights.
Figure 1: U.S. Field Production
Much like any report, presenting one bit of information, often leads to more questions. For us, that next question was the source of production. We have added this data in in Figure 2. In the same file as above, the EIA provides production from the “Lower 48 States” and Alaska. We would note that while this information is useful, production from Petroleum Administration for Defense Districts (“PADD”) offers additional insights. This is an element we will be adding in upcoming releases.
In Figure 2, we have broken out current week production, by area, in the top left. Below it we illustrate the total weekly production and the contribution from the Lower 48 States and Alaska. It should be no surprise that production from the Lower 48 dominates. To add some perspective, we have calculated (via DAX) the percentage contribution from each area on the right side of the page. What stands out to us is the continued decline in Alaska production. It is obvious to see fracking for light oil has not taken off in Alaska like it has in the Bakken or the Permian. Again, this brings up more questions. Is it reservoir or access? Our money is on access – moving crews around Midland, Texas is considerably easier than in the shadow of Mt. McKinley (Denali).
Figure 2: U.S. Field Production by Area
Since U.S. production continues to increase, it is only logical to wonder what is happening to imports. This is addressed in Figure 3. The graph illustrates the change in imports due to increased field production. We would note the raw data for imports (grey line) is quite choppy, so we have superimposed a 90-day moving average (using DAX) to reduce noise (dark blue). It is easy to see the decline in imports, although the decline did begin before the rapid increase in field production and has leveled off in the past few years. Since it was available, we have included the amount of weekly imports by PADD on the left of the page.
It should be no shock to anyone where the bulk of imports originate. Here we only have presented a point in time estimate. While useful, it does make us wonder about the direction of imports for individual PADDs.
Figure 3: U.S. Field Production and Imports
To answer these questions, we have presented PADD imports over time in Figure 4.
Trying to include all data on one graph (even with moving averages) made it impossible to decipher. Therefore, we added a slicer where you could select a PADD of interest. Even though aggregate volumes have leveled off, when you look at individual PADD data you begin to get a more fulsome picture. Illustrated in Figure 4 are imports from PADD 2, the Midwest.
Go to the attached Power BI report to look at individual PADDs.
The most significant decrease is observed in PADD 3, where imports decreased by ~65% since 2005. These volumes look to have been replaced by imports from PADD 4 (Rocky Mountains), which began to increase around the same time. Of note to Canadians will be the 200% increase in imports from PADD 2 (Midwest) beginning in 2010. No surprise where those volume originated.
Figure 4: PADD Imports of Commercial Crude
We will end it there, for now. Reviewing our current report, it naturally lends itself to wanting to dig a little deeper. Due to the ease of maintaining this report (hit ‘Refresh’) we can now focus on building additional depth. Before Power BI maintaining this report would have been a labour-intensive exercise; only increasing with each additional element added. “Look at all the additional work I’ve created for myself!” Little wonder this information was purchased, rather than built in house! What’s a Bloomberg terminal these days? $U$2,500 per month? We’ll let you do the math on the savings that can be achieved.
The importance of this bears repeating – Once constructed, reports are low maintenance. Going forward, analysts can spend their time, analyzing and adding to the report, rather than stressing about the work they’ve created for themselves.
I would like to add two additional pieces of information which further illustrate the benefits:
i. First, and most importantly, we now have pushed the responsibility of updating the data to the EIA.
ii. Secondly, refreshing a report can be automated to coincide with weekly updates. This becomes increasing relevant as the number of reports you manage increases. Once set, these reports can truly become hands off.
If you have any questions on how Power BI can increase your efficiency in financial modelling, reporting, data analysis, or if you would like to have free consultation, please contact us at (403) 361-1754 or go to our Contact page and select Finance in the “How Can I Help You?” drop down menu.