Transcript An Optimisation add-in for Microsoft Excel

GANetXL
Evolutionary Optimisation
for Microsoft Excel
J.Bicik, M.S.Morley, E.C.Keedwell & D.A.Savić
Presentation Outline
 General description
 Structure & features of the application
 Definition of the optimisation problem
 Live demonstration
 Software & hardware requirements
 Getting GANetXL
 Future work
23 February 2007
2/17
Description
 A user friendly add-in which integrates
into Microsoft Excel
 Uses evolutionary algorithms to solve
complex optimisation problems
 Interacts with Excel using COM interface
23 February 2007
3/17
Existing Tools
 Evolver 4.0 (Palisade)
 Premium Solver Platform (Frontline
Systems Inc.)
 OptWorks Excel (Pi Blue)
 GeneHunter (Ward Systems)
 Generator (NewLight Industries, Ltd.)
 xl bit (XLPert Enterprise)
 GenSheet (Inductive Solutions, Inc.)
23 February 2007
4/17
Motivation
 The commercial software does not
support multi-objective (MO) optimisation
 Use of existing GA library
– Flexibility
– Extensibility
 To let students get in touch with GAs
 Simple use
 Save money on buying software licenses
23 February 2007
5/17
History
 GA Excel add-in (2003) by F. Zheng
– written in Pascal
– Single Objective Genetic Algorithms
 GenetXL (2004)
– written in C++
– GA Library in Pascal
– SOGA + MOGA (NSGA-II)
 GANetXL (2007)
– written purely in C++
23 February 2007
6/17
Structure of GANetXL
23 February 2007
7/17
List of Features
 Single and multiple-objective
optimisation techniques
 Support for integration with simulation
packages
 Suspend, resume
 Multiple-objective results browser
 Batch runs
 User defined constraints & penalty
multipliers
23 February 2007
8/17
Features - Continued
 Automatic saving of population
 Backups of intermediate population
 Visualisation of results and progress
 Built-in help
 User manual
23 February 2007
9/17
Defining the Problem
 Decision variables – Genes
altered by GA within their
ranges (x1,x2,x3,x4)
must occupy continuous range
 Objective function(s)
used to evaluate the
fitness of solutions
(formula in C6)
 Constraints
used to limit values of
objective function(s)
(formula in C8)
23 February 2007
10/17
Practical Examples
1. Single-objective
– optimisation of an advertising campaing
2. Multiple-objective
– optimisation of an advertising campaing
3. Link with simulation software
– optimisation of the “NY tunnels” problem
4. Benchmark
– Zitzler test T1
5. Another multiple-objective example
– demonstration of population backups
23 February 2007
11/17
Supported Platforms
 Microsoft Windows
– Vista (not tested but should work)
– XP
– 2000
 Microsoft Excel
– 2010
– 2007
– 2003
– XP
– 2000
23 February 2007
12/17
New features of Excel 2007
 Supports multi-core CPUs
 The max. number of columns:
– extended from 256 to 16,384
 The max. number of rows:
– extended from 65,536 to 1,048,576
 Total amount of PC memory that Excel
can use:
– extended from 1GB to maximum allowed by
Windows
23 February 2007
13/17
Licensing
 Licenses are bound to specific computer
Registration +
HW Identifier
Registration
Details
Serial Number
Serial No.
 Serial number
– contains expiration date
– valid for limited number of days
– chromosome size, population size
– objectives count, generations count
23 February 2007
14/17
Getting GANetXL
Download GANetXL from following site:
http://www.exeter.ac.uk/cws/ganetxl
To obtain a license please follow the
installation instructions:
http://centres.exeter.ac.uk/cws/technolo
gy/ganetxl-addin/ganetxl-installation
Support, reporting bugs and problems:
Josef Bicik ([email protected])
23 February 2007
15/17
Future Work
 Add more multiple-objective algorithms
– MO Simulated Annealing
– ε-NSGA II
 Make the application multi-threaded
 Visualisation of more than 2 objectives
 Do more testing of the application
Any other ideas?
23 February 2007
16/17
Thank you for your
attention!
Questions & Discussion
Links
 Evolver 4.0 (Palisade)







http://www.palisade-europe.com/evolver/
Premium Solver Platform (Frontline Systems Inc. )
http://www.solver.com/xlsplatform.htm
OptWorks Excel (Pi Blue)
http://www.piblue.com/products/optworks_ex.html
GeneHunter (Ward Systems)
http://www.wardsystems.com/products.asp?p=genehunter
Generator (NewLight Industries, Ltd.)
http://www.nli-ltd.com/products/genetic_algorithms/generator.htm
xl bit (XLPert Enterprise )
http://www.xlpert.com/wxl%20bit.htm#gg1
GenSheet (Inductive Solutions, Inc.)
http://www.inductive.com/softgen.htm
GA Optimization for MS Excel (freeware)
http://www.alexschreyer.net/projects/xloptim/index.php
23 February 2007
18/17