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!

 

[pullmyefinger]

I want to do the same thing also, but it would seem to me that we need to go to XE or wherever and ask them how to go about getting the exchange rate?

My only other guess is to use AutoIT and write a vb macro to pull the rate out of the website, but again, with their approval. If I get it to work I will let you know.

 

[circledchicken]

Hi,

Here's a VBA solution using Google Finance as an example. After pasting the code in a module (and setting the appropriate references mentioned in the code), you would use it in your worksheet through the formula:

=GFQuote("USDGBP")

changing the string to whichever currency pair was required. Although as you mentioned I have no idea if this is allowed, and you probably should check you have permission to extract their data in this way.

Option Explicit
' set references to Microsoft XML and Microsoft HTML Object Library
' (in the VBA Editor go to Tools>References... and tick the appropriate boxes)

Function GFQuote(sCP As String) As String
    
Dim oHttp As MSXML2.XMLHTTP
Dim sURL As String
Dim HTMLDoc As HTMLDocument

' Create an XMLHTTP object
Set oHttp = New MSXML2.XMLHTTP
         
' get the URL to open
sURL = "http://www.google.com/finance?q=" & sCP
    
' open socket and get website html
oHttp.Open "GET", sURL, False
oHttp.Send
Set HTMLDoc = New HTMLDocument
With HTMLDoc
    ' assign the returned text to a HTML document
    .body.innerHTML = oHttp.responseText
    ' parse the result
    GFQuote = .getElementById("currency_value").innerText
    GFQuote = Mid(GFQuote, InStr(1, GFQuote, "=") + 2, InStr(1, GFQuote, Right(sCP, 3)) - InStr(1, GFQuote, "=") - 3)
End With

'Clean up
Set oHttp = Nothing

End Function

 

[jamesxv]

Sorry for bringing up this old post, but the VBA code that circledchicken provided above works excellently.

I'm wondering if anyone can help me revise the code so that it works with XE.com?

I don't know anything about VBA and from experimenting I cannot get it to work.

Google doesn't show accurate rates for some currencies, and some currencies are not even available.

Would really appreciate help to get this VBA module to work with XE. Thank you!

 

[circledchicken]

Sorry for bringing up this old post, but the VBA code that circledchicken provided above works excellently.

I'm wondering if anyone can help me revise the code so that it works with XE.com?

I don't know anything about VBA and from experimenting I cannot get it to work.

Google doesn't show accurate rates for some currencies, and some currencies are not even available.

Would really appreciate help to get this VBA module to work with XE. Thank you!

Hi jamesxv and welcome to the forum,

I am happy to try and help but I think you need explicit permission from XE.com to extract and use their data in this way. From a quick look at the website they offer a number of data feed options (at a cost) and I suspect you may have to use that. Even for the Google Finance example, you should be sure you have the appropriate approval before extracting any data.

 

[jamesxv]

Hi jamesxv and welcome to the forum,

I am happy to try and help but I think you need explicit permission from XE.com to extract and use their data in this way. From a quick look at the website they offer a number of data feed options (at a cost) and I suspect you may have to use that. Even for the Google Finance example, you should be sure you have the appropriate approval before extracting any data.

Hi circledchicken,

Thank you for the reply. I think the data feed is a commercial product with accurate rates for corporations to use, I just need an approximate rate they give for free for my use, I'm not selling something. $540 / year for my personal use is way too much.

If you're not comfortable helping with this, it's no problem. If you know another way that I can get exchange rates using an Excel function() please let me know. It's just to save me time by not having to input stuff manually on a website.

The one that comes with MSN/Excel is way too limited.

Edit: Also just to note I'm using Excel version 2010.

 

[circledchicken]

Hi circledchicken,

Thank you for the reply. I think the data feed is a commercial product with accurate rates for corporations to use, I just need an approximate rate they give for free for my use, I'm not selling something. $540 / year for my personal use is way too much.

If you're not comfortable helping with this, it's no problem. If you know another way that I can get exchange rates using an Excel function() please let me know. It's just to save me time by not having to input stuff manually on a website.

The one that comes with MSN/Excel is way too limited.

I understand, but unfortunately I'm not sure they would want any data being scraped from their website. If you can get confirmation from them, then I am happy to help. Hopefully in the meantime someone else may be able to help with another solution.

 

[jamesxv]

I understand, but unfortunately I'm not sure they would want any data being scraped from their website. If you can get confirmation from them, then I am happy to help. Hopefully in the meantime someone else may be able to help with another solution.

I contacted legal@xe, let's see what they say.

If anyone else has any other solution, please let me know.

Thanks again.

 

[jamesxv]

No reply from XE.

Out of curiosity, where does it say that you cannot get data from them this way? I would assume they would block such connections to their server if it wasn't allowed.

 

[circledchicken]

No reply from XE.

Out of curiosity, where does it say that you cannot get data from them this way? I would assume they would block such connections to their server if it wasn't allowed.

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