We’ve had several customers ask about porting their Excel model to a .NET language in order to leverage the performance and functionality of NMath or NMath Stats. NMath does have good crossover functionality with Excel making this porting job easier. It is also possible to accelerate your Excel models by calling the NMath .NET assemblies directly from a VBA macro in Excel . This post provides some guidance for porting all or just a portion of your Excel model to C# and NMath.
Excel is designed to interoperate with external assemblies from VBA using COM. Type libraries built in .NET are not directly COM compatible, however all .NET class libraries including NMath and NMath Stats can be made to present a COM interface making Excel interop possible. This interop is acheived by building the COM type library directly from the assembly – no recompiling needed – using a tool shipped with the .NET framework, and then adding this type library as a reference to an Excel sheets’ VBA macro. Because of the many differences between the C# language and VBA, only a small portion of NMath will be accessable from Excel using this procedure, however a simple remedy will be outlined below that can expand the available functionality to all of NMath.
- To build the type library interface to the NMath.dll use the
regasm.exeutility shipped with the .NET framework
The COM compatible type library now resides in the>regasm.exe NMath.dll /tlb:NMathCom.tlb
NMathCom.tlbfile. You will see some warning messages regarding incompatibilities between COM and NMath. - Open a spreadsheet, right click on a sheet tab and choose View Code to open the VBA development environment.
- In the Tools menu select References… and browse to the location of the new NMath type library.
Now the COM compatible portions of NMath are now available for use from VBA. At this point we can code up simple example for generating random numbers in VBA to test out the NMath interoperability
Private Sub TestNMath()
Dim rand As New NMath.RandGenLogNormal
rand.Mean = 50
rand.Variance = 10
Dim i As Integer
For i = 1 To 10
Cells(i, 1) = rand.Next()
Next
End Sub
This simple VBA script populates cells A1:A10 with LogNormal distributed random numbers with a mean of 50 and a variance of 10. This is not so easily achieved within Excel natively.
Because NMath was not designed from the ground up to interoperate with the aging COM standard, many types will not be useable from VBA. All types lacking an empty (default) constructor, generic classes, static methods, and all static classes are not directly usable from VBA. However, any of these incompatible classes or methods can be wrapped in VB and then made available to Excel via COM. We understand many Excel users are not C#/.NET experts and so we are happy to help wrapping any NMath classes or NMath Stats classes you may need for enhancing and accelerating your Excel models.
Porting Excel Models to C# using NMath
As models grow in Excel, they commonly devolve into a bizantine workbook that becomes slow, opaque, and difficult to version, debug and manage. Usually, it is at this point that users start looking at porting the complex portions to another compatible platform, typically VB. These workbooks tend to be business critical applications so the port must be done carefully and in a piecewise fashion. This conservative strategy can be acheived using the following steps.
- Identify the computationally demanding and complex portion of the spreadsheet carefully selecting a separation point where the interface is thin between the workbook and the new VB class.
- Identify all of the input and output cells of this computation.
- Create build a VB wrapper class in a (class library) VB project that wraps the functionality necessary for the port. This class should include methods for loading and returning results in types compatible with VBA.
- Build the library and generate the COM type library and add this new type library to the workbook’s VBA.
- Test the new model in parallel with the existing model.
- Remove the duplicated Excel computation and enjoy the new faster model.
Porting functionality from an Excel model to a VB type library can be facilitated by NMath due to the large crossover in functionality between Excel and NMath Stats. Also, due to NMath’s high performance, once ports are complete, considerable performance gains can be expected. Below is a table of Excel functions and their supporting classes in either NMath Stats or native .NET.
Happy Computing,
-Paul
Resources
- Microsoft ported the Excel financial functions to F#, making them accessible to any VB or C# project. Follow this link to download this library and read the libraries’ documentation and limitations.
- Microsoft’s landing page on the assembly registration tool
regasm.exefor building the COM type libraries. - A little dated but clear and complete article on accessing .NET assemblies from Excel.
Excel Functions Supported in NMath and .NET
Between the NMath and NMath Stats numeric libraries and the .NET framework many of the Excel functions are covered for a port to C#. If you have a math function that you need which is not covered or is not in this list, let us know and we can probably add it to NMath or NMath Stats. Note resources above if financial functions are needed.
| Excel Function | Framework/NameSpace | Class/Method |
|---|---|---|
| ABS | NMath.Core | NMathFunctions.Abs() |
| ACOS | NMath.Core | NMathFunctions.ACos() |
| AND | NMath.Core | NMathFunctions.And() |
| ASIN | NMath.Core | NMathFunctions.ASin() |
| AREAS | No | |
| ASIN | NMath.Core | NMathFunctions.Asin() |
| ASINH | No | |
| ATAN | NMath.Core | NMathFunctions.Atan() |
| ATAN2 | NMath.Core | NMathFunctions.Atan2() |
| ATANH | No | |
| AVEDEV | NMath.Stats | StatsFunctions.MeanDeviation() |
| AVERAGE | NMath.Stats | StatsFunctions.Mean() |
| AVERAGEA | No | |
| BETADIST | NMath.Stats | BetaDistribution class |
| BETAINV | NMath.Stats | InverseCDF method in BetaDistribution class |
| BINOMDIST | NMath.Stats | BinomialDistribution class |
| CALL | .NET | |
| CEILING | NMath.Core | NMathFunctions.Ceil() |
| CELL | No | |
| CHAR | No | |
| CHIDIST | NMath.Stats | ChiSquareDistribution class |
| CHIINV | NMath.Stats | InverseCDF method in ChiSquareDistribution class |
| CHITEST | No | |
| CHOOSE | No | |
| CLEAN | No | |
| CODE | No | |
| COLUMN | No | |
| COLUMNS | NMath.Core | DataFrame class using Cols property |
| COMBIN | NMath.Stats | StatsFunctions.Binomial() |
| CONCATENATE | .NET | |
| CONFIDENCE | NMath.Stats | OneSampleZTest class using LowerConfidenceBound or UpperConfidenceBound properties |
| CORREL | NMath.Stats | StatsFunctions.Correlation() |
| COS | NMath.Core | NMathFunctions.Cos() |
| COSH | NMath.Core | NMathFunctions.Cosh() |
| COUNT | NMath.Stats | StatsFunctions.Count() |
| COUNTBLANK | NMath.Stats | If blanks are represented by NaNs then use StatsFunctions.NaNCount() |
| COUNTIF | NMath.Stats | StatsFunctions.CountIf() |
| COVAR | NMath.Stats | StatsFunctions.Covariance() |
| CRITBINOM | NMath.Stats | BinomialDistribution class |
| DATE | .NET | |
| DATEVALUE | .NET | |
| DAVERAGE | NMath.Stats | StatsFunctions.If() to get Subset then StatsFunctions.Mean() |
| DAY | .NET | |
| DAYS360 | .NET | |
| DB | No | |
| DCOUNT | NMath.Stats | StatsFunctions.CountIf() |
| DCOUNTA | NMath.Stats | If blanks are represented by NaNs then use StatsFunctions.NaNCountIf() |
| DDB | No | |
| DEGREES | No | |
| DEVSQ | NMath.Stats | StatsFunctions.SumOfSquares() |
| DGET | NMath.Stats | StatsFunctions.If() and then index property on resulting DataFrame |
| DMAX | NMath.Stats | StatsFunctions.If() to create Subset then StatsFunctions.Max() |
| DMIN | NMath.Stats | StatsFunctions.If() to create Subset then StatsFunctions.Min() |
| DOLLAR | .NET | |
| DPRODUCT | NMath.Stats | StatsFunctions.If() to create Subset then to DoubleVector() then NMathFunctions.Product() |
| DSTDEV | NMath.Stats | StatsFunctions.If() to create Subset then StatsFunctions.StandardDeviation(BiasType.Unbiased) |
| DSTDEVP | NMath.Stats | StatsFunctions.If() to create Subset then StatsFunctions.StandardDeviation() |
| DSUM | NMath.Stats | StatsFunctions.SumIf() |
| DVAR | NMath.Stats | StatsFunctions.If() to create Subset then toDoubleVector() then NMathFunctions.Variance(BiasType.Unbiased) |
| DVARP | NMath.Stats | StatsFunctions.If() to create Subset then toDoubleVector() then NMathFunctions.Variance() |
| ERROR.TYPE | No | |
| EVEN | .NET | |
| EXACT | .NET | |
| EXP | NMath.Core | NMathFunctions.Exp() |
| EXPONDIST | NMath.Stats | ExponentialDistribution class |
| FACT | NMath.Stats | StatsFunctions.Factorial() |
| FALSE | .NET | |
| FDIST | NMath.Stats | FDistribution class |
| FIND | .NET | |
| FINV | NMath.Stats | InverseCDF method in FDistribution class |
| FISHER | No | |
| FISHERINV | No | |
| FIXED | .NET | |
| FLOOR | NMath.Core | NMathFunctions.Floor() |
| FORECAST | NMath.Stats | LinearRegression class in Stats |
| FREQUENCY | NMath.Stats | StatsFunctions.Mode() for frequency, use StatsFunctions.If() to find the indices. |
| FTEST | NMath.Stats | TwoSampleFTest class in Stats |
| FV | No | |
| GAMMADIST | NMath.Stats | GammaDistribution class |
| GAMMAINV | NMath.Stats | InverseCDF method in GammaDistribution class |
| GAMMALN | NMath.Stats | StatsFunctions.GammaLn() |
| GEOMEAN | NMath.Stats | StatsFunctions.GeometricMean() |
| GETPIVOTDATA | No | |
| GROWTH | NMath.Stats | LinearRegression class in Stats |
| HARMEAN | NMath.Stats | StatsFunctions.HarmonicMean() |
| HLOOKUP | No | |
| HOUR | .NET | |
| HYPERLINK | .NET | |
| HYPGEOMDIST | No | |
| IF | NMath.Stats | StatsFunctions.If() |
| INDEX | NMath.Core | Index properties in vector, matrices, columns and frames |
| INDIRECT | No | |
| INFO | .NET | |
| INT | .NET | |
| INTERCEPT | NMath.Stats | LinearRegression class in Stats |
| IPMT | No | |
| IRR | No | |
| ISBLANK | NMath.Core | Can use NaN to indicate missing value. Then use Double.IsNaN(cell) to verify. |
| ISERROR | No | |
| ISLOGICAL | No | |
| ISNA | No | |
| ISNONTEXT | No | |
| ISNUMBER | No | |
| ISPMT | No | |
| ISREF | No | |
| ISTEXT | No | |
| KURT | NMath.Stats | StatsFunctions.Kurtosis() |
| LARGE | NMath.Stats | StatsFunctions.Sort() with StatsFunctions.Percentile() |
| LEFT | .NET | |
| LEN | .NET | |
| LINEST | NMath.Stats | LeastSquares class |
| LN | NMath.Core | NMathFunctions.Log() |
| LOG | No | |
| LOG10 | NMath.Core | NMathFunctions.Log10() |
| LOGEST | NMath.Stats | LinearRegression class in Stats |
| LOGINV | NMath.Stats | InverseCDF method in LognormalDistribution class |
| LOGNORMDIST | NMath.Stats | LognormalDistribution class |
| LOOKUP | NMath.Stats | IndexOf(), IndicesOf() methods in DataFrame class |
| LOWER | .NET | |
| MATCH | No | |
| MAX | NMath.Stats | StatsFunctions.Max() |
| MAXA | No | |
| MDETERM | NMath.Stats | Use LUFact class to factorize then call Determinant() method to compute. |
| MEDIAN | NMath.Stats | StatsFunctions.Median() |
| MID | .NET | |
| MIN | NMath.Stats | StatsFunctions.Min() |
| MINA | No | |
| MINUTE | .NET | |
| MINVERSE | NMath.Stats | Use LUFact class to factorize then call Inverse() method to compute. |
| MIRR | No | |
| MMULT | NMath.Core | Call Multiply() on a matrix class |
| MOD | .NET | |
| MODE | NMath.Stats | StatsFunctions.Mode() |
| MONTH | .NET | |
| N | No | |
| NA | .NET | |
| NEGBINOMDIST | NMath.Stats | NegativeBinomialDistribution class |
| NORMDIST | NMath.Stats | NormalDistribution class |
| NORMINV | NMath.Stats | InverseCDF() in NormalDistribution class |
| NORMSDIST | NMath.Stats | NormalDistribution class |
| NORMSINV | NMath.Stats | InverseCDF() in NormalDistribution class |
| NOT | .NET | |
| NOW | .NET | |
| NPER | No | |
| NPV | No | |
| ODD | .NET | |
| OFFSET | No | |
| OR | .NET | |
| PEARSON | NMath.Stats | StatsFunctions.Correlation() |
| PERCENTILE | NMath.Stats | StatsFunctions.Percentile() |
| PERCENTRANK | NMath.Stats | StatsFunctions.PercentileRank() |
| PERMUT | NMath.Stats | Factorial() in StatsFunctions |
| PI | .NET System.Math.PI | |
| PMT | No | |
| POISSON | NMath.Stats | PoissonDistribution class |
| POWER | NMath.Stats | NMathFunctions.Pow |
| PPMT | No | |
| PROB | NMath.Stats | PDF methods in all of the distribution classes |
| PRODUCT | NMath.Stats | NMathFunctions.Product() |
| PROPER | .NET | |
| PV | No | |
| QUARTILE | NMath.Stats | StatsFunctions.Quartile() |
| RADIANS | .NET | |
| RAND | NMath.Stats | Many RandomNumberGenerator classes in NMath Core |
| RANK | NMath.Stats | StatsFunctions.Rank() |
| RATE | No | |
| REGISTER.ID | No | |
| REPLACE | .NET | |
| REPT | .NET | |
| RIGHT | .NET | |
| ROMAN | No | |
| ROUND | NMath.Core | NMathFunctions.Round() |
| ROUNDDOWN | NMath.Core | NMathFunctions.Round() |
| ROUNDUP | NMath.Core | NMathFunctions.Round() |
| ROW | NMath.Stats | Row property on DataFrame |
| ROWS | NMath.Stats | Rows property on DataFrame |
| RSQ | No | |
| SEARCH | .NET | |
| SECOND | .NET | |
| SIGN | .NET | |
| SIN | NMath.Core | NMathFunctions.Sin() |
| SINH | NMath.Core | NMathFunctions.Sinh() |
| SKEW | NMath.Stats | StatsFunctions.Skewness() for samples or Skewness properties on Distribution classes. |
| SLN | No | |
| SLOPE | NMath.Stats | LinearRegression class in Stats |
| SMALL | NMath.Stats | StatsFunctions.Sort() and then index or find percentage of count and use StatsFunctions.Percentile() |
| SQRT | NMath.Core | NMathFunctions.Sqrt() |
| STANDARDIZE | NMath.Stats | NormalDistribuion class |
| STDEV | NMath.Stats | StatsFunctions.StandardDeviation(BiasType.Unbiased) |
| STDEVA | No | |
| STDEVP | NMath.Stats | StatsFunctions.StandardDeviation() |
| STDEVPA | No | |
| STEYX | NMath.Stats | LinearRegression class in Stats |
| SUBSTITUTE | .NET | |
| SUBTOTAL | NMath.Stats | Use Slice/Subset/Range to get portion of data then StatsFunctions.Sum(), Or, StatsFunctions.SumIf() |
| SUM | NMath.Stats | StatsFunctions.Sum() |
| SUMIF | NMath.Stats | StatsFunctions.SumIf() |
| SUMPRODUCT | NMath.Stats | Use operator* on vectors then NMathFunctions.Sum() |
| SUMSQ | NMath.Stats | StatsFunctions.SumOfSquares() |
| SUMX2MY2 | NMath.Core | Square each vector using NMathFunctions.Pow(2) then us operator- for difference then NMathFunctions.Sum() |
| SUMX2PY2 | NMath.Core | Square each vector using NMathFunctions.Pow(2) then use operator+ for difference then NMathFunctions.Sum() |
| SUMXMY2 | NMath.Stats | use operator- to find difference then Product(2) then NMathFunctions.Sum() |
| SYD | No | |
| T | No | |
| TAN | NMath.Core | NMathFunctions.Tan() |
| TANH | NMath.Core | NMathFunctions.Tanh() |
| TDIST | NMath.Stats | TDistribution class |
| TEXT | No | |
| TIME | .NET | |
| TIMEVALUE | .NET | |
| TINV | NMath.Stats | InverseCDF() in TDistribution class |
| TODAY | .NET | |
| TRANSPOSE | NMath.Core | Transpose() method on a matrix |
| TREND | NMath.Stats | LinearRegression class in Stats |
| TRIM | NMath.Stats | StatsFunctions.Trim() |
| TRIMMEAN | NMath.Stats | StatsFunctions.TrimmedMean() |
| TRUE | .NET | |
| TRUNC | .NET | |
| TTEST | NMath.Stats | TwoSamplePairedTTest or TwoSampleUnpairedTTest classes |
| TYPE | .NET | |
| UPPER | .NET | |
| VALUE | .NET | |
| VAR | NMath.Stats | StatsFunctions.Variance(BiasType.Unbiased) |
| VARA | No | |
| VARP | NMath.Stats | StatsFunctions.Variance() |
| VARPA | No | |
| VDB | No | |
| VLOOKUP | NMath.Stats | Combination of IndexOf() method on DataFrame and index operators |
| WEEKDAY | .NET | |
| WEIBULL | NMath.Stats | WeibullDistribution |
| ZTEST | NMath.Stats | OneSampleZTest |