In module 2, we were introduced to Steve, a recent grad with a degree in finance. We were also tasked with helping Steve analyze an Excel file containing stock data from 2017 and 2018. Now, Steve needs help analyzing the dataset of the entire stock market over the last few years. To do this, I first used the Microsoft Visual Basics Editor also known as Microsoft Visual Basics for Applications (VBA) to refactor the provided VBA script. I then collected the required data and tested the code’s performance. In the end, stock data from 2018 and refactor data from 2018 were compared.
The purpose of this project is to refactor the VBA script for the 2018 stock dataset and to measure this VBA script’s performance.
The first macro created in VBA was used to analyze stock data from 2017 and 2018. Figure 1 shows the analyzed stock data for 2018.
Figure 1
As seen in Figure 1, it took about 0.609375 seconds for the code to analyze the 2018 stock data. We were then provided with a starter code file that was used to refactor the code as seen in Figure 2.
Figure 2
As an example, Figure 2, shows the comments in green with the filled in code right below it in blue and black colors. Once the code was refactored, the code was run on the 2018 stock data again. Figure 3 shows the results.
Figure 3
As seen in Figure 3, it took around 0.0625 seconds for the restructured code to analyze the 2018 stock data. Overall, the code improved by a factor of ten meaning the refactored code was 10 times faster than the original code.
Some of the advantages of refactoring code would be speed and legibility. When the code is refactored, some lines of codes can be removed if they are being repeated, and this can lead to a faster run time. This can also make the code more readable. However, refactoring can also have some disadvantages as it can be time consuming. It would be almost impossible to refactor code on a short deadline or if the code is too long. In the end, I recommend refactoring a few lines of code but not complex code.