Web scraping Exchange rates

[gruntingmonkey]

Hello, I am looking to automate getting an exchange rate off the following site: Currency converter online

I thought I could work my way through it but seem to get stuck when I'm trying to manipulate the web page (not even in code!!!)

I would appreciate if anyone had any ideas. Also, it doesn't have to be from that site, it just seemed easier than googles currency section!

Sub GetRates()
'''Reference Microsoft Internet Controls and Microsoft HTML controls

Dim ie As New SHDocVw.InternetExplorer

ie.Visible = True
ie.Navigate "http://www.convertmymoney.com/"

'''Wait for ie.ready
Do While ie.ReadyState <> READYSTATE_COMPLETE Or ie.Busy
Loop

'''change currency
ie.Document.getElementById("to-list").Value = EUR''' or whatever currency

End sub

 

[J.Ty.]

Hi,

This page seems much easier to scrap in Excel.

J.Ty.

 

[gruntingmonkey]

So, I looked at that site and it confused me. I have just seen the converter widget though but cant work out how to manipulate it from code.

 

[J.Ty.]

You do not have to manipulate it from code.
Open Excel, go to Ribbon->Data->From Web, enter the URL, choose Web view and after a few steps of the dialogue you will have all data in Excel, and moreover, it will be updated dynamically. You can control it from Ribbon->Data->Existing connections.

J.Ty.

 

[KOKOSEK]

try this:

Sub DisplayRates()
Dim strURL As String
Dim objHTTP
Dim WebResponse
Dim d As Long
Dim pairs As Variant
Dim CurrAcr As Variant
Dim CRate As Variant
Application.ScreenUpdating = False
'https://api.exchangeratesapi.io/history?start_at=2017-12-25&end_at=2018-01-02&symbols=CAD&base=EUR

strURL = "https://api.exchangeratesapi.io/latest?base=EUR"

'Debug.Print strURL  'The Url that is being called
Range("A2:B50").ClearContents

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Open "GET", strURL, False
objHTTP.send
If objHTTP.Status = "200" Then
    objHTTP.WaitForResponse
    'WebResponse = objHTTP.responseText
    WebResponse = Mid(objHTTP.responseText, 11, Len(objHTTP.responseText) - 45)
    pairs = Split(WebResponse, ",")
    'Debug.Print WebResponse
End If
Set objHTTP = Nothing
For d = LBound(pairs) To UBound(pairs)
    CurrAcr = Replace(Split(pairs(d), ":")(0), """", "")
    CRate = Split(pairs(d), ":")(1)
    Range("A" & d + 2).Value = CurrAcr
    Range("B" & d + 2).Value = CRate
Next d
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

[gruntingmonkey]

Both of the above methods work, HOWEVER, they are pulling back different data! Using the web connection I can see that it brings back the inverse figure in the same cell which makes it tricky to deal with as there is no set decimal figure, but it does bring back the same figure as in the web page.

The coding brings back exactly what I want, but I cant find anywhere on the web page where the figure matches. Any ideas?

 

[gruntingmonkey]

I should state that I have changed the Base rate to GBP and not Euro so its not that.

 

[J.Ty.]

Both of the above methods work, HOWEVER, they are pulling back different data! Using the web connection I can see that it brings back the inverse figure in the same cell which makes it tricky to deal with as there is no set decimal figure, but it does bring back the same figure as in the web page.

The coding brings back exactly what I want, but I cant find anywhere on the web page where the figure matches. Any ideas?

I see. It still seems that the automated tool in Excel is much easier to work with, when compared to writing custom code. So the solution is to search for a page whose structure does not confuse Excel. This one doesn't, but there might be even better ones out there.

J.Ty.

 

[gruntingmonkey]

I see. It still seems that the automated tool in Excel is much easier to work with, when compared to writing custom code. So the solution is to search for a page whose structure does not confuse Excel. This one doesn't, but there might be even better ones out there.

J.Ty.

I found that exact one and have used the Web method. I guess I'm always interested about learning the code and therefore would still like to understand why it brings back different results. I have achieved what I needed to do, the rest is just extra learning for me!

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.