VBA - Pulling value from webpage?

[tazrockon]

I'm working on a finance assignment analyzing stock returns and I want to be able to pull the website HTML code that contains the stock information from a website, preferably in a string, and parse out the price and other information. I used to know how to do this in VB6 but I have no idea how to go about it in Excel with VBA. Any ideas? Thanks!

[Leith Ross]

Hello tazrockon,

Welcome to the Forum!

I know two ways to get the page source (HTML text) from a site it. One it to use an API call (the quickest). The other way is to use Internet Explorer. It can also be done using XML but I am not fluent in XML.

Parsing can be done automatically if you use a Web Query. The Web Query eliminates having to pull the text in, store it, and then write a parsing routine. It will also load the text directly to the worksheet. I would try the Web Query first.

[tazrockon]

Thanks, Leith Ross!

I was able to record a macro of me pulling the table I needed off the finance website through a web query and then loop the code to get the prices of all the stocks in my portfolio.

The only issue I have is this is a (relatively) slow process, taking up to a couple seconds for each data retrieval. You mentioned that an API call would be quicker, so could you point me in the general direction of more information on this method?

Thanks!

[Leith Ross]

Hello tazrockon,

The API call will return the source page text. You will have to write the code to parse and load the worksheet. Here is the macro. Copy and paste this code into a VBA module in your workbook's VBA project.

Function GetSourceText(URL As String) As String

  Dim Request As Object

    On Error Resume Next
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
      If Request Is Nothing Then
         Set Request = CreateObject("WinHttp.WinHttpRequest.5")
      End If
    Err.Clear
    On Error GoTo 0

    Request.Open "GET", URL, False
    Request.Send
    
   'When using this property in synchronous mode, the limit to the number of
   'characters it returns is approximately 2,169,895.
    GetSourceText = Request.responsetext
    
End Function

Example of Using the Function

    Dim PageSource As String

        PageSource = GetSourceText("www.google.com")