Download Coronavirus data to Excel – Best article

This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook with a basic query already built. If you’re new to Power Query, this article explains the process of building a single query in more detail.

The attached Excel workbooks have a working query already created, and each query returns data to an Excel Table. As long as you have Excel with Power Query, you can refresh the data by right-clicking in the table and selecting “Refresh”. When data has been updated since the last refresh, you will see more recent data appear.

Requirements

This project depends on Power Query, so you’ll need Excel 2013 or later on Windows. On a Mac, you can refresh queries with Office 365 Excel, but can’t yet edit or create queries yet. 

Sample Coronavirus Data

The purpose of this article is to show examples of how to get Coronavirus testing data into Excel. I can’t vouch for the quality of the data. The links below provide more information about each website. The COVID Tracking Project provides a grade for each state.

Excel workbooks are attached at the below descriptions. To inspect or edit a query, click Queries and Connections on the Data tab of the ribbon, then double-click on the query.

United States – current

Source: COVID Tracking Project (https://covidtracking.com/api)

This data contains the latest snapshot of Coronavirus testing data for the United States at the state level. It contains current totals only, not historical data. This page describes in detail how Power Query is set up to download the data.

COVID-19 US States Current

United States  – historical

Source: COVID Tracking Project (https://covidtracking.com/api)

This data contains historical Coronavirus testing data for the United States at the state level. Each row in the data has a date, and the next column is the state.

COVID-19 US States Historical

Worldwide – current

Source: Worldometers (https://www.worldometers.info/coronavirus/)

This is an example of data retrieved directly from a table on a web page. In general, connecting a web page is less reliable, since the structure of a web page can be complex and less reliable.

COVID-19 World Current

Worldwide – historical

Source: EU Open Data Portal (https://data.europa.eu/euodp/en/data/dataset/covid-19-coronavirus-data)

This example shows how you can connect directly to an Excel workbook. Data Europa has a JSON api as well. This data would be a good candidate for a Pivot Table.

COVID-19 World Historical

 

Leave a Reply