Curve fitting: Reproducing Excel

ALGLIBNewbie · **Joined:** Wed Jun 16, 2010 2:05 pm **Posts:** 7

Hi there.

I have a beginners question. I have some data: independent values x and dependent values y.
When pasting the mere values into Excel, I instantly receive a fitting curve by simply declaring what column is what. Looks like this.

Attachment:

scre1.PNG [ 29.9 KiB | Viewed 22945 times ]

Is ALGLIB a candidate to reproduce this? If so,
...how fast will it do this job. Instantly as Excel?
...what ALGLIB function should I go for? This looks most promising: http://www.alglib.net/interpolation/lea ... hp#header2 isn't it?

Sorry for these newbie's questions. I hope you pros can help me out.

Cheers
Andr?

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

Yes, ALGLIB can do such things and even more :) But it is not intended to be fast-and-convenient replacement for Excel's functions. It can't be called from GUI, for example. Some code should be written if you want to use ALGLIB.

Returning to your question, yes, these functions are exactly what you need. Unfortunately, VBA version doesn't have examples yet (although they will be introduced in one of the next releases), but you may look for examples in other programming languages. Pascal, for example, is very easy to read.

ALGLIBNewbie · **Joined:** Wed Jun 16, 2010 2:05 pm **Posts:** 7

Thank you, Sergey.

Actually I am looking for a way to code a solution. In fact, some guys (a mathematician and a physicist) around my local work space were so kind to supply me with GUI solutions. These do instantly calculate coefficients to a specific dataset. Though handing me this was very kind, it doesn't address my problem. That is that I need a generic solution to all kind of curves displayed above (the only constraint there is - at least there is one constraint!).

Side note: The kind of curves I'll be dealing with have this form:

Attachment:

scre2.PNG [ 3.81 KiB | Viewed 22929 times ]

So I am quite glad I found this library and glad about your confirmation. The software will be written in C++, alas I can't make much of the ALGLIB sample code. First a C++ programmer isn't automatically a C programmer and the interfaces are somewhat awkward to me. My main struggle is
(a) how to correctly set up my data rows in terms of code feeding, and
(b) what function to choose. I checked stuff like spine and polynomial handling.

http://www.alglib.net/interpolation/spline3.php

http://www.alglib.net/interpolation/polynomial.php

Right now it is way more important to get a grip on (a). I can deal with what algorithm to choose later.
As not being a mathematician I have a hard time figuring out what data the functions need to have and how to feed them. And I hope, you guys laugh about this - means you could give me a hand here.

Getting specific:
I consider the form of the above type of curves being: f(x) = [ax^4] + bx^3 + cx^2 + dx + e / Objections? Let me know.
My basing data is always a set of N x-values and N f(x)-values, which are just N pairs of values measured by someone else.
When checking a ALGLIB function, say the nonlinear least fit algorithm, the sample code (_demo_lsfit_nonlinear.cpp) demands this data:

Code:

    int m;
    int n;
    ap::real_1d_array y;
    ap::real_2d_array fmatrix;
    ap::real_2d_array cmatrix;
    lsfitreport rep;
    int info;
    ap::real_1d_array c;
    int i;
    int j;
    double x;
    double a;
    double b;

What is what? Could you give or point to a description?
Would greatly appreciate any hints here.

Cheers
Andr?

PS: I know this is very basic. If I could eventually make this lib accessable, I'd love to write a beginners tutorial designed for dummies and non-mathematicians like me.

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

If you want to approximate curve by something simple, you should use either spline fitting or polynomial fitting in barycentric form. Barycentric fitting is flexible and has average stability for powers up to 10-15, but current version of ALGLIB can't convert polynomial from barycentric form to powers of X. So if you don't want to have black-box function, you can fit by Hermite splines - they can easily be converted from black-box representation to sum of piecewise cubic functions.

Code:

void spline1dfithermite(const ap::real_1d_array& x,
     const ap::real_1d_array& y,
     int n,
     int m,
     int& info,
     spline1dinterpolant& s,
     spline1dfitreport& rep);

Here:
* x are X-values
* y are Y-values
* n is number of X-values (Y-values)
* M is number of basis functions, see below
* info is completetion code
* s is interpolant itself
* rep is detailed report

Number of basis functions must be at least 4, and it is number of degrees of freedom. If you want to reproduce curve like which was posted, you may try with 10-20 degrees of freedom (depending on how precisely you want to reproduce it). Too many degrees of freedom is not good, because a) it is slow, and b) when we have more degrees of freedom than we have data, we can't build good fit.

But are you really needs fitting? It is inexact procedure by its nature. May be you will have better results with spline interpolation?

ALGLIBNewbie · **Joined:** Wed Jun 16, 2010 2:05 pm **Posts:** 7

Thank you for your hints!

I still don't have a grip on this, but the door has opened up quite a bit.

Sergey.Bochkanov wrote:

But are you really needs fitting? It is inexact procedure by its nature. May be you will have better results with spline interpolation?

No, I don't. If spline interpolation is doing the job better, I'd go for that. My main concern is still how to correctly use the lib and how to supply my data (see columns at first pic). (I'll get tons of x, f(x) - column data. And to each of these columns pairs the software ought to deliver the curve.)

Your hints have helped a lot in that matter. Next I try to ask specific questions.

Cheers

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

AlglibNewbie - I have written some Excel UDFs that call the AlgLib spline functions, that you can download from:

http://newtonexcelbach.wordpress.com/20 ... functions/

All the code is open source, so you can either use the functions as they are, or use them as a model for your own work.

ALGLIBNewbie · **Joined:** Wed Jun 16, 2010 2:05 pm **Posts:** 7

Thanks Doug!
This looks like good work. I'll surely have it under decent scrutiny once I'll have more time for this project.

Right now I fail with the basic setup. My first try was to follow Sergey's suggestion:

Code:

void spline1dfithermite(...)

But given (a) I am not a mathematician and (b) the data I have is solely the stuff visible in the first pic above (one column x data, one column f(x) data) I ended up having this test code:

Code:

#include "stdafx.h"
#include "ap.h"
#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include "spline1d.h"

double xDoubles[] = {
0.0, 
1.2, 
2.3, 
...
62.5
};

double yDoubles[] =
{
0.0,
4.9,
10.1,
...
121.5
};

int _tmain(int argc, _TCHAR* argv[])
{
   // Input objects
   ap::real_1d_array xValues;
   ap::real_1d_array yValues;
   int numberOfValues = 56;
   xValues.setcontent(0, numberOfValues, xDoubles);
   yValues.setcontent(0, numberOfValues, yDoubles);
   int numberOfFunctions = 5;

   // Return objects
   int info = 0;
   spline1dinterpolant interpolant;
   spline1dfitreport fitReport; 

   spline1dfithermite(xValues, yValues, numberOfValues, numberOfFunctions, info, interpolant, fitReport);

   if (info > 0)
   {
            // Check values
            ...
   }
   else
   {
            // Program ends here with info=-2
            // meaning execution failed! (?)
   }

   return 0;
}

This is very humble, I know. Obviously I need some basic hints. Where am I going wrong?

Appreciate your efforts. And Sergey, though I haven't access to your lib yet, from studying the code I can tell that you did a great job!

ALGLIBNewbie · **Joined:** Wed Jun 16, 2010 2:05 pm **Posts:** 7

Hmmm... No one? Can't anybody help me?

Do you need more information? Is this confusing?

Don't want to abandon this project. Alas I must in case this Lib don't do the maths for me.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

ALGLIBNewbie - I don't have time to look at the C++ version, but it seems to work in VBA. One thing is that it seems that M (number of functions) needs to be an even number, so try your code with M = 4 or 6!

Here's the VBA code:

Code:

Function HSplineFit1DA(XA As Variant, YA As Variant, M As Long, Optional XIA As Variant) As Variant
Dim FitResA() As Double, NumXRows As Long, NumXIrows As Long, i As Long, J As Long
Dim XAD() As Double, YAD() As Double, Rtn As Variant, INFO As Long
Dim C1 As Spline1DInterpolant
Dim Rep As Spline1DFitReport

If IsMissing(XIA) = True Then
Rtn = GetSplineData(XA, YA, XAD, YAD, NumXRows)
Else
Rtn = GetSplineData(XA, YA, XAD, YAD, NumXRows, XIA, NumXIrows)
End If

If Rtn <> 0 Then
HSplineFit1DA = Rtn
Exit Function
End If
'Spline1DFitHermite
Spline1DFitHermite XAD, YAD, NumXRows, M, INFO, C1, Rep

If IsMissing(XIA) = True Then
ReDim FitResA(1 To M - 3, 1 To 4)
For i = 1 To M - 3
 For J = 1 To 4
FitResA(i, J) = C1.C((i - 1) * 4 + J - 1)
Next J
Next i

Else
ReDim FitResA(1 To NumXIrows, 1 To 1)
For i = 1 To NumXIrows
FitResA(i, 1) = Spline1DCalc(C1, XIA(i, 1))
Next i
End If

HSplineFit1DA = FitResA

End Function

GetSplineData is my VBA routine to convert the input variant arrays into the correct form for the AlgLib VBA routines.

ALGLIBNewbie · **Joined:** Wed Jun 16, 2010 2:05 pm **Posts:** 7

Doug Jenkins wrote:

ALGLIBNewbie - I don't have time to look at the C++ version, but it seems to work in VBA. One thing is that it seems that M (number of functions) needs to be an even number, so try your code with M = 4 or 6!

Thank you very much, Doug! Indeed the number of functions set to an even number makes my code run. Boy, I can't believe days went by with all it needed was setting "5" to "6".

Okay, this means I can address now the spline1dfithermite function of ALGLIB. On to the next step, that is the maths. Again, I have no clue whether spline1dfithermite is actually the function that suits my mathematical problem. To recap: My only input is 56 x-values and corresponding 56 f(x)-values. I need a math function that displays the curve just as Excel does. I suspect the form of the function to be something like f(x) = [ax^5 + bx^4] + cx^3 + dx^2 + ex + f. Means, I actually only need the coefficients a, b, c, d, e and f calculated.
So now where I get some results from spline1dfithermite I give it a try to hopefully do that.

The obvious main return object is spline1dinterpolant.

Code:

struct spline1dinterpolant
{
    bool periodic;
    int n;
    int k;
    ap::real_1d_array x;
    ap::real_1d_array c;
};

I set the number of functions M which I learned must be an even number to 6. And after executing spline1dfithermite with my 56 x/y data columns spline1dinterpolant supplies these values:

ap::real_1d_array c delivers:

Attachment:

scre1.PNG [ 2.51 KiB | Viewed 22818 times ]

ap::real_1d_array c delivers:

Attachment:

scre2.PNG [ 574 Bytes | Viewed 22818 times ]

n is 4, k is 3, and periodic is false.
And I have only but one more question and we're done: What does all this mean? Is c the list of coefficients? That would be great.

Doug Jenkins wrote:

GetSplineData is my VBA routine to convert the input variant arrays into the correct form for the AlgLib VBA routines.

I studied your VBA code briefly. I can somewhat read what's going on but the mathematical background fails me.
One final remark: I could be light years away from the solution. But please don't give up on me, hahaha... I think it will be most valuable to the LIB-forum if you math pros keep giving a hand to absolute outsiders.

forum.alglib.net

Forum rules

Curve fitting: Reproducing Excel

Who is online