# Free Template Pack: Shopify Report Cleanup, the Manual Version

Two spreadsheet templates that turn the monthly Shopify export chore into a repeatable routine. This is the manual version of a workflow we are testing as a one-click tool. More on that at the end, briefly and honestly.

## Template 1: Monthly reconciliation layout

File: template-1-monthly-reconciliation.csv

One row per money event: every sale, every fee, every refund, in one file, tagged with the payout it belongs to. This is the file you reconcile against your bank deposits.

Build it from your two Shopify exports in about ten minutes:

1. Export A: your transactions or orders export for the month. Export B: your fees or payout export for the same period. Shopify gives you these as separate files; that separation is the whole problem.
2. Open Template 1 and delete the five sample rows. Keep the header row exactly as it is.
3. From Export A, copy one row per order into the Sale and Refund rows. Order Name is the key column; keep it exactly as Shopify writes it, hash included.
4. From Export B, add one Fee row per transaction fee, using the same Order Name as its sale. If your fee export keys rows by payout instead of order, match on the payout reference and date, and put the order name from the payout detail.
5. In a spreadsheet, you can do step 4 automatically: put the fee export in a second tab, then use XLOOKUP (Google Sheets and modern Excel) on Order Name to pull Fee Amount and Payout ID into the merged sheet. Formula shape: =XLOOKUP([@Order Name], Fees[Order], Fees[Amount], 0). Do the same for Payout ID and Payout Date.
6. Fill Account Code from your own chart of accounts. The sample codes (400 Sales, 610 Bank and card fees, 410 Sales returns) are placeholders; replace them with yours once, then reuse the file every month.
7. Check your work: the sum of Net Amount grouped by Payout ID should equal the deposit that hit your bank for that payout. If it does not, a fee row is missing. It is almost always a fee row.

## Template 2: Accounting import format

File: template-2-accounting-import.csv

The same month, reshaped the way accounting imports expect it: one journal-style line per event, debits and credits in separate columns, ready to map into Xero, QuickBooks, or any importer that accepts a CSV journal or bank-line format.

Build it from your finished Template 1:

1. Every Sale row becomes a Credit line on your sales account, amount in the Credit column.
2. Every Fee row becomes a Debit line on your fees account. Give it its own Reference by adding -FEE to the order name, as in the samples, so your accounting software does not reject it as a duplicate of the sale line.
3. Every Refund row becomes a Debit line on your returns account.
4. Set Tax Rate to match how you actually report. The samples say No Tax because the template cannot know your situation; if your sales are tax inclusive, split the tax into its own line per your accountant's instruction.
5. Dates: keep them as YYYY-MM-DD, as in the samples. Most importers accept it without complaint, and it removes the day and month order guessing that breaks imports silently.

## Column mapping, Shopify export to Template 2

- Order Name (orders export) goes to Reference, unchanged.
- Created at or Processed at goes to Date, reformatted to YYYY-MM-DD.
- Total goes to Credit for sales, on the sales account line.
- Transaction fee (fees or payout export) goes to Debit, on the fees account line, with the -FEE reference.
- Refund amount goes to Debit, on the returns account line.
- Currency carries over unchanged. Do not let your spreadsheet convert it.

## Two traps worth knowing

- Decimal separators: if your spreadsheet locale uses commas as decimal separators, it will rewrite amounts on save and your import will fail or, worse, succeed with wrong numbers. Export the final file from a sheet set to a dot-decimal locale, or check the raw file in a text editor before importing.
- Duplicate references: importers reject files where a Reference repeats. That is why fee lines get the -FEE suffix instead of sharing the order reference.

## About the one-click version

We are testing whether this exact routine is worth turning into a small local tool: drop in the two exports, apply a saved template like these, download the clean file. No store login, nothing uploaded. The test runs for 14 days and the preorder is $7, one time, refunded in full automatically if the tool does not ship. If that sounds like your kind of shortcut, the landing page linked wherever you found this pack has the details, written plainly. If the spreadsheet version is enough for you, keep it with our thanks. It is genuinely free.

Questions or a column layout your accountant insists on that these templates miss? Reply wherever you downloaded this pack. Real layouts from real bookkeepers are exactly what this test is listening for.
