Insertar datos vacíos en serie temporal cuando faltan fechas (Excel)

Trabajando hoy con una serie temporal en Excel me he encontrado con un problema: quería calcular la correlación entre dos series temporales de variables, pero en una de las series temporales no aparecían todas las fechas. En vez de tener la fecha y un valor vacío cuando no existía ese valor (por ejemplo, un NaN, un espacio en blanco, o un clásico -999), simplemente el fichero de datos saltaba a la siguiente fecha donde sí había dato. Eso impedía que pudiera calcular la correlación con la otra serie temporal, porque para eso necesitamos todos los pares de datos (todos los valores para una misma fecha).

Para solucionar el problema lo único que se me ocurrió fue intentar rellenar esa serie temporal con las fechas que faltaban, y en esas fechas dejar vacío el dato. Lo mejor en estos casos no es trabajar en Excel, pero ya tenía todos los datos ahí y no me quedaba otro remedio. Publico aquí la solución de cómo lo conseguí tras buscar mucho en internet y probar incluso programando macros. Al final todo se ha reducido a poco más que una función de Excel.

Voy a poner un ejemplo para situarnos mejor. Imagina que tenemos esta serie temporal:

Vemos que a partir del 2/10/04 la fecha salta al 13/10/04, luego al 15/10/04 y así varias veces hay saltos de fecha. Lo que quiero conseguir es que aparezcan todas las fechas entre el 1/10/04 y el 31/10/04 y, donde antes no había fecha (ni dato, obviamente), ahora aparezca una casilla en blanco como dato. Vamos a suponer que esto lo tengo en las columnas A y B.

Lo primero que hice fue crear una nueva columna con todas las fechas, sin saltos. Para ello escribí en otra columna (supongamos que en la C) la primera fecha y, situando el ratón en la esquina inferior derecha de esa casilla, arrastré hacia abajo hasta llegar a la última fecha. Esa es una manera de rellenar casillas que usa Excel.

Una vez tenía una columna con todas las fechas (en la columna C), en otra columna (por ejemplo, la D) escribo esta fórmula en la casilla donde iría el primer dato:

=SI.ERROR(BUSCARV(C2;A:B;2;FALSO);» «)

Esta fórmula lo que hace es buscar en todos los datos de las columnas A y B la fecha que tenemos en la casilla correspondiente (la de la fila en que estemos situados) de la casilla C. Si encuentra una fecha igual, escribe el valor del dato asociado a ella. Si no encuentra esa fecha en nuestra serie de datos originales (en la que faltaban fechas), deja la casilla vacía.Fíjate en que busca a partir de la fila 2, porque la fila 1 la he dedicado a etiquetar las columnas. Si en tu caso no usas etiquetas o tus datos empiezan en otra fila, tenlo en cuenta para modificar la fórmula. También debes modificar la fórmula, por supuesto, si los nombres de tus columnas no coinciden con los míos.

El resultado sería este:

Ahora sí podría usar la columna Datos para calcular la correlación con otra variable que se encuentra asociada a cada una de esas fechas.

Si tienes que hacer lo mismo con datos en otra columna, simplemente arrastra la casilla donde esté la primera fórmula a la casilla correspondiente de esa otra columna.

Espero que si te encuentras con este problema, una serie temporal en la que faltan fechas y tienes que trabajar con Excel, llegues hasta aquí y te sirva de ayuda.