Real Statistics Support for Forecast Accuracy

Worksheet Functions

Real Statistics Functions: The Real Statistics Resource Pack provides the following functions. R1 is a column range containing time series data and R2 is a column range containing the corresponding forecast. For the Diebold-Mariano test, a second forecast is contained in column range R3. Note that these arrays have the same number of rows and they cannot contain any non-numeric values.

ForecastError(R1, R2, type) = the error statistic of one of the specified types; type takes one of the following string values: me, mse, rmse, mae, mpe, mape, smape, u1, u2 (see Time Series Forecast Error).

Forecast_Error(R1, R2, lab) = a column array containing the error statistics: me, mse, rmse, mae, mpe, mape, smape, u1, u2 (see Time Series Forecast Error); if lab = TRUE then an extra column of labels is appended to the output (default FALSE).

DIEBOLD(R1, R2, R3, h, type) = the test statistic for the Diebold-Mariano test. h is the number of lags (default 0, which is equivalent to h = the cube-root of the number of elements in R1). type = 0 (default) for e2 error measurement and type = 1 for the |e| error measurement.

DMTEST(R1, R2, R3, h, type) = p-value for the Diebold-Mariano test where h and type are as for the DIEBOLD function.

HLN(R1, R2, R3, h, type) = the test statistic for the HLN test. h is the number of lags (default 0, which is equivalent to h = the cube-root of the number of elements in R1). type = 0 (default) for e2 error measurement and type = 1 for the |e| error measurement.

HLNTEST(R1, R2, R3, h, type) = p-value for the HLN test where h and type are as for the DIEBOLD function.

LossDiff(R1, R2, R3, type) = a column array containing the loss-differential time series for the Diebold-Mariano test with type as for the DIEBOLD function.

PESARAN(R1, R2) = the test statistic for the Pesaran-Timmermann test

PTTEST(R1, R2) = p-value for the Pesaran-Timmermann test

Examples

We can calculate the DM statistic in cell N8 and p-value in cell N9 of Figure 3 of Diebold-Mariano Test by the formulas

=DIEBOLD(A4:A23,B4:B23,C4:C23) 

=DMTEST(A4:A23,B4:B23,C4:C23),

respectively (referencing the cells in Figure 1 of Diebold-Mariano Test). =LossDiff(A4:A23,B4:B23,C4:C23) produces the output shown in range H4:H23 of Figure 1 of Diebold-Mariano Test.

We can calculate the HLN statistic in cell N8 and p-value in cell N9 of Figure 3 of Diebold-Mariano Test by the worksheet formulas =HLN(A4:A23,B4:B23,C4:C23) and =HLNTEST(A4:A23,B4:B23,C4:C23), respectively (referencing the cells in Figure 1 of Diebold-Mariano Test).

The PT statistic in cell E11 and p-value in cell E12 of Figure 1 of Pesaran-Timmermann Test can be calculated by the worksheet formulas =PESARAN(A4:A18,B4:B18) and =PTTEST(A4:A18,B4:B18), respectively.

Data Analysis Tool

Real Statistics Data Analysis Tool: The Real Statistics Resource Pack supplies the Forecast Accuracy data analysis tool to determine the accuracy of a time series forecast or determine whether one forecast is better than another.

To use this tool, for example, to perform the analysis for Example 1 of Diebold-Mariano Test, press Ctrl-m and select the Forecast Accuracy option from the resulting menu (or Time S tab if using the Multipage interface). Now fill in the dialog box that appears as shown in Figure 1.

Forecast Accuracy dialog box

Figure 1 – Forecast Accuracy dialog box

The Order field is left blank to get the default, which, as we have seen, will be set to 4. The output will be similar to that shown in Figures 1 and 3 of Diebold-Mariano Test.

Results

If we select the Forecast Error option in Figure 1, then we would get the output shown in Figure 2.

Forecast error statistics

Figure 2 – Error statistics

Despite the fact that we found no significant difference between the two forecasts using the Diebold-Mariano and HLN tests, we see from Figure 2 that the MSE statistic is considerably smaller for Forecast 2 than Forecast 1 (as are all but the ME error statistic).

Note that range E5:F13 contains the worksheet array formula =Forecast_Error(A4:A23,B4:B23,TRUE) and range G5:G13 contains the array formula =Forecast_Error(A4:A23,C4:C23). You can get the same results for column G by placing =Forecast_Error(A$4:A$23,C$4:C$23,E5) in cell G5, highlighting the range G5:G13 and pressing Ctrl-D.

Examples Workbook

Click here to download the Excel workbook with the examples described on this webpage

References

Hyndman, R. J. and Athanasopoulos, G. (2018) Evaluating forecast accuracy. Forecasting: Principles and Practice. 2nd ed.
https://otexts.com/fpp2/accuracy.html

Oracle (2018) Time-series forecasting error measures
https://docs.oracle.com/en/cloud/saas/planning-budgeting-cloud/pfusu/time-series_forecasting_error_measures.html

Triacca, U. (2016) Lesson 19: comparing predictive accuracy of two forecasts: the Diebold-Mariano test.
https://docplayer.net/21167781-Lesson19-comparing-predictive-accuracy-of-two-forecasts-th-diebold-mariano-test.html

Pesaran, M. H. and Timmermann, A. (1992) A simple nonparametric test of predictive performance. Journal of Business & Economic Statistics, Vol. 10, No. 4, pp. 461-465
https://www.jstor.org/stable/1391822

Leave a Comment