work / automation / co-pay claims billing automation

Daily claims, compiled and reconciled automatically.

Daily claims files were compiled by hand into billing. I built a workflow that reads each day's files and builds the full workbook, reconciliation included.

role
Sole automation engineer
client
Canadian accounting firm
status
Works end to end

Languages

JavaScript

Tools & platforms

n8nGoogle DriveGoogle SheetsQuickBooks Online API

AI & LLM

to build it
ClaudeChatGPTPerplexity

Skills

File ingestionData transformationReconciliationWorkflow automation
01// how I approached it

The thinking behind it

  1. 01 · What I noticed

    The same daily file was copied, pasted and reconciled by hand, every single day.

  2. 02 · What I decided

    Let the file trigger everything: build the daily tab, the summary and the reconciliation automatically.

  3. 03 · What I delivered

    A billing workbook that builds and checks itself, working end to end.

02// old vs new

Old system vs new system

Old systempeople as the glue
  1. Download today's file
  2. Paste into the master sheet
  3. Rebuild the pivot
  4. Reconcile at month end
New systemthe system does the work
  1. File lands in Drive
  2. Daily tab built automatically
  3. Summary updates
  4. Reconciliation checked
03// how it works

Watch it run

Click through the four steps. In step 3, click any box to see what it does.

synthetic data · no client screens
Reconstruction with synthetic data · no client screens
04// the hard part

A workbook that builds and checks itself.

The same file arrived every day and was handled by hand every day. Now the file itself starts the work: daily tabs, a combined view, a summary and a reconciliation against the books, plus a troubleshooting tab for anything odd.

05// key decisions

Why it's built this way

decision 1

Let the file be the trigger

Nobody has to remember to run anything.

Trade-off: depends on files arriving in the right place.
decision 2

Reconciliation built in

Totals are checked against the accounting system automatically.

Trade-off: more logic in the workflow.
decision 3

A troubleshooting tab

Odd rows are surfaced instead of hidden.

Trade-off: one more tab to read.
06// lessons

What made it work

  1. 01

    Daily chores are the best automation targets

    Small, frequent tasks add up fastest.

  2. 02

    Check your own output

    A workbook that reconciles itself catches mistakes early.

  3. 03

    Make problems visible

    A troubleshooting view saves hours of hunting.