2 min read

Scripting with APL: Convert GNUCash XML format to Ledger CLI

I want to play around with the Ledger CLI software, but this requires that I have something to work with. I have previously kept most of my stuff in the GNUCash program, and while I found a few conversion utilities around, I didn't find anything that worked for the XML format that GNUCash was using as well as giving me the confidence that I knew what it was doing and knew how it could go wrong or right.

I took a quick look at the XML file and realized that there was lots of regularity to exploit and that it would be almost too easy to do this in APL, so rather than trying to continue a possibly fruitless hunt for the right software to do what I wanted, I just took half a day and wrote up the appropriate code in APL. It was quick, it was simple, and more importantly, it gets the job done without over complicating my needs. I just needed to get data out in a format that resembles the ledger format so that I can get some of the unique specialties such as check numbering and the like put into the system in the right way.

It's a simple 55 line file, including the license, and it's a mere 25 lines if you only take into account the code. GNUCash XML manipulation in 25 lines is pretty good if you ask me. :-)

⍝ GnuCash XML to Ledger CLI Format
⍝ Copyright (c) 2016 Aaron W. Hsu.
⍝
⍝ Permission to use, copy, modify, and distribute this software for any
⍝ purpose with or without fee is hereby granted, provided that the above
⍝ copyright notice and this permission notice appear in all copies.
⍝
⍝ THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
⍝ WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
⍝ MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
⍝ ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
⍝ WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
⍝ ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
⍝ OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

⍝ General Usage
⍝
⍝ output←⍕⍪g2l.(convert∆trns gcdb)'./Accounts.gnucash'
⍝ output←,output,(⎕UCS 10)
⍝ output utf8put './general_ledger.txt'

:Namespace g2l

⎕IO ← 0

⍎'''utf8get''⎕CY''dfns'' ⋄ 0'

⍝ Utilities
value←⊃(⊢⍪0,''(0 2⍴⊂'')5,⍨(⊂⊣))⌷⍨2,⍨(⊂⊣)⍳⍨1⌷∘⍉⊢
values←(2⌷∘⍉⊢)(/⍨)(⊂⊣)∊⍨1⌷∘⍉⊢

⍝ Account Lookup and Reification
convert∆acct←'act:name' 'act:id' 'act:parent'value¨∘⊂⊢
convert∆accts←↑(convert∆acct¨)
reify∆accts←(∨.∧⍨⍣≡∘(⊢∨(∘.=⍨∘⍳≢))(↑2⌷⍉)∧.=∘⍉∘↑1⌷⍉)({⊃{⍺,':',⍵}/⍺/⍵}⍤1)0⌷⍉
build∆acct∆db←((1⌷⍉),∘⍪∘↓(≢'Root Account:')↓[1]reify∆accts)convert∆accts
acct∆lookup←⊃⊣⌷⍨1,⍨(0⌷∘⍉⊣)⍳∘⊂⊢

⍝ File Extraction
getchunks←{(2=0⌷⍉⍵)⊂[0]⍵}∘⎕XML∘utf8get
getaccts←⊢(/⍨)(⊂'gnc:account')(⊣≡∘⊃0 1⌷⊢)¨⊢
gettrns←⊢(/⍨)(⊂'gnc:transaction')(⊣≡∘⊃0 1⌷⊢)¨⊢
gcdb←(build∆acct∆db∘getaccts{⍺⍵}gettrns)getchunks

⍝ Transaction Conversion
trn∆description←'trn:description'value⊢
trn∆date←10↑'ts:date'value ⊢
split∆value∆converted←('$',(¯6↓⊢),'.',2↑¯6↑⊢)¨'split:value'values⊢
split∆acct∆converted←(⊂⊣)acct∆lookup¨'split:account'values⊢
trn∆splits←(⊂''),split∆acct∆converted,∘⍪split∆value∆converted
trn∆header←(⊂''),⍨trn∆date{⍺⍵}trn∆description
convert∆trn←trn∆header⍪trn∆splits
convert∆trns←⊃((⊂⊣)convert∆trn¨⊢)/

:EndNamespace

Keep in mind that this is an example of quick and dirty APL scripting, and it does not involve any cleaned up code or refactoring to make the code easier to follow. This was meant to get my problem solved as quickly as possible. A few interesting features to note, however, are the almost exclusive use of trains. This code contains some of my own unique train-isms that I find particularly helpful when working in this style of programming. Maybe I'll write up a vector article on them one day.