Re: [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 > 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 
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.

Re: [GNC] Price database

[David T. via gnucash-user]

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  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  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.
>>>
>> ___
>> 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.
>
>
>
>___
>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.
___
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.

Re: [GNC] Price database

[Elmar]

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  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.



___
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.

Re: [GNC] Price database

[Elmar]

Thank you, but how I have my investments labeled make sense to me, and 
not to any online databases.  I personally enter the quarterly (or 
monthly) prices that the statements give me (some of which are not 
tracked by, say, finance:quote), so conventional tools are really 
useless for me.  It's my personal GC price database I want to export as 
a whole thing (all the "investments", with all their date stamped data, 
appropriately labeled for later massaging in a spreadsheet).



- Elmar


On 10/15/22 14:19, Murugan Muruganandam wrote:
if you are looking for historical price data for analysis, you can use 
google finance function in goolge sheets to get the same, for example 
=GOOGLEFINANCE("AAPL","price",DATE(2016,1,1),today(),"DAILY") will 
give you daily rates from 2016 january




Saludos Cordiales


Murugan



___
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.

Re: [GNC] Price database

[Murugan Muruganandam]

if you are looking for historical price data for analysis, you can use google 
finance function in goolge sheets to get the same, for example 
=GOOGLEFINANCE("AAPL", "price", DATE(2016,1,1), today(), "DAILY") will give you 
daily rates from 2016 january




Saludos Cordiales


Murugan


From: gnucash-user 
 on behalf of john 

Sent: Saturday, October 15, 2022 2:40 PM
To: Elmar 
Cc: Gnucash Users 
Subject: Re: [GNC] Price database

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  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.

___
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.
___
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.

Re: [GNC] Price database

[john]

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  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.

___
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.

[GNC] Price database

[Elmar]

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.

Re: [GNC] Price Database

[Les]

Thanks, John.

On 6/17/20 7:27 PM, John Ralls wrote:

$ bin/gnc-fq-dump currency HKD USD
1 HKD = 0.12903193 USD

Regards,
John Ralls




On Jun 17, 2020, at 11:15 AM, Les  wrote:

Thanks for your reply.  However, the problem is I have a Hong Kong bank account 
that has multiple currencies. The default is HKD, but I need Price Database to 
reflect the USD amount for tax reporting.

(I hope I am stating this correctly.)  :)

Les

On 6/16/20 7:59 PM, Chris Good wrote:

Message: 6
Date: Tue, 16 Jun 2020 17:04:09 -0500
From: Les 
To: gnucash-user@gnucash.org
Subject: [GNC] Price Database
Message-ID: 
Content-Type: text/plain; charset=utf-8; format=flowed

Hi All:

For at least 2 weeks, I have been unable to obtain a quote for HKD (Hong
Kong dollar).

Is anyone else having this problem?? Any suggestions?

Thanks,

Les

Linux Mint 19.3? GC 3.10 (Flatpak)

Hi Les,

Works for me (this is in Linux):

ALPHAVANTAGE_API_KEY=ATALQOFWCSKL2VZ4 ./gnc-fq-dump currency HKD AUD
1 HKD = 0.18765 AUD

See https://wiki.gnucash.org/wiki/Online_Quotes#Trouble_Shooting

Regard, Chris Good


___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Price Database

[John Ralls]

$ bin/gnc-fq-dump currency HKD USD
1 HKD = 0.12903193 USD

Regards,
John Ralls



> On Jun 17, 2020, at 11:15 AM, Les  wrote:
> 
> Thanks for your reply.  However, the problem is I have a Hong Kong bank 
> account that has multiple currencies. The default is HKD, but I need Price 
> Database to reflect the USD amount for tax reporting.
> 
> (I hope I am stating this correctly.)  :)
> 
> Les
> 
> On 6/16/20 7:59 PM, Chris Good wrote:
>> Message: 6
>> Date: Tue, 16 Jun 2020 17:04:09 -0500
>> From: Les 
>> To: gnucash-user@gnucash.org
>> Subject: [GNC] Price Database
>> Message-ID: 
>> Content-Type: text/plain; charset=utf-8; format=flowed
>> 
>> Hi All:
>> 
>> For at least 2 weeks, I have been unable to obtain a quote for HKD (Hong
>> Kong dollar).
>> 
>> Is anyone else having this problem?? Any suggestions?
>> 
>> Thanks,
>> 
>> Les
>> 
>> Linux Mint 19.3? GC 3.10 (Flatpak)
>> 
>> Hi Les,
>> 
>> Works for me (this is in Linux):
>> 
>> ALPHAVANTAGE_API_KEY=ATALQOFWCSKL2VZ4 ./gnc-fq-dump currency HKD AUD
>> 1 HKD = 0.18765 AUD
>> 
>> See https://wiki.gnucash.org/wiki/Online_Quotes#Trouble_Shooting
>> 
>> Regard, Chris Good
>> 
> ___
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see 
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Price Database

[Les]

Thanks for your reply.  However, the problem is I have a Hong Kong bank 
account that has multiple currencies. The default is HKD, but I need 
Price Database to reflect the USD amount for tax reporting.


(I hope I am stating this correctly.)  :)

Les

On 6/16/20 7:59 PM, Chris Good wrote:

Message: 6
Date: Tue, 16 Jun 2020 17:04:09 -0500
From: Les 
To: gnucash-user@gnucash.org
Subject: [GNC] Price Database
Message-ID: 
Content-Type: text/plain; charset=utf-8; format=flowed

Hi All:

For at least 2 weeks, I have been unable to obtain a quote for HKD (Hong
Kong dollar).

Is anyone else having this problem?? Any suggestions?

Thanks,

Les

Linux Mint 19.3? GC 3.10 (Flatpak)

Hi Les,

Works for me (this is in Linux):

ALPHAVANTAGE_API_KEY=ATALQOFWCSKL2VZ4 ./gnc-fq-dump currency HKD AUD
1 HKD = 0.18765 AUD

See https://wiki.gnucash.org/wiki/Online_Quotes#Trouble_Shooting

Regard, Chris Good


___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Price Database

[Chris Good]

Message: 6
Date: Tue, 16 Jun 2020 17:04:09 -0500
From: Les 
To: gnucash-user@gnucash.org
Subject: [GNC] Price Database
Message-ID: 
Content-Type: text/plain; charset=utf-8; format=flowed

Hi All:

For at least 2 weeks, I have been unable to obtain a quote for HKD (Hong 
Kong dollar).

Is anyone else having this problem?? Any suggestions?

Thanks,

Les

Linux Mint 19.3? GC 3.10 (Flatpak)

Hi Les,

Works for me (this is in Linux):

ALPHAVANTAGE_API_KEY=ATALQOFWCSKL2VZ4 ./gnc-fq-dump currency HKD AUD
1 HKD = 0.18765 AUD

See https://wiki.gnucash.org/wiki/Online_Quotes#Trouble_Shooting

Regard, Chris Good

___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[GNC] Price Database

[Les]

Hi All:

For at least 2 weeks, I have been unable to obtain a quote for HKD (Hong 
Kong dollar).


Is anyone else having this problem?  Any suggestions?

Thanks,

Les

Linux Mint 19.3  GC 3.10 (Flatpak)

___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Price Database - can't just type ticker symbol, must scroll to select

[David Carlson]

I cannot comment on why that doesn't work in release 3.7 but it sure is a
handy feature in the 2.6 series that is new to me.  I hope it will get
fixed.

David  Carlson

On Tue, Dec 3, 2019, 10:45 AM George Riner  wrote:

> I'm on Windows 10, build 1903
> running Build ID: 3.7+(2019-09-07)
> Finance::Quote: 1.47  (I don't use the downloading of prices)
>
> I'm manually entering prices in the Price Database. Apparently, the tab
> order of the fields skips over the field value and will only land on the
> down arrow to the right of the field. (yes, the focus will land on the
> other fields in the window, just not the value for the security
> ticker/name).
>
> Thus, I cannot type the security symbol to have the list select it once
> I type in enough unique characters.  I have to use the down arrow to
> open the list and scroll through it with the mouse to get to the
> security I want - many of mine are at the bottom of the list - tedious.
>
> This used to work in 2.x series of GnuCash - I could tab to the security
> symbol/name list, start typing a symbol and the list would open up and
> scroll to the first matching symbol. Usually with only 3-4 characters
> typed I'd be at the intended security and could press Enter and it would
> select it into the Security value field, and I could tab on from there
> to put in the date and price.
>
> :George
> Sebastpol, CA
>
> ___
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

[GNC] Price Database - can't just type ticker symbol, must scroll to select

[George Riner]

I'm on Windows 10, build 1903
running Build ID: 3.7+(2019-09-07)
Finance::Quote: 1.47  (I don't use the downloading of prices)

I'm manually entering prices in the Price Database. Apparently, the tab 
order of the fields skips over the field value and will only land on the 
down arrow to the right of the field. (yes, the focus will land on the 
other fields in the window, just not the value for the security 
ticker/name).


Thus, I cannot type the security symbol to have the list select it once 
I type in enough unique characters.  I have to use the down arrow to 
open the list and scroll through it with the mouse to get to the 
security I want - many of mine are at the bottom of the list - tedious.


This used to work in 2.x series of GnuCash - I could tab to the security 
symbol/name list, start typing a symbol and the list would open up and 
scroll to the first matching symbol. Usually with only 3-4 characters 
typed I'd be at the intended security and could press Enter and it would 
select it into the Security value field, and I could tab on from there 
to put in the date and price.


:George
Sebastpol, CA

___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.