Construção de uma matriz de correlação

O tópico desta edição foi inspirado por uma pergunta de apoio: como posso construir uma matriz de correlação para os retornos de meus N ativos de forma eficiente?

O NumXL vem com várias funções para calcular a correlação cruzada entre duas séries temporais, mas como podemos fazer isso para, digamos, 20 variáveis sem cálculos intermediários? Não se preocupe, o Excel tem algumas funções: MATCH(.) e INDEX(.), que podemos usar em nossas fórmulas para tornar a construção de uma matriz de correlação fácil e rápida. É exatamente isso que abordaremos nesta edição.

Em suma, queremos transformar a seguinte tabela de dados de entrada:

Nessa matriz de correlação:

Interessado? Vamos lá!

Preparação de dados

Neste tutorial, usaremos os retornos semanais logarítmicos de 16 ETFs para o período entre 28 de setembro de 2015 e 17 de fevereiro de 2020 (pouco antes da volatilidade epidêmica do mercado da COVID-19). Escolhemos os 16 ETFs para cobrir o mercado de ações dos EUA (por exemplo, S&P 500, Dow Jones Industrial Average, Russell 1000/2000/3000, etc.), o mercado de energia dos EUA (petróleo e gás natural), metais (ouro, prata e metais básicos industriais) e, por fim, o mercado de dívida ultracurta (dinheiro).

Em seguida, copiamos os retornos semanais de registro em uma planilha separada, cada um em uma coluna separada, mas alinhamos todos os ativos usando um campo de data comum (coluna A).

Agora, vamos definir um nome para o intervalo de células da tabela de dados:

• Selecione ou mude para a guia Formulas (Fórmulas)
• Selecione o intervalo de células de entrada e, em seguida, clique em "Definir nome"

• A caixa de diálogo "Definir nome" é exibida, com alguns campos pré-preenchidos (por exemplo, "Referir-se a" tem o intervalo de células da tabela de dados e o escopo está definido como "Pasta de trabalho").
• Altere o nome na caixa de diálogo "Define Name" (Definir nome) para algo significativo, por isso escolhemos "RETURNS" (Retornos)

• Clique em OK.

Vamos criar outro nome definido (por exemplo, SYMBOLS") para os símbolos de ativos (linha 2). Repita as mesmas etapas acima, mas para a linha superior com símbolos de ticker.

Agora, se você examinar o Name Manager, deverá ter o seguinte:

Matriz de correlação

Para uma matriz de correlação, cada linha ou coluna corresponde a um único ativo, portanto, definiremos os símbolos de ticker como cabeçalhos de coluna e linha de nossa matriz de correlação.

Dica: Você pode usar o recurso "transpor" em "Opções de colagem" para colar uma linha copiada (por exemplo, linha de símbolos de ticker) em uma coluna.

Referência a séries temporais

Na tabela de correlação, cada célula representa a correlação cruzada entre os retornos dos dois ativos: coluna e linha. Por exemplo, na figura abaixo, a célula cinza é a correlação entre o IWB e o DIA.

Agora, precisamos usar o símbolo do ticker para fazer referência à série temporal na tabela de dados:

Etapa 1: Converta o símbolo do ticker em um deslocamento numérico. Use a função MATCH(.) para avaliar o índice do ticker no nome definido SYMBOLS. Fazemos isso tanto para o cabeçalho da coluna quanto para o da linha. Por exemplo:

Etapa 2: Referenciar a série temporal correspondente

Use a função INDEX(.) para fazer referência a uma coluna, mas a todas as linhas, no nome definido RETURNS. Fazemos isso para o cabeçalho da coluna e da linha, mas usando seu deslocamento.

Etapa 3: Combinar as funções MATCH(.) e INDEX(.)

Etapa 4: Escolha sua função de correlação favorita, passe as duas séries temporais e armazene o valor retornado na tabela.

Etapa 5: Copie a fórmula acima para outras células (linhas e colunas) para calcular toda a matriz de correlação.

Conclusão

Neste tutorial, usamos as funções internas do Excel: MATCH(.) e INDEX(.), e construímos uma matriz de correlação sem nenhum cálculo intermediário, em apenas algumas etapas simples. Além disso, usamos o recurso "nomes definidos" para encapsular o conjunto de dados e simplificamos não apenas as fórmulas, mas também a manutenção dos dados de entrada: adicionando observações e/ou ativos.