Simple question - help please and thank you.

[Guest]

Hey,

I am calculating simple monthly returns on a series of daily asset prices
for a number of years worth of data. The problem is I am not sure how to
indentify the end of a month, and the start of the next in a column of
05/01/2006 format sequential dates. I could just add another column and use
the "month" function in excel (what I have done thus far), but I have to do
the whole operation in VBA. What I have now is:

If ActiveSheet.Cells(dayofmonth, 1) = ActiveSheet.Cells(dayofmonth - 1, 1)
Then day +1 and continue till they are different etc......

The first column is a single number derived from a = month(date column)
formula

This unfortunately will not cut it.

I want to be able to do the same operation, but not have to use the month
function in excel - just reference the date column (04/09/2006 format). I am
pretty sure I need to use a ActiveCell.Formula and a month function, but am
unsure as to how to write the formula - any help would be tremendously
appreciated.

Regardless of how easy it is, I have sweat over this for some time.......

Thanks - cheers.

 

[Guest]

A simple way is to compare the months with a if statement.

If(month(b1)<>month(c1),,) assuming the date is in row 1.

 

[Don Guillett]

If you want to sum a month with a formula
=SUMPRODUCT((MONTH(a2:a22)=1)*b2:b22)

a macro to do the same thing and dollar format
Sub sumproductrange()
x = Application.Dollar(Evaluate _
("=SumProduct((Month(rngA) = 1)* rngB)"))
MsgBox x
End Sub

 

[Tom Ogilvy]

DateSerial(year(dt),Month(dt)+1,0)

gives the last day of the month for Date

demo'd from the immediate windows (date in m/d/yyyy format)

dt = DateSerial(2007,5,1) ' May 1, 2007
? dt
5/1/2007
? dateserial(year(dt),month(dt)+1,0)
5/31/2007
? day(dateserial(year(dt),month(dt)+1,0))
31

 

[Guest]

Hi Joel - thanks for the message -

tried this in VBA:

If Month(bin, 1) = Month(bin - 1, 1) Then


and I got a compile error "Wrong number of arguments or invalid property
assignments"

 

[Guest]

The format for functions on a excel worksheett verses the visual basic
language are different. To reference a cell from VB you would neet to do
something like this


if month(cell("A3")) <> month(cell("A3").Offset(rowOffset:=0,
columnOffset:=-1)) then

I don't know how much experiece you have with the VB language. O

 

[Guest]

Hi Don - thanks for the note - helpful but not exactly what I was looking
for. Here is the code I have thus far - the first line refers to the
numerical month - from a month function in excel - ie.
ActiveSheet.Cells(daily, 1) is equal to 1 for any day in january, 2 for any
day in February etc. I want to do the same operation but reference a
dd/mm/yyyy format, not a number between 1 and 12. Just need help with the
first line........
- hopefuly the below helps to clarify:

If ActiveSheet.Cells(daily, 1) = ActiveSheet.Cells(daily - 1, 1) Then
runningtotal = (1 + (ActiveSheet.Cells(daily, 2))) *
runningtotal
daily = daily + 1

Else

If daily = 10 Then
runningtotal = 1 + ActiveSheet.Cells(daily, 2)
daily = daily + 1
Else
Sheets("MonthlyReturn").Select
ActiveSheet.Cells(Period, 2) = runningtotal - 1
Sheets("data").Select
runningtotal = 1 + ActiveSheet.Cells(daily, 2)
daily = daily + 1
Period = Period + 1

End If
End If

 

[Guest]

Not a whole lot -
I used it a bit 4-5 years ago, but been a while. I know the basics, but not
a whole lot more.

 

[Guest]

change the 1st line to

If month(ActiveSheet.Cells(daily, 1)) = month(ActiveSheet.Cells(daily - 1,
1)) Then
runningtotal = (1 + (ActiveSheet.Cells(daily, 2))) *

 

[Guest]

You da man - so simple I feel like a moron, but hey it is 3am Tokyo time and
I am dam tired.
Thanks for the patience. Cheers - a canuck in Tokyo.