EasyER, Time Series in Excel

Facebooktwittergoogle_plusredditlinkedin

In the last EasyER update (Version 1.1) the ability to use date time values was added. This makes it easier to deal with time series in Excel, allowing analysis and forecasting methods to be used.

Time Series in Excel

Excel doesn’t do time series and applying standard statistical methods to time series with strong seasonal or repeating patterns results in misleading results. Using time series modelling also allows for forecasts and predictions about future observations. Linking Excel to R allows all kinds of time series analysis method to be used.

The following illustrates some time series functions defined in R code that EasyER can run from Excel. These functions can be downloaded as an EasyER function file so you can try them out and modify as you see fit.

Data

The following examples are done using the R Air Passengers data set. It is monthly data, obtained from 1949 To 1960. All the chart images in this article are pasted directly from Excel as produced by EasyER. The data set is just used for illustrative purposes. The following functions may not necessarily be appropriate for the data set.

Time Series Plot

Plotting time series can be done in all kinds of way. One way that can be useful for getting an idea of how a values varies over time is to plot the series along with a box plot of each cycle. I find this good for seasonal data as it highlights the variability in the months. The function allows the user to specify the frequency. This is the time period on which the data is based. In this example it’s 12, 12 cycles a year. If you look at the function file the time series is set up using the day as a parameter so the function should work OK with daily data as is.

plot of time series in excel

Time Series Decomposition

Often with time series it is required that different components, trend, seasonal variation and random variation are separated. The Decomposition function performs this task.

time series decomposition in excel

As well as producing the plot, the function output will also contain the values for the 3 components. By clicking on the ‘Output’ button on the EasyER function control the charts as well as the generated data will be copied into Excel.

Time series decomposition in excel

ARIMA Forecasting

A major part of time series analysis is to try and forecast future values and although this method doesn’t require a time series input I’ve added it as an example of the types of time series analysis that can be done in Excel with EasyER.

arima forecast time series in excel

This function also produces a diagnostic plot (not shown) of the auto correlation functions. The function output will also copy the numerical summary of the model to Excel.

Summary

The functions briefly outlined in this article are just simple examples to illustrate the ability of EasyER to make analysis of time series in Excel possible. If you are interested in playing with these functions then you can download the file.

Facebooktwittergoogle_plusredditlinkedin