Forecasting Using Excel 2013 Client

A very interesting topic is predictive analytics and within that, forecasting. In this article we will see how to use Excel 2013 as a client and the SQL Analysis Service as a server to do data mining activities. Excel does not have the analysis capability built in. It relies on the Analysis Server to provide those.

 

Prerequisites

• Apart from the SQL Server Analysis Server and Excel 2013, you will need to install the Microsoft SQL Server 2012 SP1 Data Mining Add-ins for Microsoft Office.
  
  
• Then you need to right-click the properties of your Analysis Server and change the DataMining\AllowSessionMiningModels setting to True.
  
  
  
  
• Create a blank database on the Analysis Server.
  
  
• If the addin is installed correctly you will see the change in Excel menu items as below.
  
  
  

Now let's see an example of forecasting on a stock price. For my example, I have downloaded a stock's data from the NSE in CSV file and imported into Excel.

 

Step 1: Select data and click on the menu item "Insert --> Table" and convert it to a table.

 

 

Step 2: Once you select OK, you will see the "Analyze" Menu in the "Table Tools" as below.

 

 

Step 3 Create a Connection by clicking on the "No Connection" button from the ribbon. 

 

Step 4: Once the connection is established, click on the Forecast menu. Choose the column you want to forecast on. Let's Select Close Price. Select the number of units. I have selected 25 future days. Click Ok.

 

 

Step 5: The Analysis service will run the algorithm of detecting patterns in the background and provide us with a graphical result as shown below.

 

 

 

Step 6: The values are also added to the original sheet in another colour.

 

 

This is one simple example of data mining that can be done from an Excel 2013 client. There are many other menu items in this plugin that can be used based on business requirements.