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.