[GNC] Finance quote troubleshooting

[Andreas Vyrides]

Dear Geoff and Ralls,

I would like to let you know that I have finally realised and fixed the
issue. Since this is an open source application I will be describing all
steps that I have taken as well as describe my results from the
aforementioned 7 and a half points provided by Geoff. In case anyone else
reading this is not interested in what happened I will be adding a
TLDR:SOLUTION paragraph towards the end.

I partially agree with point #1 that our tests showed that my API key was
functioning adequately. Unfortunately as it turns out my antivirus (more
details on that later) was the culprit all along.

I had already done point number 2 at least 3 times by this point, but one
more time would not hurt, after all insanity is doing the same thing over
and over again and expecting different results. Of course that did not work.

Point #3 sort of goes a bit with point number 1, but at first glance, I had
no luck with it.

Point #4 although valid I had already searched high and low for an issue
with perl and alphavatage, but the vast issues were nothing like mine as
well as outdated, usually discussing issues with gnucash 2.6.

To be completely honest point #5 was no help at all. I did attempt
modifying the file and removing the # (commenting on that line) which could
potentially give more information, but no luck. I would still get "Urgh!
Nothing back"

I was not willing to even go through the process of point #7 since I
believe it would be quite a hustle to maintain a VM just for the sake of
running one application. I had already used Windows Sandbox as suggested by
Mr Ralls, which oddly enough was successful.

One of my last attempts was to test if this was working on other devices in
the house. I installed gnucash and perl on my brother's laptop. No
surprises there, it worked flawlessly.

Finally we get to point #6. I was already fed up at this point in time
since I have been trying to get this working for over a week, long before I
posted on the mail server. Sounds like a last ditch effort, why not try it,
it's not like I haven't wiped my laptop numerous times before.

I wiped the disk, installed a fresh version of Windows 10 pro (version
2004, latest at the time). First thing I installed was firefox, then
gnucash. After that I let the online price retrieval script run. I went
into cmd and types the following commands (# will be used for comments and
are not part of the command):

set ALPHAVANTAGE_API_KEY= #my key#

echo %ALPHAVANTAGE_API_KEY%  #make sure that the variable has the correct
value

cd .. #change into \gnucash\bin\ directory

perl gnc-fq-dump currency GBP EUR

After the final command, I got the correct result which was the exchange
rate at the moment.

At this point I was relieved and I wanted to know how I was unable to
connect to the server.
I installed my antivirus - ESET ENDPOINT SECURITY, allowed it to set up and
update. Then I ran the command "perl gnc-fq-dump currency GBP EUR" in the
same cmd window that I had before with the correct result This time I got
"1 EUR = <unknown> GBP"

At this point I knew that just disabling the antivirus and firewall were
not enough.

TLDR:SOLUTION

Some antiviruses have webmail and internet protection embedded into them,
just like mine. This might eventually lead to some of your traffic getting
blocked. I will discuss step by step what I did and where exactly I had to
navigate in this version of this specific antivirus that I own (as
mentioned above, ESET ENDPOINT SECURITY), you should be able to adapt
accordingly.


Go to >>SETUP<< (left side of the window), then at the bottom right >>
Advanced SetUp<<.
A new window will open, you will need to navigate to >>Web and Mail<<.
These should be an >>SSL/TLS<< list with multiple options.Now, if you don't
really care, you can just disable that and Bob's your Uncle. In the case
you want to keep that on for more security there are 2 more ways of going
about doing it below.

Method 1

Next to >>List of SSL/TLS filtered applications<< there should be an
>>Edit<< link for you to press. In the new window the list of applications
will include "C:\Strawberry\perl\bin\perl.exe" as well as a scan action,
often set up as >>Auto<<. Select the application and edit the option from
>>Auto<< to >>Ignore<< (a new window will pop up again with option). With
this you are done with method 1, as whenever perl runs, it will not be
blocked by the antivirus, but this means if you are not sure what you are
running, in the future something might get missed. This is where method 2
comes in.

Method 2

In the >>Advanced setup<< under >>SSL/TLS<< there should also be an option
for >>List of known certificates<<. Press >>Edit<<, a new window will
appear, you will need to press >>Add<< to create a new certificate. In the
new window, press >>URL<< and type "www.alphavantage.co", press >>OK<<. You
will have a number of options below about Access and Scan actions. I have
set mine up us >>Access action - Allow (even if untrusted)<< and >>Scan
action - Ignore<<. Press >>OK<< throughout all the windows you have opened,
save your changes and you are done

I would suggest running "perl gnc-fq-dump currency GBP EUR" to verify that
the request goes through in both methods.

Thank you for all your assistance. I hope in the future if anyone else
reads this, he will be able to solve this problem without having to wipe
his windows install.

Kind Regards

Andreas Vyrides



On Sun, 13 Sep 2020 at 01:57, Geoff <cleanoutmyshed at gmail.com> wrote:

> Well Andreas, this is not looking promising, and I suspect it is beyond
> my capabilities to solve.  Anyway, below are 7.5 further suggestions for
> you to try out at your discretion, ranging from the simple to the extreme.
>
> 1. These results have confirmed that your API key is good and you have
> connectivity to Alphavantage.  I very much doubt that your anti-virus
> will be the problem, but it won't hurt to momentarily turn it off to
> double check.
>
> 2. My next step would be to completely uninstall your Strawberry Perl
> installation, and double check to see that all related directories have
> been removed (and delete them if not).  Then do a vanilla installation
> exactly as you did on the Sandbox.
>
> 3. If that doesn't work, there might be something else on your PC - do
> you have any custom firewall rules for example?  Control Panel ->
> Firewall -> Advanced settings -> Outbound rules etc.
>
> 4. We seem to have established that this may be a Perl problem, it is
> definitely not a GnuCash one.  You could seek help on Perl forums
> instead of this list - perhaps https://stackoverflow.com/, or even Reddit.
>
> 5. If you feel comfortable, you could hack into the Finance::Quote code
> to try and get some diagnostics instead of just the "Urgh!  Nothing
> back" error from currency-lookup.pl.  The code for the currency function
> in Quote.pm is just calling the Alphavantage API and deciphering the
> results.  Start by removing the "#" at the start of line 277 to see what
> that "Failed" message prints out (see attached screenshot):
> #####
>    my $ALPHAVANTAGE_API_KEY = $ENV{'ALPHAVANTAGE_API_KEY'};
>    return undef unless ( defined $ALPHAVANTAGE_API_KEY );
>
>    my $try_cnt = 0;
>    my $json_data;
>    do {
>      $try_cnt += 1;
>      my $reply = $ua->request(GET "${ALPHAVANTAGE_CURRENCY_URL}"
>        . "&from_currency=" . ${from}
>        . "&to_currency=" . ${to}
>        . "&apikey=" . ${ALPHAVANTAGE_API_KEY} );
>
>      my $code = $reply->code;
>      my $desc = HTTP::Status::status_message($code);
>      return undef unless ($code == 200);
>
>      my $body = $reply->content;
>
>      $json_data = JSON::decode_json $body;
>      if ( !$json_data || $json_data->{'Error Message'} ) {
>        return undef;
>      }
> #     print "Failed: " . $json_data->{'Information'} . "\n" if
> (($try_cnt < 5) && ($json_data->{'Information'}));
>      sleep (20) if (($try_cnt < 5) && ($json_data->{'Information'}));
>    } while (($try_cnt < 5) && ($json_data->{'Information'}));
>
>    my $exchange_rate = $json_data->{'Realtime Currency Exchange
> Rate'}->{'5. Exchange Rate'};
> #####
>
> 6. Otherwise, are you are prepared to wipe and rebuild your Windows 10
> installation?  The Sandbox demonstrated it will work on a clean install.
>
> 7. The only other suggestion I have (not ideal) is to set up a virtual
> machine to run a Linux distribution to host GnuCash.  Google is your guide:
>
> https://www.windowscentral.com/how-run-linux-distros-windows-10-using-hyper-v
> https://itsfoss.com/install-linux-in-virtualbox/
>
> 7.5 Someone else on this list may have some suggestions?
>
>
> Good luck, and do let us know if you find a solution.
>
> Regards
>
> Geoff
> =====
> On 12/09/2020 11:32 pm, Andreas Vyrides wrote:
> > Dear Geoff,
> >
> > Thank you for all your feedback. I have done as you instructed.
> >
> > I was able to follow the instructions from the 3 first points with
> > success, as I am able to access the documentation, the demo key, as well
> > as the exchange rates using my own key. Please see the screenshot (6)
> > provided (using my own key)
> >
> > I was also able to do a clean install in windows sandbox, which was also
> > a success, where I am able to get forex data from both cmd as well as
> > gnucash (from F::Q) (please see attached screenshot 8).
> >
> > The issue at this point in time is that I am not aware of how to
> > proceed, in order to allow F::Q to work on my machine without requiring
> > the use of sandbox. I have already removed and re-installed gnucash and
> > F::Q multiple times without any changes. On first look one of the things
> > that differs from the native windows environment and sandbox is the
> > presence of my antivirus, which would be an unlikely candidate as I have
> > the same antivirus in a desktop computer and F::Q works fine.
> >
> > I am open to any type of suggestions.
> >
> > I appreciate all your help and feedback helping me deal with this issue.
> >
> > Kind Regards
> >
> > Andreas Vyrides
> >
> > On 12/09/2020 12:19, Geoff wrote:
> >> OK Andreas, so this isn't really a GnuCash issue per se, and we seem
> >> to have narrowed down the problem to one of Perl / Finance::Quote /
> >> Alphavantage / Your computer.
> >>
> >> The next steps are to independently verify the connection from your
> >> computer to Alphavantage.
> >>
> >> Step 1: Can you access their API documentation page?
> >>
> >> https://www.alphavantage.co/documentation/
> >>
> >> Step 2: Can you call their API using their *demo* API Key?
> >>
> >>
> https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=USD&to_currency=JPY&apikey=demo
> >>
> >>
> >> Step 3: Can you call their API using *your* API Key?
> >> Don't forget to append your actual API key to the URL:
> >>
> >>
> https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=GBP&to_currency=EUR&apikey==__Your_Actual_Key__
> >>
> >>
> >> See attached screenshot.
> >>
> >>
> >> If all of the above are working, my recommendation would be to
> >> uninstall Perl and reinstall from scratch *or* (if you have Windows 10
> >> Professional) you could use a Windows 10 Sandbox to do a clean
> >> installation of both GnuCash and Perl to try and further isolate the
> >> cause of the problem.
> >>
> >>
> https://www.howtogeek.com/399290/how-to-use-windows-10s-new-sandbox-to-safely-test-apps/
> >>
> >>
> >>
> >> Good luck!
> >>
> >> Geoff
> >> =====
> >> P.S. What version of Windows do you have?
> >> P.P.S.  What version of Strawberry Perl are you running?
> >> perl -v
> >>
> >> On 12/09/2020 6:41 pm, Andreas Vyrides wrote:
> >>> Dear Geoff and Ralls
> >>>
> >>> After following along with the provided link, I ended up running
> >>> "force install Finance::Quote", to verify the integrity of the
> >>> module, and tried a different API key. The similarities end in the
> >>> post on the 8th of august, where Samantha is able to query
> >>> alphavantage and get a correct exchange rate via the terminal, where
> >>> I am not. Please see the attached screenshot for the information
> >>> described above.
> >>>
> >>> All previous tests that I have run in cmd remain the same
> >>>
> >>> Kind Regards
> >>>
> >>> Andreas Vyrides
> >>>
> >>> On 12/09/2020 08:54, Andreas Vyrides wrote:
> >>>>
> >>>> Dear Geoff
> >>>>
> >>>> I apologise for the misunderstanding. Yes I did run set my
> >>>> alphavantage key before running the commands. Please see the
> >>>> attached screenshot.
> >>>>
> >>>> I will have a close look at the link provided. In case you have any
> >>>> other ideas about why this is happening, please do not hesitate to
> >>>> contact me
> >>>>
> >>>> Kind Regards
> >>>>
> >>>> Andreas Vyrides
> >>>>
> >>>> On 12/09/2020 01:59, Geoff wrote:
> >>>>> Dear Andreas
> >>>>>
> >>>>> Did you set your Alphavantage key as an environment variable before
> >>>>> you ran this test script?  Try entering these commands first:
> >>>>>
> >>>>> set ALPHAVANTAGE_API_KEY=__Your_Actual_Key__
> >>>>> echo %ALPHAVANTAGE_API_KEY%
> >>>>>
> >>>>> See attached screenshot.
> >>>>>
> >>>>> See also this long thread on resolving a perl related currency
> >>>>> problem on Windows:
> >>>>>
> >>>>>
> http://gnucash.1415818.n4.nabble.com/GNC-Difficulty-with-online-price-retrieval-John-Ralls-tc4720222.html
> >>>>>
> >>>>>
> >>>>> Regards
> >>>>>
> >>>>> Geoff
> >>>>> =====
> >>>>>
> >>
>