Difference between revisions of "GnuCash SQL Examples"

From GnuCash
Jump to: navigation, search
(Upgrade the SQL to match all of the GnuCash Price Database columns.)
(Remove obsolete Introduction header; link XML format; use {{URL:GH}})
 
(2 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
[[Category:SQL]]
 
[[Category:SQL]]
== Introduction ==
+
This page shows you how to extract various data from a GnuCash database using SQL statements.<br>
 +
;Note: This will not work if your GnuCash data is stored in the default [[GnuCash XML format‎|XML format]], you must "Save As" and choose one of the supported database formats.
 +
 
 +
== Other Samples and Tools==
 +
the piecash project offers some example sqlite based examples files at which you can try out.
 +
{{URL:GH}}sdementen/piecash/tree/master/gnucash_books
  
This page shows you how to extract various data from a GnuCash database using SQL statements.<br>
+
With a tool such as {{URL:GH}}coleifer/sqlite-web you can interactively try the examples shown on this page.
'''NOTE''' This will not work if your GnuCash data is stored in the default XML format, you must "Save As" and choose one of the supported database formats.
 
  
== Template ==
+
== Wiki "Template" ==
''(Use this template to add new sections.)''
+
''(Use this template to add new SQL sections.)''
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
  
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Create a read-only account for the database ==
 
== Create a read-only account for the database ==
 
If you use the account, which is also used by gnucash, you will be alerted every time gnucash starts up, when it can not get exclusive write permission. This normally makes sense, but we do not want to edit the data anyway. So it's better to create a read-only account first. The example uses postgres, but other database systems are identical.
 
If you use the account, which is also used by gnucash, you will be alerted every time gnucash starts up, when it can not get exclusive write permission. This normally makes sense, but we do not want to edit the data anyway. So it's better to create a read-only account first. The example uses postgres, but other database systems are identical.
 
 
<syntaxhighlight lang="sh">
 
<syntaxhighlight lang="sh">
 
sudo su postgres # switch to the postgres account to have admin rights in the database
 
sudo su postgres # switch to the postgres account to have admin rights in the database
Line 27: Line 29:
 
gnucash=# GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO gnucashdbreader; -- You must be connected to the database gnucash, while doing this. PUBLIC is context specific and refers to the current database
 
gnucash=# GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO gnucashdbreader; -- You must be connected to the database gnucash, while doing this. PUBLIC is context specific and refers to the current database
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Request all transactions like shown in the account list ==
 
== Request all transactions like shown in the account list ==
 
 
This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed.
 
This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed.
 
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
 
select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
Line 44: Line 43:
 
where a1.name!=a2.name;
 
where a1.name!=a2.name;
 
</syntaxhighlight>
 
</syntaxhighlight>
 
+
To get the right amounts correctly signed adjusted by the factor for the currency, we have to use this sql-querry: <syntaxhighlight lang="sql">
To get the right amounts correctly signed adjusted by the factor for the currency, we have to use this sql-querry:
 
 
 
<syntaxhighlight lang="sql">
 
 
select distinct CAST((CAST(-s2.value_num as DECIMAL)/s2.value_denom) as DECIMAL(10,2)) as amount, t1.post_date as date , t1.description,
 
select distinct CAST((CAST(-s2.value_num as DECIMAL)/s2.value_denom) as DECIMAL(10,2)) as amount, t1.post_date as date , t1.description,
 
a1.description, a1.name, a1.account_type,
 
a1.description, a1.name, a1.account_type,
Line 58: Line 54:
 
where a1.name!=a2.name
 
where a1.name!=a2.name
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Request expenses for a certain period and category ==
 
== Request expenses for a certain period and category ==
 
+
If you just want to know, how much you spend on certain things during some period, you can use this querry: <syntaxhighlight lang="sql">
If you just want to know, how much you spend on certain things during some period, you can use this querry:
 
 
 
<syntaxhighlight lang="sql">
 
 
SELECT SUM(CAST(-CAST(s2.value_num AS DECIMAL(10,2))/s1.value_denom AS DECIMAL(10,2))) AS amount
 
SELECT SUM(CAST(-CAST(s2.value_num AS DECIMAL(10,2))/s1.value_denom AS DECIMAL(10,2))) AS amount
 
FROM splits s1
 
FROM splits s1
Line 73: Line 65:
 
WHERE a1.name!=a2.name AND t1.post_date >= '<some start date>' AND t1.post_date <= '<some end date>' AND a1.name = '<category>';
 
WHERE a1.name!=a2.name AND t1.post_date >= '<some start date>' AND t1.post_date <= '<some end date>' AND a1.name = '<category>';
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Return an account guid by account path or account path by guid ==
 
== Return an account guid by account path or account path by guid ==
 
 
This returns a guid of an account by its path. The path must unlike in the GnuCash frontend start with 'Root Account'.  
 
This returns a guid of an account by its path. The path must unlike in the GnuCash frontend start with 'Root Account'.  
 
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
with recursive path_list as (
 
with recursive path_list as (
Line 100: Line 89:
 
select * from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';
 
select * from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Request all child accounts of a given account ==
 
== Request all child accounts of a given account ==
 
 
This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from [https://www.sql-workbench.eu/comparison/recursive_queries.html sql-workbench].
 
This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from [https://www.sql-workbench.eu/comparison/recursive_queries.html sql-workbench].
 
 
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
with recursive all_nested_accounts as (
 
with recursive all_nested_accounts as (
Line 122: Line 107:
 
select a.name,b.name,c.name from all_nested_accounts as ana join accounts as a on ana.start_guid=a.guid join accounts as b on ana.guid=b.guid join accounts as c on ana.parent_guid=c.guid where start_guid = '31e4ca3657b94feabe31054757aa7a5a';
 
select a.name,b.name,c.name from all_nested_accounts as ana join accounts as a on ana.start_guid=a.guid join accounts as b on ana.guid=b.guid join accounts as c on ana.parent_guid=c.guid where start_guid = '31e4ca3657b94feabe31054757aa7a5a';
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Request the guid of all multi-transactions ==
 
== Request the guid of all multi-transactions ==
 
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
select * from (select tx_guid, count(tx_guid) as tx_count from splits group by tx_guid) as split_lookup_preselect where tx_count>2;
 
select * from (select tx_guid, count(tx_guid) as tx_count from splits group by tx_guid) as split_lookup_preselect where tx_count>2;
 
</syntaxhighlight>
 
</syntaxhighlight>
 
  
 
== Extract prices and associated descriptions==
 
== Extract prices and associated descriptions==
 
This query will extract all prices from the <code>prices</code> table, and corresponding descriptions from the <code>commodities</code> table.<br>
 
This query will extract all prices from the <code>prices</code> table, and corresponding descriptions from the <code>commodities</code> table.<br>
 
Note the use of <code>1.0</code> to force the results of the division to show decimal places; and the SQLite <code>||</code> variant to the SQL <code>CONCAT()</code> function.
 
Note the use of <code>1.0</code> to force the results of the division to show decimal places; and the SQLite <code>||</code> variant to the SQL <code>CONCAT()</code> function.
 
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
SELECT c1.namespace AS 'Namespace',
 
SELECT c1.namespace AS 'Namespace',
Line 146: Line 127:
 
JOIN commodities AS c2 ON p.currency_guid = c2.guid
 
JOIN commodities AS c2 ON p.currency_guid = c2.guid
 
ORDER BY c1.namespace, c1.mnemonic, p.date
 
ORDER BY c1.namespace, c1.mnemonic, p.date
 
 
</syntaxhighlight>
 
</syntaxhighlight>
  

Latest revision as of 13:20, 8 October 2024

This page shows you how to extract various data from a GnuCash database using SQL statements.

Note
This will not work if your GnuCash data is stored in the default XML format, you must "Save As" and choose one of the supported database formats.

Other Samples and Tools

the piecash project offers some example sqlite based examples files at which you can try out. https://github.com/sdementen/piecash/tree/master/gnucash_books

With a tool such as https://github.com/coleifer/sqlite-web you can interactively try the examples shown on this page.

Wiki "Template"

(Use this template to add new SQL sections.)


Create a read-only account for the database

If you use the account, which is also used by gnucash, you will be alerted every time gnucash starts up, when it can not get exclusive write permission. This normally makes sense, but we do not want to edit the data anyway. So it's better to create a read-only account first. The example uses postgres, but other database systems are identical.

sudo su postgres # switch to the postgres account to have admin rights in the database
psql             # open the sql shell, you can also call psql gnucash, to directly connect to the gnucash database right away
-- now we are in the psql shell. So comments start with double dash -- instead of pound #

postgres=# \c gnucash -- connect to the gnucash database
gnucash=# CREATE ROLE gnucashdbreader WITH ENCRYPTED PASSWORD 'randomUserPassword' LOGIN; -- new user account, which may login
CREATE ROLE -- response from the system
gnucash=# GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO gnucashdbreader; -- You must be connected to the database gnucash, while doing this. PUBLIC is context specific and refers to the current database

Request all transactions like shown in the account list

This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed.

select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name;
To get the right amounts correctly signed adjusted by the factor for the currency, we have to use this sql-querry:
select distinct CAST((CAST(-s2.value_num as DECIMAL)/s2.value_denom) as DECIMAL(10,2)) as amount, t1.post_date as date , t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name

Request expenses for a certain period and category

If you just want to know, how much you spend on certain things during some period, you can use this querry:
SELECT SUM(CAST(-CAST(s2.value_num AS DECIMAL(10,2))/s1.value_denom AS DECIMAL(10,2))) AS amount
FROM splits s1
INNER JOIN splits s2 ON s1.tx_guid=s2.tx_guid
INNER JOIN transactions t1 ON t1.guid=s2.tx_guid
INNER JOIN accounts a1 ON s1.account_guid=a1.guid
INNER JOIN accounts a2 ON s2.account_guid=a2.guid
WHERE a1.name!=a2.name AND t1.post_date >= '<some start date>' AND t1.post_date <= '<some end date>' AND a1.name = '<category>';

Return an account guid by account path or account path by guid

This returns a guid of an account by its path. The path must unlike in the GnuCash frontend start with 'Root Account'.

with recursive path_list as (
   select guid, parent_guid,name,concat(name) as path
   from accounts
   where parent_guid is null  -- this defines the start of the recursion
   union all
   select child.guid,child.parent_guid,child.name,concat(path,':',child.name)
   from accounts as child
     join path_list as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select guid from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';

or the other way round:

select path from path_list where guid='9bea76f63e5f1caa9b36c4d5e9d0a721';

You can check the output and have a bit more insight by replacing the last line with

select * from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';

Request all child accounts of a given account

This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from sql-workbench.

with recursive all_nested_accounts as (
   select guid as start_guid,guid, parent_guid
   from accounts
   union all
   select start_guid,child.guid,child.parent_guid
   from accounts as child
     join all_nested_accounts as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from all_nested_accounts where start_guid = '31e4ca3657b94feabe31054757aa7a5a';

If you want to have names instead of guids, replace the last line with

select a.name,b.name,c.name from all_nested_accounts as ana join accounts as a on ana.start_guid=a.guid join accounts as b on ana.guid=b.guid join accounts as c on ana.parent_guid=c.guid where start_guid = '31e4ca3657b94feabe31054757aa7a5a';

Request the guid of all multi-transactions

select * from (select tx_guid, count(tx_guid) as tx_count from splits group by tx_guid) as split_lookup_preselect where tx_count>2;

Extract prices and associated descriptions

This query will extract all prices from the prices table, and corresponding descriptions from the commodities table.
Note the use of 1.0 to force the results of the division to show decimal places; and the SQLite || variant to the SQL CONCAT() function.

SELECT c1.namespace AS 'Namespace',
  c1.mnemonic || ' (' || c1.fullname || ')' AS 'Security',
  c2.mnemonic || ' (' ||  c2.fullname || ')' AS 'Currency',
  p.date AS 'Date', p.source AS 'Source', p.type AS 'Type',
  p.value_num * 1.0 / p.value_denom  AS 'Price Decimal',
  p.value_num AS 'Price Numerator', p.value_denom AS 'Price Denominator'
FROM prices AS p
JOIN commodities AS c1 ON p.commodity_guid = c1.guid 
JOIN commodities AS c2 ON p.currency_guid = c2.guid
ORDER BY c1.namespace, c1.mnemonic, p.date

Here is a screenshot of this query in action using the "DB Browser for SQLite" application:- Example of querying GnuCash price data using the "DB Browser for SQLite" application DB Browser for SQLite