There’s a lot of data here, but how can I make sense of it?

notimeOnce 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 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):

medicare text

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.

Medicare converted

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?


Leave a reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s