Skip to main content

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.

Comments

Popular posts from this blog

Mastering Medicare Cheat Sheet

Amy Schiffman and I have created an awesome 1-page Medicare Cheat Sheet. You can get it for free by joining the Mastering Medicare subscriber list . Here's a sneak peak

Dr. Alex Mohseni invited to speak at Brault’s Healthcare Leadership Forum about the future of Emergency Medicine services and how ED groups can adapt using telemedicine.

  Dr. Mohseni presented on the topic of how Emergency Medicine groups could expand their scope of practice to the virtual space to and how to do so in a financially viable way.

MasteringMedicare Medicare Cheat Sheet updated

 I'm excited to share the newest version, for 2021, of our MasteringMedicare Medicare Cheat Sheet. Feel free to share and provide feedback with suggestions and corrections.  Link to download the Medicare Cheet Sheet:  https://drive.google.com/file/d/1Nu45djmYDln7mSNG0v2Q57pkiACFvRv4/view?usp=sharing