title: VBA: to calculate Monthly Returns from Daily Values
body: <p>I have two columns, Date and Daily Return. I have ~7000 daily returns, and I want to group them by their compounded return for each month of each year. </p>
  
  <p>I created a VBA function to solve this:</p>
  
  <pre><code>Function MonthlyRet(YearNum, MonthNum, DateData, RetData) As Double
  Dim nValues As Integer
  nValues = DateData.Rows.Count
  
  MonthlyRet = 1
  For i = 2 To nValues
      If (IsNumeric(RetData(i)) And Year(DateData(i)) = YearNum And Month(DateData(i)) = MonthNum) Then
          MonthlyRet = MonthlyRet * RetData(i)
      End If
  Next i
  End Function
  </code></pre>
  
  <p>I can run this on my data using:</p>
  
  <pre><code>=MonthlyRet(2015,1,Data!$A$1:$A$7000,Data!$AR$1:$AR$7000)-1
  </code></pre>
  
  <p>This would return the compounded return for Jan 2015 in my dataset. This seems to work, however, when I copy and paste the equation to all months from 1990 to 2015, around 300 cells, it takes quite a long time to complete the operation on a fast computer. </p>
  
  <p>The algorithm is looping 300 times through 7000 if statements. That does not seem to be that many iterations to me, but my computer will take a minute or two to calculate them all. In java I know the results would be milliseconds, but I am a VBA-novice.</p>
  
  <p>Is there any way to improve the performance of this routine in VBA?</p>