vba 从每日价格时间序列计算每周回报
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42115152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating Weekly Returns from Daily Time Series of Prices
提问by orkanoid
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
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.
日期是 (dd/mm/yy) 格式，“,”是十进制分隔符。这将通过使用以下公式来完成：（最后一个工作日的价格 - 第一个工作日的价格）/（第一个工作日的价格）。例如，第一周的回报是 (2,7391 - 2,7587)/2,7587 = -0,007，第二周的回报是 (2,7619 - 2,7288)/2,7288 = 0,012。
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:
问题是这个清单持续了一年，有些星期由于假期或其他原因，不到五个工作日。所以我不能简单地复制和粘贴上面的公式。我使用 WEEKNUM 和 WEEKDAY 函数为周数和工作日添加了额外的两列，认为这可能会有所帮助。我想用公式或使用 VBA 自动执行此操作，并希望得到这样的表：
WEEK RETURN
1 -0,007
2 0,012
3 0,047
.
.
.
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)".
正如我所说，由于假期或其他原因，有些周的工作日少于五个，有些周从第 2 天开始或以第 3 天结束等。所以我在想一种方法来告诉excel“找到对应于每周最大和最小工作日的价格并应用公式（上一个工作日的价格 - 第一个工作日的价格）/（第一个工作日的价格）” .
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)
抱歉，帖子太长了，我试图尽可能清楚，我将不胜感激！（我连续几年有 5 个单独的工作表，每个工作表的每日价格为 20 个共同基金）
采纳答案by Scott Craner
To do it in one formula:
用一个公式来做：
=(INDEX(D:D,AGGREGATE(15,6,ROW($D:$D)/(($C:$C=AGGREGATE(14,6,$C:$C/($B:$B=G2),1))*($B:$B=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.
您可能需要根据本地设置将所有更改,为;。
回答by pintxo
I would solve it using some lookup formulas to get the values for each week and then do a simple calculation for each week.
我会使用一些查找公式来解决它以获得每周的值，然后对每周进行简单的计算。
Resulting table:
结果表：
H I J K L M
first last first val last val return
1 02.01.2012 06.01.2012 2,7587 2,7391 -0,007
2 09.01.2012 13.01.2012 2,7288 2,7619 0,012
3 16.01.2012 20.01.2012 2,747 2,876 0,047
Formula in column I:
第 I 列中的公式：
=MINIFS($A:$A;$B:$B;$H2)
Fomula in column J:
J列中的公式：
=MAXIFS($A:$A;$B:$B;$H2)
Formula in column K:
K列中的公式：
=VLOOKUP($I2;$A:$D;4;FALSE)
Formula in column L:
L 列中的公式：
=VLOOKUP($J2;$A:$D;4;FALSE)
Formula in column M:
M列中的公式：
=(L2-K2)/K2
