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
| Function OSScal(SerieDates As Variant, SerieReturns As Variant) As Variant
Dim TR As Variant
Dim TD As Variant
Dim dd As Date
Dim df As Date
Dim n As Integer
Dim ad As Integer
Dim af As Integer
Dim V() As Variant
Dim nby As Integer
Dim i As Integer
Dim dda As Date
Dim dfa As Date
Dim maligdeb As Variant
Dim maligfin As Variant
'----------On créé les variables------------------------------------------------------
TR = SerieReturns
TD = SerieDates
n = UBound(TD)
dd = TD(1, 1)
df = TD(n, 1)
ad = Year(dd)
af = Year(df)
nby = af - ad + 1
'------------ On remplit le tableau---------------------------------------------------
ReDim V(1 To nby, 1 To nby)
dfa = df
For i = 1 To nby
V(1, i) = af + 1 - i '------on rempli la première colonne avec les années
'on cherche les dates correspondantes à l'année recherchée
dda = Application.WorksheetFunction.Max(dd, DateSerial(Year(dfa) - 1, 12, 31))
maligdeb = Application.WorksheetFunction.Match(dda, TD, 1)
maligfin = Application.WorksheetFunction.Match(dfa, TD, 1)
V(2, i) = TR(maligfin, 1) / TR(maligdeb) - 1 '----on calcule la perf de la période et on la met dans le tableau
dfa = dda '------on reset pour l'année n-1
Next i
'------------On applique la fonction
OSScal = Application.Transpose(V)
End Function |
Partager