macro vba, calculs de gains/pertes selon dates et conditions

**SeB18** · 24/07/2012, 12h04

Bonjour à tous,

j'ai essayé de faire un titre qui vous résume ce que je tente désespérément de faire depuis 3 semaines...
Je suis toute débutante sur VBA, on m'a confié ce projet suite à une formation interne de 3 jours et je crois que mes petites compétences touchent leurs limites.

Je vous explique :

J'ai une première feuille excel (BASE) qui présente 774 lignes et 7 colonnes.
A = date
B = prix A
C = prix B
D = prix A - prix B
E = estimation statistique de D
F = D - E
G = ID

Je cherche à créer une seconde feuille (RESULTS) qui contiendra 12 colonnes en dépendant de la feuille BASE.
La première instruction :
Si abs(BASE.range("f")) > abs (Seuil)
(seuil étant une donnée imposée que j'ai stockée dans une 3ème feuille)
alors on répertorie dans la feuille RESULTS la date correspondante à F dans BASE, prix A, prix B, D F et G

Jusque là, je n'ai pas de difficulté, tout roule.

Là où ça devient plus compliqué (pour moi en tous cas) :
Maintenant, je cherche, pour chaque date répertoriée dans RESULTS, à faire le calcul suivant :
BASE.range("d") - RESULTS.range("d")
lorsque ce calcul est supérieur à GAIN (une nouvelle constante que j'ai stockée dans la 3ème feuille) alors on répertorie la date correspondante dans RESULTS, la valeur de D pour cette date et le résultat du calcul.

Le tout est que je n'arrive pas à faire comprendre à VBA qu'il doit faire ce calcul pour chacune des dates qu'il a trouvées dans RESULTS; que ces dates sont tour à tour sa référence et qu'il doit trouver cette référence dans BASE pour effectuer le calcul sur toutes les lignes suivantes de BASE jusqu'à répondre à ma condition.

Je ne sais pas si c'est très clair en français...
Si vous voulez que je joigne mon fichier, dites le moi.

Je commence sincèrement à désespérer.
J'ai tenté des boucles dans des boucles, des boucles séparés, des base.range.find; etc... et la macro tourne sans bug mais ne me donne AUCUN résultat. Alors je commence à manquer d'imagination et d'idées pour y arriver.

D'avance merci de votre aide

**gFZT82** · 24/07/2012, 13h45

Bonjour,

Voici pour commencer un code qui balaye la colonne A de la feuille "BASE" et qui recherche dans la colonne A de la feuille "RESULTS" une valeur correspondante.
La ligne étant repérée, il ne te reste plus qu’à effectuer ton traitement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Option Explicit
Sub Test()
Dim WsS As Worksheet
Dim DerLig As Long
Dim MaPlage As Range, Cel As Range, PlageRecherche As Range, c As Range
    'On déclare la plage de "RESULTS" dans laquelle sera effectuée la recherche de correspondance
    Set PlageRecherche = Worksheets("RESULTS").Columns("A")
    With Worksheets("BASE")
        'Dernière ligne renseignée dans la colonne A de "BASE"
        DerLig = .Range("A" & .Rows.Count).End(xlUp).Row
        'On déclare la plage de "BASE" qui contient toutes les dates à rechercher
        Set MaPlage = .Range("A2:A" & DerLig)
        'Pour chacune de ces dates, on recherche une correspondance dans "RESULTS"
        For Each Cel In MaPlage
            Set c = PlageRecherche.Find(Cel.Value, LookIn:=xlValues)
            'Si on trouve, on effectue le traitement
            If Not c Is Nothing Then
                'Traitement
                MsgBox "La date " & Cel & " de la feuille " & .Name & " a été trouvée à l'adresse " & _
                c.Address & " de la feuille ""RESULTS"""
            End If
        Next Cel
    End With
End Sub

Cordialement.

**SeB18** · 24/07/2012, 17h23

Merci beaucoup pour la rapidité de la réponse.
Ça me permet de débloquer la suite de ma macro; merci

J'ai continué le processus mais la macro ne donne rien.
Elle ne présente aucun bug mais elle ne donne pas de résultats autre que ceux déjà présents avant mon premier post.

Mon code actuel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
Sub CalcArbitrageN()
 
'Déclaration des variables utilisée
Dim DateEntree As Date
Dim LigneBase  As Long, LigneResult  As Long, LigneBase2 As Long
Dim Seuil, Gain, Perte, Dif, Calcul As Variant
Dim GainPerteH, GainPerteB, ID As Variant
Dim DerLig As Long
Dim MaPlage As Range, Cel As Range, PlageRecherche As Range, c As Range
 
' Nettoyage de la feuille de résultat
WsResult.Range(WsResult.Range("A2"), WsResult.Cells(WsResult.Rows.Count, "Z")).ClearContents
 
' Initialisation des variables
LigneBase = 2
LigneResult = 1
Seuil = WsMenu.Range("g12")
Gain = WsMenu.Range("g10")
Perte = WsMenu.Range("g11")
 
Do Until IsEmpty(WSBase.Range("a" & LigneBase))
 
'vérif qu'il s'agit d'une date avant traitement
    If IsDate(WSBase.Range("A" & LigneBase)) Then
        DateEntree = WSBase.Range("A" & LigneBase)
    Else
        MsgBox "La colonne A ne doit contenir que des dates"
        Exit Do
    End If
 
    Dif = WSBase.Range("h" & LigneBase)
    ID = WSBase.Range("i" & LigneBase)
 
'Si on a une erreur dans la colonne différence, on l'ignore et on passe à la ligne suivante
    If IsError(Dif) = True Then
 
'La différence est supérieure au seuil d'initation indiqué
    ElseIf Abs(Dif) >= Abs(Seuil) And DateSerial(Year((DateEntree) - 1), 5, 10) < DateEntree < DateSerial(Year(DateEntree), 2, 15) Then
    LigneResult = LigneResult + 1
'Libellés dans la feuille ResultatsN
    With WsResult
    .Range("a1") = "Date d'entrée"
    .Range("b1") = "Différence"
    .Range("c1") = "Spread In"
    .Range("d1") = "Prix Euronext In"
    .Range("e1") = "Prix CBOT In"
    .Range("f1") = "Date de sortie"
    .Range("g1") = "Spread Out"
    .Range("h1") = "Prix Euronext Out"
    .Range("i1") = "Prix CBOT Out"
    .Range("j1") = "Gain/Perte"
    .Range("k1") = "Nombre de jours en position"
    .Range("l1") = "Appel de marge moyen"
    .Range("m1") = "Appel de marge maximum"
    .Range("n1") = "ID référent"
    End With
'Récupération des données à l'entrée sur le marché
    WsResult.Range("a" & LigneResult) = DateEntree
    DateRef = WsResult.Range("a" & LigneResult)
    WsResult.Range("b" & LigneResult) = Dif
    WsResult.Range("c" & LigneResult) = WSBase.Range("f" & LigneBase)
    WsResult.Range("n" & LigneResult) = ID
    ID2 = WsResult.Range("n" & LigneResult)
'Si la différence est supérieure à 0
        If WsResult.Range("b" & LigneResult) > 0 Then
        WsResult.Range("d" & LigneResult) = WSBase.Range("d" & LigneBase)
        WsResult.Range("e" & LigneResult) = WSBase.Range("e" & LigneBase) * -1
 
'Si la différence est inférieure à 0
        ElseIf WsResult.Range("b" & LigneResult) < 0 Then
        WsResult.Range("d" & LigneResult) = WSBase.Range("d" & LigneBase) * -1
        WsResult.Range("e" & LigneResult) = WSBase.Range("e" & LigneBase)
        End If
    End If
 
 
 
LA PARTIE QUI BUGG :
 
 
    Set PlageRecherche = WSBase.Columns("i")
With WsResult
    'Dernière ligne renseignée dans la colonne A de "Result"
    DerLig = .Range("A" & .Rows.Count).End(xlUp).Row
    'On déclare la plage de "Results" qui contient toutes les dates à rechercher
    Set MaPlage = .Range("n2:n" & DerLig)
    'Pour chacune de ces dates, on recherche une correspondance dans "Base"
    For Each Cel In MaPlage
        Set c = PlageRecherche.Find(Cel.Value, LookIn:=xlValues)
        'Si on trouve, on effectue le traitement
        If Not c Is Nothing Then
        .Range("o" & LigneResult) = c.Address(0, 0)
        'Do Until IsEmpty(WSBase.Range("i" & (LigneBase)))
        'If WsResult.Range("b" & LigneResult) > 0 Then
            'Do
            'Calcul = (-WSBase.Range("f" & (LigneBase2) + WsResult.Range("c" & LigneResult)))
            'If Calcul >= Gain Then
                'WsResult.Range("f" & LigneResult) = WSBase.Range("a" & (LigneBase2))
                'WsResult.Range("g" & LigneResult) = WSBase.Range("f" & (LigneBase2))
                'WsResult.Range("h" & LigneResult) = WSBase.Range("d" & (LigneBase2)) * -1
                'WsResult.Range("i" & LigneResult) = WSBase.Range("e" & (LigneBase2))
                'WsResult.Range("j" & LigneResult) = Calcul
            'ElseIf Calcul <= Perte Then
                'WsResult.Range("f" & LigneResult) = WSBase.Range("a" & (LigneBase2))
                'WsResult.Range("g" & LigneResult) = WSBase.Range("f" & (LigneBase2))
                'WsResult.Range("h" & LigneResult) = WSBase.Range("d" & (LigneBase2)) * -1
                'WsResult.Range("i" & LigneResult) = WSBase.Range("e" & (LigneBase2))
                'WsResult.Range("j" & LigneResult) = Calcul
            'End If
            'Loop
        'Else
            'Do
            'Calcul = (WSBase.Range("f" & (LigneBase2) - WsResult.Range("c" & LigneResult)))
            'If Calcul >= Gain Then
                'WsResult.Range("f" & LigneResult) = WSBase.Range("a" & (LigneBase2))
                'WsResult.Range("g" & LigneResult) = WSBase.Range("f" & (LigneBase2))
                'WsResult.Range("h" & LigneResult) = WSBase.Range("d" & (LigneBase2))
                'WsResult.Range("i" & LigneResult) = WSBase.Range("e" & (LigneBase2)) * -1
                'WsResult.Range("j" & LigneResult) = Calcul
            'ElseIf Calcul <= Perte Then
                'WsResult.Range("f" & LigneResult) = WSBase.Range("a" & (LigneBase2))
                'WsResult.Range("g" & LigneResult) = WSBase.Range("f" & (LigneBase2))
                'WsResult.Range("h" & LigneResult) = WSBase.Range("d" & (LigneBase2))
                'WsResult.Range("i" & LigneResult) = WSBase.Range("e" & (LigneBase2)) * -1
                'WsResult.Range("j" & LigneResult) = Calcul
            'End If
            'LigneBase2 = LigneBase2 + 1
            'Loop
        'End If
        'LigneBase = LigneBase + 1
        'Loop
            'Traitement
            'MsgBox "La date " & Cel & " de la feuille " & .Name & " a été trouvée à l'adresse " & _
            c.Address & " de la feuille ""Données d'arbitrage"""
        End If
    Next Cel
End With
 
 
LigneBase = LigneBase + 1
Loop
end sub

**gFZT82** · 24/07/2012, 18h05

Tu peux exécuter la partie de code incriminée en pas à pas pour trouver la cause du dysfonctionnement. Un survol des variables avec la souris ou la fenêtre Variables locales te permettront de visualiser les valeurs.
A priori, si tu n'obtiens pas de résultat, c'est parce que la recherche find ne trouve pas l'élément recherché.

Petite question : c'est quoi "i" dans

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set PlageRecherche = WSBase.Columns("i")

J'aurais mieux compris avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set PlageRecherche = WSBase.Columns(i)

Si tu ne parviens pas à trouver, fais passer un extrait de fichier (sans données confidentielles).

EDIT : s'il s''agit bien de la colonne "I", ne tiens pas compte de ma question. Inutile de t'embrouiller les idées

.

**SeB18** · 30/07/2012, 10h06

Décidément ça ne passe pas !

Je m'y prends mal, c'est certain mais je ne vois plus les problèmes à force de relire ce code ^^
Je joins donc mon fichier pour faire mes calculs.
Il ne faut se concentrer que sur les données d'arbitrage et les résultats N.
Tout ce qui est N+1 se gèrera différemment

et ça c'est mon code actuel.
Si vous trouvez quelque chose...

merci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
Sub CalcArbitrageN()
 
'Déclaration des variables utilisée
Dim DateEntree As Date, DateRef As Date
Dim LigneBase  As Long, LigneResult  As Long, LigneBase2 As Long
Dim Seuil, Gain, Perte, Dif, Calcul As Variant
Dim ID, ID2 As Variant
Dim DerLig As Long
Dim DonneesRecherchees As Range, Cel As Range, PlageDansOnCherche As Range, c As Range
 
' Nettoyage de la feuille de résultat
WsResult.Range(WsResult.Range("A2"), WsResult.Cells(WsResult.Rows.Count, "Z")).ClearContents
 
' Initialisation des variables
LigneBase = 2
LigneResult = 1
DerLig = WSBase.Range("A10000").End(xlUp).Row
Seuil = WsMenu.Range("g12")
Gain = WsMenu.Range("g10")
Perte = WsMenu.Range("g11")
 
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                       ENTREE SUR LE MARCHE                                        '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
Do Until IsEmpty(WSBase.Range("a" & LigneBase))
 
'vérif qu'il s'agit d'une date avant traitement
    If IsDate(WSBase.Range("A" & LigneBase)) Then
        DateEntree = WSBase.Range("A" & LigneBase)
    Else
        MsgBox "La colonne A ne doit contenir que des dates"
        Exit Do
    End If
 
    Dif = WSBase.Range("h" & LigneBase)
    ID = WSBase.Range("i" & LigneBase)
 
'Si on a une erreur dans la colonne différence, on l'ignore et on passe à la ligne suivante
    If IsError(Dif) = True Then
    LigneBase = LigneBase + 1
'La différence est supérieure au seuil d'initation indiqué
    ElseIf Abs(Dif) >= Abs(Seuil) Then
    LigneResult = LigneResult + 1
 
'Libellés dans la feuille ResultatsN
    With WsResult
    .Range("a1") = "Date d'entrée"
    .Range("b1") = "Différence"
    .Range("c1") = "Spread In"
    .Range("d1") = "Prix Euronext In"
    .Range("e1") = "Prix CBOT In"
    .Range("f1") = "Date de sortie"
    .Range("g1") = "Spread Out"
    .Range("h1") = "Prix Euronext Out"
    .Range("i1") = "Prix CBOT Out"
    .Range("j1") = "Gain/Perte"
    .Range("k1") = "Nombre de jours en position"
    .Range("l1") = "Appel de marge moyen"
    .Range("m1") = "Appel de marge maximum"
    .Range("n1") = "ID référent"
    End With
 
'Récupération des données à l'entrée sur le marché
    WsResult.Range("a" & LigneResult) = DateEntree
    DateRef = WsResult.Range("a" & LigneResult)
    WsResult.Range("b" & LigneResult) = Dif
    WsResult.Range("c" & LigneResult) = WSBase.Range("f" & LigneBase)
    WsResult.Range("d" & LigneResult) = WSBase.Range("d" & LigneBase)
    WsResult.Range("e" & LigneResult) = WSBase.Range("e" & LigneBase)
    WsResult.Range("n" & LigneResult) = ID
    End If
 
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                           SORTIE DU MARCHE                                        '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
    'LigneBase2 = LigneBase + 1
    '    Do Until IsEmpty(WSBase.Range("a" & LigneResult))
    '    LigneResult = LigneResult + 1
    '        If WsResult.Range("b" & LigneResult) > 0 Then
    '        Calcul = (-WSBase.Range("f" & LigneBase2) + WsResult.Range("c" & LigneResult))
    '        ElseIf WsResult.Range("b" & LigneResult) < 0 Then
    '        Calcul = (WSBase.Range("f" & LigneBase2) - WsResult.Range("c" & LigneResult))
    '        End If
    '
    '        If Calcul >= Gain Or Calcul <= Perte Then
    '        WsResult.Range("f" & LigneResult) = WSBase.Range("a" & (LigneBase2))
    '        WsResult.Range("g" & LigneResult) = WSBase.Range("f" & (LigneBase2))
    '        WsResult.Range("h" & LigneResult) = WSBase.Range("d" & (LigneBase2))
    '        WsResult.Range("i" & LigneResult) = WSBase.Range("e" & (LigneBase2))
    '        WsResult.Range("j" & LigneResult) = Calcul
    '        Exit Do
    '        End If
    '    LigneResult = LigneResult + 1
    '    Loop
 
LigneBase = LigneBase + 1
Loop
 
'On calcule le nombre de jours ouvrés entre la date d'initiation et la date de sortie afin de connaître le nombre de jours passés en position ouverte
    'WsResult.Range("k" & LigneResult) = WorksheetFunction.NetworkDays(WsResult.Range("A" & LigneResult), WsResult.Range("f" & LigneResult))
 
End Sub

**gFZT82** · 30/07/2012, 11h16

Bonjour,

Première remarque, dans ton code je lis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
        Dif = WsBase.Range("H" & LigneBase)
        ID = WsBase.Range("I" & LigneBase)

Or, dans la feuille "données d'arbitrageN" la colonne "différence (n)" correspond à la colonne G et la colonne "id" correspond à la colonne H.

macro vba, calculs de gains/pertes selon dates et conditions

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager