XLSTAT: Spectral Analysis in Excel

 

Dataset for Example

The attached 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

• Open XLSTAT.

• Click on the Forecasting/ Time series analysis menu and select the Spectral analysis feature.

• After clicking the button, the Spectral Analysis dialog appears.

In the General tab:

• Select the sunspot series as the variable to analyze and years as the time variable.

• Keep the Column labels option activated since the data has headers.

In the Options tab:

• Select fixed weights for spectral density and specify the weights from the Excel sheet.

• This smoothed periodogram provides a better estimate of the true spectral density.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first table shows white noise test results. The p-values indicate the series significantly differs from white noise at the 0.05 significance level.

The next table provides data for the periodogram and spectral density charts, shown here on the period scale matching the Time variable.

The spectral density chart is smoother than the periodogram, showing effective smoothing. To analyze its peak, we adjusted the x-axis scale.

The peak at 11 years indicates that sun activity follows regular 11-year cycles.