Metadata-Version: 2.4
Name: rcvatkit
Version: 0.1.0
Summary: 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.
Author: Duckboard
License: MIT
Project-URL: Homepage, https://github.com/Duckboard/rcvatkit
Project-URL: Buy me a coffee, https://ko-fi.com/duckboard
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: Microsoft :: Windows
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Office/Business :: Financial :: Accounting
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sagekit>=0.1.0
Dynamic: license-file

# 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](https://ko-fi.com/duckboard).

## 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](https://github.com/Duckboard/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

```bash
pip install -r requirements.txt
```

or, to install as an editable package:

```bash
pip install -e .
```

This installs [sagekit](https://github.com/Duckboard/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:

```bash
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

```python
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

```bash
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

```bash
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](LICENSE). Do whatever you like with this; a
credit or a [Ko-fi tip](https://ko-fi.com/duckboard) is appreciated
but never required.
