---
source: https://carbone.io/examples/transaction-report/
title: "Transaction Report Template in Excel with Balance | Carbone"
description: "Generate an account transaction report in Excel from JSON with the Carbone API: a native table with a running balance, filters, totals and colored amounts."
generated_at: "2026-11-17"
---

# Excel transaction report

A bank account's entries, balances and totals in a live table

[![Excel transaction report: XLSX generated from an XLSX template and JSON data](/examples/transaction-report/thumb-result.webp)](/examples/transaction-report/result.xlsx "Download the generated document")

[Download template](/examples/transaction-report/template.xlsx)

[Sample data](/examples/transaction-report/data.json)

[Download the generated XLSX](/examples/transaction-report/result.xlsx)

- **Template:** XLSX
- **Export as:** XLSX, PDF, ODS, CSV, HTML [+19 more](/documentation/developer/http-api/generate-reports.md#output-file-type)
- **Carbone:** 5.18+

Features

-   [Loop](/documentation/design/repetitions/with-arrays.md)
-   [Excel table](/documentation/design/template-formats/xlsx.md)
-   [Formula](/documentation/design/template-formats/xlsx.md)
-   [Subtotal](/documentation/design/computation/aggregation.md)
-   [Conditional formatting](/documentation/design/template-formats/xlsx.md)
-   [Date](/documentation/design/formatters/date.md)
-   [Number format](/documentation/design/formatters/number.md)

## Generate account transaction reports from your accounting or banking app

Finance teams and accounting tools send the month's bank transactions to the Carbone API as JSON and get back the report as a native Excel file.

Here, an XLSX template fills an Excel table with the transactions of the month. Carbone extends the table to the generated rows, so the banded rows, the filter buttons and the total row apply to all the transactions. The generated XLSX stays a live workbook: the balance of each row, the totals and the closing balance are Excel formulas, so they update when you change an amount.

In this example, you will:

-   Display the account, the period and a statement summary in a header block
-   List the transactions in a loop inside an Excel table
-   Compute a running balance, the totals and the closing balance with Excel formulas
-   Color the credits and the debits with conditional formatting

## How to build a transaction report in Excel

Follow these steps to create your template:

### 1\. Create the Report Header

Display the report information at the top with [Carbone tags](/documentation/design/substitutions/the-basics.md) and [:formatD](/documentation/design/formatters/date.md#formatd-patternout-patternin):

```text
A1: {d.company}
A2: {d.account.name} · {d.account.number} · {d.account.currency}
A3: Period: from {d.period.from:formatD(LL)} to {d.period.to:formatD(LL)}
A4: Opening balance          C4: {d.openingBalance:formatN}
A5: Total credits            C5: =SUBTOTAL(109,Table1[Credit])
A6: Total debits             C6: =SUBTOTAL(109,Table1[Debit])
A7: Closing balance          C7: =C4+C5-C6
```

_Tip: In XLSX, [:formatN](/documentation/design/formatters/number.md#formatn-precision-options) writes real numbers, so the Excel number format of the cell applies (here `[$-409]$#,##0.00;[$-409]-$#,##0.00` to display US dollars with US separators)._

### 2\. List the Transactions

-   In row 9, type the column labels Date, Reference, Counterparty, Description, Category, Credit, Debit and Balance.
-   In the first row of the table, display the transactions in a [loop](/documentation/design/repetitions/with-arrays.md#simple-array): `{d.lines[i].date:formatD(L)}`, `{d.lines[i].reference}`, `{d.lines[i].counterparty}`, `{d.lines[i].description}`, `{d.lines[i].category}`, `{d.lines[i].credit:formatN}` and `{d.lines[i].debit:formatN}`
-   In the second row, define the iteration: `{d.lines[i+1]}`
-   Select the header and the two loop rows, then choose **Insert > Table** and check **My table has headers**. In the **Table Design** tab, choose a table style and check **Banded Rows** and **Total Row**.

The amounts are single `:formatN` tags, so Excel reads them as numbers. A transaction has either a credit (money in) or a debit (money out): the empty value leaves its cell blank.

### 3\. Compute the Running Balance

In the Balance column of the first row, type an Excel formula that adds the credit and subtracts the debit to the balance of the previous row:

```text
H10: =IF(ROW()=ROW($A$9)+1,$C$4,H9)+F10-G10
```

On the first transaction, the formula starts from the opening balance in `$C$4`. Carbone copies the formula to every generated row, and each row points to the row above, as Excel does when you insert rows.

### 4\. Calculate the Totals

-   In the total row, type **Total** in the first column and choose **Sum** in the Credit and Debit columns. Excel writes the formulas `=SUBTOTAL(109,Table1[Credit])` and `=SUBTOTAL(109,Table1[Debit])`.
-   In the Balance column of the total row, type `=$C$4+SUBTOTAL(109,Table1[Credit])-SUBTOTAL(109,Table1[Debit])`: the closing balance equals the opening balance plus the credits minus the debits, as in the summary of a bank statement, and the last running balance matches it.
-   Carbone extends the table range to all the generated rows, so the structured references `Table1[Credit]` and `Table1[Debit]` cover every transaction.

### 5\. Color the Amounts

Select the cells of the first loop row in the Credit column, then choose **Conditional Formatting > Highlight Cells Rules > Greater Than**, enter `0` and pick a green text color. Do the same in the Debit column with a red text color, and in the Balance column with a red text color for the values **Less Than** `0`. Carbone extends the rules to all the generated rows.

> This template requires Carbone v5.18.0 or later. Add the tag `{o.preReleaseFeatureIn=5018000}` to the template to enable the Excel table, formula and conditional formatting support. Here, the tag is stored in the comments of the document properties.

Save the XLSX template, connect it to your JSON data, and let Carbone generate your **transaction report** automatically.

## Related Examples

Check out these similar templates to learn more:

- [Quarterly financial dashboard](/examples/s-financial-dashboard/index.md): XLSX template to XLSX.
- [Checking account statement](/examples/bank-statement-expert/index.md): DOCX template to PDF.

## Related topics

- [Accounts receivable aging report](/examples/accounts-receivable-aging-report/index.md)
- [Bank reconciliation report](/examples/bank-reconciliation-report/index.md)
- [Sales commission statement](/examples/sales-commission-statement/index.md)
- [Budget vs actual report](/examples/budget-vs-actual-report/index.md)
- [13-week cash flow forecast](/examples/cash-flow-forecast-13-week/index.md)
- [Fixed asset register](/examples/fixed-asset-register/index.md)
