从每日价格系列计算每周回报 [英] Calculating Weekly Returns from Daily Time Series of Prices

问题描述

我想从每日价格的时间序列中计算共同基金的每周收益。我的资料如下所示：

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
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

日期是（dd / mm / yy）格式， ，是小数分隔符。这将通过使用这个公式来完成：（上个工作日的价格 - 第一个工作日的价格）/（第一个工作日的价格）。例如，第一周的回报是（2,7391 - 2,7587）/ 2,7587 = -0,007，第二周的回报是（2,7619 - 2,7288）/ 2,7288 = 0,012。

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.

问题是该列表持续了一年，有些星期由于假期或其他原因而少于五个工作日。所以我不能简单地复制和粘贴上面的公式。我用WEEKNUM和WEEKDAY功能为周数​​和周日添加了两列，以为可能有帮助。我想使用公式自动化或使用VBA，并希望得到如下表格：

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:

WEEK    RETURN
 1     -0,007
 2      0,012
 3      0,047
.       
.       
.       

正如我所说的几个星期有不到五个工作日，有些从平日2开始，或以工作日3结束由于假期或其他原因等。所以我正在考虑一种方法来告诉excel找到与每周最高和最低工作日对应的价格，并应用公式（上周的价格 - 第一个工作日的价格）/（第一个工作日的价格） 。

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)".

对于长期的帖子，我尽量做到尽可能的清楚，我会感谢任何帮助！ （我连续5年有5个独立的工作表，每个日常价格为20个共同基金）

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)

推荐答案

要在一个公式中：

=(INDEX(D:D,AGGREGATE(15,6,ROW($D$2:$D$16)/(($C$2:$C$16=AGGREGATE(14,6,$C$2:$C$16/($B$2:$B$16=G2),1))*($B$2:$B$16=G2)),1))-INDEX(D:D,MATCH(G2,B:B,0)))/INDEX(D:D,MATCH(G2,B:B,0))

您可能需要将所有，更改为 ; 根据您的本地设置。

You may need to change all the , to ; per your local settings.

这篇关于从每日价格系列计算每周回报的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！