Per Transaction Mode: Amazon – QuickBooks Clearing Reconciliation Guide for Synder Users

Understanding how Synder works with clearing accounts

What is a clearing account? It’s a temporary holding account that Synder uses to replicate the real money flow between Amazon and your Bank.  When Amazon processes orders, fees, refunds, etc, Synder records these in your QuickBooks Online clearing account. Then Amazon sends the settlement money to the bank, and Synder transfers the corresponding amount from the clearing to the checking account.

Important: Clearing reconciliation represents cash and cash movements through Amazon to your bank. In many cases, it will not equal your sales!

Why reconciling clearing accounts matters:

Reconciling your clearing accounts is essential for keeping your books accurate, especially since these accounts appear on your balance sheet.

Here’s what affects the balance:

  • Sales increase the clearing account balance (money coming in)
  • Fees and refunds decrease the clearing account balance (money going out)
  • Payouts transfer money from the clearing account to your bank account

 If these movements aren’t tracked and reconciled correctly, your balance sheet won’t reflect your actual financial position.

We suggest reconciling this account monthly when using Synder. 

How to reconcile an Amazon clearing account?

Reconciliation of a clearing account is basically comparing its balance to a statement at a given date. However, Amazon doesn’t provide a monthly statement for reconciliation. The instructions below show how to determine the number to reconcile against:

  1. Determine the starting balance for reconciliation  (if this is your first reconciliation, the next chapter will help you figure it out).
  2. Download the Custom Unified transactions report from the following location: Amazon → “Payments” section of the menu → Reports repository: Account types – “All (unified report)”, Type – “Transaction”, for the needed time period -> click “Request” and once file is prepared -> Download CSV.

  3. Sum up the “total” column from the report, which indicates the net amount earned for each order including all the fees and settlements (aka payouts/transfers). This amount is the turnover of the cash for the selected period.
  4. Sum up the starting balance and the turnover from the report – this is your ending balance for reconciliation.

Starting and ending balances

Figuring out your starting balance may be a tricky task. Of course, if you have just started selling, your starting balance is zero, and you should use it as your original reconciliation starting balance. Moving on, the ending balance of your previous reconciliation will be the starting balance for the next one.

However, if you start using Synder well after launching your business, you’ll need to determine a starting balance for a specific month.  Synder lets you begin syncing data from a chosen date, so everything prior to that date related to the first payout will make up your starting balance.

 Here is how to figure out the starting balance for Amazon:

  1. Identify your starting date. Let’s take as an example  May 1st, 2025, as an example.
  2. Find the first settlement after your starting date under Amazon -> Payments -> All Statements tab.
    In our example with a May 1st starting date, you can see that the first May settlement appears on May 14th.

  3. Download the report and sum up all the transactions before the start date. This will be your starting balance. Set it in your accounting platform manually.

    In our example, it means filtering all the transactions with a “posted_date” in April. Once done, sum up all the values for these transactions (sales totals, fees, etc). This will be your starting balance for May.
  4. Start syncing with Synder from your chosen starting date. In our example, this date is  May 1st.

Discrepancies in reconciliation

If your Amazon ending balance doesn’t match what you see in QuickBooks Online, it usually means that there’s a mismatch in the data being compared or some records didn’t sync properly. This guide will walk you through how to compare Amazon and QuickBooks Online reports, locate where discrepancies are coming from, and what steps to take to resolve them using Excel.

Understanding and resolving discrepancies early helps prevent accounting errors and ensures your books are audit-ready.

Step 1: Checking the basics

  1. Go to Synder and filter transactions by status. Make sure that all the transactions within your reconciliation period are in statuses: Synced or Skipped on the Platform transactions page. All the other statuses indicate that the transaction is not in your books or need additional checking. As long as these transactions are present, your books won’t reconcile.
  2. Make sure you are comparing the right numbers (sales might not equal cash, for example). It’s important to use the right numbers for reconciliation.
  3. If your platform was disconnected within the reconciliation period, make sure you run a “Historical Import” on the Platform transactions page to retrieve any missing transactions.

If you’ve checked all the basics but the discrepancy still persists, download the report from Amazon and compare it with the report from QuickBooks.This will help identify the transactions causing differences.

Step 2: Download required reports

From Amazon:

  1. Log in to your Amazon Seller Central.
  2. Navigate to the “Payments” section of the menu.
  3. Go to the Reports repository.
  4. Select Account types = “All (unified transactions)”.
  5. Select Type = “Transaction” (this gives you detailed line-by-line data).
  6. Set your date range for the reconciliation period.
  7. Download the report.

⚠️ Important notes:

  • Mind the timezone. Usually, this report is in PST (Pacific Standard Time) or UTC timezones. Make sure it matches the time zone set for Amazon in Synder (you can check it in Synder’s Settings).
  • Download only released transactions, not deferred ones, as deferred transactions won’t appear in settlement reports until released.
  • If you have multiple Amazon accounts with different currencies, download reports from all accounts.

From QuickBooks Online:

Method 1 – Transaction list (recommended):

  1. In QuickBooks Online, go to the Chart of Accounts.
  2. Find your Amazon clearing account(s).
    • Note: You may have multiple clearing accounts if you process different currencies.
  3. Click on the clearing account name.
  4. Click “Run Report”.
  5. Set the same date range as your Amazon report.
  6. Export to Excel.

Method 2 – Balance Sheet:

  1. Go to Reports → Balance Sheet.
  2. Set display columns to “by month”.
  3. Click on the clearing account balance for your target month.
  4. Export the detailed transactions.

Note: If your QuickBooks clearing account has tens of thousands of transactions, it might not download the full dataset. After downloading one of the reports above, check for data completeness. If the report is missing data, try the alternative download method.

Step 3: Prepare your Excel data for comparison

How to clean up transaction IDs

Understanding Amazon ID formats: Amazon IDs in Synder have a specific format that differs from what appears in Amazon reports:

  • Synder Format: CHARGE-111-5443183-4202645-1735810561000
  • Amazon Report Format: 111-5443183-4202645

Why is there a difference?

Synder uses a specific format for Amazon transaction IDs that includes additional metadata not found in Amazon reports. This is done to help identify transaction types and processing times more clearly. Here’s what the extra parts mean:

  • CHARGE = Transaction type prefix (helps identify transaction type)
  • 1735810561000 = Unix timestamp (when Synder processed the transaction)

To clean IDs in Excel:

  1. Create a new column called “Clean ID”.
  2. Use the following formulas to remove prefixes and timestamps:
MethodFormula ExampleWhen to Use
Remove everything before first dash=MID(A1,SEARCH(“-“,A1)+1,LEN(A1))For IDs starting with transaction type
Remove everything after last dash=LEFT(A1,LEN(A1)-SEARCH(“-“,SUBSTITUTE(A1,”-“,””,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””)))))To remove timestamps
Extract middle portion=MID(A1,SEARCH(“-“,A1)+1,SEARCH(“-“,A1,SEARCH(“-“,A1)+1)-SEARCH(“-“,A1)-1)For complex ID structures
  1. Apply this cleaning to the QuickBooks Online ID columns to have proper matches.

Handling IDs that don’t exist:

It’s important to know that some Amazon fees don’t have IDs in the Amazon report, but Synder creates IDs for them during sync. These often include:

  • Service fees (storage, fulfillment, subscription fees)
  • Some advertising charges
  • Miscellaneous adjustments

To clean this up, we’ll use Excel’s Consolidate function.

Handling transactions without IDs:

  • Filter them out initially during ID-based comparison
  • Handle them separately by comparing amounts and dates
  • Group them by transaction type for easier matching

We will discuss this in more detail at the end.

How to organize your data

1. Create separate worksheets for:

  • Amazon raw data (original download)
  • QuickBooks Online raw data (original download)
  • Comparison analysis (your working sheet)

2. In your Comparison Analysis sheet, create columns for:

  • Amazon ID 
  • Amazon amount
  • QuickBooks Online ID (cleaned)
  • QuickBooks Online amount
  • Lookup
  • Difference

3. Consolidate multiple lines

Amazon transactions may create multiple QuickBooks Online entries, especially for complex orders with multiple fees. For example, one Amazon order might create:

  • Order line: 111-5443183-4202645 Amount: $100
  • Fulfillment fee line: 111-5443183-4202645 Amount: -$15
  • Referral fee line: 111-5443183-4202645 Amount: -$8

How to consolidate using Excel’s CONSOLIDATE function

  1. Prepare your data:
    • Ensure IDs are in Column A and amounts are in Column B (side by side).
    • Filter OUT transactions without IDs first, otherwise, Excel may merge no-ID rows incorrectly..
  2. Use Excel’s Consolidate function:
    • Go to Data → Consolidate.
    • In the reference box, select both columns A and B (your ID and amount columns).
    • Click the “+” (plus sign) to add this reference.
    • In the “Use labels in” section, check BOTH boxes:
      • ☑️ Top row (uses column headers);
      • ☑️ Left column (uses IDs as grouping criteria).
    • Click OK.
  3. As a result, Excel will:
    • Group all transactions with the same ID;
    • Sum the amounts for each unique ID;
    • Create a new consolidated table with one row per unique ID.
  4. Compare consolidated totals:
    • Use your newly consolidated QuickBooks Online data to compare against the Amazon report.
    • Each Amazon transaction should now align with a single consolidated total from QuickBooks Online.

Step 4: Compare data using VLOOKUP

Our objective is to match transaction amounts from Stripe and QuickBooks Online that have shared IDs. This process helps us identify any discrepancies in amounts or missing transactions within QuickBooks Online.

1. Set up your comparison formula

  1. Copy your cleaned Amazon IDs and amounts to columns A and B.
  2. In the ‘Lookup’ column, use VLOOKUP to find matching QuickBooks Online amounts:

=VLOOKUP(A2,C:D,2,FALSE)

Where:

  • A2 = Amazon ID you’re looking up
  • C:D = Your QuickBooks Online data range (ID in column C, amount in column D)
  • 2 = Returns the amount from the 2nd column (QuickBooks Online amount)
  • FALSE = Exact match only
  1. In the ‘Difference’ column, calculate the difference by subtracting the looked-up amount from the Amazon amount:

2. Identify discrepancies

Perfect matches should show 0 in the difference column.

Common discrepancy indicators include:

  • Negative number = QuickBooks Online amount is higher than the Amazon amount
  • Positive number = Amazon amount is higher than the QuickBooks Online amount 
  • N/A = Transaction ID exists in Amazon but not in QuickBooks Online.

3. Consolidate multiple lines

Amazon transactions may create multiple QuickBooks Online entries, especially for complex orders with multiple fees. Use Excel’s CONSOLIDATE function:

  1. Filter data with IDs first: exclude no-ID transactions.
  2. Use Data → Consolidate to group by ID.
  3. Sum up amounts for transactions with the same ID.
  4. Compare consolidated totals.

Step 5: Analyze transaction types

Different Amazon transactions sync to QuickBooks Online as different record types:

Amazon TransactionQuickBooks Online Record TypeWhat to Look For
Charges (orders)Sales Receipts/Invoices + PaymentsCustomer orders
RefundsRefund ReceiptsCustomer returns
FBA Fulfillment FeesExpensesPer-unit fulfillment charges
Storage FeesExpensesMonthly storage charges
Advertising ChargesExpensesSponsored product costs
Subscription FeesExpensesMonthly seller plan fees
Balance ReserveTransfersWithheld funds
PayoutsTransfersBank deposits
Disposal/Removal FeesExpensesInventory disposal costs
Commission/Referral FeesExpensesAmazon’s selling fees

Filter and compare by transaction type:

  1. Sort your data by transaction type
  2. Compare each type separately (orders vs orders, fees vs fees, etc.)
  3. This makes it easier to spot patterns in missing or incorrect data

Step 6: Address common discrepancies

1. Missing transactions in QuickBooks Online

Possible causes:

  • Deferred transactions not yet released (Amazon holds some payments for 7-14 days)
  • Transaction failed to sync due to network issues
  • Transaction filtered out by Synder settings
  • Unsupported transaction type (https://synder.com/help/unsupported-amazon-transaction-types-guide/)
  • Multicurrency transactions from different Amazon marketplaces

Actions to take:

  1. Check if transactions are “Deferred” in Amazon reports
  2. Verify all Amazon marketplaces are connected to Synder
  3. Check Synder’s sync logs for failed transactions
  4. Force a manual sync in Synder for the missing period
  5. Confirm timezone settings match between Amazon reports (PST/UTC) and Synder.

2. Amount differences

Possible causes:

  • Currency conversion differences between marketplaces
  • Partial refunds not properly matched to original orders
  • Difference in fee calculations between Amazon reports and Synder processing
  • Tax withholding adjustments for marketplace facilitator tax
  • Reserve balance adjustments affecting net amounts

Actions to take:

  1. Check for currency conversions if selling in multiple countries
  2. Verify tax withholding settings in Synder
  3. Look for reserve balance movements in the same period
  4. Compare gross vs. net amounts in Amazon reports
  5. Check for related adjustment entries in subsequent periods

3. Extra transactions in QuickBooks Online

Possible causes:

  • Manual entries created by your accounting team
  • Duplicate syncing from multiple sync attempts
  • Transactions from other platforms (Amazon Pay vs, Amazon Seller)
  • Journal entries or adjustments made outside of Synder
  • Starting balance entries when first setting up clearing accounts

Actions to take:

  1. Look for transactions without Amazon IDs in descriptions
  2. Check if descriptions indicate manual entry
  3. Verify transaction source in QuickBooks Online (look for “Synder” in descriptions)
  4. Check for duplicate Amazon connections in Synder
  5. Review any manual clearing account adjustments

4. Transactions without IDs

Special handling required for:

  • Service fees (storage, fulfillment, disposal)
  • Subscription fees
  • Some advertising charges
  • Miscellaneous adjustments

Comparison strategy:

  1. Filter out all transactions with IDs first
  2. Group remaining transactions by type
  3. Compare totals by type and date
  4. Use amount matching for verification

Step 7: Handle Amazon specific considerations

1. Deferred transactions

What are deferred transactions? Amazon sometimes holds payments for certain orders as part of the risk management process. These transactions appear with a “Deferred” status until the funds are released.

Impact on reconciliation:

  • Deferred transactions won’t appear in settlement reports
  • Synder only syncs released transactions
  • Orders may be created in January but paid/synced in March

How to handle:

  1. Only compare released transactions
  2. Check Amazon’s “Deferred Transactions” report separately
  3. Adjust expectations for timing differences

2. Multi-currency considerations

If you sell in multiple Amazon marketplaces:

  1. Download reports from all marketplaces (US, Canada, UK, etc.)
  2. Be aware that each marketplace may have separate clearing accounts
  3. Account for currency conversion, as it may impact totals
  4. Combine all reports before comparing to QuickBooks Online

3. Timezone alignment

Critical: Amazon Unified Transaction reports are in PST (Pacific Standard Time).

  1. Ensure the Synder timezone is set to PST for Amazon
  2. Verify QuickBooks Online report dates align with PST
  3. Account for date differences if your business operates in a different timezone

Step 8: When to reach out  to support

Get in touch with support when you’ve identified specific transactions causing issues but can’t resolve them.

What to include in your support request:

  • Reconciliation period (start and end dates)
  • Specific transaction IDs (both Amazon format and Synder format)
  • Screenshots of problematic transactions in both Amazon Seller Central and QuickBooks Online
  • Summary of your findings (e.g., “Found 5 missing fulfillment fees totaling $47.50”)
  • Currency/marketplace information if applicable
  • Steps already taken to resolve the issue

Best practices for ongoing reconciliation

Staying on top of your clearing accounts requires consistency. Here are key practices to help you keep your books accurate and your reconciliation process smooth over time:

  1. Reconcile monthly rather than waiting for quarter-end
  2. Check for deferred transactions regularly in Amazon reports
  3. Monitor multiple currency clearing accounts if selling internationally
  4. Set up alerts in Synder for failed sync transactions
  5. Maintain PST timezone consistency across Amazon, Synder, and reconciliation periods
  6. Document any manual entries made to clearing accounts
  7. Review unsupported transaction types periodically as Synder adds support

A consistent reconciliation routine saves time, reduces errors, and keeps your financials audit-ready. By following these best practices, you’ll avoid surprises down the line and ensure your clearing accounts stay clean and reliable month after month.

If this sounds like a lot to manage, consider moving to Pro, and your account manager will be able to handle it from here. Book a call to see if it’s a good fit.

Author

Leave a Reply

Your email address will not be published. Required fields are marked *