I recently found a neat little Genetic Algorithm (GA) addin for Excel that I wanted to share with my readers. Its called Genetik Solver and you can download it for free. Its pretty straight forward if you know your way around GA’s but if you’re just starting to fool around with it then you’ll find their terse help file a bit lacking. Have no fear, Neural Market Trends is here to guide you through this fascinating algorithm!

1. Download the addin. It has an .xla extension and should be unzipped.
2. Next, open Excel then go to the Tools menu > AddIns and click Browse.
3. Select the Genetik Solver from whatever directory you saved it too. It should automatically install itself.

The next steps explain what the menu system means, or at least how I understand it. Refer to the image to the left and follow along.

Parameters

The most important aspect of the GA’s is their ability take an initial population of data (or genes), crossbreed them, mutate a few of them, weed out the weak, and then allow the fittest to breed further. I seem to have read somewhere that in Nature, the saving of energy and resources takes the utmost precedent so the strongest and least energy consuming individual is the one that survives to breed. That is unless an unfortunate accident were to occur but we won’t be that cynical for this tutorial.

Genetik Solver using four parameters: Population, Crossover Probability, Mutation Probability, and Maximum Generation.

1. Population is the initial number of participants in your experiment. It could be 100 genes, 200 pieces of data, or 1500 people (Genetik Solver has 1500 max limit). You can choose whatever number you like.
2. Crossover Probability is nothing more than the likelihood that the participants in your population will cross breed with each other. This number is in decimal form and must be between 0 and 1.00
3. Mutation Probability is the likelihood that mutations will occur during the cross breed (Nature at work). This number is usually between 0 and 0.1 but you can go as high as 1.0
4. Maximum Generation is the total number of iterations of future generations you want to model. I suggest the max generation below 1000 because it ties up your computer memory when is running, unless you have a fast computer and lots of memory.

Target Cells to Minimize/Variables Cells

Now comes the interesting part of this addin, the target and variable cells. The GA is just that, an algorithm and its main goal is to minimize something. If you are familiar with Excel’s goal seek function, then you’ll understand how a GA works.

Let’s back up a minute and think of an interesting example to try this addin out on. Download my Excel example and follow along.

Suppose you have one column of three inputs: height, weight, and age. Based on your research you find out that the output, energy expenditure (for whatever reason), yields the following equation: 0.5Height + (Weight)^2 - 0.45(Age)^0.88. Your goal is to minimize the Energy Expenditure by varying the Height, Weight, and Age input variables.

You have your lower and upper boundary conditions, for Height your lower boundary is 10 inches and upper boundary is 72 inches, for Weight its 10 and 200 lbs, and for Age its 1 and 100 years.

Click on the Genetik Solver menu and select toggle for “Target Cell to Minimize” and select the cell with the Energy Expenditure formula in it. Next click on the Variable Cells toggle and select the Height, Weight, and Age input variables. Click on the lower boundary toggle and select the lower boundary variables. Repeat this step for the upper boundary variables.

The last step before you click solve is to set your parameters. You can fool around with the parameters but for this example I used 1500 for the population parameter, 0.45 for cross over probability, 0.1 for mutation, and 100 for generations.

Solving

Click on solve and you should see something like this (click image to the left). The results indicate that the to get the lowest Energy Expenditure, you must be 99 years of age, 10 inches tall, and weigh only 10 lbs. Hardly realistic but what do you expect from this quick and dirty tutorial.

Let me stress something: YOU MUST PUT YOUR INPUTS IN THE SAME COLUMN. The same goes for your Lower and Upper Boundary variables. If you don’t do this, the addin won’t recognize all your inputs and boundary conditions.

Now I scratched the surface with GA’s here and you can easily migrate this little addin to something more complex and interesting like trading models! If you are subscriber of My Del.icio.us links, you’ll note a strange named PDF that I recently posted. It was written by Korean researchers using GA’s to generate BUY and SELL signals for the Kospi Index. They used the GA’s to find the best coefficients for their trading rule formulas. It’s a great read and worth downloading to get some ideas.

As always with my tutorials, please feel free to comment or drop me an email if you have questions.