View Full Version : calculating monthly returns in table from historical data

10-15-2009, 11:14 PM
Hi guys,

Long time no see haha, need your help, this is probably quite easy but im stuck

There are 2 sheets - 1 and 2
In Sheet 2 i will have unfiltered data from which i need to transfer 2 cells onto Sheet 1. I need the Date and 'Total Equity' (which will be called Price in Sheet 1).....the code should be something like -' If Column A has accoun# 323 and column G = USD, then copy the corresponding cells in column C(date) and column E (total eq.)

Then this has to be pasted in Sheet 1. The date is pasted in A2, and the tot.eq. in B2, the historical data should just move down the column(it shouldnt be deleted)...formula is in place and it will calculate the new 'Daily return' and 'volatility'

So that is one part of it.....then i need to populate a table in sheet 1 - with monthly returns...i need the macro to go through column A and detect dates so it can calculate monthly gains.

Thanks in advance, hope i was clear

10-17-2009, 07:05 AM
any ideas people?
If someone can help me with the 2nd bit (calculating monthly returns in the table), that would be great.


10-17-2009, 02:03 PM
Part 1
How are you enering the citeria?

Part 2
where Dates is =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
and Prices is =OFFSET(Dates,0,1)

10-18-2009, 03:41 PM
Hey thanks mdmac,
I tried this macro for the first one but it didnt work, not sure where the problem is - this is slightly diffrnt from the 1st file in that, it send one acct# to Sheet A, and the other to Sheet B. And tries to make sure that all formulas are being calculated on the new data combined with historical data

Public Sub Example()
Dim wsSource As Worksheet, wsA As Worksheet, wsB As Worksheet, wsOutput As Worksheet
Dim rngCell As Range, rngData As Range
On Error GoTo ExitPoint
Set wsSource = Sheets("Raw")
Set wsA = Sheets("A")
Set wsB = Sheets("B")
With wsSource
Set rngData = .Range(.Cells(2, "C"), .Cells(.Rows.Count, "C").End(xlUp))
End With
For Each rngCell In rngData.SpecialCells(xlCellTypeConstants, xlNumbers)
If UCase(rngCell.Offset(, 4)) = "USD" Then
Select Case rngCell.Offset(, -2)
Case 323
Set wsOutput = wsA
Case 540
Set wsOutput = wsB
End Select
If Not wsOutput Is Nothing Then
With wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Offset(1)
.Value = rngCell.Value
.Offset(, 1).Value = rngCell.Offset(, 2).Value
.Offset(-2, 2).Resize(2, 4).Formula = .Offset(-3, 2).Resize(2, 4).Formula
End With
End If
End If
Set wsOutput = Nothing
Next rngCell
Set wsA = Nothing
Set wsB = Nothing
Set wsSource = Nothing
End Sub

i'll post the new file

thanks a lot btw

10-18-2009, 03:42 PM
here's the new file

10-19-2009, 01:15 AM
hey mdmac,

I wasnt able to use your formula correctly, not sure what im doing wrong here,
this is what i used


ACT! is in place of 'Sheet1'

Do I replace 'YEAR' with a cell number or the actual year e.g '2001'?


10-19-2009, 07:10 AM
hey mdmac,
your formula did work, but it didnt do what i need, i think you may have misunderstoof what i was trying to do --

i'm trying to get monthly returns, so for eg Mar 2009 would be 31st Mar price minus 1st March price divided by 1st march price which should be about -2% but the formula here gives me 20436.

any ideas on how i should adjust that?

thanks again for spending time on this