Example File:  GHSAMPLE.XLS, Portfolio worksheet

Solution Type:  Enumerated Chromosomes

This example is located in a tab labeled Portfolio 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.)

A money manager has a list of stocks, each with a different value,  that he wants to separate into six groups.  The money manager wants to classify the stocks into six groups based upon specified percentages of his entire portfolio.  Although this problem is not one a real money manager would likely need to solve, it is representative of many problems which require

you to group items with different values into sets, e.g., students into academic competition teams, workers with different skills into job teams, or different sized packages into different sized delivery trucks (typical of many bin packing problems).  The setup for this problem may also be used to allocate resources to department stores or manufacturing plants.

Figure 5.1  View of Portfolio worksheet

In order to solve the problem, the money manager enters a list of the stocks, the price for 100 shares, and a group number in columns B, C, and D respectively of the spreadsheet.  Initially, the group numbers are arbitrary, but each possible group number must appear at least once in the list.

Next, the money manager lists the group numbers in column F and the percentage of his portfolio he wishes to allocate to that group in Column H.  GeneHunter adjusts the group number of each stock in Column D until the groups closely match the percentages listed in Column H.

The desired percentages in Column H are compared with the actual percentages in Column I.  The error between the two columns for each stock is computed in Column J as the absolute value of the difference between the actual percentage (computed in Column I) and the target percentage (computed in Column H).  These values are summed for all stocks in cell J14.  This cell becomes the fitness function.

The money manager wants to minimize the error, so he selects Min on the main GeneHunter screen (see Figure 5.2).  The adjustable values are the group numbers for the list of stocks in cells D8 through D33, which are specified on the main GeneHunter screen.

The screen also shows that enumerated chromosomes with repeating genes were chosen to solve this problem.  Enumerated chromosomes allow GeneHunter to select a group number from a specified list (Groups 1 to 6).  Repeating genes allow the group number to appear multiple times in the adjustable cells, i.e., more than one stock may be assigned to each group.

Figure 5.2  View of main GeneHunter screen for Portfolio problem.

In order to solve the problem, GeneHunter takes advantage of Excel’s database functions by using DSUM() formulas in cells G8:G13.  The DSUM formulas allow you to sum up the values in Column C which belong to specific groups that are listed in Column D.

For example, the DSUM formula in cell G8 is DSUM($B$7:$D$33,”PRICE”, B35:B36).  This formula instructs Excel to refer to the range $B$7 to $D$33 which includes Column B Ticker, Column C Price, and Column D Group.  The formula sums up the values in the Price column which are included in Group 1.  The formula only includes Group 1 values because of the reference to cells B35:B36, which specify Group 1.

You might experiment with adding constraints to the actual percentages or changing the target percentages.  Note that the percentages must add up to 100.