• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open a link using vba and find the value of currency

Tharabai

Member
Hi,

Out of curiosity, it is possible to open a link related to currency conversion to find the converted rate as of today using vba. eg from pounds to Euro or Euro to Rupees...
 
Here's simple code, that you can use to get "Prev Close:" exchange rate from Yahoo Finance. You can see how it works in attached.

Code:
Sub CurrencyClose()

    Dim url As String, lRow As Long
    Dim XMLHTTP As Object, wText As String
    Dim start_time As Date
    Dim end_time As Date
    Dim sClose As Long
    Dim eClose As Long

    lRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lRow

        url = "http://finance.yahoo.com/q?s=" & Cells(i, 1) & Cells(i, 2) & "=X"
        Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
        XMLHTTP.Open "GET", url, False
        XMLHTTP.send

        wText = XMLHTTP.responseText
        sClose = InStr(wText, "Prev Close:")
        eClose = InStr(sClose, wText, "</td>", vbTextCompare)
        Cells(i, 3).Value = Mid(wText, sClose + 44, eClose - sClose - 44)
    Next
 
End Sub

P.S. You can ignore Dim start_time and end_time. As these are leftover from the original code I had for Google Search that I recycled for this purpose.
 

Attachments

  • Currency_YahooFinance.xlsm
    17.4 KB · Views: 17
Last edited:
Hi, Thank you for the coding but got an error message in the below line

eClose = InStr(sClose, wText, "</td>", vbTextCompare)
Invalid procedure call or argument

Also, I have included some also currency conversions also (till row # 6)
 
So you got error when you used the sheet?

In that instance test by changing language setting to English (US). They've been known to cause issues.

Other causes can be...
1) Abbreviation used must be in standard ones used by Yahoo.com
2) Check your MSXML2 version, it should either be 3.0 or 6.0
 
Oh one other test. If you put code break at eClose line. What does sClose evaluate to? Say for EUROINR?
 
Back
Top