I have recently imported all the ALGLIB statistics functions into VBA, and written interface functions where required, so that all the functions may now be used as User Defined Functions (UDFs) on the spreadsheet. A spreadsheet including full open source code and descriptions and examples of each function may be downloaded from AL-Stats.zip. The available functions are shown on the screenshots below:
The screen shots show output from the ALGLIB functions together with the equivalent Excel results, where available. Note that many of the Excel functions are only available in Excel 2010, and will not work in earlier versions. Also note that in some cases Excel returns statistics for an estimate of the population value, whereas the ALGLIB functions always return the statistic for the sample. See for example this Wikipedia article on the Kurtosis function.
The ALGLIB functions with names not preceded by AL_ call the ALGLIB code directly. For the remainder it has been necessary to write an interface function for various reasons:
- The ALGLIB routine is written as a sub, rather than a function
- The ALGLIB routines often require a 1D, base 0, array as input, or have several values as output, which must be assigned to an array so they can be returned by a UDF entered as an array function
- A few of the ALGLIB routines written as functions, which could otherwise be called directly, have the same name as the equivalent Excel function
Code for a typical interface function is shown below:
Public Function AL_DistMoments(XA As Variant) As Variant Dim XA0() As Double, N As Long, Ncols As Long, Rtn As Long, DMRes(1 To 4) As Double Dim Mean As Double, Variance As Double, Skewness As Double, Kurtosis As Double Rtn = VarAtoDouble1D_0(XA, XA0, N, Ncols) Call CalculateMoments(XA0, N, Mean, Variance, Skewness, Kurtosis) DMRes(1) = Mean DMRes(2) = Variance DMRes(3) = Skewness DMRes(4) = Kurtosis AL_DistMoments = DMRes End Function
The VarAtoDouble1D_0 routine converts a VBA Variant array into a 1D, base 0, Double array and returns the number of rows and columns of the array:
Function VarAtoDouble1D_0(XL_A As Variant, ByRef Cpp_A() As Double, ByRef Nrows As Long, ByRef Ncols As Long) As Long Dim i As Long, j As Long, LB As Long On Error GoTo iErr If TypeName(XL_A) = "Range" Then XL_A = XL_A.Value2 LB = LBound(XL_A) Nrows = UBound(XL_A) Ncols = UBound(XL_A, 2) ' Copy 2D base 1 variant array to 1D base 0 double array ReDim Cpp_A(0 To Nrows * Ncols - 1) For i = 1 To Nrows For j = 1 To Ncols Cpp_A((i - 1) * Ncols + j - 1) = XL_A(i, j) Next j Next i VarAtoDouble1D_0 = 0 Exit Function iErr: VarAtoDouble1D_0 = 1 End Function
The download spreadsheet includes all the necessary code, but for the benefit of those wanting to use the code direct from the ALGLIB site, or to use the versions in different languages, all the necessary code modules are listed below:
- binomialdistr: Binomial distribution
- chisquaredistr: Chi-Square distribution
- correlation: Pearson/Spearman correlation coefficients
- correlationtests: Hypothesis testing: correlation tests
- descriptivestatistics: Descriptive statistics: mean, variance, etc.
- fdistr: F-distribution
- gammafunc
- hqrnd: High quality random numbers generator
- ibetaf
- igammaf
- jarquebera: Hypothesis testing: Jarque-Bera test
- mannwhitneyu: Hypothesis testing: Mann-Whitney-U test
- nearunityunit
- normaldistr: Normal distribution
- poissondistr: Poisson distribution
- stest: Hypothesis testing: sign test
- studenttdistr: Student’s t-distribution
- studentttests: Hypothesis testing: Student’s t-test
- variancetests: Hypothesis testing: F-test and one-sample variance test
- wsr: Hypothesis testing: Wilcoxon signed rank test
Very nice! Appreciate the code sharing and conversion. Do you have a function for generating a skewed Student’s t distribution? I checked everywhere for a VBA or worksheet functions to do this, but alas none exists! ‘R’ has it as do other commercial software packages, but nothing native to VBA is out there currently. Will follow in case this is undertaken as it has wide applications in many disciplines.
Cheers!
LikeLike
Dave – I don’t know, but I have posted the question at the Alglib Forum (http://forum.alglib.net/viewforum.php?f=2).
LikeLike
Dave – see response from Sergey at ALGLIB at the link below:
http://forum.alglib.net/viewtopic.php?f=2&t=64&sid=5ca381415ee78e3d3fe981d53c0c567b
This is outside my area of expertise, so probably best to follow up directly at the ALGLIB site if you have any further questions.
LikeLike
Pingback: Daily Download 13: The ALGLIB maths library and Excel | Newton Excel Bach, not (just) an Excel Blog