Ignorance may be Strength

Saturday, May 24, 2025

Processing the Verbs

The final step of generating a GnuCash file is to process the verbs. Then we can wire everything up and let it rip.

Each "verb/action" combines two sources of information: the source and destination accounts from the configuration file together with the date, description and amount from the spreadsheet. There is also the odd case where the spreadsheet can "override" the source or destination accounts.

Configuration

We can start by defining the verbs in the configuration file:

type Configuration struct {
    APIKey       string
    OAuth        string
    Token        string
    RedirectPort int
    Spreadsheet  string
    Output       string

    Business Business
    Accounts []Account
    Verbs    []Verb
    VerbMap  map[string]*Verb
}

type Verb struct {
    Name   string
    Source string
    Dest   string
}

GNUCASH_SPREADSHEET_TO_XML:accounts/internal/gnucash/config/config.go

Because we want to configure the verbs as a list, but use them as a map, I have defined VerbMap here which is not read from the JSON file but filled in later:

func ReadConfig(file string) (*Configuration, error) {
    log.Printf("reading %s\n", file)
    bs, err := os.ReadFile(file)
    if err != nil {
        return nil, err
    }
    ret := Configuration{VerbMap: make(map[string]*Verb)}
    err = json.Unmarshal(bs, &ret)
    if err != nil {
        panic(err)
    }
    for _, v := range ret.Verbs {
        ret.VerbMap[v.Name] = &v
    }
    log.Printf("read %v\n", ret)
    return &ret, nil
}

GNUCASH_SPREADSHEET_TO_XML:accounts/internal/gnucash/config/reader.go

That completes handling the configuration.

Processing the Sheets

The bulk of the code we're going to add is in the AccountsTransformer. It receives a sheet and processes it.

func (at *AccountsTransformer) DeliverSheet(tabs []sheets.Tab) {
    fmt.Printf("%v\n", tabs)
    accts := writer.NewAccounts(at.conf)
    for _, tab := range tabs {
        verb := at.conf.VerbMap[tab.Title]
        if verb == nil {
            panic("there is no verb " + tab.Title)
        }
        for _, r := range tab.Rows {
            dt := r.Columns["Date"]
            var yr, mon, day int
            fmt.Sscanf(dt.(string), "%04d-%02d-%02d", &yr, &mon, &day)

            amount := r.Columns["Value"].(string)
            var amt writer.Money
            amount = strings.Replace(amount, "£", "", -1)
            amount = strings.Replace(amount, ",", "", -1)
            if strings.Contains(amount, ".") {
                var gbp, pence int
                fmt.Sscanf(amount, "%d.%d", &gbp, &pence)
                amt = writer.GBPP(gbp, pence)
            } else {
                var gbp int
                fmt.Sscanf(amount, "%d", &gbp)
                amt = writer.GBP(gbp)
            }

            tx := accts.Verb(verb, writer.Date(yr, mon, day, 900), r.Columns["Description"].(string), amt)
            if r.Columns["Source"] != nil && r.Columns["Source"] != "" {
                tx.SetSrc(r.Columns["Source"].(string))
            }
            if r.Columns["Dest"] != nil && r.Columns["Dest"] != "" {
                tx.SetDest(r.Columns["Dest"].(string))
            }
        }
    }
    at.dlvr.Deliver(accts)
}

GNUCASH_SPREADSHEET_TO_XML:accounts/internal/gnucash/accounts/transform.go

The verb is chosen based on the title of the tab. (This was not recorded when I wrote the original version of the spreadsheet reader, so I had to go back and add it.)

Then, all the Rows are processed sequentially. The date is recovered and parsed using Sscanf. The amount is extracted and all the punctuation is removed before it is parsed using Sscanf (I'm sure that it's possible to do this all in one step, but I didn't want to deal). I decided to consider the two separate cases of round pounds (which most of my transactions are) as well as pounds and pence (about 3).

Then we call the Verb method on GnuCash which is just a simple wrapper over the Transact method we wrote last time which does the combining of the verb and transaction details.

func (g *Gnucash) Verb(verb *config.Verb, date DateInfo, description string, amount Money) *Transaction {
return g.Transact(date, description, verb.Source, verb.Dest, amount)
}

GNUCASH_SPREADSHEET_TO_XML:accounts/internal/gnucash/writer/gnucash.go

And finally we consider that the user might have defined (and put values in) columns called Source and Dest which override the configured columns by calling SetSrc or SetDest on the transaction:

func (tx *Transaction) SetDest(name string) {
    destGuid := tx.accountGuids[name]
    if destGuid == "" {
        panic("there is no account for " + name)
    }
    tx.destAcct.Value = destGuid
}

func (tx *Transaction) SetSrc(name string) {
    srcGuid := tx.accountGuids[name]
    if srcGuid == "" {
        panic("there is no account for " + name)
    }
    tx.srcAcct.Value = srcGuid
}

GNUCASH_SPREADSHEET_TO_XML:accounts/internal/gnucash/writer/gnucash.go

(In the previous episode, we ignored the transaction description, so I quietly reworked that to add it. I also realized that I had inverted the splits "from" and "to" so I changed them. On top of that, a whole bunch of other things had to change to make it possible to implement SetSrc and SetDest.)

Conclusion

And, for now, that is that. I ran through all my spreadsheets and after correcting a few data entry errors found that everything matched up.

I want to do two more things with this project, though. The first - and most important - is to see if I can use the GnuCash ixbrl-reporter project to automate generation of my accounts in iXBRL format (inline eXtensible Business Reporting Language). If I understand this correctly, if correctly configured, I should be able to generate an HTML document that will present my accounts. But, internally, it will have semantic tags that enable the government's computers to process it automatically. The other - which is somewhat related inasmuch as it supports me understanding how to do that - is to generate my own reports (i.e. annual P&L and Balance Sheets). I can do all that without going through GnuCash, of course - and my plan is to do just that by keeping track of all the accounts in memory and then spewing out the appropriate things.

Because it is a supporting task, and because I think I understand how to do it, I am going to do the second one first.

Friday, May 23, 2025

Generating the XML

I'm now going to skip to the other end, writing out the XML file, for a number of reasons:

I haven't done it before, so it seems high risk;
It connects to our other dependency in this project, so its shape is forced on us;
In order to build this out, I will need to define the structs that I want to transform into;
The middle piece is the most interesting, so I'm pushing it off until last.

So, for now, the middle stage of the pipeline is just going to print out the incoming sheet data and then call the final phase with initially no accounts. As I need them, I will add more test data there.

In the first episode, I toyed around with GnuCash, so I have a sample file. I'm going to try and produce something "similar" to that, although I doubt that I can generate an identical file (for example, the GUIDs will be different).

XML Marshalling

So the basic thing we want to do is take the incoming structs and marshal them to XML. There is a package xml and a method MarshalIndent that does exactly that. How hard can it be?

It's not that hard to do the writing:

package writer

import (
    "encoding/xml"
    "os"

    "github.com/gmmapowell/ignorance/accounts/internal/gnucash/config"
)

const (
    header = `<?xml version="1.0" encoding="UTF-8"?>` + "\n"
)

type Writer struct {
    Config *config.Configuration
}

func (w *Writer) Deliver(accts *Gnucash) {
    bs, err := xml.MarshalIndent(accts, "", "  ")
    if err != nil {
        panic(err)
    }

    withHeader := header + string(bs)

    os.WriteFile(w.Config.Output, []byte(withHeader), 0666)
}

func MakeWriter(conf *config.Configuration) *Writer {
    return &Writer{Config: conf}
}

GNUCASH_WRITE_XML_1:accounts/internal/gnucash/writer/writer.go

As much as anything, it's a pain that the xml package doesn't allow you to specify the doctype header, so there are hoops to jump through for that.

All the pain, of course, comes from having to specify the types to be marshalled and getting all the XML tags in place.

At the top level, we have the GnuCash definition which represents the whole file:

package writer

import "encoding/xml"

type Gnucash struct {
    XMLName xml.Name `xml:"gnc-v2"`
    Namespaces
    Elements
}