I’m creating a report on a stock index where I am supposed to get the annual open to close net change in stock price for each stock in the market. I have panel data with an observation for each stock every day of the year. I’m currently using a conditional for loop to create annual total volume as well. Is there any way for me to select the first and last values in a conditional to select the annual open and close of a stock? Column 1 is the name of the stock so I am using that as the conditional for when the stock changes. Here is what I have thus far. Column 4 is daily open and column 5 is daily close for the observation.
'Establish variables Dim ticker As String Dim change_price As Double Dim change_percent As Double Dim volume As Double Dim annual_open As Double Dim annual_close As Double ' Keep track of each stock the summary table Dim Summary_Table_Row As Integer Summary_Table_Row = 2 For i = 2 To 800000 If Cells(i 1, 1).Value <> Cells(i, 1).Value Then 'create a list of stocks ticker = ticker Cells(i, 1).Value 'total volume for each stock volume = volume Cells(i, 7).Value 'annual_open = ? 'annual_close = ? ' Print the stock in the Summary Table Range("H" & Summary_Table_Row).Value = ticker ' Print the stock volume to the Summary Table Range("I" & Summary_Table_Row).Value = volume ' Add one to the summary table row Summary_Table_Row = Summary_Table_Row 1 'reset the stock name ticker = "" ' Reset the Brand Total volume = 0 ' If the cell immediately following a row is the same stock... Else ' Add to the stock total volume = volume Cells(i, 7).Value End If Next i