'**********************************************************************************
'************************Sharpe ratio**********************************************
'**********************************************************************************
Function ratio_sharpe(ptf As Range, bench As Range)
Dim nb As Integer
Dim i As Integer
Dim rdt_ptf(), rdt_bench() As Single
Dim rdtmoy_ptf, rdtmoy_bench, sigma_ptf As Single
rdtmoy_bench = 0
rdtmoy_ptf = 0
nb = ptf.Rows.Count 'Nombre de lignes dans le portefeuille
ReDim rdt_ptf(nb - 1)
ReDim rdt_bench(nb - 1)

'Matrix calculating the returns of the portfolio and the benchmark
For i = 1 To nb - 1
    rdt_ptf(i) = (ptf(i + 1) - ptf(i)) / ptf(i)
    rdtmoy_ptf = rdt_ptf(i) + rdtmoy_ptf
Next i
rdtmoy_ptf = rdtmoy_ptf / (nb - 1)
For i = 1 To nb - 1
    rdt_bench(i) = (bench(i + 1) - bench(i)) / bench(i)
    rdtmoy_bench = rdt_bench(i) + rdtmoy_bench
Next i
rdtmoy_bench = rdtmoy_bench / (nb - 1)

'Portfolio volatility Calculus
sigma_ptf = 0
For i = 1 To nb - 1
    sigma_ptf = ((rdt_ptf(i) - rdtmoy_ptf) ^ 2) + sigma_ptf
Next i
sigma_ptf = sigma_ptf / ((nb - 1) ^ 2)
ratio_sharpe = (rdtmoy_ptf - rdtmoy_bench) / sigma_ptf
End Function