Simple Agent Designer

Process Excel file with an Agent

Process an Excel Bank Statement

This guide walks you through building an AI agent that reads a bank-statement Excel file and creates General Journal lines in Microsoft Dynamics 365 Business Central using the Simple Agent Designer. We’ll replicate the flow shown in the video: splitting an Excel file into individual lines, applying bookkeeping rules, validating results, and posting to a dedicated journal.

What you’ll need

  • Business Central sandbox (or environment) with Simple Agent Designer installed

  • A configured AI persona (e.g., “Finley”) with general accounting know-how

  • A sample Excel bank statement (one row per transaction)

  • A General Journal batch reserved for AI-generated entries (e.g., EXCEL)

  • Your company’s account numbers (e.g., fees to 67100, payroll to Wages/Payroll, etc.)

Key Concepts (who does what)

  • Initial Agent (Finley): Interprets each transaction and decides what to create.

  • Splitters (Excel Splitter): Turn one uploaded Excel file into many “child” workloads—one per row.

  • Helpers (Kenzie, Lot/L): Look up Business Central data (e.g., customers, bank accounts) and create journal lines.

  • Auditor (Wisely): Reviews the proposed journal entry against accounting rules before you mark the task complete.

Step 1 — Define the Workload Type

  1. Open Simple Agent Designer → Workload Types → New.

  2. Name it (e.g., “Excel file”).

  3. Destination:

    • Target Area: General Journal

    • Journal Batch: your AI batch (e.g., EXCEL)

    • Number Series: choose your general journal posting series

  4. (Optional) Filters: Restrict what the agent can touch (e.g., allowed G/L accounts, items, bank accounts).

  5. Splitter: Select Excel Splitter.

    • This converts a single Excel with 7 rows into 7 separate workloads—cheaper, faster, more reliable.

  6. Initial Agent: Choose Finley (the accountant agent).

Why split? It reduces confusion with large files, improves cost efficiency, and makes errors easier to isolate and fix.

Step 2 — Write Clear, Compact Instructions

Think of this like onboarding a temp in your accounting team. Keep it specific and action-oriented. Here’s a starter block you can paste and adapt:

Purpose
You are given a bank statement one line at a time from an Excel file. For each line, create exactly one General Journal line in the EXCEL batch.

Posting rules

  • Bank fees (anything that looks like a fee): Debit 67100 (Bank Fees).

  • Stripe payouts: Credit Stripe bank account, Debit Checking.

  • Payroll deposits: Post to the Wages/Payroll account (use our standard payroll G/L).

  • Customer wire payments: Descriptions often start with TT1/CustomerName.

    • Extract the customer name after TT1/.

    • Find the customer and apply to an open invoice if there’s a clear match; otherwise record as payment without application.

Data lookups & validations

  • Bank account code for this file is CHECKING, not BANK001.

  • If a referenced account/bank doesn’t exist, ask Kenzie to resolve the correct entity.

Output

  • Post a single, balanced journal line per transaction with correct date, description, amount, and accounts.

  • After creating the line, send to Wisely for audit. If Wisely flags an issue, fix and resubmit.

Tip: Keep your instruction block short (5–12 bullet rules). Add only what’s needed for this file’s transaction types.

Step 3 — Test with a Debug Run

  1. Go to Workloads → New Workload.

  2. Pick the Workload Type (e.g., Excel file).

  3. Upload your Excel.

  4. Click Debug Run (instead of “Process”) to step through decisions:

    • Split shows how many child workloads were created (e.g., 7 lines → 7 children).

    • Open a child workload (e.g., a $5 fee) and run Next Step to see Finley’s reasoning.

    • If a helper says something like “balance account doesn’t exist,” update your instructions (e.g., bank code is CHECKING, not BANK001) and retry.

    • Finley will hand off to Lot/L to create the journal line, then to Wisely for an audit check.

  5. Confirm Status = Success, and note the Document No. and posted details.

Step 4 — Iterate Quickly

  • If Finley repeatedly guesses an incorrect bank account (e.g., BANK001), add a rule:
    “Do not assume the bank account. For this workload, use CHECKING.”

  • If customer wires contain a naming pattern (e.g., TT1/AcmeCo), codify it:

    • “For TT1/<Name>, treat <Name> as the customer.”

  • If you want to limit scope, add Filters on the Workload Type (allowed accounts, bank codes, etc.)

Small edits → Save → Debug Run again. You’re training a process, not a model—fast, safe, reversible.

Example: How the 7-Line Statement Is Handled

  • Bank fee $5 → Debit 67100, Credit Checking; date from the row → Post → Audit = OK

  • Stripe payout → Credit Stripe bank, Debit Checking → Post → Audit = OK

  • Customer wire “TT1/Adatum” → Identify Adatum → check open entries → apply if clear match; else record unapplied → Post → Audit = OK

  • Payroll deposit → Post to Wages/Payroll per chart → Post → Audit = OK

Each line remains isolated as a child workload. One failure doesn’t block the rest.

Troubleshooting & Tips

  • “Balance account doesn’t exist”

    • Add/verify the Bank Account in BC; update instructions to reference the correct code (e.g., CHECKING).

  • Wrong accounts used

    • Lock it down with Filters and make the instruction explicit (e.g., “Fees → 67100 only”).

  • Customer name parsing issues

    • Add a pattern rule (e.g., “After TT1/, text until next space is the customer name”).

  • Too many rows

    • You’re covered—the Excel Splitter scales to thousands of lines by creating one workload per row.

  • Audit fails

    • Read Wisely’s feedback, refine the rule, re-run the child workload.

Best Practices

  • Dedicated journal batch (e.g., EXCEL) for traceability.

  • Short, plain-English rules → easier to audit and maintain.

  • Use Debug Run for first passes; switch to Process once stable.

  • Name helpers clearly (Finley = accountant; Kenzie = data lookup; Wisely = auditor; Lot/L = journal posting).

  • Version your instruction block (add a date or v# in the first line).

FAQ

Q: Can I restrict what the agent can post?
Yes—use Filters on the Workload Type (allowed G/Ls, bank accounts, etc.).

Q: Do I need to change my AI persona for each file?
No. Keep Finley generic (“accountant”), and use the Workload Type instructions to tailor behavior per file.

Q: What if there’s no invoice to apply a customer payment to?
In your instructions: “If no open invoice is found, record the payment without application.”

Q: Can the agent handle mixed transactions in one file?
Yes. The Excel Splitter isolates each row; your rules map patterns (fees, Stripe, payroll, customer wires) to the right postings.

Quick Checklist

  • Workload Type points to General Journal → EXCEL batch → Number Series set

  • Excel Splitter enabled

  • Finley selected as Initial Agent

  • Concise instructions added (fees→67100, Stripe mapping, payroll, TT1/ customer parsing, bank code=CHECKING)

  • Optional Filters to limit scope

  • Debug Run a few child workloads, iterate rules

  • When green: Process the full file

That’s it! With a tight instruction block and the Excel Splitter, you’ll turn messy bank files into clean, auditable journal entries—one line at a time.