This application replicates real stock market data and the trading strategy of an investor. The investor owns some cash and shares of a stock at the beginning of day 1. From then on, the investor uses a buy/sell strategy that depends on the recent stock price changes. The current strategy, described on the Inputs sheet, is a version of “buy low/sell high.” Output of this strategy is put on the Summary and Comparison sheets. You can also utilize an advanced Evolutionary Algorithm mode to have the computer automatically test thousands+ combinations of input modifications to attain the fittest output.

The stock price, on the ‘Model’ worksheet, gets its data from Yahoo! Finance. In order for pricing from Yahoo! finance to work, be sure that you have a network connection

You can view/change the inputs first or run the simulation with the current inputs. Try changing inputs, and re-running the simulation, comparing their outputs. Be sure to understand the ‘Comparison’ worksheet. It gives each runs results in a concise format for easy comparison as to which is better.

After experimenting with the simulation and inputs, you can use an automated evolutionary method of changing the inputs and re-running of consequtive simulations by clicking the ‘Run Evolutionary Algorithm’ button. Many simulations will run fullly automated, giving many results on the ‘Comparison’ worksheet. Each generation of trials are visually separated. The ‘Evolution’ worksheet gives the results of the evolutionary fitness tree, basically which generations lived on, which died, which is best.

• Having hard time imagining my current line of work, integrating with all concepts of what this class has done, especially portfolio optimization
  • My, currently, technical trading platform works for a single stock best, and I really need to address this downfll
  • Could I just send the entire process into another huge loop that changes the stock, so that it does the entire process of intelligence for each stock? Will this take the computer too long to calculate?

• I definitely had to modify and trace the code to make sure it was what I wanted, and then integrate it into my specific use.
• It is good generalistic code though, and very succinct, so very usable, yay! Thanks Igor!
• Base functionality came from http://msdn2.microsoft.com/en-us/library/aa384072(VS.85).aspx
• As Igor and the above article tell, you have to turn on a certain .dll reference in VBA to use the code
  • 
• Looked into details of yahoo csv Vs web output, since Igor mentioned in comments that dividends are not output on csv’s
  • I verified this, and went into finding out what Adjusted Closing price was.

• What is it?
• defined many places but it did not give me a feel for what was being done with the numbers specifically by yahoo
• See yahoo web quote for discrepancy between closing and adj closing before and after the marked dividend
  • 
• Took graphs to see diff between closing and adj closing
  • 
• Answer: Turns out, yahoo decreases the closing price by the amount of the dividend, going BACK in time. They must have to recalculate past Adj Closing prices, as new dividends or splits roll in. I do not think that this is a number that I want to use. If anything, I would want to do the opposite, adding any dividend to the closing price, since this is what it does in real life. You get more profit. There must be a reason why yahoo does it that way.

• Created new Module called ‘Data’ that has the sole purpose of retrieving remote data
• Had problems with named ranges, where I found answers here: http://www.cpearson.com/excel/named.htm
• Had to modify sort line that Igor said he got from a macro recording session

  • oPrices.Range("A1", oPrices.Range("C1").End(xlDown)).Sort Key1:=oPrices.Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

• I have to add and remove columns from this worksheet constantly, which involved changing coded numbers, and remember to do it. One time I might forget to change the numbers, or do it wrong, not know it, and mess up my calculations. Other users might not know either.
• So, I needed to name column headers as ‘named ranges’
  • This way, add notes from comments of proj

• Allow the user to enter a date range that the model will pull stock data from
• Added inputs to worksheet for start date, and a non-user input calculated end date
  • calculated end date is needed currently because Model worksheet is constrained to exactly how many time periods of trading it needs
• Modified code in multiple functions to work with new dating system, keeping in mind next task of allowing replications across multiple time spans to take place
• new date simplification code in Data module
  • instead of sending in many variables for a complete date, just send in a simple Date variable and split it up inside the function

• Allow users to automatically process many trading instances across differing spans of time
  • 1/1/07-1/1/08
  • 12/1/06-12/1/07
  • 11/1/06-11/1/07
  • etc for n Repetitions

• I did not document step-by-step by progress through the evolution function here, as I have done above. I will try to estimate my thought process and implementation through it below
• A good explanation of the code in is included in the VBA function description, copied below:

  • ' This function will use a simple evolutionary algorithm to go through the SimMain function
    ' many times with varying inputs each time. You could do this manually by changing variables
    ' yourself, and re-running the simple simulation over and over again. This algorithm does it
    ' much faster, and systematically.
    '
    ' General Implementation explanation:
    ' The computer will examine numerous generational outputs and try to improve the output
    ' by mutating numerous inputs independantly. Basically, four variables are changed one at a time by a
    ' single random number modifier. Each change of one of the four variables, is a child instance. The
    ' various children are examined for fitness/goodness, and the best is chosen and sent on to 'live'.
    ' In this algorithm, we will make changes to all 4 variables, by the same amount. The children
    ' changes will be recorded in the 'Evolution' worksheet.
    '
    ' Best/fittest-tracking implementation explanation:
    ' Was originally thinking about implementing keeping track of the best/fittest over generations, via
    ' an external function called something like 'Make Best' or 'Find best'. I realized that instead of
    ' finding the best, after all children were made was inneficient, because it was the same loop that had
    ' already been run. Why not just keep track of best as children are made? So, now its built into the
    ' children loop
    '
    ' Sub created originally 03/03/2008 by Nick Yeates
    ' additional notes here: http://nickyeates.com/personal/school/financialitsystems_is698/assignment_5_intelligence
    '
    ' Assumes that upon running this function, this is the 'first' time you have run an evolution.
    ' No prior attempts count. This means that this function does not start by looking back at
    ' prior trials on the 'Comparison' worksheet.
    '
    ' Assumes user has entered first set of Inputs. User can set these intentionally as a good
    ' start point, or arbitrarily. Likely to see better results when starting with good initial inputs
    '
    '
    ' Terms:
    ' Generation: Levels, repetitions, or births of new trials that will exist. Just like a family tree says
    '             that there are 3 generations between you-parents-grandparents, the number of generations in
    '             this algorithm works the same
    ' Best Generation: In evolutionary terms, the best generation is equivalent to the
    '                  living/non-dead generation, or the fittest outcome
    

• Sketch output
  • Sketch working output of normal evolution run
  • scan here
• Pseudo-code
  • From sketch, create pseudo-code/workflow sketch
  • scan here
• Create evolution worksheet
  • From sketch of output, create a worksheet that looks similar
• Pseduo-code to VBA comments
  • This is a common method of mine. It helps me create implicit documentation/comments of the code and a complete understanding start to finish of what I will do BEFORE I code. Works really effectively.
  • It also allows me to easily pickup where I left off if I leave code for some time.
• Number of Generations
  • This will be determined manually at first, by the user. So either hard coded in, or the user will be asked.
  • Generations are how many levels or repetitions or births of new trials there will be. Just like a family tree says that there are 3 generations between you-parents-grandparents, the number of generations in this algorithm works the same
  • could smart-stop, when optimal results are reached, instead of being hard coded-in, or in addition to being hard coded
• Run initial 0 generation
  • Basically a call to Main
  • did not yet implement the picking of the best/fittest
  • first 0 generation will automatically become the fittest because it is all that exists at first, no other generations
• Generation loop
  • this is the most important main loop of the program that sends the algorithm through each successive generation creation and evaluation
  • A generation will be created, multiple children will be created within the generation, inputs will be mutated, outputs compared and fittest found
  • various generational and child outputs are to be ‘stored’ by writing them to the ‘Evolution’ worksheet, grabbed from each successive ‘Comparison’ worksheet
• Child loop
  • Creates mutliple children, 4 in our example, one for each input change
  • A random change amount is then applied, one at a time, to each input. So, child 1 will have var 1 changed by X, child 2 will have var 2 changed by the same X, etc to child 4.
• Pretty up code in Evolve
  • Functionalization of repeated ugly ‘Determine Last’ code
    • LastComparisonRow()
    • Because of initial time constraints, I had to repeatedly add in very similar code throughout the Evolve function
    • This code can be put into its own function and called upon by evolve
    • Even better, would be to implement it with VBA-native calls
    • talk to Igor about this
    • added complete code documentation
    • Tested extensively to work
  • Functionalization of repeated line creation code, which was used to visually separate generations on the comparison sheet
    • AddLineToComparisonRow()
    • make sure to implement for single runs too
    • added complete code documentation
    • Tested extensively to work
• Which child is best
  • Implemented and commented; in-code comments roughly tell thought process of what method i used over which
  • I used an in-loop method of finding the best, as each child was created, instead of after all were creating and having to loop back through them to find the best
  • put in new code comments

            ' Compare parent and outputs of each child to see which has Max(output)
            
            ' If best is a CHILD, then
            '   record new change to the optimal solution
            '   make child winner of generation
            '   make child the new best generation
            
            ' If best is PARENT, then
            '   nothing, keep best
            '   make parent winner of generation