Calculando medias móviles con Google Apps Script

La media móvil es un tipo de cálculo estadístico utilizado  frecuentemente cuando se desea analizar la evolución de una variable a lo largo del tiempo de manera que sus variaciones instantáneas queden suavizadas. 

Pero como el movimiento se demuestra andando, creo que lo mejor será que arranquemos este artículo con un ejemplo sencillo que demuestre cómo se calcularía de un modo inmediato la media móvil simple de una serie de datos usando una hoja de cálculo de Google, ¿no te parece?

La idea es extremadamente intuitiva. Al valor registrado (columna B) de cada elemento (columna A) de la serie de datos original se le asignará un valor calculado resultante de promediar las magnitudes registradas para sus últimos n puntos.

En nuestro ejemplo, la columna D contiene la serie obtenida usando una media móvil con n = 4, en tanto que en la C tenemos su homóloga con n = 2.  Las filas en blanco en C (2 - 1) y D (4 - 1) se corresponden con aquellos elementos de la serie original para los que no es posible realizar el cálculo dado que no se dispone para ellos de un histórico de valores anteriores lo suficientemente extenso.

Al subconjunto de datos que se utiliza para el cálculo de la media móvil de cada elemento de la serie original se le suele denominar ventana, una venta que es móvil (como la media) dado que se va desplazando hacia abajo ⬇️ a medida que recorre todos sus elementos.

Representemos ahora los valores de la serie, así como sus medias móviles, con respecto a un supuesto eje X secuencial.

Fíjate, el elemento en 6º posición (x = 6) de la serie original rompe la tendencia de crecimiento, mucho más moderada hasta el momento. Ese salto abrupto entre los valores 5º y 6º se ve notablemente amortiguado en la serie calculada mediante una media móvil con ventana 2. Y aún más en la de ventana 4. ¿Vas captando la idea?

Las medias móviles se utilizan a menudo como indicadores técnicos de la tendencia de los precios de activos financieros, aunque su uso se puede generalizar a otros ámbitos que requieran de la construcción de modelos predictivos sin demasiado esfuerzo.

ℹ️ En general, cuando nuestro objetivo sea minimizar el impacto de valores anormalmente altos o bajos, de acuerdo con un histórico previo, en el análisis que realizamos sobre una serie de datos, las medias móviles pueden resultarnos de ayuda.

¿Hay más medias móviles además de esta, que si no me equivoco he denominado en algún momento simple? Pues claro. Sin esforzarnos mucho podemos hablar de alguna que otra más:

• Central. En este caso se utilizan para el cálculo los (n - 1) / 2 datos anteriores y posteriores a cada elemento de la serie. Este tipo de media móvil requiere que la ventana sea de tamaño (n) impar.
• Acumulada. Es similar a la media simple, pero ahora se tienen en cuenta todos los valores previos del intervalo.
• Ponderada. Los valores del intervalo pierden peso en el cálculo a medida que envejecen, esto es, la influencia de un valor en la media móvil es inversamente proporcional a su antigüedad. Los valores más viejos son por tanto progresivamente menos relevantes.
• Exponencial. Similar a la anterior, pero en este caso la pérdida de relevancia sigue una ley exponencial.

Puedes leer una estupenda fundamentación matemática de todas ellas, incluida la simple, en este artículo de la socorrida Wikipedia, que enlazo en inglés no por esnobismo sino porque la versión en castellano es un poquito infumable. En cualquier caso, hay millones de recursos que explican en detalle eso de las medias móviles. Usa el buscador y quédate con el que mejor impresión te cause.

Pero volvamos a nuestra querida hoja de cálculo que, como puedes suponer, es un instrumento ideal para realizar este tipo de cosas. 

Si disfrutas buscando tesoros ocultos en las vastas planicies de las hojas de cálculo probablemente hayas torcido el morro, estupefacto, al ver cómo he calculado la media móvil en el ejemplo de hace un momento: una simplona fórmula con unos aún más simplones operadores aritméticos que debe ser simplonamente arrastrada verticalmente en toda la extensión de la serie de datos (ya advertí que era un cálculo inmediato). Esto... ¿y no podríamos utilizar expresiones matriciales para ahorrarnos eso de arrastrar, que es muy cansado? Pues claro.

Y esta pregunta me lleva a un tuit no muy remoto del siempre inspirador Martin Hawksey. Este, concretamente:

Y no te pierdas la respuesta de Ben Collins, poco más abajo, que como siempre nos revela algún misterio iniciático relacionado con las hojas de cálculo (en este caso la revelación tenía que ver con la función INDICE).

Efectivamente, amigo o amiga, las hojas de cálculo son una herramienta increíble. Con ellas se puede hacer casi cualquier perrería. La pregunta es ¿a qué coste?

Y hablando de hojas de cálculo en este tono, no es la primera vez que menciono algo que a mí me gusta llamar explosión de complejidad 🌋. Esta explosión de complejidad surge cuando parimos fórmulas tan complejas que se convierten en desconocidas incluso para nosotros mismos con el paso del tiempo. Y eso dificulta su uso y mantenimiento, no te quepa la menor duda.

Y es que aunque en las hojas de cálculo es posible realizar cálculos que supongan procesos iterativos, de esos que se resolverían con un sencillo bucle en cualquier lenguaje de programación imperativo, las expresiones resultantes pueden llegar a ser tan pero tan diabólicamente complejas o simplemente presentar tal grado de ofuscación... que quizás merezca la pena tratar de buscar alternativas.

Y en el mundo de las hojas de cálculo de Google la alternativa es en estos momentos Google Apps Script.

Por esa razón me he puesto manos a la obra y he preparado MEDIAMOVIL(), una función personalizada GAS capaz de calcular varios tipos de medias móviles, que puede ser utilizada (como todas las funciones personalizadas) de manera combinada con el resto de funciones integradas que nos ofrecen las hojas de cálculo de Google.

Para demostrar su uso, aquí tienes esta hoja de cálculo, en la que se recogen los positivos de Covid_19 registrados en España entre el 20 de febrero y el 20 de mayo de 2020 (datos facilitados por el Instituto de Salud Carlos III). A partir de esta serie de datos se calculan y representan diversas medias con un tamaño de ventana variable para comprobar de qué modo cambia la visualización en función del tipo de media móvil calculada (reproduce el vídeo a pantalla completa para apreciarlo adecuadamente).

También puedes hacerte una copia de la hoja de cálculo que se muestra en el vídeo y jugar tranquilamente con ella en la intimidad de tu sheetcueva :

👉🏻 fx MEDIAMOVIL # demo 👈🏻

Encontrarás un pequeño manual de uso de MEDIAMOVIL(), así como una descripción tećnica de su implementación en este repositorio de GitHub.

Y nada más, por hoy. Tan solo me gustaría decir que me he pasado un rato de lo más entretenido escribiendo estas líneas (y la documentación del repo GitHub). El tiempo pasa volando cuando disfrutas. Este fin de curso tan endemoniadamente complicado 🦠 me ha tenido hasta hace nada apartado de mis scripts, mis hojas de cálculo, mis paneles Data Studio, mi querida comunidad de GEG Spain y... sí, este blog. Espero poder dedicarles a partir de ahora el tiempo que se merecen.