Artículo patrocinado por BisonTrade. Tu broker español de confianza.
Seguramente recuerden la extensa serie que publicamos este verano sobre extracción de estacionalidades con Python que nos mostró el gran Elmer Niño. Por si no se acuerdan, aquí tienen los enlaces:
- Trading Estacional I. Introducción
- Trading Estacional II. Análisis Cuantitativo en Python
- Trading Estacional III. Extrayendo Estacionalidades
Sin embargo, aunque sé que muchos de nuestros lectores se manejan bien con la programación, lo cierto es que no todo el mundo se defiende o está cómodo con los lenguajes informáticos. Y dado que aquí en X-Trader.net no se discrimina a nadie por sexo, religión, plataforma de trading o lenguaje de programación utilizado :D, aquí va un sencillo tutorial que os permitirá extraer estacionalidades de forma muy sencilla usando tablas dinámicas en Excel. Ah, ¿qué no sabéis que es eso de las tablas dinámicas? Vamos al lío…
Tablas Dinámicas en Excel
Aunque la mayoría de usuarios de Excel apenas las conocen o usan, las tablas dinámicas (o pivot tables en inglés) de Excel posiblemente sean una de las herramientas más potentes de esta hoja de cálculo, por cuanto nos van a permitir resumir, agrupar y ordenar información de forma masiva, con el fin de extraer patrones y tendencias en los datos, pudiendo modificar rápidamente las métricas calculadas para los datos, así como intercambiar las filas y columnas de las categorías mostradas.
Para que se entienda mejor, veamos un ejemplo rápido de cómo hacer una para empezar a intuir la potencia que tiene esto de las tablas dinámicas. Imaginemos que tenemos la siguiente base de datos de vendedores:
Supongamos que queremos averiguar el importe de las ventas por modelo de móvil (columna E), agregadas por vendedor (columna B). Es decir, vamos a cruzar la información para obtener un cuadro resumen que nos resultará muy útil, por cuanto podremos ver por ejemplo qué vendedor vende más, sabiendo además en qué modelos tiene un mejor resultado.
Para ello, nos vamos al menú Insertar -> Tabla Dinámica y seleccionamos que tome los datos de una tabla o rango:
En la siguiente ventana, simplemente seleccionaremos qué datos queremos coger para trabajar y dónde queremos que nos cree la tabla dinámica:
Automáticamente nos saltará una nueva hoja de cálculo con el siguiente aspecto:
Ahora ya solo faltaría configurar los campos que queremos agregar a la tabla para dotarla de contenido. Dado que nos interesa conocer el cruce de vendedores y ventas por modelo, basta con poner en filas el campo Vendedor, en columnas el campo Marca, y en Valores calcularíamos la Suma. Una vez hagamos click en Actualizar el resultado sería el siguiente:
Observad que hemos tomado la suma, pero dentro del apartado de Valores tenemos un montón de posibilidades más: recuentos, promedios, máximos, mínimos, productos, desviaciones típicas, varianzas… Por supuesto, también existe la posibilidad de mostrar los resultados en términos porcentuales.
Estoy seguro de que, después de ver esto, muchos de nuestros lectores ya se huelen por dónde van los tiros. Y es que combinando los campos de una base de datos de cotizaciones, podemos construir casi cualquier análisis de estacionalidad que se nos ocurra. Vamos a ello.
Estacionalidad en el Nasdaq 100 con Tablas Dinámicas
Para nuestro ejemplo, he seleccionado el histórico en barras de 1 hora del futuro e-mini Nasdaq 100 entre enero de 2010 y octubre de 2022, aunque por supuesto los pasos van a ser reproducibles en cualquier otra base de datos. Ah un detalle importante aquí: las horas de la base de datos se corresponden con las de la apertura de la barra y están en horario GMT (spoiler: cuidado a la hora de interpretar los resultados).
Con nuestra base de datos del Nasdaq, y antes de ponernos a realizar tablas dinámicas como si no hubiera un mañana, toca calcular algunas columnas extra que nos vendrán muy bien para hacer algunos cálculos.
Por un lado, me he sacado un par de columnas con las variaciones porcentuales y en puntos de cada barra:
También me interesa conocer los rangos, tanto desde un punto de vista absoluto como porcentual:
Por último, he convertido la primera columna a formato fecha (simplemente he reemplazado los puntos por barras) y he obtenido el día de la semana, el mes y el año al que corresponde la fecha, usando la función TEXTO:
Para obtener el mes y el año, simplemente basta reemplazar en la fórmula TEXTO el valor “dddd” por “mmmm” o por “aaaa” respectivamente (columnas L y M).
Con todo esto, ya podemos hacer muchas cositas interesantes :D. Empecemos por lo más sencillo: ¿cuánto sube o baja de media en cada hora el futuro del Nasdaq 100? Para ello, repetimos los pasos que hemos visto anteriormente: insertamos Tabla Dinámica desde el menú, seleccionamos todo el rango de datos que tenemos, marcamos que nos cree una nueva hoja de cálculo y construimos la tabla que deseamos.
En este caso, en filas ponemos el campo <TIME> con las horas y dentro de Valores ponemos Promedio de <VAR> (no tenéis más que hacer click en Suma de <VAR>, seleccionar Configuración de Campo de Valor y cambiarlo por Promedio). Nos queda configurado de esta manera:
Si lo hemos hecho bien nos saldrá algo como esto:
Por supuesto, podemos darle un formato de heatmap como hacen en Python, simplemente seleccionando los valores de Promedio de <VAR> y yendo a Inicio -> Formato Condicional -> Escalas de Color y seleccionar la gama de colores que queramos:
El resultado obtenido con los colores ahora resulta mucho más legible:
Como podemos ver, en promedio el Nasdaq tiene a subir con más intensidad entre 6 y 7 h., y entre 13 y 14 h. GMT. Por el contrario, el Nasdaq tiende a bajar en las siguientes franjas horarias: 11 -12 h., 12 – 13 h. y 14-15 h. GMT.
Por supuesto, podemos poner esto en términos porcentuales sin más que reemplazar <VAR> por <RET>:
En términos porcentuales la cosa cambia ligeramente: en este caso, el Nasdaq 100 tiende a subir de 1 a 2 h. y de 6 a 7 h. GMT, mientras que claramente baja de 17 a 18 h. GMT.
¿Tendrá algún efecto el día de la semana en estos resultados? Basta con añadir el campo en columnas para averiguarlo:
De la tabla anterior se desprende que si tuviéramos que vender de 17 a 18 h. sin duda el mejor día de la semana para ello sería el jueves por cuanto coincide la pauta en ambos casos. Asimismo observamos una nueva pauta a estudiar: subidas los viernes de 19 a 21 h.
Por supuesto, si cambiamos por ejemplo la columna de <DAYOFWEEK> por <MONTH> podemos encontrar más pautas que analizar estudiar:
Análisis de Volatilidades en el Nasdaq 100 con Tablas Dinámicas
El análisis anterior se puede replicar para analizar en qué horarios se mueve más el futuro sobre Nasdaq 100. Para ello, ahora vamos a analizar el promedio de los rangos, tanto en términos absolutos como porcentuales en dos columnas.
Los resultados son los siguientes:
Podéis ver cómo de un vistazo rápidamente detectamos:
- Una clara zona de aumento de volatilidad que coincide con la apertura del mercado estadounidense (entre las 13 y 15 h. GMT), tanto en términos absolutos como porcentuales.
- Un marcado descenso de la amplitud de las velas en el horario asiático (21 – 6 h. GMT).
- En términos porcentuales, también observamos un leve repunte de los rangos porcentuales tras la apertura europea (8 – 10 h. GMT).
Nuevamente podemos combinar, por ejemplo, los rangos porcentuales con días de la semana:
Como podemos ver en la tabla anterior, el resultado es contundente: aumento de volatilidad tras la apertura de la sesión europea, pero sobre todo en la apertura del mercado estadounidense. No obstante, en el resultado emerge también un interesante detalle: los viernes de 16 a 20 h. GMT la volatilidad parece que también tiende a repuntar.
Conclusión
Acabamos de ver cómo con una buena base de datos y un poco de imaginación se pueden extraer rápidamente pautas estacionales y filtros horarios a partir de los cuales podemos crear estrategias o mejorar las que ya tengamos. No obstante, tras este ejercicio didáctico, conviene hacer algunas puntualizaciones metodológicas relevantes:
- Por un lado, es importante revisar que trabajamos con unos datos con una calidad mínima. Esto es algo que puede parecer de Perogrullo pero que conviene no olvidar nunca, por cuanto si trabajamos con datos erróneos o incompletos, estaréis introduciendo sesgos en el análisis que pueden haceros perder bastante dinero. No obstante, en el momento que empecéis a hacer análisis de vuestros datos con algunas tablas dinámicas, rápidamente os daréis cuenta de si existen errores (de hecho, esta puede ser una excelente manera de comprobar la validez e integridad de nuestros datos).
- Por otro, hay que tener cuidado con el tema de las horas ya que estas se pueden ver afectadas por los cambios de hora a nivel internacional. Por ejemplo, la apertura del mercado estadounidense puede producirse a las 13.30 h. o a las 14.30 h. GMT, dependiendo de si estamos en horario de verano o de invierno. Por otro lado, cuidado porque si tomamos el horario español, tendremos un problema con aquellas semanas en las que España ha cambiado la hora pero EE.UU. no. Por todo ello, para realizar este tipo de análisis antes de nada conviene hacerse con la base de datos en el horario local o, alternativamente, transformar las horas (algo que puede resultar un poco laborioso pero que merece la pena).
Ahora, queridos lectores, es vuestro turno: podéis bajaros desde aquí la hoja de cálculo utilizada en este artículo para que podáis practicar y experimentar con las tablas dinámicas, así como encontrar los dos errores a los que hago referencia.
Por cierto, os adelanto que en la hoja de cálculo utilizada hay un par de “huevos de pascua” en forma de errores en la base de datos, a ver si alguien los reporta en el Foro ;).
Saludos,
X-Trader
