Construir un backtest sencillo en una hoja de cálculo
Construye en una hoja de cálculo, columna a columna, un backtest completo de una regla de media móvil y revísalo en busca de errores.
Qué aprenderás
- La disposición estándar de columnas de un backtest en hoja de cálculo.
- Las fórmulas de la señal, la posición, la rentabilidad de la estrategia, la curva de capital y el drawdown.
- Cómo desplazar la posición una fila para evitar el sesgo de anticipación.
- Cinco comprobaciones que detectan la mayoría de los errores en una hoja de cálculo.
Explicación
Un backtest en hoja de cálculo es lento para pruebas grandes, pero cada número está a la vista, lo que lo convierte en el mejor lugar para aprender. Vas a probar la regla de media móvil de la lección 2, aquí con una media móvil simple (SMA) de 3 meses para que la tabla sea corta.
La disposición de las columnas
La fila 1 contiene los encabezados; el mes 1 está en la fila 2, así que el mes m está en la fila m+1. La celda K1 contiene el coste por cambio de posición (aquí, 0.2%).
| Columna | Contenido | Fórmula (fila 5 = mes 4) |
|---|---|---|
| A | Mes (o fecha) | dato |
| B | Cierre ajustado | dato |
| C | SMA de 3 meses | =AVERAGE(B3:B5) |
| D | Señal al cierre (1 = invertido, 0 = liquidez; un empate mantiene la señal anterior) | =IF(B5>C5,1,IF(B5<C5,0,D4)) |
| E | Posición mantenida durante el mes | =D4 |
| F | Rentabilidad del activo | =B5/B4-1 |
| G | Rentabilidad de la estrategia después de costes | =E5*F5-IF(E5<>E4,$K$1,0) |
| H | Capital (inicio 100) | =H4*(1+G5) |
| I | Máximo acumulado | =MAX($H$4:H5) |
| J | Drawdown | =H5/I5-1 |
En las versiones de hojas de cálculo en alemán, los nombres de las funciones y los separadores son distintos, por ejemplo MITTELWERT en lugar de AVERAGE, WENN en lugar de IF y punto y coma en lugar de comas.
La idea clave: el desplazamiento de una fila
La columna E copia la señal de la fila anterior. La señal solo se conoce al cierre del mes m, así que, como pronto, puede obtener la rentabilidad del mes m+1. Si multiplicas la señal de una fila por la rentabilidad de esa misma fila, la prueba «conoce» el resultado del mes antes de decidir. Este único error hace que muchos backtests en hoja de cálculo parezcan demasiado buenos.
Capital y drawdown
La curva de capital es el valor de la cuenta a lo largo del tiempo, aquí como un índice que empieza en 100. Las rentabilidades se capitalizan: cada mes multiplica el valor anterior. El drawdown es la caída porcentual desde el capital más alto alcanzado hasta el momento (el máximo acumulado); las matemáticas detalladas del drawdown y de la recuperación se trataron en el curso «Gestión del riesgo y de las operaciones».
Ejemplo resuelto
Ejemplo hipotético: cierres mensuales €100, €104, €108, €106, €101, €97, €99, €104, €109, €112. Coste: 0.2% por cambio de posición (una aproximación sencilla del spread y las comisiones, lección 8). Se excluyen los impuestos y los intereses de la liquidez. El capital empieza en 100 al cierre del mes 3, el primer mes con SMA; la posición anterior es 0.
| Mes | Cierre | SMA | Señal | Posición | Rentabilidad del activo | Rentabilidad de la estrategia | Capital | Drawdown |
|---|---|---|---|---|---|---|---|---|
| 3 | 108 | 104.00 | 1 | 0 | – | – | 100.00 | 0.00% |
| 4 | 106 | 106.00 | 1 (empate) | 1 | −1.85% | −2.05% | 97.95 | −2.05% |
| 5 | 101 | 105.00 | 0 | 1 | −4.72% | −4.72% | 93.33 | −6.67% |
| 6 | 97 | 101.33 | 0 | 0 | −3.96% | −0.20% | 93.14 | −6.86% |
| 7 | 99 | 99.00 | 0 (empate) | 0 | +2.06% | 0.00% | 93.14 | −6.86% |
| 8 | 104 | 100.00 | 1 | 0 | +5.05% | 0.00% | 93.14 | −6.86% |
| 9 | 109 | 104.00 | 1 | 1 | +4.81% | +4.61% | 97.43 | −2.57% |
| 10 | 112 | 108.33 | 1 | 1 | +2.75% | +2.75% | 100.11 | 0.00% |
Comprobaciones de tres filas:
- Mes 4: posición 1 (del mes 3), se aplica el coste de entrada: −1.85% − 0.20% = −2.05%. Capital: 100 × (1 − 0.0205) = 97.95.
- Mes 6: la posición cambia a 0, solo coste de salida: −0.20%. Capital: 93.33 × 0.998 ≈ 93.14. Drawdown: 93.14 ÷ 100 − 1 = −6.86%.
- Mes 9: nueva entrada: +4.81% − 0.20% = +4.61%. Capital: 93.14 × 1.0461 ≈ 97.43.
Resultado: capital de 100.11 (+0.11%) con 3 cambios de posición y un drawdown máximo de −6.86%. Comprar y mantener del mes 3 al 10: €112 ÷ €108 − 1 = +3.70%. Con la fórmula más sencilla =IF(B5>C5,1,0), el empate del mes 4 habría supuesto salir un mes antes, y el resultado habría sido de aproximadamente +5.07%. Un solo caso límite cambió el resultado en unos 5 puntos porcentuales. Ocho meses no demuestran nada sobre la regla; aquí el objetivo es la mecánica.
Error frecuente
=D5*F5 en lugar de =E5*F5 permite que la estrategia obtenga la rentabilidad del mes en el que se formó la señal. Pregúntate siempre: «¿Se conocía esta celda antes de la rentabilidad por la que se multiplica?».Actividad práctica
Cinco comprobaciones para cualquier backtest en hoja de cálculo. Aplícalas en papel a la tabla anterior y anota lo que verías si la prueba fuera correcta.
- Cuenta los cambios de posición y compáralos con los costes cargados.
- Recalcula a mano una fila al azar.
- Pon el coste a 0 y la posición a 1 en todas las filas: el resultado debe ser igual al de comprar y mantener.
- Comprueba que la primera fila de posición usa una señal de una fila anterior.
- Comprueba que el drawdown nunca es positivo.
Ver solución
1. Cambios en los meses 4, 6 y 9 = 3; costes cargados 3 × 0.20% = 0.60% en total. Coincide.
2. Ejemplo del mes 5: posición 1 × (€101 ÷ €106 − 1) = −4.72%, sin cambio, así que sin coste; 97.95 × 0.9528 ≈ 93.33. Coincide.
3. Con posición 1 y sin coste, el capital pasa a ser 100 × 112 ÷ 108 ≈ 103.70, el resultado de comprar y mantener del +3.70%.
4. La posición del mes 4 (1) procede de la señal del mes 3. Correcto.
5. Todos los drawdowns son 0.00% o negativos. Correcto.
Limitaciones: la tabla supone que se opera al cierre de fin de mes con un coste fijo; las ejecuciones reales se producen en la siguiente apertura con spreads y slippage variables (lección 8).
Ideas clave
- Usa columnas separadas para la señal y la posición, con la posición desplazada una fila hacia abajo.
- Capitaliza las rentabilidades en una curva de capital y calcula el drawdown desde el máximo acumulado.
- Carga los costes cada vez que cambia la posición, no todos los meses.
- Haz las cinco comprobaciones antes de fiarte de cualquier resultado de una hoja de cálculo.
Siguiente paso
En la próxima lección convertirás una curva de capital como esta en métricas de rendimiento estándar: CAGR, drawdown, ratio de Sharpe y factor de beneficio.
Autoevaluación
Responde a las preguntas y luego comprueba tus respuestas. Puedes volver a intentarlo tantas veces como quieras.
Autoevaluación automática para tu propio repaso. Tus respuestas se comprueban en tu navegador y no las vemos ni evaluamos. No es un examen ni una titulación.
Tu progreso
«Completada» solo significa que marcaste la lección como terminada. No es una prueba de tu comprensión. El progreso y las notas se guardan solo en este navegador y en este dispositivo, no en una cuenta.