Accounting Ledger

Build a two-sheet accounting workbook with a transaction ledger and a summary sheet that uses cross-sheet formulas.

The Code

package main

import (
	"fmt"
	"log"
	"os"

	"github.com/jpoz/werkbook"
)

func main() {
	wb := werkbook.New(werkbook.FirstSheet("Ledger"))
	ledger := wb.Sheet("Ledger")

	// Ledger headers
	ledger.SetValue("A1", "Date")
	ledger.SetValue("B1", "Description")
	ledger.SetValue("C1", "Category")
	ledger.SetValue("D1", "Amount")

	// Style headers
	ledger.SetRangeStyle("A1:D1", &werkbook.Style{
		Font: &werkbook.Font{Bold: true, Color: "FFFFFF"},
		Fill: &werkbook.Fill{Color: "2D5F2D"},
	})

	// Transaction data
	txns := []struct {
		date string
		desc string
		cat  string
		amt  float64
	}{
		{"2025-01-02", "Client payment", "Income", 5000.00},
		{"2025-01-05", "Office rent", "Rent", -1500.00},
		{"2025-01-08", "Software licenses", "Software", -299.99},
		{"2025-01-10", "Consulting fee", "Income", 3200.00},
		{"2025-01-12", "Office supplies", "Supplies", -85.50},
		{"2025-01-15", "Client payment", "Income", 4500.00},
		{"2025-01-18", "Internet service", "Utilities", -89.99},
		{"2025-01-20", "Freelancer payment", "Contractors", -1200.00},
		{"2025-01-25", "Client payment", "Income", 6000.00},
		{"2025-01-28", "Equipment", "Equipment", -450.00},
	}

	for i, tx := range txns {
		row := i + 2
		r := fmt.Sprint(row)
		ledger.SetValue("A"+r, tx.date)
		ledger.SetValue("B"+r, tx.desc)
		ledger.SetValue("C"+r, tx.cat)
		ledger.SetValue("D"+r, tx.amt)
	}

	// Format the amount column
	lastRow := len(txns) + 1
	for row := 2; row <= lastRow; row++ {
		ledger.SetStyle("D"+fmt.Sprint(row), &werkbook.Style{
			NumFmt: "$#,##0.00",
		})
	}

	// Add an absolute-value helper column (hidden logic)
	for i, tx := range txns {
		row := i + 2
		if tx.amt < 0 {
			ledger.SetFormula("E"+fmt.Sprint(row),
				fmt.Sprintf("ABS(D%d)", row))
		}
	}

	// Column widths
	ledger.SetColumnWidth("A", 12)
	ledger.SetColumnWidth("B", 22)
	ledger.SetColumnWidth("C", 14)
	ledger.SetColumnWidth("D", 14)

	// --- Summary sheet with cross-sheet formulas ---
	summary, _ := wb.NewSheet("Summary")

	summary.SetValue("A1", "Category")
	summary.SetValue("B1", "Total")
	summary.SetValue("C1", "Count")

	summary.SetRangeStyle("A1:C1", &werkbook.Style{
		Font: &werkbook.Font{Bold: true, Color: "FFFFFF"},
		Fill: &werkbook.Fill{Color: "2D5F2D"},
	})

	// Summarize by category using cross-sheet SUMIF/COUNTIF
	categories := []string{
		"Income", "Rent", "Software",
		"Supplies", "Utilities", "Contractors", "Equipment",
	}

	for i, cat := range categories {
		row := i + 2
		r := fmt.Sprint(row)
		summary.SetValue("A"+r, cat)
		summary.SetFormula("B"+r,
			fmt.Sprintf("SUMIF(Ledger!C2:C%d,\"%s\",Ledger!D2:D%d)",
				lastRow, cat, lastRow))
		summary.SetFormula("C"+r,
			fmt.Sprintf("COUNTIF(Ledger!C2:C%d,\"%s\")",
				lastRow, cat))

		summary.SetStyle("B"+r, &werkbook.Style{NumFmt: "$#,##0.00"})
	}

	// Net total
	netRow := len(categories) + 3
	summary.SetValue("A"+fmt.Sprint(netRow), "Net Total")
	summary.SetFormula("B"+fmt.Sprint(netRow),
		fmt.Sprintf("SUM(Ledger!D2:Ledger!D%d)", lastRow))
	summary.SetStyle("B"+fmt.Sprint(netRow), &werkbook.Style{
		NumFmt: "$#,##0.00",
		Font:   &werkbook.Font{Bold: true, Size: 12},
	})

	summary.SetColumnWidth("A", 14)
	summary.SetColumnWidth("B", 14)

	// Recalculate and preview
	wb.Recalculate()
	fmt.Println("=== Summary ===")
	summary.PrintTo(os.Stdout)

	if err := wb.SaveAs("ledger.xlsx"); err != nil {
		log.Fatal(err)
	}
}

What It Produces

Ledger sheet — raw transaction data:

Summary sheet — aggregated by category:

Key Concepts Used

• Cross-sheet formulas — SUMIF(Ledger!C2:C11,...) and COUNTIF(Ledger!C2:C11,...)
• SUMIF / COUNTIF — conditional aggregation by category
• ABS — computing absolute values for expense tracking
• Recalculate — forcing evaluation before printing with PrintTo
• NumFmt — currency formatting
• SetRangeStyle — consistent header styling across sheets