Why the Monday report is still built by hand
Most owner-managed businesses on Xero have a Monday ritual. Someone opens the bank summary, exports aged receivables and aged payables, totals last week's sales invoices, pastes it all into a workbook and writes three lines for the managing director. It takes an hour or two when nothing is odd, and longer when the numbers do not agree with last week's version.
None of that work needs judgement until the very end. The figures already sit in Xero. The awkward part is that they sit in different screens, measured on different bases (some with VAT, some without), and the report has to stitch them together the same way every week. That is exactly the kind of job worth automating, provided you know which parts of Xero to read and how to combine them.
This page sets out a general weekly management report for one Xero organisation: cash, sales, money owed to you and money you owe. It is deliberately plain. Site-level debt chasing and multi-company consolidation are different problems with their own pages.
Four questions the report answers
A useful weekly report from Xero answers four things, in this order:
- Cash. What did we start the week with, what came in, what went out, and where did we finish?
- Sales. How much did we invoice last week, after credit notes?
- Receivables. How much are customers holding, and how much of it is late?
- Payables and the next fortnight. What bills fall due soon, and does the cash cover them?
Each question maps onto specific parts of the Xero Accounting API. Getting that mapping right is most of the work.
Which Xero endpoints feed it
Every object, field and parameter named here appears in Xero's published Accounting API OpenAPI specification.
| Endpoint | What it gives the report | Useful parameters |
|---|---|---|
GET /Reports/BankSummary |
Per bank account: opening balance, cash received, cash spent, closing balance | fromDate, toDate |
GET /Invoices |
Sales invoices (Type ACCREC) and bills (ACCPAY) with Date, DueDate, SubTotal, TotalTax, Total, AmountDue, AmountPaid, AmountCredited, Status |
Statuses, where, page, If-Modified-Since header |
GET /CreditNotes |
Credit notes (ACCRECCREDIT, ACCPAYCREDIT) with SubTotal, RemainingCredit and Allocations to invoices |
If-Modified-Since header |
GET /Payments |
Each payment with PaymentType (ACCRECPAYMENT for customer receipts, ACCPAYPAYMENT for supplier payments), Date, Amount, the linked Invoice and the bank Account |
where, page, If-Modified-Since header |
GET /BankTransactions |
Money in or out that is not an invoice payment: Type RECEIVE or SPEND, Total, BankAccount, IsReconciled |
where, page, If-Modified-Since header |
GET /Reports/ProfitAndLoss |
Optional monthly context: income and expense by account | fromDate, toDate, periods, timeframe |
Two details from the specification matter for a weekly job. If-Modified-Since returns only records created or changed since a timestamp, so after the first full load each Monday run reads a small delta. And the invoices, payments and bank transactions endpoints page at up to 100 records per call.
Why not just call the aged debt report?
The specification does include GET /Reports/AgedReceivablesByContact and GET /Reports/AgedPayablesByContact, but both require a contactId. That means one call per customer or supplier. A business with 400 active customers would need 400 calls for the receivables view alone, against a per-organisation limit of 60 calls a minute. Building the ageing yourself from open invoices takes a handful of calls and gives you the same answer, plus the freedom to age by due date, by invoice date, or both.
Rate limits and connections
Xero's API limits page sets, per connected organisation, 5 calls in progress at once, 60 calls a minute and 5,000 calls a day, with an app-wide ceiling of 10,000 calls a minute across all organisations. Going over returns HTTP 429 with a Retry-After header, and successful responses carry X-DayLimit-Remaining and X-MinLimit-Remaining so a job can slow itself down before it hits the wall. A weekly report built as above uses a few calls, so limits only bite on the first historical load or if you fall back to per-contact reports.
Each Xero organisation is a separate tenant. After a user authorises the connection, the identity API's GET /connections returns the tenants the app can see, each with a tenantId (identity API specification), and every accounting request must send that ID in the xero-tenant-id header. Ask for read-only scopes. Xero is moving apps to narrower "granular" scopes: according to its granular scopes FAQ, apps created on or after 2 March 2026 get them by default and older apps have until September 2027 to move.
The data model
Fields used
| Xero object | Fields | Used for |
|---|---|---|
| Bank Summary report row | account (with accountID attribute), Opening Balance, Cash Received, Cash Spent, Closing Balance |
The cash section |
| Invoice | InvoiceID, InvoiceNumber, Type, Status, Contact, Date, DueDate |
Which invoices count, and when they age from |
| Invoice | SubTotal, TotalTax, Total, AmountDue |
Sales ex VAT; balances inc VAT |
| Credit note | Type, Date, SubTotal, Allocations |
Netting credits off the week's sales |
| Payment | PaymentType, Status, Date, Amount, Invoice, Account |
Receipts and supplier payments by bank account |
| Bank transaction | Type, Date, Total, BankAccount, Status |
Payroll, rent, refunds and anything else with no invoice |
Joins
| From | To | Key | Watch for |
|---|---|---|---|
| Payment | Invoice | Invoice.InvoiceID |
A part payment leaves the invoice AUTHORISED with a smaller AmountDue |
| Payment, bank transaction | Bank Summary row | bank AccountID |
Transfers between your own accounts appear on both sides |
| Credit note | Invoice | Allocations[].Invoice |
Unallocated credit sits in RemainingCredit, not against any invoice |
| Invoice | Contact | Contact.ContactID |
Duplicate contacts split one customer's overdue balance in two |
Join on Xero's IDs, not on invoice numbers or names. InvoiceNumber is editable text and contact names change.
Formulas
| Measure | Formula |
|---|---|
| Net sales for the week | Σ SubTotal of ACCREC invoices dated in the week with Status AUTHORISED or PAID − Σ SubTotal of ACCRECCREDIT credit notes dated in the week |
| Cash from customers | Σ Amount of ACCRECPAYMENT payments dated in the week |
| Closing cash | Opening balance + cash received − cash spent, per account, from Bank Summary |
| Receivables owed | Σ AmountDue of open ACCREC invoices |
| Days overdue | Report date − DueDate, where positive |
| Overdue share | Σ AmountDue where days overdue > 0 ÷ receivables owed |
| Payables due soon | Σ AmountDue of open ACCPAY bills with DueDate in the next 14 days |
| Cash in 14 days | Closing cash + receivables due in 14 days − payables due in 14 days − payables already overdue |
One trap catches most hand-built versions. Sales measured from SubTotal exclude VAT. Payments, AmountDue and bank balances include it. If the report shows "sales £27,090, cash in £35,946" side by side, it looks as though customers paid far more than was sold, when much of the gap is VAT that will be paid over to HMRC. Label each figure with its basis, or show both.
The 14-day line is a planning figure, not a forecast. It assumes customers pay on their due date, which the overdue figure above it usually contradicts, and it ignores payroll, VAT and anything not yet entered as a bill.
Monday 9 November 2026 · 07:03
Xero weekly management report · Kelsdon Packaging Ltd (fictional)
Cash down £19,153 to £125,477.
£47,346 of receivables overdue.
Week 2 Nov to 8 Nov. Built from one Xero organisation using 5 read-only Accounting API calls.
- Net sales invoiced
- £27,090
- 9 invoices, 1 credit note, ex VAT
- Cash from customers
- £35,946
- 11 payments, inc VAT
- Closing cash
- £125,477
- −£19,153 on the week
- Receivables overdue
- £47,346
- 43.6% of £108,498 owed
Cash by bank account
| Account | Opening | Received | Spent | Closing |
|---|---|---|---|---|
| Business current account | £84,630 | £36,258 | £55,452 | £65,436 |
| Deposit account | £60,000 | £41 | £0 | £60,041 |
| Total | £144,630 | £36,299 | £55,452 | £125,477 |
Next 14 days, if customers pay on time
- Closing cash, all accounts£125,477
- Customer invoices falling due+ £15,528
- Bills falling due− £17,172
- Bills already overdue− £1,716
- Cash in 14 days£122,117
Net sales and customer receipts, eight weeks
Net sales (invoices less credit notes, ex VAT) Cash from customers (inc VAT) · week ending
Receivables by days past due date
- Not yet due£61,152 · 16
- 1–30 days overdue£28,830 · 6
- 31–60 days overdue£11,868 · 2
- 61–90 days overdue£6,648 · 2
- Over 90 days overdue£0 · 0
| Overdue customer | Invoices | Oldest | Overdue |
|---|---|---|---|
| Cresswold Bakery | 3 | 56d | £16,236 |
| Ashgill Dairy | 2 | 47d | £10,644 |
| Norbeck Pet Supplies | 2 | 71d | £7,644 |
| Whinfell Brewing | 1 | 66d | £5,748 |
| Fallowmere Foods | 1 | 22d | £4,404 |
Which Xero endpoints feed this report
5 calls · limit 60/min, 5,000/day per organisation
| Endpoint | Filter | Feeds | Calls |
|---|---|---|---|
| GET /Reports/BankSummary | fromDate=2026-11-02&toDate=2026-11-08 | Cash: opening, received, spent, closing per bank account | 1 |
| GET /Invoices | Statuses=AUTHORISED,PAID · If-Modified-Since | Sales raised, open receivables and payables | 1 |
| GET /CreditNotes | If-Modified-Since | Credits netted off sales | 1 |
| GET /Payments | If-Modified-Since | Customer receipts and supplier payments | 1 |
| GET /BankTransactions | If-Modified-Since | Spend and receipts with no invoice (payroll, rent, refunds) | 1 |
Monday run · log
Xero Accounting API · read only
- 07:00Refresh Xero connection, one tenantxero-tenant-id setSynced
- 07:00Read bank summary for the week2 accountsSynced
- 07:01Read invoices, bills, credit notes changed since last run9 new sales · 1 credit noteSynced
- 07:01Read payments and bank transactions15 payments · 5 otherSynced
- 07:02Check bank summary received = payments + other receipts£36,299Done
- 07:02Age receivables by due date£47,346 overdueDone
- 07:03Flag customers over 60 days overdue2 invoicesFor review
- 07:03Send weekly report to MD and FD5 API calls usedDelivered
- 07:03Send chasers or pay billsOff in this set-upSkipped
Worked example (illustrative, fictional)
Kelsdon Packaging Ltd, a fictional wholesaler, runs its report at 07:00 on Monday 9 November 2026 for the week Monday 2 to Sunday 8 November. All figures are invented and match the report above.
Sales. Nine sales invoices were dated in the week, with SubTotal adding to £27,730. One credit note, CN-0418 for £640 ex VAT, was raised to Whinfell Brewing and allocated against an older invoice. Net sales: £27,730 − £640 = £27,090. The same nine invoices carry £5,546 of VAT, so they total £33,276 including VAT.
Cash. The Bank Summary gives two accounts:
| Account | Opening | Received | Spent | Closing |
|---|---|---|---|---|
| Business current account | £84,630 | £36,258 | £55,452 | £65,436 |
| Deposit account | £60,000 | £41 | £0 | £60,041 |
| Total | £144,630 | £36,299 | £55,452 | £125,477 |
Cash fell by £144,630 − £125,477 = £19,153. The report checks the Bank Summary against the transactions it read. Received: eleven customer payments of £35,946, a £312 supplier refund and £41 of interest, which add to £36,299. Spent: four supplier payments of £16,536, net pay of £31,480, rent of £6,250 and £1,186 of card spend, which add to £55,452. Both agree, so the cash section goes out without a query.
Receivables. Twenty-six sales invoices are still open, with £108,498 owed:
| Days past due | Invoices | Owed |
|---|---|---|
| Not yet due | 16 | £61,152 |
| 1–30 | 6 | £28,830 |
| 31–60 | 2 | £11,868 |
| 61–90 | 2 | £6,648 |
| Over 90 | 0 | £0 |
Overdue: £28,830 + £11,868 + £6,648 = £47,346, which is £47,346 ÷ £108,498 = 43.6% of the ledger. Cresswold Bakery holds the most overdue (£16,236 across three invoices); Norbeck Pet Supplies has the oldest, at 71 days past due.
Payables and the next 14 days. Ten bills are open, totalling £29,280. One, £1,716 to Selbury Freight, was due on 2 October and is still unpaid. Four more, worth £17,172, fall due between 9 and 22 November. Customer invoices worth £15,528 fall due in the same window. Cash in 14 days: £125,477 + £15,528 − £17,172 − £1,716 = £122,117, before payroll and anything not yet billed.
Calls used. One call each to Bank Summary, invoices, credit notes, payments and bank transactions: five calls, against 60 a minute and 5,000 a day for the organisation.
What the report leaves to people
The report states facts and flags exceptions. It does not decide what to do about them. Whether to put Norbeck on stop, whether to pay Selbury now or query the bill, whether the cash fall is seasonal or a warning: those are calls for the finance director and the managing director, and the report should make them quicker, not make them.
It is also worth agreeing what "the week" means before automating it. Xero dates invoices by their Date, not by when someone keyed them, so an invoice back-dated on Tuesday into the previous week changes last week's sales after the report has gone. Using If-Modified-Since catches those edits; decide whether to restate the previous week or roll the change into the current one, and say which in the footer.
How to automate it with IVP.ai
The IVP.ai Data Layer connects to Xero with read-only access, loads the history once, then reads changes each night using the endpoints above. It keeps invoices, credit notes, payments and bank transactions as tables joined on Xero's own IDs, applies the formulas in this page, and checks the Bank Summary against the transactions before anything is sent.
A Deskless by IVP.ai agent then delivers the Monday report to the people who should see it and flags the exceptions: customers past 60 days, overdue bills, a cash summary that does not reconcile. In this set-up it does not chase customers or pay suppliers; it flags and waits for a person. Deskless can be configured to draft chasers for approval if you want that later.
If you also keep budgets in spreadsheets, the Excel integration covers bringing those alongside the Xero actuals. If your operational data lives in a database, see reporting from a SQL database. Businesses on QuickBooks rather than Xero can read the QuickBooks page. Other connections are listed on the integrations hub.
To see this report built on your own Xero data, start with the free 48-hour build. We connect up to three sources, for example Xero plus a CRM and a budget spreadsheet, clean the data and set one report or agent running on real data within 48 hours of read-only access. You then have 14 days of access to choose a plan or walk away.