VBA_Script_JG.txt

Wall Street Data Analysis Using VBA Script

Sub Analysis_1()

'Performed:
    'Created a loop through each year of stock data and and grab the total amount of volume each stock had over the year and displayed the ticker symbol to coincide with the total volume.

    
'Loop thru all WORKSHEETS
    For Each ws In Worksheets
        
        'Created a variable to hold the ticker, total stk volume, and lastrow of each wksht
        Dim Ticker As String
        
        'Dim TotalStkVol As Long - this was creating "overflow errors" same with
        'defining lastrow as a long because there's too many rows
        
        ws.Cells(1, 9).Value = "Ticker Symbol"
        ws.Cells(1, 10).Value = "Total Stock Volume"
        
        TotalStkVol = 0
        
        Dim SummaryTableTick As Integer
        SummaryTableTick = 2
        
        lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        
        'setting up the for loop and conditionals
        For Row = 2 To lastrow
        
            If ws.Cells(Row + 1, 1).Value <> ws.Cells(Row, 1).Value Then
                
                'Set the Ticker Name
                Ticker = ws.Cells(Row, 1).Value
                
                'Add to volume total
                TotalStkVol = TotalStkVol + ws.Cells(Row, 7).Value
                
                'Print Ticker Name in the Summary Table
                ws.Range("I" & SummaryTableTick).Value = Ticker
                
                'Print the TotalStkVol to the Summary Table
                ws.Range("J" & SummaryTableTick).Value = TotalStkVol
                
                'add one to the summary table row
                SummaryTableTick = SummaryTableTick + 1
                
                'reset the TotalStkVol back to zero
                TotalStkVol = 0
                
            Else
                TotalStkVol = TotalStkVol + ws.Cells(Row, 7).Value
        
            End If
        
        Next Row
    
    Next ws
    
End Sub



Sub Analysis_2()

    'Performed:
        'Created a script that will loop through all the stocks and take the following info.
            'Yearly change from what the stock opened the year at to what the closing price was.
            'The percent change from the what it opened the year at to what it closed.
            'The total Volume of the stock
            'Ticker Symbol
            'You should also have conditional formatting that will highlight positive
            'change in green and negative change in red.

    
'Set up the loops thru all WORKSHEETS
    For Each ws In Worksheets
        
        'Declare all variables and define them
        Dim Ticker As String
        Dim TotalOpenPrice As Double
        Dim TotalClosePrice As Double
        Dim Yearly_Change As Double
        Dim Percent_Change As Double
        
        'define the variables within each wksht
        TotalStkVol = 0
        TotalOpenPrice = 0
        TotalClosePrice = 0
        Yearly_Change = TotalClosePrice - TotalOpenPrice
        
        Dim SummaryTableTick2 As Integer
        SummaryTableTick2 = 2
        
        'Set up SummaryTableTick2
        ws.Cells(1, 9).Value = "Ticker Symbol"
        ws.Cells(1, 10).Value = "Yearly Change"
        ws.Cells(1, 11).Value = "Percent Change"
        ws.Cells(1, 12).Value = "Total Stock Volume"
        
        lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        
        'setting up the for loop and conditionals
        For Row = 2 To lastrow
        
            If ws.Cells(Row + 1, 1).Value <> ws.Cells(Row, 1).Value Then
                
                'Set the Ticker Name
                Ticker = ws.Cells(Row, 1).Value
                
                'Add to totals for: TotalOpenPrice, TotalClosePrice, TotalStkVol
                
                TotalOpenPrice = TotalOpenPrice + ws.Cells(Row, 3).Value
                TotalClosePrice = TotalClosePrice + ws.Cells(Row, 6).Value
                TotalYearlyChange = (TotalClosePrice - TotalOpenPrice)
                TotalStkVol = TotalStkVol + ws.Cells(Row, 7).Value
                
                'Print Ticker Name in the Summary Table
                ws.Range("I" & SummaryTableTick2).Value = Ticker
                
                'Print "YEARLY CHANGE" to Summary Table
                ws.Range("J" & SummaryTableTick2).Value = TotalYearlyChange
                
                    'setting up condition that if the total yearly change is positive color the interior green
                    'otherwise if negative, color the interior red
                    If (TotalYearlyChange > 0) Then
                        ws.Range("J" & SummaryTableTick2).Interior.ColorIndex = 4
                    ElseIf (TotalYearlyChange = 0) Then
                        ws.Range("J" & SummaryTableTick2).Interior.ColorIndex = 2
                    Else
                        ws.Range("J" & SummaryTableTick2).Interior.ColorIndex = 3
                    End If
                    
                    'Total PERCENT CHANGE - Need to account for If TotalYearlyChange = 0 (will cause an error)
                    If (TotalYearlyChange = 0) Then
                        ws.Range("K" & SummaryTableTick2).Value = 0
                    Else
                    
                      'Print "Percent Change" to Summary Table = (totalcloseprice - totalopenprice)/ totalopenprice)
                        ws.Range("K" & SummaryTableTick2).Value = TotalYearlyChange / TotalOpenPrice
                        
                        'Change format for Total Percentage Change to Percent style
                        ws.Range("K" & SummaryTableTick2).Style = "Percent"
                    End If
                    
                   
                'Print the TotalStkVol to the Summary Table
                ws.Range("L" & SummaryTableTick2).Value = TotalStkVol
                
                'add one to the summary table row
                SummaryTableTick2 = SummaryTableTick2 + 1
                
                'reset the TotalStkVol back to zero
                TotalStkVol = 0
                
            'Else the tickers from one cell below to the cell above are equal, then continue
            'to sum up the following:
            Else
                TotalOpenPrice = TotalOpenPrice + ws.Cells(Row, 3).Value
                TotalClosePrice = TotalClosePrice + ws.Cells(Row, 6).Value
                TotalStkVol = TotalStkVol + ws.Cells(Row, 7).Value
                
            End If
        
        Next Row
        
    Next ws
    
End Sub



Sub Analysis_3()

'Performed: locate the stock with the: "Greatest % increase", "Greatest % Decrease" and "Greatest total volume"


'Set up the loops thru all WORKSHEETS
    For Each ws In Worksheets
        
        'Declare all variables and define them
        Dim Ticker As String
        Dim TotalOpenPrice As Double
        Dim TotalClosePrice As Double
        Dim Yearly_Change As Double
        Dim Percent_Change As Double
        
        
        
        'define the variables within each wksht
        TotalStkVol = 0
        TotalOpenPrice = 0
        TotalClosePrice = 0
        Yearly_Change = TotalClosePrice - TotalOpenPrice
        
        Dim SummaryTableTick2 As Integer
        SummaryTableTick2 = 2
        
        'Set up SummaryTableTick2
        ws.Cells(1, 9).Value = "Ticker Symbol"
        ws.Cells(1, 10).Value = "Yearly Change"
        ws.Cells(1, 11).Value = "Percent Change"
        ws.Cells(1, 12).Value = "Total Stock Volume"
        
        lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

        'setting up the for loop and conditionals
        For Row = 2 To lastrow
        
            If ws.Cells(Row + 1, 1).Value <> ws.Cells(Row, 1).Value Then
                
                'Set the Ticker Name
                Ticker = ws.Cells(Row, 1).Value
                
                'Add to totals for: TotalOpenPrice, TotalClosePrice, TotalStkVol
                
                TotalOpenPrice = TotalOpenPrice + ws.Cells(Row, 3).Value
                TotalClosePrice = TotalClosePrice + ws.Cells(Row, 6).Value
                TotalYearlyChange = (TotalClosePrice - TotalOpenPrice)
                TotalStkVol = TotalStkVol + ws.Cells(Row, 7).Value
                
                'Print Ticker Name in the Summary Table
                ws.Range("I" & SummaryTableTick2).Value = Ticker
                
                'Print "YEARLY CHANGE" to Summary Table
                ws.Range("J" & SummaryTableTick2).Value = TotalYearlyChange
                
                    'setting up condition that if the total yearly change is positive color the interior green
                    'otherwise if negative, color the interior red
                    If (TotalYearlyChange > 0) Then
                        ws.Range("J" & SummaryTableTick2).Interior.ColorIndex = 4
                    ElseIf (TotalYearlyChange = 0) Then
                        ws.Range("J" & SummaryTableTick2).Interior.ColorIndex = 2
                    Else
                        ws.Range("J" & SummaryTableTick2).Interior.ColorIndex = 3
                    End If
                    
                    'Total PERCENT CHANGE - Need to account for If TotalYearlyChange = 0 (will cause an error)
                    If (TotalYearlyChange = 0) Then
                        ws.Range("K" & SummaryTableTick2).Value = 0
                    Else
                    
                      'Print "Percent Change" to Summary Table = (totalcloseprice - totalopenprice)/ totalopenprice)
                        ws.Range("K" & SummaryTableTick2).Value = TotalYearlyChange / TotalOpenPrice
                        
                        'Change format for Total Percentage Change to Percent style
                        ws.Range("K" & SummaryTableTick2).Style = "Percent"
                    End If
                    
                   
                'Print the TotalStkVol to the Summary Table
                ws.Range("L" & SummaryTableTick2).Value = TotalStkVol
                
                'add one to the summary table row
                SummaryTableTick2 = SummaryTableTick2 + 1
                
                'reset the TotalStkVol back to zero
                TotalStkVol = 0
                
            'Else the tickers from one cell below to the cell above are equal, then continue
            'to sum up the following:
            Else
                TotalOpenPrice = TotalOpenPrice + ws.Cells(Row, 3).Value
                TotalClosePrice = TotalClosePrice + ws.Cells(Row, 6).Value
                TotalStkVol = TotalStkVol + ws.Cells(Row, 7).Value
                
            End If
        
        Next Row
            
         'set up SummaryTableGreatest
        'Dim SummaryTableGreatest As Integer
        'SummaryTableGreatest = 2
        
        ws.Cells(1, 16).Value = "Ticker"
        ws.Cells(1, 17).Value = "Value"
        ws.Cells(2, 15).Value = "Greatest % Increase"
        ws.Cells(3, 15).Value = "Greatest % Decrease"
        ws.Cells(4, 15).Value = "Greatest Total Volume"
        
        'define the max and min variables for the percent change
        Dim Max As Double
        Dim Min As Double

        Max = Application.WorksheetFunction.Max(ws.Columns(11))
        Min = Application.WorksheetFunction.Min(ws.Columns(11))
       
        
        lastrowPercent = ws.Cells(Rows.Count, 11).End(xlUp).Row

        'new loop for returning greatest % increase and decrease
        'and total vol
        For row2 = 2 To lastrowPercent
                
                'set up max function for percentage change
                If (ws.Cells(row2, 11).Value = Max) Then
                    ws.Cells(2, 16).Value = ws.Cells(row2, 9).Value
                    ws.Cells(2, 17).Value = Max
                    ws.Cells(2, 17).Style = "Percent"
                
                'set up min function for percentage change
                ElseIf (ws.Cells(row2, 11).Value = Min) Then
                    ws.Cells(3, 16).Value = ws.Cells(row2, 9).Value
                    ws.Cells(3, 17).Value = Min
                    ws.Cells(3, 17).Style = "Percent"
                
                End If
        Next row2
                    
                    
         MaxTotalVol = Application.WorksheetFunction.Max(ws.Columns(12))
         lastrowMaxVol = ws.Cells(Rows.Count, 12).End(xlUp).Row
         
        'Start a new loop for TotalMaxVolume
        For row3 = 2 To lastrowMaxVol
                 
            If (ws.Cells(row3, 12).Value = MaxTotalVol) Then
                ws.Cells(4, 16).Value = ws.Cells(row3, 9).Value
                ws.Cells(4, 17).Value = ws.Cells(row3, 12).Value
            End If
                 
        Next row3
            
    Next ws
    
End Sub