The following is a summary of all the trend analysis functions provided in the Real Statistics Resource Pack.
For many of the following functions, R1 contains a column range containing a time series.
Autocorrelation and Partial Autocorrelation Functions
ACF(R1, k) = the ACF value at lag k for the time series in range R1
ACVF(R1, k) = the autcovariance at lag k for the time series in range R1
BARTEST(r, n, lag) = p-value of Bartlett’s test for correlation coefficient r based on a time series of size n
BARTEST(R1, type, lag) = BARTEST(r, n, lag) where n = the number of elements in range R1 and r = ACF(R1,lag)
PIERCE(R1, type, lag) = Box-Pierce statistic Q for range R1
BPTEST(R1, type, lag) = p-value for the Box-Pierce test for range R1
LJUNG(R1, type, lag) = Ljung-Box statistic Q for range R1
LBTEST(R1,type, lag) = p-value for the Ljung-Box test for range R1
ACOV(R1, k) – the autcovariance matrix at lag k
ACORR(R1, k) – the autcorrelation matrix at lag k
PACF(R1, k) – the PACF value at lag k
In the above functions where the type argument is missing, the test is performed using the autocorrelation coefficient (ACF). If the value assigned instead is 1 or “pacf” then the test is performed using the partial autocorrelation coefficient (PACF). Actually if the second argument takes any value except 1 or “pacf”, then the ACF value is used. lag = the lag value used for the test.
Augmented Dickey-Fuller Test
ADFTest(R1, lab, lag, criteria, type, alpha): returns a 8 × 1 range which contains the following values: tau-statistic, tau-critical, yes/no (stationary or not), AIC value, BIC value, # of lags (p), the first-order autoregression coefficient and estimated p-value.
- lab : if lab = TRUE (default is FALSE), the output consists of a 6 × 2 range whose first column contains labels.
- lag = the maximum number of lags to use in the test (default 0); if lag < 0 then lag is automatically set to =ROUND(12*(n/100)^.25,0), as proposed by Schwert, where n = the # of elements in the time series.
- criteria = “none” : no criteria is used, and so p is set to the value of lag
- criteria = “aic” : the AIC is used to determine the number of lags p (where p ≤ lag)
- criteria = “bic” : the BIC is used to determine the number of lags p (where p ≤ lag)
- type =the test type : 0 = no constant, no trend, 1 = constant, no trend (default), 2 = constant, trend
- alpha = significance level (default .05)
ADFCRIT(n, alpha, type) = critical value, tau-crit, for the stated type of ADF test at the stated alpha value, when the time series has n elements
ADFPROB(x, n, type) = estimated p-value (based on linear interpolation) for the ADF test at x for a time series of length n
Other Unit Root Test Functions
PPTEST(R1, lab, lags, type, alpha) – an array function which returns a column range for the PP test consisting of tau-stat, tau-crit, stationary (yes/no), lags, autocorrelation coefficient and p-value.
KPSSTEST(R1, lab, lags, type, alpha) – an array function which returns a column range for the KPSS test consisting of test-stat, crit-value, stationary (yes/no), lags and p-value.
ARIMA_Coeff(R1, p, q, d, con, lab) = returns a p+q+1 × 4 array, each row of which contains the coefficient, standard error, t-stat and p-value (in order: constant, phi 1, phi 2, …, theta 1, theta 2, …) of the ARIMA(p,q,d) model for the time series data in column range R1; if lab = TRUE (default FALSE), then an extra row and column are appended with labels; if con = TRUE (default) then a constant term is used, otherwise it is not (i.e. it is set to zero).
ARIMA_Stats(R1,R2, p, q, d, con, lab) = 7 × 1 column array containing the values LL, SSE, MSE, AIC, BIC, AIC augmented and BIC augmented for the ARIMA(p,q,d) model for the time series data in column range R1 based on the coefficients in the p+q+1 × 1 column range R2; if lab = TRUE (default FALSE), then an extra column of labels is appended to the output; if con = TRUE (default) then a constant term is used, otherwise it is not.
ARMap(R1,p) – takes the time series in the n × 1 range R1 and outputs the n–p × p+1 range where the first p columns represent the X values in the linear regression and the last column represents the Y values.
ADIFF(R1, d) – takes the time series in the n × 1 range R1 and outputs an n–d × 1 range containing the data in R1 differenced d times
ARRoots(R1, prec, iter, r, s) – takes the p × 1 range R1 containing the phi coefficients of an ARIMA model and outputs a p × 3 range, each of whose rows consists of the real and imaginary parts of the roots of the characteristic polynomial along with that root’s absolute value.
MARoots(R1, prec, iter, r, s) – takes the q × 1 range R1 containing the theta coefficients of an ARIMA model and outputs a q × 3 range, each of whose rows consists of the real and imaginary parts of the roots of the characteristic polynomial along with that root’s absolute value.
See ROOTS function below for information about the prec, iter, r and s arguments.
PSICoeff(R1, R2, k, rev): based on the column ranges R1 consisting of phi coefficients and R2 consisting of theta coefficients, outputs a k × 1 range containing the first k psi coefficients (starting with ψ0 = 1) for the ARMA model with the coefficients in R1 and R2.
If k is omitted (default) then k is set equal to the number of rows in the highlighted range. If rev = TRUE (default), then the phi and theta coefficients are listed in reverse order.
ARMA_SSE(R0, R1, R2, avg, p1, q1) = SSE value for the time series data in R0 based on the p phi coefficients in R1 and q theta coefficients in R2; also the p1th phi coefficient is zeroed out and/or the q1th theta coefficient is zeroed out; finally the mean is zeroed out if avg is set to a non-zero value.
Complex Numbers and Matrices
Roots of a polynomial: The following is an array function where R1 is an n+1 × 1 range containing the coefficients of a polynomial where a0 (the constant term) is in the first position and an is in the last position.
ROOTS(R1, prec, iter, r, s): outputs an n × 2 range where each row contains one root, and where the first column consists of the real part of the roots and the second column consists of the imaginary part of the roots
Here prec = the precision of the result, i.e. how close to zero is acceptable (default 0.00000001). iter = the maximum number of iteration performed when performing Bairstow’s Method (default 50). r, s = the initial seed values when using Bairstow’s Method (default 0).
Complex numbers: The following are array functions in which z1 and z2 are 1 × 2 ranges which represent complex numbers with c + di, and a and b are real numbers.
|CReal(z1) = c||CAdd(z1, z2) = z1 + z2||CExp(z1) = exp(z1) = ez1|
|CImag(z1) = d||CSub(z1, z2) = z1 – z2||CLn(z1) = ln(z1)|
|CAbs(z1) = |z1|||CMult(z1, z2) = z1 * z2||CSet(a,b) = a + bi|
|CConj(z1) = c – di||CDiv(z1, z2) = z1 / z2||CMap(“a+bi”) = a + bi|
|CPower(z1, n) = z1n||CText(z1) = “c+di”|
Complex matrices: The following are array functions where Y and Z are ranges that represent complex matrices, while z is a range that represents a complex (scalar) number and k is a positive integer.
|ZAdd(Y, Z) = Y + Z||ZSub(Y, Z) = Y – Z||ZMult(Y, Z) = Y × Z|
|ZInverse(Z) = Z-1||ZTranspose(Z) = ZT||ZIdentity(k) = identity|
|ZMultScalar(Y, z) = z × Y|