assignment_02/ticker_script.vbs at master · mahlonduke/assignment_02 · GitHub

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
131
132
133
134
135
136
137
138
Sub ticker()

' Define variables
Dim ticker As String
Dim open_price, close_price As Double
Dim percent_change As Double

Dim volume, result_row, last_row As Integer
Dim yearly_change As Double

' Define variables for "greatest" totals
Dim greatest_increase As Double
Dim greatest_increase_ticker As String
Dim greatest_decrease As Double
Dim greatest_decrease_ticker As String
Dim greatest_total As Double
Dim greatest_total_ticker As String


For Each ws In Worksheets

    ' Initialize variables
    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    open_price = ws.Cells(2, 3)
    result_row = 2
    greatest_increase = 0
    greatest_decrease = 999
    greatest_total = 0

    ' Loop through the rows
    For i = 2 To LastRow


        volume = volume + ws.Cells(i, 7).Value
        ticker = ws.Cells(i, 1).Value


            ' Check whether the next row is a new ticker
            If (ticker <> ws.Cells(i + 1, 1).Value) Then
                ' We're on a new ticker.  Print the simple results
                ws.Cells(result_row, 9).Value = ticker
                ws.Cells(result_row, 12).Value = volume

                ' Update the close_price for use in calculating the yearly change
                close_price = ws.Cells(i, 6)
                yearly_change = close_price - open_price


                    ' Check whether close or open price are zero before dividing
                    If (close_price = 0 Or open_price = 0) Then

                        ' We have a zero.  Set percent_change to 0
                        percent_change = 0
                        ws.Cells(result_row, 10).Value = yearly_change
                        ws.Cells(result_row, 11).Value = FormatPercent(percent_change, 12)


                        ' Neither of the values are 0.  Divide as normal
                        Else


                            ' No zeros.  Calculate and print the close price
                            percent_change = Round((close_price / open_price), 2)
                            ws.Cells(result_row, 10).Value = yearly_change
                            ws.Cells(result_row, 11).Value = FormatPercent(percent_change, 12)


                    End If

                    ' Set the color of yearly_change
                    ' Positive change set to green
                    If (yearly_change > 0) Then
                      ws.Cells(result_row, 10).Interior.ColorIndex = 4

                        ' Negative change set to red
                        ElseIf (yearly_change < 0) Then
                          ws.Cells(result_row, 10).Interior.ColorIndex = 3

                            ' 0 change set to grey
                            Else
                              ws.Cells(result_row, 10).Interior.ColorIndex = 15

                    End If


                ' Update values for next ticker cycle
                result_row = result_row + 1
                open_price = ws.Cells(i + 1, 3)

                ' -----------------------------------------------------------------------------
                ' Check for the "greatest" totals

                ' Greatest % Increase
                If (percent_change > greatest_increase) Then
                    greatest_increase = percent_change
                    greatest_increase_ticker = ticker

                End If

                ' Greatest % Decrease
                If (percent_change < greatest_decrease) Then
                    greatest_decrease = percent_change
                    greatest_decrease_ticker = ticker

                End If

                ' Greatest Total Volume
                If (volume > greatest_total) Then
                    greatest_total = volume
                    greatest_total_ticker = ticker

                End If

            ' Reset volume for next ticker cycle
            volume = 0

            End If


    Next i

    ' Set the "Greatest" totals
    ws.Cells(2, 16) = FormatPercent(greatest_increase, 12)
    ws.Cells(3, 16) = FormatPercent(greatest_decrease, 12)
    ws.Cells(4, 16) = greatest_total
    ws.Cells(2, 15) = greatest_increase_ticker
    ws.Cells(3, 15) = greatest_decrease_ticker
    ws.Cells(4, 15) = greatest_total_ticker

Next ws


End Sub