# Implementing Excel's function SLOPE

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:

``````array = ( C2-MA(C2,len) )*( C1-MA(C1,Len) ) / ( C1-MA(C1,len) )^2;
SLOPE = LinRegSlope(array,len);
``````

Which is obviously not working, otherwise I would'nt ask for help Or maybe there is already an Amibroker function for this. Any indication will be appreciated. Thank you

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().

See this post for detailed explanation:

1 Like

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? I have found the solution in this forum, sorry I though I had searched thoroughly before posting. Thank you very much!

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 ) );
}
`````` Not required because....
FYI, Beta is already part of AmiBroker setup and being located in Charts - Indicators. ``````function Beta( BaseSymbol, N )
{
// PART OF CHARTS - INDICATORS - BETA
// by AMIBROKER.COM
Chg = ROC( C, 1 );
ChgBase = ROC( Foreign( BaseSymbol, "C" ), 1 );

return ( N * Sum( Chg * ChgBase , N ) - Sum( Chg, N ) * Sum( ChgBase, N ) ) /
( N * Sum( ChgBase ^ 2 , N ) -  Sum( ChgBase, N ) ^ 2 );
}

symbol = ParamStr( "Symbol", "^NDX" );
Plot( Beta( symbol, Param("Periods", 21, 2, 100 ) ), _DEFAULT_NAME(), ParamColor( "Color", ColorCycle ) );
``````
1 Like

Ok, thanks, it seems that i reinvented the wheel That is why it is important to describe THE GOAL as pointed out in How to ask a good question (what you want to achieve at the end of the day) before focusing on details.

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.