![xlminer analysis toolpak. xlminer analysis toolpak.](https://i1.wp.com/theleansixsigmaoffice.com/wp-content/uploads/2016/11/GoogleSheets_002.png)
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. Then you can run that formula against the historical data to estimate what those basis levels would have been.
Xlminer analysis toolpak. 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.
Xlminer analysis toolpak. 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? 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.
![xlminer analysis toolpak. xlminer analysis toolpak.](https://blog.sheetgo.com/wp-content/uploads/2018/09/Screen-Shot-2018-09-03-at-6.27.12-PM-1024x532.png)
![xlminer analysis toolpak. xlminer analysis toolpak.](https://www.solver.com/sites/default/files/GettingAddons1.png)
One of the first resources I found in my quest to quantify the local basis market was this dataset from the University of Illinois.