Matriz de covariância em Excel

Matriz de covariância em excel

A matriz de covariância é uma matriz quadrada para mostrar a covariância entre as colunas e a variância nas colunas. Excel apresentou a ferramenta embutida de 'Análise de dados' para determinar a covariância entre os diferentes conjuntos de dados. O presente artigo irá explicar o cálculo da matriz de covariância no Excel, cobrindo os seguintes tópicos, incluindo

Explicação

Covariância é uma das medidas usadas para entender como uma variável está associada a outra variável. A fórmula a seguir é usada para a determinação da covariância.

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

A matriz de covariância é uma matriz quadrada para entender as relações apresentadas entre as diferentes variáveis ​​em um conjunto de dados. É fácil e útil mostrar a covariância entre duas ou mais variáveis.

A covariância terá valores positivos e negativos. Um valor positivo indica que duas variáveis ​​diminuirão ou aumentarão na mesma direção. Um valor negativo indica que se uma variável diminui, outra variável aumenta e existe uma relação inversa entre elas. A matriz de covariância é representada no seguinte formato. A matriz de covariância tridimensional é mostrada como

Para criar a matriz de covariância quadrada 3 × 3, precisamos ter dados tridimensionais. Os valores diagonais da matriz representam as variâncias das variáveis ​​X, Y e Z (ou seja, COV (X, X), COV (Y, Y) e COV (Z, Z)). A matriz de covariância é simétrica em relação à diagonal. Isso indica que COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) e COV (Y, Z) = COV (Z, Y). Um ponto a lembrar sobre essa matriz é o resultado da matriz de covariância NXN para dados de n-dimensional.

Como usar uma matriz de covariância no Excel?

A matriz de covariância é usada em várias aplicações, incluindo

  • Analisando como dois vetores se diferenciam um do outro
  • Usado no aprendizado de máquina para determinar os padrões de dependência entre os dois vetores
  • A matriz de covariância é usada para dizer a relação entre as diferentes dimensões das variáveis ​​aleatórias
  • Usado em modelagem estocástica em engenharia financeira para correlacionar as variáveis ​​aleatórias
  • O componente principal é outra aplicação da matriz de covariância para variáveis ​​originais para variáveis ​​independentes lineares
  • Na análise de dados, a matriz de covariância tem um papel vital.
  • A matriz de covariância é utilizada na moderna teoria de portfólio na estimativa de riscos.
  • As medidas da matriz de covariância são utilizadas na antecipação dos retornos dos ativos financeiros

Exemplos de matriz de covariância no Excel

Abaixo estão alguns dos exemplos de uso da matriz de covariância no Excel.

Você pode baixar este modelo de matriz de covariância em Excel aqui - modelo de matriz de covariância em Excel

Exemplo 1

Realização de análise de covariância nas notas obtidas pelos alunos nas diferentes disciplinas.

Passo 1: Os dados a seguir, incluindo as notas dos alunos em Matemática, Inglês e Ciências, são considerados conforme mostrado na figura.

Etapa 2: Vá para a guia “Dados” na faixa de opções e encontre o ToolPak de 'Análise de dados' no canto direito.

Se o ToolPak de “Análise de dados” não estiver disponível, siga estas etapas

Etapa A: Vá para a guia 'Arquivo' e selecione as “opções”.

A seguinte tela será aberta.

Etapa B: vá para Add-ins. Na opção Gerenciar, certifique-se de que 'Suplementos do Excel' esteja selecionado e selecione o botão 'Ir' conforme mostrado na figura.

Etapa C: Selecione “Analysis-Tool Pak” e “Analysis-ToolPak VBA” como mostrado na captura de tela.

Depois de concluir essas etapas, o pacote de ferramentas “Análise de dados” é adicionado à guia 'Dados'.

Etapa 3: Clique em Análise de dados. Isso abre a caixa de diálogo “Análise de dados”. Selecione “Covariância” rolando para cima e clique em “OK”.

Ele exibe a caixa de diálogo “Covariância”.

Etapa 5:  Selecione o intervalo de entrada incluindo os nomes dos assuntos, verifique os “rótulos na primeira linha” e forneça o “intervalo de saída” na planilha existente. E clique em “OK”.

Etapa 6:  obteremos a saída da seguinte forma -

A parte superior da diagonal está vazia porque a matriz de covariância do Excel é simétrica em relação à diagonal.

Exemplo # 2

Cálculo da matriz de covariância para determinar as variações entre os retornos de diferentes ações da carteira.

Etapa 1: para este exemplo, os dados a seguir, incluindo os retornos das ações, são considerados.

Passo 2: Abre a caixa de diálogo “Data Analysis” e selecione “Covariância” rolando para cima e clique em “OK”.

Ele exibe a caixa de diálogo “Covariância”.

Etapa 3: Selecione o intervalo de entrada incluindo os cabeçalhos, verifique os “rótulos na primeira linha” e forneça o “intervalo de saída” na planilha existente. E clique em “OK”.

Etapa 4: obteremos a saída da seguinte forma -

A parte superior da diagonal está vazia porque a matriz de covariância é simétrica em relação à diagonal.

Exemplo # 3

Cálculo da matriz de covariância para preços de ações de empresas corporativas

Etapa 1: para este exemplo, os dados a seguir, incluindo preços de ações de diferentes empresas, são considerados.

Passo 2: Abre a caixa de diálogo “Análise de Dados”, selecione “Covariância” rolando para cima e clique em “OK”.

Ele exibe a caixa de diálogo “Covariância”.

Passo 3: Selecione o intervalo de entrada incluindo os cabeçalhos, marque os “rótulos na primeira linha” e forneça o “intervalo de saída” na planilha existente e clique em “OK”.

Etapa 4: obteremos a saída da seguinte forma -

Coisas para lembrar

  • A ferramenta de covariância apresentada pelo Excel tem algumas limitações, incluindo a determinação de apenas fórmulas de variâncias da população, criação de matriz apenas com valores diagonais mais baixos e consideração de fórmulas apenas para uma variância.
  • Quando os valores de retorno são alterados, ele não atualiza automaticamente os valores da matriz.
  • A metade superior da matriz é exibida como vazia, pois é simétrica e os valores da imagem espelhada são exibidos na diagonal inferior.