[GNC] Price database

[Geoff]

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/
(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 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 <etschme at 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 at 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.
>>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at 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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: gnc_price_database_export_to_csv.jpg
Type: image/jpeg
Size: 974181 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20221016/79311eba/attachment-0001.jpg>