API tools faq
paste
J2897

GnuCash Accounts CSV Book Builder

J2897
Feb 11th, 2026
190
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 33.40 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/env python3
  2. """
  3. GnuCash Accounts CSV → Book Builder
  4.  
  5. Build a new GnuCash XML book (*.gnucash) from a “chart of accounts” (account tree) CSV.
  6.  
  7. Outputs
  8. - A new book containing your full account hierarchy (a ROOT account is created automatically).
  9. - All commodities/securities referenced by the CSV (currencies, crypto-assets, precious metals, etc.).
  10. - Optional account attributes stored as slots: Placeholder, Hidden, Tax related, Notes, and Colour.
  11.  
  12. Deterministic by default
  13. - Account GUIDs are stable: UUIDv5 derived from each account’s full path, so rebuilding produces the
  14.  same IDs and git-friendly diffs.
  15. - Use --random-guids to generate new GUIDs on each run.
  16.  
  17. Usage
  18.  build  <accounts.csv> --out <book.gnucash> [options]
  19.  verify <book.gnucash> <accounts.csv> [options]
  20.  
  21. CSV format (header must match exactly)
  22.  Type, Full Account Name, Account Name, Account Code, Description, Account Colour, Notes,
  23.  Symbol, Namespace, Hidden, Tax Info, Placeholder
  24.  
  25. Commodity rules enforced
  26. - ISO 4217 currencies (GBP, USD, EUR, …) must use Namespace=CURRENCY.
  27. - ISO precious metal codes are treated as securities (not currencies) to avoid currency-style behaviour:
  28.  XAG/XAU/XPT/XPD → METALS:SILVER/GOLD/PLATINUM/PALLADIUM
  29. - Defaults are provided for common crypto-assets and metals (names and display fractions). Override any
  30.  non-currency fraction with:
  31.    --commodity-fraction NAMESPACE:SYMBOL=FRACTION
  32.  
  33. GnuCash compatibility notes
  34. - The internal template commodity (template:template) is created by default; disable with
  35.  --no-template-commodity.
  36. - Hidden and Tax Info are stored via account slots and round-tripped so strict verify can compare results.
  37.  
  38. Getting started
  39. - Begin with a small CSV: your top-level accounts plus at least one CURRENCY row (e.g. GBP/CURRENCY),
  40.  then run:
  41.    python gnucash_accounts_csv_book_builder.py build accounts.csv --out new_book.gnucash
  42. """
  43.  
  44. from __future__ import annotations
  45.  
  46. import argparse
  47. import sys
  48. import uuid
  49. from dataclasses import dataclass
  50. from pathlib import Path
  51. from typing import Any
  52.  
  53. import pandas as pd
  54. import pycountry
  55. import xml.etree.ElementTree as ET
  56.  
  57.  
  58. EXPECTED_COLUMNS: list[str] = [
  59.     "Type",
  60.     "Full Account Name",
  61.     "Account Name",
  62.     "Account Code",
  63.     "Description",
  64.     "Account Colour",
  65.     "Notes",
  66.     "Symbol",
  67.     "Namespace",
  68.     "Hidden",
  69.     "Tax Info",
  70.     "Placeholder",
  71. ]
  72.  
  73.  
  74. GN_ACCOUNT_TYPES: set[str] = {
  75.     "ASSET", "BANK", "CASH", "STOCK", "MUTUAL",
  76.     "LIABILITY", "CREDIT", "EQUITY",
  77.     "INCOME", "EXPENSE",
  78.     "RECEIVABLE", "PAYABLE",
  79.     "TRADING",
  80. }
  81.  
  82.  
  83. # Defaults for common securities (crypto + precious metals) precisions
  84. DEFAULT_SECURITY_FRACTIONS: dict[str, int] = {
  85.     # Crypto
  86.     "BTC": 100_000_000,
  87.     "ETH": 100_000_000,
  88.     "BNB": 100_000_000,
  89.     "ADA": 1_000_000,
  90.     "USDT": 1_000_000,
  91.     "USDC": 1_000_000,
  92.  
  93.     # Precious metals (treat as normal securities, units = ounces)
  94.     "SILVER": 1_000,
  95.     "GOLD": 1_000,
  96.     "PLATINUM": 1_000,
  97.     "PALLADIUM": 1_000,
  98. }
  99.  
  100. DEFAULT_SECURITY_NAMES: dict[str, str] = {
  101.     "BTC": "Bitcoin",
  102.     "ETH": "Ethereum",
  103.     "BNB": "BNB",
  104.     "ADA": "Cardano",
  105.     "USDT": "Tether",
  106.     "USDC": "USD Coin",
  107.  
  108.     "SILVER": "Silver",
  109.     "GOLD": "Gold",
  110.     "PLATINUM": "Platinum",
  111.     "PALLADIUM": "Palladium",
  112. }
  113.  
  114.  
  115. # Precious metals: avoid treating ISO metal codes (XAG/XAU/XPT/XPD) as CURRENCY,
  116. # because GnuCash handles CURRENCY commodities as currencies/exchange rates, which
  117. # makes bullion "buy 1 oz for £44" style entries awkward. We normalise these to
  118. # METALS:<NAME> securities instead.
  119. PRECIOUS_METALS_NAMESPACE = "METALS"
  120. PRECIOUS_METAL_ISO_TO_SYMBOL: dict[str, str] = {
  121.     "XAG": "SILVER",
  122.     "XAU": "GOLD",
  123.     "XPT": "PLATINUM",
  124.     "XPD": "PALLADIUM",
  125. }
  126.  
  127.  
  128.  
  129. def die(msg: str, *, code: int = 2) -> "NoReturn":
  130.     print(f"ERROR: {msg}", file=sys.stderr)
  131.     raise SystemExit(code)
  132.  
  133.  
  134. def load_iso_currency_codes() -> set[str]:
  135.     codes: set[str] = set()
  136.     for c in pycountry.currencies:
  137.         a3 = getattr(c, "alpha_3", None)
  138.         if a3:
  139.             codes.add(str(a3).upper())
  140.     return codes
  141.  
  142.  
  143. ISO_CURRENCY_CODES = load_iso_currency_codes()
  144.  
  145.  
  146. def _norm_bool_tf(val: Any) -> str:
  147.     s = str(val).strip().upper()
  148.     if s in {"T", "TRUE", "1", "YES", "Y"}:
  149.         return "T"
  150.     if s in {"F", "FALSE", "0", "NO", "N", ""}:
  151.         return "F"
  152.     die(f"Invalid boolean flag value: {val!r} (expected T/F)")
  153.  
  154.  
  155. def normalize_account_type(t: str) -> str:
  156.     t = str(t).strip().upper()
  157.     return {
  158.         "CREDITCARD": "CREDIT",
  159.         "CREDIT_CARD": "CREDIT",
  160.         "A/RECEIVABLE": "RECEIVABLE",
  161.         "A/RECEIVABLES": "RECEIVABLE",
  162.         "RECEIVABLES": "RECEIVABLE",
  163.         "A/PAYABLE": "PAYABLE",
  164.         "A/PAYABLES": "PAYABLE",
  165.         "PAYABLES": "PAYABLE",
  166.     }.get(t, t)
  167.  
  168.  
  169. @dataclass(frozen=True)
  170. class CommodityKey:
  171.     namespace: str
  172.     symbol: str
  173.  
  174.     def __post_init__(self) -> None:
  175.         ns = str(self.namespace).strip()
  176.         sym = str(self.symbol).strip()
  177.  
  178.         # v3: preserve template commodity EXACTLY as lowercase
  179.         if ns.lower() == "template" and sym.lower() == "template":
  180.             ns_out, sym_out = "template", "template"
  181.         else:
  182.             ns_out, sym_out = ns.upper(), sym.upper()
  183.  
  184.         object.__setattr__(self, "namespace", ns_out)
  185.         object.__setattr__(self, "symbol", sym_out)
  186.  
  187.  
  188. def read_accounts_csv(path: Path) -> pd.DataFrame:
  189.     df = pd.read_csv(path, dtype=str, keep_default_na=False).fillna("")
  190.     missing = [c for c in EXPECTED_COLUMNS if c not in df.columns]
  191.     if missing:
  192.         die(f"CSV is missing required columns: {missing}")
  193.  
  194.     df = df[EXPECTED_COLUMNS].copy()
  195.     df["Type"] = df["Type"].map(normalize_account_type)
  196.  
  197.     for col in [
  198.         "Full Account Name", "Account Name", "Account Code",
  199.         "Description", "Account Colour", "Notes", "Symbol", "Namespace",
  200.     ]:
  201.         df[col] = df[col].astype(str).str.strip()
  202.  
  203.     df["Hidden"] = df["Hidden"].map(_norm_bool_tf)
  204.     df["Tax Info"] = df["Tax Info"].map(_norm_bool_tf)
  205.     df["Placeholder"] = df["Placeholder"].map(_norm_bool_tf)
  206.  
  207.     # Normalise user-specified commodities (template commodity is internal, not from CSV)
  208.     df["Namespace"] = df["Namespace"].str.upper()
  209.     df["Symbol"] = df["Symbol"].str.upper()
  210.  
  211.     # Precious metals: normalise XAG/XAU/XPT/XPD (and any accidental CURRENCY:SILVER etc)
  212.     # to METALS:<NAME> securities.
  213.     pm_iso = df["Symbol"].isin(PRECIOUS_METAL_ISO_TO_SYMBOL.keys())
  214.     if bool(pm_iso.any()):
  215.         df.loc[pm_iso, "Symbol"] = df.loc[pm_iso, "Symbol"].map(PRECIOUS_METAL_ISO_TO_SYMBOL)
  216.         df.loc[pm_iso, "Namespace"] = PRECIOUS_METALS_NAMESPACE
  217.  
  218.     pm_named = df["Namespace"].eq("CURRENCY") & df["Symbol"].isin(PRECIOUS_METAL_ISO_TO_SYMBOL.values())
  219.     if bool(pm_named.any()):
  220.         df.loc[pm_named, "Namespace"] = PRECIOUS_METALS_NAMESPACE
  221.  
  222.     return df
  223.  
  224.  
  225. def sanity_check(df: pd.DataFrame) -> CommodityKey:
  226.     if df["Full Account Name"].duplicated().any():
  227.         dups = df[df["Full Account Name"].duplicated()]["Full Account Name"].tolist()[:20]
  228.         die(f"Duplicate Full Account Name values found (first 20): {dups}")
  229.  
  230.     bad = df[df.apply(lambda r: str(r["Full Account Name"]).split(":")[-1] != str(r["Account Name"]), axis=1)]
  231.     if not bad.empty:
  232.         sample = bad[["Full Account Name", "Account Name"]].head(10).to_dict(orient="records")
  233.         die(f"Account Name must match the last component of Full Account Name. Examples: {sample}")
  234.  
  235.     bad_types = sorted(set(df["Type"]) - GN_ACCOUNT_TYPES)
  236.     if bad_types:
  237.         die(f"Unsupported account Type(s): {bad_types}. Allowed: {sorted(GN_ACCOUNT_TYPES)}")
  238.  
  239.     full_set = set(df["Full Account Name"])
  240.     parent_map: dict[str, str | None] = {}
  241.     for full in df["Full Account Name"]:
  242.         parts = full.split(":")
  243.         parent = ":".join(parts[:-1]) if len(parts) > 1 else None
  244.         parent_map[full] = parent
  245.         if parent is not None and parent not in full_set:
  246.             die(f"Missing parent account '{parent}' needed for '{full}'")
  247.  
  248.     siblings: dict[str | None, set[str]] = {}
  249.     for full, name in zip(df["Full Account Name"], df["Account Name"], strict=True):
  250.         parent = parent_map[full]
  251.         siblings.setdefault(parent, set())
  252.         if name in siblings[parent]:
  253.             die(f"Duplicate sibling account name '{name}' under parent '{parent or '<ROOT>'}'")
  254.         siblings[parent].add(name)
  255.  
  256.     commodities = {CommodityKey(ns, sym) for ns, sym in zip(df["Namespace"], df["Symbol"], strict=True)}
  257.  
  258.     non_iso_in_currency = sorted([c.symbol for c in commodities if c.namespace == "CURRENCY" and c.symbol not in ISO_CURRENCY_CODES])
  259.     if non_iso_in_currency:
  260.         die(f"Namespace=CURRENCY used with non-ISO currency code(s): {non_iso_in_currency[:50]}")
  261.  
  262.     iso_wrong_ns = sorted([f"{c.namespace}:{c.symbol}" for c in commodities if c.symbol in ISO_CURRENCY_CODES and c.namespace != "CURRENCY"])
  263.     if iso_wrong_ns:
  264.         die("ISO currency codes must use Namespace=CURRENCY. Conflicts found (namespace:symbol): "
  265.             f"{iso_wrong_ns[:50]}")
  266.  
  267.     by_symbol: dict[str, set[str]] = {}
  268.     for c in commodities:
  269.         by_symbol.setdefault(c.symbol, set()).add(c.namespace)
  270.     clashes = sorted([sym for sym, nss in by_symbol.items() if sym in ISO_CURRENCY_CODES and len(nss) > 1])
  271.     if clashes:
  272.         die(f"Currency/stock namespace conflict for ISO currency code(s): {clashes[:50]}")
  273.  
  274.     cur_rows = df[df["Namespace"] == "CURRENCY"]
  275.     if cur_rows.empty:
  276.         die("No CURRENCY commodities found in CSV. At least one currency (e.g. GBP/CURRENCY) is required.")
  277.     home_symbol = cur_rows["Symbol"].mode().iat[0]
  278.     return CommodityKey("CURRENCY", home_symbol)
  279.  
  280.  
  281. def qn(uri: str, local: str) -> str:
  282.     return f"{{{uri}}}{local}"
  283.  
  284.  
  285. NS = {
  286.     "gnc": "http://www.gnucash.org/XML/gnc",
  287.     "act": "http://www.gnucash.org/XML/act",
  288.     "book": "http://www.gnucash.org/XML/book",
  289.     "cd": "http://www.gnucash.org/XML/cd",
  290.     "cmdty": "http://www.gnucash.org/XML/cmdty",
  291.     "slot": "http://www.gnucash.org/XML/slot",
  292. }
  293.  
  294.  
  295. def _indent(elem: ET.Element, level: int = 0) -> None:
  296.     i = "\n" + level * "  "
  297.     if len(elem):
  298.         if not elem.text or not elem.text.strip():
  299.             elem.text = i + "  "
  300.         for child in elem:
  301.             _indent(child, level + 1)
  302.         if not elem[-1].tail or not elem[-1].tail.strip():
  303.             elem[-1].tail = i
  304.     if level and (not elem.tail or not elem.tail.strip()):
  305.         elem.tail = i
  306.  
  307.  
  308. def make_book_slots(parent: ET.Element) -> None:
  309.     slots_el = ET.SubElement(parent, qn(NS["book"], "slots"))
  310.  
  311.     def slot_frame(key: str, inner: list[tuple[str, str, str]]) -> None:
  312.         s = ET.SubElement(slots_el, "slot")
  313.         ET.SubElement(s, qn(NS["slot"], "key")).text = key
  314.         v = ET.SubElement(s, qn(NS["slot"], "value"))
  315.         v.set("type", "frame")
  316.         for ikey, itype, itext in inner:
  317.             inner_s = ET.SubElement(v, "slot")
  318.             ET.SubElement(inner_s, qn(NS["slot"], "key")).text = ikey
  319.             iv = ET.SubElement(inner_s, qn(NS["slot"], "value"))
  320.             iv.set("type", itype)
  321.             iv.text = itext
  322.  
  323.     def slot_string(key: str, value: str) -> None:
  324.         s = ET.SubElement(slots_el, "slot")
  325.         ET.SubElement(s, qn(NS["slot"], "key")).text = key
  326.         v = ET.SubElement(s, qn(NS["slot"], "value"))
  327.         v.set("type", "string")
  328.         v.text = value
  329.  
  330.     counters_keys = ["gncBill", "gncCustomer", "gncEmployee", "gncExpVoucher", "gncInvoice", "gncJob", "gncOrder", "gncVendor"]
  331.     slot_frame("counter_formats", [(k, "string", "") for k in counters_keys])
  332.     slot_frame("counters", [(k, "integer", "0") for k in counters_keys])
  333.     slot_string("remove-trading-splits", "true")
  334.     slot_string("trading-accts", "false")
  335.  
  336.  
  337. def build_gnucash_xml(
  338.     df: pd.DataFrame,
  339.     out_path: Path,
  340.     *,
  341.     book_currency: CommodityKey,
  342.     default_currency_scu: int = 100,
  343.     default_security_fraction: int = 100_000_000,
  344.     commodity_fractions: dict[CommodityKey, int] | None = None,
  345.     deterministic_guids: bool = True,
  346.     include_template_commodity: bool = True,
  347. ) -> None:
  348.     for prefix, uri in NS.items():
  349.         ET.register_namespace(prefix, uri)
  350.  
  351.     root = ET.Element("gnc-v2")
  352.  
  353.     count_book = ET.SubElement(root, qn(NS["gnc"], "count-data"))
  354.     count_book.set(qn(NS["cd"], "type"), "book")
  355.     count_book.text = "1"
  356.  
  357.     book = ET.SubElement(root, qn(NS["gnc"], "book"))
  358.     book.set("version", "2.0.0")
  359.  
  360.     bid = ET.SubElement(book, qn(NS["book"], "id"))
  361.     bid.set("type", "guid")
  362.     bid.text = uuid.uuid4().hex
  363.  
  364.     make_book_slots(book)
  365.  
  366.     commodity_fractions = commodity_fractions or {}
  367.  
  368.     def commodity_fraction(key: CommodityKey) -> int | None:
  369.         if key.namespace == "template" and key.symbol == "template":
  370.             return 1
  371.         if key.namespace == "CURRENCY":
  372.             return None
  373.         if key in commodity_fractions:
  374.             return commodity_fractions[key]
  375.         if key.symbol in DEFAULT_SECURITY_FRACTIONS:
  376.             return DEFAULT_SECURITY_FRACTIONS[key.symbol]
  377.         return int(default_security_fraction)
  378.  
  379.     def account_scu(key: CommodityKey) -> int:
  380.         if key.namespace == "CURRENCY":
  381.             return int(default_currency_scu)
  382.         return int(commodity_fraction(key) or default_security_fraction)
  383.  
  384.     needed = {CommodityKey(ns, sym) for ns, sym in zip(df["Namespace"], df["Symbol"], strict=True)}
  385.     if include_template_commodity:
  386.         needed.add(CommodityKey("template", "template"))
  387.  
  388.     count_commodity = ET.SubElement(book, qn(NS["gnc"], "count-data"))
  389.     count_commodity.set(qn(NS["cd"], "type"), "commodity")
  390.     count_commodity.text = str(len(needed))
  391.  
  392.     count_account = ET.SubElement(book, qn(NS["gnc"], "count-data"))
  393.     count_account.set(qn(NS["cd"], "type"), "account")
  394.     count_account.text = str(len(df) + 1)
  395.  
  396.     def emit_commodity(key: CommodityKey) -> None:
  397.         c = ET.SubElement(book, qn(NS["gnc"], "commodity"))
  398.         c.set("version", "2.0.0")
  399.         ET.SubElement(c, qn(NS["cmdty"], "space")).text = key.namespace
  400.         ET.SubElement(c, qn(NS["cmdty"], "id")).text = key.symbol
  401.  
  402.         frac = commodity_fraction(key)
  403.         if key.namespace == "template" and key.symbol == "template":
  404.             ET.SubElement(c, qn(NS["cmdty"], "name")).text = "template"
  405.             ET.SubElement(c, qn(NS["cmdty"], "fraction")).text = "1"
  406.             return
  407.  
  408.         if key.namespace != "CURRENCY":
  409.             ET.SubElement(c, qn(NS["cmdty"], "name")).text = DEFAULT_SECURITY_NAMES.get(key.symbol, key.symbol)
  410.         if frac is not None:
  411.             ET.SubElement(c, qn(NS["cmdty"], "fraction")).text = str(frac)
  412.  
  413.     for key in sorted([k for k in needed if k.namespace == "CURRENCY"], key=lambda k: k.symbol):
  414.         emit_commodity(key)
  415.     for key in sorted([k for k in needed if k.namespace not in {"CURRENCY", "template"}], key=lambda k: (k.namespace, k.symbol)):
  416.         emit_commodity(key)
  417.     if include_template_commodity:
  418.         emit_commodity(CommodityKey("template", "template"))
  419.  
  420.     def guid_for(seed: str) -> str:
  421.         if deterministic_guids:
  422.             return uuid.uuid5(uuid.NAMESPACE_URL, seed).hex
  423.         return uuid.uuid4().hex
  424.  
  425.     root_guid = guid_for("gnucash-account:__ROOT__")
  426.  
  427.     root_acc = ET.SubElement(book, qn(NS["gnc"], "account"))
  428.     root_acc.set("version", "2.0.0")
  429.     ET.SubElement(root_acc, qn(NS["act"], "name")).text = "Root Account"
  430.     rid = ET.SubElement(root_acc, qn(NS["act"], "id"))
  431.     rid.set("type", "guid")
  432.     rid.text = root_guid
  433.     ET.SubElement(root_acc, qn(NS["act"], "type")).text = "ROOT"
  434.     root_cmd = ET.SubElement(root_acc, qn(NS["act"], "commodity"))
  435.     ET.SubElement(root_cmd, qn(NS["cmdty"], "space")).text = book_currency.namespace
  436.     ET.SubElement(root_cmd, qn(NS["cmdty"], "id")).text = book_currency.symbol
  437.     ET.SubElement(root_acc, qn(NS["act"], "commodity-scu")).text = str(account_scu(book_currency))
  438.  
  439.     df2 = df.copy()
  440.     df2["depth"] = df2["Full Account Name"].map(lambda s: str(s).count(":"))
  441.     df2 = df2.sort_values(["depth", "Full Account Name"]).reset_index(drop=True)
  442.  
  443.     guid_map: dict[str, str] = {
  444.         row["Full Account Name"]: guid_for(f"gnucash-account:{row['Full Account Name']}")
  445.         for _, row in df2.iterrows()
  446.     }
  447.  
  448.     def emit_account(row: pd.Series) -> None:
  449.         full = str(row["Full Account Name"])
  450.         name = str(row["Account Name"])
  451.         typ = str(row["Type"])
  452.         desc = str(row["Description"]).strip()
  453.         code = str(row["Account Code"]).strip()
  454.         notes = str(row["Notes"]).strip()
  455.         color = str(row["Account Colour"]).strip()
  456.         placeholder_tf = str(row["Placeholder"]).strip().upper()
  457.  
  458.         hidden_tf = str(row["Hidden"]).strip().upper()
  459.         tax_info_tf = str(row["Tax Info"]).strip().upper()
  460.  
  461.         ckey = CommodityKey(str(row["Namespace"]), str(row["Symbol"]))
  462.  
  463.         a = ET.SubElement(book, qn(NS["gnc"], "account"))
  464.         a.set("version", "2.0.0")
  465.  
  466.         ET.SubElement(a, qn(NS["act"], "name")).text = name
  467.         aid = ET.SubElement(a, qn(NS["act"], "id"))
  468.         aid.set("type", "guid")
  469.         aid.text = guid_map[full]
  470.  
  471.         ET.SubElement(a, qn(NS["act"], "type")).text = typ
  472.  
  473.         acmd = ET.SubElement(a, qn(NS["act"], "commodity"))
  474.         ET.SubElement(acmd, qn(NS["cmdty"], "space")).text = ckey.namespace
  475.         ET.SubElement(acmd, qn(NS["cmdty"], "id")).text = ckey.symbol
  476.         ET.SubElement(a, qn(NS["act"], "commodity-scu")).text = str(account_scu(ckey))
  477.  
  478.         if code:
  479.             ET.SubElement(a, qn(NS["act"], "code")).text = code
  480.         if desc:
  481.             ET.SubElement(a, qn(NS["act"], "description")).text = desc
  482.  
  483.         slots_to_write: list[tuple[str, str, str | None]] = []
  484.         if placeholder_tf == "T":
  485.             slots_to_write.append(("placeholder", "string", "true"))
  486.         if hidden_tf == "T":
  487.             slots_to_write.append(("hidden", "string", "true"))
  488.         if tax_info_tf == "T":
  489.             slots_to_write.append(("tax-related", "string", "true"))
  490.         if notes:
  491.             slots_to_write.append(("notes", "string", notes))
  492.         if color:
  493.             slots_to_write.append(("color", "string", color))
  494.         if typ in {"STOCK", "MUTUAL"}:
  495.             slots_to_write.append(("balance-limit", "frame", None))
  496.  
  497.         if slots_to_write:
  498.             slots_el = ET.SubElement(a, qn(NS["act"], "slots"))
  499.             for key, vtype, vtext in slots_to_write:
  500.                 s = ET.SubElement(slots_el, "slot")
  501.                 ET.SubElement(s, qn(NS["slot"], "key")).text = key
  502.                 v = ET.SubElement(s, qn(NS["slot"], "value"))
  503.                 v.set("type", vtype)
  504.                 if vtype != "frame":
  505.                     v.text = vtext or ""
  506.  
  507.         parent_full = ":".join(full.split(":")[:-1]) if ":" in full else None
  508.         parent_guid = guid_map[parent_full] if parent_full else root_guid
  509.         ap = ET.SubElement(a, qn(NS["act"], "parent"))
  510.         ap.set("type", "guid")
  511.         ap.text = parent_guid
  512.  
  513.     for _, row in df2.iterrows():
  514.         emit_account(row)
  515.  
  516.     _indent(root)
  517.     out_path.write_bytes(ET.tostring(root, encoding="utf-8", xml_declaration=True))
  518.  
  519.  
  520. def parse_gnucash_accounts_for_verify(gnucash_path: Path) -> pd.DataFrame:
  521.     tree = ET.parse(gnucash_path)
  522.     root = tree.getroot()
  523.  
  524.     ns = {"gnc": NS["gnc"], "act": NS["act"], "cmdty": NS["cmdty"], "slot": NS["slot"]}
  525.     book = root.find("gnc:book", ns)
  526.     if book is None:
  527.         die("Invalid .gnucash file: missing gnc:book")
  528.  
  529.     accs = book.findall("gnc:account", ns)
  530.  
  531.     data: dict[str, dict[str, Any]] = {}
  532.     for a in accs:
  533.         aid = a.find("act:id", ns).text  # type: ignore[union-attr]
  534.         name = (a.find("act:name", ns).text or "").strip()  # type: ignore[union-attr]
  535.         atype = (a.find("act:type", ns).text or "").strip()  # type: ignore[union-attr]
  536.         parent_el = a.find("act:parent", ns)
  537.         parent = parent_el.text.strip() if parent_el is not None and parent_el.text else None
  538.  
  539.         desc_el = a.find("act:description", ns)
  540.         desc = (desc_el.text or "").strip() if desc_el is not None else ""
  541.  
  542.         code_el = a.find("act:code", ns)
  543.         code = (code_el.text or "").strip() if code_el is not None else ""
  544.  
  545.         com = a.find("act:commodity", ns)
  546.         symbol = ""
  547.         namespace = ""
  548.         if com is not None:
  549.             space = com.find("cmdty:space", ns)
  550.             cid = com.find("cmdty:id", ns)
  551.             namespace = (space.text or "").strip() if space is not None else ""  # type: ignore[union-attr]
  552.             symbol = (cid.text or "").strip() if cid is not None else ""  # type: ignore[union-attr]
  553.  
  554.         slots = a.find("act:slots", ns)
  555.         slot_map: dict[str, tuple[str, str]] = {}
  556.         if slots is not None:
  557.             for s in slots.findall("slot", ns):
  558.                 key = s.find("slot:key", ns)
  559.                 val = s.find("slot:value", ns)
  560.                 if key is not None and val is not None:
  561.                     slot_map[(key.text or "").strip()] = (val.get("type") or "", (val.text or "").strip())
  562.  
  563.         def _slot_truthy(key: str) -> bool:
  564.             vtype, vtext = slot_map.get(key, ("", ""))
  565.             vt = (vtype or "").strip().lower()
  566.             vx = (vtext or "").strip().lower()
  567.             if vx in {"true", "t", "1", "yes", "y"}:
  568.                 return True
  569.             # Tax related info is often stored as a frame when TXF metadata is set.
  570.             if vt == "frame":
  571.                 return True
  572.             return False
  573.  
  574.         placeholder = "T" if _slot_truthy("placeholder") else "F"
  575.         hidden = "T" if _slot_truthy("hidden") else "F"
  576.         tax_info = "T" if _slot_truthy("tax-related") else "F"
  577.         notes = slot_map.get("notes", ("", ""))[1]
  578.         color = slot_map.get("color", ("", ""))[1]
  579.  
  580.         data[aid] = {
  581.             "id": aid,
  582.             "name": name,
  583.             "type": atype,
  584.             "parent": parent,
  585.             "description": desc,
  586.             "code": code,
  587.             "namespace": namespace,
  588.             "symbol": symbol,
  589.             "placeholder": placeholder,
  590.             "hidden": hidden,
  591.             "tax_info": tax_info,
  592.             "notes": notes,
  593.             "color": color,
  594.         }
  595.  
  596.     def full_name(aid: str) -> str:
  597.         parts: list[str] = []
  598.         cur = aid
  599.         while cur and cur in data:
  600.             t = data[cur]["type"]
  601.             if t == "ROOT":
  602.                 break
  603.             parts.append(data[cur]["name"])
  604.             cur = data[cur]["parent"]
  605.         return ":".join(reversed(parts))
  606.  
  607.     rows: list[dict[str, Any]] = []
  608.     for aid, info in data.items():
  609.         if info["type"] == "ROOT":
  610.             continue
  611.         rows.append({
  612.             "Type": info["type"],
  613.             "Full Account Name": full_name(aid),
  614.             "Account Name": info["name"],
  615.             "Account Code": info["code"],
  616.             "Description": info["description"],
  617.             "Account Colour": info["color"],
  618.             "Notes": info["notes"],
  619.             "Symbol": str(info["symbol"]).upper(),
  620.             "Namespace": str(info["namespace"]).upper(),
  621.             "Hidden": info["hidden"],
  622.             "Tax Info": info["tax_info"],
  623.             "Placeholder": info["placeholder"],
  624.         })
  625.  
  626.     out = pd.DataFrame(rows).fillna("")
  627.     out = out[EXPECTED_COLUMNS].sort_values(["Full Account Name"]).reset_index(drop=True)
  628.     return out
  629.  
  630.  
  631. def parse_gnucash_commodities(gnucash_path: Path) -> dict[CommodityKey, dict[str, str]]:
  632.     tree = ET.parse(gnucash_path)
  633.     root = tree.getroot()
  634.     ns = {"gnc": NS["gnc"], "cmdty": NS["cmdty"]}
  635.     book = root.find("{" + NS["gnc"] + "}book")
  636.     if book is None:
  637.         die("Invalid .gnucash file: missing gnc:book")
  638.  
  639.     out: dict[CommodityKey, dict[str, str]] = {}
  640.     for c in book.findall("{" + NS["gnc"] + "}commodity"):
  641.         space_el = c.find("{" + NS["cmdty"] + "}space")
  642.         id_el = c.find("{" + NS["cmdty"] + "}id")
  643.         name_el = c.find("{" + NS["cmdty"] + "}name")
  644.         frac_el = c.find("{" + NS["cmdty"] + "}fraction")
  645.         space = (space_el.text or "").strip() if space_el is not None else ""
  646.         cid = (id_el.text or "").strip() if id_el is not None else ""
  647.         key = CommodityKey(space, cid)
  648.         out[key] = {
  649.             "space": space,
  650.             "id": cid,
  651.             "name": (name_el.text or "").strip() if name_el is not None else "",
  652.             "fraction": (frac_el.text or "").strip() if frac_el is not None else "",
  653.         }
  654.     return out
  655.  
  656.  
  657. def verify(
  658.     gnucash_path: Path,
  659.     csv_path: Path,
  660.     *,
  661.     allow_missing: bool,
  662.     allow_extra: bool,
  663.     max_list: int,
  664.     check_commodities: bool,
  665. ) -> None:
  666.     df_csv = read_accounts_csv(csv_path).copy()
  667.     df_gc = parse_gnucash_accounts_for_verify(gnucash_path)
  668.  
  669.     for df in (df_csv, df_gc):
  670.         for c in EXPECTED_COLUMNS:
  671.             df[c] = df[c].fillna("").astype(str).str.strip()
  672.  
  673.     set_csv = set(df_csv["Full Account Name"])
  674.     set_gc = set(df_gc["Full Account Name"])
  675.  
  676.     missing_in_gc = sorted(set_csv - set_gc)
  677.     extra_in_gc = sorted(set_gc - set_csv)
  678.  
  679.     if missing_in_gc:
  680.         print("Missing in GnuCash (present in CSV):", file=sys.stderr)
  681.         for name in missing_in_gc[:max_list]:
  682.             print(f"  - {name}", file=sys.stderr)
  683.         if len(missing_in_gc) > max_list:
  684.             print(f"  ... and {len(missing_in_gc) - max_list} more", file=sys.stderr)
  685.  
  686.     if extra_in_gc:
  687.         print("Extra in GnuCash (not in CSV):", file=sys.stderr)
  688.         for name in extra_in_gc[:max_list]:
  689.             print(f"  + {name}", file=sys.stderr)
  690.         if len(extra_in_gc) > max_list:
  691.             print(f"  ... and {len(extra_in_gc) - max_list} more", file=sys.stderr)
  692.  
  693.     if (missing_in_gc and not allow_missing) or (extra_in_gc and not allow_extra):
  694.         die(
  695.             f"Account set mismatch: {len(missing_in_gc)} missing account(s) in GnuCash; "
  696.             f"{len(extra_in_gc)} extra account(s) in GnuCash."
  697.         )
  698.  
  699.     # Compare intersection if non-strict
  700.     common = sorted(set_csv & set_gc)
  701.     df_csv2 = df_csv[df_csv["Full Account Name"].isin(common)].sort_values(["Full Account Name"]).reset_index(drop=True)
  702.     df_gc2 = df_gc[df_gc["Full Account Name"].isin(common)].sort_values(["Full Account Name"]).reset_index(drop=True)
  703.  
  704.     diffs: list[str] = []
  705.     for c in EXPECTED_COLUMNS:
  706.         if not df_csv2[c].equals(df_gc2[c]):
  707.             diffs.append(c)
  708.  
  709.     if diffs:
  710.         for c in diffs:
  711.             neq = df_csv2[c] != df_gc2[c]
  712.             if bool(neq.any()):
  713.                 i = int(neq[neq].index[0])
  714.                 print(f"First difference in column '{c}':", file=sys.stderr)
  715.                 print(f"  Account: {df_csv2.at[i, 'Full Account Name']}", file=sys.stderr)
  716.                 print(f"  CSV:     {df_csv2.at[i, c]!r}", file=sys.stderr)
  717.                 print(f"  GnuCash: {df_gc2.at[i, c]!r}", file=sys.stderr)
  718.                 break
  719.         die(f"Verification failed. Columns differing: {diffs}")
  720.  
  721.     if check_commodities:
  722.         comms = parse_gnucash_commodities(gnucash_path)
  723.  
  724.         # 1) template commodity must remain lowercase (if present)
  725.         tmpl_key_any = None
  726.         for k in comms.keys():
  727.             if k.namespace.lower() == "template" and k.symbol.lower() == "template":
  728.                 tmpl_key_any = k
  729.                 break
  730.         if tmpl_key_any is not None:
  731.             # Must be EXACTLY template:template with fraction 1 and name template
  732.             if not (tmpl_key_any.namespace == "template" and tmpl_key_any.symbol == "template"):
  733.                 die(f"Template commodity regression: found {tmpl_key_any.namespace}:{tmpl_key_any.symbol} (expected template:template)")
  734.             meta = comms[tmpl_key_any]
  735.             if meta.get("fraction", "") not in {"1", ""}:
  736.                 die(f"Template commodity fraction must be 1 (found {meta.get('fraction')!r})")
  737.             if meta.get("name", "") not in {"template", ""}:
  738.                 die(f"Template commodity name must be 'template' (found {meta.get('name')!r})")
  739.  
  740.         # 2) required securities (non-currency) must exist
  741.         required = {CommodityKey(ns, sym) for ns, sym in zip(df_csv["Namespace"], df_csv["Symbol"], strict=True)}
  742.         required = {k for k in required if k.namespace != "CURRENCY"}
  743.  
  744.         missing = sorted([f"{k.namespace}:{k.symbol}" for k in required if k not in comms])
  745.         if missing:
  746.             die(f"Missing commodity definition(s) in book: {missing[:50]}")
  747.  
  748.         # 3) for known crypto, check expected fraction if present
  749.         bad_frac = []
  750.         for k in required:
  751.             if k.namespace != "CRYPTO":
  752.                 continue
  753.             exp = DEFAULT_SECURITY_FRACTIONS.get(k.symbol)
  754.             if exp is None:
  755.                 continue
  756.             meta = comms.get(k, {})
  757.             frac_txt = meta.get("fraction", "")
  758.             if frac_txt and frac_txt.isdigit() and int(frac_txt) != exp:
  759.                 bad_frac.append(f"{k.namespace}:{k.symbol} fraction={frac_txt} expected={exp}")
  760.         if bad_frac:
  761.             die(f"Commodity fraction mismatch(es): {bad_frac[:50]}")
  762.  
  763.     if allow_missing or allow_extra:
  764.         print(
  765.             f"OK: {gnucash_path.name} matches {csv_path.name} ({len(df_gc2)} account(s)). "
  766.             f"Compared {len(common)} common account(s); missing {len(missing_in_gc)}; extra {len(extra_in_gc)}."
  767.         )
  768.     else:
  769.         print(f"OK: {gnucash_path.name} matches {csv_path.name} ({len(df_gc2)} account(s)).")
  770.  
  771.  
  772. def parse_commodity_fraction_args(items: list[str]) -> dict[CommodityKey, int]:
  773.     out: dict[CommodityKey, int] = {}
  774.     for item in items:
  775.         try:
  776.             key_part, frac_part = item.split("=", 1)
  777.             ns_part, sym_part = key_part.split(":", 1)
  778.             frac = int(frac_part.strip())
  779.             if frac <= 0:
  780.                 die(f"Invalid fraction (must be >0): {item!r}")
  781.             out[CommodityKey(ns_part, sym_part)] = frac
  782.         except ValueError:
  783.             die(f"Invalid --commodity-fraction value {item!r}. Expected NAMESPACE:SYMBOL=FRACTION")
  784.     return out
  785.  
  786.  
  787. def main(argv: list[str] | None = None) -> int:
  788.     p = argparse.ArgumentParser(description="Generate a GnuCash XML book (*.gnucash) from an accounts tree CSV.")
  789.     sub = p.add_subparsers(dest="cmd", required=True)
  790.  
  791.     b = sub.add_parser("build", help="Build a new .gnucash XML file from a CSV")
  792.     b.add_argument("csv", type=Path, help="Input accounts tree CSV")
  793.     b.add_argument("--out", type=Path, required=True, help="Output .gnucash file path")
  794.     b.add_argument("--default-currency-scu", type=int, default=100, help="Default currency SCU (GBP uses 100)")
  795.     b.add_argument("--default-security-fraction", type=int, default=100_000_000, help="Default fraction/SCU for non-currency commodities")
  796.     b.add_argument("--commodity-fraction", action="append", default=[], help="Override fraction: NAMESPACE:SYMBOL=FRACTION (repeatable)")
  797.     b.add_argument("--random-guids", action="store_true", help="Use random GUIDs (default is deterministic)")
  798.     b.add_argument("--home-currency", type=str, default="", help="Override book currency (e.g. GBP). Default: most common currency in CSV")
  799.     b.add_argument("--no-template-commodity", action="store_true", help="Do not emit the internal template:template commodity")
  800.  
  801.     v = sub.add_parser("verify", help="Verify an existing .gnucash matches a CSV (same schema)")
  802.     v.add_argument("gnucash", type=Path, help="Existing .gnucash XML file")
  803.     v.add_argument("csv", type=Path, help="CSV to compare against")
  804.     v.add_argument("--allow-missing", action="store_true", help="Allow accounts present in CSV but missing in GnuCash")
  805.     v.add_argument("--allow-extra", action="store_true", help="Allow accounts present in GnuCash but missing in CSV")
  806.     v.add_argument("--max-list", type=int, default=50, help="Max missing/extra accounts to list")
  807.     v.add_argument("--no-check-commodities", action="store_true", help="Skip commodity checks (template + securities)")
  808.  
  809.     args = p.parse_args(argv)
  810.  
  811.     if args.cmd == "verify":
  812.         verify(
  813.             args.gnucash,
  814.             args.csv,
  815.             allow_missing=args.allow_missing,
  816.             allow_extra=args.allow_extra,
  817.             max_list=args.max_list,
  818.             check_commodities=not args.no_check_commodities,
  819.         )
  820.         return 0
  821.  
  822.     if args.cmd == "build":
  823.         df = read_accounts_csv(args.csv)
  824.         book_currency = sanity_check(df)
  825.  
  826.         if args.home_currency:
  827.             sym = args.home_currency.strip().upper()
  828.             if sym not in ISO_CURRENCY_CODES:
  829.                 die(f"--home-currency {sym!r} is not an ISO 4217 currency code")
  830.             book_currency = CommodityKey("CURRENCY", sym)
  831.  
  832.         overrides = parse_commodity_fraction_args(args.commodity_fraction)
  833.  
  834.         build_gnucash_xml(
  835.             df,
  836.             args.out,
  837.             book_currency=book_currency,
  838.             default_currency_scu=args.default_currency_scu,
  839.             default_security_fraction=args.default_security_fraction,
  840.             commodity_fractions=overrides,
  841.             deterministic_guids=not args.random_guids,
  842.             include_template_commodity=not args.no_template_commodity,
  843.         )
  844.         print(f"Wrote: {args.out}")
  845.         return 0
  846.  
  847.     die("Unknown command")
  848.     return 2
  849.  
  850.  
  851. if __name__ == "__main__":
  852.     raise SystemExit(main())
  853.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!