Implementing Time Series

Time evolving phenomena are fundamental to many disciplines. Understanding what happened and foreseeing how different variables will evolve is key knowledge for making correct, informed decisions.

Time series analysis is a broad field, with many different methods to detect patterns, predict behaviors, and decompose the time evolution into known and previously studied shapes. We are going to discuss some of them, focusing on the ones that are easily solved using Excel. The idea, as usual, is that our machines learn about the details in the data and can extract useful knowledge about the past and the possible future developments.

In this chapter, we will cover the following topics:

• Modeling and visualizing time series
• Forecasting time series automatically in Excel
• Studying the stationarity of a time series

...

Technical requirements

To complete this chapter, the reader will need to download the AirPassengers_modified.csv file from the GitHub repository at https://github.com/PacktPublishing/Hands-On-Machine-Learning-with-Microsoft-Excel-2019/tree/master/Chapter07.

Modeling and visualizing time series

We have seen that doing a preliminary data analysis and visualizing the dataset is the first step in any machine learning project. Time series are no exception. So, we will start by exploring time series and learning about its different characteristics.

In the case of a time series, a preliminary analysis implies modeling it; that is, understanding whether it is periodic, whether it shows a given tendency (increasing or decreasing with time), or whether it is stationary (mean and variance of the values don't change over time), among other measures. Visualization plays a fundamental role in this analysis, since many of the time series characteristics can be deduced using a graphical representation of the data points, even if there are numerical methods to calculate them.

Let's use a popular dataset to illustrate the modeling and visualization...

Forecasting time series automatically in Excel

Now that we have forecast a time series the hard way, understanding every step, we can do it the easy way. We will use Excel's built-in functions to predict the future number of passengers. Perform the following steps:

1. Select both columns, TravelDate and Passengers, corresponding to the time and number of passengers.
2. Navigate to Data in the main menu.
3. Select Forecast Sheet (see the following screenshot for reference):

4. A window will pop up, showing a preview of the forecast and giving us the chance to change some parameters by clicking in Options:

• • Forecast End: We can choose the end of the forecast period. By default, Excel forecasts three seasons ahead (more on this to follow).
  • Forecast Start: We can do the same with the start of the forecast period. Default is the last point in time of our time series.
  • Confidence interval...

Studying the stationarity of a time series

Most methods for a time series forecast rely on the fact that the series is stationary. This makes sense, since this increases the probability of repeating a certain behavior in the future and makes the prediction easier.

How can we know whether a given time series is stationary or not? There are formal, statistical methods to measure this, but we can also look at some properties of the series. There are three main checks of stationarity in practice:

• The mean value is constant (does not depend on time).
• The variance is constant.
• The covariance of the elements i and i+m is constant.

In our previous example, in the Modeling and visualizing time series section, we plotted the moving average (mean) and variance. If you revisit the diagram, you will see that none of them is constant with time, hence the series is non-stationary, and we...

Summary

We have seen a step-by-step method to decompose a time series and forecast its future values. This can help us, at least in general terms, to predict the outcome of different processes. Time series can be studied both graphically and numerically, extracting their characteristics and using them to understand how they will behave in the future. We have also seen that this can be done automatically in Excel, with the risk of using it as a black box and not understanding the full forecasting method. More advanced techniques exist, and we will discuss them in future chapters.

The next chapter will show you how to build some basic diagrams in Excel and how to use them to gain insights on your datasets.

Questions

1. In our forecast, we modeled the increasing part of the time series using the trend function generated by Excel. We could also have used the moving average values. How can that be done? Try it and compare the results.
2. Change the values of the seasonality and confidence interval and study how the forecast diagram and parameters change.
3. How can you calculate the covariance between two values in a time series?
4. A possible way to make the variance independent of time is to take the logarithm of the time series values. Try this in the air passengers series and check the values of the variance.

Further reading

• Time Series Analysis and Its Applications, by R.H. Shumway and D.S. Stoffer
• An Introductory Study on Time Series Modeling and Forecasting, by Ratnadip Adhikari R. K. Agrawal