Hello. I am looking for suggestions to implement Excel SLOPE() function in Amibroker:
Excel's function SLOPE loads 2 arrays of data, x and y, so it gives you the slope of the scatter plot of these two arrays. Amibroker's function gives you the slope of 1 array only. Maybe there is a way to replicate excel with Amibroker's function, something like:
LinRegSlope is a MOVING (end-point) linear regression slope - like moving average - you are getting ARRAY as an output. Slope() in Excel isn't moving. You are getting just one value (scalar). To generate moving linear regression in Excel you need to place several Slope() calls using moving window in consequent cells.
In LinRegSlope() the the "X" array is simply BarIndex() (but moving - counting from 0...Range-1). "Y" is the data array you pass to LinRegSlope().
Thank you. I understand the difference, array/scalar is not an issue since I can use LastValue() of the LinRegSlope() output if needed or alternatively drag rows in Excel if I want a moving solution.
From your answer I understand that there is no way to implement a 2 variable linear regression slope using Amibrokers' LinRegSlope() function since as you just mentioned first array "X" is occupied with the barindex(), and that leaves only one array "Y" available. If there is a way to combine "X" and "Y" to insert them in LinRegSlope() and get the Excel result for SLOPE(X,Y) I have not found it.
I will rephrase the question. Can I use any available Amibroker function to implement the following formula or do I have to code it in a loop bar by bar?
In case it is of any help to others this is the Amibroker function that replicates Excel's SLOPE(Y,X) with reasonable accuracy in a sliding window of data for two variables X and Y.
//THIS FUNCTION RETURNS THE 2 VARIABLE LINEAR REGRESSION SLOPE
function CalculaBeta( Y, X, len )
return Nz( ( len * Sum( Y * X, len ) - Sum( Y, len ) * Sum( X, len ) )
/ ( len * Sum( X ^ 2 , len ) - Sum( X, len ) ^ 2 ) );