> ## Documentation Index > Fetch the complete documentation index at: https://docs.ndi.nace.ai/llms.txt > Use this file to discover all available pages before exploring further. # Ledger and report recipes > Counterparty from a GL description, signed trial-balance amounts, and a printed report total Three requests that look like new features are answered by the tools you already have. Each recipe below was run on an ingested workspace holding the workbooks it names; the outputs are trimmed copies of the real responses. | You want | Use | Why | | - | - | - | | A counterparty for each ledger line, quoted from its description | [Extract](https://docs.ndi.nace.ai/guides/extract.md) | Telling a party from a place or a date takes reading, not a pattern | | A signed trial-balance amount and the rule behind it | [Run SQL](https://docs.ndi.nace.ai/guides/file-tools.md#run-sql) | The sign rule is arithmetic; the statement is the rule | | The printed TOTAL row of a report, next to the lines | [Extract](https://docs.ndi.nace.ai/guides/extract.md) | A document-level field with its own citation | Upload and [ingest](https://docs.ndi.nace.ai/guides/choose-a-tool.md#before-you-ask) the files first. Extract reads a workspace file by `file_id` (`GET /v1/workspaces/{id}/files`). Run SQL reads the table names that [file metadata](https://docs.ndi.nace.ai/guides/file-tools.md) lists for the file. ## Counterparty for each GL line A general ledger sheet with `Document Number` and `Document Description` columns and no counterparty column. The description names the party after a dash (`Vendor payment — Lantern Label Company — BILL-2025-0002`), but a dash also introduces a place (`Warehouse and office rent — Los Angeles and Commerce`) or a date. Ask for the party and allow `null`: ```json theme={"dark"} { "type": "object", "properties": { "gl_lines": { "type": "array", "description": "One entry per posted line of the General Ledger sheet. Skip account headers, Opening Balance and Closing Balance rows.", "items": { "type": "object", "properties": { "document_number": {"type": "string"}, "document_description": {"type": "string"}, "counterparty": { "type": ["string", "null"], "description": "The customer, vendor or other party named in the line's Document Description, copied verbatim from that text. Null when the description names no party. Never infer from outside the description." } }, "required": ["document_number", "document_description", "counterparty"] } } }, "required": ["gl_lines"] } ``` ```python theme={"dark"} from ndi_sdk import NdiClient, WorkspaceFileSource with NdiClient() as client: source = WorkspaceFileSource( workspace_id="550e8400-e29b-41d4-a716-446655440001", file_id="550e8400-e29b-41d4-a716-446655440004", ) job = client.jobs.wait( client.documents.extract(source, json_schema=gl_counterparty_schema).job_id, timeout=900, ) for line in job.result.data["gl_lines"]: print(line["document_number"], line["counterparty"]) ``` Result on `funky_town_general_ledger_2025.xlsx` (671 posted lines, about 2.5 minutes, 39 credits): ```json theme={"dark"} [ {"document_number": "RENT-2025-01", "document_description": "Warehouse and office rent — Los Angeles and Commerce", "counterparty": null}, {"document_number": "BP-OPEN-AP-01", "document_description": "Payment of opening payable — Sumac Carton Supply", "counterparty": "Sumac Carton Supply"}, {"document_number": "PMT-38290", "document_description": "Customer receipt — Harbor & Pine Retail Group, Inc. — Invoice I38290-1", "counterparty": "Harbor & Pine Retail Group, Inc."}, {"document_number": "INV-2025-0001", "document_description": "Invoice INV-2025-0001 — apparel shipment — Fieldnote Outfitters", "counterparty": "Fieldnote Outfitters"} ] ``` All 671 lines came back in sheet order, and every non-null `counterparty` is a substring of its own `document_description`. 434 lines name a party; the other 237 (rent, payroll, depreciation, interest, closing entries) return `null`. Check the quote yourself with `counterparty in document_description`. * **Citations.** `counterparty` is quoted from the description cell, so the line's row is its citation. In the run above only the first 20 of the 671 `/gl_lines/N` fields carried a citation (a `sheet_range` such as `A7:I7` with the row's text); the rest had none. To find any line's row, look its `document_number` up with [Run SQL](https://docs.ndi.nace.ai/guides/file-tools.md#run-sql) on the ledger's table. * **Long ledgers.** One call covered 671 lines. For a ledger much longer than that, split the request by account range in `instructions` (a spreadsheet has no pages, so `page_ranges` does not apply) and compare the line count with the sheet's. ## Signed trial balance with the rule A trial balance that prints natural signs: assets and expenses positive when debit, liabilities, equity and revenue positive when credit, contra balances negative. Run SQL turns that into a debit-positive amount and returns the rule on each row. The account type comes from the account number here (1xxx asset, 2xxx liability, 3xxx equity, 4xxx revenue, 5xxx and up expense); change the `CASE` ranges if the chart of accounts differs. ```bash theme={"dark"} curl -s -X POST "$NDI_BASE_URL/v1/workspaces/$WS/tools/run-sql" \ -H "X-API-Key: $NDI_API_KEY" \ -H "Content-Type: application/json" \ -d '{"sql":"SELECT CAST(\"Account\" AS INTEGER) AS account, \"Account Name\" AS name, \"Closing\" AS printed_closing, CASE WHEN \"Account\" < 2000 THEN '"'"'asset'"'"' WHEN \"Account\" < 3000 THEN '"'"'liability'"'"' WHEN \"Account\" < 4000 THEN '"'"'equity'"'"' WHEN \"Account\" < 5000 THEN '"'"'revenue'"'"' ELSE '"'"'expense'"'"' END AS account_type, CASE WHEN \"Account\" < 2000 OR \"Account\" >= 5000 THEN '"'"'debit-natural: signed = printed'"'"' ELSE '"'"'credit-natural: signed = -printed'"'"' END AS rule, CASE WHEN \"Account\" < 2000 OR \"Account\" >= 5000 THEN \"Closing\" ELSE -\"Closing\" END AS signed_closing_debit_positive FROM funky_town_trial_balance_2025_sheet1 WHERE \"Account\" IS NOT NULL ORDER BY \"Account\""}' ``` Result on `funky_town_trial_balance_2025.xlsx` (26 accounts): | account | name | printed\_closing | account\_type | signed\_closing\_debit\_positive | | - | - | - | - | - | | 1000 | Cash & Cash Equivalents | 285106.1 | asset | 285106.1 | | 1105 | Allowance for Credit Losses (Trade AR) | -1830.04 | asset | -1830.04 | | 2000 | Accounts Payable | 16055.48 | liability | -16055.48 | | 3300 | Retained Earnings / Accumulated Deficit | 473594.47 | equity | -473594.47 | `rule` reads `debit-natural: signed = printed` for the asset rows and `credit-natural: signed = -printed` for the liability and equity rows. Check the result with a second statement over the returned handle: ```bash theme={"dark"} curl -s -X POST "$NDI_BASE_URL/v1/workspaces/$WS/tools/run-sql" \ -H "X-API-Key: $NDI_API_KEY" \ -H "Content-Type: application/json" \ -d '{"sql":"SELECT round(sum(signed_closing_debit_positive), 2) AS tb_sum FROM res_316297c88977"}' ``` `tb_sum` was `0.0` over all 26 accounts: a signed trial balance nets to zero. Replace `res_316297c88977` with the `result.handle` your first call returned. A sum that is not zero means a sign or an account type is wrong. * **Citations.** `source_paths` names the file, and `result_citation.result_file.download_url` fetches the whole result as parquet. To point at the printed account line, give the account number and name from the result. * **Movement columns.** This file also prints `Debits` and `Credits` movement columns. They are not the sign of `Closing`; the recipe signs the closing balance from the account type. * **The sheet's own convention.** The statement does not read the sign convention the sheet prints in its header. If that line differs from the rule above, change the rule. ## Printed total as a document field A report whose last row is a printed TOTAL. Declare the lines and the total as separate fields, and say the TOTAL row is not a line: ```json theme={"dark"} { "type": "object", "properties": { "lines": { "type": "array", "description": "One entry per invoice row. Exclude the printed TOTAL row.", "items": { "type": "object", "properties": { "customer": {"type": "string"}, "invoice": {"type": "string"}, "bucket": {"type": "string"}, "open_balance": {"type": "number"} }, "required": ["customer", "invoice", "open_balance"] } }, "document_total": { "type": ["number", "null"], "description": "The amount printed on the report's TOTAL row, copied as printed. Null if no TOTAL row is printed." } }, "required": ["lines", "document_total"] } ``` ```python theme={"dark"} from ndi_sdk import NdiClient, WorkspaceFileSource with NdiClient() as client: source = WorkspaceFileSource( workspace_id="550e8400-e29b-41d4-a716-446655440001", file_id="550e8400-e29b-41d4-a716-446655440004", ) job = client.jobs.wait( client.documents.extract( source, json_schema=report_total_schema, instructions="The TOTAL row is a printed report total, not an invoice line: return it only as document_total and never as an entry of lines.", ).job_id ) print(job.result.data["document_total"]) total = next(f for f in job.result.fields if f.path == "/document_total") print(total.citations[0].location, total.citations[0].source_text) ``` Result on `AR_Aging_2025-12-31.xlsx` (3 credits): ```json theme={"dark"} { "data": { "lines": [ {"customer": "Boulder Climb Supply", "invoice": "RO-1023", "bucket": "Current", "open_balance": 6435} ], "document_total": 23282.5 }, "fields": [ { "path": "/document_total", "value": 23282.5, "status": "found", "citations": [ { "location": {"kind": "sheet_range", "sheet": "AR Aging", "a1_range": "A4:F4"}, "source_text": "TOTAL | | | | | 23,282.50" } ] } ] } ``` The total is in `document_total`, the invoice row is the only entry in `lines`, and the total's citation points at the TOTAL row (`A4:F4`). The printed total is not recomputed: compare `document_total` with the sum of `lines[].open_balance` and treat a difference as a finding about the report. In this file the two differ (23,282.50 against 6,435), because the printed report holds one invoice row under a larger total. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.