Sunday, September 11, 2016

How to connect Google Sheets to live Medicare data

This is pretty cool. Medicare/CMS posts a lot of their data in two main websites for public consumption:

Data.Medicare.gov
Data.CMS.gov

I don't know why these two are separated, but it does seem like they host two separate datasets of healthcare data.

One can connect Windows Excel to an OData feed from Medicare/CMS, but I can't figure out how to do it with Mac Excel '11 (I don't have a Windows computer). I prefer using Google Sheets anyways, so I wanted to find an easy way to connect Sheets to Medicare/CMS data.

Here's the technique I have found, and please let me know if there is a better way:

1. First, access the table of data you want. For me it was this one:

https://data.cms.gov/Public-Use-Files/Medicare-Provider-Utilization-and-Payment-Data-Phy/ee7f-sh97

2. Because these tables are usually massive, you will probably need to login and create a saved view of a subset of the data.

Here's the subset of data I created, where I did a search for "Mohseni":

https://data.cms.gov/Public-Use-Files/test1/5fxd-ai9f



3. Click on "Export", then "Download",  and then right click and save the link to the CSV file. Let me clarify: don't click the CSV file to download it, rather right click and save the LINK to the CSV file. It should be something like this:

https://data.cms.gov/api/views/5fxd-ai9f/rows.csv?accessType=DOWNLOAD

4. Go to Google Sheets and use the following function:

=IMPORTDATA("url")

... and put your url that you just copied in the url spot.

=IMPORTDATA("https://data.cms.gov/api/views/5fxd-ai9f/rows.csv?accessType=DOWNLOAD")

...

Now, every time the data is updated, your Google Sheet will update as well.

This process will not work if your data set is too large.
Post a Comment

My piece on my experience with the healthcare system as an Emergency Medicine physician

My piece on the challenges and failures of the US healthcare system is being featured on the front page of Medium today. Would love to hear...