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

 

 

 

 

Statistical Array-Formulae Returning a Range of Values

Example Excel spreadsheet (Dbank required)

The following table lists all of the "array-formulae" functions in Dbank's XLA, i.e., XLA functions that return a range or vector of values. These functions offer Excel users access to univariate statistical routines that are typically found only in specialized statistical packages.

Excel users create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. The critical requirement here is that the target range is large enough to accept the time-series, which the user is responsible for selecting before entering the function call. Note that the target range may need to be larger than the actual number of series in the range (or time-series), especially when the function extends the series. The result is truncated to the actual size of the target range if the target range is too small.

While all the functions return an array by default, users can also restrict the result to a single value by specifying an optional "RequiredDate" parameter that implies the offset, within the range result, of the desired value. This date is typically a "string" formatted in a format understood by Dbank, e.g., "1960:1Q".

The functions listed in this table can be nested.

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

Array-Formulae Result

rAcf( Range, [RequiredDate=1] )

Autocorrelation coefficients from 1 to N-1, where N is the number of observations in the range.

rBaxterKing( Range,  LowerBound, UpperBound,  Number_Of_Lags, [DontTrim=True], [RequiredDate=1])

Baxter-King (1999) band-pass filter. This function is typically used to extract the "growth cycle" component of a time-series, but can also be used to infer the long-run trend of the series.  The lower bound represents the first period to include in the filter series (typically 6 for quarterly data); the upper bound represents the last period to include in the filtered series (typically 32).  The number of lags parameter controls the number of leads and lags to use when calculating the filtered series.  The "number_of_lags" will also determine the number of blank cells found at the start & end of the resulting range.

rBoxCox( Range, Power, Shift, [RequiredDate=1])

Standard Box-Cox ( power ) transformation.  "Power" is the exponent parameter in the power transformation, while "Shift" is the so-called shift parameter, and must satisfy min(range) + SHIFT > 0.  The forward transformation is useful in the analysis of linear models or models with non-normal errors or non-constant variance. In the time series setting, application of the appropriate transformation and subsequent differencing of a series may enable model identification and parameter estimation in the class of homogeneous stationary autoregressive-moving average models. 

rCDF( Range, [RequiredDate=1] )

Cumulative distribution function.

rCompoundGrowth( Range, [Length = 1], [Span = 1], [ReturnProportion=False] , [RequiredDate=1])

 

rCumProd( Range , [RequiredDate=1])

Cumulative product.

rCumSum( Range , [RequiredDate=1])

Cumulative sum.

rDeMean( Range, [RequiredDate=1])

Subtracts the mean (or average) from every value in the range.

rDetrend( Range, [RequiredDate=1])

Subtracts the estimated trend (and constant) from every value in the range.

rDiff( Range, [Order=1], [Times=1] , [RequiredDate=1])

Difference operator; returns first difference if optional parameters are not present.

rDlog( Range, [Order=1], [Times=1], [RequiredDate=1])

Estimate of the growth rate, using natural logs.  Returns one period growth rate if optional parameters are not present.

rExp2Smooth( Range, [ForecastHorizion], [Alpha], [MinimizeMeanAbsoluteError=False], [RequiredDate=1])

Double exponential smooth. Optional alpha or smoothing parameter will be estimated by minimizing the residual sum of squares; however, it can also be estimated by minimizing the mean absolute error.  Function will extend the series by the number of data points indicated, or the number of data points needed to reach the "last date" indicated with the "ForecastingHorizion" parameter.

rExpSmooth( Range, [ForecastHorizion], [Alpha], [MinimizeMeanAbsoluteError=False], [RequiredDate=1])

Simple exponential smooth.  The optional alpha or smoothing parameter will be estimated by minimizing the residual sum of squares; however, it can also be estimated by minimizing the mean absolute error.  Function will extend the series by the number of data points indicated (or the number of data points needed) to reach the "last date" indicated using the optional "ForecastingHorizion" parameter.

rExtend( Range, N, Growth_Rates, [RequiredDate=1])

Starting from the last valid observation in the range, this function extends the range by N periods using the growth rates given in the "Growth_Rates" range (an Excel range).  If the latter contains a single growth rate, the range is extended by multiplying its last valid observation N times by the (1+g), where g is the given growth rate.  When the growth rate contains N terms, the range is extended using the formulae (1+g(1))*(1+g(2)) ... (1+g(n)), where n <= N is the number of terms since the last valid observation.  

rGreaterThan( Range, Value, [RequiredDate=1])

Function returns 1 when element in range is greater than indicated value.

rGrowth( Range, [Length=1], [ReturnProportion=True], [RequiredDate=1])

One-period growth rate (expressed as a proportion) of the series.  Optional "Length" and "Proportion" parameters can be used to control the functions default behavior.

rHolt( Range, [ForecastHorizion], [Alpha], [Beta], [Periodicity], [KeepExistingData=False],[ MinimizeMeanAbsoluteError As Boolean = False], [RequiredDate=1]

Holt forecasting technique (no seasonal term). Alpha and Beta parameters will be estimated by minimizing mean squared error (or mean absolute deviations) when they are not specified.  Function will extend the series by the number of data points indicated (or the number of data points needed) to reach the "last date" indicated using the optional "ForecastingHorizion" parameter.  Within sample or range data can be preserved by setting the "KeepExistingData" parameter to "True".

rHoltWinters( Range, [ForecastHorizion], [Alpha], [Beta], [Gamma], [Periodicity], [KeepExistingData=False], [AdditiveVersion=False],[ MinimizeMeanAbsoluteError = False], [RequiredDate=1])

Holt-Winters forecasting technique, with additive or multiplicative seasonal factors. Alpha, Beta, and Gamma (Seasonality) parameters will be estimated by minimizing mean squared error (or mean absolute deviations) when they are not specified.   Function will extend the series by the number of data points indicated ( or the number of data points needed) to reach the "last date" indicated using the optional "ForecastingHorizion" parameter.  Within sample data can be preserved by setting the "KeepExistingData" parameter to "True".

rHPFilter( Range, [Lamda=1600], [RequiredDate=1] )

Hodrick-Prescott filter for economic time-series. 

rIdealBandPass( Range, LowerBound, UpperBound, [RequiredDate=1])

Approximate ideal band pass filter, as defined in Ouliaris and Corbae (2001). Improves on Baxter-King band pass filter (see rBaxterKing) by avoiding sample truncation problem, and producing a smoother series over the required periodicities.  Implemented by first detrending data in the frequency domain (over the selected frequencies) rather than the time-domain.

rInvBoxCox( Range, Power, Shift, [RequiredDate=1])

Inverse Box-Cox transform.

rIsMissing( Range, [RequiredDate=1] )

Flags missing values in the range; missing value flag is 1.

rLag( Range , N, [RequiredDate=1])

Moves or shifts values down "N" positions.  N can be negative.

rLessThan( Range, [RequiredDate=1])

Function returns 1 when element in range is less than indicated value.

rLogit( Range, [RequiredDate=1] )

Standard logit transformation.

rMovingAverage( Range, N, [RequiredDate=1])

Centered moving average involving N terms.

rPacf( Range , [RequiredDate=1])

Partial autocorrelation coefficients from 1 to N-1, where N is the number of observations in the range.

rPack( Range , [RequiredDate=1])

Compresses range by removing missing values.

rPDF( Range , [RequiredDate=1])

Estimate of the partial density function.

rPercentageGrowth( Range, [Length=1] , [RequiredDate=1])

Growth-rate (over "length" periods), expressed as a percentage.

rPeriodogram( Range, [RequiredDate=1] )

[0, p] Periodogram ordinates.  Result will contain n/2 ordinates when n (the number of observations) is even, and (n+1)/2 ordinates if n is odd. 

rPermute( Range , [RequiredDate=1])

Randomly shuffles/permutes values in a range.

rPermuteIndex( Range, [RequiredDate=1] )

Shuffles values in a range, but returns the values new position or index in the shuffled range.

rQStat( Range, [RequiredDate=1])

Sequential Q statistic for testing the null hypothesis of no auto-correlation, up to and including the (N-1)th autocorrelation term.  Function returns N-1 Q statistics.

rQStatPV( Range , [RequiredDate=1])

p-values, derived from the Chi-Squared distribution, of the Q statistic (see rQStat( ) ).  Function return N-1 p-values, where N is the number of values in the range.

rRank( Range , [RequiredDate=1])

Returns the rank of an observation if the range was sorted from highest to lowest.

rRebase( Range, RebaseDate , [RequiredDate=1])

Rebases a series using the value associated with "RebaseDate".  The resulting series will be 100 at this date.

rRecode( Range, Old_Value, New_Value, [RequiredDate=1])

Replaces "old value" with "new value".  "Old_Value" can be set to "." to replace missing values in the range.

rReplaceMissing( Range, [ReplaceMethod=Spline] , [RequiredDate=1])

Replaces missing values in a range using, by default, a "double-knot" cubic-spline procedure.  Set "ReplaceMethod" to "Linear" to replace missing values using linear interpolation; "R" for repeat; "P" for prorate, and "N" for polynomial interpolation.

rReverse( Range, [RequiredDate=1])

Reverses range; first value ends up in the last position of the range; second value in the second last position, and so on.

rRollingMax( Range, w, [RequiredDate=1] )

Returns maximum value in each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of maximum values.  If w is greater than the number of observations in the range, function returns the maximum of the entire range.

rRollingMean( Range, w, [RequiredDate=1] )

Returns average value of each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of  average or mean values.  If w is greater than the number of observations in the range, function returns the mean of the entire range.

rRollingMin( Range, w, [RequiredDate=1] )

Returns minimum value in each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of minimum values.  If w is greater than the number of observations in the range, function returns the minimum of the entire range.

rRollingRange( Range, w, [RequiredDate=1] )

Returns the maximum minus the minimum value (i.e. range) in each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of ranges values.  If w is greater than the number of observations in the Excel range, function returns the actual range (i.e., maximum less the minimum) of the selected numbers.

rRollingStdDeviation( Range, w, [RequiredDate=1] )

Returns the standard deviation of each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of standard deviations.  If w is greater than the number of observations in the range, function returns the standard deviation of the entire range.

rRollingStdUnits( Range, w, [RequiredDate=1] )

Returns the standard unit of each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of standardized units.  If w is greater than the number of observations in the range, function returns the standardized unit of the entire range.

rRollingSum( Range, w, [RequiredDate=1] )

Returns the sum of each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of value aggregates.  If w is greater than the number of observations in the range, function returns the range sum.

rRollingVar( Range, w, [RequiredDate=1] )

Returns the variance of each sequential window of width "w" starting from  w+1.  Result is a vector containing a sequence of variances.  If w is greater than the number of observations in the range, function returns the range variance.

rRound( Range, N, [RequiredDate=1] )

Rounds values to N decimal places.

rSave( Range, SeriesName, [Frequency], [StartDate], [Title], [LongName], [DataSource], [RequiredDate=1])

Saves range to a Dbank database.  Name of the time-series is controlled using "SeriesName", e.g. "example[excel]test".

rSelect( Range, N, [RequiredDate=1])

Randomly selects N values from the range.

rSelectIndex( Range, N, [RequiredDate=1])

Randomly selects N values from the range, then returns index or position in original range.

rSgn( Range, [RequiredDate=1] )

Equals 1 if number is positive; -1 if number if negative, and 0 if number is zero.

rShift( Range, N, [RequiredDate=1] )

Moves or shifts values down "N" positions.  N can be negative.  Equivalent to rLag( ) function.

rSigDigits( Range, N, [RequiredDate=1])

Returns N significant digits of values in range.

rSmooth( Range , [RequiredDate=1])

Friedman's super smoother.  

rSort( Range , [RequiredDate=1])

Sort elements in the range.

rSortIndex( Range , [RequiredDate=1])

Sorts elements in the range, and returns the  index of the value in the sorted array.

rSpectralDensity( Range, WindowWidth, [Kernel="Parzen"] , [RequiredDate=1])

[0, p] Spectral density of range.  "WindowWidth" controls the size of the periodogram window.  The weights placed on each ordinate is determined by the "Kernel" parameter, which can be set to "Parzen", "Fejer", "Rectangular", or "Tukey".  Result will contain n/2 ordinates if n (the number of observations) is even, and (n+1)/2 ordinates if n is odd. 

rSqrt( Range, [RequiredDate=1] )

Square root of values in range.

rStdSeasonals( Range, [RequiredDate=1]

Standard seasonal factors (text book variety).

rSVT( Range, [CValue=0.02], [WindowLength], [RequiredDate=1])

Stochastic volatility transform (used to avoid ln(0)).   "WindowLength" parameter is used to replace unconditional variance with a rolling variance of length "WindowLength" (see rRollingVar() function ).

rTrend( Range , [RequiredDate=1])

Fitted trend (a+bt) using ordinary least squares.

rUncenteredMovingAverage( Range, N, [RequiredDate=1])

Un-centered moving average of range using N terms.

rX11( Range, [X11Component="S"], [RangeStartDate], [RangeFrequency], [RequiredDate=1]))

Department of Commerce's original X11 program.  If X11Component = "S", the function returns the seasonally adjusted value of the range.  Use "T" to retrieve the estimate of the trend component; "C" the cyclical component; "I" the irregular component, and "F" for the seasonal factors.  You must specify a start date, and range frequency (monthly or quarterly) for this function to operate correctly when operating on an Excel range.  The value of  these parameters will be read from the data base if the range refers to a Dbank time-series.

rX12( Range,  [X12Form="Basic"], [X12Component="S"], [RangeStartDate], [RangeFrequency], [RequiredDate=1])

Department of Commerce's recently released X12 program.  If X12Component = "S", the function returns the seasonally adjusted value of the range.  Use "T" to retrieve the estimate of the trend component; "C" the cyclical component; "I" the irregular component, and "F" for the seasonal factors.  X12Form parameter accepts "Basic", "Flow" or "Stock".  You must specify a start date, and range frequency (monthly or quarterly) for this function to operate correctly when operating on an Excel range.  The value of  these parameters will be read from the data base if the range refers to a Dbank time-series.

While not

Although not explicitly stated in the function descriptions, all of the above range 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, and return an array. However, you can restrict the function to operate on a subset of the series by using the optional "EarliestDropDate" and "LastDropDate" parameters, which must be placed immediately after the last argument of each function described above.

As an example, suppose you want to compute the rolling maximum for 5 period of the above series from 1975:1 to 1984:4. The following call will produce the desired result, and return an array of numbers:
=rRollingMax("c:\program files\dbank32\example[first]bpimr",5,,"1975:1","1984:4")
In this case, the sRollingMax() 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 rolling 5-period maximum of these numbers. It will return an array of values.

Note that it is the user's responsibility to specify valid starting and ending dates, meaning that, given the frequency of the time-series, they should be expressed in a valid format that Dbank understands. For example, if the series has a quarterly or monthly frequency, then the format should be in the form "yyyy:pp".

As described, you may also restrict the result of rRollingMax () to a single observation by specifying an explicit date in the "RequiredDate" argument.
=rRollingMax("c:\program files\dbank32\example[first]bpimr",5,"1980:1","1975:1","1984:4")

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