Discussion :

[elpiero]

Bonjour tout le monde,
Je cherche à résoudre un problème depuis plusieurs semaines d'un mémoire. J'ai eu l'aide de plusieurs programmeur VBA mais sans solution. Il s'agit de boucles pour un ratio financier.

J'ai donc une base de données en feuille 2 d'excel avec en Colonne A des dates allant sur 10 ans de la ligne 3 à 2612 ( du 20/08/1999 au 20/08/2009). En suite j'ai en colonne B le prix d'une action et en colonne C son volume, commencant en ligne3( car en ligne 1 et 2, il s'agit du nom et du ticker de l'action). Ceci pour 257 actions, l'action 2 a donc son prix en colonne D et volume en colonne E... ceci allant jusqu'en colonne 515. En colonne 516 , j'ai un taux quotidien commençant en ligne 3 appelé Rf, pour charque jour jusqu'en ligne2612.

Je souhaite effectuer des boucles pour un ratio dit de Sharpe.
Le numérateur se décompose en 2/
- La première partie est appelé le retour de l'action qui est le prix en t - le prix en t-1 divisé par le prix en t-1 ceci pour tours les jours (tt les lignes).
- La deuxième partie est le Rf à soustraire du retour du jour.
Soit pour 1 action , le retour du 23 aout - le Rf du 23 aout ...
La somme de ses soustractions se divise par le nombre de jour dans le mois (qui varient selon les mois) afin d'obtenir un numérateur mensuel.

Une fois le numérateur d'aout obtenu par example, il faut le divisé par la standard deviation des retour du mois en questions afin d'obtenir le fameux ratio de Sharpe pour aout... Si je m'explique bien il ya donc pour 10 ans, 120 ratios pour 1 action et ceci pour 257 actions. ( Le volume n'intervient pas dans ce ratio)
J'espère pas trop mal m'expliquer.
Voici une partie de la feuille2

Name S&P.DEPY S&P.DEPY. BY VOLUM MSCI AUST MSCI AUSTVOLUME
Code U:SPY(P) U:SPY(VO)U:EWA(P)U:EWA(VO)
20/08/1999 133,9063 3663,7 10,625 2,6
23/08/1999 136,4688 5637,8 10,8125 2,1
24/08/1999 136,9688 9279,5 10,75 12,2
25/08/1999 138,375 6005,3 10,4375 11,6
26/08/1999 136,7188 4322,3 10,4375 16,9
27/08/1999 135,0625 6247,4 10,375 3,2
30/08/1999 132,5625 4652 10,25 4,1
31/08/1999 132,0625 11453,7 10 15,6
01/09/1999 133,6875 6863,9 10,4375 321,3
02/09/1999 132,1094 10896,3 10,25 7,5
03/09/1999 135,9688 9160,8 10,375 17,8
06/09/1999 135,9688 10,375
07/09/1999 135,4688 4560,8 10,375 19,7
08/09/1999 134,8125 6159,6 10,375 10
09/09/1999 134,75 6177,8 10,4375 4,9
10/09/1999 135,875 2934,5 10,5 8,8
13/09/1999 134,9531 2320,5 10,5625 4,4
14/09/1999 134,0625 3736 10,5 7,8
15/09/1999 131,9375 6984,5 10,1875 11,4
16/09/1999 132,4531 15377,6 10,1875 6,5
17/09/1999 133,75 8542,3 10,375 1,7
20/09/1999 133,5625 2803,9 10 5,6
21/09/1999 130,75 9335,2 9,9375 16,2
22/09/1999 130,6094 13009,1 10,0625 4,2
23/09/1999 127,875 12204,2 10,125 7,6
24/09/1999 127,75 13792,9 10,1875 25,7
27/09/1999 128,5938 6970,3 10,125 38,4
28/09/1999 128,3438 11063,4 10,3125 9,4
29/09/1999 126,8125 7580,3 10,25 19,9
30/09/1999 128,75 7498,9 10,3125 29
01/10/1999 128,4688 11127,1 10,25 6,9
04/10/1999 130,75 6341,6 10,1875 1,5

Je joins également, le code actuel qui ne marche pas. Je pense que le problème vient du count, ou du tableau avec j. Il reste dans le code les lignes liés au volume utilisé pour un autre ratio dans un autre module.

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
Function StdDev(ByVal k As Long, Arr() As Single)
     Dim i As Integer
     Dim avg As Single, SumSq As Single
 
     avg = Mean(k, Arr)
     For i = 1 To k
          SumSq = SumSq + (Arr(i) - avg) ^ 2
     Next i
     StdDev = Sqr(SumSq / (k - 1))
End Function
 
Function Mean(ByVal k As Long, Arr() As Single)
     Dim Sum As Single
     Dim i As Integer
 
 
     Sum = 0
     For i = 1 To k
         Sum = Sum + Arr(i)
     Next i
     Mean = Sum / k
End Function
 
Private Sub MiseAZero(Row_Dest As Integer)
    prix_column = 2
    Do While (prix_column <= 515)
        Feuil7.Cells(Row_Dest, prix_column) = 0
        prix_column = prix_column + 2
    Loop
End Sub
 
Sub Feuil3_Bouton2_Clic()
 
    i = 1
    J = 1
 
    prix_row = 4
    prix_column = 2
    volume_row = 4
    volume_column = 3
 
    Row = 3
    Column = 1
    Row_Dest = 3
    Month_XLS = ""
 
    Month_XLS = Right(Feuil2.Cells(Row, Column), 7)
    Feuil7.Cells(Row_Dest, Column) = Month_XLS
    Do While (prix_column <= 515)
        Feuil7.Cells(1, prix_column) = Feuil2.Cells(1, prix_column)
        Feuil7.Cells(2, prix_column) = Split(Feuil2.Cells(2, prix_column), "(")
        Feuil7.Cells(Row_Dest, prix_column) = 0
        prix_column = prix_column + 2
    Loop
    Count = 0
    Row = Row + 1
    Deb = Row
    Do While (Row <= 2612)
        Month_XLS = Right(Feuil2.Cells(Row, Column), 7)
        If (Month_XLS <> Right(Feuil7.Cells(Row_Dest, Column), 7)) Then
            prix_column = 2
            Do While (prix_column <= 515)
                J = 1
                L = Deb
                Dim MonTab(1 To 1000) As Single
                Do While (J <= Count)
                    MonTab(J) = Feuil8.Cells(L, prix_column)
                    J = J + 1
                    L = L + 1
                Loop
                Ret = StdDev(Count, MonTab)
                If (Ret <> 0) Then Feuil7.Cells(Row_Dest, prix_column) = (Feuil7.Cells(Row_Dest, prix_column) / Count) / Ret
                prix_column = prix_column + 2
            Loop
            Count = 0
            Deb = Row
            Row_Dest = Row_Dest + 1
            MiseAZero (Row_Dest)
            Feuil7.Cells(Row_Dest, Column) = Month_XLS
        End If
        Count = Count + 1
        prix_column = 2
        volume_column = 3
        Do While (prix_column <= 10)
            If ((Not IsEmpty(Feuil2.Cells(prix_row - 1, prix_column))) And (Not IsEmpty(Feuil2.Cells(prix_row, prix_column))) And (Not IsEmpty(Feuil2.Cells(volume_row, volume_column))) And (Feuil2.Cells(volume_row, volume_column) <> 0)) Then
                retour = (Feuil2.Cells(prix_row, prix_column) - Feuil2.Cells(prix_row - 1, prix_column)) / Feuil2.Cells(prix_row - 1, prix_column)
                Feuil7.Cells(Row_Dest, prix_column) = Feuil7.Cells(Row_Dest, prix_column) + retour - (Feuil2.Cells(prix_row, 516) / 100)
                Feuil8.Cells(prix_row, prix_column) = retour
                Feuil8.Cells(prix_row, prix_column + 1) = Feuil2.Cells(prix_row, 516) / 100
            End If
            prix_column = prix_column + 2
            volume_column = volume_column + 2
        Loop
        prix_row = prix_row + 1
        volume_row = volume_row + 1
        Row = Row + 1
    Loop
End Sub

Merci à l'avance pour votre aide.

[Jean-Philippe André]

salut,
est-ce volontaire de ta part de réinventer la roue ?
Les worksheetfunction sont toute dédiées

Code :

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

1
2
worksheetfunction.StDev
worksheetfunction.Average

par exemple...

concernant les soucis que tu rencontres, sans commentaires dans le code, j'avoue que j'ai pas su aller au bout

à quel niveau as-tu détecté une erreur ?

le résultat final est-il faux ? le code plante-t-il ?

ne peux-tu pas simplifier tout ca avec les worksheetfunction ?

[elpiero]

Non ce n'est pas volontaire. J'ai un soucis avec la stdev et elle utilise des plages et mes plages sont toutes variables. Je n'arrive pas à coder cette fonction de manière variable, c'est pour cela.
Oui désolé de ne pas avoir spécifier, on était deux à faire le code et la personne la plus douée qui n'est pas moi, n'as pas fait trop de commentaire ce pourquoi moi même j'ai du mal.
Le code ne plante pas mais le résultat final est faux. S'il y a moyen de tout simplifier je suis partant Mais je n'y arrive pas.

[Jean-Philippe André]

ok,
l'idée est d'avoir comme paramètres d'entrée des variables de type Range (cellules/plage de cellules)
ce qui permet d'aller beaucoup plus vite dans le calcul, au lieu de faire du ligne à ligne

si on synthétise ca peut etre tres rapide
Exemple :

Code :

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

1
2
3
4
5
6
7
Function Sharpe_Ratio(MyRange As Range, Rf as Double) As Double
dim EspRend As Double
dim EcartType As Double
EspRend= WorksheetFunction.Avg(MyRange)
EcartType = WorkSheetFunction.StdDev(MyRange)
Sharpe_Ratio = (EspRend - Rf) / EcartType
End Function

[elpiero]

Merci de ta réponse qui m'éclairci déjà, mais j'ai un petit souci. Comme faire pour rendre MyRange dynamique. Car chaque mois a un nombre de jours différents. Une fois je fais la moyenne de 28 retours, une autre fois la moyenne de 30 retours...

[Jean-Philippe André]

Comment définis-tu ta plage de cellules ?
Comment appelles-tu la fonction Sharpe_Ratio ?