GnuCash: Stock and Currencies

This is probably a post that can be categorized as a first-world problem, but it’s something that I found difficult to figure out by myself, and I thought it may be interesting to other free software users who happen to work for a company that provides stock grants as a form of compensation. As it happens, I’m one of them.

Many years ago I set up my accounting through GnuCash, because I needed something to keep track of the expenses of my company, back when I had one. Being stubborn and pedantic, now four years after closing said company, I still keep running it and I have a nearly complete accounting of my expenses, for sure from 2009 (time from which I started recording all movements on the credit cards) and some before — I actually manually imported all operations on my bank account from its opening in 2005!

One thing that was not very clear to me how to handle, as setting up proper accounts to track the compensation in form of stock. The as usual unnamed company I work for provides me with a part of compensation in form of stock grants, as long as I work for them. The tax implications of these grants are a bit complicated, but boil down to needing to know the “fair market value” (FMV) of the stock at vesting, and the exchange rate for that month on the Irish Revenue website, since the stock is valued in dollars, but my taxes are in Euro (and do note that it does not matter when you transfer that money, as far as I can tell, which is both a blessing and a curse of course).

For this description to work, your GnuCash file needs to be set to Use Trading Accounts, as that makes it much nicer to have multiple currencies and securities in the same transaction. You also need to create a new security (symbol), a new income account and a new stock account. For the first you select Tools → Security Editor → Add, the two others can be created by right clicking on the Income and Stock accounts respectively, and choosing New Account…:

Creating a new Security for Free Software Foundation, with symbol GNU and type BAZAAR.

Creating a new Stock Grants income with USD as currency.

Creating a new FSF stock account with GNU as the security.

Once you open the newly-created stock account, it will show you a number of column as usual for GnuCash, but in particular it’ll have the stock-specific Price, Buy, and Sell column, which are used for trading. What was definitely not originally clear to me before was that the price is always expressed in the default currency of the GnuCash file, which as far as I know is not editable.

You can of course manually calculate the value at time of vesting by using the FMV and the exchange rate, but it becomes quite more complicated in my opinion. So instead, whenever one of my grants vests, I’m now going to the income account to record it. This account is denominated in dollars, which match the symbol’s currency, and we’ll use it to add new stock to the stock (or asset) account.

Say for example as of today I received a grant of two stock units at FMV of $1337 (note I expanded the splits of the line, that is on purpose because it makes it easier to edit):

Adding an Income entry of $1337*2 in the Stock Grants account.

You then add a second split that points to the stock account created previously and insert the number of stock units (2) in that Charge. Again this only works correctly if you’re using trading accounts! Once you do that, the Transfer Funds dialog will pop up and it will have already the right “Exchange Rate” (which is actually the FMV at vesting time).

Transfer Funds dialog for an income of 2 FSF stock units.

Up to now this is all good, now you have some stock, and the Buy field is actually showing the right price in dollars, but the account’s value is not going to be aggregated correctly in the Accounts page. This is because up until now we have made no effort of dealing with the exchange rate between USD and EUR.

The easiest way to fix this is going to Tools → Price Editor, select the symbol and add a new price (I use Net Asset Value for type), and in the price divide the value in dollar for the right exchange rate.

Price Editor dialog setting the price of GNU to 1337/1.0555 EUR

Et voilà! Now you should have most of the data you need not only to keep an eye on the stock grants, but also to do tax reporting as you have recorded the FMV already converted to Euro, which is what you need there. Unfortunately, as far as I can tell, there is no easy way to export the prices in a nice table that you can just use for reporting. And I’m horrible with Scheme so I have not considered writing my own reporting module yet.

There is also another caveat: I have tried to track the grants already in two different ways in the past, and they did not work out very well. I’m seriously considering making a copy of my GnuCash file, delete the accounts altogether and recreate them following this simplified procedure. But that will probably be a bridge for another time.

Help request: extending Finance::Quote

It’s not very common for me to ask explicit help with writing new software, but since this is something that I have no experience with, in a language I don’t know, and not mission-critical for any of my jobs, I don’t really feel like working on this myself.

Since right now I not only have a freelancing, registered job, but I also have to take care of most, if not all, house expenses, I’ve started keeping my money in check through Gnucash as I said before. This makes it much easier to see how much (actually, little) money I make and I can save away or spend on enjoying myself from time to time (to avoid burning out).

Now, there is one thing that bothers me: to save away the money that I owe the government as taxes (both VAT I have to pay, and extra taxes) I subscribed to a security fund, paying regularly (if I have the money available, of course!); unfortunately I need to explicitly go look up the data on my bank’s website to know exactly how much money I have stashed in there at any time.

Gnucash obviously have a way to solve this problem, by using Finance::Quote Perl module to fetch the data from a longish series of websites, mostly through scraping. Let’s not even start to talk about the chances that the websites changed their structure in the past months since the 1.17 release of the module (hint: at least one had, since I tried it out manually and it only gets a 404 error), but at last Yahoo, while accepting the ISIN of the fund, doe not give me any data for the current value of the share.

Now, the fund, which is managed by Pioneer Investments and they do provide the data, and via a very simple, ISIN-based, URL! Unfortunately, they provide that data only… in PDF. Now, this does not seem to be too bad: the data is available in text form because pdftotext provides it properly, and it’s clearly marked with the previous line to be a fixed string; on the other hand, I have no idea how it would be possible to scrape a PDF, especially in Perl, and even worse within Finance::Quote!

If somebody feels like helping me out, the URL for the PDF file with the data is the following, and the grep command will tell you what to look for in the PDF’s text. If you can help me out with this I’ll be very glad. Thanks!

# wget '∈=IT0000388204'
# pdftotext pioneer_monetario_euro_a.pdf* - | grep 'Valore quota' -A 2
Valore quota


Health, accounting and backups

For those who said that I have anger management issues regarding my last week’s post I’d like to point out that it’s actually a nervous breakdown that I got, not strictly (but partly) related to Gentoo.

Since work, personal life, Gentoo and (the last straw) taxes all merged this week, I ended up having to take a break from a lot of stuff; this included putting on hold for the week all kind of work, and actually spend most of my time making sure I have proper accounting, both for what concerns my freelancer activity, and home expenses (this is getting particularly important because I’m almost living alone – even if I technically am not – and thus I have to make sure that everything fits into the budget). Thankfully, GnuCash provides almost all the features I need. I ended up entering all the accounting information I had available, dating back to January 1st 2009 (my credit card company’s customer service site hasn’t worked in the past two weeks — since it’s the subsidiary of my own bank, I was able to get the most recent statements through them, but not the full archive of statements since issuing of the cards, which is a problem to me), and trying to get some data out of it.

Unfortunately, it seems like while GnuCash already provides a number of reports, it does not have the kind of reports I have, such as “How much money did the invoices from 2009 consists of?” (which is important for me to make sure I don’t go over the limit I’m given), or “How much money did I waste in credit card interests?”… I’ll have to check out the documentation and learn whether I can make some customised reports that produce the kind of data I need. And maybe there’s a way to set the term of payments that I have with a client of mine (30 days from the end of the month the invoice was issued in… which means if I issue the invoice tomorrow, I’ll be paid on May 1st).

On a different note, picking up from Klausman’s post I decided to also fix up my backup system, which was, before, based on single snapshots of the system on external disks and USB sticks; and moved to use a single rsnapshot system to back everything up in a single external disk, from the local system, the router, the iMac, the two remote servers, and so on. This worked out fine when I tried again the previous eSATA controller I had, but unfortunately it again failed (d’oh!) so I fell back to Firewire 400 but that’s way too slow for rsnapshot to do a full backup hourly. I’m thus trying to find a new setup for the external disk. I’m unsure whether to look up a FireWire 800 card or a new eSATA controller. I’m not sure about Linux’s support for the former though; I know that FireWire used to be not too well maintained, so I’m afraid it might just go down to FireWire 400, which is pointless. I’m not sure about eSATA because I’m afraid it might not be the controller’s fault but rather a problem with (three different kind of) disks or the cables; and if the problem is in the controller, I’m afraid about the chip on it; the one I have here is a JMicron-based controller, but with a memory chip that is not flashable with the JMicron-provided ROM (and I think there might be a fix in there for my problem) nor with flashrom as it is now.

So if you have to suggest an idea about this I’d be happy to hear of it; right now I only found a possibly interesting (price/features) card from Alternate (Business-to-business) “HighPoint RocketRAID 1742” which is PCI-based (I have a free PCI slot right now, and in case I can move it to a different box that has no PCI-E), and costs around €100. I’m not sure about driver support for that though, so if somebody have experience about it, please let me know. Interestingly enough my two main suppliers in Italy seem to not have any eSATA card, and of course high-grade, dependable controllers aren’t found at the nearest Saturn or Mediamarkt (actually, Mediaworld here, but it’s the very same thing).

Anyway, after this post I’m finally back to work on my job.

I dream the paperless office

And I know pretty well it’s something almost impossible to have; yet I’d like to have it because I’m succumbing in a ocean of paper right now. And paperwork as well.

While I have the Sony Reader to avoid having to deal with tons of dead tree books (although I do have quite a bit still, lots of which are still being consulted), I didn’t try before to clean up my archive of receipts, packaging slips, and stuff like that.

Time has come now since I have to keep some fuller, cleaner archive of invoices sent and received for my new activity as a self-employed “consultant”; I decided to scan and archive away (in a plastic box in my garage, that is) the whole of the job papers I had from before, as well as all my medical records, and the remaining parts of the archive. The idea was that by starting anew I could actually start keeping some time of accountability of what I receive, and spend, both for job and for pleasure. Together with the fact that is less stuff to bring around with me, this makes two things that would get me nearer toward actually moving out of home.

Sunday and Monday I spent about eight hours a day scanning and organising documents, trashing all the stuff I’m not interested in keeping an original of (that is stuff I’m glad I can archive, but that even if I lost is not that important), and putting away in the plastic box the important stuff (job and medical records, receipts for stuff that is already in warranty, etc.). I think I got through around 400 pages, on a flatbed scanner, without document feeder, assigning a name to each, and switching scans between 150 and 300 dpi, colour, grayscale and lineart scans.

I guess I’ll try to keep my archive more updated from now on by scanning everything as it arrives instead of waiting for it to pile up for twelve years (yes I got some receipts dating back to twelve years ago, like my first computer, a Pentium 133) and then trying to crunch it away in a few days. My wrist is aching like it never did before, for the sheer amount of sheets put on and removed from the scanner (I sincerely hope it’s not going to give up on me, it would be a bad thing).

Now I’m looking for a way to archive this stuff in a quick and searchable way, file-based structures don’t work that well, tagging the stuff would work better, but I have no idea what to use for that. If anybody has a free software based solution for archiving, that can be queried by the network too is a bonus, that it works on Mac OS X with Spotlight is a huge bonus, I’d be glad to hear it.

I’m also going to try out some software for accountability; I’ve heard good words of gnucash but never tried it before so I’m merging it right now; for now I don’t have enough invoices to send out that would give me reason to start writing my own software, but if there is something out there customisable enough I’d be glad to bite the bullet and get to use it. Spending my free time to work on software I need to work is not my ideal way to solve the problem.

Up to now I only worked very low profile, without having to invoice or keep records; luckily I have an accountant that can tell me what to do, but there are personal matters, including personal debts, credit cards and other expenses I finally want to take a good look at, so that I can extinguish them as soon as possible, and then start putting some away to pay for a car and a places to move to. Not easy to do I guess, but that’s what I hope to be able to do.