[GNC-dev] Robust error handling and respecting the Apha Vantage API limits in gnc-fq-helper.

Mon Feb 10 06:27:13 EST 2020

On Sun, 9 Feb 2020 at 23:41, Mike Alexander <mta at umich.edu> wrote:
> You don't seem to be following what I'm saying.

I thought I was ;)

> First, the delay when currency quotes exceed the AlphaVantage rate limit is in Finance::Quote itself, not in any code distributed as part of GnuCash. You're right that GnuCash asks for currency exchange rates as fast as possible, but this is not relevant to what I'm saying. As of F::Q 1.49 the code in F::Q to rate limit currency exchange rate calls is broken. Earlier I gave a patch to fix this. If you haven't applied this patch to F::Q, GnuCash won't be able to retrieve more than a few currency exchange rates. Have you applied this patch?
>
> Second, you keep saying that GnuCash should ask for multiple currency quotes per call to F::Q. I don't know of any way to do this. I just read the F::Q documentation and code again and can't see any method that returns more than one currency exchange rate per call. How do you do this in F::Q? Is there some undocumented method that I am missing?

Firstly, I have posted a Perl script that demonstrates parallel vs.
serial F::Q calls [1].  The output of this script is at [2].  I now
realise that the F::Q currency() call that is currently used for
currencies in gnc-fc-helper does not have the same flexibility as the
fetch() call, so that only serial operation is possible.  From the
output in [2], it also seems like the rate limiting bugs are only in
the currency() call and not in fetch().  That output shows that both
parallel and serial calls respect the Alpha Vantage API limits and
have about the same running time.

As a 2nd test, I wrote a shell script to directly call gnc-fc-helper
and trigger the fetch() rather than currency() calls [3].  With this,
I see that the fetch() call can be used successfully, both in parallel
and serial [4].  This causes F::Q to be called in exactly the same way
as in the perl script [1], with pretty much the same timings.  GnuCash
is not affected by the current F::Q API limit bugs in this mode of
operation.

As a 3rd test, I have mimicked the GnuCash behaviour in a script
calling gnc-fc-helper to fetch via the F::Q currency() call [5].  This
clearly shows the F::Q bug that GnuCash has tripping up on for two
years now [6].

I realise that F::Q is broken in some places.  As I said, I reported
an issue.  However, as you have seen yourself, the F::Q developers are
simply non-responsive.  I believe that asking all GnuCash users to
manually patch their own Finance::Quote perl modules to get around
this 2 year old issue is too much to ask of users.  My reasoning is
that a little more logic on the GnuCash side to handle F::Q
misbehaving is easier for all GnuCash users.  And, from the behaviour
of the scripts below, I now wonder if GnuCash could benefit by
switching from piping '(currency "USD" "EUR")' into gnc-fc-helper to
instead piping in '(alphavantage "USDEUR")'?  The only disadvantage is
that gold and silver quotes do not work via the fetch() interface with
Alpha Vantage.

Regards,

Edward

P. S. For other wanting to test this, note that you need to have a
free key from Alpha Vantage and set the environmental variable
ALPHAVANTAGE_API_KEY to that key.  As a side note, I chased down the
current currency fetching logic to this commit
(https://github.com/Gnucash/gnucash/commit/4fae9b):

"""
commit 4fae9be45ed5cc7faa4a3190b118aa47900e6e20 (HEAD)
Author: Dave Peticolas <dave at krondo.com>
Date:   Tue May 8 09:36:40 2001 +0000

    2001-05-08  Dave Peticolas  <dave at krondo.com>

            * src/scm/price-quotes.scm: add currency quote support

            * src/gnome/dialog-account.c: add currency quote support

            * src/engine/Account.c: allow CURRENCY accounts to have price
            source set.

            * src/quotes/finance-quote-helper.in: add currency quote support

    git-svn-id: svn+ssh://svn.gnucash.org/repo/gnucash/trunk@4128
57a11ea4-9604-0410-9ed3-97b8803252fd
"""

The logic has not changed significantly since then.

[1] The 'parallel_vs_serial.pl' Perl script demonstrating parallel vs.
serial calls to F::Q:

"""
#!/usr/bin/perl -w

use Finance::Quote;
use Time::HiRes qw(time);
print("Using Finance::Quote $Finance::Quote::VERSION\n");

# The currencies to fetch.
my @symbols = (
    "USDAUD",
    "USDBRL",
    "USDCAD",
    "USDCHF",
    "USDCNY",
    "USDCZK",
    "USDDKK",
    "USDEUR",
    "USDGBP",
    "USDHKD",
    "USDHRK",
    "USDILS",
    "USDJPY",
    "USDKRW",
    "USDMYR",
    "USDNOK",
    "USDNZD",
    "USDRUB",
    "USDSGD",
    "USDTWD",
    "USDXAU"
);

# The labels to print out.
my @labels = (
    "last",
    "success",
    "errormsg"
);

# Parallel execution.
my $begin_par = time();
my $quoter_par = Finance::Quote->new();
%data_par = $quoter_par->fetch("alphavantage", @symbols);
foreach my $symbol (@symbols) {
    foreach my $label (@labels) {
        if (exists $data_par{$symbol, $label}) {
            print($symbol, " ", $label, ": ");
            print($data_par{$symbol, $label}, "\n");
        }
    }
}
my $end_par = time();
printf("Parallel fetching in %0.02f s\n", $end_par-$begin_par);

# Wait for the Alphavantage API limit to reset.
print("\nSleeping 1 min for the API limit to reset.\n\n");
sleep(60);

# Serial execution.
my $begin_ser = time();
my $quoter_ser = Finance::Quote->new();
foreach my $symbol (@symbols) {
    %data_ser = $quoter_ser->fetch("alphavantage", $symbol);
    foreach my $label (@labels) {
        if (exists $data_ser{$symbol, $label}) {
            print($symbol, " ", $label, ": ");
            print($data_ser{$symbol, $label}, "\n");
        }
    }
}
my $end_ser = time();
printf("Serial fetching in %0.02f s\n", $end_ser-$begin_ser);

# Final wait for the Alphavantage API limit to reset.
print("\nSleeping 1 min for the API limit to reset.\n\n");
sleep(60);
"""

[2] The output from [1]:

"""
$ ./parallel_vs_serial.pl
Using Finance::Quote 1.49
USDAUD last: 1.4937
USDAUD success: 1
USDBRL last: 4.2680
USDBRL success: 1
USDCAD last: 1.3297
USDCAD success: 1
USDCHF last: 0.9749
USDCHF success: 1
USDCNY last: 6.9694
USDCNY success: 1
USDCZK last: 22.8407
USDCZK success: 1
USDDKK last: 6.8247
USDDKK success: 1
USDEUR last: 0.9131
USDEUR success: 1
USDGBP last: 0.7745
USDGBP success: 1
USDHKD last: 7.7623
USDHKD success: 1
USDHRK last: 6.7882
USDHRK success: 1
USDILS last: 3.4306
USDILS success: 1
USDJPY last: 109.9700
USDJPY success: 1
USDKRW last: 1184.1200
USDKRW success: 1
USDMYR last: 4.1460
USDMYR success: 1
USDNOK last: 9.2543
USDNOK success: 1
USDNZD last: 1.5591
USDNZD success: 1
USDRUB last: 63.3550
USDRUB success: 1
USDSGD last: 1.3856
USDSGD success: 1
USDTWD last: 30.0590
USDTWD success: 1
USDXAU success: 0
USDXAU errormsg: Invalid API call. Please retry or visit the
documentation (https://www.alphavantage.co/documentation/) for
TIME_SERIES_DAILY.
Parallel fetching in 242.41 s

Sleeping 1 min for the API limit to reset.

USDAUD last: 1.4938
USDAUD success: 1
USDBRL last: 4.2680
USDBRL success: 1
USDCAD last: 1.3297
USDCAD success: 1
USDCHF last: 0.9749
USDCHF success: 1
USDCNY last: 6.9694
USDCNY success: 1
USDCZK last: 22.8341
USDCZK success: 1
USDDKK last: 6.8229
USDDKK success: 1
USDEUR last: 0.9129
USDEUR success: 1
USDGBP last: 0.7743
USDGBP success: 1
USDHKD last: 7.7623
USDHKD success: 1
USDHRK last: 6.7882
USDHRK success: 1
USDILS last: 3.4306
USDILS success: 1
USDJPY last: 109.9700
USDJPY success: 1
USDKRW last: 1184.1200
USDKRW success: 1
USDMYR last: 4.1450
USDMYR success: 1
USDNOK last: 9.2505
USDNOK success: 1
USDNZD last: 1.5589
USDNZD success: 1
USDRUB last: 63.3550
USDRUB success: 1
USDSGD last: 1.3856
USDSGD success: 1
USDTWD last: 30.0590
USDTWD success: 1
USDXAU success: 0
USDXAU errormsg: Invalid API call. Please retry or visit the
documentation (https://www.alphavantage.co/documentation/) for
TIME_SERIES_DAILY.
Serial fetching in 241.34 s

Sleeping 1 min for the API limit to reset.
"""

[3] Shell script showing that gnc-fq-helper can be used to fetch
currencies via the F::Q fetch() function call.  For general reference,
the first 'echo' line would need to be unwrapped if copying and
pasting this script.

"""
#! /bin/sh

printf '\nParallel calls.\n'
start=`date +%s`
echo '(alphavantage "USDAUD" "USDBRL" "USDCAD" "USDCHF" "USDCNY"
"USDCZK" "USDDKK" "USDEUR" "USDGBP" "USDHKD" "USDHRK" "USDILS"
"USDJPY" "USDKRW" "USDMYR" "USDNOK" "USDNZD" "USDRUB" "USDSGD"
"USDTWD" "USDXAU")' | gnc-fq-helper
end=`date +%s`
time=$((end-start))
printf "Parallel fetching in %s s\n" $time

printf '\nSerial calls.\n'
start=`date +%s`
echo '(alphavantage "USDAUD")' | gnc-fq-helper
echo '(alphavantage "USDBRL")' | gnc-fq-helper
echo '(alphavantage "USDCAD")' | gnc-fq-helper
echo '(alphavantage "USDCHF")' | gnc-fq-helper
echo '(alphavantage "USDCNY")' | gnc-fq-helper
echo '(alphavantage "USDCZK")' | gnc-fq-helper
echo '(alphavantage "USDDKK")' | gnc-fq-helper
echo '(alphavantage "USDEUR")' | gnc-fq-helper
echo '(alphavantage "USDGBP")' | gnc-fq-helper
echo '(alphavantage "USDHKD")' | gnc-fq-helper
echo '(alphavantage "USDHRK")' | gnc-fq-helper
echo '(alphavantage "USDILS")' | gnc-fq-helper
echo '(alphavantage "USDJPY")' | gnc-fq-helper
echo '(alphavantage "USDKRW")' | gnc-fq-helper
echo '(alphavantage "USDMYR")' | gnc-fq-helper
echo '(alphavantage "USDNOK")' | gnc-fq-helper
echo '(alphavantage "USDNZD")' | gnc-fq-helper
echo '(alphavantage "USDRUB")' | gnc-fq-helper
echo '(alphavantage "USDSGD")' | gnc-fq-helper
echo '(alphavantage "USDTWD")' | gnc-fq-helper
echo '(alphavantage "USDXAU")' | gnc-fq-helper
end=`date +%s`
time=$((end-start))
printf "Serial fetching in %s s\n" $time
"""

[4]  Output from the above shell script in [3]:

"""
$ ./gnc_fq_helper_testing.sh | tee gnc_fq_helper_testing.log

Parallel calls.
(("USDAUD" (symbol . "USDAUD") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e1.4931) (currency . "USD"))
 ("USDBRL" (symbol . "USDBRL") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e4.2680) (currency . "USD"))
 ("USDCAD" (symbol . "USDCAD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e1.3297) (currency . "USD"))
 ("USDCHF" (symbol . "USDCHF") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e0.9749) (currency . "USD"))
 ("USDCNY" (symbol . "USDCNY") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e6.9694) (currency . "USD"))
 ("USDCZK" (symbol . "USDCZK") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e22.8401) (currency . "USD"))
 ("USDDKK" (symbol . "USDDKK") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e6.8223) (currency . "USD"))
 ("USDEUR" (symbol . "USDEUR") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e0.9127) (currency . "USD"))
 ("USDGBP" (symbol . "USDGBP") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e0.7745) (currency . "USD"))
 ("USDHKD" (symbol . "USDHKD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e7.7623) (currency . "USD"))
 ("USDHRK" (symbol . "USDHRK") (gnc:time-no-zone . "2020-02-07
12:00:00") (last . #e6.7882) (currency . "USD"))
 ("USDILS" (symbol . "USDILS") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e3.4306) (currency . "USD"))
 ("USDJPY" (symbol . "USDJPY") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e109.9700) (currency . "USD"))
 ("USDKRW" (symbol . "USDKRW") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e1184.1200) (currency . "USD"))
 ("USDMYR" (symbol . "USDMYR") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e4.1445) (currency . "USD"))
 ("USDNOK" (symbol . "USDNOK") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e9.2492) (currency . "USD"))
 ("USDNZD" (symbol . "USDNZD") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e1.5587) (currency . "USD"))
 ("USDRUB" (symbol . "USDRUB") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e63.3550) (currency . "USD"))
 ("USDSGD" (symbol . "USDSGD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e1.3856) (currency . "USD"))
 ("USDTWD" (symbol . "USDTWD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e30.0590) (currency . "USD"))
 #f)
Parallel fetching in 244 s

Serial calls.
(("USDAUD" (symbol . "USDAUD") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e1.4929) (currency . "USD")))
(("USDBRL" (symbol . "USDBRL") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e4.2680) (currency . "USD")))
(("USDCAD" (symbol . "USDCAD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e1.3297) (currency . "USD")))
(("USDCHF" (symbol . "USDCHF") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e0.9749) (currency . "USD")))
(("USDCNY" (symbol . "USDCNY") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e6.9694) (currency . "USD")))
(("USDCZK" (symbol . "USDCZK") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e22.8390) (currency . "USD")))
(("USDDKK" (symbol . "USDDKK") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e6.8219) (currency . "USD")))
(("USDEUR" (symbol . "USDEUR") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e0.9128) (currency . "USD")))
(("USDGBP" (symbol . "USDGBP") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e0.7746) (currency . "USD")))
(("USDHKD" (symbol . "USDHKD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e7.7623) (currency . "USD")))
(("USDHRK" (symbol . "USDHRK") (gnc:time-no-zone . "2020-02-07
12:00:00") (last . #e6.7882) (currency . "USD")))
(("USDILS" (symbol . "USDILS") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e3.4306) (currency . "USD")))
(("USDJPY" (symbol . "USDJPY") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e109.9700) (currency . "USD")))
(("USDKRW" (symbol . "USDKRW") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e1184.1200) (currency . "USD")))
(("USDMYR" (symbol . "USDMYR") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e4.1445) (currency . "USD")))
(("USDNOK" (symbol . "USDNOK") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e9.2470) (currency . "USD")))
(("USDNZD" (symbol . "USDNZD") (gnc:time-no-zone . "2020-02-10
12:00:00") (last . #e1.5585) (currency . "USD")))
(("USDRUB" (symbol . "USDRUB") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e63.3550) (currency . "USD")))
(("USDSGD" (symbol . "USDSGD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e1.3856) (currency . "USD")))
(("USDTWD" (symbol . "USDTWD") (gnc:time-no-zone . "2020-02-06
12:00:00") (last . #e30.0590) (currency . "USD")))
(#f)
Serial fetching in 213 s
"""

[5] Shell script using gnc-fc-helper in the same way GnuCash currently
does, i.e. in the "Serial calls" section.  The "parallel calls"
sections will obviously fail.

"""
#! /bin/sh

printf '\nParallel calls.\n'
start=`date +%s`
echo '(currency "USD" "AUD" "USD" "BRL" "USD" "CAD" "USD" "CHF" "USD"
"CNY" "USD" "CZK" "USD" "DKK" "USD" "EUR" "USD" "GBP" "USD" "HKD"
"USD" "HRK" "USD" "ILS" "USD" "JPY" "USD" "KRW" "USD" "MYR" "USD"
"NOK" "USD" "NZD" "USD" "RUB" "USD" "SGD" "USD" "TWD" "USD" "XAU")' |
gnc-fq-helper
end=`date +%s`
time=$((end-start))
printf "Parallel fetching in %s s\n" $time

printf '\nParallel calls.\n'
start=`date +%s`
echo '(currency "AUD" "BRL" "CAD" "CHF" "CNY" "CZK" "DKK" "EUR" "GBP"
"HKD" "HRK" "ILS" "JPY" "KRW" "MYR" "NOK" "NZD" "RUB" "SGD" "TWD"
"XAU")' | gnc-fq-helper
end=`date +%s`
time=$((end-start))
printf "Parallel fetching in %s s\n" $time

printf '\nSerial calls.\n'
start=`date +%s`
echo '(currency "USD" "AUD")' | gnc-fq-helper
echo '(currency "USD" "BRL")' | gnc-fq-helper
echo '(currency "USD" "CAD")' | gnc-fq-helper
echo '(currency "USD" "CHF")' | gnc-fq-helper
echo '(currency "USD" "CNY")' | gnc-fq-helper
echo '(currency "USD" "CZK")' | gnc-fq-helper
echo '(currency "USD" "DKK")' | gnc-fq-helper
echo '(currency "USD" "EUR")' | gnc-fq-helper
echo '(currency "USD" "GBP")' | gnc-fq-helper
echo '(currency "USD" "HKD")' | gnc-fq-helper
echo '(currency "USD" "HRK")' | gnc-fq-helper
echo '(currency "USD" "ILS")' | gnc-fq-helper
echo '(currency "USD" "JPY")' | gnc-fq-helper
echo '(currency "USD" "KRW")' | gnc-fq-helper
echo '(currency "USD" "MYR")' | gnc-fq-helper
echo '(currency "USD" "NOK")' | gnc-fq-helper
echo '(currency "USD" "NZD")' | gnc-fq-helper
echo '(currency "USD" "RUB")' | gnc-fq-helper
echo '(currency "USD" "SGD")' | gnc-fq-helper
echo '(currency "USD" "TWD")' | gnc-fq-helper
echo '(currency "USD" "XAU")' | gnc-fq-helper
end=`date +%s`
time=$((end-start))
printf "Serial fetching in %s s\n" $time
"""

[6] Output from the script in [5]:

"""
Parallel calls.
(#f)
Parallel fetching in 3 s

Parallel calls.
(#f)
Parallel fetching in 2 s

Serial calls.
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-10 11:38:51")
(last . #e1.4933) (currency . "AUD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-10 11:38:53")
(last . #e4.3143) (currency . "BRL")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-10 11:38:56")
(last . #e1.3295) (currency . "CAD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-10 11:38:57")
(last . #e0.9774) (currency . "CHF")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-10 11:38:59")
(last . #e6.9817) (currency . "CNY")))
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
(#f)
Serial fetching in 47 s
"""