US natural gas priced in yuan?? How to combine datasets in a single analysis
Written by Jason on May 18, 2011This will be the first blog post that deals with linking multiple distinct datasets together (though the tutorials and reference guide cover this topic extensively). For this example, we'll show you how to link foreign exchange rates with energy prices to calculate the price of natural gas that the US consumes in Canadian dollars (or roubles, yen, euros, ringgit, or one of dozens of other possibilities).
Conceptually, linking is similar to the vlookup function found in Excel, where you have a field in your primary dataset, and you want to use a field in a separate dataset that can be related to the first dataset by one or more fields. In our case, we have a range of natural gas prices (in US dollars) at different dates, and want to know what the gas prices would have been at each date in the foreign currency.
What we're doing is getting the spot price of natural gas for each month, and multiplying that price by the exchange rate for the month. We keep the price and exchange rate consistent by linking them together on the date field in each dataset.
This is easy to accomplish - linking the two groups together is done in seconds, and pulling the chart together takes a couple of minutes. Keep reading to see how it's done.
First, linking. Go to the Import tab, Manage Data section, and click on Link Groups. Select FXRatesUSDMonthly as the key group in step 1. Select USEnergy as the filter group in step 2. Link the two groups on their respective date fields in step 3. The linking is finished, and now when you go to the filter and report editor using the USEnergy group, you'll see the FXRatesUSDMonthly appear in the field selector popup.
If you don't see these groups, you'll need to add them to your account under the Subscribe tab. Both subscriptions are free.For more information, please refer to the reference section on linking.
Create a filter for the USEnergy group. It contains:
Date>="Jan 1, 2000"
Create a report that contains:
DateNGCitygatePriceNGCitygatePrice*FXRatesUSDMonthly_USDCADall_min(NGCitygatePrice)all_min(NGCitygatePrice*FXRatesUSDMonthly_USDCAD)
The last two lines are the minimum prices over the period for each currency, and create the horizontal line seen above. We changed the labels for the various lines to make them look better.
Select a line chart (we used Line (Fast) -- it's non-interactive, but as the name implies, it renders more quickly) and configure the options as desired - we gave it a title, added a footnote, and customized the line colors -- other than that, the default settings were used.
As an aside, you might be wondering why we looked at US natural gas prices in Canadian dollars. Canada exports NG to the US, and I thought it would be interesting to see how prices received look to a Canadian producer with costs in Canadian dollars.
There have been a number of articles in the news that discuss the depressed nature of Canadian gas production. While they often reference depressed gas prices, I haven't seen one that adjusts for the currency of the producer, which shows that while prices are low in US dollars, on a percentage basis they're significantly higher than the 2000-2002 lows. That's not the case for Canadian producers. (of course, there are other factors involved as well)
