I kept expenses in a spreadsheet for a year. The spreadsheet was never the problem. Opening it was. By the time Numbers had loaded and I had scrolled to the last row and remembered which column held the category, the receipt was back in my pocket. September ended with eleven rows for a month that had forty purchases in it.
Expense tracker apps fix the friction by asking for an account, a bank login and a subscription. The job is four fields and a SUM. This flow keeps the four fields and the sum, and puts the form one keystroke away.
What it does
Control-Option-E opens a small form: amount, category, an optional note, an optional receipt file. Save writes one row to a local database and confirms with a notification. Every morning at 9:00 the flow checks the date, and on the 1st it adds up the previous month by category and opens the totals in a window.
The flow
Log an expense
Keyboard Shortcut is set to ctrl+option+e with Consume Key on, so the app in front never sees the press. It needs Accessibility permission; without it nothing fires.
The form is a Mid-Flow Form, listed as Form in the Actions palette, named Log an expense. A hotkey cannot start the Form trigger, which waits for you to press Run, so the hotkey starts the flow and the form is its first step.
| Field | Type | Rules |
|---|---|---|
amount | Number | Required, minimum 0.01 |
category | Select | Required. Groceries, Eating out, Transport, Household, Work, Other. Default Groceries |
note | Text | Optional |
receipt | File | Optional |
The panel stays on top, follows you to the active Space and works over full-screen apps. Validation runs before Save does anything. An empty amount keeps the button disabled, and so does 12,50: the number field wants a decimal point even on a Mac set to a comma locale, so type 12.50. Escape or Cancel ends the run cleanly and writes nothing.
Save to Database, named Save expense, writes to a table called expenses with four fields: amount = {{amount}}, category = {{category}}, note = {{note}}, receipt = {{receipt}}. Each value is a lone {{...}}, so it keeps the type the form gave it. The amount is stored as a number, not the text "12.5". A note you left empty arrives as null with the key present, so it is stored as NULL rather than skipped. The receipt column holds the file's path, not a copy of the file, so keep receipts somewhere they will not be cleaned up.
There is no schema step. The first save creates the table with those four columns plus the two Watchflows manages, id and created_at (a UTC timestamp). Notification then confirms with {{amount}} in {{category}}, row {{rowId}}.
A monthly total by category
The Schedule trigger has no monthly option. One node is one set of weekdays at one time. So this Schedule fires every day at 9:00, and Run SQL decides whether today is the day:
SELECT month,
SUM(n) AS entries,
printf('%.2f', SUM(spent)) AS total,
group_concat('<tr><td>' || category || '</td><td>' || n || '</td><td>'
|| printf('%.2f', spent) || '</td></tr>', '') AS table_rows
FROM (
SELECT strftime('%Y-%m', created_at, 'localtime') AS month,
category, COUNT(*) AS n, SUM(amount) AS spent
FROM expenses
WHERE strftime('%d', {{scheduledFor}}, 'localtime') = '01'
AND strftime('%Y-%m', created_at, 'localtime')
= strftime('%Y-%m', {{scheduledFor}}, 'localtime', 'start of month', '-1 month')
GROUP BY category
ORDER BY spent DESC
)
GROUP BY month{{scheduledFor}} is the occurrence the Schedule is firing for, and Run SQL binds it as a value rather than pasting it into the text. On any day but the 1st the WHERE matches nothing and count is 0. On the 1st you get one row: the month, the number of entries, the total, and the table rows already built as HTML. That last part matters because HTML Template fills in {{...}} values and has no loops, so the SQL builds the rows and the template drops them in with {{records.0.table_rows}}.
The Condition The 1st? passes when count is greater than 0. Report page wraps the row in a small table, and HTML View, Show report, opens it in an ordinary window with a Done button that calls watchflows.dismiss().
When I press Control-Option-E, show a form with a required amount, a category dropdown (Groceries, Eating out, Transport, Household, Work, Other), an optional note and an optional receipt file, and save it to an expenses table in a new Expenses database.
What bites
Leave a few seconds between entries. A second Control-Option-E within about five seconds of pressing Save does not open a new form: the Form node takes the identical keypress for an echo of the last one and saves the previous expense again. Give it a breath between entries until that is fixed.
created_at is UTC. An expense logged at 8pm on the 31st in California is already the 1st in UTC. Every date in the statement goes through 'localtime' so the months are yours.
A sleeping Mac. If the Mac is asleep at 9:00 on the 1st, the Schedule fires once when it wakes, with scheduledFor set to the occurrence it missed. Wake it any time on the 1st and the report still comes. Wake it on the 2nd after 9:00 and the missed occurrence is the 2nd's, so that month's report is skipped.
Arming asks every time. Run SQL is always marked destructive, because the statement could be a DELETE. Arming this flow shows a confirmation naming Last month by category, even though this statement only reads. See Destructive Actions.
An open report holds the flow. While a page is open the flow cannot run again, so the hotkey does nothing until you press Done. Give up after is 240 minutes: an unattended report closes after four hours of silence and that run is marked failed.
Don't test with the canvas Run button. It fires both triggers: the form appears, and Run SQL refuses because a hand-started run carries no scheduledFor. Open the SQL field in the Workbench instead, put a scheduledFor on the 1st in its Test Values, and press ▶ to run that one node against the real table.
After importing
- Select Save expense, open Database, choose New Database… and create Expenses. Point Last month by category at the same database.
- Grant Watchflows Accessibility permission if the hotkey is new to it.
- Arm the flow and confirm the prompt.
Variations
- Turn off Create the table if it doesn't exist once the first row is in, so a typo in Table cannot start a second table.
- Swap the categories for clients and the amount for hours, and the same flow bills time.
- Keep every report: add a Write File after Report page with File Path
~/Documents/Expenses/{{records.0.month}}.html, Content{{html}}and Write Mode Append, which creates the file without marking the node destructive. - Set the form's Remember Values to For This Session if you log several receipts in a row from the same category.