Medical aid and insurance in general generates a lot of data which requires reconciliation to match what has been paid, what is outstanding and what will never be paid. A client required the ability to (easily) reconcile the records they received from the various medical aids in South Africa to their records. In order to complete this there were a number of mini projects to get all the data into a comparable format in order to be able to facilitate reconciling medical insurance claims.
Aggregate CSV files of the Medical Claims paid out
First step was to take the available data received from the Medical Aid (downloadable for 2 week periods in CSV format) and get them into a single file. It needed to be set up in such a way that as more CSV’s are downloaded they could be saved into a folder and this would (automatically) be added into the final database. A number of validations were required in order to make sure that duplicate csv’s weren’t entered.
So we needed to be able to put the CSV’s into a folder as per below
and automatically get a summary report for all CSV’s in a single database format as shown below (names and numbers have been changed but this is an extract of the summary of all the data in the CSV folder shown above)
Once in this format it was easier to start the reconciling!
Get the Medical Practice Software in the same format
Now that the medical claims were in a flat file format, the various reports were extracted from the medical practice software and converted into a similar format on a similar basis i.e. going forward you would just need to add the new data and the rest would be done automatically.
Matching the insurance claim to appointment
The next step was matching as many as possible of the claims versus appointments. This created a single flat file of all the practice records versus the matching medical aid claims (as well as the resultant mismatches).
Create Exception Reports
The last step was to create reports that made it easy to interrogate any of the data. So it was possible to:
- On a day by day basis see appointments, payment method and payment received ( or not)
- Using the same data see a patient by patient analysis
- Compare records based on individual medical aids.
All of the above is updated by placing the new information in the correct place and refreshing the spreadsheet.
If you need any help with reconciling medical insurance claims (or any sort of claims) contact us at info@AuditExcel.co.za .