Dbank

Time Series Data Management
For Windows Desktops and Servers.

Using Microsoft Excel

Automating Excel Spreadsheets

Reading Dbank

Updating Dbank

Univariate Statistical Functions

Statistical Array-Formulae

 

 

 

Univariate Statistical Functions

Example Excel spreadsheet (Dbank required)

The following table lists all the statistical functions in Dbank's add-in that return a single (or univariate) statistical value. Click on any of the hyper-links (if available) to obtain a more detailed explanation of each function. You may download an example Excel spreadsheet that demonstrates how to call most of the functions described below.
Note that each of these functions can operate either on an Excel range, or a time-series that resides in a Dbank database. In each case, the function will typically ignore missing values within the specified range so as to return a numeric value for the statistic.
The "Range" argument in what follows refers to any valid Excel range, e.g. A1:A100. Non-numeric values in the range, including empty cells, are treated as missing values.

 

Dbank XLA Range Functions

Result

sAcf ( Range, K )

Autocorrelation coefficient at lag (or order) K.

sAdf ( Range, Number_Of_Lags, Trend_Order )

Augmented Dickey Fuller (ADF) t-statistic for testing the null hypothesis of a unit root. For the "Trend_Order" argument, use -1 to suppress the deterministic part in the ADF regression; 0 to include just a constant term, and 1 to include a deterministic trend and constant.  The number of lags must be positive.

sAdfa ( Range, Number_Of_Lags, Trend_Order )

Augmented Dickey Fuller (ADF) alpha-statistic (i.e., it is comparable to Phillips (1987) Za statistic) for the null hypothesis of a unit root. For the "Trend_Order" argument, use -1 to suppress the deterministic part in the ADF regression; 0 to include just a constant term, and 1 for a deterministic trend and constant. The number of lags must be positive.

sAdfAlpha ( Range, Number of Lags, Trend_Order )

First-order autoregressive coefficient estimate from the Augmented Dickey Fuller (or unit root) regression containing lagged difference terms. For the "Trend_Order" argument, use -1 to suppress the deterministic part in the ADF regression; 0 to include just a constant term, and 1 for a deterministic trend and constant.  The number of lags must be positive.

sAndersonDarling ( Range )

Anderson-Darling statistic for the null hypothesis of white noise.

sAndrewsLag ( Range, Kernel )

Optimal Andrews (1992) lag for calculating the long-run covariance term.  Kernel parameter can be either "Parzen", "Fejer", "Tukey", or "Rectangular".

sCoefficientofVariation ( Range )

Standard deviation divided by the arithmetic average.

sCorrelation ( Range1, Range2 )

Correlation coefficient.  Note that the worksheet ranges must be consistent in terms of the number of rows and columns.

sCount ( Range )

Count indicating the number of elements in a range.  Equal to either the number of rows or columns in a range.

sCovariance  ( Range1, Range2 )

Covariance.

sCramerVonMises ( Range )

Cramer Von Mises statistic for the null hypothesis of white noise.

sDoornickHansen ( Range )

Doornick-Hansen statistic for the null hypothesis of normality.

sFirstQuartile ( Range )

First quartile.

sGeometricMean ( Range )

Geometric mean.

sHarmonicMean ( Range )

Harmonic mean.

sInterQuartileRange ( Range )

Inter-quartile range.

sJarqueBera ( Range )

Jarque-Bera Chi-square statistic for the null hypothesis of normality.

sKolmogorovSmirnov ( Range )

Kolmogorov-Smirnov statistic for the null hypothesis of white noise.

sKpss ( Range, Trend_Order )

Kwiatkowsi, D., P.C.B. Phillips, P. Schmidt and Y. Shin (1992) (KPSS) statistic for testing the null of stationarity against the null of a unit root.  For the "Trend_Order" argument, use 0 for a constant term, and 1 for a deterministic trend and constant. See also sKpssCriticalValue(), which retrieves critical values for the KPSS statistic at various significance levels.

sKpssCriticalValue  (Trend_Order, Significance_Level )

Critical values for the KPSS statistic (use sKpss() to calculate the actual statistic);  0 indicates 1 per cent level; 1 indicates 5 per cent level. For the "Trend_Order" argument, use 0 for a constant term, and 1 for a deterministic trend.

sKuiper ( Range )

Kuiper statistic for the null hypothesis of white noise.

sKurtosis ( Range )

Excess Kurtosis.

sLargest ( Range, N )

Sorts the values in a range from largest to smallest, and then returns the value in the "N"th position (or of rank) "n".  N must be between 1 and number of valid observations in the range. Set N = 1 for maximum value, and so on.

sMax ( Range )

Maximum.

sMean ( Range )

Mean or average.

sMeanAbsDev ( Range )

Average of the absolute deviations from the average .

sMedian ( Range )

Median.

sMedianAbsDev ( Range )

Median of the absolute deviations from the median.

sMin ( Range )

Minimum.

sMissingCount ( Range )

Count of missing values in a range.

sObsCount ( Range )

Count of observations in a range (including missing values). Equal to either the number of rows or columns in a range.

spAcf ( Range, Order )

Partial autocorrelation coefficient at lag (or order) k.

sPopulationVariance ( Range )

Population variance---divisor for this function is N, the number of valid observations in a range.

sQStatistic ( Range, N )

Chi-Squared (N) statistic for the null hypothesis that the first N auto-correlation coefficients are zero.

sRange ( Range )

Range; i.e., maximum less minimum.

sSampleVariance ( Range )

Sample variance --- divisor is N-1.

sScaledMedianAbsDev ( Range )

Scaled median absolute deviation.

sSkewness ( Range )

Skewness.

sSmallest ( Range, N )

Sorts the values from smallest to largest, and then returns the value in the "N"th position.  N must lie between 1 and number of valid observations in the range. Set N = 1 for minimum value, and so on.

sStdDev ( Range )

Standard deviation.

sSum ( Range )

Sum.

sThirdQuartile ( Range )

Third Quartile.

sUnitRootCriticalValue (Trend_Order, Significance_Level)

Returns the asymptotic critical value of standard unit root tests; 0 indicates 1 per cent level; 1 indicates 5 per cent level. For the "Trend_Order" parameter, use -1 for no deterministic part; 0 for a constant term, and 1 for a deterministic trend and constant term.

sValidCount ( Range )

Count indicating the number of non-missing observations in a range.

sVariance ( Range )

Sample Variance

sVonNuemanRatio ( Range )

Standard Von Nueman ratio statistic for the null hypothesis of white noise.

sZa ( Range, Number_Of_Lags, Trend_Order, Kernel )

Phillips Za statistic for the null hypothesis of a unit root. For the "Trend_Order" argument, use -1 for no deterministic part; 0 for a constant term, and 1 for a deterministic trend. Kernel can be either "Parzen", "Fejer", "Tukey", or "Rectangular"

sZAlpha  ( Range, Trend_Order )

First-order autoregressive coefficient from the standard, first-order regressive model, y(t) = a y(t-1)+e(t).

sZt  ( Range, Number_Of_Lags, Trend_Order, Kernel )

Phillips Zt statistic for the null hypothesis of a unit root. For the "Trend_Order" argument, use -1 for no deterministic part; 0 for a constant term, and 1 for a deterministic trend.  Kernel can be either "Parzen", "Fejer", "Tukey", or "Rectangular"

 

While not explicitly stated in the function descriptions, all of the above functions can also operate on a time-series that resides in a Dbank database rather than a worksheet range. Simply replace the "Range" argument in the function with the fully-qualified address of the time-series you wish to operate on; e.g:
"c:\program files\dbank32\example[first]bpimr"
By default, the function will operate on all the values in a time-series. You can restrict the function to operate on a subset of series by specifying optional "StartDate" and "EndDate" parameters, which must be placed immediately after the last argument of each function described above.

As an example, suppose you want to compute the range of the above series from 1975:1 to 1984:4. The following call will produce the desired result:
=sRange("c:\program files\dbank32\example[first]bpimr","1975:1","1984:4")
In this case, the sRange() function will first read the series from the Dbank database, extract the time-series values between 1975:1 and 1984:4, and then compute the range of these numbers. Please note that it is the users responsibility to specify valid starting and ending dates that are recognized by Dbank.

| Copyright © 1994-2011 | TSDM Pte Ltd | Time Series Data Management for Windows | Contact TSDM |