Reconcile general ledger detail to the trial balance with an Excel AI agent
What the source describes
A Journal of Accountancy article by two University of Akron accounting professors shows how they built an agent in ChatGPT for Excel, with a custom skill, to clean messy general ledger data and reconcile it to the trial balance. The skill makes the agent work from a copy, inspect before acting, present a plan for approval, use native Excel formulas, log every change, and aggregate the cleaned ledger by account to flag non-zero differences against the trial balance. The authors stress human validation, including formula tracing and spot checks.
Result or outcome
According to the source, in the authors' simulation, cleaning, validating and reconciling 59,157 rows of transaction-level general ledger data to the trial balance took about 10 minutes on average, a task they say would take many hours manually.
How to run it
- Give your Excel AI agent a written skill telling it to work from a copy, inspect first, present a plan, use native formulas, and log changes.
- Put the general ledger detail export and the trial balance on separate sheets in one workbook, keeping the raw export untouched.
- Paste the prompt on this page into the agent, review the cleaning plan it proposes, and approve it only once the rules fit your file.
- Let the agent clean the data, sum the ledger by account, compare it to trial balance ending balances, and flag non-zero differences.
- Trace formulas for high-volume accounts back to source, spot check rows, read the Modifications_Log, and resolve rounding or other differences.
Try it yourself
Copyable prompt
Act as a careful close assistant in Excel. Sheet [GL sheet name] holds the general ledger detail export for [period] and sheet [TB sheet name] holds the trial balance. First, inspect the GL for blank rows, repeated headers, text-formatted numbers and inconsistent dates, and show me a cleaning plan; wait for my approval. After approval, work on a copy, clean the data with Excel formulas only, and log every change on a Modifications_Log sheet. Then sum the GL by account number, compare to the trial balance ending balances, and list every account with a non-zero difference for follow-up.
Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.
What to review
Confirm every account on the trial balance appears in the aggregated ledger and that each difference is truly zero, not hidden by rounding. Trace the agent's formulas to the right sheets and cells, and read the change log for deleted rows that were real transactions. The authors say these tools should run under enterprise licenses for audit work, so follow your firm's privacy and data policies.
Related free download: Month-End Close Checklist for Small Business Bookkeepers (Printable checklist (PDF))
Source: Journal of Accountancy (AICPA magazine)
Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.
This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.
Related workflows
- Month-End Close
Track period-close status with Sage Copilot Close Assistant in Sage Intacct
Sage Intacct's help documentation explains that Close Assistant, part of Sage Intacct Close Automation, shows which applications are closed and which tasks need attention in the Sage Copilot panel. You open Copilot, select Run Close Assistant, pick the reporting period, and it lists tasks such as reviewing unposted bills and invoices, preparing AP and AR reconciliations, reviewing bank reconciliations, reviewing unposted journal entries and running variance analysis, with links to close each subledger and the general ledger.
Sage Intacct, Sage Copilot +1
Sage Intacct Help Center - Receipts and Expenses
Chase Clients About Uncoded Transactions with an AI-Drafted Email
The Firm's guide to ChatGPT prompts for bookkeepers argues that bank feeds and ledger rules already handle matching, so chat assistants are most useful for the writing around the ledger, starting with the email asking a client to explain transactions the bookkeeper cannot code. It recommends grouping similar items, numbering questions, requesting receipts, and following up with a shorter, firmer second chase. The article also says to strip names, tax identifiers and account numbers before pasting, use a firm-approved tier, and never let the assistant decide tax treatment or code from memory.
Works in ChatGPT, Claude, Grok, Gemini
The Firm (accounting publication) - Reporting and Analysis
Build Horizontal, Trend and Ratio Analysis in Excel with AI
A Journal of Accountancy technology Q&A shows how ChatGPT-4o can create Excel spreadsheets from scratch and analyze uploaded files. In the financial analysis example, the author pasted raw financial data and ChatGPT returned a workbook with tabs for Raw Financials, Horizontal Analysis, Trend Analysis and Ratio Analysis. The author advises being specific in the prompt if you want working formulas rather than values.
Works in ChatGPT, Claude, Grok, Gemini
Microsoft Excel
Journal of Accountancy - Receipts and Expenses
Reclassify Miscoded Expenses in Excel with AI
In this Journal of Accountancy Tech Q&A, a CPA and accounting professor shows an accountant uploading a journal entry log where every expense was coded to Office Supplies. ChatGPT scans the Description column for keywords like laptop and monitor and builds a formula that adds a Reclassified Account column, for example moving those items to Computer Equipment. The author stresses checking the AI's work and never entering client or confidential information into ChatGPT.
Works in ChatGPT, Claude, Grok, Gemini
Microsoft Excel
Journal of Accountancy