To keep things simple starting out, I decided to make one model that includes December, March, and July rather than treat each contract month as distinct models. You’ll want to reformat the data with each region’s basis level for a given contract in separate columns (Columns E-K below), then a column for your locally observed values on/near those same dates (Column N). Here‘s a helpful YouTube tutorial for using the XLMiner Toolpak in Google Sheets and understanding regression theory, and here’s a similar video for Excel. We can do this using Frontline Solver’s popular XLMiner Analysis Toolpak for Excel and Google Sheets.
![cougarbyte frontline solver xlminer cougarbyte frontline solver xlminer](https://store-images.s-microsoft.com/image/apps.56271.dda126ef-5dd2-44ef-b6bc-a6cb0309f3a1.5b43104b-37e9-4650-8c16-71e032e6cb7d.98e46225-4022-4ac8-acfd-a01521318690.png)
Then you can run that formula against the historical data to estimate what those basis levels would have been.
#COUGARBYTE FRONTLINE SOLVER XLMINER SERIES#
If you’re unfamiliar with regression analysis, essentially you provide the algorithm a series of input variables (basis levels for the 7 regions of Illinois) and a single output to find the best fit to (the basis level of your area) and it will compute a formula for how a+b+c+d+e+f+g (plus or minus a fixed offset) approximates y. While I won’t save myself any money on a basis data purchase, I can at least test the theory to see if it would have worked and give others an idea for the feasibility of doing this in their own local markets.
#COUGARBYTE FRONTLINE SOLVER XLMINER FULL#
I ended up buying historical data for my area before I could full test and answer this. This got me thinking- what if I had the actual weekly basis for my local market for just a year or two and combined that with the U of I regional levels reported for the same weeks? Could I use that period of concurrent data to calibrate a regression model to reasonably approximate many more years of historical basis for free?
![cougarbyte frontline solver xlminer cougarbyte frontline solver xlminer](https://pbs.twimg.com/media/EhjxXsyXsAEzKH8.jpg)
Perhaps I’m on the edge of two or three regions, I thought. On its own, I haven’t found any region in this dataset to be that accurate for my location. It provides historical basis and cash price data for 3 futures months in 7 regions of Illinois going back to the 1970s.
![cougarbyte frontline solver xlminer cougarbyte frontline solver xlminer](https://pbs.twimg.com/media/EiSQKlFU4AAHgxo.jpg)
One of the first resources I found in my quest to quantify the local basis market was this dataset from the University of Illinois.