Friday, October 18, 2013

GnuCash: Macros?

I love GnuCash. GnuCash is the best.

But it could be better.

BLUF: we need a macro facility. Something to do simple manipulations. No invoking international or GAAP. Just a way of automating simple repetitive tasks.

For example:

http://list-archives.org/2012/10/12/gnucash-user-gnucash-org/buildin-currency-exchange/f/6417193929

How can I enter a transaction in a foreign currency and have it converted
automatically to my home currency?

For example, in a USD account (e.g., {Visa-USD}) enter a GBP amount (e.g. GBP 83.22) and have it converted to USD with the current rate recorded in the Price Editor?

The price editor correctly updates exchange rates, so I believe I have the
necessary modules installed, but entering "GBP 83.22" or a host of other
variants returns an error. I am relatively certain that I remember being
able to do that in the past, but perhaps I'm disremembering Quicken... :-(


Well, I never got a reply, except that it is hard to do.

OK, said the little red hen, I'll do it myself. So I did. Easy when you know how.

All you have to do is:

1. Go to a foreign currency account, e.g., {Cash-GBP}, that is denominated in the foreign currency, e.g.,GBP.

2. Enter all your splits in GBP: {Food}, {Supplies}, {Drinks}, etc.

3. Multiply all your splits by 1.01 (insert *1.01 at the end of each split value and move to the next split) to accommodate the "Foreign Transaction Fee" that the bank will charge you.

4. If it is an ATM transaction add a split for your {Financial Misc} account with the value of the inverse of the exchange rate (e.g., £0.60) as a placeholder for the one dollar ATM fee the bank will charge.

5. After everything is set correctly, change the {Cash-GBP} split account to {Checking-NFCU} (or whatever your home currency (e.g., USD) checking or credit card account is called) and arrow down to the next split line.

     a. This will invoke a currency exchange dialog box to update the exchange rate. When you click OK and accept the transaction it will disappear from {Cash-GBP}  and move to the {Checking-NFCU} account.

6. Go to {Checking-NFCU} to check the result.

Easy when you know how...

=====

Now, when it comes to balancing the checkbook, things are only a little more complicated. The process above will give you pretty close ballpark numbers in your home-denominated account, but time delays may result in a few pennies' worth of exchange rate differences.

That is where the macros would really come into use, since you probably want to redistribute those pennies to the various splits if you are really a nut for detail. (Otherwise, just chuck them into {Financial Misc} and be done.)

But no macros. So what to do?

I then wield the faithful calculator, an HP-35s in my case.  A tutorial on Reverse Polish Notation (RPN) is beyond the scope of this article but easily available with the link and this manual:
http://h10010.www1.hp.com/wwpc/pscmisc/vac/us/product_pdfs/user_guide.pdf

I use two routines:

     • LBL I uses the Imbalance to calculate the Original transaction amount and save the current transaction Total (the transaction amount reported by the bank).

     • LBL D then calculates the ratio of the original split to the Original total and multiplies it by the current total to give the redistribution value.

Open the transaction and change the checking account value to what the bank has reported and arrow down (don't press Enter or you will get an imbalance split). This will result in an undesignated imbalance. Now with the calculator:

LBL I

<Imbalance>
ENTER
<Bank transaction amount>
XEQ I

This stores the current bank Total in register T, returns the Original total, and stores it in register O:

        I001  LBL I       
            I002  STO T    Save the current Total
            I003  x<>y      Flip the values
            I004  -            Get the Original total
            I005  STO O    Store the Original total
        I006  RTN

LBL D


<Split value>
XEQ D

This saves the split Value in register V, divides it by the Original total to get the ratio, recalls the current Total and multiplies it by the ratio to return the reproportioned Distribution amount and save it in register D:

        D001 LBL D       
            D002  STO V    Save the Value
            D003  RCL O    Recall the original total
            D004  ÷            Divide to get this item's percentage of total
            D005  RCL T    Recall the new total
            D006  x            Multiply to get the new proportional value
            D007  STO D    Save the Distribution
        D008  RTN       

Yes, it was fun figuring all this out, but how much nicer it would be to just click a button...

No comments: