-
Notifications
You must be signed in to change notification settings - Fork 1
/
The VBA of Wall Street.vb
141 lines (88 loc) · 3.24 KB
/
The VBA of Wall Street.vb
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
Sub Stocks()
For Each ws In Worksheets
ws.Range("I1") = "Ticker"
ws.Range("J1") = "Yearly Change"
ws.Range("K1") = "Percent Change"
ws.Range("L1") = "Total Stock Volume"
Dim Ticker As String
Dim YearlyChange As Double
Dim PercentChange As Double
Dim Volume As Double
Dim StockOpen As Double
Dim StockClose As Double
Dim lastrow As Double
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Volume = 0
Dim Summary_Table_Row As Double
Summary_Table_Row = 2
For i = 2 To lastrow
If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1) Then
Ticker = ws.Cells(i, 1).Value
Volume = Volume + ws.Cells(i, 7).Value
ws.Range("I" & Summary_Table_Row).Value = Ticker
ws.Range("L" & Summary_Table_Row).Value = Volume
Volume = 0
StockClose = ws.Cells(i, 6)
If StockOpen = 0 Then
YearlyChange = 0
PercentChange = 0
Else:
YearlyChange = StockClose - StockOpen
PercentChange = (StockClose - StockOpen) / StockOpen
End If
ws.Range("J" & Summary_Table_Row).Value = YearlyChange
ws.Range("K" & Summary_Table_Row).Value = PercentChange
ws.Range("K" & Summary_Table_Row).Style = "Percent"
ws.Range("K" & Summary_Table_Row).NumberFormat = "0.00%"
Summary_Table_Row = Summary_Table_Row + 1
ElseIf ws.Cells(i - 1, 1).Value <> ws.Cells(i, 1) Then
StockOpen = ws.Cells(i, 3)
Else: Volume = Volume + ws.Cells(i, 7).Value
End If
Next i
For r = 2 To lastrow
If ws.Range("J" & r).Value > 0 Then
ws.Range("J" & r).Interior.ColorIndex = 4
ElseIf ws.Range("J" & r).Value < 0 Then
ws.Range("J" & r).Interior.ColorIndex = 3
End If
Next r
ws.Range("P1").Value = "Ticker"
ws.Range("Q1").Value = "Value"
ws.Range("O2").Value = "Greatest % Increase"
ws.Range("O3").Value = "Greatest % Decrease"
ws.Range("O4").Value = "Greatest Total Volume"
Dim GreatestIncrease As Double
Dim GreatestDecrease As Double
Dim GreatestVolume As Double
GreatestIncrease = 0
GreatestDecrease = 0
GreatestVolume = 0
For a = 2 To lastrow
If ws.Cells(a, 11).Value > GreatestIncrease Then
GreatestIncrease = ws.Cells(a, 11).Value
ws.Range("Q2").Value = GreatestIncrease
ws.Range("Q2").Style = "Percent"
ws.Range("Q2").NumberFormat = "0.00%"
ws.Range("P2").Value = ws.Cells(a, 9).Value
End If
Next a
For b = 2 To lastrow
If ws.Cells(b, 11).Value < GreatestDecrease Then
GreatestDecrease = ws.Cells(b, 11).Value
ws.Range("Q3").Value = GreatestDecrease
ws.Range("Q3").Style = "Percent"
ws.Range("Q3").NumberFormat = "0.00%"
ws.Range("P3").Value = ws.Cells(b, 9).Value
End If
Next b
For c = 2 To lastrow
If ws.Cells(c, 12).Value > GreatestVolume Then
GreatestVolume = ws.Cells(c, 12).Value
ws.Range("Q4").Value = GreatestVolume
ws.Range("P4").Value = ws.Cells(c, 9).Value
End If
Next c
ws.Columns("A:Q").AutoFit
Next ws
End Sub