User Defined Function for stock variance

[bilalalam]

Hello,

I need help in creating a custom function that will calculate variance of a stock using its raw prices. This means that the returns calculation will take place within the function.

I have written the code, but its not giving the right answer, could anyone please guide me?
Thanks in Advance


Function portfolio_var(Asset2 As Range)


Dim LFirstRow2, LLastRow2 As Integer
Dim LCurrentRow2 As Integer
Dim LPrevPrice2 As Integer
Dim LTotal2 As Double
Dim LCount2 As Integer


'Determine first and last row to average
LFirstRow2 = Asset2.Row
LLastRow2 = LFirstRow2 + Asset2.Rows.Count - 2




'Determine previous price
LPrevPriceRowFirst2 = Asset2.Row + 1
LPrevPriceRowLast2 = LPrevPriceRowFirst2 + Asset2.Rows.Count - 2


'Initialize variables
LTotal2 = 0
LCount2 = 0
sumsqr = 0
avg = 0


'Move through each cells in the range and include in average calculation
For LCurrentRow2 = LFirstRow2 To LLastRow2
For LPrevPrice2 = LPrevPriceRowFirst2 To LPrevPriceRowLast2


LTotal2 = LTotal2 + WorksheetFunction.Ln(Cells(LCurrentRow2, 5) / Cells(LPrevPrice2, 5))
LCount2 = LCount2 + 1
Next
Next


avg = LTotal2 / LCount2


For LCurrentRow2 = LFirstRow2 To LLastRow2
For LPrevPrice2 = LPrevPriceRowFirst2 To LPrevPriceRowLast2


sumsqr = sumsqr + (WorksheetFunction.Ln(Cells(LCurrentRow2, 5) / Cells(LPrevPrice2, 5))) ^ 2


Next
Next


portfolio_var = (((LTotal2) ^ 2 / LCount2) - sumsqr) / (LCount2 - 1)

End Function

 

[James006]

Hello,

Take a look at Excel built-in functions ... with a wide choice Variance, Standard Deviation, Normal Distribution, etc ...