Implementing Excel's function SLOPE

[Armin_Tamzarian]

Hello. I am looking for suggestions to implement Excel SLOPE() function in Amibroker:

SLOPE function

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

[Tomasz]

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:

How is LinRegSlope() calculated?

You are missing Math 101. Your formula is wrong. The internet is wide and open LinRegSlope is described precisely in Wikipedia. Really, Google is your friend. https://en.wikipedia.org/wiki/Linear_regression Also take Excel and do the same and you will get the same result. Copy paste this code Plot( C, "Price", colorDefault ); period = 20; Plot( LinearReg( C, period ), "LR", colorBlue ); bi = BarIndex(); sbi = SelectedValue( bi ); b = SelectedValue( LinRegIntercept( C, period ) ); a = Sele…

[Armin_Tamzarian]

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?

[Armin_Tamzarian]

I have found the solution in this forum, sorry I though I had searched thoroughly before posting. Thank you very much!

Calculating half life for mean reverting series

Also you can code just from scratch like below. Code uses ALL bars and produces single value of intercept / slope for all bars. X = /* independent var */; Y = /* dependent var */; bars = BarCount - 1; // use all bars // calculate mean values mX = LastValue( MA( X, bars ) ); mY = LastValue( MA( Y, bars ) ); // differences x - mean(x) and y - mean(y) dX = X - mX; dY = Y - mY; // sums of squared differences sumX2 = LastValue( Sum( dX * dX, bars ) ); sumXY = LastValue( Sum( dX * dY, bars ) ); …

[Armin_Tamzarian]

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 ) );  
}

[fxshrat]

@Armin_Tamzarian

function CalculaBeta( Y, X, len )

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 ) );

[Armin_Tamzarian]

Ok, thanks, it seems that i reinvented the wheel

[Tomasz]

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.