Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
A simple way is to compare the months with a if statement.
If(month(b1)<month(c1),,) assuming the date is in row 1. "Tokyo or bust" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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 -- Regards, Tom Ogilvy "Tokyo or bust" <Tokyo or wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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" "Joel" wrote: A simple way is to compare the months with a if statement. If(month(b1)<month(c1),,) assuming the date is in row 1. "Tokyo or bust" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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 "Tokyo or bust" wrote: 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" "Joel" wrote: A simple way is to compare the months with a if statement. If(month(b1)<month(c1),,) assuming the date is in row 1. "Tokyo or bust" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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 "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "Tokyo or bust" <Tokyo or wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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. "Joel" wrote: 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 "Tokyo or bust" wrote: 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" "Joel" wrote: A simple way is to compare the months with a if statement. If(month(b1)<month(c1),,) assuming the date is in row 1. "Tokyo or bust" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
change the 1st line to
If month(ActiveSheet.Cells(daily, 1)) = month(ActiveSheet.Cells(daily - 1, 1)) Then runningtotal = (1 + (ActiveSheet.Cells(daily, 2))) * "Tokyo or bust" wrote: 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 "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "Tokyo or bust" <Tokyo or wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple question - help please and thank you.
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. "Joel" wrote: change the 1st line to If month(ActiveSheet.Cells(daily, 1)) = month(ActiveSheet.Cells(daily - 1, 1)) Then runningtotal = (1 + (ActiveSheet.Cells(daily, 2))) * "Tokyo or bust" wrote: 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 "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "Tokyo or bust" <Tokyo or wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Simple question | Excel Discussion (Misc queries) | |||
Simple Question | Excel Programming | |||
simple question, hopefully a simple answer! | Excel Programming |