Quick Guide to Using the Online Medicare Physician Billing Database

Since 2014, CMS has posted annual files of Part B billing data by provider name.  Available files are for CY 2012, CY2013, CY2014, and in June 2017, CY2015.

Originally, you had to download many very large files by alphabet letter of provider's last name, or download a 2 GB database too large for Excel.   Now, you can go to the online web interface and instantly sort for data of interest and download that data quickly into an Excel file that can be easily studied.

For more about the history of this important data file and an example of analyzed data, here.

Some process steps are described below.

Find the home page for the data set here.   The path would be CMS home page > Research and Statistics > Medicare Provider Utilization > Physician and Other Supplier Data 2014 (or 2015).

Click on "Detailed Data: Interactive Dataset: Medicare Physician and Other Supplier PUF CY2014 Interactive Dataset."   Here.   Click yes on a CPT copyright screen.  You now are looking at the tip-to corner of a massive, 2 GB dataset with a couple dozen columns and millions of rows.

What you want to focus on is the FILTER button (above).  Click on this and see a drop down box that includes the button: "+ Add A New Filter Condition." (Below).

Click on "Add A New Filter Condition."  There will be another dropdown - you can choose to filter on NPI, Last Name, City, State, etc.   For example, filter on "HCPCS Code" 95004 to see all data on allergy skin testing.

Instantly, the screen you see in your browser is a far tinier dataset of the 4,114 providers who billed Medicare for "95004" skin allergy testing in CY2014.

You're almost done.  Just click the pale blue EXPORT button and export CSV EXCEL (comma delimited file for Excel).  Save on your hard drive as 2014 95004.csv.  

Open it in Excel, and resave it right away as Excel Workbook.

You have the 900 kb of data you need and are free of the 2 GB scale of the original data.   Now, you can delete unnecessary columns and sort and analyze with ease.

One quirk.  The spreadsheet holds a column for "number of services allowed," and one for "average Medicare allowable price per service."  You probably will want to make an additional column that multiplies these two columns together to get the total dollars allowed for that provider for that service in that year.  E.g. 500 services allowed X $9.00 per service becomes a new column of $4,500.00.

_______________________

I can't give a course in Excel and I'm not an expert with it, but two particularly useful functions are these.

"Sort and Filter" (upper right), click on  Custom Sort, click "Expand Selection" if offered, be sure "My Data Has Headers" is checkmarked, and filter on one or more columns.  For example, you can filter by state, then, by provider type, then, by services allowed in the year.

To quickly see a line plot of data, select an entire column, click Insert, and click "Recommended Charts."  This instantly gives you graphics like this one: