This project aimed to refactor a Microsoft Excel VBA code to improve the implementation and efficiency of the original VBA code for collecting stock information in both 2017 and 2018. The purpose of this code is to determine which stocks may be worth investing in and can analyze an entire dataset of stocks. Even though the current focus is on stocks from Green Companies, this newly refactored code will also be able to work for larger datasets in a timely manner. For this workbook specifically, we created two charts that compare stock information from 12 different companies using the ticker value, the total daily volume, and the yearly return.
Using my original code, I was able to achieve the desired results, yet with a run time of 0.9453125 for stocks from 2017 and a run time of 0.984375 for stocks from 2018. To improve this time, some changes needed to be made. Before refactoring the code, I first copied the original code from my “All Stocks Analysis” macro. I made no changes to the code that set the runtime, created an input box, labeled the chart headers, added a ticker array, activated the worksheet, and to find the number of rows to loop over. Key changes were then made to add a tickerIndex variable and three output arrays for ticker volumes, ticker starting prices, and ticker ending prices were added before looping through all rows and running our conditionals. You can see these changes in the image below.
Image 1: Refactored code to create output arrays for tickerVolumes, tickerStartingPrices, and tickerEndingPrices
We were able to use the tickerIndex variable to access the stock ticker index for all our arrays. Within the script loops, we were able to successfully read and store data from each row for the Ticker, Ticker Volumes, Ticker Starting Prices, and Ticker Ending Prices. Once complete, the code for formatting the cells was included within the same macro to automate the formatting when switching between years. You can view the refactored code for the loops and formatting below.
Image 2: Refactored code to loop through each row to read and store data as well as the formatting code for the table.
After refactoring our code, debugged and tested our new macro to ensure it created the same results, yet faster. We found that the run time decreased to 0.2421875 for 2017 and 0.1835938 for 2018, successfully creating a faster and more efficient code. The run time for both years can be viewed in the images below.
Image 3: Run time of refactored code for stocks from 2017.
Image 4: Run time of refactored code for stocks from 2018.
After reviewing this project, refactoring code can help make it cleaner and more organized which can lead to improved software efficiency through reduced run time and use of less storage, it becomes easier to read and debug and can allow for faster programming overall. Yet, while the efficiency and time for the refactored code improve, one disadvantage that comes with refactoring is how much time the task takes to be complete. There may also be cases of working with larger programming code which can pose the risk of getting lost in the code or not understanding where to go next after making changes.
Looking specifically at our VBA script for the stock analysis, we can see that one important advantage of refactoring the original code was the reduction in run time for the macros. In reference to our results, the run time of our original code decreased from a run time of 0.9453125 for stocks from 2017 with the original code to 0.2421875 seconds. The run time of 0.984375 for stocks from 2018 respectively decreased to 0.1835938 with the refactored code. One disadvantage worth making note of when refactoring code there can be risks of having duplicate code, which is why copying the original and using comments can help you know where you are at in the code.