General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Linear regression function for excel

Discussion in 'Technical Analysis' started by kalzayani, Oct 19, 2006.

1. ### kalzayani

made this function for u guys...
sort of explained
GL

Function LinearRegression(VarInputRange As Range, DateInputRange As Range, iTemList As Variant) As Variant

' This function is writen by Khalid Alzayani kalzayani@yahoo.com
' returns the Regression parameter from InputRange to regress with DateInputRange
' select the desired target range for the function and
' enter as an array function with Ctrl+Shift+Enter.
' a is the constant, b is the slope
' mse is error
ReDim iTemList(4) As Variant

b = (Application.SumProduct(VarInputRange, DateInputRange) - Application.Sum(VarInputRange) * Application.Sum(DateInputRange) / Application.Count(DateInputRange)) / (Application.SumProduct(DateInputRange, DateInputRange) - Application.Sum(DateInputRange) ^ 2 / Application.Count(DateInputRange))
a = Application.Average(VarInputRange) - b * Application.Average(DateInputRange)
SSy = (Application.SumProduct(VarInputRange, VarInputRange) - Application.Sum(VarInputRange) ^ 2 / Application.Count(VarInputRange))
SSxy = (Application.SumProduct(VarInputRange, DateInputRange) - (Application.Sum(VarInputRange) * Application.Sum(DateInputRange)) / Application.Count(DateInputRange))
SSx = (Application.SumProduct(DateInputRange, DateInputRange) - Application.Sum(DateInputRange) ^ 2 / Application.Count(DateInputRange))

mse = ((SSy - b * SSxy) / Application.Count(DateInputRange)) ^ 0.5

iTemList(2) = mse ' sqr root of Mean squared error around the Line
iTemList(1) = b
iTemList(0) = a
LinearRegression = iTemList

'============== Copy all this into a macro in excel
' by going to macros and just creat any name and copy this over the whole thing in VBA Editor
' Then just to insert function and go to user defined

End Function

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: