Instructions to use a import GnuCash data into a spreadsheet with a PostgreSQL backend

[Mark Sluser]

If you use PostgreSQL to store your data instead of the usual XML  
file, you can use a spreadsheet to filter your data in a way that the  
GnuCash reporting engine won't let you.

This requires
1. Preparing a PostgreSQL backend
2. Modifying the gnucash database
3. Importing the data into a spreadsheet

1. Prepare a PostgreSQL database backend
--------------------------------------------------------

1. Download the latest version of GnuCash that supports a database  
backend (as of today this would be 2.3.2 unstable) and install it.
2. Download PostgreSQL from www.postgresql.org. You will need at least  
8.4 (that is the latest stable release to date) and install it.
3. Configure PostgreSQL with all the defaults. The documentation is  
pretty thorough.
4. Open the PGAdmin3 database administration tool and create a new  
database called 'gnucash' under the postgres username. Use a UTF-8  
text encoding.
5. Start GnuCash and open your existing XML file.
6. Save your data to the database by going File->Save As
7. Select postgresql from the destination list and enter the settings  
you used when setting up postgresql (such as username and password).  
Here is what I used:
	host:		localhost
	database:	gnucash
	username:	postgres
	password:	{your password here}
8. Wait for the database to be saved. Now it is available for viewing  
in the PGAdmin3 database administration tool.

Most of the above has been mentioned before in other newsgroup  
discussions. Now the interesting part.
Initially I used the OpenOffice extension  'gnuc2ooo' to import my  
data into openoffice base. The OO extension can be found here http://www.alice-dsl.net/gnuc2ooo/gnuc2ooo_en/intro.html 
.
This macro provided access to my data in a nice table of transactions  
and I could filter it using the usual spreadsheet tools. However every  
time I made a change to my data, I had to re-run the gnuc2ooo import  
macro, and it was pretty slow for my data (my XML file is 14.5 MB).   
Once gnucash released their database backend version I figured I could  
build a nice table of transactions using a SQL query. This would give  
me access to any changed in my data immediately and make storing my  
14.5MB of data way more efficient.

The format of the transaction table provided in gnuc2ooo made it easy  
to filter gnucash's account tree heirarchy because it created a column  
for each level in the tree.
This meant that
LEVEL1 had the accounts Assets, Income, Equity, Expenses, and  
Liabilities.
LEVEL2 had sub accounts such as 'Accounts Payable', 'Accounts  
Receivables', Sales, Groceries, Property Taxes, Visa, Utilities, etc.
LEVEL3 had sub accounts such as Gas, Electricity, Cable, Water, etc.

So If I wanted to see the transactions were for utilities, I would  
filter LEVEL1=Expenses and LEVEL2=Utilities. If I wanted to see what  
transactions I had for Electricity, I would add LEVEL3=Electricity.  
And if you sum the amount column, you can easily get totals for reports.
This makes the data really simple and easy to work with.

2. Modifying the gnucash database
-----------------------------------------------

To get the same Level1, Level2, etc. type columns in PostgreSQL you  
need to create 3 things in the gnucash database.
1. A trigger function -- plpgSQL script to create a new accounts table  
called accounts_tree which will contain the extra information required  
to produce a nice table view.
2. A trigger -- tells PostgreSQL when to execute the Trigger function.  
It must be associated with a table.
3. A View -- this will be the table view that the spreadsheet can  
connect to.


2.1. Add a Trigger Function
-----------------------------------
Trigger functions are usually written in PostgreSQL's procedural  
language called plpgSQL and this one is too.
Open the PgAdmin3 tool and expand the tree to gnucash->schemas->public- 
 >Trigger Functions
Right click on Trigger Functions and select "New Trigger Function ..."
Set the following properties:
name:		build_acct_tree
owner:		postgres
language:	plpgsql

Select the definition tab and past the following code:


DECLARE
	d integer := 0;
	txt_lev_guid TEXT := 'lev_guid';
	txt_lev_name TEXT := 'lev_name';
	txt_exec TEXT;
	rs RECORD;
BEGIN

DROP TABLE IF EXISTS accounts_tree;

CREATE TABLE accounts_tree(guid character varying, parent_guid  
character varying,
	name character varying, depth integer, lineage character varying[]);

INSERT INTO accounts_tree (guid, parent_guid, name)
	SELECT guid, parent_guid, name FROM accounts;

FOR rs IN
	SELECT DISTINCT accounts.parent_guid
		FROM accounts LEFT JOIN accounts A2
		ON accounts.parent_guid = A2.guid
		WHERE A2.guid IS NULL
LOOP

UPDATE accounts_tree
	SET lineage=array_append('{}',rs.parent_guid), depth=0
	FROM accounts
	WHERE accounts_tree.parent_guid = rs.parent_guid;

END LOOP;


EXECUTE
'ALTER TABLE accounts_tree
	ADD COLUMN '|| txt_lev_guid || d || ' character varying ,
	ADD COLUMN '|| txt_lev_name || d || ' character varying';

EXECUTE '
UPDATE accounts_tree
	SET ' || txt_lev_guid || d || ' = lineage['|| d ||'+1]
	WHERE accounts_tree.depth >= ' || d ;

EXECUTE '
UPDATE accounts_tree
	SET '|| txt_lev_name || d || ' = accounts.name
	FROM accounts
	WHERE accounts_tree.'|| txt_lev_guid || d || ' = accounts.guid';

WHILE (EXISTS (SELECT * FROM accounts_tree WHERE depth Is Null)) LOOP

d := d + 1;

UPDATE accounts_tree
SET depth = A2.depth + 1,
lineage = array_append(A2.lineage,  accounts_tree.parent_guid)
FROM accounts_tree as A2
WHERE accounts_tree.parent_guid=A2.guid
AND A2.depth>=0
AND A2.lineage Is Not Null
AND accounts_tree.depth Is Null;


EXECUTE
'ALTER TABLE accounts_tree
    ADD COLUMN '|| txt_lev_guid || d || ' character varying ,
    ADD COLUMN '|| txt_lev_name || d || ' character varying';

txt_exec := 'UPDATE accounts_tree SET ';
txt_exec := txt_exec || txt_lev_guid || '0 = lineage[1] ';

FOR i IN 1..d LOOP
	txt_exec := txt_exec || ',' || txt_lev_guid|| i || ' = lineage['|| i  
||'+1] ';
END LOOP;

txt_exec := txt_exec || 'WHERE accounts_tree.depth >= ' || d ;

EXECUTE txt_exec;

END LOOP;

FOR i IN 0..d LOOP
EXECUTE '
UPDATE accounts_tree
	SET '|| txt_lev_name || i || ' = accounts.name
	FROM accounts
	WHERE accounts_tree.'|| txt_lev_guid || i || ' = accounts.guid';
END LOOP;


RETURN NEW;

END;


Select OK and you should see the new trigger function appear in the  
tree view.

2.2. Add a PostgreSQL Trigger
---------------------------------------

Navigate the tree view to gnucash->schemas->public->Tables->accounts- 
 >Triggers
Right click on Triggers and select "New Trigger"

Set the following properties:
name:			build_tree
trigger function:	build_acct_tree
Fires:			AFTER
Events:			INSERT, UPDATE, DELETE (all selected)

Click OK and you should see the new trigger appear in the tree view.


2.3. Add a PostgreSQL View
------------------------------------
Navigate the tree view to gnucash->schemas->public->Tables->Views
Right click on Views and select "New View..."

Set the following properties:
name:		transactions_tree
owner:		postgres

Select the definition tab and past the following code:

SELECT
   accounts.account_type,
   accounts_tree.lev_name0,
   accounts_tree.lev_name1,
   accounts_tree.lev_name2,
   accounts_tree.lev_name3,
   accounts_tree.lev_name4,
   accounts_tree.lev_name5,
   accounts_tree.lev_name6,
   accounts."name",
   substring(transactions.enter_date FROM 1 FOR 4) AS integer) || '-'
   substring(transactions.enter_date FROM 5 FOR 2) AS integer) || '-'
   substring(transactions.enter_date FROM 7 FOR 2) AS integer) AS  
'date',

   CAST(substring(transactions.enter_date FROM 1 FOR 4) AS integer) AS  
yyyy,
   CAST(substring(transactions.enter_date FROM 5 FOR 2) AS integer) AS  
mm,
   CAST(substring(transactions.enter_date FROM 7 FOR 2) AS integer) AS  
dd,
   transactions.num,
   transactions.description,
   splits.memo,
   CAST(splits.quantity_num AS decimal)/splits.quantity_denom AS  
quantity_num,
   CAST(splits.value_num AS decimal)/splits.value_denom AS value_num
FROM
   public.accounts,
   public.accounts_tree,
   public.splits,
   public.transactions
WHERE
   splits.account_guid = accounts.guid AND
   splits.account_guid = accounts_tree.guid AND
   splits.tx_guid = transactions.guid;


Click OK and you should see the new view appear in the tree view.
Now the database is ready to be queried and viewed in your spreadsheet.

3. View the data in a spreadsheet
------------------------------------------------------------------
I am writing this for windows people using Microsoft Excel.

3.1. Create a data connection
-------------------------------------
Because I am writing this for windows the data connection will have to  
be an ODBC data connection. When you installed PostgreSQL on your  
computer you may not have also installed the ODBC driver for windows.
To install the driver start the PostgreSQL 'Application Stack Builder'  
which should be available from Start->Programs->PostgreSQL 8.4- 
 >Application Stack Builder.
Hopefully you can manage installing the driver once you see the stack  
builder.

To setup the ODBC data connection open the ODBC configuration tool  
located at Start->Control Panel->Administrative Tools->Data  
Sources(ODBC)
Under the User tab, click Add.
Scroll to the bottom of the list and select 'PostgreSQL Unicode' and  
click Finish
Set the following properties:
datasource:		PostgreSQL_GnuCash
description:		PostgreSQL to GnuCash
database:		gnucash
SSL Mode:		disable
server:			localhost
port:			5432
username:		postgres
password:		{your password here}

Click the test button to ensure the connection works.
If the test fails, check username, password and the port. Port 5432 is  
the default.
Note: if your database exists on another server, you will have to edit  
the pg_hba.conf file on the server to allow remote connections.

Click Save and then Ok to close the ODBC configuration tool.


3.2. Insert the data into a spreadsheet
-------------------------------------------------
Most spreadsheets programs have detailed instructions on how to  
connect to a database in their help systems.
I have Office 2007 for windows so here is what I did:

Click the Data Tab
Click 'From External Sources'
Click 'From Data Connection Wizard'
Select 'ODBC DSN' and click Next
Select 'PostgreSQL_GnuCash' and click Next
Select 'transactions_tree' and click Next
Click Finish

Import Data window appears
Click OK
The transactions data will fill your worksheet

Now you can use column filters and a totals row(Excel 2007 feature) to  
get totals of transactions that were not possible with the GnuCash  
reporting engine.

Enjoy.



Mark Sluser
Calgary, AB