Palisade NEURALTOOLS 5.5 User Manual
Guide to Using
NeuralTools
Neural Network Add-In for
®
Microsoft
Version 5.5
February, 2009
Excel
Palisade Corporation
798 Cascadilla St.
Ithaca, NY USA 14850
(607) 277-8000
(607) 277-8001 (fax)
http://www.palisade.com (website)
sales@palisade.com (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 easeof-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
@RISK 4.5 Help System © Palisade Corporation, 1999
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 (607) 277-8000.
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
4 Introduction
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.
Contacting Palisade
Palisade Corporation welcomes your questions, comments or
suggestions regarding NeuralTools. Contact our technical support
staff using any of the following methods:
• Email us at support@palisade.com.
• Telephone us at (607) 277-8000 any weekday from 9:00 AM to 5:00 PM,
EST. Follow the prompt to reach technical support.
• Fax us at (607) 277-8001.
• 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 support@palisade-europe.com.
• Telephone us at +44 1895425050 (UK).
• Fax us at +441895425051 (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 support@palisade.com.au.
• Telephone us at +61 2 9929 9799 (AU).
• Fax us at +61 2 9954 3882 (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
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 CD-
ROM 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.
Removing NeuralTools from Your Computer
Chapter 1: Getting Started 7
If you wish to remove NeuralTools from your computer, use the
Control Panel’s Add/Remove Programs utility and select the entry
for NeuralTools.
Setting Up the NeuralTools Icons or Shortcuts
Creating the Shortcut in the Windows Taskbar
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-1be04d7f-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
Chapter 1: Getting Started 11
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.
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
activation@palisade.com. 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
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.
Net Configurations
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
Loading...