-
Notifications
You must be signed in to change notification settings - Fork 0
/
Stock_Market_Analysis
165 lines (114 loc) · 5.35 KB
/
Stock_Market_Analysis
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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
Sub Stock_Market_Analysis():
'First Part
'---------------------------------------
' Loop iterate through all worksheet
For Each ws In ActiveWorkbook.Worksheets
' Declaring variables and setting variables
Dim ticker As String
Dim yearOpen As Double
yearOpen = ws.Cells(2, 3).Value
Dim yearClose As Double
Dim yearChange As Single
Dim percentChange As Variant
Dim Volume As Variant
Dim rowCounter As Variant
Dim resultsCounter As Variant
resultsCounter = 2
Dim lastRow As Long
Volume = 0
' Column headers for the analysis fields
ws.Range("I1").Value = "Ticker"
ws.Range("J1").Value = "Yearly Change"
ws.Range("K1").Value = "Percent Change"
ws.Range("L1").Value = "Total Stock Volume"
ws.Range("O2").Value = "Greatest % Increase"
ws.Range("O3").Value = "Greatest % Decrease"
ws.Range("O4").Value = "Greatest Total Volume"
ws.Range("P1").Value = "Ticker"
ws.Range("Q1").Value = "Value"
' Determine the last row
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
' For loop to iterate through the second row to the last
For rowCounter = 2 To lastRow
'Sums the total volume
Volume = Volume + ws.Cells(rowCounter, 7).Value
'---------------------------------------------------
'Second Part
'---------------------------------------------------
' The percentChange, yearChange and conditionally formats the values under yearChange
If (ws.Cells(rowCounter - 1, 1).Value = ws.Cells(rowCounter, 1).Value And ws.Cells(rowCounter + 1, 1).Value <> ws.Cells(rowCounter, 1).Value) Then
' The year close value at the current row, calculate the changes between close and open value
yearClose = ws.Cells(rowCounter, 6).Value
yearChange = yearClose - yearOpen
' The if statement below handles a division by zero, the result can be changed to NaN string
If yearOpen = 0 And yearClose <> 0 Then
percentChange = yearClose / yearClose
ElseIf yearOpen = 0 And yearClose = 0 Then
percentChange = 0
Else
percentChange = (yearClose - yearOpen) / yearOpen
End If
' Add one than move to the next year open
yearOpen = ws.Cells(rowCounter + 1, 3).Value
' Display year change by ticker
ws.Cells(resultsCounter, 10).Value = yearChange
' Conditional formating for year change; positive (green), negative (red)
If ws.Cells(resultsCounter, 10).Value < 0 Then
ws.Cells(resultsCounter, 10).Interior.ColorIndex = 3
Else
ws.Cells(resultsCounter, 10).Interior.ColorIndex = 4
End If
' Display percent change by ticker
ws.Cells(resultsCounter, 11).Value = percentChange
' Format the numbers as a percentage with % and two decimal places
ws.Cells(resultsCounter, 11).NumberFormat = "0.00%"
End If
'-----------------------------------------------
'Third Part
'-----------------------------------------------
'Finds the total volume per ticker
' To display the total volume per ticker we check if the cell below is different
If (ws.Cells(rowCounter + 1, 1).Value <> ws.Cells(rowCounter, 1).Value) Then
' Display tickers
ws.Cells(resultsCounter, 9).Value = ws.Cells(rowCounter, 1).Value
' Print total volume in column 12
ws.Cells(resultsCounter, 12).Value = Volume
' Reset the volume for the next ticker
Volume = 0
' Increase the results counter by one for the next loop
resultsCounter = resultsCounter + 1
End If
Next rowCounter
'--------------------------------------
'Final Part
'-------------------------------------
' Final part is the final results section for the greatest increase, greatest decrease, and greatest volume comparison
' Declare values to loop for compare
ws.Cells(2, 17).Value = 0
ws.Cells(3, 17).Value = 0
ws.Cells(4, 17).Value = 0
' Start loop for the final results
For resultsCounter = 2 To lastRow
' If the percent change is greater than the temporary value stored in cell (2,17)
If ws.Cells(resultsCounter, 11).Value > ws.Cells(2, 17).Value Then
ws.Cells(2, 17).Value = ws.Cells(resultsCounter, 11).Value
ws.Cells(2, 16).Value = ws.Cells(resultsCounter, 9).Value
End If
' If the percent change is lower than the temporary value stored in cell (3,17)
If ws.Cells(resultsCounter, 11).Value < ws.Cells(3, 17).Value Then
ws.Cells(3, 17).Value = ws.Cells(resultsCounter, 11).Value
ws.Cells(3, 16).Value = ws.Cells(resultsCounter, 9).Value
End If
' If the volume is greater than the temporary value in cell (4, 17)
If ws.Cells(resultsCounter, 12).Value > ws.Cells(4, 17).Value Then
ws.Cells(4, 17).Value = ws.Cells(resultsCounter, 12).Value
ws.Cells(4, 16).Value = ws.Cells(resultsCounter, 9).Value
End If
Next resultsCounter
'Format the results table
ws.Cells(2, 17).NumberFormat = "0.00%"
ws.Cells(3, 17).NumberFormat = "0.00%"
'Format table columns to auto fit
ws.Columns("I:Q").AutoFit
Next ws
End Sub