Solved: Looking up date and calculating monthly returns

[Anomandaris]

Hi, I've been stuck on this a long time and hopefully someone can figure this out. I've tried pivot tables but its not visually acceptable here unfortunately. So I hope someone can write a macro to solve it.

Column A has dates which i need the macro to detect and calculate monthly returns from Column B. The figures should go into a table as shown

eg. for March 2009----detect that month started at Row 2 and ended Row 23.......so calculate (B23-B2)/B2....which is about -1%.


thanks
i will be extremely grateful if this is somehow solved, although i have low expectations of that

[JKwan]

see if this will do


Public Function FindLastRow(ColumnLetter As String)
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = Range(ColumnLetter & "65536").End(xlUp).Row
End Function
Sub test()
Dim VisibleRange As Range
Dim sDates As Variant
Dim InitialPrice As Double
Dim FinalPrice As Double
Dim ROI As Double

LastRow = FindLastRow("A")
Set VisibleRange = Range("$A$" & 2 & ":$A$" & LastRow). _
SpecialCells(xlCellTypeVisible)

sDates = Split(VisibleRange.Address(False, False), ":")
InitialPrice = Range(sDates(0)).Offset(0, 1)
FinalPrice = Range(sDates(1)).Offset(0, 1)
ROI = ((FinalPrice - InitialPrice) / InitialPrice) * 100
MsgBox "Rate of return " & vbCrLf & vbTab & Format(ROI, "##.####") & "%"

End Sub

[Anomandaris]

hey Kwan, thanx but this just gives me the overall return. I need it for every month separately

[JKwan]

That's what the code will give you. Paste the code into your SS then do a filter and run the code. IE, filter to Mar09 run code, filter Apr09 run code and see if it is what you wanted.

[JKwan]

After rethinking (I saw the autofilter).... Maybe this is what you want.
When you run this code, it will detect a change in your date and then calc the ROI, put the value next to it.
Just updated the output, so it will put into your table on the right


Sub test2()
Dim LastRow As Long
Dim Index As Long
Dim InitialDate As String
Dim InitialPrice As Double
Dim FinalPrice As Double
Dim ROI As Double
Dim TableRow As Long

LastRow = FindLastRow("A")
InitialDate = Cells(2, "A").Text
InitialPrice = Cells(2, "A").Offset(0, 1)
TableRow = 3
For Index = 3 To LastRow
If InitialDate <> Cells(Index, "A").Text Then
FinalPrice = Cells(Index, "A").Offset(-1, 1)
ROI = ((FinalPrice - InitialPrice) / InitialPrice) * 100
Cells(TableRow, "M") = ROI & "%"
InitialDate = Cells(Index, "A").Text
InitialPrice = Cells(Index, "A").Offset(0, 1)
TableRow = TableRow + 1
End If
Next Index
FinalPrice = Cells(Index, "A").Offset(-1, 1)
ROI = ((FinalPrice - InitialPrice) / InitialPrice) * 100
Cells(TableRow, "M") = ROI & "%"
End Sub

[Anomandaris]

Thanks a lot Kwan, that does Work!!! So once the year ends in December, does it start a new column for 2010?

[JKwan]

Simply put, NO.

[Anomandaris]

Thats fine, thanks again JKwan.
I'll try to fiddle around with the code, see if I can make some adjustments to recognize when the year ends, then start a new column.

[JKwan]

Try this:
Comes with warnings, I am EXPECTING that all 12 months are there! NO REALLY on error checking


Sub test3()
Dim LastRow As Long
Dim Index As Long
Dim InitialDate As String
Dim InitialPrice As Double
Dim FinalPrice As Double
Dim ROI As Double
Dim TableRow As Long
Dim lColumn As Long

LastRow = FindLastRow("A")
InitialDate = Cells(2, "A").Text
InitialPrice = Cells(2, "A").Offset(0, 1)
TableRow = 2
lColumn = 13
For Index = 3 To LastRow
If InitialDate <> Cells(Index, "A").Text Then
FinalPrice = Cells(Index, "A").Offset(-1, 1)
ROI = ((FinalPrice - InitialPrice) / InitialPrice) * 100
Cells(TableRow, lColumn) = ROI & "%"
InitialDate = Cells(Index, "A").Text
InitialPrice = Cells(Index, "A").Offset(0, 1)
TableRow = TableRow + 1
If TableRow > 13 Then
TableRow = 2
lColumn = lColumn + 1
End If
End If
Next Index
FinalPrice = Cells(Index, "A").Offset(-1, 1)
ROI = ((FinalPrice - InitialPrice) / InitialPrice) * 100
Cells(TableRow, lColumn) = ROI & "%"
End Sub

[arkusM]

For a non VBA solution...
It requires the use of Helper columns, not sure if that can be accommadated. They could always be hidden.

See attached

Cheers,

[Anomandaris]

Excellent Excellent JKwan! you're the man, it works (even if all 12 months in a given year are not there)

Say we only have Nov, Dec of 2009------- just set TableRow = 12 before you begin the for/next statement

I've been looking for a code like this on and off for weeks, and no one has been able to come up with an efficient solution until now

Thanks!!

[Anomandaris]

hey Arkus, thanks for your solution too, that works as well!
btw JKwan, how did you insert a filter in Column A without inserting filters for the whole row? (not a big issue, just curious)

Everytime i try its either all or nothing
Thanks guys