Integrations

Automated weekly reporting from Xero

Xero already holds every number a weekly management report needs; the work is reading the right endpoints and combining them the same way each Monday. This page maps the Accounting API calls behind a report on cash, sales, receivables and payables, with the joins, formulas and a worked example.

By Simon DuttonLast reviewed

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:

  1. Cash. What did we start the week with, what came in, what went out, and where did we finish?
  2. Sales. How much did we invoice last week, after credit notes?
  3. Receivables. How much are customers holding, and how much of it is late?
  4. 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.

IVP.ai / Weekly report

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

Opening balance, cash received, cash spent and closing balance for each bank account this week, from the Bank Summary report
AccountOpeningReceivedSpentClosing
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

Weekly net sales invoiced and cash received from customersEight weeks to 8 November 2026. This week net sales £27,090 and cash received £35,946; the week before, £26,230 and £33,710.20/0927/0904/1011/1018/1025/1001/1108/11

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
Customers with overdue invoices, largest overdue balance first
Overdue customerInvoicesOldestOverdue
Cresswold Bakery356d£16,236
Ashgill Dairy247d£10,644
Norbeck Pet Supplies271d£7,644
Whinfell Brewing166d£5,748
Fallowmere Foods122d£4,404

Which Xero endpoints feed this report

5 calls · limit 60/min, 5,000/day per organisation

Accounting API endpoints read by the weekly run, the parameters used, what each feeds and calls made
EndpointFilterFeedsCalls
GET /Reports/BankSummaryfromDate=2026-11-02&toDate=2026-11-08Cash: opening, received, spent, closing per bank account1
GET /InvoicesStatuses=AUTHORISED,PAID · If-Modified-SinceSales raised, open receivables and payables1
GET /CreditNotesIf-Modified-SinceCredits netted off sales1
GET /PaymentsIf-Modified-SinceCustomer receipts and supplier payments1
GET /BankTransactionsIf-Modified-SinceSpend and receipts with no invoice (payroll, rent, refunds)1

Monday run · log

Xero Accounting API · read only

  1. 07:00Refresh Xero connection, one tenantxero-tenant-id setSynced
  2. 07:00Read bank summary for the week2 accountsSynced
  3. 07:01Read invoices, bills, credit notes changed since last run9 new sales · 1 credit noteSynced
  4. 07:01Read payments and bank transactions15 payments · 5 otherSynced
  5. 07:02Check bank summary received = payments + other receipts£36,299Done
  6. 07:02Age receivables by due date£47,346 overdueDone
  7. 07:03Flag customers over 60 days overdue2 invoicesFor review
  8. 07:03Send weekly report to MD and FD5 API calls usedDelivered
  9. 07:03Send chasers or pay billsOff in this set-upSkipped

Sales ex VAT from invoice SubTotal · receivables, payables and cash inc VAT

Illustrative data

Illustrative, fictional data. Company, customers, suppliers, invoices and balances are invented; not customer results or a live product screenshot.

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.

Questions

Frequently asked questions

Can Xero send a weekly report automatically?

Not as one combined pack out of the box in the way most finance directors want it. A weekly report covering cash, sales, receivables and payables together usually means running several Xero reports and joining them by hand. Automating it means reading those figures through the Accounting API on a schedule, applying the same formulas every week and sending the result to the people who need it.

Which Xero API endpoints do I need for a weekly management report?

Five cover most of it: the Bank Summary report for opening, received, spent and closing cash per account; Invoices for sales and bills; Credit Notes to net credits off sales; Payments for receipts and supplier payments; and Bank Transactions for spending with no invoice, such as payroll and rent. The Profit and Loss report adds monthly context if you want it.

Will a weekly report hit Xero's API rate limits?

Unlikely. Xero allows 60 calls a minute and 5,000 a day per connected organisation. Reading only records changed since the last run, with up to 100 records per page, a weekly report needs a handful of calls. Limits matter for the first historical load, or if you call the aged receivables report once per customer, which needs a contact ID each time.

Why don't sales and cash received match in my weekly Xero figures?

Usually because they are measured on different bases. Sales taken from invoice SubTotal exclude VAT, while payments, amounts due and bank balances include it. Timing also differs: cash received this week mostly pays invoices raised weeks ago. Label each figure ex or inc VAT and compare like with like before reading anything into the gap.

Related

Keep reading

About the author

Simon Dutton

Simon has more than 10 years of experience as a data scientist, working with start-ups, SMEs, Google and the CMA.

Last reviewed 13 November 2026.

Your first report or agent in 48 hours.

Tell us which systems feed it. We connect them, clean the data and set the report running automatically. If it isn’t useful, walk away.