excel - Cálculo de devoluciones semanales a partir del horario diario Serie de precios
vba excel-vba (2)
Quiero calcular los rendimientos semanales de un fondo mutuo a partir de una serie temporal de precios diarios. Mi información se ve así:
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
La fecha es el formato (dd / mm / aa) y "," es el separador decimal. Esto se haría usando esta fórmula: (Precio del último día de la semana - Precio del primer día de la semana) / (Precio del primer día de la semana). Por ejemplo, el rendimiento de la primera semana es (2,7391 - 2,7587) / 2,7587 = -0,007 y para el segundo es (2,7619 - 2,7288) / 2,7288 = 0,012.
El problema es que la lista continúa durante un año, y algunas semanas tienen menos de cinco días hábiles debido a vacaciones u otras razones. Así que no puedo simplemente copiar y pegar la fórmula anterior. Agregué las dos columnas adicionales para el número de semana y el día de la semana con las funciones WEEKNUM y WEEKDAY, y pensé que podrían ser útiles. Quiero automatizar esto con una fórmula o usando VBA y esperando obtener una tabla como esta:
WEEK RETURN
1 -0,007
2 0,012
3 0,047
.
.
.
Como he dicho, algunas semanas tienen menos de cinco días de la semana, algunas comienzan el día de la semana 2 o terminan con el día de la semana 3, etc. debido a las vacaciones u otras razones. Así que estoy pensando en una forma de decirle a Excel que "encuentre los precios que corresponden al día de semana máximo y mínimo de cada semana y aplique la fórmula (Precio del último día de la semana - Precio del primer día de la semana) / (Precio del primer día de la semana)" .
Perdón por la publicación larga, traté de ser lo más clara posible, ¡apreciaría cualquier ayuda! (Tengo 5 hojas de trabajo separadas por años consecutivos, cada una con precios diarios de 20 fondos mutuos)
Lo resolvería usando algunas fórmulas de búsqueda para obtener los valores de cada semana y luego hacer un cálculo simple para cada semana.
Tabla resultante:
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
Fórmula en la columna I:
=MINIFS($A:$A;$B:$B;$H2)
Fomula en la columna J:
=MAXIFS($A:$A;$B:$B;$H2)
Fórmula en la columna K:
=VLOOKUP($I2;$A:$D;4;FALSE)
Fórmula en la columna L:
=VLOOKUP($J2;$A:$D;4;FALSE)
Fórmula en la columna M:
=(L2-K2)/K2
