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
| Function fnAlpha(r, rm, r0) As Variant
Dim i, j, nb, observ As Integer
Dim beta, cov, varm, error, moy, stdev As Double
Set ws = wb.Worksheets("titres")
Set wer = wb.Worksheets("er")
nb = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1
observ = wer.Cells(Rows.Count, 2).End(xlUp).Row - 1
varm = WorksheetFunction.stdev(rm) * (36) ^ 0.5
cov = WorksheetFunction.Covar(r, rm) * 36
beta = cov / varm
error = r - (r0 - beta * (rm - r0))
moy = WorksheetFunction.Average(error)
stdev = WorksheetFunction.stdev(error)
fnalpha= (moy,stdev)
End Function |
Partager