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

How To Automatically Upload a Zoho File Attachment to Dropbox from Zoho Creator using Dropbox API v2 and Zoho postUrl

I'm an idiot, so this took about 6 hours for me to figure out, but apparently many people have not been able to figure this out, and I could not find a complete and working solution online anywhere. If you want to be able to upload a file to Dropbox from Zoho Creator, you have to create a custom function like below (here I called the function "Dropbox", under namespace "Create"). In the function, the ID is being passed to it from the underlying form entry. void Create.Dropbox(int id) { url = "https://api.dropboxapi.com/2/files/save_url"; headers = {"Authorization":"Bearer XXXXX","Content-Type":"application/json"}; data = {"path":"/FFFF/TTTT.XXX","url":"YYYY"}; datastring = data.toString(); response = postUrl(url,datastring,headers,false); } ... where XXXXX is your secret Dropbox Token, and YYYY is the url of the file you are uploading, and FFFF is the Dr

How to track CME - guest post published on Knack blog

I am happy to announce that my detailed post on how I built CMElog.org using Knack , Zapier, Dropbox, Mailchimp, Mandrill, and Google Drive has been published on the Knack blog: In this post, I dive into details of how to use Zapier to build automated services, like CMElog.org, in which medical providers' continuing medical education credits can be automatically extracted, identified, copied for back up, organized, and accessed.