Bill Ballou
fdb813d647
New capability for importing bank transactions (Schwab JSON), matching against existing ledger entries, creating missing transactions, and reconciling balances. Includes two new Grist tables (Reconciliations, BankRules), two bank-import templates, and reference documentation.
423 lines
12 KiB
Markdown
423 lines
12 KiB
Markdown
# Workflow Examples
|
|
|
|
Detailed code examples for common accounting operations.
|
|
|
|
**Important:** Before adding records, consult [schema.md](schema.md) to identify writable fields. Formula columns (marked "Formula" in schema) must NOT be included in `add_records` payloads or a 400 error will occur.
|
|
|
|
## Contents
|
|
- [Create a Vendor](#create-a-vendor)
|
|
- [Create Items](#create-items-for-common-purchases)
|
|
- [Complete Bill Entry](#complete-bill-entry-6-steps)
|
|
- [Pay Bill from Checking](#pay-bill-from-checking-account)
|
|
- [Pay Bill via Owner](#pay-bill-via-owner-reimbursement)
|
|
- [Reimburse Owner](#reimburse-owner)
|
|
- [Batch Operations](#batch-operations)
|
|
- [Bank Reconciliation](#bank-reconciliation)
|
|
|
|
## Create a Vendor
|
|
|
|
```python
|
|
add_records("Vendors", [{
|
|
"Name": "Acme Corp",
|
|
"DefaultExpenseAccount": 36, # Software & Subscriptions
|
|
"PaymentTerms": "Due on Receipt",
|
|
"Notes": "Software vendor",
|
|
"IsActive": True
|
|
}])
|
|
# Returns: {"inserted_ids": [vendor_id]}
|
|
```
|
|
|
|
## Create Items for Common Purchases
|
|
|
|
```python
|
|
add_records("Items", [{
|
|
"Name": "Monthly Software",
|
|
"DefaultAccount": 36,
|
|
"DefaultDescription": "Monthly SaaS subscription",
|
|
"IsActive": True
|
|
}])
|
|
```
|
|
|
|
## Complete Bill Entry (6 Steps)
|
|
|
|
**Step 1: Create Bill Header**
|
|
```python
|
|
add_records("Bills", [{
|
|
"Vendor": 1, # vendor_id
|
|
"BillNumber": "INV-001",
|
|
"BillDate": 1759708800, # Unix timestamp
|
|
"DueDate": 1759708800,
|
|
"Status": "Open",
|
|
"Memo": "October services"
|
|
}])
|
|
# Returns: {"inserted_ids": [bill_id]}
|
|
```
|
|
|
|
**Step 2: Create Bill Line(s)**
|
|
```python
|
|
add_records("BillLines", [{
|
|
"Bill": 1, # bill_id from step 1
|
|
"Item": 1, # optional - auto-fills Account/Description
|
|
"Account": 36, # expense account
|
|
"Description": "Monthly subscription",
|
|
"Amount": 100.00
|
|
}])
|
|
```
|
|
|
|
**Step 3: Create Journal Entry**
|
|
```python
|
|
# Transaction header
|
|
add_records("Transactions", [{
|
|
"Date": 1759708800,
|
|
"Description": "Acme Corp - October services",
|
|
"Reference": "INV-001",
|
|
"Status": "Posted"
|
|
}])
|
|
# Returns: {"inserted_ids": [txn_id]}
|
|
|
|
# Transaction lines: Debit expense, Credit AP
|
|
add_records("TransactionLines", [
|
|
{"Transaction": 1, "Account": 36, "Debit": 100.00, "Credit": 0, "Memo": "Monthly subscription"},
|
|
{"Transaction": 1, "Account": 4, "Debit": 0, "Credit": 100.00, "Memo": "Monthly subscription"}
|
|
])
|
|
```
|
|
|
|
**Step 4: Link Bill to Transaction**
|
|
```python
|
|
update_records("Bills", [{"id": 1, "fields": {"EntryTransaction": 1}}])
|
|
```
|
|
|
|
**Step 5: Upload Invoice (if available)**
|
|
```bash
|
|
bash scripts/upload-attachment.sh invoice.pdf Bills 1 $TOKEN Invoice
|
|
```
|
|
|
|
**Step 6: Post-Entry Audit (REQUIRED)**
|
|
|
|
Run audit checks before concluding. See [Audit Reference](audit.md) for details.
|
|
|
|
```sql
|
|
-- Check 1: Transaction balanced
|
|
SELECT IsBalanced FROM Transactions WHERE id = {txn_id}
|
|
-- Expected: true
|
|
|
|
-- Check 2: Bill integrity
|
|
SELECT id, Vendor, EntryTransaction, Amount FROM Bills WHERE id = {bill_id}
|
|
-- Expected: All fields populated, Amount > 0
|
|
```
|
|
|
|
## Pay Bill from Checking Account
|
|
|
|
```python
|
|
# Step 1: Create payment transaction
|
|
add_records("Transactions", [{
|
|
"Date": 1760832000,
|
|
"Description": "Payment - Acme Corp INV-001",
|
|
"Reference": "Check #1001",
|
|
"Status": "Cleared"
|
|
}])
|
|
# Returns: {"inserted_ids": [txn_id]}
|
|
|
|
# Step 2: Debit AP, Credit Checking
|
|
add_records("TransactionLines", [
|
|
{"Transaction": 2, "Account": 4, "Debit": 100.00, "Credit": 0, "Memo": "Pay INV-001"},
|
|
{"Transaction": 2, "Account": 14, "Debit": 0, "Credit": 100.00, "Memo": "Pay INV-001"}
|
|
])
|
|
|
|
# Step 3: Create BillPayment record
|
|
add_records("BillPayments", [{
|
|
"Bill": 1,
|
|
"Transaction": 2,
|
|
"Amount": 100.00,
|
|
"PaymentDate": 1760832000
|
|
}])
|
|
|
|
# Step 4: Update bill status
|
|
update_records("Bills", [{"id": 1, "fields": {"Status": "Paid"}}])
|
|
|
|
# Step 5: Upload receipt (if available)
|
|
# bash scripts/upload-attachment.sh receipt.pdf Bills 1 $TOKEN Receipt
|
|
|
|
# Step 6: Post-Payment Audit (REQUIRED)
|
|
# Verify payment transaction balances and bill status updated correctly
|
|
```
|
|
|
|
## Pay Bill via Owner Reimbursement
|
|
|
|
When the owner pays a business expense personally:
|
|
|
|
```python
|
|
# Step 1: Create payment transaction
|
|
add_records("Transactions", [{
|
|
"Date": 1760832000,
|
|
"Description": "Owner payment - Acme Corp INV-001",
|
|
"Reference": "Owner Reimb",
|
|
"Status": "Posted"
|
|
}])
|
|
|
|
# Step 2: Debit AP, Credit Due to Owner (not Checking)
|
|
add_records("TransactionLines", [
|
|
{"Transaction": 2, "Account": 4, "Debit": 100.00, "Credit": 0, "Memo": "Pay INV-001"},
|
|
{"Transaction": 2, "Account": 22, "Debit": 0, "Credit": 100.00, "Memo": "Owner paid"}
|
|
])
|
|
|
|
# Step 3: Create BillPayment record
|
|
add_records("BillPayments", [{
|
|
"Bill": 1,
|
|
"Transaction": 2,
|
|
"Amount": 100.00,
|
|
"PaymentDate": 1760832000
|
|
}])
|
|
|
|
# Step 4: Update bill status
|
|
update_records("Bills", [{"id": 1, "fields": {"Status": "Paid"}}])
|
|
|
|
# Step 5: Upload receipt (if available)
|
|
# bash scripts/upload-attachment.sh receipt.pdf Bills 1 $TOKEN Receipt
|
|
|
|
# Step 6: Post-Payment Audit (REQUIRED)
|
|
```
|
|
|
|
## Reimburse Owner
|
|
|
|
When business pays back the owner:
|
|
|
|
```python
|
|
add_records("Transactions", [{
|
|
"Date": 1762041600,
|
|
"Description": "Owner reimbursement",
|
|
"Reference": "Transfer",
|
|
"Status": "Cleared"
|
|
}])
|
|
|
|
add_records("TransactionLines", [
|
|
{"Transaction": 3, "Account": 22, "Debit": 500.00, "Credit": 0, "Memo": "Reimburse owner"},
|
|
{"Transaction": 3, "Account": 14, "Debit": 0, "Credit": 500.00, "Memo": "Reimburse owner"}
|
|
])
|
|
```
|
|
|
|
## Batch Operations
|
|
|
|
When entering multiple bills efficiently:
|
|
|
|
1. **Create all Bills first** → collect inserted IDs
|
|
2. **Create all BillLines** referencing bill IDs
|
|
3. **Create all Transactions** → collect inserted IDs
|
|
4. **Create all TransactionLines** referencing transaction IDs
|
|
5. **Update all Bills** with EntryTransaction links in one call
|
|
6. (If paying) Create payment transactions, lines, and BillPayments
|
|
7. **Upload invoice attachments** if files are available
|
|
|
|
### Batch Attachment Uploads
|
|
|
|
```bash
|
|
# Example batch upload pattern
|
|
TOKEN=$(request_session_token with write permission)
|
|
for each (bill_id, invoice_path):
|
|
curl -X POST -H "Authorization: Bearer $TOKEN" \
|
|
-F "file=@$invoice_path" \
|
|
https://grist-mcp.bballou.com/api/v1/attachments
|
|
# Returns attachment_id
|
|
update_records("Bills", [{"id": bill_id, "fields": {"Invoice": ["L", attachment_id]}}])
|
|
```
|
|
|
|
### Batch Update Example
|
|
|
|
```python
|
|
update_records("Bills", [
|
|
{"id": 1, "fields": {"EntryTransaction": 1}},
|
|
{"id": 2, "fields": {"EntryTransaction": 2}},
|
|
{"id": 3, "fields": {"EntryTransaction": 3}}
|
|
])
|
|
```
|
|
|
|
## Sample Payloads
|
|
|
|
Copy-paste ready JSON payloads for `add_records`. Only writable fields are included.
|
|
|
|
### Owner Equity Contribution
|
|
|
|
```json
|
|
// Transaction
|
|
{"Date": 1768348800, "Description": "Owner equity contribution", "Reference": "Owner Investment", "Status": "Posted", "Memo": ""}
|
|
|
|
// TransactionLines (Debit Checking, Credit Owner's Investment)
|
|
[
|
|
{"Transaction": 51, "Account": 14, "Debit": 1000, "Credit": 0, "Memo": "Owner equity contribution"},
|
|
{"Transaction": 51, "Account": 23, "Debit": 0, "Credit": 1000, "Memo": "Owner equity contribution"}
|
|
]
|
|
```
|
|
|
|
### Owner Draw
|
|
|
|
```json
|
|
// Transaction
|
|
{"Date": 1768348800, "Description": "Owner draw", "Reference": "Transfer", "Status": "Cleared", "Memo": ""}
|
|
|
|
// TransactionLines (Debit Owner's Draws, Credit Checking)
|
|
[
|
|
{"Transaction": 52, "Account": 24, "Debit": 500, "Credit": 0, "Memo": "Owner draw"},
|
|
{"Transaction": 52, "Account": 14, "Debit": 0, "Credit": 500, "Memo": "Owner draw"}
|
|
]
|
|
```
|
|
|
|
### Direct Expense (No Bill)
|
|
|
|
For bank fees, minor expenses without vendor invoices:
|
|
|
|
```json
|
|
// Transaction
|
|
{"Date": 1768348800, "Description": "Bank service fee", "Reference": "Statement", "Status": "Cleared", "Memo": "Monthly account fee"}
|
|
|
|
// TransactionLines (Debit Expense, Credit Checking)
|
|
[
|
|
{"Transaction": 53, "Account": 30, "Debit": 15, "Credit": 0, "Memo": "Monthly fee"},
|
|
{"Transaction": 53, "Account": 14, "Debit": 0, "Credit": 15, "Memo": "Monthly fee"}
|
|
]
|
|
```
|
|
|
|
### Revenue Receipt
|
|
|
|
```json
|
|
// Transaction
|
|
{"Date": 1768348800, "Description": "Client payment - Project X", "Reference": "INV-100", "Status": "Cleared", "Memo": ""}
|
|
|
|
// TransactionLines (Debit Checking, Credit Revenue)
|
|
[
|
|
{"Transaction": 54, "Account": 14, "Debit": 2500, "Credit": 0, "Memo": "Project X payment"},
|
|
{"Transaction": 54, "Account": 25, "Debit": 0, "Credit": 2500, "Memo": "Service revenue"}
|
|
]
|
|
```
|
|
|
|
### Key Account IDs Reference
|
|
|
|
| ID | Code | Name |
|
|
|----|------|------|
|
|
| 4 | 2000 | Accounts Payable |
|
|
| 14 | 1001 | Checking Account |
|
|
| 22 | 2203 | Due to Owner |
|
|
| 23 | 3001 | Owner's Investment |
|
|
| 24 | 3002 | Owner's Draws |
|
|
| 25 | 4001 | Service Revenue |
|
|
| 26 | 4010 | Interest Income |
|
|
| 30 | 5020 | Bank & Merchant Fees |
|
|
| 36 | 5080 | Software & Subscriptions |
|
|
|
|
## Bank Reconciliation
|
|
|
|
Import bank transactions, match against ledger, create missing entries, and reconcile.
|
|
|
|
For full reference: see [reconciliation.md](reconciliation.md)
|
|
|
|
### Phase 1: Import Bank File (Schwab JSON)
|
|
|
|
```python
|
|
# Read and parse the bank export file
|
|
import json
|
|
with open("path/to/schwab_export.json") as f:
|
|
data = json.load(f)
|
|
|
|
# Parse each PostedTransaction
|
|
for txn in data["PostedTransactions"]:
|
|
# Date: "MM/DD/YYYY" -> Unix timestamp
|
|
# Amount: parse "$X,XXX.XX" strings; Deposit = positive, Withdrawal = negative
|
|
# Empty string = no amount (skip)
|
|
pass
|
|
```
|
|
|
|
### Phase 2: Match Against Ledger
|
|
|
|
```python
|
|
# Fetch existing TransactionLines for Checking (id=14)
|
|
# MUST use get_records, not sql_query (Transaction is reserved word)
|
|
get_records("TransactionLines", filter={"Account": [14]})
|
|
|
|
# Get transaction details for date matching
|
|
sql_query("SELECT id, Date, Description, Status FROM Transactions WHERE id IN (...)")
|
|
|
|
# Match criteria: exact amount AND date ±3 days AND not already matched
|
|
# Deposits match Debit > 0 (money into checking = debit to asset)
|
|
# Withdrawals match Credit > 0 (money out of checking = credit to asset)
|
|
```
|
|
|
|
### Phase 3: Create Missing Transactions
|
|
|
|
```python
|
|
# For unmatched deposits (e.g., interest income):
|
|
add_records("Transactions", [{
|
|
"Date": 1738195200,
|
|
"Description": "Bank interest income",
|
|
"Reference": "Interest Paid",
|
|
"Status": "Cleared",
|
|
"Memo": "Auto-imported from bank statement"
|
|
}])
|
|
# Returns: {"inserted_ids": [txn_id]}
|
|
|
|
# Dr Checking, Cr Interest Income
|
|
add_records("TransactionLines", [
|
|
{"Transaction": txn_id, "Account": 14, "Debit": 0.01, "Credit": 0, "Memo": "Bank interest"},
|
|
{"Transaction": txn_id, "Account": 26, "Debit": 0, "Credit": 0.01, "Memo": "Bank interest"}
|
|
])
|
|
|
|
# For unmatched withdrawals (e.g., ATM owner draw):
|
|
add_records("Transactions", [{
|
|
"Date": 1739750400,
|
|
"Description": "ATM withdrawal - owner draw",
|
|
"Reference": "P421164 88 ESSEX STREET NEW YORK",
|
|
"Status": "Cleared",
|
|
"Memo": "Auto-imported from bank statement"
|
|
}])
|
|
|
|
# Dr Owner's Draws, Cr Checking
|
|
add_records("TransactionLines", [
|
|
{"Transaction": txn_id, "Account": 24, "Debit": 102.00, "Credit": 0, "Memo": "ATM withdrawal"},
|
|
{"Transaction": txn_id, "Account": 14, "Debit": 0, "Credit": 102.00, "Memo": "ATM withdrawal"}
|
|
])
|
|
|
|
# Optionally save a BankRule for auto-categorization:
|
|
add_records("BankRules", [{
|
|
"Account": 14,
|
|
"Pattern": "Interest Paid",
|
|
"MatchType": "Contains",
|
|
"OffsetAccount": 26,
|
|
"TransactionDescription": "Bank interest income",
|
|
"IsActive": true
|
|
}])
|
|
```
|
|
|
|
### Phase 4: Reconcile
|
|
|
|
```python
|
|
# Update matched existing transactions to Cleared status
|
|
update_records("Transactions", [
|
|
{"id": existing_txn_id, "fields": {"Status": "Cleared"}}
|
|
])
|
|
|
|
# Calculate cleared balance
|
|
lines = get_records("TransactionLines", filter={"Account": [14]})
|
|
txn_ids = list(set(l["fields"]["Transaction"] for l in lines))
|
|
cleared = sql_query(f"SELECT id FROM Transactions WHERE Status = 'Cleared' AND id IN (...)")
|
|
cleared_ids = set(r["id"] for r in cleared)
|
|
cleared_balance = sum(
|
|
l["fields"]["Debit"] - l["fields"]["Credit"]
|
|
for l in lines
|
|
if l["fields"]["Transaction"] in cleared_ids
|
|
)
|
|
|
|
# Create Reconciliation record
|
|
add_records("Reconciliations", [{
|
|
"Account": 14,
|
|
"StatementDate": 1739750400, # date of last bank entry
|
|
"StatementBalance": 1398.01,
|
|
"ClearedBalance": cleared_balance,
|
|
"Difference": 1398.01 - cleared_balance,
|
|
"Status": "Completed", # if Difference == 0
|
|
"StartedAt": 1739923200, # today
|
|
"CompletedAt": 1739923200,
|
|
"Notes": "Reconciled against Schwab export"
|
|
}])
|
|
|
|
# Verify
|
|
sql_query("SELECT * FROM Transactions WHERE IsBalanced = false")
|
|
```
|