Calcul variation glissante 3 Mois avec problème dates exactes

[jeje1664]

Bonjour à tout le forum, 🙂

Je travaille actuellement sur une base de données comportant l'intégralité des cours de cloture de plusieurs dizaines d'actions boursières.

Pour cela une interface avec un logiciel financier me met à disposition chaque soir l'ensemble des historique de clôture (1ere colonne = date / 2eme colonne = cours de cloture) en effacant et remplacant la base de la veille. (je ne peux donc pas intervenir moi même sur cette base)

Supposons que mon souhait est de connaitre à tout moment la variation de cours d'une action par rapport à sa valeur de clôture 3 mois auparavant.

Pour cela j'utilise la formule suivante qui me donne des résultat plutôt satisfaisant : (($GN$3)/(RECHERCHEV(MOIS.DECALER($GM$3;-3);$GM:$GN;2;FAUX))-1)

(pour info GN3 est la cellule qui comporte le dernier cours de clôture connu).

Le hic, c'est que comme vous pouvez le voir ci-dessous, l'action en question n'est pas cotée tous les jours. Excel n'est dc pas en mesure de calculer une variation entre le cours du 26/08 et celui du 26/05 (car il n'existe pas!) et me retourne donc la valeur #N/A.

26/08/2008 40 321,05
19/08/2008 40 017,34
12/08/2008 40 242,99
05/08/2008 40 322,80
29/07/2008 40 507,07
22/07/2008 40 421,79
15/07/2008 40 629,47
08/07/2008 40 774,90
01/07/2008 41 314,74
24/06/2008 41 480,85
17/06/2008 41 760,04
10/06/2008 41 822,88
03/06/2008 41 715,36
27/05/2008 41 527,82
20/05/2008 41 391,40
13/05/2008 41 206,91
06/05/2008 40 981,36
29/04/2008 40 946,92
22/04/2008 41 141,76
15/04/2008 41 052,98
08/04/2008 40 943,98
01/04/2008 40 764,67

Ma question est donc la suivante :

Comment faire comprendre à Excel (si possible sans VBA) que dans ce cas de figure, celui-ci doit alors appliquer son calcul de variation sur la date la plus proche de cette date "manquante" (dans l'exemple ci-dessus il calculerait donc la variation entre le 26/08 et le 27/05)....?

J'espère avoir été clair dans la présentation de mon problème.
Ca peut paraitre assez bêta mais je bloque dessus depuis plusieurs jours... 😕

Merci bcp pr vos éventuelles réponses et/ou contributions !

 

[dixit]

Re : Calcul variation glissante 3 Mois avec problème dates exactes

bonjour,
une suggestion : mettre le paramètre à VRAI au lieu de FAUX dans RECHERCHEV (ou supprimer le paramètre) : dans ce cas, si aucune valeur exacte n'est trouvée, la valeur immédiatement inférieure est renvoyée.
à suivre

 

[wilfried_42]

Re : Calcul variation glissante 3 Mois avec problème dates exactes

bonjour

je n'ai pas tout compris, mais si tu fournissais un fichier exemple histoire de tester des formules

un petit exemple : tester la valeur minimun des le diference entre la date de reference et les date de la base de données)

=index(mesdates;si(min(Abs(mesdate-dateref)=mesdate-dateref;ligne(mesdate);0))

ceici n'est qu'un exemple non testé (fait à main levée), sans fichier, dur de donner une formule

dans l'attente de te lire

ps : il y a bien meilleur que moi en Formule

 

[Catrice]

Re : Calcul variation glissante 3 Mois avec problème dates exactes

Bonjour,

Si j'ai bien compris, une solution avec INDEX et EQUIV qui permet de choisir une valeur proche (avec le dernier argument à -1).

Voir le fichier joint, à tester dans les différents cas de figures que tu peux rencontrer

 

[jeje1664]

Re : Calcul variation glissante 3 Mois avec problème dates exactes

Bonjour à tous,

un gros merci Catrice : j'ai réussi à trouver mon bonheur dans ton fichier en combinant comme tu le proposes les formules INDEX et EQUIV.

C'est mon chef qui va être ravi, encore merci !

 

[Monique]

Re : Calcul variation glissante 3 Mois avec problème dates exactes

Bonjour,

La date la plus proche, à mon avis :
=MAX(SI(ABS(Date-MOIS.DECALER(E3;-3))=MIN(ABS(Date-MOIS.DECALER(E3;-3)));Date))
Formule matricielle, à valider par ctrl, maj et entrée

Le cours à cette date :
=INDEX(Cours;EQUIV(MAX(SI(ABS(Date-MOIS.DECALER(E3;-3))=MIN(ABS(Date-MOIS.DECALER(E3;-3)));Date));Date;0))
Formule matricielle, à valider par ctrl, maj et entrée

 

[jeje1664]

Re : Calcul variation glissante 3 Mois avec problème dates exactes

Ok j'ai également téléchargé ton fichier Monique, tout comme pr Catrice tu as parfaitement compris et résolu ma problématique.

Encore merci