The project's background is to prepare a VBA script to analyze the performance of a dozen stocks on the spreadsheet. Since computing time is precious and the stock market has thousands of stocks. This analysis aims to make the original code run more efficiently so that more stocks can be analyzed in less time.
The year 2017 was a pretty good year for growth. All the stocks (except for ticker "TERP") had increased returns. Ticker "DQ" had the most growth (199.4% of return). However, compared to 2017, 2018 was a lousy year for growth. All of the stocks (except for tickers "ENPH" AND "RUN") experienced negative growth (please see the tables below).
The original code is less efficient because of its nested loop. The code contains an outer loop, "i", that will iterate the tickers from 0 to 11. Here, the "totalVolume" is set to zero, so that it will reset for each of the outer loops. The code also has an inner loop, "j", that will iterate all of the rows. For each row, it will execute three "If ... Then" conditional statements, to check for "the current ticker volume", "starting price" and "ending price" before it loops over to the next row. Once the inner loop loops through all the rows. It will start the next outer loop "i", and repeat the code over and over again until i = 11. This nested loop structure takes longer to run.
'4) Loop through tickers
For i = 0 To 11
ticker = tickers(i)
totalVolume = 0
'5) loop through rows in the data
Worksheets("2018").Activate
For j = 2 To RowCount
'5a) Get total volume for current ticker
If Cells(j, 1).Value = ticker Then
totalVolume = totalVolume + Cells(j, 8).Value
End If
'5b) get starting price for current ticker
If Cells(j - 1, 1).Value <> ticker And Cells(j, 1).Value = ticker Then
startingPrice = Cells(j, 6).Value
End If
'5c) get ending price for 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 data for 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
The refactored code is faster because it has only one loop to execute. First, "tinkerIndex" is set to zero. Three output arrays are created for the three variables of the 11 tickers. Loop "i" is created to iterate over the tickerVolumes(i), and the initial "tickerVolumes" is reset to zero for each ticker. Next, the code will loop over all the rows with three "If...Then" conditional statements to check for "tickerVolumes", "tickerStartingPrices", and "tickerEndingPrices" of the curent tickerIndex before it moves on to the next tickerIndex (tickerIndex + 1).
'1a) Create a ticker Index
tickerIndex = 0
'1b) Create three output arrays
Dim tickerVolumes(11) As Long
Dim tickerStartingPrices(11) As Single
Dim tickerEndingPrices(11) As Single
''2a) Create a for loop to initialize the tickerVolumes to zero.
For i = 0 To 11
tickerVolumes(i) =
Next
''2b) Loop over all the rows in the spreadsheet.
For i = 2 To RowCount
ticker = tickers(tickerIndex)
'3a) Increase volume for current ticker
If Cells(i, 1).Value = ticker Then
tickerVolumes(tickerIndex) = tickerVolumes(tickerIndex) + Cells(i, 8).Value
End If
'3b) Check if the current row is the first row with the selected tickerIndex.
If Cells(i - 1, 1).Value <> ticker And Cells(i, 1).Value = ticker Then
tickerStartingPrices(tickerIndex) = Cells(i, 6).Value
End If
'3c) check if the current row is the last row with the selected ticker
'If the next row’s ticker doesn’t match, increase the tickerIndex.
'If Then
If Cells(i + 1, 1).Value <> ticker And Cells(i, 1).Value = ticker Then
tickerEndingPrices(tickerIndex) = Cells(i, 6).Value
'3d Increase the tickerIndex.
tickerIndex = tickerIndex + 1
End If
Next i
'4) Loop through your arrays to output the Ticker, Total Daily Volume, and Return.
For i = 0 To 11
Worksheets("All Stocks Analysis").Activate
Cells(4 + i, 1).Value = tickers(i)
Cells(4 + i, 2).Value = tickerVolumes(i)
Cells(4 + i, 3).Value = tickerEndingPrices(i) / tickerStartingPrices(i) - 1
Next
The original code for 2017 and 2018 took 0.273 and 0.305 seconds, respectively. The refactored code for 2017 and 2018 took 0.090 and 0.078 seconds, respectively (please see the images below). Compared to the original code, the refactored code is about 3-times faster.
In general, refactored code is faster and more efficient. In finance, less computation time means more immediate conclusion and is helpful in today’s fast-paced economy. However, refactored code is more complex, harder to de-bugged, and error-prone. Therefore, the code must be carefully evaluated to ensure it performs as it should.
The refactored code in this analysis is faster and more efficient than its original code. This is because all variables are in arrays. Therefore, the output is more straightforward, and the code runs faster. However, the array output makes it difficult to track all intermediate calculations and double-check numbers. Also, the refactored code is much harder to write and de-bugged structurally. In contrast, the original code is much easier to write and de-bugged.