Relative commodity prices in recession, part II
Written by Jason on September 22, 2011Similar to the first part of this post, we're looking at the historical prices of aluminum and gold. However, this time, instead of looking at the ratio between the prices of the two, we'll examine each of their year-over-year changes.
Keep reading to learn how to compute rates of change over an arbitrary period (daily, weekly, monthly, etc., or custom measures like 58 days).
Before we get to the filter and report, let's take a moment to discuss two different ways of getting a historical value so we can compute rates of change. The lookup function allows you to specify a single period (e.g., exactly 180 days ago), while the back_mean function allows you to use an average value as the base (e.g., the average value between 178 and 181 days ago).
When there is only one value in the period, the back_mean function will provide the same result as the lookup function, and in fact, you'll see both methods used below give the same output.
In the table above, we're using this filter:
Date>="Jan 1, 1990"Date<"Jan 1, 2010"
With this report:
DateAluminum/lookup(Aluminum,1,Date,concat(year(Date)-1,"-",month(Date),"-01"))-1Aluminum/back_mean(Aluminum,370,360,Date,1)-1
The lookup function is retrieving the aluminum price in the prior year, with the date being built piecemeal by concatenating the desired year, month and date, separated by dashes. It is taking the year of the current date less one, the month of the current date, and the first day of the month.
The back_mean function is getting the mean price of aluminum between 360 and 370 days ago (excluding the 360th day, including the 370th). Since the data is monthly, we're only picking up the price one year ago. In our case, the Date field is specifying the dimension to "look back" upon. In addition to dates and times, you can also translate along numbers. The final "1" indicates that the lookback should use all of the data in the dataset; with a "0", only the filtered data would be part of the average.
Moving on to the chart. Our filter is the same as the one above, and the report is:
Date(using the custom format ":mmm yyyy" - i.e., the one seen in part I)Aluminum/lookup(Aluminum,1,Date,concat(year(Date)-1,"-",month(Date),"-01"))(current Aluminum price divided by the price in the prior year)Recession_US(US recession status, as a 0 or 1, with 1 being in recession. This series is plotted on the right y-axis.)Recession_Japan(also plotted on the right axis)
That's all there is to finding rates of change. Please let us know if you have any questions
