Coding in VBA to crunch excel spreadsheet data wasn’t very fun, but it was good mental exercise and got my mind working. I found the language obtuse compared to python, and making mistakes that output to the spreadsheet was annoying to clean up. Not to mention the numerous times I crashed the excel application with infinite loops, lol. Either way, I am proud of the code I wrote. I don’t do much stock analysis in excel, and typically use SQL and python for that stuff, but I still might revisit this exercise and use it to crunch some of the stock and option data I collect. My code produces the I-L values:
Sub stock_hw()
Dim total_rows As Double
Dim sym_array() As Variant
Dim total_volume As Double
Dim i As Long
Dim j As Long
Dim unique_syms As New Collection
Dim sym As Variant
Dim ws As Worksheet
Dim unique_index As Double
Dim first_price As Double
Dim last_price As Double
Dim first_bool As Boolean
Dim prcnt_chng As Double
'Creates the loop to go through each worksheet in the workbook
For Each ws In Worksheets
'activate the worksheet and enter new column names in J and K
ws.Activate
'insert the headers
Cells(1, 9).Value = "Ticker"
Cells(1, 12).Value = "Total Volume"
Cells(1, 10).Value = "Yearly Change"
Cells(1, 11).Value = "Percent Change"
'calculate the total rows to use in the array range
total_rows = Rows(Rows.Count).End(xlUp).Row
'add stock tickers to array
sym_array = Range(Cells(2, 1), Cells(total_rows, 1)).Value
'loop through array to find distinct values and add them to a collection object
'set the collection to Nothing to reset it on each worksheet
Set unique_syms = Nothing
'resuming on error is needed or it will error on duplicate values
On Error Resume Next
For Each sym In sym_array
unique_syms.Add sym, sym
Next
'populate column J with distinct ticker values
For i = 1 To unique_syms.Count
Cells(i + 1, 9).Value = unique_syms(i)
Next i
'USED FOR DEBUGGING 'Cells(1, 11).Value = Cells(2, 1).Value
'USED FOR DEBUGGING 'Cells(1, 14).Value = Cells(2, 9).Value
'check to see if the distinct value = sym and total the volume if it does
'j loops through the master ticker list
'unique_index is used to stay on the correct distinct ticker
'set the first boolean to true so the logic knows to store the first price
first_bool = True
unique_index = 2
For j = 2 To total_rows + 1
If Cells(j, 1).Value = Cells(unique_index, 9).Value Then
total_volume = Cells(j, 7).Value + total_volume
'Logic for storing the first price when it is greater than 0
If first_bool = True And Cells(j, 6).Value > 0 Then
first_price = Cells(j, 6).Value
'set the first boolean to false so the logic doesn't change the first price
first_bool = False
End If
Else
'subtract 1 from j so you don't skip the first row for the next distinct ticker
'also so you can select the correct last closing price
j = j - 1
'set the last closing price value
last_price = Cells(j, 6).Value
'output volume
Cells(unique_index, 12).Value = total_volume
'output difference between start and end price
Cells(unique_index, 10).Value = last_price - first_price
'calculate the percent change
prcnt_chng = ((last_price / first_price) - 1) * 100
'output the percent change
Cells(unique_index, 11).Value = prcnt_chng
'format the cell color based on the percent change being positive or negative or 0
If prcnt_chng > 0 Then
Cells(unique_index, 10).Interior.ColorIndex = 4
ElseIf prcnt_chng < 0 Then
Cells(unique_index, 10).Interior.ColorIndex = 3
Else
Cells(unique_index, 10).Interior.ColorIndex = 2
End If
'increase the unique index
unique_index = unique_index + 1
'rest the sum of volume so it doesn't carry over for the next ticker
total_volume = 0
'reset the boolean value for figuring the first price
first_bool = True
'USED FOR DEBUGGING ' Cells(1, 11).Value = first_price
'USED FOR DEBUGGING ' Cells(1, 14).Value = last_price
End If
Next j
'USED FOR DEBUGGING 'Cells(1, 15).Value = total_volume
ws.Columns("A:M").AutoFit
Next ws
End Sub
