This example started out as a way to construct interactive charts using Excel web services. The sample workbook now includes Power Query to obtain data from a badly structured web-based dataset, using Named Formulas to simplify workbooks by creating custom formulas, and the original charting samples. This is a longer example. Hopefully it will showcase some methods of interest.
The workbook can be found here. It started as a way to show how on-line Excel can be used to show interactive charts. You do not need to download the workbook to see it in action, but you are welcome to download it if you wish to run it locally. It contains no VBA code.
The British Antarctic Survey has compiled average monthly temperatures for several Stations in the Antarctic. The data is available at https://legacy.bas.ac.uk/met/READER/surface/stationpt.html
For example, clicking on the ALL link on the Byrd Station takes us to the data page for that Station. As you can see, this is not nicely formatted data ready to import into Excel.
To bring the data into Excel, we use Power Query.
Select the Data Ribbon, click From Web...
Enter the URL for Byrd and click OK.
Now select the last item in the list. It will be called Table produced on date. then click on Transform Data.
You will then see the Power Query Editor. Select all the month headings (click on Jan, hold the shift key down and scroll across to click on Dec). Then go to the Transform Ribbon, and click Unpivot Columns.
Rename the Attribute column to Month. Power Query now looks like this.
The temperature values are what we want. However, this data sometimes has a value in brackets next to the temperature. We could remove the values inside the brackets using Power Query. In this example, we will use Excel formulas to remove the brackets later on.
The value inside the brackets tells us how confident the boffins are that all data for that month was collected properly. We will ignore these values and just concentrate on the temperature. After all, this is an example on how to draw pretty charts, not a science paper.
Click on the Home Ribbon, then on Save Data to
In the Import Data dialog, make sure Table is selected, and put the data in an Existing worksheet. Here, I am inserting the Byrd data in an already created Sheet unimaginatively named Data.
The data has now been imported into an Excel Table. Now add a column named Temp to the Table. We want 'clean' temperature values in this column, so we add a simple formula to the column.
First an IF statement looks for "-" and returns an NA if found; ELSE
We FIND first occurrence of "(" in string and take data to the LEFT of that. If this gives an error, just take the data, because we have not found a "(".
And we use VALUE to convert the result of the formula to a number.
The formula looks like this =IF(Data!C3="-",NA(),VALUE(IF(ISERR(FIND("(",Data!C3)),Data!C3,LEFT(Data!C3,FIND("(",Data!C3)-1))))
BUT, we don't want to type that into a cell because (1) we are going to make a mistake, and (2) it looks like a dogs breakfast and it hides what we are trying to do from a casual user. So instead, select cell D3, then use the Formulas Ribbon, click on Name Manager, Click New... and enter the formula in the Refers To box, and CleanTemp in the Name box. It is important here to not enter absolute references (no $ signs).
Now we can enter =CleanTemp in the Temp column of our imported Table to get the required result.
The advantage of using Named Formulas in a workbook is that we can build complex nested formulas for specific actions and name them appropriately so that they become a simple formula that can be used in a workbook.
When building Named Formulas, proper documentation is very important. Otherwise, revisiting a complex workbook a few years down the track can become a debugging nightmare. Luckily, we can use the Formulas Ribbon and click on Use In Formula, go to the bottom of the list, and click on Paste Names, then Paste List. This lists all the Named Ranges in the workbook together with their formulas.
In the sample workbook, the Range Name list has been put in a Table in the Documentation sheet and a column named Notes has been added that explains what each Range Name does.
Note that we can create a Named Formula that references another Named Formula. For example, the formula FirstCell uses the OFFSET and MATCH functions to find the cell in Data that has the Temp heading for the selected Station. The FirstCell formula uses the SelectedStation Named Range to get the Station name from cell Report!$C$8.
Next, the NoObs function returns the number of observations in the data set for the selected station.
I may provide expanded information on each formula at a later date, but this should be enough to get you started in understanding the workbook.
Note that this is easily converted to allow comparisons of any time series, especially those that may involve seasonality. For example product sales or electricity consumption.