rcvatkit 0.1.0

Find UK VAT reverse-charge purchase invoices sitting in Sage 50 at the wrong tax code, and calculate the Box 1 / Box 4 VAT return adjustment to correct it.

rcvatkit

Finds UK VAT reverse-charge purchase invoices sitting in Sage 50 at the wrong tax code, and calculates the VAT return manual adjustment needed to correct it.

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

What problem this solves

If you receive foreign-currency (non-GBP) supplier invoices and use an OCR/import tool such as AutoEntry, Dext, or Hubdoc, there's a good chance those invoices are landing in Sage at the default "no VAT" tax code (often T0) instead of the reverse-charge code Sage expects (often T18). HMRC needs Boxes 1 and 4 of your VAT return to reflect these transactions correctly.

Sage 50 won't let you post the reverse-charge code on a manual journal ("This tax code cannot be used for Journal transactions"), so the fix isn't a journal at all -- it's a manual adjustment on the VAT return itself: add the same amount to Box 1 (output VAT) and Box 4 (input VAT). The net effect on your VAT bill is nil, but the boxes are populated the way HMRC expects.

This toolkit finds the transactions and does the arithmetic. It does not touch Sage or submit anything -- applying the adjustment is a manual step in Sage's VAT Return "Adjustments" screen.

Features

• Finds non-GBP purchase invoices/credits not yet coded with your reverse-charge tax code, using sagekit for the Sage 50 ODBC connection and its column auto-detection
• Calculates the Box 1 / Box 4 adjustment amount
• Built-in helper for UK VAT "stagger groups" -- works out which VAT quarter just finished without you having to calculate the dates by hand
• One worked example, ready to adapt: a CLI script with date prompts, a results table, and a saved log file

Installation

pip install -r requirements.txt

or, to install as an editable package:

pip install -e .

This installs sagekit automatically -- rcvatkit builds on it rather than reimplementing the Sage connection. You'll also need the Sage Line 50 ODBC driver (installed alongside Sage 50 itself) and Sage 50 running while you query it -- see sagekit's README for how to find your DSN.

Configuration

Copy .env.example to .env if you want to pre-fill values:

cp .env.example .env

SAGE_DSN=SageLine50v33
SAGE_UID=MANAGER
SAGE_PWD=

RCVAT_STAGGER=1
RCVAT_TARGET_CODE=T18
RCVAT_VAT_RATE=0.20

.env is already in .gitignore -- never commit real credentials.

Which VAT stagger group are you in?

HMRC assigns every VAT-registered business to one of three stagger groups, which determine which months your VAT quarters end in. Check your VAT registration certificate or HMRC online account if you're not sure.

Stagger	Quarters end
1	Mar / Jun / Sep / Dec
2	Jan / Apr / Jul / Oct
3	Feb / May / Aug / Nov

Usage

from datetime import date
from sagekit import connect_or_exit
from rcvatkit import find_reverse_charge_candidates, compute_adjustment, last_completed_quarter

conn = connect_or_exit()
cursor = conn.cursor()

# Work out the last completed quarter automatically...
date_from, date_to = last_completed_quarter(stagger=1)

# ...or just pass your own dates:
# date_from, date_to = date(2026, 1, 1), date(2026, 3, 31)

lines = find_reverse_charge_candidates(cursor, date_from, date_to)
adjustment = compute_adjustment(lines)

print(f"{len(lines)} invoice line(s) found, net GBP {adjustment['net_gbp']:.2f}")
print(f"Add £{adjustment['box_1']:.2f} to Box 1 AND Box 4")

conn.close()

Example script

python examples/check_reverse_charge.py

Prompts for a date range (defaulting to the last completed quarter for your stagger group), connects to Sage, prints a table of the affected invoices grouped by supplier plus the Box 1/Box 4 adjustment, and saves everything to a timestamped log file alongside the script. Copy and adapt it -- it's a starting point, not a fixed report.

Concept reference

Concept	What it means
Reverse charge	The VAT treatment HMRC requires for certain foreign-currency purchases -- both output and input VAT are recorded, netting to nil but keeping Boxes 1 and 4 accurate
T18	Sage's common default reverse-charge tax code (yours may differ -- check your Sage tax code list)
Stagger group	Which three months your VAT quarters end in -- see table above
AUDIT_HEADER / AUDIT_SPLIT / PURCHASE_LEDGER	The three Sage ODBC tables this toolkit joins to find purchase invoices, their tax code, and the supplier's currency

A Sage ODBC quirk worth knowing

Sage's ODBC driver rejects INNER JOIN across three or more tables ("Invalid outer join specification"). find_reverse_charge_candidates uses the older implicit (comma) join style for exactly this reason -- if you're adapting the SQL yourself and add a fourth table, keep using commas rather than INNER JOIN.

Also, Sage's ODBC currency IDs are offset by one from what the Sage 50 UI dropdown shows (UI "0 -- Pound Sterling" is ODBC value 1, UI "1 -- US Dollar" is ODBC value 2, and so on). base_currency_id defaults to 1 (the near-universal UK base currency ID), but if your own install differs, pass your own value -- run SELECT DISTINCT CURRENCY FROM PURCHASE_LEDGER via sagekit to check.

Testing

pip install pytest
pytest

Tests use a fake cursor object, so they run offline without a real Sage install, ODBC driver, or sagekit dependency needing to connect anywhere.

Troubleshooting

Symptom	Likely cause	Fix
Invalid outer join specification	SQL changed to use INNER JOIN across 3+ tables	Use the comma-join style -- see above
Column not found / COLUMN DETECTION ERROR	Column name varies by Sage version	Check the error message's "Available" list against your install
No rows returned	Wrong date range, or no non-GBP suppliers in that period	Confirm Sage is open, widen the date range, check RCVAT_TARGET_CODE matches what your install actually uses
Adjustment looks too big/small	Wrong RCVAT_VAT_RATE or wrong base_currency_id	Confirm your VAT rate and check PURCHASE_LEDGER.CURRENCY values for your GBP accounts
This tax code cannot be used for Journal transactions	Expected -- you tried to journal the reverse charge instead of using a manual VAT return adjustment	Use the Adjustments screen on the VAT return, not a journal

Notes

• This calculates the adjustment; it does not post anything to Sage or submit your VAT return. Always sanity-check the figures before applying them.
• Not tax advice -- if you're unsure whether reverse charge applies to your purchases, check with HMRC guidance or your accountant.

License

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