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 duplicates
categorize — assign categories to imported Square transactions
export_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-import
3description: >
4 Import Square sales CSV exports with tips, refunds, and fee handling.
5metadata:
6 source: https://skillmd.com/skills/openaccountant/square-import
7---
8
9# Square Import
10
11## Overview
12Import 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.
13
14## Wilson Tools Used
15- `transaction_search` — check for existing Square transactions to prevent duplicates
16- `categorize` — assign categories to imported Square transactions
17- `export_transactions` — export reconciled Square data
18
19## Column Mapping
20
21| Square CSV Column | Open Accountant Field | Notes |
22|---|---|---|
23| `Date` | `date` | Transaction date |
24| `Time` | — | Not stored |
25| `Transaction ID` | `reference_id` | Square payment ID for dedup |
26| `Description` or `Item` | `description` | Item or payment description |
27| `Gross Sales` | `amount` | Total before fees (positive = income) |
28| `Tips` | `amount` (separate row) | Tip amount (positive = income) |
29| `Processing Fees` | `amount` (separate row) | Square fee (negative = expense) |
30| `Refunds` | `amount` (separate row) | Refund amount (negative) |
31| `Net Sales` | — | Calculated, not stored directly |
32| `Payment Method` | `notes` | Cash, card, etc. |
33
34## Workflow
351. Ask the user for the Square CSV file path.
362. Parse the CSV and validate Square column headers.
373. For each transaction row, create up to four entries:
38 - **Sale**: gross sales amount (positive), category "Revenue:Sales"
39 - **Tips**: tip amount (positive), category "Revenue:Tips" (if > $0)
40 - **Fee**: processing fee (negative), category "Fees:Payment Processing" (if > $0)
41 - **Refund**: refund amount (negative), category "Revenue:Refunds" (if > $0)
424. Cash transactions have $0 processing fees — still import the sale.
435. Deduplicate using Square Transaction ID.
446. Preview the import summary: total sales, total tips, total fees, total refunds, net.
457. Insert transactions and confirm.
46
47## Without Wilson
48To work with Square exports manually:
49
50### Downloading from Square
511. Log in to **squareup.com** (Square Dashboard)
522. Go to **Transactions** (left sidebar)
533. Click **Export** (top right, or the download icon)
544. Select date range
555. Choose **Transactions CSV** (not Items CSV — that's inventory)
566. Download
577. Alternative: **Reporting** > **Sales** > **Export** for summary-level data
58
59### Manual Processing in a Spreadsheet
601. Open the CSV in Google Sheets or Excel.
612. **Separate revenue streams**:
62 - Column E: Gross Sales (your product/service revenue)
63 - Column F: Tips (income, not taxed the same as sales in some jurisdictions)
64 - Column G: Processing Fees (expense)
65 - Column H: Refunds (negative revenue)
663. **Create separate category rows** (or use multiple category columns):
67 - For each row with a tip > $0, create a new row for the tip amount
68 - For each row with a fee > $0, create a new row with the negative fee
694. **Summary formulas**:
70 ```
71 Gross Sales: =SUM(E:E)
72 Total Tips: =SUM(F:F)
73 Total Fees: =SUM(G:G)
74 Total Refunds: =SUM(H:H)
75 Net Revenue: =GrossSales + Tips - Fees - Refunds
76 Effective Fee %: =TotalFees / (GrossSales + Tips) * 100
77 Avg Transaction: =GrossSales / COUNTA(D:D)
78 ```
795. **Reconcile with bank**: Square deposits funds daily or weekly. Sum Net Sales between deposit dates and match against your bank statement.
80
81### Square Reports (No Export Needed)
82Square 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.
83
84## Important Notes
85- 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.
86- Tips are separated because they may have different tax treatment than sales revenue.
87- Cash transactions appear in Square if rung through the POS but have $0 processing fee. These are still imported.
88- 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.
89- If you use Square for both in-person and online sales, both appear in the same export.