Steve recently began a career in finance and is conducting a stock analysis for some prospective investments for his first clients: his parents. He requested a VBA macro to conduct an analysis on a specific dataset containing a set group of stocks over a two-year window. Now he wants to increase the scope of his analysis to the entire stock market over a larger time frame.
The purpose of this project is to refactor the original stock analysis script created in VBA and develop a better script that can handle a larger dataset in less time and more efficiently.
Both the original script and the refactored script display the same output data, but the process by which each arrives to that point is dramatically different.
Based on the results generated by the original script, the stock performance of the stocks in the dataset was drastically different in 2017 compared to 2018. In 2017, all stocks but one returned a positive return with half of the 12 stocks generating a return of 50% or more.
Then in 2018, 10 out of 12 stocks report a loss, or a negative return.
As you can see in the images above, the run time for the original script for both the 2017 and 2018 datasets was approximately 1.48 seconds consistently. The run time for the refactored script for both the 2017 and 2018 datsets was approximately 0.19 seconds for the same data.
The refactored code yields such quicker run times because of the way it is designed. The original script has a nested For loop that runs through the entire 3013-line dataset 12 times over, that's one complete cycle of the dataset for each of the 12 tickers.
'4. Loop through the tickers.
For i = 0 To 11
ticker = tickers(i)
totalVolume = 0
'5. Loop through rows in the data.
Sheets(yearValue).Activate
For j = 2 To RowCount
'5a. Find the total volume for the current ticker.
If Cells(j, 1).Value = ticker Then
totalVolume = totalVolume + Cells(j, 8).Value
End If
'5b. Find the starting price for the current ticker.
If Cells(j - 1, 1).Value <> ticker And Cells(j, 1).Value = ticker Then
startingPrice = Cells(j, 6).Value
End If
'5c. Find the ending price for the current ticker.
If Cells(j + 1, 1).Value <> ticker And Cells(j, 1).Value = ticker Then
endingPrice = Cells(j, 6).Value
End If
Next j
'6. Output the data for the current ticker
Worksheets("All Stocks Analysis").Activate
Cells(4 + i, 1).Value = ticker
Cells(4 + i, 2).Value = totalVolume
Cells(4 + i, 3).Value = endingPrice / startingPrice - 1
Next i
On the other hand, the refactored code only runs through the 3013-line dataset one time, but gathers all the necessary information from each line of data efficiently by storing it in one of three different output arrays tickerVolumes, tickerStartingPrices, and tickerEndingPrices.
// '1b) Create three output arrays
Dim tickerVolumes(12) As Long
Dim tickerStartingPrices(12) As Single
Dim tickerEndingPrices(12) As Single
Then the refactored code runs through each 12-line array and uses the tickerIndex variable to access the stock ticker index for the tickers, tickerVolumes, tickerStartingPrices, and tickerEndingPrices arrays to display the stock analysis data in the output cells.
//'4) Loop through your arrays to output the Ticker, Total Daily Volume, and Return.
For k = 0 To 11
Worksheets("All Stocks Analysis").Activate
Cells(4 + k, 1).Value = tickers(k)
Cells(4 + k, 2).Value = tickerVolumes(k)
Cells(4 + k, 3).Value = tickerEndingPrices(k) / tickerStartingPrices(k) - 1
Next k
This is a much faster and more efficient process.
-
When code is refactored it is written more efficiently, the run time is quicker, the required memory decreases, the code is applicable to a dataset that can be added onto. Those are just a few advantages to refactoring code. Some disadvantages are the additional time that it may take a developer to redesign the mechanism of the code and how it is written. This may require the developer to research different/better ways to write the code.
-
These advantages are definitely applicable to the refactoring of the original stock analysis script in VBA. The refactored code shrank in size and in the run time. However, it took at least 5 additional hours to go back and reinvent the mechanism for how the subroutine combed through the dataset to gather the information needed to display the output analysis.