Table of Contents
Notes
- 250-252 trading days in a year
- this is why the application has 250 daily simulation rows in the ‘Model’ worksheet
What I did
- Read backtesting notes, definitions on web investopedia, wikipedia
- Thought about backtesting strategies I have heard of and saw at the stock expo
- Though about how I wanted to integrate it into a grand project of mine, or more of as a side project, to first learn the concept of programming a backtesting example
- Decided it would be easier and more appropriate timeline for class, to make a simplistic backtesting mechnism based on a prior excel spreadsheet
- StockTrading.xls is a stock trading simulation
- simulates various technical analysis ‘strategies’
- I had modified this in IS601, but never got it to a 100% stable state
- Decided to use the original implementation for ease of understanding and use
Naming / Versioning Convention
- Named versions of past StockTrading.xls’s
- I had many versions and edits of the StockTrading.xls worksheet
- Naming begins with main file name ‘StockTrading’
- has a fork name ‘Original’ or ‘Strategies’ or ‘Backtesting’
- has a version number, or date ‘071023’; YYMMDD; Oct 23, 2007
- Forking off of ‘StockTrading.Original.v071021.xls’
- Calling it ‘StockTrading.Backtesting.vYYMMDD.xls’
- Updated ‘Summary’ worksheet to be like new summary in Strategies fork
- highlighted important fields
- Added comment explanation for user
- Added run buttons
Backtesting too hard right now
- Tested version with many runs to make sure that it was stable
- Fixed buttons on ‘Summary’ worksheet that were incorrectly transfered over from other version
- They brought their original file name with them in their macro code/reference
When renaming or making a new version name, you must SAVE AS... a new copy from inside of excel- If you copy the file or rename it, macro code links will not be correct, they will be named after old file and get confused
- Discover Yahoo API
- Following links are historical daily prices for AAPL from 01/01/2003 to 01/01/2004
-
- Got really confused and in depth to Yahoo api, decided to use my time elsewhere, can pick this up later
Visit this subject later
'Comparison' worksheet Coding
- Started a new worksheet ‘Comparison’ that will take many trials of different variables and show them in a nice neat chart!
- Split data into two main sections, ‘Outputs’ and ‘Inputs’
- started recording what I needed done, mainly to bring values in different sheets into the ‘Comparison’ sheet
- modified and used recorded code to do what I needed
- recorded code not good enough, used recycled With code to bring over cells
- Tested that cells would come over even if stuff was moved around in the ‘Comparison’ worksheet.
- all relies on the ComparisonFirstCell cell name
- Took longer than expected to implement last row
- Need this to work for sure, to not have any comparison data accidently written over
- Each Comparison result set at the end of a run will be concatenated or added to the end of the comparison list
- Works, tested, tested, tested!
adding a single line of code.
- It is difficult to add variables or outputs to the ‘Comparison’ worksheet. It should be a matter of making a new column, giving it a name, and
- currently, you have to modify hard coded line numbers in the code, in addition to all of the above
Effectively Used!
- I now Started to actually effectively use my functionality to run what-if tests
- I would change 1 or 2 variables and record what would happen
- You can start to see patterns, and hopefully make more correct hypothesis or conclusions on the patterns
- Made different areas of runs... comments and colors on the right of the Comparison page
- This allows you to easily add comments to multiple run sets so you can keep track of what you are doing
- I was able to successfully change variables to get higher returns!
- This method could be used in conjunction with AI algorithms, that repeatedly run these what-if scenarios to get better and better profits
