[GNC] Price database

[David T.]

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, 4:38 AM, at 4:38 AM, Geoff <cleanoutmyshed at 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/
>(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.
>
>------------------------------------------------------------------------
>
>_______________________________________________
>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.