vba - 根据每日价格时间序列计算每周 yield

Question

我想根据每日价格的时间序列计算共同基金的每周返回。我的数据如下所示:

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。

问题是这个 list 持续了一年，而且有些周由于假期或其他原因少于五个工作日。所以我不能简单地复制并粘贴上面的公式。我使用 WEEKNUM 和 WEEKDAY 函数添加了额外的两列，分别表示周数和周日，认为这可能会有所帮助。我想使用公式或使用 VBA 自动执行此操作，并希望得到这样的表格:

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

正如我所说，由于假期或其他原因，有些周的工作日少于五个，有些周从第 2 个工作日开始或从第 3 个工作日结束等。所以我正在考虑一种方法告诉Excel“找到与每周最大和最小工作日相对应的价格并应用公式(上一个工作日的价格 - 第一个工作日的价格)/(第一个工作日的价格)” .

很抱歉这篇文章很长，我尽量说得清楚，如果有任何帮助，我将不胜感激! (我连续几年有 5 个单独的工作表，每个工作表都有 20 个共同基金的每日价格)

Answer 1

用一个公式来完成:

=(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))

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