In this post, I’ll show you how to use Analytic Solver Platform in Excel 2013 with Power BI to build a time series analysis model using data from the cloud. Together, Analytic Solver Platform and Power BI provide a powerful, easy-to-use predictive analytics platform at a fraction of the cost of alternatives. A new release of Analytic Solver Platform is coming later this week! Visit solver.com to download a free trial or purchase.
My goal is to forecast Gross Domestic Product (GDP) in the United States. Historical GDP is available for free on Windows Azure Marketplace, so our first step is to subscribe to the data feed.
- Go to http://datamarket.azure.com and sign in using your Windows ID.
- Go to the Real GDP Per Capita By State data set and subscribe to it. The subscription is free.
If you look around you will find a number of interesting data sets.
Now we’d like to bring this data into Excel so we can work with it. Power Query, a free Excel add-on provided by Microsoft, makes this extremely easy. If you use Excel and do not know about Power Query, it is definitely worth your time!
- Download and install Microsoft Power Query for Excel here.
- Start Excel and click on the Power Query tab.
- Select “From Other Sources” and then “From Windows Azure Marketplace”:
- The Navigator task pane will open on the right hand side. Your Windows Azure Marketplace subscriptions will be listed, including Real GDP Per Capita By State:
- Expand the Real GDP Per Capity By State entry and click on its only sub-item, called RealGDPByStatePerCapital [sic].
- The Query Editor window is displayed. You should see GDP information by state as well as rolled up to the US:
- Let’s focus on national GDP. Click on the Area column header, uncheck “Select All” to clear all entries and scroll down to United States (screenshot below). Click OK.
- Click Apply and Close. A new worksheet with national GDP data will be created by Power Query.
These steps only scratch the surface of Power Query. For one thing, Power Query supports a number of interesting data sources, as you may have noticed in the menu in step 3. For another, Power Query lets you easily join and clean data, loading it into worksheets, or PowerPivot data models.
Now that we’ve got the data into Excel, we can use XLMiner to run a time series forecast.
- Click on the XLMiner ribbon tab.
- Under Time Series, select ARIMA -> ARIMA Model.
- Enter the following options (and check out the screenshot below):
- Choose RealGDPPerCapital as the “Selected Variable”. This is what we want to forecast.
- Select Year as the Time Variable.
- Set Autoregressive (p) = 4, Difference (d) = 0, Moving average (q) = 1.
- Click Advanced and:
- Check “Fitted values and residuals”. This will show the error in the time series analysis in the report.
- Check “Produce forecasts” and change “Number of forecasts” to 5.
- Click OK.
- Click OK. XLMiner produces an output worksheet, a residuals worksheet, and a stored model worksheet [which can be used for scoring].
- We want to look at how well the ARIMA model performed. Click on the ARIMA_Residuals worksheet and compare the Actual Value and Fitted Value columns. Not too shabby!
If I explore the tables and charts in the output worksheets, I can explore other aspects of the model results, including the forecasted GDP values.
Once you get going, you’ll find that it’s really easy to build cool predictive models using Power Query and Analytic Solver Platform. In the coming days I will share some other interesting scenarios.