locked
VBA Function to retrieve stock name from Google really slow RRS feed

  • Question

  • I hope this is the right place for the this post.  I found this VBA function in searching for how to retrieve a stocks name from Google finance, since Google shutdown it's finance API.

    I had to modify it to find the name and then check and replace an ampersand, but it is really slow.  I have a similar function that gets the Yahoo stock name, but Yahoo returns a truncated name and I prefer to have a full name.

    Can someone take a long at the code and see if their is a way to speed up this function?

    Thanks

    Harry

    Public Function GetGoogName(Symbol As String) As Variant
        Dim xmlhttp As Object
        Dim strURL As String
        Dim CompanyName As String
        Dim x As String
        Dim sSearch As String
         
        strURL = "http://www.google.com/finance?q=" & Symbol
        Set xmlhttp = CreateObject("msxml2.xmlhttp")
        With xmlhttp
            .Open "get", strURL, False
            .Send
            x = .ResponseText
        End With
         'find goolge's "Company name"
        sSearch = "_companyName ="
        'Find element in responsetext
        CompanyName = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
        'Pull out compnay from above string
        CompanyName = Trim(Mid(CompanyName, InStr(1, CompanyName, "'") + 1, InStr(1, CompanyName, ";") - 4))
        'Replace apersand, if there is one, in compnay name
        GetGoogName = Replace(CompanyName, "\x26", "&")
        Set xmlhttp = Nothing
         
    End Function
    

    Wednesday, June 11, 2014 2:30 PM

All replies

  • Hi,

    Thank you for posting in the MSDN Forum.

    Since the issue is more related to the feature of VBA and msxml2.xmlhttp object rather than Excel Object Model, I'd like to move it to VBA forum. You could also resort to XML, System.Xml, MSXML and XmlLite forum for more effective responses.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 12, 2014 6:19 AM