Square Import
Overview
Import transaction data from Square POS CSV exports. Handles sales, tips, refunds, and Square processing fees. Maps Square's item-level detail into Open Accountant's transaction format.
Wilson Tools Used
transaction_search — check for existing Square transactions to prevent duplicatescategorize — assign categories to imported Square transactionsexport_transactions — export reconciled Square data
Column Mapping
| Square CSV Column |
Open Accountant Field |
Notes |
Date |
date |
Transaction date |
Time |
— |
Not stored |
Transaction ID |
reference_id |
Square payment ID for dedup |
Description or Item |
description |
Item or payment description |
Gross Sales |
amount |
Total before fees (positive = income) |
Tips |
amount (separate row) |
Tip amount (positive = income) |
Processing Fees |
amount (separate row) |
Square fee (negative = expense) |
Refunds |
amount (separate row) |
Refund amount (negative) |
Net Sales |
— |
Calculated, not stored directly |
Payment Method |
notes |
Cash, card, etc. |
Workflow
- Ask the user for the Square CSV file path.
- Parse the CSV and validate Square column headers.
- For each transaction row, create up to four entries:
- Sale: gross sales amount (positive), category "Revenue:Sales"
- Tips: tip amount (positive), category "Revenue:Tips" (if > $0)
- Fee: processing fee (negative), category "Fees:Payment Processing" (if > $0)
- Refund: refund amount (negative), category "Revenue:Refunds" (if > $0)
- Cash transactions have $0 processing fees — still import the sale.
- Deduplicate using Square Transaction ID.
- Preview the import summary: total sales, total tips, total fees, total refunds, net.
- Insert transactions and confirm.
Without Wilson
To work with Square exports manually:
Downloading from Square
- Log in to squareup.com (Square Dashboard)
- Go to Transactions (left sidebar)
- Click Export (top right, or the download icon)
- Select date range
- Choose Transactions CSV (not Items CSV — that's inventory)
- Download
- Alternative: Reporting > Sales > Export for summary-level data
Manual Processing in a Spreadsheet
- Open the CSV in Google Sheets or Excel.
- Separate revenue streams:
- Column E: Gross Sales (your product/service revenue)
- Column F: Tips (income, not taxed the same as sales in some jurisdictions)
- Column G: Processing Fees (expense)
- Column H: Refunds (negative revenue)
- Create separate category rows (or use multiple category columns):
- For each row with a tip > $0, create a new row for the tip amount
- For each row with a fee > $0, create a new row with the negative fee
- Summary formulas:
Gross Sales: =SUM(E:E)
Total Tips: =SUM(F:F)
Total Fees: =SUM(G:G)
Total Refunds: =SUM(H:H)
Net Revenue: =GrossSales + Tips - Fees - Refunds
Effective Fee %: =TotalFees / (GrossSales + Tips) * 100
Avg Transaction: =GrossSales / COUNTA(D:D)
- Reconcile with bank: Square deposits funds daily or weekly. Sum Net Sales between deposit dates and match against your bank statement.
Square Reports (No Export Needed)
Square Dashboard has built-in reports at Reporting > Sales Summary that show gross sales, fees, and net by day, week, or month. Use these for quick reference without exporting.
Important Notes
- Square CSV exports can be either transaction-level or item-level. This skill expects transaction-level exports. Item-level exports have one row per item per sale, which requires grouping by Transaction ID first.
- Tips are separated because they may have different tax treatment than sales revenue.
- Cash transactions appear in Square if rung through the POS but have $0 processing fee. These are still imported.
- Square deposits to your bank are net of fees. Do not import deposit rows from your bank as Square revenue — use this skill for the gross breakdown instead.
- If you use Square for both in-person and online sales, both appear in the same export.
1---2name: square-import3description: Import Square sales CSV exports with tips, refunds, and fee handling.4---5
6# Square Import
7
8## Overview
9Import transaction data from Square POS CSV exports. Handles sales, tips, refunds, and Square processing fees. Maps Square's item-level detail into Open Accountant's transaction format.
10
11## Wilson Tools Used
12- `transaction_search` — check for existing Square transactions to prevent duplicates
13- `categorize` — assign categories to imported Square transactions
14- `export_transactions` — export reconciled Square data
15
16## Column Mapping
17
18| Square CSV Column | Open Accountant Field | Notes |
19|---|---|---|
20| `Date` | `date` | Transaction date |
21| `Time` | — | Not stored |
22| `Transaction ID` | `reference_id` | Square payment ID for dedup |
23| `Description` or `Item` | `description` | Item or payment description |
24| `Gross Sales` | `amount` | Total before fees (positive = income) |
25| `Tips` | `amount` (separate row) | Tip amount (positive = income) |
26| `Processing Fees` | `amount` (separate row) | Square fee (negative = expense) |
27| `Refunds` | `amount` (separate row) | Refund amount (negative) |
28| `Net Sales` | — | Calculated, not stored directly |
29| `Payment Method` | `notes` | Cash, card, etc. |
30
31## Workflow
321. Ask the user for the Square CSV file path.
332. Parse the CSV and validate Square column headers.
343. For each transaction row, create up to four entries:
35 - **Sale**: gross sales amount (positive), category "Revenue:Sales"
36 - **Tips**: tip amount (positive), category "Revenue:Tips" (if > $0)
37 - **Fee**: processing fee (negative), category "Fees:Payment Processing" (if > $0)
38 - **Refund**: refund amount (negative), category "Revenue:Refunds" (if > $0)
394. Cash transactions have $0 processing fees — still import the sale.
405. Deduplicate using Square Transaction ID.
416. Preview the import summary: total sales, total tips, total fees, total refunds, net.
427. Insert transactions and confirm.
43
44## Without Wilson
45To work with Square exports manually:
46
47### Downloading from Square
481. Log in to **squareup.com** (Square Dashboard)
492. Go to **Transactions** (left sidebar)
503. Click **Export** (top right, or the download icon)
514. Select date range
525. Choose **Transactions CSV** (not Items CSV — that's inventory)
536. Download
547. Alternative: **Reporting** > **Sales** > **Export** for summary-level data
55
56### Manual Processing in a Spreadsheet
571. Open the CSV in Google Sheets or Excel.
582. **Separate revenue streams**:
59 - Column E: Gross Sales (your product/service revenue)
60 - Column F: Tips (income, not taxed the same as sales in some jurisdictions)
61 - Column G: Processing Fees (expense)
62 - Column H: Refunds (negative revenue)
633. **Create separate category rows** (or use multiple category columns):
64 - For each row with a tip > $0, create a new row for the tip amount
65 - For each row with a fee > $0, create a new row with the negative fee
664. **Summary formulas**:
67 ```
68 Gross Sales: =SUM(E:E)
69 Total Tips: =SUM(F:F)
70 Total Fees: =SUM(G:G)
71 Total Refunds: =SUM(H:H)
72 Net Revenue: =GrossSales + Tips - Fees - Refunds
73 Effective Fee %: =TotalFees / (GrossSales + Tips) * 100
74 Avg Transaction: =GrossSales / COUNTA(D:D)
75 ```
765. **Reconcile with bank**: Square deposits funds daily or weekly. Sum Net Sales between deposit dates and match against your bank statement.
77
78### Square Reports (No Export Needed)
79Square Dashboard has built-in reports at **Reporting** > **Sales Summary** that show gross sales, fees, and net by day, week, or month. Use these for quick reference without exporting.
80
81## Important Notes
82- Square CSV exports can be either transaction-level or item-level. This skill expects transaction-level exports. Item-level exports have one row per item per sale, which requires grouping by Transaction ID first.
83- Tips are separated because they may have different tax treatment than sales revenue.
84- Cash transactions appear in Square if rung through the POS but have $0 processing fee. These are still imported.
85- Square deposits to your bank are net of fees. Do not import deposit rows from your bank as Square revenue — use this skill for the gross breakdown instead.
86- If you use Square for both in-person and online sales, both appear in the same export.
openaccountant