Crude Oil Mispricing Model – Commodity Prices and a study in 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.
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 eia.gov.
- Gold Spot Price Data from www.onlygold.com. 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 oil||Fuel oil, No. 2 NY gal.|
|Diesel Fuel||Diesel Fuel, 500 ppm S, NY harbor low sulfur|
|Natural Gas||Natural gas Henry Hub, $ per MMbtu|
|Gold||Gold: American Eagle, troy oz.|
|Silver||Silver: London fixing, spot price, per troy oz (expressed in GBP)|
|Platinum||Platinum: free market|
|Aluminum||Aluminum, LME $ per metric ton|
|Steel||St. Steel scrap, US, $ per gross ton-D|
|Copper||Copper, high grade: Comex spot price $ per lb.|
|Cotton||Cotton, 1 1/16 strand lw-md Mmphs, per lb|
|Corn||Corn, No. 2 yellow. Cent. Ill. Bu|
|Wheat No.1||Wheat, No. 1 soft white, del Portland, Ore|
|Coffee||Coffee, Colombian, NY lb.|
|Sugar||Sugar, cane, raw, world, lb. fob|
|Corn oil||Corn oil, crude wet/dry mill|
|Soybean oil||Soybean 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 (www.oanda.com) 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:
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:
and review the following article:
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.