VBA Stock Analysis
File size for the yearly datasets was too large to upload to github. Alphabetical was subsituted instead.
VBA script was successfull on both the Yearly and Alphabetical datasets.
Latest script version follows:
Sub TickerPicker()
' Volume Total (vtotal). ' Index Row (inrow). ' Index Column (incol). ' Actual Change (valchange). ' Percentage Change (pchange). ' Index Column (incol). ' Start (start). ' Row Count (rowcount). ' Days (days). ' Daily Change (daychange). ' Average change (avchange). ' Worksheet (ws).
Dim vtotal As Double Dim inrow As Long Dim incol As Integer Dim valchange As Double Dim pchange As Double Dim start As Long Dim rowcount As Long Dim days As Integer Dim daychange As Single Dim avchange As Double Dim ws As Worksheet
' Looping through each worksheet. For Each ws In Worksheets
inrow = 2
incol = 0
vtotal = 0
valchange = 0
start = 2
daychange = 0
' Setting Headers ws.Range("I1").value = "Ticker" ws.Range("J1").value = "Yearly Change ($)" ws.Range("K1").value = "Yearly Change (%)" ws.Range("L1").value = "Total Stock Volume" ws.Range("O1").value = "Ticker" ws.Range("P1").value = "Value" ws.Range("N2").value = "Greatest % Increase" ws.Range("N3").value = "Greatest % Decrease" ws.Range("N4").value = "Greatest Total Volume"
' Retrieving row # of the last row with data. rowcount = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For inrow = 2 To rowcount
' Checking if the Ticker name is the same.
If ws.Cells(inrow + 1, 1).value <> ws.Cells(inrow, 1).value Then
' Store results in a variable.
vtotal = vtotal + ws.Cells(inrow, 7).value
If vtotal = 0 Then
'Print the results
ws.Range("I" & 2 + incol).value = ws.Cells(inrow, 1).value
ws.Range("J" & 2 + incol).value = 0
ws.Range("K" & 2 + incol).value = "%" & 0
ws.Range("L" & 2 + incol).value = 0
Else
If ws.Cells(start, 3) = 0 Then
For find_value = start To inrow
If ws.Cells(find_value, 3).value <> 0 Then
start = find_value
Exit For
End If
Next find_value
End If
valchange = (ws.Cells(inrow, 6) - ws.Cells(start, 3))
pchange = valchange / ws.Cells(start, 3)
start = inrow + 1
' Print Values and format them according to specifications.
ws.Range("I" & 2 + incol) = ws.Cells(inrow, 1).value
ws.Range("J" & 2 + incol) = valchange
ws.Range("J" & 2 + incol).NumberFormat = "0.00"
ws.Range("K" & 2 + incol).value = pchange
ws.Range("K" & 2 + incol).NumberFormat = "0.00%"
ws.Range("L" & 2 + incol).value = vtotal
'Set cell shading.
Select Case valchange
Case Is > 0
ws.Range("J" & 2 + incol).Interior.ColorIndex = 4
Case Is < 0
ws.Range("J" & 2 + incol).Interior.ColorIndex = 3
Case Else
ws.Range("J" & 2 + incol).Interior.ColorIndex = 0
End Select
End If
vtotal = 0
valchange = 0
incol = incol + 1
days = 0
daychange = 0
Else
' If "Ticker" is still the same, add the results.
vtotal = vtotal + ws.Cells(inrow, 7).value
End If
Next inrow
'Print the Min and Max Values in Column P.
ws.Range("P2") = "%" & WorksheetFunction.Max(ws.Range("K2:K" & rowcount)) * 100
ws.Range("P3") = "%" & WorksheetFunction.Min(ws.Range("K2:K" & rowcount)) * 100
ws.Range("P4") = WorksheetFunction.Max(ws.Range("L2:L" & rowcount))
increase_number = WorksheetFunction.Match(WorksheetFunction.Max(ws.Range("K2:K" & rowcount)), ws.Range("K2:K" & rowcount), 0)
decrease_number = WorksheetFunction.Match(WorksheetFunction.Min(ws.Range("K2:K" & rowcount)), ws.Range("K2:K" & rowcount), 0)
volume_number = WorksheetFunction.Match(WorksheetFunction.Max(ws.Range("L2:L" & rowcount)), ws.Range("L2:L" & rowcount), 0)
' Print Ticker values in column O
ws.Range("O2") = ws.Cells(increase_number + 1, 9)
ws.Range("O3") = ws.Cells(decrease_number + 1, 9)
ws.Range("O4") = ws.Cells(volume_number + 1, 9)
Next ws
End Sub