Find Stock Price year opening and Closing with Scripting Dictionary

[James Turner]

I have this code that works beautifully to total up stock volumes for a year. However, I've tried to modify this code to select the first occurence of the stock in a year and the last occurence of the stock in a year. The assignment is to select the year opening and closing price. The code that gets the volumes is:

Sub GetVolumes()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 6))        'creates an array of values from cols A:G
   With CreateObject("Scripting.dictionary")                               'creates a dictionary
      For i = 1 To UBound(Ary)                                             'loops through the array
         If Not .exists(Ary(i, 1)) Then                                    'checks if the value in col A is in the dictionary
            .Add Ary(i, 1), Ary(i, 7)                                      'if it doesn't exist adds col A as the key & col G as the item
         Else
            .Item(Ary(i, 1)) = .Item(Ary(i, 1)) + Ary(i, 7)                'if it does exist adds the value from G to the existing item
         End If
      Next i
      Range("J2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .items))
   End With
End Sub

Modified Code to get year closing price (works except for stocks that don't run a full year to 12/31

Sub GetClosingVolume()
   On Error Resume Next
 
   Dim Ary As Variant
  Dim i As Long
  Dim j As Long
  Dim nullCounter As Integer
   
   Dim strKey(10) As Variant
  
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 6))        '
  
   With CreateObject("Scripting.dictionary")                             
      For i = 1 To UBound(Ary)                                            
         If Not .exists(Ary(i, 1)) And Right(Ary(i, 2), 4) = "1230" Then                                  'checks if ary val date is 1230 then adds
            .Add Ary(i, 1), Ary(i, 6)
                                                                            
          End If                                                           
      Next i                                                                
      
   
    Next j
    
      Range("L2").Resize(.Count, 1).Value = Application.Transpose(Array(.items))
      Range("M2").Resize(.Count, 1).Formula = "=(RC[-1]-RC[-2])/RC[-2]"
      Range("M2").Resize(.Count, 1).NumberFormat = "0.00%"
     
     
      Cells(2, 17) = Application.WorksheetFunction.Max(Range("M2").Resize(.Count, 1))
      Cells(2, 17).NumberFormat = "0.00%"
       Cells(3, 17) = Application.WorksheetFunction.Min(Range("M2").Resize(.Count, 1))
        Cells(3, 17).NumberFormat = "0.00%"
    
    End With
    
Set Ary = Nothing

End Sub

 

[Rijnsent]

Hi James,
in order to get the first and last price of the year, do you need to have that Dictionary? Otherwise you could simply do:

StartPrice = 0
EndPrice = 0
For i = 1 To UBound(Ary) 'loops through the array, assuming opening price in 3 and closing price in 4
    If StartPrice = 0 And Ary(i, 3) > 0 Then StartPrice = Ary(i, 3)
    If Ary(i, 4) > 0 Then EndPrice = Ary(i, 4)
Next i