How to audit 90 days of platform refunds by hand

How to audit 90 days of platform refunds by hand

How much money did the last 90 days of refunds fail to return — to your platform, or to the sellers on it? This guide walks through the complete manual answer: the API queries, the spreadsheet, the two formulas, and a disputes pass. Everything runs on a restricted read-only key. No product is required at any step.

Scope and inputs

The audit asks two questions of every refund issued in the trailing 90 days. Did the transfer get reversed when your refund policy says it should have been? And did the application fee come back when policy says it should? Two formulas answer both, and the audit's output is one number per gap class, in cents, ready to sum.

Anchor the window at run time. Ninety days is 7,776,000 seconds, so the lower bound in Unix seconds is now − 7,776,000. Treat the window as half-open — everything with created >= start and created < end — so reruns on consecutive days never double-count a boundary refund.

Two extraction routes exist. The Dashboard route exports payments filtered to refunded status and joins everything in a spreadsheet. The API route lists refunds directly. Prefer the API route: the joins this audit needs run on object IDs — transfer, application_fee, reversal records — and exports make those joins painful in ways the API makes trivial.

Access needs less privilege than the task sounds like it should. Stripe supports restricted API keys scoped per resource, with read or write access chosen per resource, so a read-only key over the relevant families audits your money while holding no money-movement rights at all (keys). Never paste a live secret key into a script; read it from an environment variable even for a one-off.

Five object families feed the audit; the table shows what each contributes and the minimum access it needs.

If you want this same procedure formatted as a step-by-step companion, the 90-day lookback walkthrough follows the identical order: extract, transform, score, sum.

Extract: the queries

Start by listing refunds created inside the window. The created filter takes Unix seconds:

curl "https://api.stripe.com/v1/refunds?created[gte]=1771977600&created[lt]=1779753600&limit=100" \
  -u "$STRIPE_SECRET_KEY:"

The example bounds cover 2026-02-25 through 2026-05-26 UTC — exactly 90 days, half-open. Swap in your own dates. limit=100 is the page size; walk the remaining pages with the follow-up cursor automatically, as documented under auto-pagination. In TypeScript with the official SDK, the paginator does that walking for you, and expanding the parent charge inline saves one request per refund:

import Stripe from "stripe";

const key = process.env.STRIPE_SECRET_KEY ?? "";
if (key === "") throw new Error("STRIPE_SECRET_KEY is not set");
const stripe = new Stripe(key);

const windowStart = Math.floor(Date.now() / 1000) - 90 * 24 * 60 * 60;

type Row = {
  refund_id: string;
  charge_id: string;
  charge_amount: number;
  amount_refunded: number;
  currency: string;
  transfer_id: string | null;
  application_fee_id: string | null;
};

const rows: Row[] = [];

for await (const refund of stripe.refunds.list({
  created: { gte: windowStart },
  expand: ["data.charge"],
  limit: 100,
})) {
  const charge = refund.charge as Stripe.Charge;
  rows.push({
    refund_id: refund.id,
    charge_id: charge.id,
    charge_amount: charge.amount,
    amount_refunded: refund.amount,
    currency: refund.currency,
    transfer_id:
      typeof charge.transfer === "string" ? charge.transfer : null,
    application_fee_id:
      typeof charge.application_fee === "string"
        ? charge.application_fee
        : null,
  });
}

console.log(rows.length + " refunds pulled");

Each refund reaches its parents through the expanded charge: the charge carries amount, the transfer ID on destination charges, and the application_fee ID wherever fees were collected. On a direct-charge fleet, none of those IDs sit on the platform side — the objects live on each connected account, so query account by account by passing the account in request options:

const perAccount = await stripe.refunds.list(
  { created: { gte: windowStart }, limit: 100 },
  { stripeAccount: "acct_PLACEHOLDER" },
);

Loop that over your connected accounts and merge the rows; the rest of the audit is identical from there. Key the merge on charge ID plus refund ID, since both survive the trip across account boundaries unchanged.

With rows in hand, enrich each one: fetch the transfer with its reversals expanded, fetch the application fee with its refunds expanded, and compute the two scores the next sections derive:

for (const row of rows) {
  if (row.transfer_id === null || row.application_fee_id === null) continue;

  const transfer = await stripe.transfers.retrieve(row.transfer_id, {
    expand: ["reversals"],
  });
  const reversalsSum = transfer.reversals.data.reduce(
    (sum, r) => sum + r.amount,
    0,
  );
  const expectedReversal = Math.round(
    (row.amount_refunded / row.charge_amount) * transfer.amount,
  );

  const fee = await stripe.applicationFees.retrieve(row.application_fee_id, {
    expand: ["refunds"],
  });
  const feeRefundedSum = fee.refunds.data.reduce(
    (sum, r) => sum + r.amount,
    0,
  );
  const expectedFeeRefund = Math.round(
    (row.amount_refunded / row.charge_amount) * fee.amount,
  );

  console.log(row.refund_id, expectedReversal - reversalsSum, expectedFeeRefund - feeRefundedSum);
}

The same logic translates directly to other languages; if your team maintains Python tooling, the Python reconciliation scripts page shows the equivalent requests end to end.

Transform: build the sheet

One row per refund. The input columns come straight off the objects; the table maps each column to its source.

Four more columns are computed, not fetched; the table defines each rule, and the next section grounds the two that matter.

Three housekeeping rules keep the sheet honest. Keep one sheet per currency and never sum cents across currencies. Store every amount as an integer in the smallest currency unit and convert to dollars only when printing. And format ID columns as text before pasting, because spreadsheets quietly mangle long alphanumeric tokens into something that no longer joins. Charges refunded in several installments simply occupy several rows; the formulas score each independently, and the cumulative check in the next section catches any drift between them.

The two formulas

Everything reduces to proportionality. Write the rules once, in cents:

expected_reversal = round((amount_refunded / charge_amount) * transfer_amount)
missing_transfer  = expected_reversal - reversals_sum
expected_fee      = round((amount_refunded / charge_amount) * app_fee_amount)
missing_fee       = expected_fee - fee_refunded_sum     (scored only if policy returns the fee)

The proportionality is not a modeling choice — it mirrors Stripe's own behavior. Full refunds of destination charges reverse the entire transfer when reverse_transfer=true, and partial refunds reverse a proportional amount (destination charges). The same parameter is documented the same way at the API level: the transfer is reversed proportionally to the amount being refunded (refunds create). Application fees behave symmetrically: refunded in full on a full refund, proportionally on a partial one, when refund_application_fee is set.

Separate charges and transfers break the symmetry, and the formulas expose it: refunding the charge has no impact on any associated transfers, so reversals_sum_cents stays zero no matter how diligent you were at refund time (separate charges and transfers). Those rows score against whatever reversal you performed manually — often none.

One caveat on rounding. When several partial refunds hit one charge, each row rounds independently, and independent rounding can drift a cent or two from the cumulative truth. Score per charge, cumulatively, before believing any single row: if the sum of amount_refunded equals the charge amount, the expected cumulative reversal is exactly the whole transfer; between boundaries, accept dust of a cent or less per charge and investigate anything larger.

Worked rows

Three synthetic rows exercise every branch of the sheet. Assumptions, labeled as assumptions:

• US platform, USD, Stripe's standard US card pricing of 2.9% + $0.30 (published pricing).
• Charge of $100.00 = 10000¢, application fee $10.00 = 1000¢, Stripe processing fee $3.20 = 320¢.
• Destination-charge transfer $90.00 = 9000¢; separate-charge transfer $70.00 = 7000¢.
• Row B is a 40% partial refund issued with both reverse_transfer=true and refund_application_fee=true.

All amounts in cents. The table shows inputs left of expected_reversal and scores right of it.

The arithmetic, line by line:

• Row A: round(10000 / 10000 * 9000) = 9000, so missing = 9000 − 0 = 9000, which is $90.00 of transfer that stayed with the seller. The fee: round(1.0 * 1000) = 1000 against 0 refunded, so $10.00 kept — the default on destination charges unless the platform acts.
• Row B: round(4000 / 10000 * 9000) = round(3600) = 3600, so missing = 3600 − 3600 = 0. The fee: round(4000 / 10000 * 1000) = 400 against 400 refunded, also 0. A correctly flagged partial refund scores clean.
• Row C: round(10000 / 10000 * 7000) = 7000, so missing = 7000 − 0 = $70.00 — the full transfer, outstanding until someone reverses it manually, which itself succeeds only if the seller's available balance covers it (separate charges and transfers).

Totals: 9000 + 0 + 7000 = 16000¢ = $160.00 of unreversed transfer value, plus 1000¢ = $10.00 of retained fee, across three toy refunds. On real data these columns are sums, and the SUM row is the number that goes in front of decision makers. Keep the refund-path audit checklist beside the sheet so each row's path classification stays consistent.

The disputes pass

Refunds are half the exposure. Lost disputes debit the platform on destination and separate charges — disputed amount plus dispute fee — and recovering from the seller is a manual transfer reversal, exactly like a refund that was issued without its flags (Connect disputes). So the audit repeats itself over disputes closed as lost inside the window:

for await (const dispute of stripe.disputes.list({
  created: { gte: windowStart },
  expand: ["data.charge", "data.charge.transfer"],
})) {
  if (dispute.status === "lost") {
    const charge = dispute.charge as Stripe.Charge;
    console.log(dispute.id, dispute.amount, charge.id, charge.transfer ?? null);
  }
}

The charge.dispute.closed explainer covers the event side if you would rather catch these as they happen. For each lost dispute, ask the sheet the same question: was the transfer reversed around the loss?

One worked row, assumptions labeled: destination charge of $100.00, transfer $90.00, dispute fee $15.00 at standard US pricing (published pricing), no reversal ever made.

Arithmetic: uncovered = 9000 − 0 = 9000, so $90.00 could still be reclaimed from the seller, while the $15.00 fee has no reversal to ride on and is gone regardless.

Fold the pass into the total: 16000 + 9000 = 25000¢ of transfer-class gaps, plus the 1000¢ fee-class gap — $260.00 identified across five synthetic rows, every cent of it traced to a specific object ID.

What you will find

Expect shape, not scatter. The findings below are qualitative patterns this audit reliably surfaces; your sheet supplies the magnitudes.

• Zero-reversal clusters concentrate. Sort missing_transfer_cents by the code path or dashboard origin that issued each refund and specific branches dominate — the places where refund calls went out without reverse_transfer, or fee refunds were never chained. Defaults leak where they were coded in.
• Age destroys recoverability. Recent gaps can often still be reclaimed. Old ones frequently cannot, in place: the seller's balance was paid out long ago, so the money exists only as a claim, not a balance. Those rows migrate from "reverse it" to "net it or negotiate it."
• Rounding dust accumulates. Rows scoring ±1¢ from multi-partial charges are noise; let them cancel rather than chasing them.
• Pending stragglers linger. Refunds sitting in pending on underfunded connected accounts look like leaks in the raw data but have simply not executed yet; park them on a separate tab.
• Separate-charge orphans surface. Transfers nobody associated with the refunds they survived, because nothing in the object graph connects them — only your records do.

The distribution is the diagnostic. Uniform spread suggests nothing actionable; concentration points at a fixable call site.

Why one pass is not enough

A snapshot ages instantly. Tomorrow's refunds repeat today's pattern, so a quarterly habit means quarters of accumulation between passes, and the oldest rows rot from "recoverable" to "write-off" while they sit. If the SUM row justifies acting at all, it eventually justifies running the identical queries continuously — the two formulas do not change week to week; only the data does. Remediation of what you find has its own procedures, from the bulk reversal of historical findings playbook onward, and continuous versions of this same arithmetic are precisely what ongoing monitoring automates, FeeGuard included.

Frequently asked questions

Does this audit need write access to my Stripe account?

No. Restricted keys scope access per resource with read or write chosen separately, so read-only over refunds, charges, transfers, application fees, and disputes covers the entire procedure (keys). The key never moves money, and it should still live in an environment variable rather than in the script.

Can I do all of this from Dashboard CSV exports?

Partially. The payments export gives you refunds and their charges, but reversals and application-fee refunds live in separate exports, and joining them means manual key matching in a spreadsheet. It works; it is slower and frailer. The Excel-export recovery workflow walks the export-based variant if you cannot use the API.

We settle in multiple currencies — what changes?

Only discipline, not method. Run one sheet per currency, convert nothing until the final report, and remember that in zero-decimal currencies like JPY the integer amounts are already the major unit, so "cents" columns become "yen" columns (supported currencies). Never sum a USD column onto a EUR column.

What about refunds still showing as pending?

Score them, but on their own tab. A pending refund has not moved money yet — on direct charges it waits for the connected account's balance to fund it, then processes automatically (connect charges). Recheck them before you publish totals, because some will resolve themselves and some will reveal a seller whose balance never recovers.

Run the 90-day audit

Nothing above requires anything but a spreadsheet and an afternoon, and rerunning it weekly is exactly as tedious as it sounds. FeeGuard exists because this arithmetic runs silently on every refund your platform issues, and the running total almost never appears anywhere. The free audit reads your last 90 days of Connect activity through a restricted, read-only API key and reports every unreversed transfer, unreturned application fee, and uncovered dispute loss with the amounts attached. You get the answer first; ongoing monitoring stays optional afterward.

Run the free 90-day audit.

FeeGuard is an independent product and is not affiliated with, endorsed by, or sponsored by Stripe, Inc.