Search code examples
vbaexcelexcel-formulafinance

Calculating Weekly Returns from Daily Time Series of Prices v2

This is a slightly adjusted version of the question here: Calculating Weekly Returns from Daily Time Series of Prices which was answered by @Scott Craner:

I want to calculate weekly returns of a mutual fund from a time series of daily prices. My data looks like this:

A        B      C        D        E
DATE     WEEK   W.DAY    MF.PRICE WEEKLY RETURN
02/01/12    1    1       2,7587   -0,0108
03/01/12    1    2       2,7667
04/01/12    1    3       2,7892
05/01/12    1    4       2,7666
06/01/12    1    5       2,7391    
09/01/12    2    1       2,7288    0,0067
10/01/12    2    2       2,6707
11/01/12    2    3       2,7044
12/01/12    2    4       2,7183
13/01/12    2    5       2,7619    
16/01/12    3    1       2,7470    0,0511
17/01/12    3    2       2,7878
18/01/12    3    3       2,8156
19/01/12    3    4       2,8310
20/01/12    3    5       2,8760 
23/01/12    4    1       2,8875

The date is (dd/mm/yy) format and "," is decimal separator. This would be done by using this formula: (Price for first weekday of next week - Price for first weekday of current week)/(Price for first weekday of current week). For example the return for the first week is (2,7288 - 2,7587)/2,7587 = -0,0108 and for the second is (2,7470 - 2,7288)/2,7288 = 0,0067.

The problem is that the list goes on for a year, and some weeks have less than five working days due to holidays or other reasons. Some weeks start with weekday 2, some end with weekday 3. So I can't simply copy and paste the formula above. I added the extra two columns for week number and week day using WEEKNUM and WEEKDAY functions, thought it might help. I want to automate this with a formula and hope to get a table like this:

WEEK    RETURN
 1      -0,0108
 2       0,0067
 3       0,0511
.       
.       
.

I'm looking for a way to tell excel to "find the prices that correspond to the min weekdays of two consecutive weeks and apply the formula "(Price for first weekday of next week - Price for first weekday of current week)/(Price for first weekday of current week)".

I would appreciate any help! (I have 5 separate worksheets for consecutive years, each with daily prices of 20 mutual funds)

Solution

  • It seems to me that you can generate your column E with this formula in E2 :

    =IF(B2=B1, "", (VLOOKUP(1+B2, B3:D9, 3, FALSE) - D2)/D2)

    It's a VLookup limited on the next 7 rows from each row that declares a new week.

    Copying into all cells will give the result indicated in your first tableau. To transform this result into to the list (Week, Return) is a matter of a filter that hides blanks from E.

    Notice that a problem could occur if the WeekNum restarts from one when a new year is reached, but since you say that each of your sheets is for one (calendar) year, it shouldn't happen.