multi_year_stock_data_VBA.vbs

Sub WallStreet()
    For Each Sh In Worksheets
        
       ' Header setup on all sheets
       
        Sh.Range("I1").Value = "Ticker"
        Sh.Range("L1").Value = "Total Stock Volume"
        Sh.Range("J1").Value = "Yearly Change"
        Sh.Range("K1").Value = "Percent Change"
        Sh.Range("P1").Value = "Ticker"
        Sh.Range("Q1").Value = "Value"
        Sh.Range("O2").Value = "Greatest % Increase"
        Sh.Range("O3").Value = "Greatest % Decrease"
        Sh.Range("O4").Value = "Greatest Total Volume"
        
        Dim Ticker As String
        Dim LRow As Long
        Dim TotalVol As Double
        Dim Cnt As Long
        Dim PreAmount As Long
        Dim YearlyChange As Double
        Dim YearlyOpen As Double
        Dim YearlyClose As Double
        Dim PercentChange As Double
        Dim IncVal As Double
        Dim DecVal As Double
        Dim LRowofVal As Long
        Dim GreatVal As Double
             
        'initializing variables
        
        TotalVol = 0
        Cnt = 2
        IncVal = 0
        DecVal = 0
        GreatVal = 0
        PreAmount = 2
        
        LRow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To LRow
            
            TotalVol = TotalVol + Sh.Cells(i, 7).Value
            
            If Sh.Cells(i + 1, 1).Value <> Sh.Cells(i, 1).Value Then
            Ticker = Sh.Cells(i, 1).Value
                    
            'Ticker symbol
            Sh.Range("I" & Cnt).Value = Ticker
            
            'Total Stock Volume
            Sh.Range("L" & Cnt).Value = TotalVol
            TotalVol = 0
            
            'Yearly Change
            YearlyOpen = Sh.Range("C" & PreAmount)
            YearlyClose = Sh.Range("F" & i)
            YearlyChange = YearlyClose - YearlyOpen
            Sh.Range("J" & Cnt).Value = YearlyChange
            
            'Percent Change
            If YearlyOpen = 0 Then
                PercentChange = 0
            Else
                YearlyOpen = Sh.Range("C" & PreAmount)
                PercentChange = YearlyChange / YearlyOpen
            End If
            Sh.Range("K" & Cnt).NumberFormat = "0.00%"
            Sh.Range("K" & Cnt).Value = PercentChange
            
            'Conditional highlight positive/negative
                If Sh.Range("J" & Cnt).Value >= 0 Then
                    Sh.Range("J" & Cnt).Interior.ColorIndex = 4
                Else
                    Sh.Range("J" & Cnt).Interior.ColorIndex = 3
                End If
            
            Cnt = Cnt + 1
            PreAmount = i + 1
            End If
        Next i
        
        'Find the greatest value
        LRowofVal = Sh.Cells(Rows.Count, 11).End(xlUp).Row
        Sh.Range("Q2").NumberFormat = "0.00%"
        Sh.Range("Q3").NumberFormat = "0.00%"
        
        For j = 2 To LRowofVal
            If Sh.Range("K" & j).Value > IncVal Then
                IncVal = Sh.Range("K" & j).Value
                Sh.Range("Q2").Value = IncVal
                Sh.Range("P2").Value = Sh.Range("I" & j).Value
            End If
            If Sh.Range("K" & j).Value < DecVal Then
                DecVal = Sh.Range("K" & j).Value
                Sh.Range("Q3").Value = DecVal
                Sh.Range("P3").Value = Sh.Range("I" & j).Value
            End If
            If Sh.Range("L" & j).Value > GreatVal Then
                GreatVal = Sh.Range("L" & j).Value
                Sh.Range("Q4").Value = GreatVal
                Sh.Range("P4").Value = Sh.Range("I" & j).Value
            End If
        Next j

    Next Sh
End Sub