Estadisticas en Excel

[ranunculo]

Hola, se me ha ocurrido mostrar en el foro, en varios posts, cómo hacer estudios estadísticos sencillos para comprobar la fortaleza de un sistema, usando simplemente Excel.

Y si a alguien se le ocurren formulas o estudios que se puedan hacer, pues las comenta..

En primer lugar, hay que copiar los resultados de un backtest del sistema.
El periodo testado debe ser al menos de 4 o 5 años, si son barras de día, o unos 1000 trades si operamos en barras mas pequeñas (aunque en este caso habría que considerar si el periodo de test incluye o no todo tipo de condiciones de bolsa).

Los datos se pueden exportar de nuestro software de backtest e importar en Excel, simplemente con la fecha del trade y el valor. Los datos exactos pueden ser el beneficio en euros del trade, o el beneficio en % respecto a la inversión de de ese mismo trade, o en resultados de la equity, bien en dinero absoluto, o en porcentaje, o en resultados de la equity a fin de día, en dinero o porcentaje.
En cualquier caso, para el estudio, inicialmente usamos un backtest sin reinversión, es decir, apostando siempre la misma cantidad absoluta, sin gestión de capital.

En mi caso, llevo a Excel resultados de mi sistema y después, calculo los porcentajes de beneficio respecto de la equity del día anterior.
Muestro un ejemplo en Excel con la equity partiendo de 40.000, y con la columna D con el calculo del % sobre el dia anterior:

El producto de los porcentajes es el total de beneficio (Total Wealth relative), y su raiz n-esima es la rentabilidad anual, considerando N el nº de años.
Despues, sacaremos la distribucion de las probabilidades de beneficio por trade, o en este caso, por día, para comprobar de un vistazo qué tal se porta este sistema.

Para ello, usamos la utilidad de Excel "histograma", dando un rango de datos que son todos nuestros trades, y un rango de clases, que son el detalle de rentabilidades que queremos graficar. En este caso, indicaremos que deseamos ver las ocurrencias de 0,05% de beneficio.
Excel nos crea una tabla tal como:

Con esta tabla, podemos hacer un histograma de densidad de probabilidades:

En este grafico ya podemos sacar conclusiones de nuestro sistema. Como suele ocurrir, el grafico es aproximadamente gausiano, pero con "largas colas". Es decir la mayor parte de los resultados estan en una banda estrecha, y con bajas probabilidad de ocurrencia hay muchos resultados relativamente extremos, tanto positivos como negativos. Por ahi andan los cisnes negros..

[ranunculo]

Otro modo de ver los mismos datos pero desde otro "angulo" es usando las probabilidades acumuladas, que tambien nos lo permite Excel:

Este grafico tambien nos indica lo mismo: casi siempre vamos a tener rentabilidades cortas, pero habrá ocurrencias de rentabilidades extremas más veces que la distribucion Normal.

Viendo las tablas, por ejemplo, vemos que el 70% de los dias el sistema va a ganar entre -0,25% a +0.25%.

Pero hay 13 días que se pierde un -1.5% a -2%. Y hay 13 dias que se gana 2% a 3% e incluso mas.

Esta curva esta por tanto un poco desplazada hacia la derecha. Es normal, si el sistema tiene esperanza positiva.

Bueno, hasta aqui es muy simple todo. Otro día voy complicando el ejemplo, que ya me he cansado..

salud!

[polxx]

En excel 2010, ¿dónde esta esa función?

Llevo unos días, haciendo walk forwad con excel, pero sin usar macros, todo a base de fórmulas.
La idea es hacerme una plantilla estándar, a ver si soy capaz de completarlo bien.

[ranunculo]

En excel 2010, ¿dónde esta esa función?

La funcion histograma forma parte de las herramientas-análisis de datos.

Si no te aparecen, es posible que tengas que añadirlo desde el disco de instalación de Office, ya que por omisión me parece que las herramientas de análisis no se instalan..

suerte con tu WF!

[ranunculo]

Otro grafico interesante es, obviamente, la equity.
Tecleamos en la columna E el cálculo del tanto por 1 de la equity, siendo 1 el capital inicial el 1º dia.

La formula obvia es B2/B$1, y la arrastramos hasta el final.
Añadimos en columna F las cifras, tambien en tanto por 1, de la maxima equity, = max(E3:E$1), y la arrastramos.:

El grafico donde se ve la curva de beneficios, y las caidas desde máximos sería tal como:

Y de aqui podemos pasar ya al estudio del gran problema de las inversiones: el Draw down

[Gamelu]

Ranunculo puedes compartir algun archivo de excel para entenderlo mejor?

[ranunculo]

Si, claro, ya pasare el ejemplo. El tema es que lo estoy elaborando a poquitos, a partir de varias hojas de calculo de sistemas mios..

Esta bien contar cosas porque te obliga a organizarte un poco; Y ademas a reflexionar y separar lo que esta bien claro de lo que es opinable.
Por eso, ahora mismo tengo un monton de calculos a medio organizar, con todo en retazos.. en cuanto os cuente un par de cosillas paso todo en un excel legible.

salud!

[ranunculo]

Una vez que tenemos la maxima equity en la columna E, podemos añadir en la columna F el Draw down, cuya formula es maxima equity/equity en curso, es decir columna E/columna D, y arrastrar.

Obtenido el DD de cada día, calculamos el maximo DD a la fecha, que es la formula MAX(F2:F$1), y arrastrar.

Con estos datos podemos representar, por un lado los DD de cada fecha.

Y por otro, el Maximo DD historico, denominador del indice Calmar.

Enseguida se sacan un par de conclusiones para este sistema, una mala y otra buena:
La mala es que los DD ocurren siempre de modo "abrupto". Es decir, de repente cae la equity en un DD en muy pocos días. Esto hace que sean difíciles de evitar con gestión de capital. Al menos para sistemas como el del ejemplo, aunque yo creo que ocurre igual para todos o casi todos los sistemas automáticos.

La buena es que, en el gráfico de maximo DD, hay un límite que parece que no se supera, una cifra de Maximo DD en 11 años(cerca del 8%) que es el límite de lo que podemos perder.

Lamentablemente, la conclusión buena en realidad es falsa. No hay un límite máximo. O dicho de otro modo, la máxima pérdida siempre está esperandonos en el futuro. Si dejamos tiempo suficiente, cualquier sistema automático que a veces pierda, acabará arruinándonos.

El gráfico de maximo DD es muy bueno porque el sistema no tiene reinversión.
Conforme pasa el tiempo el capital es más grande, y nuestra inversion no varía. Asi que los DD tienden a ir hacia abajo, tal como se ve en el gráfico. El problema es que tambien iran hacia abajo las rentabilidades.

Pero, ¿que pasa si reinvertimos?. ¿Si componemos los resultados?

->proximo post!

[ranunculo]

Bien, los datos con reinversion de beneficios se grafican exactamente igual que los otros.

Lo unico que hay que variar son los datos de la equity, que deben venir compuestos desde el origen.

Directamente cuelgo los graficos del mismo sistema que he usado hasta ahora, pero compuesto. Este son los DD por fecha:

Y el acumulado de maximos DD es:

Como se ve, los DD son mucho mas grandes, más del doble. Y aunque pueda haber periódos largos sin superar el maximo DD historico (como entre los años 2005 y 2010), tarde o temprano se supera el maximo DD, en este caso se llega al 18% en 2011.

Este tipo de estudios se pueden realizar más rápidamente en software comercial, pero la ventaja de Excel es que es mucho más versátil para hacer cualquier tipo de estadísticas.

Por ejemplo, una nueva tuerca de vuelta es introducir muestreos aleatorios contra nuestros datos. Esto permite hacer diferentes distribuciones tipo Montecarlo.

Voy a intentar preparar un ejemplo que muestre una histograma de frecuencia rentabilidades anuales y otro de frecuencia de draw downs, en vez del tipico Montecarlo con un monton de equitys de colorines..

Es fácil, pero es un poco más trabajoso que lo visto hasta ahora. A ver si me sale..

salud!

[nostrasladamus]

Conforme pasa el tiempo el capital es más grande, y nuestra inversion no varía. Asi que los DD tienden a ir hacia abajo, tal como se ve en el gráfico. El problema es que tambien iran hacia abajo las rentabilidades.

Hola Ranunculo,

No estoy de acuerdo en esa forma de calcular el DD respecto al capital en cada momento. Creo que deberia hacerse respecto al capital inicial.
Hay que ponerse en la peor situacion, y en este caso, la peor situacion seria comenzar con el capital inicial justo antes de ese DD. En ese caso el % de DD habria sido mayor...

A partir de ahi, ademas, todo podra ir peor

Muy interesante el post, siempre se puede aprender algo nuevo, gracias.

Un saludo,

[ranunculo]

Hola Ranunculo,

No estoy de acuerdo en esa forma de calcular el DD respecto al capital en cada momento. Creo que deberia hacerse respecto al capital inicial.

No se si te entiendo.
Si tenemos un capital inicial de 10.000 euros, invertimos, al de 3 meses tenemos 15.000, y desde ahi caemos un 20%.. eso son 3.000 euros, el 20% de lo que tenemos pero el 30% de lo inicial..

Es muy distinto pensar en porcentajes que en cifras absolutas.

Por cierto, adjunto un excel con los ejemplos expuestos. He copiado de aqui y de allá, confio que no me haya equivocado en los calculos..


salud!

[nostrasladamus]

Eso es,

Tu comienzas con 10.000 eur y tras 3 meses, con 15.000 eur, has perdido 3.000, y cuentas un DD del 20%, como en tus calculos.

Yo, con lo que me habias comentado, tras ver que a ti te funciona los 3 primeros meses, me meto tambien con 10.000 eur, siguiendo tu misma estrategia.
Como todavia no reinvertes, tu y yo usamos los mismos lotes, todo igual. Mi caida es del 30%.

Tu te encontrarias dentro de los resultados historicos, pero yo estaria "fuera".....

[ranunculo]

Claro, Nostraladamus. El problema es el modo de hacer las inversiones.

Siempre tengo dificultades para explicar mis números porque la mayor parte de los traders piensan en futuros, donde invierten siempre en bloques grandes (contratos), normalmente usando apalancamiento.

Tu planteas que inviertes en la punta de la equity, que esta en 15.000 euros, y supones que compras 15.000 euros de un lote, a pesar de que tienes sólo 10.000 euros de capital. Y claro, al caer 3.000 euros el lote, pues pierdes un 30%.

Pero yo, para bien o para mal, me decanté por el trading sobre acciones hace mucho tiempo, con lo que compro en "lotes" pequeños, es decir muchas acciones cada vez. Y habitualmente, sin apalancamiento.

Eso significa que, si yo soy un inversor en acciones y entro, como tú, en la punta de la equity, no compro 15.000 euros, sino 10.000 (todo mi capital), por lo que, al caer las acciones, pierdo un 20%, 2000 euros.

Es simplemente el modo de ver las compras. Si no usamos apalancamiento, es muy fácil calcular todo en porcentajes.
Este modo de calcular los beneficios y los DD lo he visto en muchos libros y manuales, y siempre me ha parecido más fácil de entender que cuando hablamos de inversion usando el margen.

Supongo que es una cuestión de hábitos..

Edito: aunque todavía no reinvierta, se supone que la cantidad fija a invertir sería 10.000 euros, aún estando en la punta de la equity, con lo que sigo teniendo el DD del 20%.. no se si me explico..

[ranunculo]

Bueno, last but not least, muestro en el ejemplo como hacer un montecarlo, espero que no resulte muy duro..

Los cálculos son los siguientes:

* Copiamos los datos de beneficio a una nueva hoja de calculo
*En la columna Random ponemos numeros aleatorios, con la formula: "=randbetween(12;2805)". Esto elige una fila al azar de las 2793 filas con datos de nuestro backtest
* En la columna Benedia, elegimos el valor de beneficio (col. A) de la fila especificada en la columna random (columna D), mediante la formula "=desref(A12;$D12;0;1;1)+1"

El randbetween crea un nuevo numero aleatorio cada vez que actualizamos la hoja o presionamos F9.
Las columnas equity, Max equity y DD se crean como ya he comentado.

Despues, entresacamos 250 dias de la columna A, que tiene 11 años (2793 dias) de datos. Cada 250 dias son mas o menos 1 año. Multiplicando los 250 dias de beneficio, obtenemos el CAR (tasa anual de beneficio) y el DD maximo, que lo situamos en G9 y H9.

Cada vez que presionemos F9, ese CAR y Maximo DD va variando. Ahora bien, ¿como guardamos esos distintos datos para hacer una distribucion?
Bueno, usando una macro muy sencillita, que lanza el randomize y copia esos CAR y MDD a las columnas K y L.

Este sería el codigo de la macro del botón:

Dim vCelda1 As String
For i = 12 To 1011
Range("g9:h9").Select
Application.CutCopyMode = False
Selection.Copy

vCelda1 = "k" + Trim(Str(i)) + ":" + "l" + Trim(Str(i))
Worksheets("montecarlo").Range(vCelda1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i

Este codigo lanza 1000 veces el randomize, cada uno de los cuales dan un CAR y un MDD. Ese CAR y MDD se copian a las columnas K y L.
Esos 1000 años aleatorios tienen un promedio, un mínimo y un maximo de rentabilidad anual y maximo DD que se ve en las columnas K y L. Vemos que los extremos son perder un 12% en 1 año, y ganar un 166%en el mejor. Y el peor DD es un -27%, y el mejor un -3% en un año.

Ahora bien, esto son distribuciones, que como hemos visto hasta ahora se pueden ver en un gráfico.

Copiando esos datos a una nueva hoja de calculo (para que no nos estorbe el randomize), podemos sacar los gráficos habituales:

Este es el de rentabilidades anuales:

Y este el de maximos DD anuales:

Y ahora aqui sacamos informaciones interesantes:

Si queremos saber cuanto vamos a ganar, con un 90% de probabilidad, hay que ver los valores de CAR por debajo del 5% del tiempo, y por encima del 95%: veremos en el grafico, que esos valores son 1,10 o menos el 5% de los años, y 1,96 o mas el 5% de los años. Es decir, el 90% de los años estaremos entre 10% y 96% de rentabilidad anual.

Los maximos DD, con una probabilidad del 90% son los que se encuentran entre el 5% y el 95% del tiempo, igual que los CAR. Vemos en la probabilidad acumulada que esos valores son 0,05 y 0,17. Es decir, el 90% de los años perderemos entre un 5% y un 17% anual.

Eso si, la curva de DD se va mucho hacia la derecha: los valores mas extremos del DD llegan hasta el -27%. Pero solo ocurren un 5% del tiempo..

Bueno espero que se haya entendido lo que he hecho. Aunque no se, no veo demasiado interés en estas cosas..
En fin, seguro que a alguien le sirve de algo..

salud!!!!!!!!!!!

[ranunculo]

Bueno vuelvo por aquí con otros pensamientos
Ultimamente estoy llegando a una conclusión importante en el tema de los sistemas automáticos: hay que centrase más en el portafolio de sistemas, más que en los sistemas en sí mismos.

Por supuesto, los sistemas que utilicemos deben éster testados y ser rentables y estables, pero todos sabemos que tarde o temprano los sistemas dan resultados mucho peores que nuestras expectativas.
Asi que estoy testando como dividir el capital entre los sistemas.

Adjunto muestro 3 sistemas y sus resultados, así como un 4º sistema mezcla de los anteriores:

Los dos primeros sistemas dan una rentab. anual entre el 40 y el 50%, pero con DD inaceptables. El 3º sistema da un 16% pero con un DD superior, del 24%.

Si cogemos los resultados diarios de sus equities, y las pegamos en Excel,y creamos un equity combinada donde asignamos el 33% de cada resultado en la combinada, obtenemos una equity con resultados mucho mejores:
un 38% de rentab. anual con un DD del solo el 15%.

Esto ya se sabe, la combinación de sistemas mejora resultados.
Pero me gustaría ir un poco más alla.
He experimentado un poco, y he probado otro algoritmo, ya que en Excel es fácil de hacer la formula:
Doy un 33% a cada sistema, salvo que el DD del sistema caiga más de un 15%, en cuyo caso asigno al sistema sólo el 16%, hasta que la equity original del sistema vuelve a un DD inferior al -15%, que es cuando vuelvo a asignarle el 33% del capital.

Parecía una buena idea en principio: ralentizar los sistemas en DD, volver a apostar por ellos cuando disminuye el DD. Pues no. Los resultados son parecidos a la equity combinada, pero peores:
Un CAR del 36% en vez del 38%, y un maximo DD superior, del 16%.

He hecho una nueva prueba, pero esta vez utilizando la volatilidad de las equities, medidas mediante la desviación estadística de 10 barras, que también se hace sencillo en Excel.

El algoritmo es: doy a cada sistema un 10% del capital, y el 70% restante lo asigno a cada sistema en proporcion directa a la proporcion de desviaciones. Por ejemplo, si el sistema1 tienen una desvacion estándar invertida de 50, el Sistema 2, de 50, y el 3 es mas volátil porque tiene una desviación invertida de 20, asigno a los 2 primeros sistemas el 41,6% (50/120) del 70% del capital, y al 3 sistema más volátil, el 16,6% (20/120) del 70% del capital.

Es muy fácil de hacer en Excel. Asigna más capital a los sistemas poco volátiles, y más a los más volátiles.

Los resultados mejoran:

El CAR sube al 43% anual, y el máximo DD solo sube al 18%, siendo mejor el indice calmar.
En 10 años, 10.000 euros se convierten en 413.000 euros, en vez de en 279.000 de la combinada normal, una diferencia importante.

La desviacion estadística sube mucho, aunque el promedio de DD disminuye de -2,7% a -2,2%. La desviación sube porque el sistema combinado gana más, tiene "brincos" al alza. La desviación estándar y el sharpe a veces dan información poco práctica, habría que calcular el Sortino, que no lo he hecho.

En suma, una linea de investigación que me parece prometedora.
Alguna pregunta: ¿alguien investiga la mezcla de sistemas de modo parecido? ¿Con qué herramientas? ¿Usais rentabilidad compuestas?.

Bueno, cualquier input será bienvenido..!

Salud!