Skip to content

S-Bonillas/Mod2-VBA-Stock-Analysis

Repository files navigation

Mod2-VBA-Clever-Naming-

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

About

VBA Demonstration

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages