This is a bare bones tutorial, focused only on the basics of connecting Excel with publicly available data.
The end result is a simple Excel table that shows the most recent testing data by state.
The approach is general and can be used with all kinds of public data.
The complete Excel file is attached below for reference.
The data is not perfect, and varies state-by-state.
you could read more about it on the website.
The API for requesting data isexplained here.
I’ll update when I have better info.
Getting the data into Excel
The best tool for the job is Power Query.
Power Query is part of Microsoft’s BI suite.
In a nutshell, Power Query is a tool for fetching, cleaning, and shaping data.
Familiar, yet distinctly different.
Never fear, we are going to keep things as simple as possible.
There are many ways this example can be improved or embellished once you get things working.
The last two steps are done in power query.
when you obtain the query set up, you might right-click inside the table and select refresh.
The steps below need to be updated slightly, but the query in the attached Excel workbook is current.
These are the steps I used to create the query that fetches data from the tracking website.
Refreshing data
To fetch the latest data, right-click in the table and select “Refresh”.
Since it lives in anExcel Table, it stays up to date when date changes.
