NYSE 2 Prediction

<< Click to Display Table of Contents >>

Navigation:  Chapter 5 - GeneHunter Examples in Excel >

NYSE 2 Prediction

Previous pageReturn to chapter overviewNext page

Example File: GHSAMPLE.XLS, NYSE2 worksheet

Solution Type: Continuous Chromosomes


This example is located in a tab labeled NYSE2 in the GHSAMPLE.XLS worksheet that was installed in the C:\GENEHUNTER\EXAMPLES\EXCEL subdirectory if you chose the default directory during GeneHunter installation. (If you install the entire AI Trilogy set of programs, the GeneHunter folder is a subfolder of the C:\AI Trilogy folder.)


The NYSE2 example is the same as the NYSE Prediction example except that we have coded the fitness function entirely in Excel formulas instead of using the VBA program code that was used in the original example. This should make the example more understandable to those users who do not program in VBA, and thus make it easier to adapt to your own data.


The new example has two additional side benefits. First of all, the evaluation of the current rule is shown as it is applied to each day in the "training" file. In the range E16:K33 you can see the exact values that are evaluated by the rule for a given day's data in A16:D33. The key to using Excel formulas here is that we utilize the Excel OFFSET function to obtain the correct value out of the range B5 through D33. (Please refer to Excel documentation for a full description of how OFFSET works.) Using cell B16 as a base, the OFFSET function pulls up the correct number of days back and to the right. For example, if you click on E16 before running the example you will see OFFSET(B16,-F7, F6). F7 is number of days ago, in this case 4. F6 is the number of

columns right of B, in this case 2. So the OFFSET function picks up the cell 4 days back from B16 and 2 columns right. This, of course, is the NYSE close 4 days ago. If F6 had been 1, it would have picked up the NYSE low 4 days ago.


The two columns L16:M33 are simply the evaluations of the two parts of the rule. The

evaluations come out as 1 for true and 0 for false. For example, in L18:M18 we see 1 and 0 meaning that 150.06 <= 150.66  was true and 150.06 > 150.06 was false. In column N16:N33 we simply do the ANDing of the two parts of the rule. The rest of the example is the same.


Another side benefit is that the entire example now runs much faster because the fitness function, which is the time consuming part of GeneHunter, is now written as an Excel formula rather than as a VBA subroutine.