Spectral analysis in Excel tutorial

This tutorial will help you set up and interpret a spectral analysis on a time series in Excel using the XLSTAT statistical software.

Dataset to run a spectral analysis

The data have been obtained in [Anderson, T.W. (1971). The Statistical Analysis of Time Series. John Wiley & Sons, New York], and correspond to activity data of the sun. Our goal is to determine if cycles characterize this activity by using Spectral analysis, a method that is based on the Fourier transform. The time series is composed by 176 data covering years 1749 to 1924.

Setting up a spectral analysis

After opening XLSTAT, select the XLSTAT / Time / Spectral analysis command.

Once you've clicked on the button, the Spectral analysis dialog box will appear. Select the data on the Excel sheet.

The Variable to analyze corresponds to the series of interest, the sunspot data. The Time variable corresponds to the years.

The option Column labels is activated because the first row of the selected data contains the header of the variable.

For the spectral density, we choose to use fixed weights. We need to select the weights on the Excel sheet.

The estimate of the spectral density corresponds to the smoothing of the periodogram, and it is often a better estimator of the real spectral density of the observed phenomenon.

The computations begin once you have clicked on OK. The results will then be displayed.

Interpreting the results of a spectral analysis

The first table displays results of the white noise tests. These tests allow to test if the time series can be considered as a white noise or not. In our case, it clearly appears, when looking at the p-values, that the series if significantly different from a white noise at a significance level of 0.05.

The next results table is used to build the two charts of the periodogram and the spectral density. The two charts are represented on both the frequencies scale (varying between 0 and p), and on the period scale which unit is identical to the Time variable. Hereunder are displayed the charts on the period scale.

We notice that the smoothing is efficient as the chart of the spectral density is smoother than the chart of the periodogram. In order to analyze the peak of the spectral density, we have modified the scale of the abscissa axis.

We notice that the peak corresponds to a period of 11 years. That means that the activity of the sun varies with quite regular cycles of 11 years.