David - good idea!
Elmar - please see updated SQL here:
https://wiki.gnucash.org/wiki/PostgreSQL-Requests_For_Direct_Database_Access#Extract_prices_and_associated_descriptions
Thanks
Geoff
=====
On 16/10/2022 5:50 pm, David T. wrote:
Geoff,
Good advice/help! I do think it would be necessary to add the currency
to the result, to inform the resulting prices.
David T.
On Oct 16, 2022, at 4:38 AM, Geoff <cleanoutmys...@gmail.com
<mailto:cleanoutmys...@gmail.com>> wrote:
Hi Elmar
To elaborate on John's reply, this SQL statement should do the trick:
select c.namespace, c.mnemonic, c.fullname, p.date,
p.value_num as 'Price Numerator', p.value_denom as 'Price Denominator',
p.value_num * 1.0 / p.value_denom as 'Price Decimal'
from prices as p
join commodities as c on p.commodity_guid = c.guid
(1) Download the free "DB Browser for SQLIte" software from
https://sqlitebrowser.org <https://sqlitebrowser.org>/
(2) Save your GnuCash data file in "sqlite3" format
(3) Open your GnuCash database file with DB Browser
(4) Click on the "Execute SQL" tab
(5) Paste in the above SQL statement
(6) Check the results
(7) Click on the "Export" button to save as a CSV file
(8) Open this CSV file in the spreadsheet of your choice.
I have attached a screenshot of an example on Windows 10, but DB Browser
is also available for MAC & LINUX.
Good luck!
Regards
Geoff
=====
On 16/10/2022 7:03 am, Elmar wrote:
Thank you. This will be a new experience for me - I have never
even
looked at SQL, much less have any facility with it. All the
included
reports don't touch the price database itself, and I don't know
how to
create an appropriate one from the menu in GC. Off to look at
the wiki
:) Probably tomorrow - too late today, and I have other more
homely
tasks still to do.
- Elmar
On 10/15/22 13:40, john wrote:
The easiest would be to save your book to SQLite3: File>Save
As...,
pick Sqlite3 from the drop-down at the top of the dialog,
pick a file
name and location.
Then run a query on the prices table (see
https://wiki.gnucash.org/wiki/SQL#Prices for
<https://wiki.gnucash.org/wiki/SQL#Prices for> the schema;
you may want
to join on commodities to get the security's symbol and
namespace and
the currency's ISO-4217 code). Note that the actual price is
saved in
two fields, value_num and value_denom, that represents a
fraction.
Sqlite3 has a -csv option to output csv and a -o option to
write a file.
Regards,
John Ralls
On Oct 15, 2022, at 9:39 AM, Elmar <etsc...@gmail.com>
wrote:
Asking again - is there any way to get the accumulated
data copied
from the price database into a spreadsheet?
- Elmar
------------------------------------------------------------------------
_______________________________________________
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
