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.
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