Pulling exchange rate data from a website

[BokiB]

Hey guys,

I would appreciate your help with this issue.

After searching multiple exchange rate websites (Oanda, Xe etc...), I've finally found one where the URL changes based on the combination of currencies (Yahoo Finance).

Examples:
USD to EUR: http://finance.yahoo.com/currency-converter/#from=USD;to=EUR;amt=1

USD to CHF: http://finance.yahoo.com/currency-converter/#from=USD;to=CHF;amt=1

I need Excel to pull data from this website for various combinations (USD to EUR, USD to CHF, EUR to JPY etc etc)

The only problem is that when go to Data->from Web and insert the URL, there is no way for me to select just the exchange rate.

Example: USD 1 = CHF 0.9611

I just need to pull the bold number from the website.

Any idea how?

Thanks for your help!

 

[jamesxv]

It is not clear to me what is allowed and what isn't and how you define personal use, but it is their data and I think it is best to get their approval. There is a short section on the legal issues associated with web scraping here: Web scraping - Wikipedia, the free encyclopedia

When I called XE they didn't know what I was talking about and told me to email legal, which I did and never heard back. I read their terms and they aren't allowing their content to be displayed on another website. That's all. Here's the quote:

present, archive, cache, frame, scrape, or mirror any Information And Content from any part of the Services within another web site, except with express written permission from XE

As I mentioned I only wanted to automate the process for my personal use because I have money in foreign currencies. I'm not going to put it on a website, I only wanted it for Excel calculations.

In any case, thank you anyways for the offer to help, since I'm not hearing back from XE I don't know what else to do than to move on and just do it all manually, which sucks but oh well.

Cheers,

James

 

[circledchicken]

If you look at the source code on the website (right-click and view page source), there are several warnings noting that automated extraction of rates is prohibited. As you said, it may be that it is only prohibited for commercial use or use on another website but I'm not sure until they reply to you. Here is a sample of what it says:

< !-- WARNING: Automated extraction of rates is prohibited under the Terms of Use. -- ><!-- WARNING: Automated extraction of rates is prohibited under the Terms of Use. -->

 

[jamesxv]

Finally got in touch with XE's legal department. You are right. They do not allow it. Thanks anyways.

 

[circledchicken]

Finally got in touch with XE's legal department. You are right. They do not allow it. Thanks anyways.

That's not good - hope you find an alternative solution.

 

[djwaas]

Dear CircledChicken:

Your solution above is very appealing!
Unfortunately, I must be doing something wrong, because the GFQuote function returns an error message (the cell displays (#NOM? which in french means #NAME?)).
It's as if the VBA code is not being taken into account.

What I did (I'm quite a newbie):
I opened the VBE, I pasted your code, then I checked the two references (I guess XML version 3 4 or 6 doesn't matter), then Debug>compile code, then back to the worksheet, then paste on a cell "=GFQuote("MADUSD")", the cell displayed "#NOM?".

What did I do wrong?

Thanks for the help!

BR

 

[circledchicken]

Hi and welcome to the forum,

The code for the user defined function needs to be placed in a Module (i.e. Insert -> Module in the editor and then paste it there),

In any case, more importantly you need to first check you have permission to do this. Even if its just for personal use I think you are likely to find that this method of extracting data is not allowed within the terms and conditions.

 

[djwaas]

Hi and welcome to the forum,

The code for the user defined function needs to be placed in a Module (i.e. Insert -> Module in the editor and then paste it there),

In any case, more importantly you need to first check you have permission to do this. Even if its just for personal use I think you are likely to find that this method of extracting data is not allowed within the terms and conditions.

Thanks a lot! It's working like a charm.
I wonder if hitting the "Refresh data" button will update the currecy values..

Cheers!

 

[circledchicken]

You're welcome but again - you need to request permission as using this method otherwise is likely to be against the websites terms of use.