Ratio Sharpe - Stdev variable

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

Citation:

---

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:

---

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.

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

Code:

---

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 :calim2:

à 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 ?

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.

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 :ccool:

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

Code:

---

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

---

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...

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

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?

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

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?

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

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

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 :)

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

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

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

http://cjoint.com/?kfr5tElT1I

merci

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.

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