Modelos de pronóstico con funciones de actualización automática

El tema que trata este newsletter es la configuración de un modelo de pronóstico (ej. ARMA, GARCH, de regresión), de tal manera que usted (o cualquier otra persona) pueda mantenerlo fácilmente a medida que nuevos puntos de datos estén disponibles y que los pronósticos se hayan auto actualizado.

Para demostrarlo, hemos definido un modelo GARCH para los retornos mensuales de S&P 500 ETF, calibramos los valores de parámetros y construimos una estructura temporal de pronóstico de volatilidad para los siguientes 36 meses. Luego conectamos un nuevo punto de datos y observamos que el modelo avanzaba la fecha de inicio del pronóstico y actualizaba los valores de pronóstico.

¿Por qué nos debe importar?

En la práctica hemos creado muchos modelos basados en Excel, algunos de ellos para un único uso, pero con frecuencia compartimos muchas hojas de cálculo con colegas, que son revisadas ocasionalmente y dejadas a un lado por mucho tiempo. Preparar nuestra hoja de cálculo para una auto actualización es relativamente simple. Una vez hecho esto, podemos añadir nuevos puntos de dato muy fácilmente, o revisar los ya existentes y ver nuestros cálculos actualizados gracias a mínimos esfuerzos.

¿Listos? ¡Ahondemos en el tema!

Preparación de datos

Para este tutorial estamos usando los retornos mensuales de S&P 500 ETF (SPY) entre enero de 2010 y noviembre de 2019:

Primero debemos adjuntar el conjunto de datos actual con marcadores de posición. Lo hacemos adelantando fechas (ej. diciembre 2019, enero de 2019) e ingresando #N/A como sus valores.

Modelado

Escogimos un GARCH (2,2) con las innovaciones de estudiantes para capturar la dinámica de volatilidad de tiempo variable mensual para este conjunto de datos. Para definir el modelo debemos ubicar una celda vacía en su hoja de cálculo y luego hacer clic en el ícono de “GARCH” en la barra de herramientas de “NumXL”, y el asistente GARCH aparecerá.

En la sección de “Datos de entrada” seleccionamos el rango de celdas de retorno mensual, incluyendo los marcadores de posición que adicionamos antes. No hay que preocuparse, las funciones NumXL descartarán las observaciones con valores #N/A en cualquier extremo del rango de celdas de entrada.
Seleccionemos ARCH y GARCH en la sección de especificaciones del modelo, y luego establezcamos el orden como 2. Hagamos clic en “Distribución-t de estudiante” bajo la sección de innovaciones. Ahora, hagamos clic en el botón de OK para confirmar nuestra selección.

El asistente GARCH genera el modelo de tabla e inicia sus valores de parámetro para valores válidos pero subóptimos. Para encontrar un conjunto óptimo de valores para los parámetros de GARCH, seleccionamos la celda del encabezado en la tabla modelo y hacemos clic en el icono de “calibrar” en la barra de herramientas de NumXL. El solucionador de Microsoft aparecerá con todo su conjunto de datos. Hagamos clic en resolver.

El solucionador de Microsoft busca una solución óptima para el proceso GARCH.

Exploración de Datos

Antes habíamos mencionado que las funciones de NumXL descartan cualquier observación en el rango de celdas de entrada con un valor igual a “#N/A”. ¿Cómo saber dónde finaliza el conjunto de datos?

Para responder la pregunta anterior necesitamos primero calcular el tamaño del conjunto de datos con dos funciones: RMNA(.) y COUNT(.). El RMNA(.) descarta puntos de datos con “#N/A” y arroja un despliegue de todas las observaciones no faltantes, y COUNT(.) devuelve el tamaño de esta matriz.

También podemos calcular la fecha final del conjunto de datos usando la función incorporada de Excel INDEX(.) y el tamaño del conjunto de datos anterior.

En suma, tenemos un conjunto de datos de 119 valores no faltantes y la última observación cayó en noviembre de 2019.

Pronóstico

Para este tutorial necesitaremos construir el pronóstico de volatilidad mensual para los siguientes 36 meses. Para hacerlo, seleccionemos la columna del encabezado de la tabla modelo y hagamos clic en el icono de “Pronóstico” en la barra de herramientas de NumXL.

En la sección de “últimos observaciones”, seleccionemos el rango de celdas en la columna “B”, incluyendo la observación con valores faltantes (ej. #N/A).

Para la volatilidad dejemos el espacio en blanco y NumXL usará la volatilidad en la muestra calculada por el modelo GARCH en sí mismo. Hagamos clic en el botón “OK”.

El asistente de pronóstico generará una tabla de pronóstico que expresa la unidad de tiempo en términos o pasos (ej. Mes) medida luego del final del conjunto de datos de entrada.

Con el tiempo, agregar nuevos puntos de datos avanza la fecha de la última observación (con valores no faltantes), impactando nuestra interpretación de la unidad de paso en la tabla de pronóstico. Para contrarrestar este problema hemos adicionado una columna a la izquierda de la tabla de pronostico para designar la fecha del calendario correspondiente.
Para calcular la fecha correspondiente para cada paso, necesitaremos las funciones NxEDATE(.) y CONCAT(.) como se muestra a continuación:

Usando la fecha final del conjunto de datos, NxEDATE devuelve los datos que cae luego de los N meses, donde N es el número de pasos en la tabla de pronóstico.
¿Qué sucede si los nuevos datos caen en un fin de semana o en un feriado? Como nuestro conjunto de datos es una serie de tiempo financiera, la fecha debe caer en un día laboral, de lo contrario debemos mover la fecha al siguiente día hábil (NxAdjust(.)).

¡Eso es todo! Examinemos pues la tabla de pronóstico y la curva de estructura temporal de volatilidad mensual.

El modelo GARCH revela una atmosfera de volatilidad baja – relativa al nivel del histórico (ej. 3.8%) pero pronostica un alza constante en la volatilidad durante el siguiente año.

Agregar puntos de datos

Examinemos lo que ocurre cuando tenemos un retorno mensual para diciembre de 2019 de, digamos, un -0.6%.
Paso 1: Ingrese el nuevo valor en el conjunto de datos.

Paso 2: Observar

El tamaño del conjunto de datos de entrada aumentó a 120 y la fecha de finalización es diciembre 2019 ahora.
En la tabla de pronóstico la fecha correspondiente para el paso uno -ahora, es enero de 2020 (era diciembre de 2019) y la estructura temporal de volatilidad es diferente (menos suave).

Paso 3: Avanzado
La tabla de pronóstico que aparece arriba utiliza el mismo proceso GARCH que hemos calibrado antes, especialmente el valor de los parámetros.
En aras del argumento, asumamos que queremos re-calibrar el modelo para conservar el orden de ARCH/GARCH (ej. P y Q) y la misma innovación de distribución (ej. Estudiante t).
Tal como hicimos anteriormente, ubiquemos y seleccionemos la fila de encabezado de tabla del modelo GARCH y hagamos clic en el icono de “calibrar” en la barra de herramientas de NumXL.
El resolvedor o solver aparecerá, pero esta vez la optimización comenzará con los valores óptimos de los parámetros (ej. valor inicial) de la última ejecución. Por lo tanto, esperamos una rápida conversión.

Hagamos clic en el botón del resolvedor o “Solver” para empezar el proceso de optimización.
Una vez finalizado, Solver muestra el cuadro de diálogo “Buscar solución”. Hagamos clic en el botón OK y este copiará los valores de los nuevos parámetros dentro del modelo de la tabla.
Para examinar el impacto de re-calibración en nuestro pronóstico, veamos nuestros valores de pronóstico en la tabla de abajo (y en la gráfica).

Conclusión

En este tutorial examinamos los dos pasos para configurar nuestros modelos para la actualización automática.
• Agregando puntos de datos de marcador al final del conjunto de datos de entrada,
• Seleccionando el conjunto de datos con los puntos de datos de marcador entre el modelo y el pronóstico.
A medida que el tiempo progresa y nos damos cuenta de nuevas observaciones, ingresamos sus valores actuales en el conjunto de datos (en lugar de #N/A), y Excel actualiza el calculo de todas las celdas de referencia (ej. Tabla de pronóstico).