![xlminer analysis toolpak xlminer analysis toolpak](https://i.ytimg.com/vi/ugSzEU5hXLY/maxresdefault.jpg)
If your queries are routinely taking several minutes, set a timer when you click OK and know that if your output doesn’t appear within 6 minutes you’ll need to reduce the input data or use Excel. So be patient, but keep in mind there is a hidden 6 minute timeout for queries (this not shown anywhere in the UI, I had to look in the developer console to see the API call timing). There’s no indication that the analysis is running, and you won’t even see a CPU increase on your computer as this runs in Google’s cloud. ⚠️ One quirk with the Google Sheet version- after setting your input and output cells and clicking the OK button, nothing will happen. I also created a “Days Until Delivery” field (Column M) that could help it spot relationship changes that may occur as delivery approaches.Īlways keep in mind: what matters in this model isn’t what happens to the actual basis, but the difference between the local basis and what is reported in the regional data. To account for local-to-regional changes that could occur through a marketing year, I generated Column L to account for this, and since anything fed into this analysis needs to be numerical, I called it “Delivery Months after Sept” where December is 3, March is 6, and July is 10. 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. 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.
![xlminer analysis toolpak xlminer analysis toolpak](https://lh3.googleusercontent.com/-FzjPldq9JpU/WO0QX2Mj1_I/AAAAAAADJiE/fr4Kis-f370/image_thumb7.png)
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.
![xlminer analysis toolpak xlminer analysis toolpak](https://www.openasapp.com/wp-content/uploads/2020/02/XLMiner-screenshot.png)
It provides historical basis and cash price data for 3 futures months in 7 regions of Illinois going back to the 1970s. The add-on's Random Number Generation function uses the Mersenne Twister algorithm.One of the first resources I found in my quest to quantify the local basis market was this dataset from the University of Illinois. Results will generally match the Excel Analysis ToolPak, as seen in many textbooks, screen images and videos. T-Test: Two-Sample Assuming Unequal Variances T-Test: Two-Sample Assuming Equal Variances Sidebar input fields and output on the spreadsheet will be very familiar to Excel Analysis ToolPak users.
XLMINER ANALYSIS TOOLPAK PLUS
The add-on provides all 19 interactive functions included in the Excel Analysis ToolPak, plus a 20th function often requested by users, logistic regression. With the XLMiner Analysis ToolPak Add-on, you can easily perform statistical analyses of the same types available in the Analysis ToolPak add-in that has been part of Microsoft Excel for over 20 years, and has been featured in large numbers of textbooks, videos, statistics courses and the like.