Macro pour comparer des valeurs entre deux fichiers

Bonjour à vous,

Récent utilisateur de VBA sur Excel, je travaille sur un projet de fichier stock d'options. J'ai déjà créé un formulaire pour insérer ces options, et une macro pour supprimer les options (à l'ouverture du fichier) dont la date d'échéance (colonne H) est échue à la date du jour.

Jusque là tout va bien ! Maintenant cela se corse, ce fichier (Stock_OptionsChange_tests ci joint) doit etre relié à un deuxième fichier (Historique EUR-USD) pour tester si les barrières (colonnes E et F du fichier Stock) ont été dépassées.

Pour essayer d'être clair, je pense à une macro qui à son activation, va regarder dans la colonne Barrière (colonne E du Stock) si il y a une barrière (c'est à dire différent de " - "), va prendre la Date de Négociation (colonne G du Stock) correspondant à cette ligne, et va regarder sur l'autre fichier (Historique) si entre cette date de négociation et la dernière date (Colonne A du fichier Stock, dernière date sera toujours en A5 car c'est un fichier qui s'actualise tous les jours avec les 600 dernières dates), le strike barrière (colonne F du Stock) n'est jamais entre le HIGH et le LOW (colonnes C et D du fichier Historique).

Et si ce strike barrière se trouve entre le HIGH et le LOW pour une date entre la date de négociation et la dernière date (A5 de l'historique), souligner en rouge par exemple dans le fichier Stock la ligne correspondante à cette barrière touchée.

Je ne sais pas si j'ai été assez clair, je vous joint les deux fichiers correspondants, merci pour toute aide pour avancer là dessus !

Bonne journée,

Duma

Re bonjour,

Ma demande n'est peut etre pas évidente à traiter, j'avoue etre assez peu à même de la quantifier..

Peut etre sera t-il plus facile pour vous de m'aider par étape.

Il me faut donc tout d'abord sélectionner les valeurs en cellules (i, 6) (colonne F) si et seulement si on a la valeur KOUSD ou KIUSD dans la colonne E.

Sub barriere()

Range("B5:M65536").Sort Key1:=Range("M4"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    For i = Range("E65536").End(xlUp).Row To 5 Step -1
        If Cells(i, 5).Value2 = "KOUSD" Then Cells(i, 6).Select
        If Cells(i, 5).Value2 = "KIUSD" Then Cells(i, 6).Select

          Next i
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

Bonjour

Quelques précisons

Duma a écrit :

entre cette date de négociation et la dernière date

Ce que je comprends

Il faut chercher dans le fichier Historique cette date et entre cette date et la dernière date, la valeur en F doit toujours être comprise entre les valeurs C et D du fichier historique

Que se passe t'il si la date n'est pas trouvée ?

Dans le fichier Historique dans quelle page faut t'il chercher ?

Il manque des valeurs dans les colonnes C et D, que fait-on ?

Banzai64 a écrit :

Bonjour

Quelques précisons

Duma a écrit :

entre cette date de négociation et la dernière date

Ce que je comprends

Il faut chercher dans le fichier Historique cette date et entre cette date et la dernière date, la valeur en F doit toujours être comprise entre les valeurs C et D du fichier historique

Que se passe t'il si la date n'est pas trouvée ?

Dans le fichier Historique dans quelle page faut t'il chercher ?

Il manque des valeurs dans les colonnes C et D, que fait-on ?

Bonjour Banzai64,

Oui pour la première précision, date de négociation du stock pour laquelle on a trouvé une barrière, et entre cette date et la date la plus récente dans Histo (cellule A5), on teste chaque ligne pour voir si valeur D <= valeur F <= valeur C.

Si on a aucune ligne du fichier histo (dans l'intervalle de date) pour laquelle on a cette équation, il ne se passe rien.

Si on a une ou plusieurs lignes du fichier histo (dans l'intervalle de date) pour laquelle on a cette équation, la barrière a donc été atteinte et il faudrait pouvoir le signaler sur le fichier Stock en soulignant la ligne.

Pour ce qui est des dates dans le fichier historique, je n'avais pas pensé à ce problème car effectivement il n'y a pas toutes les dates dans ce fichier.. Il faudrait pouvoir prendre la date suivante si celle correspondante n'est pas dans le fichier Historique (exemple on a date de négo = 02/06/2012, on compare les valeurs entre 04/06/2012 et 21/02/2013).

Il faut chercher dans la page "Histo" du fichier Historique EUR-USD

Enfin pour les valeurs des colonnes C et D, il va falloir que je revois mon fichier qui intègre les valeurs car normalement on devrait avoir 600 dates avec des valeurs. Pour le fichier Stock actuel on a heureusement des barrières qu'à partir de la date du 22/06/2012 donc ça le fait avec les dates du fichier Historique sur l'exemple.

Merci en tous les cas pour ton aide.

Bonjour

A tester

Les 2 fichiers dans le même répertoire

Bonsoir,

Merci beaucoup pour ton aide ça semble vraiment être ce que j'avais imaginé ... sans savoir écrire le code correspondant !

Quelques questions/remarques :

Il faut toujours que le fichier Historique soit dans le même répertoire, par contre on dirait que la macro n'accepte pas que ce fichier historique soit ouvert pendant qu'elle tourne !?

Dans un second temps, j'aimerai pouvoir effectuer une action différente selon le fait qu'une barriere "KOUSD" ou "KIUSD" soit touchée.

Je m'explique, ton code identifie parfaitement les barrières touchées via le lien entre les dates avec le fichier historique, et maintenant que ceci fonctionne, je souhaiterai pouvoir supprimer la ligne si c'est une barriere "KOUSD" (KO étant une barrière désactivante donc si elle est touchée, l'option = sa ligne doit disparaitre du stock), et si c'est une barriere "KIUSD" qui est touchée, que la ligne soit coloriée comme actuellement.

Dans le code que tu as réalisé, on teste si c'est différent de "-" (<> "-"), pourrait-on copier coller l'ensemble du code en changeant juste cette condition par = KOUSD pour l'un et = KIUSD pour l'autre et ensuite différencier dans le "Then" final ? (J'ai essayé mais sans succès sur le coup alors je me demande si c'est faisable aussi simplement..)

Merci encore pour le temps consacré à m'aider et bonne soirée !

Bonsoir

Duma a écrit :

par contre on dirait que la macro n'accepte pas que ce fichier historique soit ouvert pendant qu'elle tourne !?

C'est la macro qui se charge de l'ouvrir

Comme il peut y avoir des lignes supprimées (pas dans ce fichier) il faut boucler de la fin vers le début

Les tests sont les mêmes, et au cas ou le test est concluant c'est à ce moment que le test sur la cellule E est fait et on agit en conséquence ==> Suppression ou coloriage

A tester

Si problème: fournis un fichier historique avec des conditions permettant la suppression de ligne

Re bonsoir,

Macro testée, ça fonctionne parfaitement que ce soir pour une KIUSD ou la suppression pour une KOUSD.

Merci beaucoup pour cette aide rapide et efficace, je vais essayer de poursuivre mon projet et si besoin je reprendrai la suite de ce topic pour faire appel à ton expertise .

Bonne soirée,

Duma, très satisfait !

Bonjour,

Tout fonctionne quand j'ajoute une KIUSD ou une KOUSD, par contre j'ai un problème avec la ligne 66, pour laquelle j'avais une KOUSD mais pas de valeur dans la colonne F. Lorsque j'ajoute une valeur à la place du tiret (en F66), que je lance la macro de comparaison, j'ai :

"Erreur d'éxécution '13' : Incompatibilité de type"

qui me renvoie à : Ligne = Application.Match(CSng(WsStock.Range("G" & J)), WsHisto.Columns("A"), -1)

Une idée du pourquoi ?

Bonjour

Ta valeur en G n'est pas une date, ça a l'air du date mais ce n'est que du texte

Et ce n'est pas la seule

Fais l'expérience suivante

Sélectionnes tes colonnes sensées contenir des dates, format de cellule en Standard , et tu verras une date sera transformée en nombre

A voir quand tu copies ces valeurs

Bonjour,

Je pensais pourtant avoir mis ces deux colonnes en "date" via Données => Convertir => Date + Nombre => Date .

On ne peut pas faire mieux pour être sur d'avoir des dates dans ces cellules ?

Effectivement en Standard tout apparaît en valeur.

Merci.

Bonjour

A l'origine ces "dates" ont un format spécial que convertir ne peut reconnaitre

Pas facile de savoir, il faut reprendre au début et vérifier ces "dates"

Ah d'accord, j'ai fait cette vérification du coup et ça fonctionne.

Lors de la mise en place de la macro, tout le stock "scanné" passe d'une couleur via la ligne :

WsStock.Range("B4:M" & NbLg).Interior.ColorIndex = 33

J'aimerais la supprimer pour garder la couleur de départ pour le stock, mais en créant une ligne, en dehors de la macro comparaison, qui colorie toute ligne où une barrière "KI" est inscrite en colonne E.

Ca donnerait donc tout le stock d'option d'une seule couleur, sauf les lignes KI toujours en gris par exemple, et quand une barrière KI est touchée, la ligne se colorie dans la même couleur que le stock, pour se fondre aux autres.

Le problème étant que je ne vois pas comment effectuer une sélection sans prendre toute la ligne, et aussi qu'on risque d'avoir un conflit entre la macro qui colorie toute ligne dans laquelle on a une barrière KI et celle qui remet la couleur du stock quand on touche cette barrière..

Je ne sais pas si j'ai été compréhensible, j'aimerai arriver à un stock de toutes les lignes disons en bleu, sauf les lignes où il est écrit KI en gris, et même si on ajoute une nouvelle option avec KI, elle apparaisse en gris.

Et lors du lancement de la macro "comparaison", si la KI est atteinte, la couleur de sa ligne se fond dans le bleu classique (manière de montrer que l'option fait désormais partie du stock "classique" puisqu'elle a été activée).

Merci encore car j'en demande beaucoup (d'aide ^^) !

Bonjour

Comme tu utilises une version d'Excel différente de la mienne, je n'ai pas la correspondance de la couleur que tu utilises

Il suffit que tu récupères cette couleur

1. Tu démarre un enregistrement macro
2. Tu sélectionnes une cellule avec la bonne couleur
3. Tu re-colorises cette cellule avec la bonne couleur
4. Tu arrêtes l'enregistrement de la macro
5. Tu récupères dans un module la couleur de ta cellule ??????????
6. Tu places dans la ligne que tu as trouvée

WsStock.Range("B4:M" & NbLg).Interior.Color = ??????????

Et comme cela tu laisses le reste du code inchangé

D'accord mais si je fais ça je vais avoir les lignes KI qui sont touchées dans l'historique qui vont se colorer d'une autre couleur que l'ensemble du stock.

Or c'est le contraire que je souhaite, c'est à dire que les lignes où il y a une barrière KI soient de couleurs différentes des autres lignes, et que quand une barrière KI est atteinte lors d'un test sur l'historique, celle-ci reprenne la couleur de toutes les autres lignes.

Après pour ce qui est de la couleur précisément ce n'est pas un problème je peux effectivement la modifier en testant l'enregistreur de macro comme tu me l'expliques ici.

Bonjour

Duma a écrit :

je vais avoir les lignes KI qui sont touchées dans l'historique qui vont se colorer d'une autre couleur que l'ensemble du stock.

Oui c'est le principe, justement qu'on puisse les voir rapidement

Sinon cela revient au même (plus long - minime je pense), si tu colories seulement les lignes qui ne sont pas touchées

J'arrive pas à cerner ta question

En fait les lignes barrières KI correspondent à des options qui ne sont activées (donc vraiment en stock) que lorsque la barrière est atteinte. Donc il faudrait que quand on a une option où la barrière est une KI, on soit sur une couleur pale pour signifier que pour l'instant elle n'existe pas vraiment, tant que la valeur de cette barrière n'est pas activée (via l'historique).

Et donc quand la barrière KI est activée, la couleur devient celle de l'ensemble du stock, car elle est maintenant une option comme une autre.

Pour les barrières KO, ce sont des barrières désactivantes donc les options sont des options normales du stock, jusqu'au moment où la barrière est touchée (= la macro la supprime du stock à ce moment là).

Je ne sais pas si c'est clair en fait ce que je dis

Bonjour

Duma a écrit :

Je ne sais pas si c'est clair en fait ce que je dis

Je peux te répondre non c'est pas plus clair

Pour que j'essaye de comprendre et pour que je puisse te modifier le macro le cas échéant

Tu fais 2 feuilles

Dans la première ce que fait la macro actuellement

Dans la 2ème ce que tu voudrais avoir

Parce que pour moi rouge ou couleur pale c'est kif kif bourricot, le résultat c'est de différencier la ligne touchée

Je me doutais un peu que je n'avais pas été clair .

Actuellement la macro (Fichier Duma Comparaison V002) colorie l'ensemble du stock, elle cherche dans ce stock et dans la colonne E les lignes qui possèdent une barrière = KI ou KO.

Pour chacune de ces lignes possédant une barrière, elle teste en fonction de la date de négociation (colonne G) si entre cette date de négociation et la dernière date du fichier Historique (cellule A4), la valeur de la barrière (cellule F du fichier Duma) est atteinte, c'est à dire est ce qu'il y a une date pour laquelle la valeur de la barrière est comprise entre les valeurs des colonnes C et D de l'historique.

Si c'est le cas, deux cas de figure : soit on a une barrière nommée KO en colonne E, et dans ce cas la ligne est supprimée. Soit on a une barrière nommée KI en colonne E, et dans ce cas la ligne est mise en couleur.

Maintenant pour ce que je souhaiterais, je voudrais tout d'abord qu'avant même le lancement d'une macro, l'ensemble du stock soit de la même couleur, sauf les lignes ou la barrière est une KI en colonne E, qui serait dans une autre couleur (peu importe la couleur, ça au moins je sais modifier ^^).

Ceci étant en place, tout nouvel ajout d'une ligne option, une macro colorie automatiquement la ligne dans la couleur si le terme "KI" s'affiche en colonne E.

La deuxième étape, la macro actuelle qui teste les barrières via le fichier historique. Pour celles qui sont activées et dont le nom est KO en colonne E, on ne change rien, les lignes sont supprimées si la condition est touchée.

Pour celles qui sont activées et dont le nom est KI en colonne E, on modifie leur couleur comme actuellement mais comme toutes les lignes KI sont de couleur différentes du reste des lignes, on modifie la couleur de la ligne concernée pour qu'elle soit la même que l'ensemble du stock.

Cf fichier "Arrivée" pour ce que j'aimerais obtenir.

Merci, Duma.

Bonjour

D'après ce que j'ai compris

La macro quand elle rencontre un "KI" colorise la ligne en jaune

Le reste de la macro ne change pas