Data Mining and Predictive Analytics Software For Microsoft Excel

Friday Dec 17th 2010 by Vangie Beal

11Ants Analytics turns Microsoft Excel into a data mining and predictive analytics tool for even the most non-technical users.

Software review: When it comes to business applications for data mining and predictive analytics, one thing is certain: Businesses want to investigate and integrate these tools, but understanding how to use them can be a stumbling block.

Vendors continue to offer the traditional assortment of enterprise applications to meet the data crunching needs of businesses of all sizes, but what is interesting to note is that not only is the variety of tools expanding, but the knowledge barrier to use them is getting lower, giving more businesses the opportunity to use technology to guide business decisions.

Also, many new applications are layered on top of popular office productivity software offerings as an "add-in" tool. This makes deployment a simple process. Importing your data is intuitive and reports are easy to customize and understand.


Predictive Analytics for the Masses

For those new to predictive analytics, the most common use in business applications is to use intelligent algorithms that will comb through data and exploit trends.

Let's look at a classic example of retail sales for an ice cream store. By taking your past sales data in addition to weather and temperature details, it is likely that with the right tools you can predict each day's gross revenues based on the day of the week and the temperature.

In this case, predictive analytics can help you determine early on what stock you need to order, how many employees you should have working on a particular day, what sales or promotions on a certain day will increase sales, and so on.

In a nutshell, mining data and using predictive analytics is going to find value in your data and predict what is most likely to happen so you can make better business decisions.


11Ants Does Data Mining with Microsoft Excel

The 11Ants Model Builder, by 11Ants Analytics, is a data mining and predictive analytics tool that is an add-in for Microsoft Excel (2007 or 2010). The benefit is that users who are familiar with Excel will be able to learn how to use the tool faster than what would be required for learning entirely new software.


Getting Started with 11Ants for Data Mining

To start, you will need to download and install the 11Ants Model Builder (a free 14-day trial is offered on the website). Once installed, you can access the tool via the Excel Ribbon so you only need to launch Excel and open your data files to get started. The 11Ants Analytics website also offers a handy guide for preparing your own data sheets.


Data Mining and Predictive Analytics in Four Steps

The tool allows you to mine your own data to find patterns using only four steps — prepare, analyze, predict and report. It is also designed to automate the process of algorithm selection, parameter tuning and reporting. Each step is easily accessed using a separate tab in your 11Ants Model Builder Excel Ribbon.

Prepare: By selecting data columns in your spreadsheet, you choose one column as the target column and others as the input. For example, for sales data you might use season, date and volume as the input and the revenues column as the target. The 11Ants Model Builder will analyze the relationships between the input and the target. The data is then partitioned — or split — into two sets: training and test. Here users familiar with Excel will have little problem with getting the data prepared.

Options in Model Builder allow you to change the target column and adjust the weight between test and train size. Once you have selected options, you select "Prepare Sheets" and you will find your Excel worksheet is now three worksheets: the original plus one for training data and test data.

Analyze Data and Generate Models: In the training data sheet you, can analyze the data by assigning a data type to a column (category, number or date/time) and then choosing the type of prediction. The 11Ants Model Builder offers three types of prediction: predict number, predict category, or rank likelihood. After choosing options you continue by selecting "Start Generating Models."

As this process runs, 11Ants Model Builder is analyzing the data for relationships and generates continuous models looking for the best one. The quality score changes based on the amount and quality of data being analyzed.

You can view quick info about the project, including estimated Input influences, Top 10 and improvement curve. As the process runs, you can watch the quality score — the higher the percentage, the more patterns found in the data. This information is also available through the Manage Tab in the Excel Ribbon.

Predict: When ready, you can build your predictive model using the test spreadsheet. By choosing "Predict" from the ribbon, you can choose your prediction settings and a new sheet will be generated to see how the model works on the test data. Using your test data worksheet, you can choose your model, confirm your input data type, choose a column to output the results, assign a confidence (hi/med/low) to each prediction, and also decide the type of prediction to be reported and compare predictions against known values.

When you click "Predict Now," a new worksheet for the prediction statistics is generated.


Why 11Ants Analytics is Worth a Look

11Ants Analytics says its software is "data mining for the masses" because it is simple enough to be used by individuals and businesses who previously never would have considered mining their own data. For the most part, this is true. The tools, which are easily integrated into Excel, prove users with a fully automated data mining and predictive analysis solution.

Those with little experience will want to view the online video tutorials on the 11Ants Analytics website. This along with downloading the sample data files and launching the tool and trying different options will prepare you for mining and building prediction models for your own data.

One obvious benefit, aside from a small learning curve, is that despite its simplicity, the 11Ants Model Builder offers more than one basic option for data mining. You can use regression (predict a number), classification (predict a category) and rank likelihood (predict and rank from most likely to least likely).

The tool is automated in such a way that you can move from tab to tab, making few changes to get results. The Model Builder also indicates which inputs are most useful in helping prediction.

There are a number of business uses for a tool like 11Ants Model Builder. You can use it to predict churn, sales forecasts and sales movement, sports data analysis, service planning, build stock market portfolios and to produce many other predictive statistics to help you make better business decisions.


Licensing and Availability

11Ants Model Builder is an add-in for Excel and requires Excel 2007 or Excel 2010. The Professional Edition is priced at $499 per user per annum, with an Enterprise Edition available per quote per requirements. A free 14-day trial of the tool can be downloaded from the 11Ants Analytics website.

Vangie Beal is a veteran online seller and frequent contributor to She is also managing editor of You can tweet with her online @AuroraGG.


Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved