Excel VBA: User defined function for stock volatility

[HockeyGuy4433]

Hi everyone,

I'm trying to create a function in VBA to find the annual volatility of a specific stock price. I have all of the numbers on my worksheet titled "Historical Copper Prices", I'm just having a hard time defining my ranges and executing the function.

'N' is around 150 right now but I'd prefer to have it from i = 3 to LastRow (Column A)
PriceRange will be from i = 3 to LastRow (Column C)

I'm running Excel 2016 on a MacBook Pro (MacOS Sierra 10.12.16), I realize there are some limitations but I think this should be pretty straightforward.

Option Explicit


Function StockVol(N, PriceRange)


' Calculates annualized volatility from price data.
' N = Number of quarters of data to use
' PriceRange = range of prices


Dim i, QtrlyRet()
ReDim QtrlyRet(N - 1)


' Code to calculate quarterly returns


    For i = 4 To N - 1
        QtrlyRet(i) = Log(Sheets("HistoricalCopperPrices").Cells(i, 3).Value / Sheets("HistoricalCopperPrices").Cells(i + 1, 3).Value)


    Next
            
' Calculate and annualize standard deviation


StockVol = Application.StDev(QtrlyRet) * Sqrt(250)


End Function