Checkout.com to Xero month-end reconciliation workbench

Build me an internal app that our finance person opens at month end to reconcile what Checkout.com actually processed against what Xero says landed in the bank. It is a working surface rather than a report: the whole point is to get quickly to the handful of days that disagree, explain them, and sign them off.

Settings the app holds and remembers between sessions: the date range being reconciled, the tolerance that decides whether two sides agree (an absolute amount, for example 1.00), which Xero bank account the Checkout.com payouts land in, which Xero account fees and adjustments should be posted to, and the Google Sheet id and tab name used for the sign off log. Populate the two account pickers from the Xero List Accounts operation so the user chooses from their real chart of accounts rather than typing a code.

The main view is a calendar style list of days across the chosen range. Each row is one day for one currency and shows: the date, the currency, the Checkout.com gross captured amount, the refunds issued, the resulting net, the matching Xero bank transaction total, and the variance between net and Xero. Show a green tick when net and the Xero total agree within the tolerance, and a red flag when they do not. Days already signed off should be visibly marked as signed. Rows with a red flag are clickable and open the day detail.

Build the Checkout.com side from Search Payments over the date range, following its cursor pagination to the end, then Get Payment Actions on each payment to read the capture, refund and void actions. Important feasibility constraint: do not try to pull a settlement report or an account balance, because listing reports and fetching balances are not available to this app. The processed figures must come from payments plus their actions. Key the reconciliation off the action timestamp rather than the payment creation date, since captures and refunds are asynchronous and often land on a different day from when the payment was created. Gross captured for a day is the sum of capture actions dated that day, refunds is the sum of refund actions dated that day, and net is gross minus refunds.

Amounts from Checkout.com are integers in the currency's minor unit, so 1000 means 10.00 GBP. Convert with a currency aware rule: most currencies are two decimal places, zero decimal currencies such as JPY and KRW use the major unit directly, and three decimal currencies such as BHD, KWD and OMR are the value divided by 1000. Group and total days by currency as well as by date, and never add different currencies together. Xero money fields are already decimals rather than minor units, so compare like for like after conversion.

Build the Xero side from List Bank Transactions, filtered to the selected bank account and the chosen date range, following its pagination. Total the receive money transactions per day to get the figure shown in the Xero column.

Clicking a day that disagrees opens a split view. On the left, every Checkout.com payment contributing to that day, each with its amount, status, reference and any refund actions, with a drill in that calls Get Payment Details for the full record. On the right, every Xero bank transaction for that day on the selected account. The app should pre match the obvious pairs automatically by comparing amount and date, allowing a match when the amounts agree within tolerance and the dates are the same day or one day apart, so that settled next day activity still pairs up. Show matched pairs collapsed and quiet, and surface the unmatched items on both sides as the exceptions that actually need a person. The user can also match or unmatch a pair by hand when the automatic guess is wrong.

For each exception the finance user picks a reason tag: processor fee, timing difference where the payment settled the next day, chargeback, or other with a free text note. Timing differences and fees carry most of the weight in practice, because payouts usually arrive net of fees and batch several days of activity together, so make those two the fastest tags to apply. Persist the tags and the match decisions per day so the work survives closing and reopening the app.

From an exception the user can write the correction into Xero in one of two ways: post a balanced journal using Create Manual Journals for a fee or an accounting adjustment, or record a receive money entry using Create Bank Transactions for money that arrived but was never recorded. Both must go through a confirmation preview first, showing the exact date, accounts, description or narration, and the debit and credit lines with their amounts, and both need an explicit confirm before anything is sent. Never post automatically as a side effect of matching or tagging. After a successful write, store the returned Xero id against that exception, show it in the row, and stop the same exception being posted twice.

Signing off a day appends a row to the configured Google Sheet using Append Values, containing the date, the currency, the gross captured, the refunds, the net, the Xero total, the variance, the reason tags applied that day, the name of the person who signed it, and the time they signed. On load, read the sheet with Get Values so days that have already been signed show as signed in the main list and are not appended twice; if a day is re signed after a correction, use Update Values to amend the existing row rather than adding a duplicate. The sheet is the audit trail the accountant reads without ever opening the app, so keep the columns stable and readable.

Attribute the sign off to the person using the app, and make the main list the landing view so the finance user lands straight on the days that need attention.