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

Edward d'Auvergne true.bugman at gmail.com
Tue Feb 11 03:59:35 EST 2020 

On Mon, 10 Feb 2020 at 22:28, Mike Alexander <mta at umich.edu> wrote:
> On 10 Feb 2020, at 6:27, Edward d'Auvergne wrote:
>> 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.
>
> I think I understand now why we've been talking past each other. There isn't any API in F::Q to fetch multiple currency quotes in one call. Instead it's a feature of AlphaVantage to treat exchange rates as if they were stock quotes and use the stock quote API to fetch them. I wasn't aware that this existed.

I thought this was also the case with the old Yahoo API.  Maybe I am
confusing this with the original Finance::YahooQuote perl module that
does not separate currencies from other symbols?


> I played with it for a few minutes and see a couple of problems with it. Some currencies are not available using the stock quote API but are available using the currency API. For example three in my list of currencies which don't work with the quote API are MNT, SBD, and VUV. These all work fine with the currency API.
>
> The other problem is that for very small exchange rates the stock quote interface loses precision. For example, compare
>
> ./gnc-fq-dump alphavantage IDRUSD
> Finance::Quote fields Gnucash uses:
> symbol: IDRUSD <=== required
> date: 02/10/2020 <=== recommended
> currency: USD <=== required
> last: 0.0001 <=\
> nav: <=== one of these
> price: <=/
> timezone: <=== optional
>
> and
>
> ./gnc-fq-dump currency IDR USD
> 1 IDR = 7.295e-05 USD
>
> These problems would make the quote API for currencies less useful.

F::Q uses this URL for all queries:

https://www.alphavantage.co/query?function=GLOBAL_QUOTE&datatype=json&symbol=IDRUSD&apikey=$MY_KEY

The result I see is:

{
    "Global Quote": {
        "01. symbol": "IDRUSD",
        "02. open": "0.0001",
        "03. high": "0.0001",
        "04. low": "0.0001",
        "05. price": "0.0001",
        "06. volume": "0",
        "07. latest trading day": "2020-02-10",
        "08. previous close": "0.0001",
        "09. change": "0.0000",
        "10. change percent": "0.0000%"
    }
}

It looks like this code then kicks in, from the "currency" subroutine
in lib/Finance/Quote.pm:

"""
if ( $exchange_rate < 0.001 ) {
    # exchange_rate is too little. we'll get more accuracy by using
    # the inverse rate and inverse it
    my $inverse_rate = $this->currency( $to, $from );
    {
        local $^W = 0;
        return undef unless ( $exchange_rate + 0 );
    }
    $exchange_rate = int( 100000000 / $inverse_rate + .5 ) / 100000000;
}
"""

The "fetch" subroutine simply does not do that.  I guess you would
first have to identify that the symbol is a currency pair, then invert
it.  Not too difficult.  But then it comes back to the issue of
fixing/modifying F::Q itself.


> By the way, I didn't mean to suggest that we should encourage all users of GnuCash to patch F::Q. I still remain hopeful that the maintainers will merge my pull request that fixes this bug. In the meantime switching to the quote API might be a good idea even if it does have issues. The code would have to be smart enough to work around the problems. For example if the quote API fails for a given currency it could try the currency API. If the returned value appears to have lost all precision then it could try the reverse quote and compute the reciprocal itself.

I finally found your pull request:
https://github.com/finance-quote/finance-quote/pull/130.  Your
changes, that were rejected, allow my previous script [1] to
successfully fetch the quotes in serial [2].  I'll try to create a
pure perl script to demonstrate that your bug fix is a proper bug fix,
and that they should reopen your pull request, merge it, and then
release F::Q v1.50!  If that works, then there wouldn't be any changes
required on the GnuCash side.  Well, apart from the error handling to
prevent GnuCash from segfaulting.


> Another possibility would be to abandon AlphaVantage entirely for currency exchange rates. For example https://currencylayer.com/ looks promising. They allow up to 250 queries per month for free and it seems very fast. Of course this would require changes to F::Q which gets us right back into those problems.

Maybe the user should be allowed to choose their favourite quote
provider via an environmental variable that F::Q recognises.

Regards,

Edward


[1] The script from my previous mail in [5]:

"""
#! /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
"""


[2]  The output from [1] with Mike's pull request changes:

"""
$ ./gnc_fq_helper_testing2.sh

Parallel calls.
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:46:05")
(last . #e1.4894) (currency . "AUD")))
Parallel fetching in 2 s

Parallel calls.
(("AUD" (symbol . "AUD") (gnc:time-no-zone . "2020-02-11 09:46:07")
(last . #e2.9031) (currency . "BRL")))
Parallel fetching in 2 s

Serial calls.
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:46:09")
(last . #e1.4894) (currency . "AUD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:46:11")
(last . #e4.3236) (currency . "BRL")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:46:13")
(last . #e1.3294) (currency . "CAD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:46:15")
(last . #e0.9775) (currency . "CHF")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:46:58")
(last . #e6.9757) (currency . "CNY")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:47:00")
(last . #e22.909) (currency . "CZK")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:47:02")
(last . #e6.8503) (currency . "DKK")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:47:04")
(last . #e0.9169) (currency . "EUR")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:47:06")
(last . #e0.775) (currency . "GBP")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:48:09")
(last . #e7.7636) (currency . "HKD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:48:11")
(last . #e6.8318) (currency . "HRK")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:48:13")
(last . #e3.4204) (currency . "ILS")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:48:17")
(last . #e109.86) (currency . "JPY")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:48:18")
(last . #e1182.8) (currency . "KRW")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:49:01")
(last . #e4.136) (currency . "MYR")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:49:03")
(last . #e9.2716) (currency . "NOK")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:49:05")
(last . #e1.5647) (currency . "NZD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:49:06")
(last . #e63.922) (currency . "RUB")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:49:08")
(last . #e1.3877) (currency . "SGD")))
(("USD" (symbol . "USD") (gnc:time-no-zone . "2020-02-11 09:50:12")
(last . #e29.993) (currency . "TWD")))
(("XAU" (symbol . "XAU") (gnc:time-no-zone . "2020-02-11 09:50:14")
(last . #e1568.9) (currency . "USD")))
Serial fetching in 247 s
"""

More information about the gnucash-devel mailing list