Because the final report requires merging data from multiple different databases, the final summary isn't able to be produced in a single Epic report. The data itself wasn't complicated — it was the repetition that was cumbersome. The same files, the same formulas, the same structure every single month. That kind of work is exactly what automation is for.

The solution was a PowerShell script that handles the file retrieval and a VBA macro that handles everything inside Excel. They're designed to work together: the script pulls the files and loads them into a dated copy of the template, then hands off to the macro, which processes the data and builds the summary. The whole process now completes in under two minutes.

Now, with one quick PowerShell prompt, I can compile an actionable report that our leadership team uses to analyze our visit utilization metrics. All of the time saved with this tool is freed up for me to complete other tasks.

The core question is simple: of all the charges we have, how many have actually been billed? But answering it required matching two different lists against each other and counting unique accounts rather than individual transactions.

The report runs at two points in the month so you can see progress. The 16th snapshot captures an early read on the billing cycle; the 23rd shows how much ground was covered in the week after. The side-by-side summary makes that comparison immediate. We can then compare performance month-over-month to verify that we are getting clean claims billed out quickly and not falling behind at any point.

The automation handles everything from file retrieval to the final summary. The PowerShell script connects to the server, finds the four files, downloads and archives them, and loads the data into Excel. The macro takes it from there — filtering out excluded plan types, building the cross-reference, and producing the two summary sheets automatically.