We’ve updated our workbook that imports the latest COVID-19 statistics and combines with population data through Excel 365’s Stock Data Type.
No technical changes in the workbook but we’ve added a few more ‘interesting’ or outlier countries. Plus some repeat help tips.
The full workbook requires a recent Excel 365 for Windows — only Excel 365’s updated in the last few months have all the features necessary (especially Get & Transform, Geography Date Types and Map Charts).
Refresh All data connections
Refresh the Coronavirus data from Data | Refresh All. A recalculation (F9 or Formulas | Calculation | Calculate now) does not update data connections. The last update time is shown at the bottom of the table. That’s something done with a little hack of PowerQuery since, astonishingly, it’s not available directly in Excel.
Update Table Sorting
Even then, the sorting of the table is not updated which means the conditional formatting might be out of order. Automatically resorting would require some VBA code (amazingly) and a change of workbook type which might concern some people (rightly). After updating the data connections, the colors/sorting might not be correct. Fix that by choosing the pull-down next to either ‘per capita’ column and choose ‘Sort Largest to Smallest’ again – that will force Excel to resort and recolor the table.
Handle with care
We’ve added more countries to show some anomalies and demonstrate how these COVID-19 stats have to be treated with care.
- The Coronavirus statistics are counted in different ways in each country. Some count only those who has tested postive for virus before death, others includes people who have matching symptoms but no confirming test. There are many cases were COVID-19 is a contributing factor which may, or may not, be listed on the death certificate.
- Some countries don’t have comprehensive system of mortality reporting. People can often die without any doctor attending or certifying cause of death. At the moment, countries like the UK are allowing doctors to certify death ‘over the phone’ for cases attended by nurses in care homes.
- Other countries have a government trying to understate the problem.
- San Marino and Andorra are tiny countries in Europe (think Freedonia or Grand Duchy of Fenwick.
- Belgium is a mystery since they’ve taken similar measures to surrounding countries. The high rates could be caused by more accurate reporting rather than a truly worse spread of the disease.
Statistical experts are regularly warning about using these numbers to make firm pronouncements. This is an ongoing situation with varying quality of statistics. It’s clear that some countries (UK, France, Italy) are suffering worse that similar places (Germany, Denmark, Canada). Australia and especially New Zealand have benefited from their remote location and fast, firm action from their governments.
But it’s way too early to make firm statements about how each country has fared based on the numbers coming in on a daily basis.