Let's see how the the NHS "Statistical Process Control (XmR) Tool" Excel Template works with Public View's data.

This is the first of two posts looking at inserting Public View metric data into NHS tools for further processing.

We're not huge fans of using Excel for repetitive processes as it's very labour intensive and has no built-in error checking. We originally took a very close look at the NHS's published Excel tools which handle Statistical Process Control. Those tools were published in 2017. We implemented our own systems based on NHS advice in 2021, which we were aware differed in detail from the implementation in the NHS Excell tools.

Here's one of our SPC charts. This is a real chart for "Bed Occupancy - General & Acute" using data last published on 19th August 2021, so current data. We're using the Isle of Wight for this example, although they're not yet one of our customers.

Public View Chart

Here's how you create that same chart using the NHS's published Excel tools.

A. Get the NHS Template and set it up

  1. Download the NHS Template and save it to your machine.
  2. Open the downloaded template. Mine's called SPC-tool-excel-201-20202915.xlsm
    The name implies it may have been updated in 2020, but that's not documented.
  3. Click "enable editing"
  4. If you see a security warning, then assuming you downloaded this macro equipped spreadsheet from a legitimate source, click "enable content"

Set up the fields as per the Instructions tab, which you can select at the bottom of the Excel page.

Set the "set baseline" value to 12, the minimum possible with these settings (see "Notes" below).

B. Get Public View Data the hard way

It's "the hard way" because the next tutorial uses the Public View Database Service to do it the easy way.

  1. Display a chart and download the data from it using the in-chart menu.

  2. Open the Excel file you just downloaded.

  3. Select the first two rows - Series and Data.

  4. Press control-C to copy the data

  5. Left-click in the cell where you want the pivoted (transposed) data to be pasted

  6. Right click, select paste-special, then tick transpose and click "ok"
    Your data is transposed (rows and columns are swapped).

  7. Select the values from the new Values column and copy them.

C. Paste the Public View data into the chart

  1. Double check that the start date is the same as the Public View data, and that the duration (number of months or quarters etc) also matches. The date range of the data you're about to paste should obviously match the dates shown in the NHS template sheet.

  2. Left click in the first column, just to the right of the first date.

  3. Paste (Control-V etc) the data copied earlier into the column.

That's it. The chart should appear as below.

NHS Template Output

This image is CC marked, but we received permission to publish it anyway.
Our stuff isn't so marked, emulators please note.


Notes

  1. The point to which process parameters are computed is by default the "half way" mark for Public View, changable in real time by the user clicking any point on the chart. With the NHS Template you have to put the index of this point into the "Set Baseline" field, and you're not allowed to
    set it to values at or below half way. We used the 12th point (they label it "months" in the template, but these are of course quarters).
  2. The rules Public View was provided with (see our FAQ or this blog post may differ from those on this Excel template when it comes to the variation icon; we're sure we're correct according to the rules we have. That's the blue circle with "P" in it for us: we think these last 6 points are all blue, so this is better than target. The NHS template shows a grey "we're not sure" icon... we think that's a possibly defect, or they have different rules we're unaware of.

The Easy Way

Well the easy way is just to use Public View, but the easy way to get this data into an Excel template is to use
a direct connexion directly to the Public View Database System. That way the data's automatically kept up to date, and no one has to manually "lift and shift" copies of it around, which is error prone and unreliable.

That's covered in the next post in this series.