Développer un outil d’analyse d’investissement personnalisé, Excel VBA
Voici un code VBA détaillé pour développer un outil d’analyse d’investissement personnalisé dans Excel. L’objectif de cet outil est de calculer des mesures courantes utilisées dans l’analyse d’investissements, telles que les rendements, la volatilité, le ratio de Sharpe et l’analyse de portefeuille.
Vue d’ensemble de l’outil d’analyse d’investissement
L’outil sera composé des étapes suivantes :
1. Importation des données : Importer les prix historiques des actions.
2. Calcul des rendements et risques : Calculer les rendements moyens, la volatilité et le ratio de Sharpe.
3. Analyse du portefeuille : Allouer des poids et calculer le rendement et le risque du portefeuille.
4. Visualisation : Créer des graphiques simples pour afficher la performance.
5. Rapport automatique : Générer un rapport résumant les résultats de l’analyse.
Étape par Étape : Code VBA et Explication
Avant de commencer, il est important que vous disposiez d’un jeu de données dans le format suivant dans votre feuille Excel :
| Date | Action A | Action B | Action C |
|————|———-|———-|———-|
| 01/01/2020 | 100 | 150 | 200 |
| 02/01/2020 | 102 | 152 | 198 |
| … | … | … | … |
Ensuite, le code VBA suivant va traiter ces données et générer les analyses nécessaires.
Code VBA :
Sub OutilAnalyseInvestissement()
' Déclaration des variables pour les données et les résultats
Dim wsData As Worksheet
Dim wsResults As Worksheet
Dim lastRow As Long
Dim stockCount As Integer
Dim stockReturns() As Double
Dim stockPrices() As Double
Dim weights() As Double
Dim portfolioReturn As Double
Dim portfolioRisk As Double
Dim sharpeRatio As Double
Dim riskFreeRate As Double
Dim i As Integer
Dim j As Integer
Dim covarianceMatrix() As Double
Dim correlationMatrix() As Double
Dim portfolioVariance As Double
' Taux sans risque (par exemple, 2% par an)
riskFreeRate = 0.02
' Définir les feuilles de données et de résultats
Set wsData = ThisWorkbook.Sheets("Données")
Set wsResults = ThisWorkbook.Sheets("Résultats")
' Trouver la dernière ligne de données dans la feuille 'Données'
lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
' Déterminer le nombre d'actions
stockCount = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column - 1 ' Exclure la colonne des dates
' Redimensionner les tableaux pour stocker les données
ReDim stockPrices(1 To lastRow - 1, 1 To stockCount)
ReDim stockReturns(1 To lastRow - 1, 1 To stockCount)
ReDim covarianceMatrix(1 To stockCount, 1 To stockCount)
ReDim correlationMatrix(1 To stockCount, 1 To stockCount)
ReDim weights(1 To stockCount)
' Importer les données des actions dans les tableaux
For i = 2 To lastRow
For j = 1 To stockCount
stockPrices(i - 1, j) = wsData.Cells(i, j + 1).Value ' Exclure la colonne des dates
Next j
Next i
' Calculer les rendements quotidiens pour chaque action
For i = 1 To lastRow - 2
For j = 1 To stockCount
stockReturns(i, j) = (stockPrices(i + 1, j) - stockPrices(i, j)) / stockPrices(i, j)
Next j
Next i
' Calculer la matrice de covariance et de corrélation
For i = 1 To stockCount
For j = 1 To stockCount
' Covariance
covarianceMatrix(i, j) = WorksheetFunction.Covar(wsData.Range(wsData.Cells(2, i + 1), wsData.Cells(lastRow, i + 1)), _
wsData.Range(wsData.Cells(2, j + 1), wsData.Cells(lastRow, j + 1)))
' Corrélation
correlationMatrix(i, j) = WorksheetFunction.Correl(wsData.Range(wsData.Cells(2, i + 1), wsData.Cells(lastRow, i + 1)), _
wsData.Range(wsData.Cells(2, j + 1), wsData.Cells(lastRow, j + 1)))
Next j
Next i
' Poids du portefeuille (on suppose des poids égaux pour simplifier)
For i = 1 To stockCount
weights(i) = 1 / stockCount ' Poids égaux pour chaque action
Next i
' Calculer le rendement et le risque du portefeuille (écart-type)
portfolioReturn = 0
portfolioVariance = 0
For i = 1 To stockCount
portfolioReturn = portfolioReturn + weights(i) * WorksheetFunction.Average(wsData.Range(wsData.Cells(2, i + 1), wsData.Cells(lastRow, i + 1)))
Next i
For i = 1 To stockCount
For j = 1 To stockCount
portfolioVariance = portfolioVariance + weights(i) * weights(j) * covarianceMatrix(i, j)
Next j
Next i
portfolioRisk = Sqr(portfolioVariance)
' Calculer le ratio de Sharpe
sharpeRatio = (portfolioReturn - riskFreeRate) / portfolioRisk
' Afficher les résultats dans la feuille 'Résultats'
wsResults.Cells(1, 1).Value = "Rendement du portefeuille"
wsResults.Cells(1, 2).Value = portfolioReturn
wsResults.Cells(2, 1).Value = "Risque du portefeuille (Écart-type)"
wsResults.Cells(2, 2).Value = portfolioRisk
wsResults.Cells(3, 1).Value = "Ratio de Sharpe"
wsResults.Cells(3, 2).Value = sharpeRatio
' Afficher la matrice de covariance
For i = 1 To stockCount
For j = 1 To stockCount
wsResults.Cells(5 + i, 1 + j).Value = covarianceMatrix(i, j)
Next j
Next i
' Afficher la matrice de corrélation
For i = 1 To stockCount
For j = 1 To stockCount
wsResults.Cells(5 + stockCount + i, 1 + j).Value = correlationMatrix(i, j)
Next j
Next i
' Créer un graphique simple pour la visualisation de la performance du portefeuille
Dim chartObj As ChartObject
Set chartObj = wsResults.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
chartObj.Chart.ChartType = xlLine
chartObj.Chart.SetSourceData Source:=wsResults.Range("A1:B3")
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Performance du portefeuille"
End Sub
Explication détaillée du code :
1. Importation des données :
– Nous importons les prix des actions historiques à partir de la feuille Données, en excluant la colonne des dates. Les données des actions sont stockées dans un tableau stockPrices.
2. Calcul des rendements :
– Les rendements quotidiens de chaque action sont calculés en utilisant la formule suivante :
Rendement = Pt – Pt-1/Pt-1
où Pt est le prix au jour et Pt-1est le prix au jour précédent. Ce calcul est effectué pour chaque action et stocké dans le tableau stockReturn.
3. Calcul de la matrice de covariance et de corrélation :
– La matrice de covariance est calculée pour évaluer comment les rendements des actions varient ensemble, ce qui est essentiel pour l’analyse du risque d’un portefeuille.
– La matrice de corrélation est également calculée pour comprendre les relations linéaires entre les actions.
4. Analyse du portefeuille :
– Les poids du portefeuille sont supposés égaux par défaut, mais vous pouvez les modifier pour ajuster la stratégie d’allocation d’actifs.
– Le rendement du portefeuille est la moyenne pondérée des rendements des actions.
– Le risque du portefeuille (ou volatilité) est calculé à partir de la variance du portefeuille, qui est dérivée de la matrice de covariance.
5. Ratio de Sharpe :
– Le ratio de Sharpe mesure le rendement ajusté au risque du portefeuille. Il est calculé comme suit :
Ratio de Sharpe = (Rendement du portefeuille – Taux sans risque) / Risque du portefeuille
– Un ratio de Sharpe plus élevé indique une meilleure performance ajustée au risque.
6. Affichage des résultats :
– Les résultats (rendement du portefeuille, risque, ratio de Sharpe) sont affichés dans la feuille `Résultats`.
– La matrice de covariance et la matrice de corrélation sont également affichées.
– Un graphique simple est généré pour visualiser la performance du portefeuille.
Personnalisation :
– Taux sans risque: Le taux sans risque est fixé à 2 % dans le code, mais vous pouvez l’ajuster en fonction de vos besoins.
– Poids du portefeuille : Actuellement, les poids sont égaux pour chaque action, mais vous pouvez les modifier selon votre stratégie d’investissement.
– Graphique : Un graphique simple est créé pour illustrer la performance du portefeuille, mais vous pouvez l’améliorer en ajoutant des graphiques supplémentaires pour la performance individuelle des actions.
Conclusion :
Ce code VBA vous permet de développer un outil d’analyse d’investissement robuste dans Excel, que vous pouvez personnaliser en fonction de vos besoins spécifiques. Vous pouvez étendre cet outil avec d’autres fonctionnalités comme les simulations de Monte Carlo pour les prévisions de portefeuille ou des optimisations pour l’allocation d’actifs.