Hello,
I've decided to tackle an issue that has been bugging me for a couple
of years now [1]. I travel a lot so I have accounts currently set up
for 45 currencies (and 5 other commodities). Ever since the shift
from the Yahoo finance APIs to Alpha Vantage in Finance::Quote, I have
had to manually add exchange rates in Gnucash.
So I have now tried to debug this myself [2]. The result of adding
these scheme printouts [3] clearly, to me, shows what the problem is.
There is an unhandled exception that sometimes occurs in
Finance::Quote. This is because the Alpha Vantage API is not very
reliable and does not always return an exchange rate. Running
gnc-fg-helper manually shows that exception [4] (the same problem I
reported as a bug upsteam to Finance::Quote [1]). This causes the
guile 'fdes' file descriptor to close, hence all subsequent attempts
at fetching a quote with gnc-fg-helper fails. The result is that only
the top few commodities in the Gnucash database are updated - ever.
For me that is between 3-6 currencies max. Those on the bottom of my
list are absolutely never updated.
There was zero movement on the part of the Finance::Quote developers,
so I decided instead to try to make Gnucash more robust against F::Q
issues. The F::Q exception was causing gnc-fg-helper to exit. In
Gnucash 3.6 and earlier, this would simply cause the bottom of the
currency list to never be updated. From Gnucash 3.7 to the current
master commit from today, instead the Gnucash GUI would die.
But another issue I noticed in the way that quotes are retrieved is
that Gnucash is not respecting the Alpha Vantage API limits. Instead
of calling F::Q once with all currency pairs - which would trigger
F::Q's sleeping mechanism to only fetch max 5 commodities per minute
(introduced in version 1.48) - Gnucash would rather fetch quotes
individually from F::Q at the maximum rate within the gnc-fq-helper
while loop. I.e. Gnucash would not sleep.
Therefore I made two minor changes to gnc-fq-helper [5]. Firstly the
while loop waits for 15 seconds at the end of each iteration (60 s / 5
= 12 s might be enough for the API limits though, as the fetching
itself takes time). Secondly I added error handling for the F::Q call
so that the loop will simply continue. There might be better ways to
do this, but now fetching quotes finally works for me. Due to the
unreliability of the Alpha Vantage API, not all currencies are
updated. But fetching quotes multiple times, i.e. via a cron job and
the --add-price-quotes option, results in almost all currencies being
updated.
I hope this would be of interest to the Gnucash developers. This does
have the effect of making quote fetching from the GUI "Price Database"
window quite slow, so a warning dialog would probably have to be
introduced explaining that only 5 currency pairs can be fetched per
minute, so the GUI will freeze for a while (a rough time could even be
calculated).
Regards,
Edward
[1] My bug reports:
https://bugs.gnucash.org/show_bug.cgi?id=795564
https://rt.cpan.org/Public/Bug/Display.html?id=125310
[2] My terrible scheme debugging:
"""
diff --git a/gnucash/price-quotes.scm b/gnucash/price-quotes.scm
index d905d9739..dd667ae5d 100644
--- a/gnucash/price-quotes.scm
+++ b/gnucash/price-quotes.scm
@@ -127,6 +127,13 @@
(lambda (request)
(catch #t
(lambda ()
+ (display "handling-request: ") (display request) (newline)
+ (display #\()
+ (display (car request))
+ (display " ")
+ (for-each write (cdr request))
+ (display #\))
+ (newline)
(gnc:debug "handling-request: " request)
;; we need to display the first element (the method,
;; so it won't be quoted) and then write the rest
@@ -141,6 +148,7 @@
(force-output)))
(let ((results (read (fdes->inport (gnc-process-get-fd
quoter 1)))))
+ (display "results: " ) (display results) (newline)
(gnc:debug "results: " results)
results))
(lambda (key . args) key)))
@@ -448,7 +456,11 @@ Run 'gnc-fq-update' as root to install them.")))
((memq 'system-error fq-results)
(set! keep-going? #f)
- (show-error (N_ "There was a system error while retrieving the
price quotes.")))
+ (display "fq-calls: ") (display fq-calls) (newline)
+ (display "fq-call-data: ") (display fq-call-data) (newline)
+ (display "fq-results: ") (display fq-results) (newline)
+ (display "problem-syms: ") (display (string-join problem-syms
", ")) (newline)
+ (display "commod-tz-quote-triples: ") (display
commod-tz-quote-triples) (newline))
((not (list? (car fq-results)))
(set! keep-going? #f)
"""
[3] My debugging output:
"""
[edward@localhost ~]$ gnucash3 --debug --add-price-quotes /data/money/accounts
This is a development version. It may or may not work.
Report bugs and other problems to gnucash-devel@gnucash.org
You can also lookup and file bug reports at https://bugs.gnucash.org
To find the last stable version, please refer to https://www.gnucash.org/
Found Finance::Quote version 1.49.
handling-request: (currency SGD EUR)
(currency "SGD""EUR")
results: ((SGD (symbol . SGD) (gnc:time-no-zone . 2020-01-18 19:30:31)
(last . 6691/10000) (currency . EUR)))
handling-request: (currency IDR EUR)
(currency "IDR""EUR")
results: ((IDR (symbol . IDR) (gnc:time-no-zone . 2020-01-18 19:30:31)
(last . 6599/100000000) (currency . EUR)))
handling-request: (currency BSD EUR)
(currency "BSD""EUR")
results: ((BSD (symbol . BSD) (gnc:time-no-zone . 2020-01-18 19:30:31)
(last . 4499/5000) (currency . EUR)))
handling-request: (currency KRW EUR)
(currency "KRW""EUR")
results: #<eof>
handling-request: (currency BRL EUR)
(currency "BRL""EUR")
handling-request: (currency CHF EUR)
(currency "CHF""EUR")
handling-request: (currency ITL EUR)
(currency "ITL""EUR")
handling-request: (currency TWD EUR)
(currency "TWD""EUR")
handling-request: (currency BEF EUR)
(currency "BEF""EUR")
handling-request: (currency NZD EUR)
(currency "NZD""EUR")
handling-request: (currency USD EUR)
(currency "USD""EUR")
handling-request: (currency HKD EUR)
(currency "HKD""EUR")
handling-request: (currency NLG EUR)
(currency "NLG""EUR")
handling-request: (currency ESP EUR)
(currency "ESP""EUR")
handling-request: (currency CAD EUR)
(currency "CAD""EUR")
handling-request: (currency XAU EUR)
(currency "XAU""EUR")
handling-request: (currency RSD EUR)
(currency "RSD""EUR")
handling-request: (currency DEM EUR)
(currency "DEM""EUR")
handling-request: (currency ILS EUR)
(currency "ILS""EUR")
handling-request: (currency XAG EUR)
(currency "XAG""EUR")
handling-request: (currency GBP EUR)
(currency "GBP""EUR")
handling-request: (currency TRY EUR)
(currency "TRY""EUR")
handling-request: (currency FRF EUR)
(currency "FRF""EUR")
handling-request: (currency INR EUR)
(currency "INR""EUR")
handling-request: (currency CNY EUR)
(currency "CNY""EUR")
handling-request: (currency RUB EUR)
(currency "RUB""EUR")
handling-request: (currency SEK EUR)
(currency "SEK""EUR")
handling-request: (currency JPY EUR)
(currency "JPY""EUR")
handling-request: (currency ISK EUR)
(currency "ISK""EUR")
handling-request: (currency ATS EUR)
(currency "ATS""EUR")
handling-request: (currency AFA EUR)
(currency "AFA""EUR")
handling-request: (currency HUF EUR)
(currency "HUF""EUR")
handling-request: (currency MYR EUR)
(currency "MYR""EUR")
handling-request: (currency KHR EUR)
(currency "KHR""EUR")
handling-request: (currency LUF EUR)
(currency "LUF""EUR")
handling-request: (currency NOK EUR)
(currency "NOK""EUR")
handling-request: (currency GRD EUR)
(currency "GRD""EUR")
handling-request: (currency HRK EUR)
(currency "HRK""EUR")
handling-request: (currency DOP EUR)
(currency "DOP""EUR")
handling-request: (currency DKK EUR)
(currency "DKK""EUR")
handling-request: (currency AUD EUR)
(currency "AUD""EUR")
handling-request: (currency ANG EUR)
(currency "ANG""EUR")
handling-request: (currency CZK EUR)
(currency "CZK""EUR")
handling-request: (currency THB EUR)
(currency "THB""EUR")
handling-request: (currency AED EUR)
(currency "AED""EUR")
handling-request: (currency ZAR EUR)
(currency "ZAR""EUR")
fq-calls: ((currency SGD EUR) (currency IDR EUR) (currency BSD EUR)
(currency KRW EUR) (currency BRL EUR) (currency CHF EUR) (currency ITL
EUR) (currency TWD EUR) (currency BEF EUR) (currency NZD EUR)
(currency USD EUR) (currency HKD EUR) (currency NLG EUR) (currency ESP
EUR) (currency CAD EUR) (currency XAU EUR) (currency RSD EUR)
(currency DEM EUR) (currency ILS EUR) (currency XAG EUR) (currency GBP
EUR) (currency TRY EUR) (currency FRF EUR) (currency INR EUR)
(currency CNY EUR) (currency RUB EUR) (currency SEK EUR) (currency JPY
EUR) (currency ISK EUR) (currency ATS EUR) (currency AFA EUR)
(currency HUF EUR) (currency MYR EUR) (currency KHR EUR) (currency LUF
EUR) (currency NOK EUR) (currency GRD EUR) (currency HRK EUR)
(currency DOP EUR) (currency DKK EUR) (currency AUD EUR) (currency ANG
EUR) (currency CZK EUR) (currency THB EUR) (currency AED EUR)
(currency ZAR EUR))
fq-call-data: ((currency (#<swig-pointer gnc_commodity * 12a2af0>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 1292bc0> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 1286490> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
1294be0> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 12862d0> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 12886a0>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 1293af0> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 12a66e0> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
1284900> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 129d3a0> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 12a7370>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 128fe20> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 129d100> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
128d440> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 1288400> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 12ac110>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 129f920> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 128bb50> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
1292d80> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 12aa720> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 128e370>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 12a6520> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 128e290> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
1292e60> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 1289b30> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 129fa00>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 12a2a10> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 1293d90> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
1293a10> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 1281f00> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 1279590>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 1292ae0> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 129c1d0> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
1294940> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 1296db0> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 129d1e0>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 128faa0> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 1292920> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
128bdf0> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 128bd10> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 12830f0>
#<swig-pointer gnc_commodity * 128d600> )) (currency (#<swig-pointer
gnc_commodity * 12819c0> #<swig-pointer gnc_commodity * 128d600> ))
(currency (#<swig-pointer gnc_commodity * 128ba70> #<swig-pointer
gnc_commodity * 128d600> )) (currency (#<swig-pointer gnc_commodity *
12a3e80> #<swig-pointer gnc_commodity * 128d600> )) (currency
(#<swig-pointer gnc_commodity * 12794b0> #<swig-pointer gnc_commodity
* 128d600> )) (currency (#<swig-pointer gnc_commodity * 12a91d0>
#<swig-pointer gnc_commodity * 128d600> )))
fq-results: (((SGD (symbol . SGD) (gnc:time-no-zone . 2020-01-18
19:30:31) (last . 6691/10000) (currency . EUR))) ((IDR (symbol . IDR)
(gnc:time-no-zone . 2020-01-18 19:30:31) (last . 6599/100000000)
(currency . EUR))) ((BSD (symbol . BSD) (gnc:time-no-zone . 2020-01-18
19:30:31) (last . 4499/5000) (currency . EUR))) #<eof> system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error system-error system-error system-error system-error
system-error)
problem-syms: CURRENCY:KRW, CURRENCY:BRL, CURRENCY:CHF, CURRENCY:ITL,
CURRENCY:TWD, CURRENCY:BEF, CURRENCY:NZD, CURRENCY:USD, CURRENCY:HKD,
CURRENCY:NLG, CURRENCY:ESP, CURRENCY:CAD, CURRENCY:XAU, CURRENCY:RSD,
CURRENCY:DEM, CURRENCY:ILS, CURRENCY:XAG, CURRENCY:GBP, CURRENCY:TRY,
CURRENCY:FRF, CURRENCY:INR, CURRENCY:CNY, CURRENCY:RUB, CURRENCY:SEK,
CURRENCY:JPY, CURRENCY:ISK, CURRENCY:ATS, CURRENCY:AFA, CURRENCY:HUF,
CURRENCY:MYR, CURRENCY:KHR, CURRENCY:LUF, CURRENCY:NOK, CURRENCY:GRD,
CURRENCY:HRK, CURRENCY:DOP, CURRENCY:DKK, CURRENCY:AUD, CURRENCY:ANG,
CURRENCY:CZK, CURRENCY:THB, CURRENCY:AED, CURRENCY:ZAR
commod-tz-quote-triples: ((#<swig-pointer gnc_commodity * 12a2af0>
((symbol . SGD) (gnc:time-no-zone . 2020-01-18 19:30:31) (last .
6691/10000) (currency . EUR))) (#<swig-pointer gnc_commodity *
1292bc0> ((symbol . IDR) (gnc:time-no-zone . 2020-01-18 19:30:31)
(last . 6599/100000000) (currency . EUR))) (#<swig-pointer
gnc_commodity * 1286490> ((symbol . BSD) (gnc:time-no-zone .
2020-01-18 19:30:31) (last . 4499/5000) (currency . EUR))) (#f .
#<swig-pointer gnc_commodity * 1294be0>) (#f . #<swig-pointer
gnc_commodity * 12862d0>) (#f . #<swig-pointer gnc_commodity *
12886a0>) (#f . #<swig-pointer gnc_commodity * 1293af0>) (#f .
#<swig-pointer gnc_commodity * 12a66e0>) (#f . #<swig-pointer
gnc_commodity * 1284900>) (#f . #<swig-pointer gnc_commodity *
129d3a0>) (#f . #<swig-pointer gnc_commodity * 12a7370>) (#f .
#<swig-pointer gnc_commodity * 128fe20>) (#f . #<swig-pointer
gnc_commodity * 129d100>) (#f . #<swig-pointer gnc_commodity *
128d440>) (#f . #<swig-pointer gnc_commodity * 1288400>) (#f .
#<swig-pointer gnc_commodity * 12ac110>) (#f . #<swig-pointer
gnc_commodity * 129f920>) (#f . #<swig-pointer gnc_commodity *
128bb50>) (#f . #<swig-pointer gnc_commodity * 1292d80>) (#f .
#<swig-pointer gnc_commodity * 12aa720>) (#f . #<swig-pointer
gnc_commodity * 128e370>) (#f . #<swig-pointer gnc_commodity *
12a6520>) (#f . #<swig-pointer gnc_commodity * 128e290>) (#f .
#<swig-pointer gnc_commodity * 1292e60>) (#f . #<swig-pointer
gnc_commodity * 1289b30>) (#f . #<swig-pointer gnc_commodity *
129fa00>) (#f . #<swig-pointer gnc_commodity * 12a2a10>) (#f .
#<swig-pointer gnc_commodity * 1293d90>) (#f . #<swig-pointer
gnc_commodity * 1293a10>) (#f . #<swig-pointer gnc_commodity *
1281f00>) (#f . #<swig-pointer gnc_commodity * 1279590>) (#f .
#<swig-pointer gnc_commodity * 1292ae0>) (#f . #<swig-pointer
gnc_commodity * 129c1d0>) (#f . #<swig-pointer gnc_commodity *
1294940>) (#f . #<swig-pointer gnc_commodity * 1296db0>) (#f .
#<swig-pointer gnc_commodity * 129d1e0>) (#f . #<swig-pointer
gnc_commodity * 128faa0>) (#f . #<swig-pointer gnc_commodity *
1292920>) (#f . #<swig-pointer gnc_commodity * 128bdf0>) (#f .
#<swig-pointer gnc_commodity * 128bd10>) (#f . #<swig-pointer
gnc_commodity * 12830f0>) (#f . #<swig-pointer gnc_commodity *
12819c0>) (#f . #<swig-pointer gnc_commodity * 128ba70>) (#f .
#<swig-pointer gnc_commodity * 12a3e80>) (#f . #<swig-pointer
gnc_commodity * 12794b0>) (#f . #<swig-pointer gnc_commodity *
12a91d0>))
Backtrace:
In ice-9/boot-9.scm:
160: 3 [catch #t #<catch-closure b8ce00> ...]
In unknown file:
?: 2 [apply-smob/1 #<catch-closure b8ce00>]
In ice-9/boot-9.scm:
160: 1 [catch #t #<catch-closure 3d70680> ...]
In unknown file:
?: 0 [apply-smob/1 #<catch-closure 3d70680>]
ERROR: In procedure apply-smob/1:
ERROR: In procedure scm_flush: Bad file descriptor
[edward@localhost ~]$
"""
[4] The Finance::Quote exception:
"""
[edward@localhost ~]$ gnc-fq-helper
(currency "SGD""EUR")
(("SGD" (symbol . "SGD") (gnc:time-no-zone . "2020-01-18 19:30:57")
(last . #e0.6691) (currency . "EUR")))
(currency "IDR""EUR")
(("IDR" (symbol . "IDR") (gnc:time-no-zone . "2020-01-18 19:30:57")
(last . #e6.599e-05) (currency . "EUR")))
(currency "BSD""EUR")
(("BSD" (symbol . "BSD") (gnc:time-no-zone . "2020-01-18 19:30:57")
(last . #e0.8998) (currency . "EUR")))
(currency "KRW""EUR")
Use of uninitialized value $inverse_rate in division (/) at
/usr/share/perl5/vendor_perl/Finance/Quote.pm line 299, <> line 4.
Illegal division by zero at
/usr/share/perl5/vendor_perl/Finance/Quote.pm line 299, <> line 4.
"""
[5] My fixes:
"""
diff --git a/libgnucash/quotes/gnc-fq-helper.in
b/libgnucash/quotes/gnc-fq-helper.in
index 95e7210c5..d63bcf983 100755
--- a/libgnucash/quotes/gnc-fq-helper.in
+++ b/libgnucash/quotes/gnc-fq-helper.in
@@ -24,6 +24,7 @@
use strict;
use English;
use FileHandle;
+use Try::Tiny;
# Date::Manip provides ParseDate, ParseDateString, and UnixTime.
use Date::Manip;
@@ -347,7 +348,13 @@ while(<>) {
last unless $from_currency;
last unless $to_currency;
- my $price = $quoter->currency($from_currency, $to_currency);
+ my $price = undef;
+ try {
+ $price = $quoter->currency($from_currency, $to_currency);
+ } catch {
+ warn "caught error: $_";
+ continue;
+ };
my $inv_price = undef;
# Sometimes price quotes are available in only one direction.
unless (defined($price)) {
@@ -378,6 +385,8 @@ while(<>) {
}
STDOUT->flush();
+
+ sleep(15); # Alphavantage limit: up to 5 API requests per minute
and 500 requests per day.
}
"""
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
