Backtesting en Excel

Introducción

El título de este artículo puede llevar a confusión, pues realmente el mismo trata de explicar cómo complementar y obtener valiosos datos de un Backtest ya realizado, más que del proceso de realizar un Backtest completo en Excel.

La idea me surge ante el problema al que me enfrentaba al querer realizar un Backtest de la estrategia presentada hace unas semanas en esta web “Trading Reversals”.

El problema no era otro que el hecho de que quería realizar distintas mediciones de una misma operación, ya que el autor plantea diferentes objetivos o métodos de salida, por lo que al realizar el Backtest, me encontraba con que para un mismo setup de entrada, tendría que abrir 3 operaciones con objetivos diferentes y llevar estadísticas separadas.

Debido a la complejidad de realizar esta tarea, lo cual implicaba mucho más tiempo en la realización de dicho Backtest, me propuse encontrar la forma de obtener los mismos resultados de una manera mucho más sencilla, programando una fórmula en Excel que me pueda proporcionar esa información.

Función BuscarFX en Excel

Mi idea no fue otra que crear una Función en Excel que me proporcionase la información necesaria para completar un Backtest, de forma que a partir de una fecha y precio de entrada, pudiese conocer el resultado de una operación en base a un objetivo de beneficios y un nivel de Stop Loss.

Es decir, a partir de un Backtest ya realizado en cualquier programa para tal fin, el cual nos dará información del momento en que hemos entrado a mercado, utilizaremos posteriormente Excel para conocer el momento de la salida en base a diferentes escenarios, pudiendo de esta forma evaluar de una manera más cómoda el resultado de una estrategia con diferentes ratios de riesgo / beneficio.

Esto es algo que obviamente la gente que realiza Backtest de estrategias en automático hace de manera directa, pudiendo optimizar el SL o TP a su conveniencia, pero cuando se realiza un Backtest manual, digamos que el proceso es mucho más laborioso y lleva de mucho tiempo.

Resumiendo, la fórmula programada nos permitirá conocer, dada una fecha de entrada, si se alcanza antes el SL o el TP y el momento en que esto se produce, pudiendo sacar otra información que podría ser interesante conocer.

Lo primero, para ir leyendo el artículo con el propio Excel abierto, puedes descargar un archivo de ejemplo aquí:

Argumentos de Entrada

Estos son los argumentos de entrada de la fórmula desarrollada para Excel usando Visual Basic

• Datos: Deberemos especificar las celdas donde se incluyen los datos de mercado, que obviamente deberemos haber descargado previamente.

La estructura de los mismos es bastante sencilla, y simplemente deberemos elegir un rango de celdas que contengan una estructura como sigue:

Fecha, Apertura, Alto, Bajo, Cierre.

• Fecha de Inicio: Deberemos indicar el momento exacto en que entramos en la operación, ya sea comprando o vendiendo.
• Precio de Inicio: Indicamos el precio al cual compramos o vendemos.  Realmente este argumento no se utilizará en la fórmula, pero lo incluí pensando en futuras mejoras de la misma.
• Precio Bajo: Indicar el nivel que queremos buscar por debajo de nuestro precio de entrada.
• Precio Alto: Indicar el nivel que queremos buscar por encima de nuestro precio de entrada.
• Resultado: La función devuelve diferentes resultados en base al argumento elegido, los cuales se explican en el siguiente apartado.

Argumentos de Salida

Como comentaba anteriormente, la fórmula proporciona diferente información en base al argumento de salida elegido en el apartado “Resultado”.

Estas son las siguientes opciones:

1. Indica la Fecha en la que se alcanza el Precio Bajo o el Precio Alto
2. Indica el precio tocado, ya sea el Bajo o el Alto
3. Indica la Fecha en la que se toca el Alto
4. Indica la Fecha en la que se toca el Bajo
5. Indica el Precio Máximo alcanzando entre la Fecha de Inicio y la Fecha en que se alcanza el Alto o el Bajo.

Obviamente, si se ha alcanzado antes el alto que el bajo, este precio será similar al Precio Alto, pero en caso de que se haya alcanzado el Precio Bajo en primer lugar, nos indicará cual ha sido el precio máximo del subyacente antes de alcanzar el Precio Bajo.

6. Indica el Precio Mínimo alcanzado entre la Fecha de Inicio y la Fecha en que se alcanza el Alto o Bajo.

Igual que anteriormente, nos servirá para saber cuál ha sido el Precio mínimo alcanzado antes de tocar el Precio Alto.

Decir por otro lado que si sólo queremos buscar un nivel determinado, ya sea el alto o el bajo, podemos dejar uno de los argumentos en blanco.

Usos de la Función en Excel

Utilidades de esta función hay muchas, según lo que se persiga, y con un poco de imaginación se pueden construir hojas de cálculo que nos arrojen bastante información.

Voy a poner dos ejemplos a modo ilustrativo, para lo cual deberéis descargar una hoja algo más pesada en tamaño ya que incluye un histórico de datos de un par de meses.

Nota:  En los ejemplos se incluyen las primeras fórmulas de BuscarFX, pero el resto (hacia abajo) se han pegado las celdas como valores para evitar que recalcule la misma cada vez que metemos algo nuevo, ya que el cálculo de la función se hace algo pesado si tenemos muchos datos.

Ejemplo 1.  Completando un Backtest con distintos Ratios Riesgo / Beneficio

En este caso contamos con un Backtest ya realizado en ForexTester donde podemos ver el resultado en el rango “B:I”

Ahora haremos los siguientes pasos:

1. Determinamos los objetivos (TP) en base a diferentes ratios riesgo / beneficio. Es decir, con un SL original, determinamos el TP para ratios del 0.5R, 1.5R y 2R.  Esto lo hacemos con una sencilla fórmula en las columnas “P:R”.
2. Utilizamos la Fórmula BuscarFX para determinar qué se alcanza antes desde el momento de la entrada, el SL o el TP. Esto lo hacemos en las columnas “T:V”
3. Con una simple fórmula, determinamos si la operación ha sido ganadora o perdedora, obteniendo el resultado en términos de R. Esto lo hacemos en las columnas “K:N”.

Con eso ya tenemos el resultado de nuestro Backtest preliminar, aplicando distintos ratios de salida de la operación.  De esta forma, ahorramos tiempo a la hora de realizar el Backtest, y ampliamos la información que nos proporciona el mismo.

Vemos por ejemplo que aplicando una relación 1:1, habríamos conseguido un resultado de un 3R (4.5% si arriesgamos un 1.5% por operación), pero sin embargo, el sistema arroja mejores resultados (5.5R) si vamos a por un ratio riesgo / beneficio de un 1.5R.

Ejemplo 2.  Realizando un Backtest completo en Excel

Supongamos que queremos probar un sistema tan simple como operar en doble 0 (X,XX00) en contra de la tendencia previa, suponiendo que esa cifra psicológica frenará el precio marginalmente y se producirá un pequeño retroceso en el mismo que trataremos de monetizar.

Por poner un ejemplo concreto, suponiendo que el mercado está a 1,3345, operaremos una de estas dos operaciones (la primera que se produzca), con un SL de 12 pipos y un TP de 18:

• Compra a 1,3300 con SL 1,3288 y TP 1,3318
• Venta a 1,3400 con SL 1,3412 y TP 1,3382

Vamos a ver cómo realizamos estos cálculos en Excel:

1. En primer lugar deberemos encontrar los momentos concretos en que el cruce llega a los niveles doble cero, para lo cual utilizamos la Función BuscarFX de una manera recursiva hasta contar con todas las fechas.

2. Una vez tenemos las fechas anteriores, establecemos el TP y SL.

3. Con la tabla anterior, ya tenemos la fecha de entrada y los niveles a buscar (TP y SL) para usar en la Función BuscarFX, de forma que podemos saber el resultado de cada operación.

Fijaros cómo realmente de un total de 82 operaciones, tan sólo hemos ganado un neto de 4, dando así un porcentaje de aciertos del 52,4%.  Sin embargo, en tanto la relación riesgo / beneficio es del 1,5R, el resultado final de la estrategia ha sido de un 25,5R en un período de casi cinco meses.

Eso implica que si arriesgamos un 1,5% por operación, habríamos conseguido un 38% de rentabilidad, más todavía si  consideramos el poder de reinversión o de los intereses compuestos, consiguiendo así una rentabilidad del 44,4%.

Nota:   Debo decir que este sistema se me ha ocurrido como algo simple para exponer en la web las posibilidades de esta función, pero  me he llevado una grata sorpresa al ver los resultados!! J

Cómo Exportar la Función a otras Hojas de Cálculo

La función aquí incluida tan sólo estará disponible para la hoja de cálculo adjunta, pero es posible que queramos poder utilizar esta función en otras hojas de cálculo, o tenerla disponible siempre en nuestro Excel.

Función Disponible en otra hoja de cálculo

1. Descargamos este módulo de VB con la función: BuscarFX
2. En la hoja de cálculo en cuestión, abrimos el editor de Visual Basic (Alt + F11)
3. En el editor, señalando la hoja Excel, botón derecho + Import File
4. Elegimos el archivo BuscarFX.bas ya descargado (descomprimir primero), y ya tendremos la función disponible en dicha hoja.

Función Disponible en Excel de forma permanente como Función adicional

Para ello realmente tendremos que grabar la macro como un complemento de Excel, e incluirlo en la lista de complementos para que se cargue al arrancar el programa.  De esta forma, podremos usar la fórmula en cualquier hoja que abramos o creemos.

En este link viene perfectamente explicado cómo hacerlo.

Cómo crear un Complemento de Excel

Mejoras de la función

Lo primero que tengo que decir es que apenas sé de programación.  Un par de cursos básicos de Visual Basic para Excel y algo de ayuda a través de internet y de un conocido me han permitido desarrollar esta Función, pero no sería de extrañar que el código pudiese optimizarse.

Dicho lo anterior, me he encontrado con los siguientes problemas

En primer lugar, un archivo con un histórico del  EURUSD en temporalidad de 1 minuto ocupa muchas más filas que el límite que tiene Excel (1.048.576) de forma que básicamente no es posible importar un .csv completo a Excel como tal sino que hay que partirlo por años, con los problemas que esto ocasiona.

Estoy seguro que hay mejores maneras de acceder a la información, ya sea guardando los datos en Access y accediendo a los mismos desde Excel, o incluso con alternativas más avanzadas como puede ser el uso de SQLite.

Sinceramente, no le he dedicado apenas tiempo a investigar sobre estas alternativas, pero agradecería si cualquier lector entendido en la materia puede proponer alguna solución para optimizar el uso de la Función.

Lo óptimo sería disponer de todo el histórico de todos los pares en alguna base de datos, y poder llamar desde cualquier archivo de Excel a la misma para recabar la información expuesta en este artículo.

Cualquier ayuda al respecto, sería más que bienvenida, y seguro que muchos otros lectores lo agradecerían.

El Segundo problema que me he encontrado en el uso de esta Fórmula es la cantidad de recursos que utiliza, haciendo que el tiempo empleado en hacer todos los cálculos cuando tenemos muchas datos es elevado.

Por poner un ejemplo a este hecho, en el Backtest que estoy realizando, un ordenador con 8 nucleos tardó cerca de 80 minutos en hacer los cálculos de unas 2.800 celdas.  Es cierto que acumulaba un histórico del EURUSD de varios años, pero lo cierto es que se hace un poco pesado si queremos utilizar esta función para optimizar un posible Backtest.

Desconozco si este problema podría solucionarse teniendo los datos en una base externa o utilizando algún programa intermedio.  En cualquier caso, como dije antes, cualquier ayuda de expertos en la materia sería de agradecer.

Comentarios o Dudas

Como siempre, abro un hilo en el Foro para comentar cualquier duda relacionada con el artículo, o para discutir posibles mejoras en la Función.

El hilo podéis encontrarlo aquí:  Artículo Backtesting en Excel – Dudas o Comentarios