Construire une matrice de corrélation

Le thème de ce numéro a été inspiré par une demande de soutien : comment puis-je construire efficacement une matrice de corrélation pour les rendements de mes N actifs ?

NumXL offre de nombreuses fonctions permettant de calculer la corrélation croisée entre deux séries chronologiques, mais comment faire pour, disons, 20 variables sans calculs intermédiaires ? Pas de panique, Excel dispose de quelques fonctions : MATCH(.) et INDEX(.), que nous pouvons utiliser dans nos formules pour créer facilement et rapidement une matrice de corrélation. C'est exactement ce que nous allons voir dans ce numéro.

En résumé, nous souhaitons transformer le tableau de données d'entrée suivant :

dans cette matrice de corrélation :

Intéressé(e) ? C'est parti !

Préparation des données

Dans ce tutoriel, nous utiliserons les rendements hebdomadaires logarithmiques de 16 ETF pour la période comprise entre le 28 septembre 2015 et le 17 février 2020 (juste avant l'épidémie de volatilité des marchés COVID-19). Nous avons choisi les 16 ETF pour couvrir le marché des actions américaines (par exemple, S&P 500, Dow Jones Industrial Average, Russell 1000/2000/3000, etc.), le marché de l'énergie américain (pétrole et gaz naturel), les métaux (or, argent et métaux de base industriels) et, enfin, le marché de la dette ultra-courte (liquidités).

Ensuite, nous avons copié le journal des rendements hebdomadaires dans une feuille de calcul séparée, chacun dans une colonne distincte, mais en alignant tous les actifs à l'aide d'un champ de date commun (colonne A).

Définissons maintenant un nom pour la plage de cellules du tableau de données :

• Sélectionnez ou passez à l'onglet Formules
• Sélectionnez la plage de cellules d'entrée, puis cliquez sur "Définir le nom"

• La boîte de dialogue "Define Name" s'ouvre, avec quelques champs pré-remplis (par exemple, "Refer to" contient la plage de cellules du tableau de données et la portée est définie sur "Workbook").
• Modifiez le nom dans la boîte de dialogue "Définir le nom" pour qu'il soit significatif, nous avons choisi "RETOURS"

• Cliquez sur OK.

Créons un autre nom défini (par exemple, SYMBOLS") pour les symboles d'actifs (ligne 2). Répétez les mêmes étapes que ci-dessus, mais pour la ligne supérieure contenant les symboles de téléscripteur.

Maintenant, si vous examinez le gestionnaire de noms, vous devriez obtenir ce qui suit :

Matrice de corrélation

Pour une matrice de corrélation, chaque ligne ou chaque colonne correspond à un seul actif. Nous définirons donc les symboles des téléscripteurs comme en-têtes de colonne et de ligne de notre matrice de corrélation.

Hint: Vous pouvez utiliser la fonction "transposer" dans "Options de collage" pour coller une ligne copiée (par exemple, la ligne des symboles de téléscripteur) dans une colonne.

Référencement des séries chronologiques

Dans le tableau de corrélation, chaque cellule représente la corrélation croisée entre les rendements des deux actifs : la colonne et la ligne. Par exemple, dans la figure ci-dessous, la cellule grise représente la corrélation entre IWB et DIA.

Nous devons maintenant utiliser le symbole du téléscripteur pour référencer la série temporelle dans le tableau de données :

Étape 1: Convertir le symbole du téléscripteur en un décalage numérique. Utilisez la fonction MATCH(.) pour évaluer l'index du ticker dans le nom défini par SYMBOLS. Nous procédons ainsi pour l'en-tête de colonne et l'en-tête de ligne. Par exemple :

Étape 2: Référence de la série temporelle correspondante

Utilisez la fonction INDEX(.) pour référencer une colonne, mais toutes les lignes, dans le nom défini RETURNS. Nous le faisons à la fois pour l'en-tête de la colonne et de la ligne, mais en utilisant son décalage.

Étape 3: Combiner les fonctions MATCH(.) et INDEX(.)

Étape 4: Choisissez votre fonction de corrélation préférée, passez les deux séries temporelles et stockez la valeur retournée dans le tableau.

Étape 5: Copiez la formule ci-dessus dans d'autres cellules (lignes et colonnes) pour calculer l'ensemble de la matrice de corrélation.

Conclusion

Dans ce tutoriel, nous avons utilisé les fonctions intégrées d'Excel : MATCH(.) et INDEX(.), et nous avons construit une matrice de corrélation sans aucun calcul intermédiaire, en quelques étapes simples. En outre, nous avons utilisé la fonction "noms définis" pour encapsuler l'ensemble de données et simplifier non seulement les formules, mais aussi la maintenance des données d'entrée : ajout d'observations et/ou d'actifs.