Alglib interpolations in C#/Excel-Dna
Some C# code again!
Alglib is a cross-platform numerical analysis and data processing library and it proposes a free edition, very useful to perform operations on matrices, minimizations, data analysis, differential equations… Here is an illustration of how you can expose alglib interpolation methods to Excel (with Excel-DNA).
The alglibnet2.dll is the C# version of the library. You should reference it to your project, and the project .dna file should contain the following mention as well:
<ExternalLibrary Path=”alglibnet2.dll” Pack=”true” />
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 |
using ExcelDna.Integration; namespace SmileofThales { public class Interpolations { public delegate double Interpolate(double[] x, double[] y, double xi); [ExcelFunction( Description = "Cubic Spline 1D Interpolation Method", Category = "Smile of Thales: Interpolations", IsVolatile = false, IsThreadSafe = true)] public static double Interp1DSpline( [ExcelArgument(AllowReference = true, Description = "X Axis", Name = "X Array")] double[] x, [ExcelArgument(AllowReference = true, Description = "Y Axis", Name = "Y Array")] double[] y, [ExcelArgument(AllowReference = true, Description = "Abciss to interpolate", Name = "Xi")] double xi) { alglib.spline1dinterpolant c; alglib.spline1dbuildcubic(x, y, out c); return alglib.spline1dcalc(c, xi); } [ExcelFunction( Description = "Linear Spline 1D Interpolation Method", Category = "Smile of Thales: Interpolations", IsVolatile = false, IsThreadSafe = true)] public static double LinearSplineInterp( [ExcelArgument(AllowReference = true, Description = "X Axis", Name = "X Array")] double[] x, [ExcelArgument(AllowReference = true, Description = "Y Axis", Name = "Y Array")] double[] y, [ExcelArgument(AllowReference = true, Description = "Abciss to interpolate", Name = "Xi")] double xi) { alglib.spline1dinterpolant c; alglib.spline1dbuildlinear(x, y, out c); return alglib.spline1dcalc(c, xi); } [ExcelFunction( Description = "Monotone Spline 1D Interpolation Method", Category = "Smile of Thales: Interpolations", IsVolatile = false, IsThreadSafe = true)] public static double MonotoneSplineInterp( [ExcelArgument(AllowReference = true, Description = "X Axis", Name = "X Array")] double[] x, [ExcelArgument(AllowReference = true, Description = "Y Axis", Name = "Y Array")] double[] y, [ExcelArgument(AllowReference = true, Description = "Abciss to interpolate", Name = "Xi")] double xi) { alglib.spline1dinterpolant c; alglib.spline1dbuildmonotone(x, y, out c); return alglib.spline1dcalc(c, xi); } [ExcelFunction( Description = "CatMull-Rom Spline 1D Interpolation Method" , Category = "Smile of Thales: Interpolations", IsVolatile = false, IsThreadSafe = true)] public static double CatMullRomSplineInterp( [ExcelArgument(AllowReference = true , Description = "X Axis" , Name = "X Array")] double[] x , [ExcelArgument(AllowReference = true , Description = "Y Axis" , Name = "Y Array")] double[] y , [ExcelArgument(AllowReference = true , Description = "Abciss to interpolate" , Name = "Xi")] double xi) { alglib.spline1dinterpolant c; alglib.spline1dbuildcatmullrom(x, y, out c); return alglib.spline1dcalc(c, xi); } [ExcelFunction( Description = "Akima Spline 1D Interpolation Method", Category = "Smile of Thales: Interpolations", IsVolatile = false, IsThreadSafe = true)] public static double InterpCAkima1DSpline( [ExcelArgument(AllowReference = true, Description = "X Axis", Name = "X Array")] double[] x, [ExcelArgument(AllowReference = true, Description = "Y Axis", Name = "Y Array")] double[] y, [ExcelArgument(AllowReference = true, Description = "Abciss to interpolate", Name = "Xi")] double xi) { alglib.spline1dinterpolant c; alglib.spline1dbuildakima(x, y, out c); return alglib.spline1dcalc(c, xi); } private static double LinInterp(double date, double[,] curve) { var high = curve.GetLength(0); if (date <= curve[0, 0]) return curve[0, 1]; for (var i = 0; i < high; i++) if (curve[i, 0] >= date) return curve[i - 1, 1] + (date - curve[i - 1, 0])/(curve[i, 0] - curve[i - 1, 0])* (curve[i, 1] - curve[i - 1, 1]); return 0; } [ExcelFunction( Description = "Linear Interpolation Method", Category = "Smile of Thales: Interpolations", IsVolatile = false, IsThreadSafe = true)] public static double LinearInterp( [ExcelArgument(AllowReference = true, Description = "2 culumns array containing X and Y axis", Name = "XY Array")] double[,] array, [ExcelArgument(AllowReference = true, Description = "Abciss to interpolate", Name = "Xi")] double xi) { return LinInterp(xi, array); } } } |
In addition to the source code, you will find an Excel spreadsheet that implements these analytics. There is a VBA call to these interpolation methods, when you click on the ‘Interpolate’ button.
Alglib C# interpolation methods exposed to Excel/VBA
Source code and Excel files : Interpolations
Leave a Reply