Time Series Data Management
For Windows Desktops and Servers.
Example Excel spreadsheet (Dbank required)
The following table lists all of the "arrayformulae" 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 timeseries, 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 timeseries), 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. Nonnumeric values in the
range, including empty cells, are treated as missing values.
Dbank XLA Range Functions 
ArrayFormulae Result 
rAcf( Range, [RequiredDate=1] ) 
Autocorrelation coefficients from 1 to N1, where N is the number of observations in the range. 
rBaxterKing( Range, LowerBound, UpperBound, Number_Of_Lags, [DontTrim=True], [RequiredDate=1]) 
BaxterKing (1999) bandpass filter. This function is typically used to extract the "growth cycle" component of a timeseries, but can also be used to infer the longrun 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 BoxCox ( power ) transformation. "Power" is the exponent parameter in the power transformation, while "Shift" is the socalled shift parameter, and must satisfy min(range) + SHIFT > 0. The forward transformation is useful in the analysis of linear models or models with nonnormal errors or nonconstant 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 autoregressivemoving 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]) 
Oneperiod 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]) 
HoltWinters 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] ) 
HodrickPrescott filter for economic timeseries. 
rIdealBandPass( Range, LowerBound, UpperBound, [RequiredDate=1]) 
Approximate ideal band pass filter, as defined in Ouliaris and Corbae (2001). Improves on BaxterKing 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 timedomain. 
rInvBoxCox( Range, Power, Shift, [RequiredDate=1]) 
Inverse BoxCox 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 N1, 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]) 
Growthrate (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 autocorrelation, up to and including the (N1)^{th} autocorrelation term. Function returns N1 Q statistics. 
rQStatPV( Range , [RequiredDate=1]) 
pvalues, derived from the ChiSquared distribution, of the Q statistic (see rQStat( ) ). Function return N1 pvalues, 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 "doubleknot" cubicspline 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 timeseries 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]) 
Uncentered 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 timeseries. 
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 timeseries. 
While not
Although not explicitly stated in the function
descriptions, all of the above range functions can also operate on a timeseries
that resides in a Dbank database rather than a worksheet range. Simply replace
the "Range" argument in the function with the fullyqualified 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 timeseries, 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 timeseries values between 1975:1 and 1984:4,
and then compute the rolling 5period 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 timeseries, 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 © 19942011  TSDM Pte Ltd  Time Series Data Management for Windows  Contact TSDM 