Xargaar ToolsSplit an Amazon settlement report into sales, fees and refunds.
Drop in your Flat File V2 settlement reports. See each settlement split into product sales, shipping and other item charges, refunds, promotions, fees, marketplace tax withheld and advertising, with every line type listed, and the rows added up and compared with the total-amount in the file. The pack adds a journal CSV for each settlement that passes the checks and your review. Nothing is uploaded.
Tested only on made-up files built from Amazon's documented column names, never on a real settlement report. The header-row layout is not verified by Amazon's column list. The check compares the rows with the total-amount written in the same file. It does not check your bank. Compare the totals with your Seller Central settlement summary before you rely on them.
Set these to match your files before you drop them. Nothing is guessed. Limits: 30 files, 40 MB each, 200 MB in total, 300,000 rows per file.
What it does
Each settlement header row in the file starts a settlement. Many files can be loaded at once, so a quarter or a year is one drop.
Amazon puts charges and fees into three columns, transaction-type, amount-type and amount-description, and can add new ones. We group the common ones and list all of them, so a new fee never disappears.
The rows are added up and compared with the settlement total-amount in the file. A difference or exact duplicate rows are flagged and block the journal.
Pay once. Work through all your settlements.
- All loaded settlements within the file limits instead of the first 3.
- A journal CSV for each settlement that passes the checks and your review, with your own account names, and a CSV of every line type.
- One folder that opens in your browser with no internet. Your Amazon data stays on your computer.
Questions
Which report do I use?
The Flat File V2 Settlement Report, reportType GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE_V2, which Amazon says replaces the older flat file and XML settlement reports (developer-docs.amazon.com, Settlement Reports). In Seller Central it is under Payments, Reports Repository. The tool needs the documented columns settlement-id, settlement-start-date, settlement-end-date, deposit-date, total-amount, currency, transaction-type, amount-type, amount-description and amount. A file without them is rejected with a message.
How are rows grouped?
A row with a total-amount starts a settlement, and the rows after it belong to it. Only rows with the transaction-type Order or Refund are grouped: ItemPrice with the description Principal is product sales, other ItemPrice rows are shipping, tax and other item charges, ItemPrice under Refund is refunds, and Promotion, ItemFees and ItemWithheldTax get their own lines. Cost of Advertising rows are advertising. Everything else goes under Everything else and is listed by name, and you must review it before the journal unlocks. Rows with a blank transaction-type or amount-type block the journal. This grouping is ours, not Amazon's, so check it against your own chart of accounts.
Is the journal entry accounting advice?
No. It is a starting point built from a settlement's totals, with account names you set. The first line is a settlement clearing account, not your bank. Your accountant decides how Amazon sales, fees, refunds and withheld tax are booked. The tool makes no tax or accounting claims.
Has it been tested on real Amazon files?
No. It was tested only on made-up files. The settlement header row (the first row with a total-amount) is described by third parties but is not in Amazon's column list, so that layout is unverified. Amounts and dates are read only in the formats you select. A date that does not fit the selected format blocks the journal for that settlement.
Is anything uploaded?
No. The files are read on your device.
Supported-format preview. Limits: 30 tab-delimited files, 40 MB each, 200 MB total, 300,000 rows per file, 2,000 characters per field, 100-character settlement IDs, 200-character types/descriptions, 100,000,000 per amount. UTF-8 or explicitly selected Windows-1252; no automatic encoding or locale detection. Currency uses ISO minor-unit precision. The format choices apply to all loaded files, so load different formats separately. Dates are labels as written, not timezone-converted. A matching file total is not proof of bank receipt or correct accounting. A journal uses a clearing account, not your bank.