Great detective work Andreas!
And thanks for sharing your solution.
I hope the rest of your Gnucash voyage is plain sailing.
Regards
Geoff
=====
On 13/09/2020 9:27 pm, Andreas Vyrides wrote:
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
<http://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 <[email protected]
<mailto:[email protected]>> 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 <http://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
>>>>> =====
>>>>>
>>
_______________________________________________
gnucash-user mailing list
[email protected]
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.
