Browse By

Forecasting Oil prices. Commodity Prices and Trailing Correlations

Forecasting oil prices. Trailing correlations

The Crude Oil Mispricing model, presented in MS Excel worksheet format, assesses what the price of crude oil should have been if the historical relationship between crude oil and a given commodity were to have continued into the future. Vice versa, what would the price of a given commodity be if the average correlation between crude oil and a given commodity was maintained over the analysis period?

A comparative study of the average-correlation-simulated time series is made against the actual time series to see whether current price levels are justified or whether there is an argument for commodity mispricing. It is a different approach to forecasting oil prices that uses historical relationships with non crude commodities.

The data used in the analysis is spot price data for the period January 2008 to August 2011. The data was obtained from a number of sources in particular:

  • West Texas Intermediate, Cushing, OK, Spot Prices, expressed in USD for 1 barrel of Crude Oil from
  • Gold Spot Price Data from This price data represents the London P.M. fix spot price which is the price at which the world’s largest size gold purchases and sales are accomplished on any given day. The price is expressed in USD for 1 troy ounce of Gold.
  • Other cash commodity prices from the Market Data resource of the Wall Street Journal. These prices reflect buying and selling of a variety of actual or “physical” commodities in the marketplace. The commodity details are as follows:
Fuel oilFuel oil, No. 2 NY gal.
Diesel FuelDiesel Fuel, 500 ppm S, NY harbor low sulfur
Natural GasNatural gas Henry Hub, $ per MMbtu
GoldGold: American Eagle, troy oz.
SilverSilver: London fixing, spot price, per troy oz (expressed in GBP)
PlatinumPlatinum: free market
AluminumAluminum, LME $ per metric ton
SteelSt. Steel scrap, US, $ per gross ton-D
CopperCopper, high grade: Comex spot price $ per lb.
CottonCotton, 1 1/16 strand lw-md Mmphs, per lb
CornCorn, No. 2 yellow. Cent. Ill. Bu
Wheat No.1Wheat, No. 1 soft white, del Portland, Ore
CoffeeCoffee, Colombian, NY lb.
SugarSugar, cane, raw, world, lb. fob
Corn oilCorn oil, crude wet/dry mill
Soybean oilSoybean oil, crude; Central Illinois lb.

With the exception of silver the remaining commodities, like gold are expressed in US Dollar for a given unit of that commodity. Silver has been expressed in UK pound sterling. In order to make a meaningful comparison between the price of crude oil and the price of silver an adjustment is needed to first convert the Silver spot prices at each data point to USD, using the relevant USD/ GBP exchange rates ( for those dates.

A screen shot of the data sheet is given below:

The analysis begins with a calculation of the historical average correlation over the entire period of study. This is calculated using EXCEL’s correlation function: CORREL(array1, array2) where array1 is the time series data for the given commodity and array2 is the time series data for WTI (Crude Oil).

This historical average correlation is then taken as the correlation that should result for future time periods. Each future time period is a 60-day period. In the worksheet we are reworking commodity prices based on these 60-day trailing moving average correlations for the period April 2011 to August 2011.

Using Excel’s Solver Function we minimize the sum of squared differences between each of these 60-day correlations (trailing correlations) and the historical average correlation calculated earlier by changing the prices of a given commodity. For example if we are looking at the relationship between WTI and Corn Oil, we first keep WTI prices as actual prices and change Corn Oil prices for the mentioned period so that each 60-day trailing correlation for the period is equal to the historical average. Next we keep Corn Oil Prices as the actual prices and change the WTI prices for the mentioned period so that each 60-day trailing correlation for the period is equal to the historical average. The resulting graphical representation of WTI and Corn Oil Actual and Simulated Price Series as derived by the model are given in the following two diagrams:

The graph above shows that there is not much deviation between the actual and simulated prices. On the other hand take the relation between gold and WTI. The resulting graphical representation of WTI and Gold Actual and Simulated Price Series as derived by the model are given below:

In this instance there is much greater variance between the actual and simulated prices indicating that maybe WTI or gold or both are mispriced. On the other hand the reason could be that the commodities are now responding to different drivers and events as compared to what was the case historically. In either event this relationship needs further investigation and assessment.

Our excel file also includes the following calculations:

1. Trailing correlations based on the actual price time series data- both a data series as well as a graphical representation as shown in the screen shot below.

For a more in depth review of how trailing correlations are calculated you may like to review the following post, a part of our on-line course on Correlation:

Correlation –Trailing Correlations

2. Relative price ratios for WTI and Gold against each commodity as shown in the screen shot below for Copper.

For a more detailed review of how relative value analysis is used in assessing commodity prices you may like to purchase our Excel file:

Relative Gold Price Model – Forecasting the price of gold

and review the following article:

Understanding Gold! Short Gold or add to your positions? A look at gold silver ratio and relative value argument

3. Besides the relative value ratios (What can 1 barrel of crude oil buy?) calculated based on actual prices the excel file also presents a revised ratio based on the simulated prices of the commodities (i.e. using the actual price of Crude oil and the simulated price of the other commodity being assessed). The graph below shows the actual and assumed ratios (based on simulated prices) for Wheat;

In the case of Wheat the assumed ratio tracks the actual ratio quite closely indicating that current Wheat prices are in line with the historical correlation with WTI prices.

Our Crude Oil mispricing model is now available for sale. To purchase the Excel file visit the Computational Finance section of our Finance Course Store.

Comodo SSL