La evolución en la tecnología ha puesto a nuestra disposición una amplia gama de herramientas para el desarrollo, prueba y optimización de sistemas de trading. Sin embargo, una manera simple de validad un sistema de trading es utilizar una hoja Excel.

 

En este artículo vamos a ver cómo podemos hacer backtesting de un sistema de rotación mensual de 5 ETFs utilizando una Excel.

 

En el siguiente fichero se comprueba que para el periodo de 9 años el sistema de rotación ETF genera un rendimiento anual del 11,88 % frente a 7,75 % para SPY.

 

La elección de los mercados es una decisión crítica a la hora de confeccionar el sistema. Para esta simulación vamos a elegir 5 ETFs que nos permitan diversificar tanto en productos como en mercados:

• SPY - SP 500
• GLD - Oro
• IEF - Tesoro de 10 años
• IYR - US Real Estate.
• EEM - Mercados Emergentes

 

Una vez elegidos los productos a seguir hay que buscar una fuente de datos históricos. Para nuestro estudio  vamos a usar los datos gratuitos de Yahoo Finance. Cuando entres en Yahoo Finance introduce el símbolo del índice que quieres recopilar los datos. En la siguiente página, haz clic en el enlace a la izquierda de " Cotizaciones históricas", seleccione la opción de datos mensuales “Mensual” y a continuación “Consulta la cotización”. Si bajamos a la parte inferior de la tabla, encontraremos un enlace para descargar los datos a una hoja de cálculo “Descargar a la hoja de cálculo”.

 

Deberemos repetir esta operación para cada uno de los productos elegidos. Una vez  extraídos los datos deberemos insertarlos en una única hoja Excel, teniendo una pestaña individual para cada uno de los ETFs y otra pestaña global donde se recopilen los datos brutos de la cotización mensual de los 5 ETFs.

 

A partir de los datos en bruto haremos los distintos cálculos. El primero será calcular cuánto ha variado el ETF cada mes, para ello utilizaremos la siguiente formula:

 

Variación mensual = (precio mes anterior – precio mes  actual) / precio mes anterior

 

En Excel la formula tendrá el siguiente formato (A1-A2)/A2 siendo A2 el mes anterior y A1 el mes en curso. Habrá que hacer este cálculo para todos los meses y para todos los ETFs.

 

A la hora de realizar el Ranking de ETFs hemos decidido clasificarlos de acuerdo a su rentabilidad en los últimos 6 meses, para ello calcularemos cuánto ha variado el ETF en ese periodo.

La fórmula utilizada será del tipo (A1-A6)/A6, siendo A1 el mes actual y A5 hace 6 meses.

 

Tras haber calculado la rentabilidad semestral de cada ETF debemos comparar los ETF de mayor a menor rentabilidad y asignarles una puntuación de 1 (mayor rentabilidad) a 5 (menor rentabilidad), es decir, si la columna de la SPY mostrará un 1 si indica que el SPY será el  ETF con mejor rentabilidad de los 5.

 

La fórmula de Excel que necesitamos para clasificar los datos es JERARQUIA

 

Una vez que hemos clasificado como el ETF de acuerdo a su rentabilidad mensual hay que extraer el valor de variación mensual para ese ETF durante el siguiente periodo de nuestro histórico de datos. Esto lo haremos a través de una formula condicional utilizando el comando IF el cual nos dará la rentabilidad esperada para el ETF situado en la posición TOP 1.

 

Por ultimo debemos simular como evolucionaria nuestra cuenta.  Para este cálculo hemos tomado 10.000€ como cuenta de partida y hemos comenzado a iterar, multiplicando la cantidad que teóricamente tendrá nuestra cuenta en el mes anterior por la rentabilidad del mes actual.

 

Si ha llegado hasta aquí, en estos momentos tendrá una hoja de cálculo con una gran cantidad de datos, ahora toca analizarlos y la forma más sencilla para hacerlo es graficándolos.