Palisade NEURALTOOLS 5.5 Manual

Guide to Using

NeuralTools

Neural Network Add-In for

Microsoft® Excel

Version 5.5

February, 2009

Palisade Corporation 798 Cascadilla St. Ithaca, NY USA 14850 [phone]

[phone] (fax) http://www.palisade.com (website) [e-mail] (e-mail)

Copyright Notice

Copyright © 2009, Palisade Corporation.

Trademark Acknowledgments

Microsoft, Excel and Windows are registered trademarks of Microsoft, Inc. IBM is a registered trademark of International Business Machines, Inc. Palisade, TopRank, BestFit and RISKview are registered trademarks of Palisade Corporation.

Welcome to NeuralTools for Excel

Welcome

NeuralTools gives Microsoft Excel - the industry-standard data analysis and modeling tool - a new, powerful modeling toolset! NeuralTools is a Microsoft Excel neural networks add-in, allowing you to analyze data in Excel worksheets and work in the familiar Microsoft Office environment. By combining a powerful data manager, along with state-of-the-art neural networks algorithms, NeuralTools brings you the best of two worlds: Microsoft Office ease- of-use and reporting, with robust, accurate predictions from neural networks.

Work Where You're Comfortable

If you know Excel, you'll know NeuralTools! NeuralTools works just as Excel does, with toolbars, menus and custom worksheet functions, all inside of Excel. Unlike stand-alone neural networks software, there's no steep learning curve and upfront training costs with NeuralTools, because you work just as you are used to working in Excel. Your data and variables are in Excel spreadsheets. You can utilize standard Excel formulas for calculations, along with Excel sorting and pivot tables. Reports and charts from your analyses are in standard Excel format and can utilize all of Excel's built-in formatting capabilities.

Welcome to NeuralTools for Excel

i

NeuralTools Analyses

Neural Networks are capable of learning complex relationships in data. By mimicking the functions of the brain, they can discern patterns in data, and then extrapolate predictions when given new data. The problems Neural Networks are used for can be divided in two general groups:

•Classification Problems: Problems in which you are trying to determine what type of category an unknown item falls into. Examples include medical diagnoses and prediction of credit repayment ability.

•Numeric Problems: Situations where you need to predict a specific numeric outcome. Examples include stock price forecasting and predicting the level of sales during a future time period.

Neural Networks are used in a broad range of applications including: stock market prediction, credit and loan risk assignment, credit fraud detection, forecasting sales, general business forecasting, investment risk, medical diagnosis, research in scientific fields, and control systems.

NeuralTools includes the latest neural network algorithms to make the best predictions on both classification problems (called category prediction in NeuralTools) and numeric problems.

NeuralTools Data Management

NeuralTools provides a comprehensive dataset and variable manager right in Excel, similar to that provided with StatTools, Palisade's statistics add-in for Excel. You can define any number of datasets, each with the variables you want to analyze, directly from your data in Excel. NeuralTools intelligently assesses your blocks of data, suggesting variable names and types as well as data locations. Your datasets and variables can reside in different workbooks and worksheets, allowing you to organize your data as you see fit. Then, you train neural networks that refer to your variables, instead of reselecting your data over and over again in Excel. And NeuralTools' variables aren't limited in size to a single column of data in an Excel worksheet – you can use the same column across up to 255 worksheets for a single variable!

ii

Welcome

NeuralTools Reporting

Excel is great for reports and graphs, and NeuralTools makes the most of this. NeuralTools uses Excel-format graphs, which can be easily customized for new colors, fonts and added text. Report titles, number formats and text can be changed just as in any standard Excel worksheet. Drag and drop tables and charts from NeuralTools reports straight into your own documents in other applications.

NeuralTools Industrial also includes Live Prediction, where predicted values are calculated as new data is entered into your Excel worksheet. This live calculation happens automatically, just like other Excel recalculations.

Data Access and Sharing

Excel has great data import features, so bringing your existing data into NeuralTools is easy! Use standard Excel capabilities to read in data from Microsoft SQL Server, Oracle, Microsoft Access, or any other ODBC compliant database. Load data from text files or other applications – if you can read it into Excel, you can use it with NeuralTools!

NeuralTools saves all its results and data in Excel workbooks. Just like any other Excel file, you can send your NeuralTools results and networks to colleagues anywhere. Sharing couldn't be easier!

NeuralTools Professional and Industrial Versions

NeuralTools is available in two versions – Professional and Industrial.

The differences are as follows:

•Datasets in NeuralTools Professional are limited to 1000 cases, while NeuralTools Industrial supports datasets with up to 16,777,216 cases.

•Live Prediction, where predicted values are calculated as new data is entered into your Excel worksheet, is provided in NeuralTools Industrial only. This live calculation happens automatically, just like other Excel recalculations.

Welcome to NeuralTools for Excel

iii

iv

Welcome

Table of Contents
Chapter 1: Getting Started	1
Introduction.........................................................................................	3
Checking Your Package ..........................................................................	3
What the Package Includes.....................................................................	3
About This Version .................................................................................	3
Working with your Operating Environment ......................................	4
If You Need Help .....................................................................................	4
NeuralTools System Requirements......................................................	6
Installation Instructions .....................................................................	7
General Installation Instructions ..........................................................	7
Setting Up the NeuralTools Icons or Shortcuts..................................	8
The DecisionTools Suite.........................................................................	9
Software Activation ..........................................................................	11
Chapter 2: An Overview of NeuralTools	15
Overview............................................................................................	17
Why Neural Networks?.........................................................................	17
NeuralTools and Neural Networks ....................................................	18
NeuralTools Menu and Toolbar..........................................................	19
Data Sets and the Data Set Manager ..................................................	19
Training a Neural Network..................................................................	21
Testing a Network..................................................................................	26
Prediction.................................................................................................	27
NeuralTools Reports and Charts.........................................................	29
NeuralTools Utilities.............................................................................	29
Using NeuralTools with StatTools, Solver and Evolver.................	30
Chapter 3: NeuralTools Reference Guide	31
Introduction.......................................................................................	33
Reference: NeuralTools Icons	35
NeuralTools Toolbar.........................................................................	35
Table of Contents	v

Reference: NeuralTools Menu Commands	37
Introduction ......................................................................................	37
Icons in Dialog Boxes ...........................................................................	38
Command Reference .......................................................................	39
Data Set Manager Command ..............................................................	39
Train Command.....................................................................................	44
Test Command.......................................................................................	56
Predict Command..................................................................................	64
Utilities ..............................................................................................	69
Application Settings Command .........................................................	69
Neural Net Manager Command .........................................................	72
Missing Data Utilities Command ......................................................	74
More on Neural Networks	77
Neural Network Basics ....................................................................	77
Neural Nets vs. Statistical Methods...................................................	77
The Structure of a Neural Net.............................................................	78
Numeric and Category Prediction......................................................	78
Training a Net ........................................................................................	78
Computer Processing of Neural Nets ................................................	79
Types of Neural Networks ..................................................................	79
Multi-Layer Feedforward Nets.........................................................	81
MLF Architecture ..................................................................................	81
MLF Net Training .................................................................................	83
Generalized Regression Neural Nets and Probabilistic Neural
Nets ................................................................................................	87
Generalized Regression Neural Nets ................................................	87
Probabilistic Neural Nets ....................................................................	89
Comparison of MLF Nets to PN/GRN Nets ....................................	93
Input Transformation .......................................................................	95
Recommended Readings ................................................................	97
Index	99
vi	Welcome

Chapter 1: Getting Started

Introduction.........................................................................................	3
Checking Your Package ..........................................................................	3
What the Package Includes.....................................................................	3
About This Version .................................................................................	3
Working with your Operating Environment ......................................	4
If You Need Help .....................................................................................	4
NeuralTools System Requirements......................................................	6
Installation Instructions .....................................................................	7
General Installation Instructions ..........................................................	7
Setting Up the NeuralTools Icons or Shortcuts..................................	8

Chapter 1: Getting Started

1

2

Introduction

This introduction describes the contents of your NeuralTools package and shows you how to install NeuralTools and attach it to your copy of Microsoft Excel 2000 for Windows 2000 or higher.

Checking Your Package

Your NeuralTools package should contain:

The NeuralTools or DecisionTools Suite CD-ROM including:

•NeuralTools Program

•NeuralTools Tutorial

•The NeuralTools Users Guide (this book) in .PDF format

The NeuralTools Licensing Agreement

If your package is not complete, please call your NeuralTools dealer or supplier or contact Palisade Corporation directly at [phone].

What the Package Includes

NeuralTools may be purchased on its own and also ships with the DecisionTools Suite Professional and Industrial versions. The NeuralTools CD-ROM contains the NeuralTools Excel add-in, several NeuralTools examples, and a fully-indexed NeuralTools on-line help system. The DecisionTools Suite Professional and Industrial versions contain all of the above plus additional applications.

About This Version

This version of NeuralTools can be installed as a 32-bit program for

Microsoft Excel 2000 or higher.

Chapter 1: Getting Started

3

Working with your Operating Environment

This User’s Guide assumes that you have a general knowledge of the

Windows operating system and Excel. In particular:

•You are familiar with your computer and using the mouse.

•You are familiar with terms such as icons, click, double-click, menu, window, command and object.

•You understand basic concepts such as directory structures and file naming.

If You Need Help

Technical support is provided free of charge for all registered users of NeuralTools with a current maintenance plan, or is available on a per incident charge. To ensure that you are a registered user of NeuralTools, please register online at http://www.palisade.com/support/register.asp.

If you contact us by telephone, please have your serial number and User’s Guide ready. We can offer better technical support if you are in front of your computer and ready to work.

Before Calling Before contacting technical support, please review the following checklist:

•Have you referred to the on-line help?

•Have you checked this User's Guide and reviewed the on-line multimedia tutorial?

•Have you read the README.WRI file? It contains current information on NeuralTools that may not be included in the manual.

•Can you duplicate the problem consistently? Can you duplicate the problem on a different computer or with a different model?

•Have you looked at our site on the World Wide Web? It can be found at http://www.palisade.com. Our Web site also contains the latest FAQ (a searchable database of tech support questions and answers) and NeuralTools patches in our Technical Support section. We recommend visiting our Web site regularly for all the latest information on NeuralTools and other Palisade software.

4	Introduction

Contacting	Palisade Corporation welcomes your questions, comments or
Palisade	suggestions regarding NeuralTools. Contact our technical support
	staff using any of the following methods:
	• Email us at [e-mail].
	• Telephone us at [phone] any weekday from 9:00 AM to 5:00 PM,
	EST. Follow the prompt to reach technical support.
	• Fax us at [phone].
	• Mail us a letter at:
	Technical Support
	Palisade Corporation
	798 Cascadilla St.
	Ithaca, NY 14850 USA

If you want to contact Palisade Europe:

•Email us at [e-mail].

•Telephone us at [phone] (UK).

•Fax us at [phone] (UK).

•Mail us a letter at:

Palisade Europe 31 The Green West Drayton Middlesex

UB7 7PN United Kingdom

If you want to contact Palisade Asia-Pacific:

•Email us at [e-mail].

•Telephone us at [phone] (AU).

•Fax us at [phone] (AU).

•Mail us a letter at:

Palisade Asia-Pacific Pty Limited Suite 101, Level 1

8 Cliff Street

Milsons Point NSW 2061 Australia

Regardless of how you contact us, please include the product name, version and serial number. The exact version can be found by selecting the Help About command on the NeuralTools menu in Excel.

Chapter 1: Getting Started

5

Student

Versions

Telephone support is not available with the student version of NeuralTools. If you need help, we recommend the following alternatives:

♦Consult with your professor or teaching assistant.

♦Log on to http://www.palisade.com for answers to frequently asked questions.

♦Contact our technical support department via e-mail or fax.

NeuralTools System Requirements

System requirements for NeuralTools 5.0 for Microsoft Excel for Windows include:

•Pentium PC or faster with a hard disk.

•Microsoft Windows 2000 SP4, Windows XP or higher.

•Microsoft Excel 2000 or higher.

6	Introduction

Installation Instructions

General Installation Instructions

Removing NeuralTools from Your Computer

The Setup program copies the NeuralTools system files into a directory you specify on your hard disk.

To run the Setup program in Windows 2000 or higher:

1)Insert the NeuralTools or DecisionTools Suite CD-ROM in your CDROM drive

2)Click the Start button, click Settings and then click Control Panel

3)Double-click the Add/Remove Programs icon

4)On the Install/Uninstall tab, click the Install button

5)Follow the Setup instructions on the screen

If you encounter problems while installing NeuralTools, verify that there is adequate space on the drive to which you’re trying to install. After you’ve freed up adequate space, try rerunning the installation.

If you wish to remove NeuralTools from your computer, use the Control Panel’s Add/Remove Programs utility and select the entry for NeuralTools.

Chapter 1: Getting Started

7

Creating the

Shortcut in the

Windows

Taskbar

Setting Up the NeuralTools Icons or Shortcuts

In Windows, setup automatically creates a NeuralTools command in the Programs\Palisade DecisionTools menu of the Taskbar. However, if problems are encountered during Setup, or if you wish to do this manually another time, follow these directions. Note that the directions given below are for Windows XP Professional. Instructions for other operating systems may vary.

1)Click the Start button, and then point to Settings.

2)Click Taskbar and Start Menu, and then click the Start Menu tab.

3)Click Customize, click Add, and then click Browse.

4)Locate the file NeuralTools.EXE, click it and then click OK.

5)Click Next, and then double-click the menu on which you want the program to appear.

6)Type the name “NeuralTools”, and then click Finish.

7)Click OK on all opened dialogs.

8	Installation Instructions

The DecisionTools Suite

NeuralTools is part of the DecisionTools Suite, a set of products for risk and decision analysis available from Palisade Corporation. The default installation procedure of NeuralTools puts NeuralTools in a subdirectory of a main “Program Files\Palisade” directory. This is quite similar to how Excel is often installed into a subdirectory of a “Microsoft Office” directory.

One subdirectory of the Program Files\Palisade directory will be the NeuralTools directory (by default called NeuralTools5). This directory contains the NeuralTools add-in program file (NEURALTOOLS.XLA) plus example models and other files necessary for NeuralTools to run. Another subdirectory of Program Files\Palisade is the SYSTEM directory which contains files needed by every program in the DecisionTools Suite, including common help files and program libraries.

Chapter 1: Getting Started

9

10	Installation Instructions

Software Activation

Activation is a one time license verification process that is required in order for your NeuralTools software to run as a fully licensed product. An activation code is on your printed/emailed invoice and may resemble a dash separated sequence like "19a0-c7c1-15ef-1be0- 4d7f-cd". If you enter your Activation code during installation, then your software is activated the first time the software is run and no further user action is required. If you wish to activate your software after installation, select the NeuralTools Help menu License Activation command and enter your activation code in the displayed

Palisade License Activation dialog box.

Frequently

Asked

Questions

1) What if my software is not activated?

If you do not enter an activation code during installation or you are installing a trial version, your software will run as a trial version with time and/or number of uses limitations and must be activated with an activation code in order to run as a fully licensed product.

2) How long can I use the product before I have to activate it?

Software that is not activated may be run for fifteen days. All of the product's features are present, but the License Activation dialog will appear each time the program is launched to remind you to activate and to indicate the time remaining. If the 15 day trial period expires, the software will require activation in order to run.

Chapter 1: Getting Started

11

3) How do I check my activation status?

The License Activation dialog box is viewed through the NeuralTools Help menu License Activation command. Activated software shows a status of Activated and trial version software shows a status of Not Activated. If the software is not activated, the remaining time that the software is allowed to run is displayed.

4) How do I activate my software?

If you do not have an activation code you may obtain one by clicking the Puchase button in the License Activation dialog. An online purchase will be immediately given an activation code and an optional link to download the installer should reinstallation become necessary. To purchase by phone call the local Palisade office given in the Contacting Palisade section of this chapter.

Activation may be done over the Internet or via email:

•Activation if you have Internet Access

In the Palisade License Activation dialog box, type or paste the activation code and press "Automatic via Internet". A success message should appear after a few seconds and the License Activation dialog box will reflect the software's activated status.

• Activation if you do not have Internet Access

Automated activation by email requires a few steps:

1.Click "Manual via Email" to display the request.xml file which you may save to disk or copy to the Windows clipboard. (It is recommended you note the location on your computer of the request.xml file.)

2.Copy or attach the XML file to an email and send it to [e-mail]. You should receive an automatic response to the return address in your email shortly.

3.Save the response.xml attachment in the response email to your hard drive.

4.Click on the Process button that is now in the Palisade License Activation dialog box and navigate to the response.xml file. Select the file and click OK.

A success message should appear and the License Activation dialog will reflect the software's activated status.

12	Software Activation

5) How do I transfer my software license to another machine?

Transfer of a license, or rehosting, may be peformed through the Palisade License Activation dialog box as a two step procedure: deactivation on the first machine and activation on the second machine. A typical use of rehosting is to transfer your copy of NeuralTools from your office PC to your laptop. To rehost a license from Machine1 to Machine2, make sure both machines have the software installed and are connected to the Internet during the deactivation/activation rehosting.

1.On Machine1, click deactivate Automatic via Internet in the License Activation dialog. Wait for the success message.

2.On Machine2, click activate Automatic via Internet. Wait for the success message.

If the machines do not have Internet access then you may follow the similar instructions above for rehosting by the automated email process.

6) I have Internet Access but I am still unable to Activate/Deactivate automatically.

Your firewall must be set to allow TCP access to the licensing server. For single user (non network installations) ths is http://service.palisade.com:8888 (TCP port 8888 on http://service.palisade.com).

Chapter 1: Getting Started

13

14	Software Activation

Chapter 2: An Overview of

NeuralTools

Overview............................................................................................	17
Why Neural Networks?.........................................................................	17
NeuralTools and Neural Networks ....................................................	18
NeuralTools Menu and Toolbar..........................................................	19
Data Sets and the Data Set Manager ..................................................	19
Variable Types ..........................................................................	21
Multi-Range Data .....................................................................	21
Training a Neural Network..................................................................	21
Combining Training, Testing and Prediction.....................	22
Net Configurations...................................................................	22
Training Preview ......................................................................	23
Training Process .......................................................................	24
Training Reports.......................................................................	25
Testing a Network..................................................................................	26
Testing Reports.........................................................................	26
Prediction.................................................................................................	27
Prediction Results.....................................................................	28
Live Prediction ..........................................................................	28
NeuralTools Reports and Charts.........................................................	29

Chapter 2: An Overview of NeuralTools

15

16

Overview

NeuralTools provides you with powerful neural network capabilities in an environment that you are familiar with - Microsoft Excel. NeuralTools procedures - such as defining data sets, training and testing neural networks and predicting values using trained networks - can be run on your data in Excel and the reports and charts from your analyses are created in Excel.

Why Neural Networks?

Neural Networks are capable of learning complex relationships in data. By mimicking the functions of the brain, they can discern patterns in data, and then extrapolate predictions when given new data. The problems Neural Networks are used for can be divided in two general groups:

•Classification Problems: Problems in which you are trying to determine what type of category an unknown item falls into. Examples include medical diagnoses and prediction of credit repayment ability.

•Numeric Problems: Situations where you need to predict a specific numeric outcome. Examples include stock price forecasting and predicting the level of sales during a future time period.

NeuralTools comes with examples that show how to apply neural networks to different prediction problems. The NeuralTools\Examples folder contains the provided examples as Excel workbooks.

Chapter 2: An Overview of NeuralTools

17

NeuralTools and Neural Networks

When using NeuralTools, neural networks are developed and used in four steps:

•Data Preparation - The data you use in NeuralTools is defined in data sets. A Data Set Manager is used to set up data sets so they can be used over and over again with your neural networks.

•Training – With training, a neural network is generated from a data set comprised of cases with known output values. This data often consists of historical cases for which you know the values of output/dependent variable.

•Testing – With testing, a trained neural network is tested to see how well it does at predicting known output values. The data used for testing is usually a subset of your historical data. This subset was not used in training the network. After testing, the performance of the network is measured by statistics such as the % of the known answers it correctly predicted.

•Prediction - A trained neural network is used to predict unknown output values. Once trained and tested, the network can be used as needed to predict outputs for new case data.

Training and testing are an iterative, sometimes time-intensive process. Typically, you may train several different times with different settings in order to generate a neural network that tests best. Once you have your "best net" you can quickly use it for predicting.

Now, let's look at how NeuralTools works in Excel and how you define data sets and train and test neural networks using those data sets. Then we will predict unknown output values using trained networks.

18

Overview

NeuralTools Menu and Toolbar

Once you have installed NeuralTools, its menu and commands will be included as part of the Excel menu bar in Excel 2003 and earlier. There will also be a NeuralTools toolbar displayed. The menu shows commands for 1) defining your data in data sets, 2) training and testing neural networks and 3) predicting values using trained neural networks. In Excel 2007, all commands are available via the NeuralTools ribbon bar.

Data Sets and the Data Set Manager

Data in NeuralTools is structured around cases and variables. You work with a data set, or a set of statistical variables, located in contiguous columns with variable names in the first row of the data set. Each row in the data set is a case. Each case has a set of independent variable values and either a known or missing value for the dependent output variable. It is the job of NeuralTools to predict output variable values for cases where they are not known.

Chapter 2: An Overview of NeuralTools

19

The NeuralTools Data Set Manager allows you to define your data sets, variables and cases. You can then use these predefined variables for training and testing neural networks, without re-selecting the data you wish to analyze over and over. You may place all known, historical cases in one data set and cases you wish to predict results for in a different data set. You can also combine all your data – known historical data and data you wish to predict - in a single data set.

Each variable in a data set has a name and a range of Excel cells associated with it. Each column within the range contains data for a different variable. A data set can include multiple blocks of cells, allowing you to put data on different sheets in the same workbook.

When you are defining a data set, NeuralTools attempts to identify the variables in a block of cells surrounding the current selection in Excel. This makes it quick and easy to set up a data set with variable names in the top row and variables laid out by column.

20

Overview

Variable Types

Multi-Range Data

In NeuralTools, variables can be independent or dependent and numeric or categorical (for example Yes or No, or Red, Green or Blue.) The Data Set Manager attempts to identify the type of each variable in your data set, but you can override this with your own selections.

A single column in an Excel 2003 or earlier worksheet can hold up to 65,536 data points for a variable. If your variables have more values than this and you choose not to adopt Excel 2007, NeuralTools allows multiple cell ranges to be assigned to a single data set. In other words, you can "repeat" a data set across multiple sheets, assigning the same columns in different worksheets to hold all the values for a data set.

Training a Neural Network

After you have defined a data set that contains cases with known historical values, you can train a neural network using that data. There are different options that determine the type of network that will be generated by NeuralTools. Depending on the nature of your data, different network options will generate better performing trained networks (i.e. networks that do a better job predicting answers). The testing process – done following training – gives precise measurements of how well your trained network does at predicting output values.

Training a neural network, along with testing and prediction, requires that you specify a data set that contains the data to be used during training. NeuralTools will either save your trained network directly in your workbook or optionally, to a file on disk.

Chapter 2: An Overview of NeuralTools

21

Combining

Training,

Testing and

Prediction

Net

Configurations

If all your data is in a single data set (including both known historical data and new data where you do not know output values), NeuralTools allows you to train and test a network, then predict output values, all in a single step. You select to withhold a certain percentage of the historical data for testing (20% is shown on the prior page) and then select to automatically predict output values for cases with missing dependent values. By doing this you quickly can get the answers you need in one operation.

NeuralTools supports different neural network configurations to give the best possible predictions. For classification/category prediction (where the dependent variable is a category type), two types of networks are available: Probabilistic Neural Networks (PNN) and Multi-Layer Feedforward Networks (MLF). Numeric prediction can be performed using MLF networks, as well as Generalized Regression Neural Networks (GRNN), which are closely related to PNN networks.

NeuralTools makes selecting a network configuration easy by offering a Best Net search. When selected, NeuralTools will train and test a variety of neural network configurations to generate the one the gives the best predictions for your data. The best configuration is determined based on testing data, so for Best Net search the "Automatically Test" option needs to be selected in the Train tab.

22

Overview