The attached Excel workbooks include a working query, and each query returns data to an Excel Table. You can refresh the data by right-clicking in the table and selecting “Refresh”. When data has changed, you will see more recent data appear. If you’re new to Power Query, this article explains how to build a single query in more detail.
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 you can’t yet edit or create queries.
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 below the descriptions. To download fresh data, right-click inside the table and select “Refresh”. To inspect or edit a query, click Queries and Connections on the Data tab of the ribbon, then double-click on the query. This article explains how to build a single query in more detail.
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 the query was created.
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.
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, a web page is not as reliable as a data file, since the structure of a web page is more complex and might change. Note: you’ll get an expression error if you try to refresh on a Mac. Power Query on Mac does not support web sources yet.
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. Data is by country by day, and would be a good candidate for a Pivot Table to provide totals.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
