More fun with Sunspot data and Excel

Excel has a neat trend-line function that can be used when plotting data. I did some exercises using the trend-line function in Excel and to compare the output with the results obtained from the LOESS function. The data set that I used is in this example is that of the sunspot cycle 23, downloaded from the Royal Observatory of Belgium.

Lets look at the raw sunspot data for sunspot cycle 23 plotted in Excel:-

The raw data points

Using the polynomial trend-line we get the following:-  

Trend-line fitted using polynomial to the 6th power  

Now I superimpose the curve obtained by plotting the LOESS 1000 days function on the graph:-

The LOESS 1000 curve, Sunspot 23 raw data and trend-line
 fitted to the sunspot raw data.

What is my conclusions?

1. Using the inbuilt Excel trend-line facility with the polynomial set to the 6th power yields a curve that is remarkably similar to the curve obtained from the LOESS function set to 1000 data points (days).
2. If the Sunspot 23 data is subjected to the LOESS function set to 1000 days  a curve that is very close to the inbuilt curve fitting functionality of Excel is obtained.
3. I cannot get similar results using the moving average data.
4. As an Engineer I would put more trust in the output from the LOESS 1000 function to describe the shape and outline of  Sunspot 23 than the results obtained from the moving average method of calculation.

Why? It just fits neater, and that is always a good indication that I am on the right track.

Sunspot Cycle 24 update for year-end 2013

Note the source of the sunspot data is:-

WDC-SILSO, Royal Observatory of Belgium, Brussels.

The Royal Observatory of Belgium have updated their website and it is now possible to download the sunspot data in a csv file format.That saves a lot of time for people who want to use the data in  spreadsheets, thank you to the good folk at that institution.

Progress of the sunspot cycle 24.

Note that by year-end the sunspot cycle did not come to a close as the sun is still merrily firing away on all cylinders. So, at this stage we are still experiencing sunspot cycle 24. It appears as if this cycle is going to a bit longer than the forecasts made by some experts, and given the complexity of the issue, that should come as no surprise.

Sunspot Cycles as a set

Up to now I showed plots of the recorded sunspot cycles individually. I decided to do some plots of the sunspot cycles on a continuum. The limitations in Excel stops me from plotting all the series in one go, that is using the daily spot counts as data.

Raw data with observed counts