Quaterly Returns from Monthly Prices

[SS1312]

Hey,
Hope everyone is doing okay and keeping safe

I have been tasked to make a Fund Performance dashboard using VBA I am able to get stock data from yahoo. Also managed to calcuated monthly returns from daily NAVs. However I am struggling to calculate Quarterly returns.

Here is the code. Though this works in calculating quarterly returns the issue is it repeats. Here is a picture (practice file cant mention real returns)

It calculates the monthly returns, but for quarterly retuns I only want for actual quarter ends in a financial year in a year. However here is it calculating for every month

If anyone can guide me it can be of great help. Thanks

Sub Button2_Click()

Worksheets("Prac").Activate

           
  Dim old_ As Double
  Dim new_ As Double
  Dim start_row As Integer
  start_row = 4

  ' Track the number of rows
   Dim num_row As Integer
   num_row = 0
   While (Not (IsEmpty(Cells(start_row + num_row, "G"))) And Not (IsEmpty(Cells(start_row +
   num_row, "G"))))
   num_row = num_row + 3
 Wend

 Dim end_row As Integer
 end_row = num_row + start_row - 1
' Calculate Rate of Return
Dim m_r_j As Double
Dim m_r_m As Double

For r = start_row To end_row - 3 Step 1
 old_ = Cells(r, "G")
 new_ = Cells(r + 3, "G")

 m_r_j = (new_ - old_) / old_
 Cells(r + 3, "M").Value = m_r_j

 Next
 End Sub

 

[shknbk2]

Welcome to the forum.

Change "Step 1" to "Step 3".

Also, your While conditions check for column G twice. Should the second one be H?

Book1
	F	G	H	I	J	K	L	M	N
3							Monthly	Quarterly	Periodically
4	21-Jul	1009.563	15763.05
5	21-Aug	1053.5	17132.2				4.35%
6	21-Sep	1091.11	17618.15				3.57%
7	21-Oct	1132.008	18114.9				3.75%	12.13%
8	21-Nov	1066.371	16983.2				-5.80%
9	21-Dec	1086.332	17354.05				1.87%
10	22-Jan	1108.526	17339.85				2.04%	-2.07%
11	22-Feb	1072.691	16793.9				-3.23%
12	22-Mar	1121.072	17464.75				4.51%
Prac

 

[SS1312]

Thanks,

However I want 3.20% and -0.44% value to be shown. Since those are the quaterly returns.

 

[shknbk2]

Sub Button2_Click()

Worksheets("Prac").Activate

           
  Dim old_ As Double
  Dim new_ As Double
  Dim start_row As Integer
  start_row = 4

  ' Track the number of rows
   Dim num_row As Integer
   num_row = 0
   While (Not (IsEmpty(Cells(start_row + num_row, "G"))) And Not (IsEmpty(Cells(start_row + num_row, "G"))))
   num_row = num_row + 3
 Wend

 Dim end_row As Integer
 end_row = num_row + start_row - 1
' Calculate Rate of Return
Dim m_r_j As Double
Dim m_r_m As Double

For r = start_row To end_row - 3 Step 1
If Month(Cells(r, "F")) Mod 3 = 0 Then

 old_ = Cells(r, "G")
 new_ = Cells(r + 3, "G")

 m_r_j = (new_ - old_) / old_
 Cells(r + 3, "M").Value = m_r_j
End If
 Next
 End Sub

 

[SS1312]

Okay this does work. However if i add months as in April, May - it doesnt work

 

[shknbk2]

But it does work if you also add June, right? April and May aren't ends of quarters. I'm not really sure what more you want.