Matrice Variance Covariance via VBA

1

1ma9

XLDnaute Nouveau
Bonjour à tous,

Je tente de faire une optimisation, et pour cela j'ai besoin d'une matrice variance covariance...

normallement elle se présente ainsi :

Matrice de variance-covariance - Wikipédia

Pas très difficile me diriez vous... la je vous le présente pour 12 valeurs dans le fichier joint... cependant j'en ai près de 450...
et donc je ne maitrise pas assez vba pour pouvoir créer une macro qui serait capable de l'automatiser... je recherche donc de l'aide sur ce forum... :)

Pour l'instant mon code ressemble a ca :

Sub varcov()
'
' varcov Macro


Range("A1").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur1)"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur2)"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur3)"
Range("A4").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur4)"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur5)"
Range("A6").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur6)"
Range("A7").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur7)"
Range("A8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur8)"
Range("A9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur9)"
Range("A10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur10)"
Range("A11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur11)"
Range("A12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur1,valeur12)"


Range("B2").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur2)"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur3)"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur4)"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur5)"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur6)"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur7)"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur8)"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur9)"
Range("B10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur10)"
Range("B11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur11)"
Range("B12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur2,valeur12)"


Range("C3").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur3)"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur4)"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur5)"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur6)"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur7)"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur8)"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur9)"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur10)"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur11)"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur3,valeur12)"


Range("D4").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur4)"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur5)"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur6)"
Range("D7").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur7)"
Range("D8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur8)"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur9)"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur10)"
Range("D11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur11)"
Range("D12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur4,valeur12)"


Range("E5").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur5)"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur6)"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur7)"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur8)"
Range("E9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur9)"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur10)"
Range("E11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur11)"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur5,valeur12)"


Range("F6").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur6)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur6,valeur7)"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur6,valeur8)"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur6,valeur9)"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur6,valeur10)"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur6,valeur11)"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur6,valeur12)"


Range("G7").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur7)"
Range("G8").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur7,valeur8)"
Range("G9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur7,valeur9)"
Range("G10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur7,valeur10)"
Range("G11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur7,valeur11)"
Range("G12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur7,valeur12)"


Range("H8").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur8)"
Range("H9").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur8,valeur9)"
Range("H10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur8,valeur10)"
Range("H11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur8,valeur11)"
Range("H12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur8,valeur12)"

Range("I9").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur9)"
Range("I10").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur9,valeur10)"
Range("I11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur9,valeur11)"
Range("I12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur9,valeur12)"

Range("J10").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur10)"
Range("J11").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur10,valeur11)"
Range("J12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur10,valeur12)"

Range("K11").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur11)"
Range("K12").Select
ActiveCell.FormulaR1C1 = "=COVAR(valeur11,valeur12)"

Range("L12").Select
ActiveCell.FormulaR1C1 = "=VAR(valeur12,valeur12)"

End Sub
Mais je ne m'imagine pas avec les 450 valeurs...

merci à vous !!!
 

Pièces jointes

  • test 12 valeurs.xls
    28.5 KB · Affichages: 243
Dernière édition:
Trier par date Trier par votes
1

1ma9

XLDnaute Nouveau
Re : Matrice Variance Covariance via VBA

il y a aura en tete de chaque colonne le nom de la valeur cela commencera à la colonne A... :) et cela fini à ACH...


et sous elle les différents cours... 1300... :)

deplus je me suis rendu compte que des fois il y avait des trous dans les cours... mais je ne veux pas que VBA le remplace par 0... est ce possible... je vous en demande bcp... c'est tres gentil de prendre un peu de temps... !

merci pour la bienvenue.. :)
 
Dernière édition:
Vote positif 0 Vote négatif
H

Habitude

XLDnaute Accro
Re : Matrice Variance Covariance via VBA

d'abord tu te déclare une class Variance
dans laquelle tu y insére une matrice de 350 éléments
Tu fais une insertion Module de Classe
Que tu renomme ClasseVariance

Dans ta classe
Code: 
dim matriceVariance(350) as double

Ensuite tu te déclare une matrice de 350 éléments d'objet de ta classe dans ta feuille

Tu aura alors 350 matrice variance qui te permettra de faire une covariance.

Tu n'auras plus qu'a afficher ta matrice de classe à l'écran


Code: 
dim matriceCovariance(1 to 350) as New ClasseVariance

Code: 
dim rng1, rng2 as range


for j = 1 to 350  'ici on créer les matrices de variances

     for i = 1 to 350
            Set rng1 = activeSheet.Range("A" & i & ":A350")'il faut ajuster les range en  conséquence
            Set rng2 = activeSheet.Range("B" & i & ":B350")'il faut ajusté les range en conséquence
            Variance = Evaluate("Var(" & rng1.Address & "*" & rng2.Address & ")")  
            matriceCovariance(j).AddVariance Variance i  'Fonction a créer dans la classe

Code: 
dim m1, m2 as range

set m1= "FF1:FF350"
set m2= "FG1:FG350"
m1= matriceCovariance(1) 'ici tu affiche les données de la matrice de variance 1
m2= matriceCovariance(2) 'ici tu affiche les données de la matrice de variance 2

Co_Variance = Evaluate("Covariance(" & m1 & "*" & m2 & ")")

En gros ca ressemblerais à ca...
 
Vote positif 0 Vote négatif
L

lhaj6

XLDnaute Nouveau
Re : Matrice Variance Covariance via VBA

j'ai un soucis j'ai inseré le code de Mr ROGER2327 mais ca semble ne pas marcher vous pouvez m'aider j'ai plusieurs valeurs je cherche à construire une matrice de variance covariance merci pour votre aide

le fichier est trop volumineux
 
Vote positif 0 Vote négatif
ROGER2327

ROGER2327

XLDnaute Barbatruc
Re : Matrice Variance Covariance via VBA

Bonjour lhaj6


lhaj6 à dit:
j'ai un soucis j'ai inseré le code de Mr ROGER2327 mais ca semble ne pas marcher vous pouvez m'aider j'ai plusieurs valeurs je cherche à construire une matrice de variance covariance merci pour votre aide

le fichier est trop volumineux
Cliquez pour agrandir...
L'essai que j'ai proposé fonctionne sur la base des données pour laquelle je l'ai écrit. Je ne sais pas si cet essai répond à la demande, 1ma9 n'ayant jamais daigné répondre à cette proposition.
Quant à l'adapter à une autre situation, pourquoi pas ? Encore faudrait-il que nous vissions la dite situation...​


Bon courage.


ROGER2327
#5689

Mercredi 4 Clinamen 139 (Saint Pagne, confident - fête Suprême Quarte)
6 Germinal An CCXX, 5,8761h - blette
2012-W13-1T14:06:09Z
 
Vote positif 0 Vote négatif
ROGER2327

ROGER2327

XLDnaute Barbatruc
Re : Matrice Variance Covariance via VBA

Re...

lhaj6 à dit:
Dites Vous n'avez pas déjà realiser une application portant sur la value at risk??
Cliquez pour agrandir...
Non. Je ne sais même pas ce que cela signifie. (Je ne parle et n'écrit que le Français.)

Désolé.


ROGER2327
#5691

Mercredi 4 Clinamen 139 (Saint Pagne, confident - fête Suprême Quarte)
6 Germinal An CCXX, 6,9012h - blette
2012-W13-1T16:33:47Z
 
Vote positif 0 Vote négatif

Discussions similaires

D
  • Question
Empêcher le rafraichissement automatique d'un tableau croisé dynamique
Réponses
3
Affichages
7 K
Guest
G
yadla
  • Question
Limite dans les additions ??
Réponses
2
Affichages
819
yadla
yadla
M
[REF] Wiki 5 de MichelXld (Formules, Audits de formules, Répertoires, Fichiers)
Réponses
0
Affichages
46 K
MichelXld
M

Membres actuellement en ligne

3 membres et 47 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 177
Messages
2 085 972
Membres
103 073
dernier inscrit
MSCHOE16