The purpose of this project was to refactor Excel VBA code that performs a stock analysis for 12 stocks during the years 2017 & 2018. The goal for this project was to increase efficiency by improving the overall logic of the code and make it easier for future users to read as wells as understand overall stocks performance from 2017 to 2018.
The data used to perform the analysis consists of 12 specific stocks during the years 2017 & 2018. Each year's data is separately displayed on their own respective worksheets and the data contains stock tickers, their daily trading volume, and various daily stock price metrics. I wrote a code that identifies each stock ticker and calculates the annual return percentage and total daily trading volume.
I measured the refactored code performance by adding a script that measures how long the code takes to identify each stock based on an inputted year and perform two calculations that provide the annual return percentage & total daily trading volume. Once fully executed, the script outputs the elapsed time in a message box. Below are images of the message box displaying the time results.
Original Code Time Results - 2017
Original Code Time Results - 2018
Refactored Code Time Results - 2017
Refactored Code Time Results - 2018
As you can see below, 11 out of 12 stocks provided a positive annual return in 2017 while only 2 out of the 12 stocks provided a positive annual return in 2018.
Stock Analysis Results - 2017
Stock Analysis Results - 2018
In general, refactoring code is a key part of the coding process. Its' general advantages are more efficient code, using less memory, and improving code logic to make it easier for future users to understand. However, it can often be time consuming and involve multiple developers. In a work environment that is demanding and fast-paced, refactoring complex code could introduce new bugs/errors and ultimately undermine efficiency.
Refactoring helped me solidify my understanding of important VBA concepts and pushed me to think about solving a problem in a different way. By the end of this project's code was faster and cleaner for any user to understand. In my opinion few disadvantages were discovered. One arguement could be made in regards to how much time I spent refactoring. Are a few hours of time worth saving approximately .6875 seconds? In most scenarios, yes, however for this specific project the original code written would have sufficed.
Overall, I still believe the advantages of refactoring code outweighed any disadvantage and creating this project improved my coding mindset and thought process.