Construyendo una matriz de correlación

El tema de este número estuvo inspirado en la siguiente consulta que recibió nuestro soporte técnico: ¿cómo puedo construir una matriz de correlación para los rendimientos de mis activos N de forma eficiente?

NumXL viene con numerosas funciones para calcular la correlación cruzada entre dos series de tiempo, pero ¿cómo podemos hacerlo para, digamos 20 variables, sin ningún cálculo intermedio? No hay problema, Excel tiene unas cuantas funciones: MATCH(.) e INDEX(.), que usamos en nuestras fórmulas para construir una matriz de correlación fácil y rápida. Esto es exactamente lo que cubriremos en este número.  

En resumen queremos transformar la siguiente tabla de datos de entrada:

Tabla de datos de entrada con devoluciones semanales de 16 ETFs

En esta matriz de correlación:

Matriz de Correlación

¿Les parece interesante? ¡Hagámoslo!

Preparación de Datos

En este tutorial usaremos las devoluciones semanales logarítmicas de 16ETFs para el período comprendido entre septiembre 28 de 2015 a febrero 17 de 2020 (justo antes de la volatilidad del mercado propia de la epidemia COVID 19). Hemos escogido los 16ETFs para cubrir la equidad del mercado norteamericano. (ej., S&P 500, Promedio Industrial del Dow Jones, Russell 1000/2000/3000, etc.), el mercado de Energía estadounidense (Petróleo y Gas Natural), Metales ( Oro, plata y metales básicos industriales) y, finalmente, el mercado de deuda ultracorta (efectivo).

Ahora definamos un nombre para el rango de celdas de la tabla de datos:

  • Seleccione o cámbiese a la pestaña de fórmulas 
  • Seleccione el rango de celdas de entrada y luego haga clic en “Definir Nombre”

Definiendo un nombre para el rango de celdas

  • El diálogo de “Definir Nombre” aparece con unos cuantos campos llenos de antemano, ej., “Referir a” tiene el rango de las celdas de la tabla de datos, y el alcance se establece en “Workbook”.
  • Cambiar el nombre en el cuadro de diálogo “Definir el nombre” por algo significativo, de manera que escogimos “DEVOLUCIONES”.

Definiendo un nombre para el rango de celdas

  • Haga clic en OK.

Creemos otro nombre definido (digamos, SÍMBOLOS) para los teletipos de símbolos de activos (fila 2). Repita los mismos pasos como anteriormente pero para la fila superior con símbolos bursátiles.

Ahora, si examinamos el administrador de nombres, debería tener lo siguiente:

Administrador de Nombres

Matriz de correlación

Para una matriz de correlación, cada fila o cada columna corresponde a un solo activo, de manera que estableceremos los símbolos bursátiles como los encabezados de columna y fila para nuestra matriz de correlación.

estableciendo los símbolos bursátiles como los encabezados de columna y fila

 Sugerencia: Puede utilizar la función “Transponer” en “Opciones de pegado”, para pegar una fila copiada (por ejemplo una fila de símbolos bursátiles) en una columna.

Usando la función de transposición para  pegar la fila pegada en la columna

Referenciación de Series Temporales 

En la tabla de correlación, cada celda representa la correlación cruzada entre  los dos retornos de activos: columna y fila. Por ejemplo, en la figura que vemos a continuación, la celda gris es la correlación entre IWB y DIA.

Cada celda representa la correlación cruzada entre los dos retornos de activos: columna y fila.

Ahora necesitamos usar el símbolo bursátil para hacer referencia a las series de tiempo en la tabla de datos:

Paso 1: Convertir el símbolo bursátil en un intervalo numérico. Usar la función MATCH(.) para evaluar el índice del símbolo bursátil en el nombre definido SÍMBOLOS/SYMBOLS. Hacer esto tanto para los encabezados de columna como para las filas. Ejemplo:

MATCH("DIA",  SYMBOLS,  0) = 2

MATCH(“IWB”,  SYMBOLS,  0) =  3

Paso 2: Hacer referencia a las series de tiempo correspondientes.

Usar la función INDEX(.)para hacer referencia a una columna, pero a todas las filas con el nombre definido RETURNS. Haremos esto tanto para el encabezado de las columnas como para las filas usando su intervalo. 

INDEX ( RETURNS, , 3) = Series de tiempo IWB

INDEX ( RETURNS, , 2 ) = Series de tiempo DIA 

Paso 3: Combine la función MATCH(.) con la función  INDEX(.)

INDEX (RETURNS , ,MATCH (“IWB”,  SYMBOLS, 0)) = series de tiempo IWB 

INDEX (RETURNS , ,MATCH (“DIA”,  SYMBOLS, 0)) = series de tiempo DIA 

Paso 4:  Escoja su función de correlación favorita, pase las dos series de tiempo y almacene el valor devuelto en la tabla.

Escoja su función de correlación favorita, pase las dos series de tiempo y almacene el valor devuelto en la tabla

Paso 5: Copie la fórmula que está encima en otras celdas (filas y columnas) para calcular toda la matriz de correlación. 

Matriz de Correlación

Conclusión

En este tutorial hemos usado las funciones integradas de excel: MATCH(.) e INDEX(.), y hemos construido una matriz de correlación sin ningún cálculo intermedio, en tan sólo unos simples pasos. Adicionalmente, hemos usado el rasgo “Definir nombres” para encapsular el conjunto de datos y simplificar, no sólo las fórmulas, sino el mantenimiento de los datos de entrada; también adicionando observaciones y/o activos.  

  Archivos adjuntos

Comentarios

Inicie sesión para dejar un comentario.

¿Fue útil este artículo?
Usuarios a los que les pareció útil: 0 de 1