The aim of this challenge was to create a script using VBA that loops through all multi-year stocks (each year in a separate worksheet)...
The outputs for this challenge includes a summary table showing:
For i = 2 To lRow
'If next cell is not equal to preceding then...
If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then
'retrieve the preceding cell value
closingPrice = ws.Cells(i, 3)
'store the preceding cell text (i.e., ticker label) in desired cell
ws.Range("I" & selectrow).Value = ws.Cells(i, 1).Value
ii) other corresponding informations such as the yearly change for the opening price, percentage change from the opening price and the total stock volume for each ticker
A functionality was added to retrieve the stock with the "Greatest % increase", "Greatest % decrease", and "Greatest total volume":
'Calculate the stock with the greatest increase, greatest decrease and greatest total volume
For i = 2 To lRow_summary
'Set variables to hold values
MaxPrice = WorksheetFunction.Max(ws.Range("K2" & ":" & "K" & lRow_summary))
MinPrice = WorksheetFunction.Min(ws.Range("K2" & ":" & "K" & lRow_summary))
MaxVolume = WorksheetFunction.Max(ws.Range("L2" & ":" & "L" & lRow_summary))
'Return the adjacent row to the specified cell
'--------------------------------------------
'Greatest % Increase
If ws.Cells(i, 11) = MaxPrice Then
ws.Range("P2").Value = MaxPrice
ws.Range("O2").Value = ws.Cells(i, 9)
End If
'--------------------------------------------
'Greatest % Decrease
If ws.Cells(i, 11) = MinPrice Then
ws.Range("P3").Value = MinPrice
ws.Range("O3").Value = ws.Cells(i, 9)
End If
'--------------------------------------------
'Greatest total volume
If ws.Cells(i, 12) = MaxVolume Then
ws.Range("P4").Value = MaxVolume
ws.Range("O4").Value = ws.Cells(i, 9)
End If
'--------------------------------------------
'set the title parameters
ws.Range("N2").Value = "Greatest % Increase"
ws.Range("N3").Value = "Greatest % Decrease"
ws.Range("N4").Value = "Greatest Total Volume"
Next i
Sub reset():
'set the summary table to blank
For Each wsSummary In Worksheets
wsSummary.Range("I:I").Value = ""
wsSummary.Range("J:J").Value = ""
wsSummary.Range("K:K").Value = ""
wsSummary.Range("L:L").Value = ""
wsSummary.Range("N1:P4").Value = ""
'Format yearly price summary color
wsSummary.Range("J:J").Interior.ColorIndex = 0
Next wsSummary
All stocks with a yearly change less than 0 are formatted to red while those greater than 0 are formatted green.
Data for this dataset was generated by edX Boot Camps LLC, and is intended for educational purposes only.