(Solved) – How to find the first and last values in a conditional loop in VBA

  • by

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...

      ' Add to the stock total
      volume = volume   Cells(i, 7).Value

    End If
Next i

Leave a Reply

Your email address will not be published. Required fields are marked *