Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Holding Period Returns Between Two Customizable Dates
I have time series data of mutual fund prices from Yahoo Finance that I
update frequently. Column A has the dates (Daily), Column B has the Adjusted Close Value, and in Column C I have formulas to calculate the daily returns (B8/B7-1). VTSMX Adj. Close 1/3/2005 26.25 1/4/2005 25.91 -1.30% 1/5/2005 25.76 -0.58% 1/6/2005 25.86 0.39% 1/7/2005 25.8 -0.23%..... to present. I have a Start Date in cell A1 and End Date in cell A2, and I have used dynamic ranges to create a price chart that expands automatically as more data is added to the time series, and it is flexible to show different periods by adjustng the two dates. However, instead of charting the data, I would just like to show the heriod return in another cell (like cell A5) between the two dates I select, for example between 10/21/2008 and 7/31/2009. I can do this manually by using the following formula between the selected dates: {=PRODUCT(C135:C197+1)-1} Is there a way to calculate different time periods, simply by changing the Start Date and End Dates? Thank you, Josh |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Holding Period Returns Between Two Customizable Dates
You can do it with a UDF
call with =productDates(B13:B19,A13:C19,A1,B1) Function ProductDates(DailyReturn As Range, DailyDates As Range, _ StartDate As Date, EndDate As Date) ProductDates = 1 For Count = 1 To DailyReturn.Count MyDate = DailyDates.Cells(Count, 1) Amount = DailyReturn.Cells(Count, 1) If MyDate = StartDate And MyDate <= EndDate Then ProductDates = ProductDates * Amount End If Next Count End Function "Josh M" wrote: I have time series data of mutual fund prices from Yahoo Finance that I update frequently. Column A has the dates (Daily), Column B has the Adjusted Close Value, and in Column C I have formulas to calculate the daily returns (B8/B7-1). VTSMX Adj. Close 1/3/2005 26.25 1/4/2005 25.91 -1.30% 1/5/2005 25.76 -0.58% 1/6/2005 25.86 0.39% 1/7/2005 25.8 -0.23%..... to present. I have a Start Date in cell A1 and End Date in cell A2, and I have used dynamic ranges to create a price chart that expands automatically as more data is added to the time series, and it is flexible to show different periods by adjustng the two dates. However, instead of charting the data, I would just like to show the heriod return in another cell (like cell A5) between the two dates I select, for example between 10/21/2008 and 7/31/2009. I can do this manually by using the following formula between the selected dates: {=PRODUCT(C135:C197+1)-1} Is there a way to calculate different time periods, simply by changing the Start Date and End Dates? Thank you, Josh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Payback Period | Excel Worksheet Functions | |||
Calculate Average for same period last year | Excel Discussion (Misc queries) | |||
Formula that returns late, early or Ok accordingly to the period | Excel Discussion (Misc queries) | |||
Calculate percentage of time period | Excel Worksheet Functions | |||
Count returns for a period - year | Excel Worksheet Functions |