VBA-Beta calculation with condition and build-in function application

[QuXiangjie]

Always put the function in to the module

Function Beta(StockReturns As Range, MarketReturns As Range) As Variant
    ' Check if the number of stock returns matches the number of market returns
    If StockReturns.Rows.Count <> MarketReturns.Rows.Count Then
        Beta = CVErr(xlErrValue) ' Return an error if counts don't match
        Exit Function
    End If

    ' Calculate the covariance between stock and market returns
    Dim Covariance As Double
    Covariance = Application.WorksheetFunction.Covar.S(StockReturns, MarketReturns)

    ' Calculate the variance of market returns
    Dim MarketVariance As Double
    MarketVariance = Application.WorksheetFunction.Var.S(MarketReturns)

    ' Calculate beta
    Beta = Covariance / MarketVariance
End Function

[QuXiangjie]

AAPL.csv This data is from Yahoo finance website of apple company, The data is monthly and a 5 yr time period. I will the adjusted close data to verify this function

Issue: can't download the SP500 data from yahoo finance