Once in a while I find myself with a lot of really good information that I’d like to use. Great! Put it into Excel. Not always so easy, even if it’s already computerized. I ran across this recently in looking at Medicare payments for doctor visits. The mymedicare.gov site tells you everything about each of your doctor visits–dates, charges, payments, procedures. You can look at it, or you can download it as PDF or text. Not one of these gives you the one-row-per-doctor-visit format you’re used to seeing and working with in Excel. The text version seems to be the best, but here’s what it looks like if you copy it into Excel (plus a lot of blank lines that I’ve removed):
One column, with keywords for what you’d like to become columns. Then below each claim are several “claim lines,” each with its own data, spanning several rows. This is good information, but it complicates things, because the number of claim lines varies, so you can’t just count off to find the next claim.
It’s those keywords that make it possible to work with this, because they’re in the same cell with the data. For example, to see all the dates you had a doctor visit, filter this on “Service Start Date”. Then copy that somewhere else. Filter on “Provider:” (don’t forget the “:”) to get a list of doctors that you can line up with the dates, and so on. Add column headers, do a global replace to get rid of the keywords, and you’ve got it.
I don’t usually need macros, but this is one of those tedious tasks that calls for a little automation. Here’s a data conversion spreadsheet, Medicare with macro. Yes, it’s all blank, but it includes a macro called Medicare. Put your Medicare text data in the first column of Sheet 1, run the macro, and you’ll get something that looks like this in Sheet 2. It also lists all the procedures in order.
Now you can sort it by date, filter it by doctor, or use any of the other ways Excel can examine a bunch of information. Pivot tables, anyone? If you have data in a similar form from some other source you can edit the macro. Wouldn’t it be nice, though, if everybody gave us data already in Excel-friendly form?