stmtkit

Generate branded PDF customer statements straight from Sage 50 (UK/Ireland) and email them out via Microsoft Graph -- no Outlook desktop client, no manual PDF exports, no copy-pasting into an email client. Built on top of two other Duckboard toolkits: sagekit for the Sage connection and graphkit for sending mail.

If this saves you time, consider buying me a coffee.

What it does

1. Reads a CSV of customers -- which layout they get, how they pay, where to email them.
2. Pulls live data out of Sage 50 via ODBC: invoices, payments, company details, bank details.
3. Builds a branded PDF for each customer.
4. Emails the PDF as an attachment via Microsoft Graph.

Everything is driven by the CSV. Add, remove, or change a customer's preferences by editing that file -- the change takes effect on the next run.

Features

• Two statement layouts: outstanding (unpaid invoices + aged analysis) and all_activity (full transaction history from a brought-forward balance)
• Payment message (direct debit vs BACS bank details) is set independently of layout, per customer
• A built-in grace period for the statement date, so a scheduled run on the 1st still works correctly even if it's missed and run a few days late (see "Statement date logic" below)
• Configurable branding (accent colour, logo) -- no fixed look
• No Outlook dependency -- sends via the Graph API directly, so it works unattended/headless (e.g. on Windows Task Scheduler)
• Column-name auto-detection for the tricky bits (Sage's DELETED_FLAG varies between numeric and string across installs) via sagekit

Installation

pip install -r requirements.txt

or, as an editable package:

pip install -e .

You'll also need:

Requirement	Notes
Windows	sagekit's ODBC driver is Windows-only
Sage 50 (UK/Ireland)	Running on this PC or reachable on the network, with its ODBC driver installed (comes with Sage)
An Azure app registration	With the Mail.Send Application permission (admin consent required) -- see graphkit's README for the registration walkthrough

Setup

1. Sage ODBC connection

stmtkit uses sagekit for the Sage connection, so set the same three environment variables sagekit reads (see its README for how to find your DSN):

SAGE_DSN=SageLine50v33
SAGE_UID=MANAGER
SAGE_PWD=

Leaving SAGE_PWD blank is fine for anything you run by hand -- you'll be prompted with hidden input instead. Only set it for unattended/scheduled runs.

2. Graph API app registration

stmtkit uses graphkit to send mail, so set the same three environment variables graphkit reads:

GRAPH_TENANT_ID=...
GRAPH_CLIENT_ID=...
GRAPH_CLIENT_SECRET=...

The app registration needs the Mail.Send Application permission (not Mail.ReadWrite) with admin consent granted. See graphkit's README for the full Azure walkthrough.

Copy .env.example to .env and fill in your values if you want to pre-fill these (never commit the real .env -- it's already gitignored).

3. recipients.csv

Copy examples/recipients.csv and edit it. Five columns:

Column	What to enter	Example
CustomerCode	The account reference from Sage -- must match exactly (case-sensitive)	SAMP001
CustomerName	For your reference only, not used in queries	C & S Traders Ltd
Email	Address(es) to send to, comma-separated for more than one	accounts@example.com
LayoutTemplate	outstanding or all_activity	all_activity
PaymentType	DD or BACS -- controls the payment footer	DD
EmailNote	Optional, appended to the email body verbatim	(blank)

PaymentType and LayoutTemplate are independent: a DD customer can use either layout and always gets the DD message; a BACS customer gets your bank details in the footer regardless of layout.

Prefix a CustomerCode with # to disable that row without deleting it.

4. settings.yaml (optional)

Copy examples/settings.yaml if you want to change where files are written, override the "from" email address, or set custom branding. Every key has a default -- the file itself is optional.

Usage

python -m stmtkit --test              # dry run: PDFs only, no emails
python -m stmtkit                     # generate PDFs and send emails
python -m stmtkit --date 2026-04-30   # override the statement date
python -m stmtkit --customer SAMP001  # process one customer only
python -m stmtkit --config examples/settings.yaml

Run --test first, check the PDFs in the output folder and the log file in the logs folder, then run for real.

As a library, everything is importable directly -- see examples/build_single_pdf.py for building a PDF from data you already have (useful if your ledger isn't Sage), and examples/run_statements.py for calling the CLI entry point from your own script.

from stmtkit import statement_date, load_recipients, Branding, build_pdf, send_statement_email

Statement date logic

Designed for a job scheduled on the 1st of the month, with a seven-day grace period: if the 1st falls on a weekend, bank holiday, or the PC was off, running any time up to the 7th still produces a statement dated for the last day of the previous month.

Run date	Statement date
1st (scheduled)	Last day of previous month
3rd (weekend grace)	Last day of previous month
7th (last grace day)	Last day of previous month
8th or later	Last day of current month (treated as a manual run)

Override with --date YYYY-MM-DD, or change the grace window by calling stmtkit.statement_date(run_date, grace_days=N) directly.

Scheduling

Windows Task Scheduler:

1. Win + R -> taskschd.msc
2. Create Basic Task -> Trigger: Monthly, all months, day 1
3. Action: Start a program -> python with arguments -m stmtkit, "Start in" set to the folder containing your recipients.csv / settings.yaml
4. Tick "Run with highest privileges" and "Run task as soon as possible after a scheduled start is missed" (Settings tab) -- the grace-period logic above covers the rest.

Log files

Every run creates a dated log file in the logs folder, recording the run date, statement date, each customer processed (sent / skipped / failed), the PDF file name, and any errors.

Troubleshooting

Symptom	Likely cause	Fix
Cannot connect to Sage via DSN ...	DSN wrong or Sage not running	See sagekit's README -- check "ODBC Data Sources (64-bit)" and that Sage is open
Customer 'X' not found in Sage	CustomerCode doesn't match the Account Reference	Copy the Account Reference exactly from Sage -- it's case-sensitive
No outstanding invoices for X -- skipping	Normal -- the customer has no unpaid balance	Nothing to fix
PDFs created but no emails arrive	Graph app registration missing Mail.Send, or wrong from_address	Check the app registration's API permissions and admin consent; confirm from_address is a real mailbox
Missing required environment variable: GRAPH_...	.env not loaded or variables not set	Set the three GRAPH_* variables (see Setup step 2)
Missing required environment variable: SAGE_... / prompted every run	SAGE_DSN not set	Set SAGE_DSN, or just answer the prompt each time

Testing

pip install pytest
pytest

Tests mock the Sage cursor and the Graph client, so they run offline with zero real credentials or a real Sage install.

Notes

• Amounts assume GBP and use a £ prefix -- if you need another currency symbol, it's one line to change in stmtkit/pdf.py (_money()).
• The two statement layouts and the SQL in stmtkit/sage_data.py assume Sage 50's standard AUDIT_HEADER / SALES_LEDGER schema (UK/Ireland). If your install uses different column names, use sagekit's get_columns() / find_column() to check what's actually there.

License

MIT -- see LICENSE. Do whatever you like with this; a credit or a Ko-fi tip is appreciated but never required.