#VBA Refactoring Challenge
The main goal of this project is to refactor a VBA script to make it more efficient. The provided VBA script calculates and displays the total daily volumes and returns for stocks using data from an Excel spreadsheet. The refactored VBA code should achieve the same results / yield the same output as the original code, but have better readability, and a shorter running time. A secondary objective of this project is to analyze the data obtained through the refactored VBA code to understand the performance of the stocks in the provided dataset.
In general, 2017 returns were higher than those of 2018. The only exceptions were the RUN (+78% higher return) and TERP (2% higher return) stocks, which performed better in 2018 than in 2017. Something to note is that while TERP’s return increased, it is still negative in 2018 (it went from -7.2% to -5%). On the other hand, the RUN stock went from a 5.5% return in 2017 to an 84% return in 2018. Another stock to note is the ENPH, while the return of this stock decreased by 48% between 2017 and 2018, its 2018 returns are still positive at 81.9%
The execution times of the refactored code are significantly faster than those of the original script. This is a result of using arrays in the code to store and retrieve information. More specifically, the original code used "Double" type variables to store the starting price, ending price, and volume of each ticker. Through a nested loop, the variables would be assigned the correct values for each ticker and then used to output the correct values in an Excel spreadsheet. While the results that the code yielded were correct, they came at a very costly "run-time" as the inner loop with if statement calculations had to run for each ticker. In the refactored code, we used arrays to store the starting price, ending price, and volume of each ticker. This allowed us to use a simple for loop to store the starting price, ending price, and volume of each ticker in the relevant arrays. To output the values, we simply created another for loop, retrieiving the data from the arrays.
If done correctly, there should not be any disadvantages to refactoring code as it should be cleaner (more readable) and more efficient (faster), without sacrificing results (achieves the same outcomes as the original code). If done incorrectly, a possible disadvantage of refactoring code could be to sacrifice readability for efficiency (i.e. faster but hard to follow) or to inadvertently change the logic of the code (the refactored code yields incorrect results).
The run time and readability of the refactored code in this challenge is better than that of the original script. Additionally, the output/result is the same as the one from the original code. As such, refactoring the original VBA script helped us have a cleaner and more efficient code (no disadvantages).