hw2_scripts_tinu_ngo 3.65 KB
Newer Older
tinubates@gmail.com's avatar
tinubates@gmail.com committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130
Attribute VB_Name = "Module1"
Sub Main()

    'loop through each worksheet and call 2 subs
    Dim sheet As Worksheet
    Application.ScreenUpdating = False
    For Each sheet In ThisWorkbook.Sheets
        sheet.Select
        Call summarize
        Call challenge
    Next
    Application.ScreenUpdating = True
    
End Sub

Sub clear()
    Dim sheet As Worksheet
    Application.ScreenUpdating = False
    For Each sheet In ThisWorkbook.Sheets
        sheet.Select
        Range("J:R").clear
    Next
    Application.ScreenUpdating = True
End Sub

Sub summarize()

    'Print summary headers
    Range("J1").Value = "Ticker"
    Range("K1").Value = "Yearly Change"
    Range("L1").Value = "% Change"
    Range("M1").Value = "Total Stock Volume"
    
    'Declare variables
    Dim lastrow As Long
    Dim opeprice As Double
    Dim closeprice As Double
    Dim yearly As Double
    Dim percent As Double
    Dim volume As Double
    Dim printrow As Integer

    'Assign initial values
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    openprice = Cells(2, 3).Value
    closeprice = 0
    volume = Range("G2").Value
    printrow = 2
    
    For i = 2 To lastrow
        'if next line is still the same ticker - add to volume
        If Cells(i + 1, 1).Value = Cells(i, 1).Value Then
            volume = volume + Cells(i + 1, 7).Value
        'if next line is a different ticker - assign close price, calculate, print, reset
        Else
            'assign values and print
            closeprice = Cells(i, 6).Value
            yearly = closeprice - openprice
            'catch divide by 0 error
            If openprice <> 0 Then
                percent = yearly / openprice
            Else
                percent = 0
            End If
            Cells(printrow, 10).Value = Cells(i, 1).Value
            Cells(printrow, 11).Value = yearly
            Cells(printrow, 12).Value = percent
            Cells(printrow, 13).Value = volume
            'conditional formatting
            If Cells(printrow, 11).Value < 0 Then
                Cells(printrow, 11).Interior.ColorIndex = 3
            Else
                Cells(printrow, 11).Interior.ColorIndex = 4
            End If
            'reset values
            volume = Cells(i + 1, 7).Value
            printrow = printrow + 1
            openprice = Cells(i + 1, 3).Value
        End If
    Next i
    
End Sub

Sub challenge()

    'Print challenge headers
    Range("Q1").Value = "Ticker"
    Range("R1").Value = "Value"
    Range("P2").Value = "Greatest % increase"
    Range("P3").Value = "Greatest % decrease"
    Range("P4").Value = "Greatest total volume"
    
    'Declare variables
    Dim lastrow As Long
    Dim increase, decrease, volume As Double
    Dim increaseticker, decreaseticker, volumeticker As String
    
    'Assign initial values
    lastrow = Cells(Rows.Count, 10).End(xlUp).Row
    increase = 0
    decrease = 0
    volume = 0
    
    For i = 2 To lastrow
        'check for largest increase
        If Cells(i, 12).Value > increase Then
            increase = Cells(i, 12).Value
            increaseticker = Cells(i, 10).Value
        End If
        'check for largest decrease
        If Cells(i, 12).Value < decrease Then
            decrease = Cells(i, 12).Value
            decreaseticker = Cells(i, 10).Value
        End If
        'check for largest volume
        If Cells(i, 13).Value > volume Then
            volume = Cells(i, 13).Value
            volumeticker = Cells(i, 10).Value
        End If
    Next i

    Range("Q2").Value = increaseticker
    Range("Q3").Value = decreaseticker
    Range("Q4").Value = volumeticker
    Range("R2").Value = increase
    Range("R3").Value = decrease
    Range("R4").Value = volume

End Sub