A Simple System for Managing Medical Expenses

If you're like me, you've experienced the bewilderment and frustration that comes from trying to make sense of bills from medical providers (doctors, labs, hospitals, etc.) and statements from health insurers. They never seem to add up. Information is broken down in strange ways, and medical provider bills often lump together items that show up on several separate insurance statements.

For years, I just paid the medical provider bills and hoped that things were accurante (and for the most part, honest). Then, back in 1998, I had surgery and was faced with a pile of bills and insurance statements that I couldn't make heads or tails of. To try and make some sense of the madness, I created a very simple system to manage this information, to track medical visits and procedures, to have some idea of what bills and insurance statements to expect, and to match up medical visits and procedures with bills and insurance statments to keep them honest.

The system consists of a Microsoft Excel spreadsheet (the download is in Excel 2000 format) and a file folder to hold paper records. Data about visits/procedures, insurance statements, bills, and payments are entered into the spreadsheet with one or more lines per visit/procedure as required. The previous statement recognizes that some events generate more than one insurance statement and/or bill. For example, surgery may generate bills from the hospital, surgeon, anesthesiologist, radiology, and lab. The system handles each one of these as a separate transaction, with transactions generated either by medical visits/procedures, insurance statements (sometimes you don't expect something associated with a visit), or a medical provider bill.

The Excel Spreadsheet

This is what the spreadsheet looks like:

There is one transaction per spreadsheet row. The blue section contains info about individual visits/procedures. As stated before, creation of a new transaction record is triggered by a medical visit or procedure, or receipt of an insurance statement or provider bill. The meanings of columns in the blue section are as follows:

The gray section deals with information from insurance statements. The system was conceived with the idea that each row is associated with one insurance statement, but sometimes this may not be the case. As a general rule, there should be no reason to ever need to combine insurance statements into one row, but exceptions do and will come up. I've never dealt with what to do in this case as it hasn't happend to me so far, but one option would be to add up info from the various statements and enter the result. The meanings of columns in the gray section follow: The light green section deals with provider bills and bill payments to providers. Again, the system assumes that you will receive only one bill from a provider per visit or procedure. If you receive more than one, you can either handle it as a separate transactions and split information between transactions (I've done a 110 and 110a transaction a few times by inserting a row under a transaction, then copying the formula in the gray "Paper Balance" to the new row), or you can use the Excel "Split" finction to split the light green section into more than one row and enter each bill separately. The meanings of columns on the light green section follow:

The File Folder

This is just an ordinary file folder is a file cabinet that's easily accessible. The reason this is a file folder is that you'll be accumulating a lot of letter size (8.5" x 11") papers that will need to be stored in an organized manner. Where you store it is up to you, but you'll need to access it every time you have a medical visit/procedure or receive an insurance statement or bill.

How the System Works

When you have a doctor visit or procedure, fill out the blue section with Date, Patient, Provider Name, Description of Services, and funds paid at the time of service. If the first thing you see is an insurance statement, such as for the surgeon's bill after surgery, you can fill out both the blue and gray sections. If the first thing to come in is a bill, as with the insurnace statement, you can fill out both the blue section and the "Amount Provider Billed" column of the light green section.

Unless you're having surgery or procedures, the normal sequence (such as a doctor visit) will be visit, then insurance statement, followed by a bill, followed by a payment. Here's how such a visit would be entered:

That's all there is to it! If a dispute arises later with a provider or insurance company, you can locate the transaction in the spreadsheet and use the "Ref #" to find the documents to deal with the problem. Simple and easy! Using the logic above, you should be able to determine what happened in the transactions in the sample spreadsheet shown above.

Computer System Requirements

This system assumes the Windows platform and Microsoft Excel. The Download section provides a ZIP file with the spreadsheet in both Excel 2000 format and in a format for Excel 5.0/Excel95 and later. If you do not use Windows or Excel, you should be able to import the Excel5.0/Excel/95 format file into whatever spreadsheet you have.

This web page is provided in the download as an Adobe Acrobat file, If you do'nt have adobe reader, you can download it from Adobe using this link.

A Few Ending Notes

After a while you have a lot of records, so I tend to mark the text in previous years transactions in italics to set them apart from the current year.

Once in a while, something wierd happens and I edit the spreadsheet to state something that wouldn't be obvious. For example, one provider didn't bill me or the insurance company for 4 months. The resulting insurance statement indicated that I didn't owe the provider anything because they breached their agreement to submit to the insurance company within 30 days. I used the Excel "cell merge" function to merge all cells in the light green section and typed in what was on the insurance statement. When the provider billed me, I opened the spreadsheet, saw that I owed them nothing, and contacted them and worked it out without cost.

Using this system, I've also found providers that billed me without deducting my copayment. I was too chicken to haggle about the first one, but never again!

I hope you find the system as useful as I have. Since I created the system in 1998, I've yet to have medical transactions that I couldn't figure out.


This ZIP file contains the spreadsheet (in Excel 2000 and pre-Excel 2000 formats) and this Web page in Adobe Acrobat format. Download and open the ZIP file to your computer. Make a Start Menu shortcut to the Excel Spreadsheet (and the instructions if you wish).

Disclaimer. Any and all opinions expressed on this page are solely those of the author. The author accepts no responsibility for the accuracy of data provided on this web site, nor does the author accept any responsibility for the use and /or misuse of information provided on this web site. It is the full and sole responsibility of the user to determine whether and/or how best to use the information provided within their own circumstances, and it is the full and sole responsibility of the user to accept any and all benefits and/or consquences of their actions. Please see my Privacy Policy.

Copyright 2001-2009, Tim Sharpe. You are free to use this information for personal, non-commerical use without restriction. All rights reserved for commerical, organizational, or government use. Questions or comments to tim@beaststwo.org. Flames to /dev/nul.

[Python Powered]
All the Perl that's Practical to Extract and Report

28261 visits since