Ratio Sharpe - Stdev variable

**elpiero** · 05/10/2009, 15h00

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é** · 05/10/2009, 15h19

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** · 05/10/2009, 15h28

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é** · 05/10/2009, 16h18

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** · 05/10/2009, 16h27

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é** · 05/10/2009, 16h47

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

**elpiero** · 05/10/2009, 17h19

Mmm, désolé si je galère vraiment mais je vais essayer de m'expliquer au mieux. La fonction Stdev demande 1 tableau en paramètre ou k est le nbre de jour du mois ici et Arr le tableau d'entier.
Je déclare mon tableau Dim MonTab...
Ret= Stddev est la std deviation que je ramene de la fonction vers le programme principal.
feuil7.cells (...)/ Count me donne mon numérateur correct.
Ret donne Sharpe en fait, Ret stocke la std deviation qui a été calculé des Ret=StdDev(Count,Arr)
Je pense que c'est lié à un problème d'indice du tableau.

Est-ce que je réponds plus ou moins à la question?

**Jean-Philippe André** · 05/10/2009, 17h29

non, en fait ton tableau en question, à quel niveau du code le définis-tu et comment ?

**elpiero** · 05/10/2009, 17h48

A la ligne65 du code si je dis pas de bêtises.
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
Voila ce que me donne en feuille 8 le code. (Ligne 2 les tickers des actions)
Ligne 3, le mois et donc les valeurs des ratios erronés.
Malheureusement pour le premier au lieu de -4,01... je devrais avoir -3,86...

U:SPY U:EWA
août-99 -4,012993462 -3,920985219
sept-99 -4,300884995 -2,944878476
oct-99 -3,247439009 -3,600858944
nov-99 -7,338255152 -3,653199274
déc-99 -6,485614771 -3,952150164
janv-00 -2,640924763 -3,048811733
févr-00 -4,301792336 -3,139361357
mars-00 -3,418480001 -2,918594672
avr-00 -2,911837175 -2,152017356
mai-00 -3,939017778 -2,369285811
juin-00 -6,368820172 -4,942062091
juil-00 -5,792165998 -5,167814185
août-00 -9,804545183 -5,324732821
sept-00 -7,215690167 -5,503430136
oct-00 -3,79303501 -3,900296775
nov-00 -5,012090033 -4,056094786
déc-00 -3,358769432 -3,80385637
janv-01 -3,68170626 -3,96435979
févr-01 -4,676146878 -4,077832688
mars-01 -2,852852172 -3,563879864
avr-01 -2,292942137 -2,277980291

ça aide un peu mieux?

**Jean-Philippe André** · 05/10/2009, 18h00

tu peux nous donner ton fichier excel, parce que là j'ai l'impression que tu tournes en rond

**elpiero** · 05/10/2009, 18h07

Oui je veux bien! Mais il est très volumineux, c'est en excel 2007 en plus, j'arrive pas à le joindre.

**Jean-Philippe André** · 05/10/2009, 18h09

diminue sa taille en ne donnant qu'une ou deux colonnes de données,
et si tu peux l'enregistrer dans un format 2003, ca sera plus facile

**elpiero** · 05/10/2009, 18h40

Je peux rien faire avec 128Ko autorisé. Comment l'envoyer?

**Jean-Philippe André** · 05/10/2009, 18h42

Envoie le sur cjoint.com et poste le lien ici, ca fera l'affaire

**elpiero** · 05/10/2009, 18h59

J'espère que ça ira j'ai essayé de mettre le maximum en taille.

http://cjoint.com/?kfr5tElT1I

merci

**elpiero** · 05/10/2009, 20h01

Le lien marche t-il?
Y'a t-il assez de données et les macros sont -elles possibles?
Les module1 et 3 calcul un autre ratio basé sur le retour et le volume. Le module 4 est le module de Sharpe. En module 5 sera un autre ratio.

Merci de l'aide encore.

**elpiero** · 06/10/2009, 19h16

Probleme résolu à 5 décimales près.

Ratio Sharpe - Stdev variable [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager