forum.alglib.net

Hi all,

I'm just trying to reproduce the "smooth curve" functionality of MS-Excel. MS say they use CatmullRom splines for that. So I've first tried to use the user friendly wrapper functions Doug Jenkins made publicly available in his Newton-Excel-Bach blog but did not succeed: the derivatives of the CR spline at the very first as well as very last node do not agree with the value used by MS-Excel (you might want to look at my screenshots which Doug kindly published in his blog). In order to identify the problem I've now tried to use the ALGLIB routines contained in the spline1d.bas file directly, but in the sub Spline1DCalc a compilation error occurs because there is a call to a routine named APPeriodicMap which is not declared in any of the ALGLIB *.bas files.

Can somebody please help me to solve this issue?

Regards
Georg

it's me again... I finally found this "Sub APPeriodicMap" in the file "apserv.bas", but only after I had located it in the Python (c-) version... ??? Seems to me that other Germans have superior capabilities in copy&paste... I then had to add functions called "RealMax" and "RealMIN" and now my UDF compiles, hooray...
Therefore, I was now able to upload a screenshot that illustrates the core CR spline problem . The data being interpolated are:
t f(t) + XLS "smooth curve"
0 5
1 -3
2 -5
3 3
4 5
5 -3
6 -5
7 3
8 5

The value of the tension parameter is set to zero; the values of the boundary parameter are given in the screenshot. As far as I have understood so far, the XLS-"smooth curve"-CR spline uses the slopes of the straight lines through the first and second as well as through the last but one and last point, resp., as slope at the first as well as last point, resp. It seems to me that this isn't true in the AlgLib CR-subroutine, is it?

Regards
Georg

ALGLIB CR spline uses slopes of lines which pass through XY[0] and XY[1], XY[0] and XY[2], XY[1] and XY[3], ...., XY[N-3] and XY[N-1], XY[N-2] and XY[N-1]. I don't know what kind of spline Excel uses, maybe there is some subtle difference in the boundary conditions....

Dear Sergey, oh I see, I assumed that you added CR splines in order to emulate Excel... because I've never heard of CR splines outside the Excel world... Meanwhile, I've played a bit with your CR-spline function and observed that the slope at intermediate nodes is set to the slope of a straight line through the adjacent points. But there seems to be a big difference in how to find the slope at the very first as well as very last node. Using non-periodic boundary conditions and a tension of 1/6 nearly exactly reproduces Excels CR spline on all but the very first as well as very last intervals. Your CR spline function uses the straight-line slope in these intervals corrected by the difference between that slope and the tangent slope of the second or one but last node, resp. It seems to me that Excel uses a similar correction algorithm, but works with angles instead of slopes. If I manage to figure that out I'll post it here. Regards Georg

Hi again,

as you can see in the screenshots, XLS does definitely not use simple CR splines to interpolate data when using its "smooth curve" feature. The XLS spline definitely is cubic because the second derivative changes linearly. From looking at the screenshot showing the second derivatives, it seems to me as if two additional nodes (around 3) have been "magically" added and one node (2) has been "magically" shifted by XLS. By "magically" I mean "in a manner as to have the resulting spline run through all the given data points", which indeed is a typical feature of CR splines. Because of the added nodes, the third derivative in the first and last segment, resp., cannot be set to zero and consequently, the XLS cubic spline does not run out parabolically on either end as is true in the case of AlgLib's Catmull-Rom splines. Concluding, I cannot tell at the moment which kind of spline XLS uses in its "smooth curve" procedure, but it is definitely not a CR spline having the data points as nodes. Perhaps, it is a CR spline on the set of given plus "magically" added nodes, but I have not checked that yet.

Cheers
Georg

P.S.: I used the freeware "Engauge Digitizer 4.1" to digitise the XLS smoothed curve using a 24MP graphics file obtained by copying a large chart to the clibboard as picture (no more than 32MP allowed with that software!). There is a website on which the XLS procedure secrets seem to be disclosed: http://www.xlrotor.com/resources/files.shtml, look for "Smooth_curve_bezier_example_file.xls". And I've learned that CR splines are famous in the animation business and implemented in MS-DirectX: http://www.mvps.org/directx/articles/catmull/ .

Hi Georg, As i mentioned in the NewtonExcelBach blog, here's some vba code for the smooth curve:

http://www.excelbanter.com/showthread.php?t=216555

Hi "lori_miller", thank you very much for drawing my attention to your code. I will try to compile it and in case I'll be successfull then publish the results here (obtained when using the same data as above).

Interested to see the results. You may want to add a missing variable declaration to the function: dim n as integer. Further info and examples can be found by following comments and links here:

http://newtonexcelbach.wordpress.com/20 ... c-splines/

Hi Lori, I've just obtained the results. In my example, your UDF named ChartCurve reproduces the results of FEvaluate_Bezier within Excel's numerical accuracy (as can be seen one of the screenshots) and is much more user-friendly than the latter UDF. Moreover, the outputs of both UDFs agree with the curve produced by Excel's smoothing procedure to better than 7E-4 on a full scale of 12 (visually checked when using zoom level of 400% in Excel), so as you stated, they can practically be regarded as being identical. The digitsed curve shows some noise and deviates from the two UDF curves by at most 1E-2.
In order to obtain correct output values, I had to subtract 1 from each x-input value when calling the UDF ChartCurve. 1 equals the minimum scale value of the x-axis in my example. I will try to modify your code to account for that automatically. But as this topic is no more related to AlgLib, I think I'll try to register at excelbanter, too. Cheers Georg

Yes, the results are close in this case but I wouldn't use the other function as the algorithm is slightly inaccurate and this can lead to significant differences when point spacing is not regular, also the function won't compile in Excel 2010 64-bit version.

As an example try entering in the original xlrotor Bezier file the following (x,y) values in order: (9,10) (9.5,12) (10,12) (12,7). The y values are incorrect for x between 9.5 and 10.

I agree choosing base as one might be better, post a response in an excel forum if you want to disuss further. Thanks for the feedback.