5 Copy-and-Paste AI Prompts to Clean Up Messy Bookkeeping Spreadsheets

You are a meticulous bookkeeping assistant.

Below is a CSV of bank transactions with columns: date, description, amount.

For each row, return a new column "vendor" with the cleaned, canonical vendor name.

Rules:
1. Strip transaction IDs, store numbers, and city codes.
2. Collapse known variants (e.g. "AMZN Mktp", "Amazon.com*A1B2C" → "Amazon").
3. Title-case the result.
4. If unclear, set vendor to "Unknown" — do not guess.

Return ONLY a CSV with columns: date, description, amount, vendor.

Transactions:
{{PASTE_CSV}}

Reformat the CSV below.

Rules:
- Convert every date to ISO 8601 (YYYY-MM-DD). Assume US format (M/D/Y) when ambiguous.
- Convert every amount to a signed number with 2 decimals. Debits negative, credits positive.
- Remove currency symbols and thousands separators.
- Drop any row where date or amount cannot be parsed, and list those rows separately under a "## Skipped" heading.

Return the cleaned CSV first, then the skipped rows.

Data:
{{PASTE_CSV}}

You are a bookkeeping assistant for a sole trader.

Assign each transaction below to ONE category from this fixed list:
Software, Marketing, Travel, Meals, Office, Contractors, Bank Fees, Personal, Income, Uncategorized.

Rules:
1. Use "Personal" for anything that looks non-business (groceries, Netflix, etc.).
2. Use "Uncategorized" only when truly unclear — do not guess.
3. Add a "confidence" column: high, medium, low.

Return CSV with columns: date, vendor, amount, category, confidence.

Data:
{{PASTE_CSV}}

Review the CSV below for data hygiene issues.

Flag rows that match ANY of these patterns:
- Exact duplicate (same date, vendor, amount).
- Near-duplicate (same vendor + amount within 2 days).
- Round-number amounts over $500 with vague descriptions.
- Negative amounts in an income category, or positive amounts in an expense category.

Return a markdown table with columns: row_number, issue_type, reason, suggested_action.

Data:
{{PASTE_CSV}}

Below is a cleaned, categorized CSV of business transactions for {{MONTH_YEAR}}.

Produce a concise month-end summary with:
1. Total income, total expenses, net profit.
2. Top 5 expense categories by dollar amount.
3. Top 3 vendors by spend.
4. Any month-over-month anomalies if I provide a prior month for comparison.
5. A 2-sentence plain-English commentary I could send to my accountant.

Format the response in clean markdown with headings.

Data:
{{PASTE_CSV}}