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

[Mark Sluser]

There were a couple of errors in my last posting of trigger function  
"build_acct_tree"
1. "accounts_tree" was not able to be deleted/dropped because it was  
in use by view "transactions_tree". This view is now deleted at the  
start of the function and then dynamically added after it is updated.
2. The lev_name0 column was always blank because a root GUID does not  
have a name. This column is now updated to 'ROOT0'

Also there was a problem with the view "transactions_tree"
1. View "transactions_tree" displayed the entered date rather than the  
posted date.


Here is the updated "build_acct_tree" code:



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

DROP VIEW IF EXISTS transactions_tree;

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 rs IN
	SELECT DISTINCT accounts_tree.lev_guid0
		FROM accounts_tree
LOOP

UPDATE accounts_tree
	SET lev0='ROOT' || r
		WHERE accounts_tree.lev_guid0 = rs.lev_guid0;
r := r + 1;
END LOOP;


FOR i IN 1..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;

txt_exec := 'CREATE VIEW transactions_tree AS ';
txt_exec := txt_exec || 'SELECT
   accounts.account_type,';
FOR i IN 0..d LOOP
	txt_exec := txt_exec || '
   accounts_tree.'|| txt_lev_name || i || ',';
END LOOP;


txt_exec := txt_exec || '
accounts."name",
   CAST(substring(transactions.post_date FROM 1 FOR 4) || ''-'' ||
   substring(transactions.post_date FROM 5 FOR 2) || ''-'' ||
   substring(transactions.post_date FROM 7 FOR 2) AS date) AS post_date,

   CAST(substring(transactions.post_date FROM 1 FOR 4) AS integer) AS  
yyyy,
   CAST(substring(transactions.post_date FROM 5 FOR 2) AS integer) AS  
mm,
   CAST(substring(transactions.post_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';


EXECUTE txt_exec;

RETURN NEW;


END;




Enjoy,

-Mark





On 16-Jul-09, at 12:14 AM, Mark Sluser wrote:

> 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
>
>