Xero reconciliation tools

Xero auto-code

Xero auto-confirm

Auto-code script Copy

/**
 * Xero Bank Rec — Auto Code Statement Lines
 *
 * WHAT: Finds statement lines on the Reconcile tab that don't have a green
 * suggested match, sends them to the ONEST bookkeeper for rule/LLM coding,
 * and creates coded + reconciled BankTransactions in Xero via API.
 *
 * HOW TO USE:
 *   1. Install the bookmarklet from bookkeeper.onestos.org/xero-rec
 *      (drag the green button to your bookmarks bar)
 *   2. Open Xero → Accounting → Bank Accounts → [account] → Reconcile
 *   3. Click the "Xero auto-code" bookmark
 *   4. Enter your bookkeeper password when prompted (stored in localStorage)
 *   5. Review the confirmation dialog and click OK
 *   6. The page reloads — coded lines are gone, skipped ones remain
 *
 * HOW IT WORKS:
 *   - Rows with a green suggestion (existing match) are SKIPPED — the
 *     auto-confirm bookmarklet handles those.
 *   - Rows without a suggestion are "Create" items. This script reads the
 *     date, amount, and description from each and sends them to the bookkeeper.
 *   - The bookkeeper matches against rules + LLM, then creates a coded
 *     BankTransaction in Xero with IsReconciled=true. Xero auto-matches it to
 *     the statement line by amount + date + bank account.
 *   - None confidence items (LLM couldn't determine account) are skipped for manual coding.
 *
 * MAINTENANCE: If Xero redesigns the Reconcile UI, the selectors may need
 * updating. Run xero-rec-diagnostic.js in the console to get current class names.
 */
(() => {
  const BK = 'https://bookkeeper.onestos.org';
  const ENTITY = 'onest-pty';

  // ── Auth ──────────────────────────────────────────────────────────────────
  let pass = localStorage.getItem('bk_password');
  if (!pass) {
    pass = prompt('Bookkeeper password (saved locally for this browser):');
    if (!pass) return;
    localStorage.setItem('bk_password', pass);
  }

  // ── Bank account ID ───────────────────────────────────────────────────────
  // Xero uses two URL formats:
  //   Old: /Bank/Go?accountID=<guid>
  //   New: /Banking/Reconcile/<guid>  or  /accounting/bankaccounts/<guid>/reconcile
  const GUID_RE = /[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}/i;
  const _params = new URLSearchParams(window.location.search);
  const accountID =
    _params.get('accountID') ||
    _params.get('accountId') ||
    (window.location.pathname.match(GUID_RE) || [])[0] ||
    null;
  if (!accountID) {
    alert(
      'Could not find bank account ID in the URL.\n\n' +
      'Current URL: ' + window.location.href + '\n\n' +
      'Make sure you are on the Xero bank reconciliation page:\n' +
      'Accounting → Bank Accounts → [account name] → Reconcile'
    );
    return;
  }

  // ── Find statement lines without a green suggestion ───────────────────────
  const lines = [];
  const diagnostics = [];

  let okBtns = Array.from(document.querySelectorAll('.okayButton'));
  if (okBtns.length === 0) {
    // Fallback: try alternate OK button selectors that Xero has used historically
    okBtns = Array.from(document.querySelectorAll('[class*="okayButton"], [data-testid*="ok"], button[class*="okay"]'));
    if (okBtns.length === 0) {
      alert(
        'No OK buttons found on this page.\n\n' +
        'Are you on the Reconcile tab of a bank account?\n\n' +
        'If yes, run the diagnostic script (xero-rec-diagnostic.js) in the console and share the output.'
      );
      return;
    }
  }

  for (const btn of okBtns) {
    // Find the bounded row container by walking up until we hit an element
    // that contains "Received" or "Spent" — the financial amount text that
    // every bank rec row has. Checking suggestions within this bounded
    // container prevents false-positives from elements elsewhere in the page.
    let rowContainer = null;
    let cur = btn;
    for (let d = 0; d < 15 && cur.parentElement && cur !== document.body; d++) {
      cur = cur.parentElement;
      if (/(Received|Spent)\s+[\d,]+/i.test(cur.innerText || '')) {
        rowContainer = cur;
        break;
      }
    }
    if (!rowContainer) continue; // can't identify the row — skip

    const hasSuggestion = !!rowContainer.querySelector(
      '.suggestion, [class*="suggest"], [class*="match-found"], .possible-match'
    );
    if (hasSuggestion) continue; // auto-confirm bookmarklet handles these

    const row = rowContainer;
    const text = (row.innerText || row.textContent || '').replace(/\s+/g, ' ').trim();

    // ── Extract date ─────────────────────────────────────────────────────────
    const MONTHS = { Jan:1, Feb:2, Mar:3, Apr:4, May:5, Jun:6, Jul:7, Aug:8, Sep:9, Oct:10, Nov:11, Dec:12 };
    const dm = text.match(/(\d{1,2})\s+(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+(\d{4})/);
    if (!dm) {
      diagnostics.push({ text: text.slice(0, 80), issue: 'no date found' });
      continue;
    }
    const month = MONTHS[dm[2]];
    const date = `${dm[3]}-${String(month).padStart(2,'0')}-${String(parseInt(dm[1],10)).padStart(2,'0')}`;

    // ── Extract amount ────────────────────────────────────────────────────────
    const rm = text.match(/Received\s+([\d,]+\.?\d*)/i);
    const sm = text.match(/Spent\s+([\d,]+\.?\d*)/i);
    if (!rm && !sm) {
      diagnostics.push({ text: text.slice(0, 80), issue: 'no Received/Spent amount found' });
      continue;
    }
    const rawAmt = rm ? rm[1] : sm[1];
    const amount = rm
      ? parseFloat(rawAmt.replace(/,/g, ''))
      : -parseFloat(rawAmt.replace(/,/g, ''));

    if (isNaN(amount) || amount === 0) {
      diagnostics.push({ text: text.slice(0, 80), issue: 'amount parsed to 0 or NaN' });
      continue;
    }

    // ── Extract description ───────────────────────────────────────────────────
    // Split into lines, drop navigation noise and UI labels
    const descLines = (row.innerText || '')
      .split('\n')
      .map(l => l.trim())
      .filter(l => l.length > 2)
      .filter(l => !/^(Match|Create|Transfer|Discuss|Find.*Match|Options?|OK|More details?|Reconcile|Compact view)$/i.test(l))
      .filter(l => !/^\d{1,2}\s+(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+\d{4}$/.test(l))
      .filter(l => !/^(Received|Spent)\s+[\d,]+/.test(l))
      .filter(l => !/^(Interbank Credit|Debit|Credit|Direct Debit|Direct Credit)$/.test(l));

    // Pick the longest remaining line as the description
    const description = descLines.sort((a, b) => b.length - a.length)[0] || text.slice(0, 100);

    lines.push({
      id: `stmt-${date}-${Math.abs(amount).toFixed(2)}-${Math.random().toString(36).slice(2,6)}`,
      date,
      amount,
      description: description.slice(0, 200),
    });
  }

  if (lines.length === 0) {
    let msg = 'No unmatched statement lines found on this page.\n\n';
    if (diagnostics.length > 0) {
      msg += `${diagnostics.length} row(s) were found but could not be parsed:\n`;
      msg += diagnostics.slice(0, 3).map(d => `  • ${d.issue}: "${d.text}"`).join('\n');
      msg += '\n\nThis may mean Xero changed their UI. Run xero-rec-diagnostic.js in the console.';
    } else {
      msg += 'All rows have green suggestions — use the Xero auto-confirm bookmarklet instead.';
    }
    alert(msg);
    return;
  }

  // ── Confirm with user ─────────────────────────────────────────────────────
  const preview = lines
    .slice(0, 5)
    .map(l => {
      const sign = l.amount > 0 ? '+' : '';
      return `  ${l.date}  ${sign}${l.amount.toFixed(2).padStart(10)}  ${l.description.slice(0,45)}`;
    })
    .join('\n');

  const more = lines.length > 5 ? `\n  ...and ${lines.length - 5} more` : '';

  if (!confirm(
    `Found ${lines.length} unmatched statement line(s).\n\n` +
    `Send to bookkeeper for auto-coding?\n\n` +
    `${preview}${more}\n\n` +
    `High/medium confidence under the auto-apply cap (default $1,000) → coded in Xero immediately\n` +
    `Low confidence or large amounts → queued at bookkeeper.onestos.org/review for your approval\n` +
    `No suggestion → skipped, stays on Reconcile tab for manual coding\n` +
    `Re-running is safe: already-coded lines are detected and skipped.`
  )) return;

  // ── Call bookkeeper ───────────────────────────────────────────────────────
  (async () => {
    let res;
    try {
      res = await fetch(`${BK}/api/auto-code-statement-lines`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${pass}`,
        },
        body: JSON.stringify({ entity: ENTITY, bankAccountID: accountID, lines }),
      });
    } catch (e) {
      alert(
        `Could not reach bookkeeper: ${e.message}\n\n` +
        `Check your internet connection and try again.\n` +
        `Bookkeeper URL: ${BK}`
      );
      return;
    }

    if (res.status === 401) {
      localStorage.removeItem('bk_password');
      alert('Wrong password. The saved password has been cleared.\nRun the bookmarklet again to re-enter it.');
      return;
    }

    let data;
    try {
      data = await res.json();
    } catch (e) {
      alert(`Bookkeeper returned unexpected response (status ${res.status}).`);
      return;
    }

    if (!data.ok) {
      alert(`Bookkeeper error: ${data.error || 'Unknown error'}`);
      return;
    }

    // ── Show result ──────────────────────────────────────────────────────────
    const skippedItems = (data.results || []).filter(r => r.status === 'skipped' || r.status === 'error');
    let msg = `Done!\n\n`;
    msg += `Coded: ${data.created} transaction(s) created in Xero\n`;
    if ((data.queued || 0) > 0) {
      msg += `Queued for review: ${data.queued} (low confidence — go to bookkeeper.onestos.org/review)\n`;
    }
    if ((data.skipped || 0) > 0) {
      msg += `Skipped: ${data.skipped} (no account found — code manually in Xero)\n`;
    }

    if (skippedItems.length > 0) {
      msg += `\nSkipped items:\n`;
      msg += skippedItems
        .slice(0, 3)
        .map(r => `  • ${r.reason || r.id}`)
        .join('\n');
      if (skippedItems.length > 3) msg += `\n  ...and ${skippedItems.length - 3} more`;
    }

    if (data.created > 0) {
      msg += '\n\nReloading the page...';
      alert(msg);
      window.location.reload();
    } else {
      alert(msg);
    }
  })();
})();

Auto-confirm script Copy

/**
 * Xero Bank Rec — Auto Confirm Suggested Matches
 *
 * WHAT: Clicks every "OK" button on the Xero bank reconciliation page that
 * has a green-box suggestion next to it, one at a time, with a delay
 * between clicks so Xero can advance to the next row.
 *
 * HOW TO USE:
 *   1. Open Xero bank rec page in Chrome (any bank account)
 *   2. Press Cmd+Option+J to open the JavaScript console
 *   3. Paste this entire file into the console, press Enter
 *   4. Click OK on the confirm dialog
 *   5. Watch the page — rows disappear as they're reconciled
 *
 * WHY IT EXISTS: Xero's public REST API deliberately does NOT expose a
 * "confirm bank rec match" endpoint. The only way to automate this action
 * is to run JS inside the page that clicks the same button the human would.
 * This script is that.
 *
 * SAFETY:
 *   - Only clicks .okayButton elements that are visible AND have a
 *     .suggestion or [class*="suggest"] element in the same row
 *   - MAX_CLICKS hard cap prevents runaway loops
 *   - Stops on 3 consecutive click errors
 *   - 1 second delay between clicks (don't hammer Xero)
 *   - You can always reload the page to reset state
 *
 * MAINTENANCE: If Xero redesigns their reconcile UI, the selectors
 * (`.okayButton`, `.suggestion`) will need updating. Re-run the diagnostic
 * script at apps/bookkeeper-cli/scripts/xero-rec-diagnostic.js to get the
 * new class names.
 */
(() => {
  const DELAY_MS = 1000;
  const MAX_CLICKS = 100;

  function findReadyOk() {
    const visibleOk = Array.from(document.querySelectorAll(".okayButton")).filter((b) => {
      const s = getComputedStyle(b);
      return s.display !== "none" && s.visibility !== "hidden" && s.opacity !== "0";
    });
    return visibleOk.find((btn) => {
      let row = btn;
      for (let d = 0; d < 6 && row; d++) {
        row = row.parentElement;
        if (!row) return false;
        if (row.querySelector('.suggestion, [class*="suggest"]')) return true;
      }
      return false;
    });
  }

  if (!findReadyOk()) {
    alert("No ready matches on this page.\n\nCheck you're on the Bank Rec tab with green-box suggestions.");
    return;
  }

  if (
    !confirm(
      "Click OK on ALL suggested matches on this page?\n\n" +
        "Clicks one at a time with 1 second between. Watch the page to verify.",
    )
  ) {
    console.log("[bookkeeper] Cancelled by user");
    return;
  }

  let clicked = 0;
  let stuck = 0;
  (async () => {
    for (let i = 0; i < MAX_CLICKS; i++) {
      const btn = findReadyOk();
      if (!btn) {
        console.log("[bookkeeper] No more ready matches — done");
        break;
      }
      try {
        btn.click();
        clicked++;
        console.log(`[bookkeeper] click ${clicked}`);
        stuck = 0;
      } catch (e) {
        console.error("[bookkeeper] click failed:", e);
        stuck++;
        if (stuck >= 3) {
          console.log("[bookkeeper] 3 consecutive errors — aborting");
          break;
        }
      }
      await new Promise((r) => setTimeout(r, DELAY_MS));
    }
    alert(
      `Done.\n\nClicked OK on ${clicked} matches.\n\n` +
        `Reload the page to verify, or re-run if more suggestions appeared.`,
    );
  })();
})();