Commodity Prices Excel File

The goal of this site is to take useful public data series and cram them into easy to use dashboards…I think this one will be useful to a lot of people. We have taken 44 global commodities from the biggies like oil and gas to the critical ones like coffee :)…and even found room for Lamb!!

Above is a screenshot…just select your commodity using the control on the right…select multiples by holding the [Ctrl] button down and clicking on or off. Keep in mind…they will default to the same axis…so if their per unit values are materially different it isn’t going to look good…you can always plot it on the secondary axis, but that takes a few extra clicks. The “Value Type” control in the bottom right corner lets you choose the monthly value, monthly change, trailing twelve month average etc….I think the monthly and the trailing twelve month will get the most use, but the others are there if you need to dig deeper. Finally, most of the series go back to the 1980’s…the bottom control lets you select which years you want to see. I set it to 2000+…hold the [CTRL] button down and click, or unclick to change the time frame if you want.

To see the raw data, the “dashboard table” tab has the selected data, and you can double click on anything to see the underlying record.

Download the file here:Excel-Data-Junkies-Monthly_Commodities_Report (01-2017)

And for the road…Coffee is headed up, but it’s not nearly as bad as 1994…hopefully this neat little dashboard will save you some time if nothing else. Enjoy!!

Climate Data + Database Linked Pivot Tables = Awesome!!

I have been tinkering with the NOAA climate data for years now…and am finally ready to start pushing out some files I think you will find both useful and interesting. We’ve all heard a lot about global warming and climate change over the last 20 years, but have you ever wondered how the climate is changing in your hometown? I wondered that exact thing a few years ago when I started playing with the available climate data. The Excel file provided for free below gives you a simple way to find a station near to you and chart the climatological history of that site.

First…a the history….I stumbled across database linked pivot tables back in 2011. I was in an accounting role, working on some massive global account reconciliations where we had some really big problems following an acquisition and new ERP rollout…..among other things the ERP was creating some erroneous entries in ways that we didn’t really understand. I was already well versed in pivot tables, but the data sets we were working with quickly grew to over the 1M records we could paste in an excel worksheet and then pivot…. So we did the obvious thing…we chopped the data up and soon were dealing with lots of huge files and lots of pivot tables….it worked, but not well, and we were crashing excel on a regular basis.

Then two things happened that changed the path of my career. First, one of our IT guy showed me how I could simply link an excel pivot table to a database….which could hold millions (or billions) of records….solving the first problem of how to break the 1M record barrier. Then I stumbled across an article about 64 bit excel 2010…which allowed Excel to use up to 4GB of RAM while the standard 32 bit excel was limited to 2GB. (Starting with Excel 2013 64 bit Excel can use as much memory as your system has available…8…16…64??) 64 bit excel was a phenomenal game changer primarily because it was extraordinarily stable. I had spent a good chunk of my career trying to avoid crashing Excel…but still it crashed on what seemed like a daily basis. After upgrading to 64 bit Excel 2010, I could count the number of crashes I had on one hand, and every single one of those was me knowingly pushing it and my system to the limit. I could go on, but the bottom line is that with 64 bit excel, and a modern mid-grade computer, we now have a stable tool that can be used by analysts to handle hundreds of millions of records. Yes….you read that right…hundreds of millions….essentially limited by your systems memory, and with a high dollar machine you can get to the billions.

So…how to test? It didn’t take long to put the idea to work inside the corporate accounting group…we had millions of records and we quickly found dozens of ways to utilize our new toy. However, I knew we were just scratching the surface…So I got a bigger computer for home and started playing around with a much bigger data set…the NOAA climate data. I will note that it was an incredible pain in the rear spanning several years in fits and starts, but I have recently finished compiling the daily climate data set. It has over 2.5 billion records covering just over 100,000 climate stations, some going all of the way back to the 1700’s. Can you cram 2.5B records into an excel pivot table? Actually yes you can….but I wouldn’t recommend it…it pretty much brings my high(ish) end I7 to it’s knees….fortunately, with this data set, we can carve it up into useful bits…which is what I hope to do on a regular basis going forward.

And so, I present to you the first of many Climate Data files….Global Average Temps. The process is quite simple. First, we take the daily max and min temperature for every station and calculate the average daily temperature. If either is missing for a given day, the day is dropped. From there, we calculate an average by year, along with a count of data points. This is critical because the majority of sites have at least some missing data here or there… Of the 30k+ sites with temperature data, only a few hundred have 50+ years “perfect” (No missed days) data. Now…let me make this perfectly clear…in no way shape or form do I ever alter or change anything from the original NOAA data set….even if it is blatantly wrong….and there are plenty that are. With 2.5B+ records….all I can do is take the official record….process it (calculating an average for example) and dump it into an excel pivot table/dashboard with some pretty charts. So…if something looks weird…my advice is to double click on that year in the table, which will give you the actual record. If that year has a count of 50 instead of something closer to 365…that’s your problem. In this file…I am pushing out everything. In later files I will isolate only stations/years with perfect, or close to perfect data sets…like maybe 350+ data points or we drop the year….stay tuned for that.

Here is your snapshot of the dashboard. There are 2 ways to find a climate station…the filter boxes in the upper left let you search for a specific station. You can also use the “slicers” to the right of the chart to select a country, then state(if US) then scroll through the station names to find one you may be interested in. To select multiple, you can hold down the [Ctrl] button to select or unselect multiples. Also remember you can clear your slicer selections by clicking on the icon in the top right corner of each….if you can’t find something it’s probably because you have a filter engaged. Selecting multiples will give you the average…just remember this isn’t a statistically sound method for calculating averages for too many reasons to list :). Just glancing at the New York Central Park Station selected in the screenshot above, we can see the variability in the year to year temperatures and the slope of both the 5 year and 20 year averages heading up.

And I know that got a little long….without further ado…your file:

Excel-Data-Junkies-Global_Annual_Average_Temps

It’s  only 17MB…yet contains hundreds of years of data for 30k+ global sites. Enjoy!! Yes…the download is going to be slow….it’s free after all.

Data Download: 02-10-2017 Weekly Petroleum Status Report


For the best usability, download the excel file here: EIA_PETROLEUM_MASTER_FILE(02-10-2017)

If you don’t have Excel or are running an older version, you can view the dashboard using the Excel Web App with the embedded copy below. You will likely want to click the icon in the bottom right corner for full screen, then adjust your browser zoom to fit.

November Natural Gas Files

It’s been a long time since I published this but I’m going to try an make it a monthly thing from here out. The source is the EIA’s monthly data set for gas, which is generally released two months after the end of the month, so this November data was released at the end of January, and December data will be released at the end of February.

So what do we capture??…almost everything. There are 104 data series in the dashboard starting with the big guys…beginning and ending storage, monthly inflows and outflows and some calculated fields off of that like dailies and trailing 12 month averages etc… Then we pull in all of the components that add up…including state by state production and category by category consumption. In all honesty, it’s a bit too much data for one dashboard, but it still fits in a nice <1 MB excel file, so it seemed silly to split it up. The great thing about the dashboard is that all the data is in there, all you have to do is select whichever series you are interested in and the chart just pops up!!

 

I will kick it off with a nice chart that copies something I have been presenting in regards to oil comparing the trailing 12 month inflows to the trailing twelve month outflows.

Here we can see clearly that inflows surpassed outflows around the end of 2014, building the YOY inventories month by month. Fast forward two years and it looks like we are on the cusp with supply and demand just about coming back to balance. Digging in you would see that domestic production is declining, and curiously so is consumption.

One more for the road…here we have the trailing 12 month production from Texas slowly building before topping out mid 2015 and now declining at a rate of about 15% for over a year now.  For reference Texas is about 25% of US production so it will be interesting to see if this decline turns around in the next 6 months or so

I could go on forever…there are over 100 series and data goes back to the 1970’s for some of them. The dashboard gives you the flexibility to chart each for whatever time period you want, or even chart multiple series against each other. Enjoy!!…and let me know if there are any questions.

 

For the best experience download the excel file and open in your desktop version of Excel 2010 or newer. EIA_NATURAL_GAS_MASTER_FILE_11-2016

If you don’t have Excel or are running an older version, you can view the dashboard using the Excel Web App with the embedded copy below. You will likely want to click the icon in the bottom right corner for full screen, then adjust your browser zoom to fit.

Data Right Where It Belongs…In Excel!!