VBA of Wall Street
- Create a VBA macro that can trigger pop-ups and inputs, read and change cell values, and format cells.
- Use for loops and conditionals to direct logic flow.
- Use nested for loops.
- Apply coding skills such as syntax recollection, pattern recognition, problem decomposition, and debugging.
As part of an assignment for the UT Data Boot Camp, an initial stock analysis was conducted for Steve. The original code was then refactored to loop only once. The purpose is to determine if the refactored changes made an impact on the run time.
Using run buttons, Steve will have the ability to put in the year into an input box, which removes any magic numbers. The VBA code uses a timer, arrays, if-then conditional statements, assigns long/string data types, and adds static/conditional formatting.
Comparing the 2017 and the 2018 stocks, the difference in the total daily volume between the two years that resulted in less than a $100,000,000 in increased volume was not enough to generate a positive 2018 return percentage. The tickers ENPH and RUN had would have been considered good investments due to the positive returns in 2018, and both of the tickers had increases greater than $200,000,000 over the 2017 total daily volumes.
Run times for the original code took around .4 seconds.
Run times for the refactored code took around .08 seconds, which is displayed in scientific notation: 8 E -02.
Refactoring the code did make the run times decrease, which optimizes the code.
The original code contained a nested for loop. Included in that outer loop, it also output the data for the current data, which resulted in a significant number of iterations.
With refactoring the code to only have one loop and moving the output to a separate loop, the number of iterations was drastically reduced.
Finding the root cause of a potential bug can be done with refactoring. A programmer can catch duplicated subroutines, unnecessary loops, redundant statements, or code that was used to run down an error but was accidently left in the script. Another advantage is when a peer reviews another programmer’s work, they will come with a fresh perspective and can tidy up the code to make it run leaner.
Programming can have multiple approaches to a solve a problem. In those differences, programmers may have alternative logic steps, which will require testing to see how those differences play out in the script. Refracting a stable code to apply a different set of logic could be costly or introduce new bugs into the system. When juggling tight deadlines, programmers may also have to choose between refactoring or developing new code.
Reducing the number of loops decreases the memory needed for processing the data, which reduces the run time and optimizes the performance of the script. To refactor the code, testing has to be done with each new addition to check for the efficiency of the new code.