Matriz de covarianza en Excel

Matriz de covarianza en Excel

La matriz de covarianza es una matriz cuadrada para mostrar la covarianza entre las columnas y la varianza en las columnas. Excel se presentó con una herramienta de 'análisis de datos' incorporada para determinar la covarianza entre los diferentes conjuntos de datos. El presente artículo explicará el cálculo de la matriz de covarianza en Excel cubriendo los siguientes temas que incluyen

Explicación

La covarianza es una de las medidas que se utilizan para comprender cómo se asocia una variable con otra variable. La siguiente fórmula se utiliza para la determinación de la covarianza.

COV (X, Y) = ∑ (x - x) (y - y) / n

La matriz de covarianza es una matriz cuadrada para comprender las relaciones presentadas entre las diferentes variables en un conjunto de datos. Es fácil y útil mostrar la covarianza entre dos o más variables.

La covarianza tendrá valores tanto positivos como negativos. Un valor positivo indica que dos variables disminuirán o aumentarán en la misma dirección. Un valor negativo indica que si una variable disminuye, otra variable aumenta y existe una relación inversa entre ellas. La matriz de covarianza se representa en el siguiente formato. La matriz de covarianza tridimensional se muestra como

Para crear la matriz de covarianza cuadrada de 3 × 3, necesitamos tener datos tridimensionales. Los valores diagonales de la matriz representan las varianzas de las variables X, Y y Z (es decir, COV (X, X), COV (Y, Y) y COV (Z, Z)). La matriz de covarianza es simétrica con respecto a la diagonal. Esto indica que COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) y COV (Y, Z) = COV (Z, Y). Un punto para recordar acerca de esta matriz es el resultado de la matriz de covarianza NXN para datos de n dimensiones.

¿Cómo usar una matriz de covarianza en Excel?

La matriz de covarianza se utiliza en varias aplicaciones que incluyen

  • Analizar cómo se diferencian dos vectores entre sí
  • Se utiliza en el aprendizaje automático para determinar los patrones de dependencia entre los dos vectores.
  • La matriz de covarianza se utiliza para determinar la relación entre las diferentes dimensiones de las variables aleatorias.
  • Utilizado en modelos estocásticos en ingeniería financiera para correlacionar las variables aleatorias
  • El componente principal es otra aplicación de la matriz de covarianza a las variables originales a las variables lineales independientes
  • En el análisis de datos, la matriz de covarianza tiene un papel vital.
  • La matriz de covarianza se utiliza en la teoría moderna de carteras en la estimación de riesgos.
  • Las medidas de la matriz de covarianza se utilizan para anticipar los rendimientos de los activos financieros.

Ejemplos de matriz de covarianza en Excel

A continuación se muestran algunos de los ejemplos para usar la matriz de covarianza en Excel.

Puede descargar esta plantilla de Excel de matriz de covarianza aquí - Plantilla de Excel de matriz de covarianza

Ejemplo 1

Realizar análisis de covarianza sobre las notas obtenidas por los alumnos en diferentes materias.

Paso 1: Los siguientes datos, incluidas las calificaciones de los estudiantes en Matemáticas, Inglés y Ciencias, se consideran como se muestra en la figura.

Paso 2: Vaya a la pestaña "Datos" en la cinta y busque el paquete de herramientas "Análisis de datos" en la esquina derecha.

Si no está disponible ToolPak "Análisis de datos", siga estos pasos

Paso A: Vaya a la pestaña 'Archivo' y luego seleccione las "opciones".

Se abrirá la siguiente pantalla.

Paso B: vaya a Complementos. En la opción Administrar, asegúrese de que 'Complementos de Excel' esté seleccionado y seleccione el botón 'Ir' como se muestra en la figura.

Paso C: Seleccione "Analysis-Tool Pak" y "Analysis-ToolPak VBA" como se muestra en la captura de pantalla.

Después de completar estos pasos, el paquete de herramientas "Análisis de datos" se agrega a la pestaña "Datos".

Paso 3: haga clic en Análisis de datos. Esto abre el cuadro de diálogo "Análisis de datos". Seleccione la "Covarianza" desplazándose hacia arriba y haga clic en "Aceptar".

Muestra el cuadro de diálogo "Covarianza".

Paso 5:  Seleccione el rango de entrada, incluidos los nombres de los sujetos, marque las "etiquetas en la primera fila" y proporcione el "rango de salida" en la hoja de trabajo existente. Y haga clic en "Aceptar".

Paso 6:  Obtendremos el resultado de la siguiente manera:

La parte superior de la diagonal está vacía ya que la matriz de covarianza de Excel es simétrica hacia la diagonal.

Ejemplo # 2

Cálculo de matriz de covarianza para determinar varianzas entre los rendimientos de diferentes acciones de cartera.

Paso 1: Para este ejemplo, se consideran los siguientes datos, incluidas las devoluciones de acciones.

Paso 2: Abre el cuadro de diálogo "Análisis de datos" y selecciona "Covarianza" desplazándote hacia arriba y haciendo clic en "Aceptar".

Muestra el cuadro de diálogo "Covarianza".

Paso 3: Seleccione el rango de entrada, incluidos los encabezados, verifique las "etiquetas en la primera fila" y proporcione el "rango de salida" en la hoja de trabajo existente. Y haga clic en "Aceptar".

Paso 4: Obtendremos el resultado de la siguiente manera:

La parte superior de la diagonal está vacía ya que la matriz de covarianza es simétrica hacia la diagonal.

Ejemplo # 3

Cálculo de matriz de covarianza para cotizaciones de acciones de empresas corporativas

Paso 1: Para este ejemplo, se consideran los siguientes datos, incluidos los precios de las acciones de diferentes empresas.

Paso 2: Abre el cuadro de diálogo "Análisis de datos", seleccione "Covarianza" desplazándose hacia arriba y haga clic en "Aceptar".

Muestra el cuadro de diálogo "Covarianza".

Paso 3: Seleccione el rango de entrada, incluidos los encabezados, marque las "etiquetas en la primera fila" y proporcione el "rango de salida" en la hoja de trabajo existente y haga clic en "Aceptar".

Paso 4: Obtendremos el resultado de la siguiente manera:

Cosas para recordar

  • La herramienta de covarianza presentada por Excel tiene algunas limitaciones, incluida la determinación de solo la fórmula de varianzas de población, la creación de una matriz con solo valores diagonales más bajos y la consideración de fórmulas solo para una varianza.
  • Cuando se cambian los valores de retorno, no actualiza automáticamente los valores de la matriz.
  • La mitad superior de la matriz se muestra vacía ya que es simétrica y los valores de la imagen especular se muestran en la diagonal inferior.