Tech Is Hard

Credibility = Talent x Years of experience + Proven hardcore accomplishment

Importing GnuCash Data Into Evernote with XSL


I guess I’ve been into GnuCash data lately. In the quest for a Memex-like repository, I realized how much of our activity is connected somehow with a financial transaction. Normally there’s a wall between accounting software and other stuff, but if I really want to connect the facts of daily life, I think I need to share that financial information.

I still love REBOL, but I need to make changes quickly and see the results, so I decided to switch to XSL for a while until I know more about the kind of data structure I need to support something bigger.  I also encountered Evernote recently and started looking at the import/export capability, and most important for use, a handy tagging functionality.

After looking at the data for some time, my first inclination is to tag each transaction, at the lowest possible granularity, with tokens that are generated from account names — but only for expenses that have some description or memo.  I don’t think expense accounts need to be organized in the hierarchical folder structure I always see.  I like searching and summarizing by tags.  Of course, accounts that represent assets would probably be more distinct entities.

I want to use Evernote to edit the transactions’ tags and be able to merge them back into my GnuCash file.  I want to accumulate information, thus the tags have to be preserved if the data makes round trips between these two applications.  If tags are already found in a transaction when going from GnuCash to Evernote, it won’t be reloaded from its account name.  The other rule I have is that if there’s no description or memo for the transaction, there isn’t any text to use as an indication of what it is, and nothing to be able to relate automatically to a tag.

I looked at both applications’ XML files and sized up the basics.  Some of the fields in either format are still a mystery, but not important to this purpose.  The file holds information about:

  • accounts
  • commodity pricing
  • currency information
  • automatically scheduled transactions
  • transactions, which hold entries, called “splits”, for each account involved in the transaction

A split holds

  • a memo field
  • an amount
  • account identifier
The transactions represent a subset of my daily life, and I think if I can relate text tokens contained in the descriptions and memos I’ve entered to semantic tags, that wall between financial data and everything else will be softer.  The nodes I’m interested in are the accounts and transactions (I removed some of the nodes I’m not interested in to make the XML shorter):
<gnc:account version="2.0.0">
    <act:name>Home Repair</act:name>
    <act:id type="guid">6ab22a808154524e40c35857ef729d63</act:id>
    <act:type>EXPENSE</act:type>
    <act:description>Home Repair</act:description>
    <act:parent type="guid">b768b5846a843ada4247c1e37fd0bd4c</act:parent>
</gnc:account>
...
<gnc:transaction version="2.0.0">
    <trn:id type="guid">a1ddec4b4fe26e84745a8cddac018620</trn:id>
    <trn:date-posted>
        <ts:date>2011-01-03 00:00:00 -0700</ts:date>
    </trn:date-posted>
    <trn:date-entered>
        <ts:date>2011-01-03 13:02:43 -0700</ts:date>
    </trn:date-entered>
    <trn:description>Hobby Lobby</trn:description>
    <trn:splits>
        <trn:split>
            <split:id type="guid">bd2263d811355b09056c503949201174</split:id>
            <split:value>4106/100</split:value>
            <split:account type="guid">6ab22a808154524e40c35857ef729d63</split:account>
            <split:memo>fix picture glass</split:memo>
        </trn:split>
        <trn:split>
            <split:id type="guid">e055a45283323c69836e57375d66e611</split:id>
            <split:value>-4106/100</split:value>
            <split:account type="guid">07a85b902dda87acd6cfed6e0237cc43</split:account>
        </trn:split>
    </trn:splits>
</gnc:transaction>

We have the name and type of the account and its ancestry information (the act:parent node). I use all account names in the path to create default tags for a transaction, so that if you have a “Medical” expense account, and within that “Jane” and “John”, we’re going to get all the information.  The Evernote notes are pretty simple (this is the “.enex” file format):

<note>
    <title>Welcome to Evernote</title>
    <content><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">
<en-note style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><table border="0" cellpadding="4"><tbody><tr><td colspan="3" height="60" align="left" bgcolor="#6FB536"><h1><strong><font face="Arial,Helvetica,sans-serif" size="6" color="white">Welcome to Evernote</font></strong></h1></td></tr><tr><td colspan="3" align="left"><font face="Arial,Helvetica,sans-serif" size="2">Use Evernote to save your ideas, things you see, and things you like. Then find them all on any computer or device you use.</font></td></tr><tr><td valign="top" width="320" align="left"><font><font size="3"><font size="3"><b><font color="#252525">A few simple ideas to get you started</font></b></font></font></font><ul><li><font face="Arial,Helvetica,sans-serif" size="2">Click New Note and take down an idea or task.</font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Clip and save a webpage using a <a href="http://www.evernote.com/about/download/web_clipper.php">Web Clipper.</a></font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Use Evernote on your phone to snap a photo of a whiteboard, business card, or wine label. <em>Evernote automatically makes text in your snapshots searchable!</em></font></li></ul><font><font color="#000000" size="3"><font size="3"><b>Lots of useful features</b></font></font></font><ul><li><font face="Arial, Helvetica, sans-serif"><font size="2">Take notes, save images, create to-dos, view PDFs, and more <br /></font></font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Access your Evernote notes from any computer or phone you use</font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Search and find everything, even printed or handwritten text in images</font></li></ul><div><font><font color="#000000" size="3"><font size="3"><b>Install and use Evernote everywhere</b></font></font></font><ul><li><font face="Arial,Helvetica,sans-serif" size="2">Download and install Evernote on all of your computers and phones</font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Install a <a href="http://www.evernote.com/about/download/web_clipper.php">Web Clipper</a> into your web browser</font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Email notes to your Evernote email address</font></li><li><font face="Arial, Helvetica, sans-serif"><font size="2"><font face="Arial,Helvetica,sans-serif" size="2">Save Twitter messages by following <a href="http://s.evernote.com/myen" target="_blank">@myEN</a></font><br /></font></font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Import photos from your digital camera</font></li></ul></div><div><font face="Arial, Helvetica, sans-serif"><font size="2"><br /></font></font></div><div><font face="Arial, Helvetica, sans-serif"><font size="2"><br /></font></font></div><div><font face="Arial,Helvetica,sans-serif" size="2">Interested in getting even more out of Evernote? Check out <a href="http://s.evernote.com/premium">Evernote Premium »</a></font></div><div><br /></div><a href="http://www.evernote.com/about/premium"></a></td><td width="30"></td><td valign="top" width="180"><font><font color="#000000" size="3"><font size="3"><b>Click the link to install Evernote to your computer:</b></font></font></font><ul><li><font face="Arial,Helvetica,sans-serif" size="2"><a href="http://www.evernote.com/about/download/#Windows">Windows</a></font></li><li><font face="Arial,Helvetica,sans-serif" size="2"><a href="http://www.evernote.com/about/download/#Mac">Mac</a></font></li><li><font face="Arial,Helvetica,sans-serif" size="2"><a href="http://www.evernote.com/about/download/#Webclipper">Web browser</a></font></li></ul><font><font color="#000000" size="3"><font size="3"><b>Click the link to install Evernote onto your mobile device:</b></font></font></font><ul><li><font face="Arial, Helvetica, sans-serif"><font size="2"><a href="http://www.evernote.com/about/download/#iPhone">iPhone / iPod</a><br /></font></font></li><li><font face="Arial, Helvetica, sans-serif"><font size="2"><a href="http://www.evernote.com/about/download/ipad.php">iPad</a><br /></font></font></li><li><font face="Arial, Helvetica, sans-serif"><font size="2"><a href="http://www.evernote.com/about/download/android.php">Android</a><br /></font></font></li><li><font face="Arial,Helvetica,sans-serif" size="2"><a href="http://www.evernote.com/about/download/#BlackBerry">BlackBerry</a></font></li><li><font face="Arial,Helvetica,sans-serif" size="2"><a href="http://www.evernote.com/about/download/#PalmPre">Palm Pre</a></font></li><li><font face="Arial,Helvetica,sans-serif" size="2"><a href="http://www.evernote.com/about/download/#WinMo">Windows Mobile</a></font></li></ul><p><font><font color="#000000" size="3"><font size="3"><b>Get the latest news</b></font></font></font></p><ul><li><font face="Arial,Helvetica,sans-serif" size="2">Read our <a href="http://s.evernote.com/blog" target="_blank">blog</a></font></li><li><font face="Arial, Helvetica, sans-serif"><font size="2"><font face="Arial,Helvetica,sans-serif" size="2">Follow us on <a href="http://s.evernote.com/tweet" target="_blank">Twitter</a></font><br /></font></font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Be a <a href="http://s.evernote.com/fbook" target="_blank">Facebook fan</a></font></li><li><font face="Arial,Helvetica,sans-serif" size="2">Check out our <a href="http://s.evernote.com/tips" target="_blank">tips blog</a></font></li></ul><a href="http://evernote.tumblr.com/" target="_blank"></a></td></tr></tbody></table></en-note>]]></content>
    <created>20091019T224837Z</created>
    <updated>20100409T043136Z</updated>
    <note-attributes>
        <latitude>37.32142900000000</latitude>
        <longitude>-122.01579099999999</longitude>
        <source>web.clip</source>
        <source-url>http://www.evernote.com</source-url>
    </note-attributes>
</note>

I tried using different fields in GnuCash and settled on using the title, date fields, note-attributes/source fields and of course, the tags.
GnuCash didn’t take kindly to me adding my own namespace prefix and nodes to its data, so I decided to use the <split:memo> nodes to hold the tags.  Here’s the stylesheet to transform my GnuCash XML file into an Evernote .enex file (I snipped some of the namespace prefix declarations; just copy them from the GnuCash file):

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:gnc="http://www.gnucash.org/XML/gnc"
    xmlns:act="http://www.gnucash.org/XML/act"
    xmlns:trn="http://www.gnucash.org/XML/trn"
    xmlns:vendor="http://www.gnucash.org/XML/vendor">

    <!--
        Transform GnuCash XML into .enex (Evernote XML) notes,
        tagging the notes using expense account names.
    -->
    <xsl:output method="xml" indent="yes" cdata-section-elements="content" version="1.0" doctype-system="http://xml.evernote.com/pub/evernote-export.dtd" />
    <xsl:param name="source-application">gc2enex.xsl</xsl:param>

    <xsl:attribute-set name="root-attr">
        <xsl:attribute name="export-date"></xsl:attribute>
        <xsl:attribute name="application">Evernote/Windows</xsl:attribute>
        <xsl:attribute name="version">4.x</xsl:attribute>
    </xsl:attribute-set>

    <xsl:import href="strings.xsl" />
    <xsl:param name="acts" select="/gnc-v2/gnc:book/gnc:account" />

    <xsl:template match="@* | node()">
        <!-- catch anything we don't want -->
    </xsl:template>

    <xsl:template match="/">
        <xsl:element name="en-export" use-attribute-sets="root-attr">
            <xsl:apply-templates select=".//trn:split[split:account = $acts[act:type = 'EXPENSE']/act:id]" />
        </xsl:element>
    </xsl:template>

    <!--
        Select transaction split rows
    -->
    <xsl:template match="trn:split">

        <!-- Parse memo field without tags -->
        <xsl:variable name="thisMemo" select="concat (
            substring-before (split:memo, '#tags '),
            substring (split:memo, 1, string-length (split:memo) * number (not (contains (split:memo, '#tags '))) ) )" />

        <xsl:if test="string-length (../../trn:description) > 0 or string-length( translate( $thisMemo, ' ', '' )) > 0">
            <xsl:element name="note">

                <xsl:element name="updated"><xsl:apply-templates select="../../trn:date-entered/ts:date" /></xsl:element>
                <xsl:element name="created"><xsl:apply-templates select="../../trn:date-posted/ts:date" /></xsl:element>

                <xsl:element name="title">
                    <xsl:text><xsl:value-of select="../../trn:description"/></xsl:text>
                    <xsl:if test="../../trn:description and string-length( translate( $thisMemo, ' ', '' )) > 0"><xsl:text> - </xsl:text></xsl:if>
                    <xsl:text><xsl:value-of select="$thisMemo"/></xsl:text>
                </xsl:element>
                <!--
                    Output tag elements keeping existing ones if already in memo, otherwise
                    create tags for this note from the account name and its parent accounts
                -->
                <xsl:choose>
                    <xsl:when test="string-length (substring-after (split:memo, '#tags ' )) > 0">
                        <xsl:call-template name="get-tags">
                            <xsl:with-param name="str" select="normalize-space (substring-after (split:memo, '#tags ' ))" />
                        </xsl:call-template>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:apply-templates select="$acts[act:id = current()/split:account]" mode="get-tags"/>
                    </xsl:otherwise>
                </xsl:choose>

                <xsl:variable name="creditEntry" select="../trn:split[starts-with (split:value, '-') ]"/>

                <xsl:element name="note-attributes">
                    <xsl:element name="source"><xsl:value-of select="$acts[act:id = $creditEntry/split:account]/act:name"/></xsl:element>
                    <xsl:element name="source-url"><xsl:value-of select="split:id"/></xsl:element>
                    <xsl:element name="source-application"><xsl:value-of select="$source-application"/></xsl:element>
                </xsl:element>
                <xsl:element name="content">
<![CDATA[<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">
<en-note>
<div id="]]><xsl:value-of select="../../trn:id" /><![CDATA[">]]><xsl:value-of select="../../trn:description" /><![CDATA[</div>
<div id="]]><xsl:value-of select="split:id" /><![CDATA[">]]><xsl:value-of select="$thisMemo" /><![CDATA[</div>
</en-note>]]>
                </xsl:element>
            </xsl:element>
        </xsl:if>
    </xsl:template>

    <xsl:template match="gnc:account" mode="get-tags">
        <!-- Make tags for all but the ROOT account -->
        <xsl:if test="act:parent">
            <xsl:call-template name="get-tags">
                <xsl:with-param name="str" select="normalize-space (translate (./act:name, '-&lt;()&amp;', ' '))" />
            </xsl:call-template>
            <xsl:apply-templates select="$acts[act:id = current()/act:parent]" mode="get-tags"/>
        </xsl:if>
     </xsl:template>

    <xsl:template name="get-tags">
        <xsl:param name="str" />
        <xsl:variable name="tag" select="concat(substring-before ($str, ' '), substring ($str, 1, string-length ($str) * number (not (contains ($str, ' ')))))" />
        <xsl:if test="(string-length ($tag) > 1) and $tag != 'and' and $tag != 'to' and $tag != 'the'">
            <xsl:element name="tag">
                <xsl:call-template name="str-tolower">
                    <xsl:with-param name="str">
                        <xsl:choose>
                            <xsl:when test="contains ($str, ' ')">
                                <xsl:value-of select="substring-before ($str, ' ')" />
                            </xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="$str" />
                            </xsl:otherwise>
                        </xsl:choose>
                    </xsl:with-param>
                </xsl:call-template>
            </xsl:element>
        </xsl:if>
        <xsl:if test="contains ($str, ' ')">
            <xsl:call-template name="get-tags">
                <xsl:with-param name="str" select="substring-after ($str, ' ')" />
            </xsl:call-template>
        </xsl:if>
    </xsl:template>

    <!-- TODO DO FANCY DATE MATH SO THE TIMEZONE IS IN EFFECT, OTHERWISE IT'S SHIFTING MY DATE AHEAD 7HRS (WHICH IS A DAY) -->
    <xsl:template match="ts:date">
        <xsl:value-of select="concat(translate(substring-before(., ' -'), ' -:', 'T'), 'Z')" />
    </xsl:template>

</xsl:stylesheet>

strings.xsl is utility stylesheet that contains, among other things, the str-tolower template which just does a translate from uppercase characters to lowercase.

If this is run against the GnuCash XML file, we get an XML document in the .enex format suitable for importing into Evernote:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE en-export SYSTEM "http://xml.evernote.com/pub/evernote-export.dtd">
<en-export export-date="" application="Evernote/Windows" version="4.x">
    <note>
        <updated>20110103T130243Z</updated>
        <created>20110103T000000Z</created>
        <title>Hobby Lobby fix - picture glass</title>
        <tag>home</tag>
        <tag>repair</tag>
        <tag>expense</tag>
        <note-attributes>
            <source>Chase MC</source>
            <source-url>bd2263d811355b09056c503949201174</source-url>
            <source-application>gc2enex.xsl</source-application>
        </note-attributes>
        <content><![CDATA[
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">
<en-note>
<div id="a1ddec4b4fe26e84745a8cddac018620">Hobby Lobby</div>
<div id="bd2263d811355b09056c503949201174">fix picture glass</div>
</en-note>]]>
        </content>
    </note>
    ...

I didn’t get too fancy with what I’m copying over.  The expense account “Home Repair” was used to create tags for the note.  The split’s id is saved in the source-url node; it will be used to merge back to GnuCash.  Go to Evernote and import the output file from the transform, and it looks like:

Our GnuCash transactions imported to Evernote

You can see all the tags that were created in Evernote from the expense account names.  Here, I’ve just selected “home”.  I will add a tag (“foobar”) to this note, export the notes and merge them back into GnuCash.

I added "foobar" to this note

After exporting from Evernote, this note’s tags look like:

<tag>home</tag><tag>repair</tag><tag>expense</tag><tag>foobar</tag>

Now I’ll run the other transform to merge the original GnuCash XML with the exported Evernotes (I snipped a bunch of namespace declarations again):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:gnc="http://www.gnucash.org/XML/gnc"
xmlns:act="http://www.gnucash.org/XML/act"
xmlns:vendor="http://www.gnucash.org/XML/vendor">

    <xsl:import href="strings.xsl" />

    <!--
        Copies a GnuCash XML file, merging the tags from .enex (Evernote XML) notes, to create a new GnuCash
        XML file with tags in memo fields. Allowing round trips between the 2 applications
    -->
    <xsl:output indent="yes" method="xml" version="1.0" />
    <xsl:param name="enex-file"/>
    <xsl:param name="enex" select="document($enex-file)//note"/>

    <!-- IdentityTransform the GnuCash file for mostly an exact copy-->
    <xsl:template match="/ | @* | node()">
        <xsl:copy>
            <xsl:apply-templates select="@* | node()" />
        </xsl:copy>
    </xsl:template>

    <!--
        We capture the whole split, not just an existing split:memo
        because we might have to add the memo if none existed before
        and we have tags
    -->
    <xsl:template match="trn:split">
        <xsl:copy>
            <xsl:apply-templates select="@*"/>
            <xsl:copy-of select="*[name() != 'split:memo']" />

            <xsl:variable name="thisMemo" select="concat (
                substring-before (split:memo, '#tags '),
                substring (split:memo, 1, number (not (contains (split:memo, '#tags ' ))) * string-length (split:memo) ))"/>

            <xsl:if test="$thisMemo or $enex[note-attributes/source-url = current()/split:id]/tag">
                <xsl:element name="split:memo">
                    <xsl:text><xsl:value-of select="$thisMemo"/></xsl:text>
                    <xsl:if test="$enex[note-attributes/source-url = current()/split:id]/tag">
                        <xsl:text> #tags</xsl:text>
                        <xsl:for-each select="$enex[note-attributes/source-url = current()/split:id]/tag">
                            <xsl:text> <xsl:value-of select="."/></xsl:text>
                        </xsl:for-each>
                    </xsl:if>
                </xsl:element>
            </xsl:if>
        </xsl:copy>
    </xsl:template>

</xsl:stylesheet>

The enex-file param has to be set to point at the exported .enex file (the main input is the original GnuCash XML file).  Now open the merged output in GnuCash:

Tags from Evernote have been added to the memo fields

When going from GnuCash to Evernote, the first XSL will respect the #tags section of the memo fields and not reapply tags from the account names.  This allows us to edit the tags in either application (if you change them in GnuCash, Evernote will create new tags when importing) and the changes will be preserved going in either direction.

Advertisements

One response to “Importing GnuCash Data Into Evernote with XSL

  1. fritz schenk April 13, 2013 at 10:08 pm

    I think your ‘resume’ is extraordinaire. I likewise have ‘dealt’ solutions in non-linear and creative ways.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: