Part III – With R in Excel – forecast | R-Bloggers

Introduction

We have already seen how we can obtain descriptive statistics in Part I and how to use lm() in Part II. In this part (part III) of the series we will look at the use of R in Excel to perform forecast and time series analysis.

In the previous two parts we have seen different ways to process the export of R -function calls, unpack and massage the data as required. In this part we will concentrate on setting up and communicating with a number of models in the ‘Prediction’ package (fpp2).

The workbook for this part of the series is: “Part III – R in Excel – forecast.xlsx”. As before, the worksheet contains ‘references’ links to external references. The worksheet ‘Libraries’ loads extra (non-default) packages. In this demonstration I use the fpp2 package. The ‘Datasets’ worksheet contains the data that is referred to in the worksheets.

Advanced average

In this worksheet we look at the advancing average function in the Forecast package. ma Calculates a simple advancing average more flexible from a certain time series. The data we use consists of ice sales.

In the previous posts we used ‘scripts’ to call R -functions. So in this case we could evaluate

ma(Sales, 3, FALSE)

And get the flattened time series back.

However, as the models become more complex and the number and types of parameters increases, this approach becomes less manageable. An alternative is to use one of the habit Forecast. Functions supplied by the add -in. This enables us to set the model parameters as a block of appointed parameters with their corresponding values. We have already seen an example of this Part II Where we set up a logistics regression model. In the case of Forecast.MAWe give the model a descriptive name. We know from the documentation that the model takes two parameters: the order (“Order of moving average more flexible”) and the center (“If it is true, the advancing average is centered for even orders”). Then we simply mention a normal Excel worksheet function.

The add -in offers wraps around a number of functions in the Forecast package. These are as follows:

• Forecast.ma – smooth out the flat average
• Prediction.ses – simple exponential flattening
• Prediction.holt – Holt exponential flattening
• Prediction.hw-holt-winters exponential flattening.
• Forecast. -car key – exponential flattening state room model.
• Prediction.arima-auto-regulative integrated advanced average model
• Forecast.autarima – fit best arima model for univariate time series
• Prediction.fc – generic function for predicting time series or models with time series
• Prediction.meanf – average forecast
• Prediction.rwf – predictions and prediction intervals for a random walk with drift model
• Prediction.splinef – Local linear predictions and prediction intervals with cubic flattening plines
• Forecast. Thetaf – Predictions and prediction intervals for a Theta method prediction
• Forecast.croston – predictions and other information for Croston’s predictions

Simple exponential flattening

In this example we make a simple exponential surface model. As before, we have set the model parameters after the forecast package ses documentation. Fortunately, most of the values have failed, so we only offer input for the following:

• alpha – the value of the flattening parameter for the level. If it is not, it will be estimated.
• H – The number of periods for forecasts.

Then we can invoke the add -in Forecast.SES(...).

The model returns the requested predictions with the average and lower and upper borders for the forecast intervals.

In earlier examples we have extracted model results by combining the returned model name (‘SES3’ in this case) with labels of the model. As illustrated in Part IITo make it easier to pick up results, the add -in offers some extra functions for searching for models: Model.Results performs a list of results from the model. Model.Result Performs the result that has been obtained from one item from the list of model results. Optionally, the result can be made as a data frame. This is a bit more convenient than evaluating scripts of the form model name'$coeffcientsetc. Finally, Model.Accuracy Returns a number of statistics with regard to measures of model accuracy.

The Holt-Winters model

The HW worksheet illustrates working with the Holt-Winters model. In this case we have set up two different models with their respective parameters, each with their own parameter block: one additive model and one multiply model. However, we first have to tackle a problem with the ‘data type’ of the data set. Usually we can just make a numerical vector. Unfortunately, the Serie Parameter of the Holt-Winters model requires information about the frequency with which the observations in the vector occur. That is why we first make a numerical vector and then convert it in a ts Time series vector with embedded frequency information. Currently the Excelraddin does not support Time series As a data type. This will be added as a future improvement.

Arima

The last example concerns the Arima model. The arrangement of the model is slightly more complicated than in earlier cases. In particular the order and the seasonal parameters.

We know from the documentation that the order Parameter is a specification of the non-seasonal part of the Arima model: the three components (P, D, Q) are the AR sequence, the degree of differences and the MA order. To handle this specification, we introduce the components with the help of the vector notation: c(0, 1, 1).

For the seasonal Parameter, the documentation says: “A specification of the seasonal part of the Arima model, plus the period (which is standard for frequency (y)). This must be a list of the order and period of components, but a specification of only a numerical vector of length 3 is converted into a suitable list of specification as the order.”

To achieve this, we add one script parameter. This is a parameter with a leading ‘^’ character as follows:

^list(order=c(0,1,1),period=12)

With this setup we can make the Arima model.

The results can be extracted with a combination of Model.Results And Model.Result functions or by using a ‘raw’ r script. The model accuracy can be checked through the function Model.Accuracy(...).

Pack

In this message we used R in Excel to perform some forecast and time series analyzes. We have seen how we can set and collect model data. In addition to evaluating R scripts, we also used a number of wrapper functions around the Prediction Package provided by the add -in to make model settings and evaluation easier.

In the last part of the series I will look at the use of R in Excel to call Python scripts.

Related