[GNC] Price database

[Geoff]

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 <cleanoutmyshed at gmail.com 
> <mailto: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  <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 <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
> 
>                 ------------------------------------------------------------------------
> 
>