C# to mimic Excel SLOPE function

Question

I'm trying to calculate the slope of two data lists. You can easily calculate this in EXCEL using the SLOPE function. =SLOPE(A1:A100, B1:B100). I'm trying to mimic this function in C# WinForm. Here is my code, it can calculate something, but not the correct number that you would get from the Excel function. Please help me find the error here. Thanks so much!

private double Getslope(List<double> ProductGrossExcessReturnOverRFR, List<double> primaryIndexExcessReturnOverRFR, int months, int go_back = 0)
{
    double slope = 0;
    double sumx = 0, sumy = 0, sumxy = 0, sumx2 = 0;
    for (int i = ProductGrossExcessReturnOverRFR.Count - 1 - go_back; i > ProductGrossExcessReturnOverRFR.Count - (1 + months + go_back); i--)
    {
        sumxy += ProductGrossExcessReturnOverRFR[i] * primaryIndexExcessReturnOverRFR[i];
        sumx += ProductGrossExcessReturnOverRFR[i];
        sumy += primaryIndexExcessReturnOverRFR[i];
        sumx2 += ProductGrossExcessReturnOverRFR[i] * ProductGrossExcessReturnOverRFR[i];      
    }

    return slope = 1 / (((sumxy - sumx * sumy / months) / (sumx2 - sumx * sumx / months)));
}

Test data:
{1.085231224, 2.335034309, 0.346667278} and
{3.185231224,3.705034309 , -0.883332722} should have slope of 0.3373 if you calculate in Excel using =SLOPE function. But my code produces 0.47 somehow...

Answer 1

I think your formula is wrong

According to the Excel documentation the formula for SLOPE is

Note also that the first argument to the function is the the y values.

It's unclear how goback and months apply, but it looks like this might work:

private double Getslope(List<double> ProductGrossExcessReturnOverRFR, 
                        List<double> primaryIndexExcessReturnOverRFR, 
                        int months, 
                        int go_back = 0)
{
    // calc # of items to skip
    int skip = ProductGrossExcessReturnOverRFR.Count - go_back - months;

    // get list of x's and y's
    var ys = ProductGrossExcessReturnOverRFR.Skip(skip).Take(months);
    var xs = primaryIndexExcessReturnOverRFR.Skip(skip).Take(months);

    // "zip" xs and ys to make the sum of products easier
    var xys = Enumerable.Zip(xs,ys, (x, y) => new {x = x, y = y});

    double xbar = xs.Average();
    double ybar = ys.Average();

    double slope = xys.Sum(xy => (xy.x - xbar) * (xy.y - ybar)) / xs.Sum(x => (x - xbar)*(x - xbar));

    return slope;
}