title: Calculating Weekly Returns from Daily Time Series of Prices
body: <p>I want to calculate weekly returns of a mutual fund from a time series of daily prices. My data looks like this:</p>
  
  <pre><code>A        B      C        D        E
  DATE     WEEK   W.DAY    MF.PRICE WEEKLY RETURN
  02/01/12    1    1       2,7587
  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    -0,007
  09/01/12    2    1       2,7288
  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    0,012
  16/01/12    3    1       2,7470
  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    0,047
  </code></pre>
  
  <p>The date is (dd/mm/yy) format and "," is decimal separator. This would be done by using this formula: (Price for last weekday - Price for first weekday)/(Price for first weekday). For example the return for the first week is (2,7391 - 2,7587)/2,7587 = -0,007 and for the second is (2,7619 - 2,7288)/2,7288 = 0,012. </p>
  
  <p>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. 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 or using VBA and hoping to get a table like this:</p>
  
  <pre><code>WEEK    RETURN
   1     -0,007
   2      0,012
   3      0,047
  .       
  .       
  .       
  </code></pre>
  
  <p>As I said some weeks have less than five weekdays, some start with weekday 2 or end with weekday 3 etc. due to holidays or other reasons. So I'm thinking of a way to tell excel to "find the prices that correspond to the max and min weekday of each week and apply the formula (Price for last weekday - Price for first weekday)/(Price for first weekday)".</p>
  
  <p>Sorry for the long post, I tried to be be as clear as possible, I would appreciate any help! (I have 5 separate worksheets for consecutive years, each with daily prices of 20 mutual funds)</p>