title: How to extract web data in Visual Basic for Applications using XML HTTP requests?
body: <blockquote>
  <p>Version: Microsoft Visual Basic for Applications 7.1</p>
  </blockquote>
  <p>I'm working on a small scale data mining/ web data extraction personal project. My question is about data extraction, though.</p>
  <p>Extracting data from a web page using IE works but runs very slowly, and for this reason I'm leaning towards the use of XML HTTP requests. However, when I tried it on a website that I plan to work on, I couldn't extract the data that I need except for some static contents. Upon examining the response text, I found out that it does not contain the data that I need. Probably it is generated by JavaScript or similar technologies. I'm not sure if these scripts are rendered in VBA using XML HTTP requests just like in web browsers.</p>
  <p>Also, interesting to note here is upon examining the web page from the Developer Tools &gt; Network, it provides a <a href="https://www.pse.com.ph/stockMarket/companyInfo.html?method=fetchHeaderData&amp;ajax=true" rel="nofollow noreferrer">Request URL</a> in which the response contains most of the data that I need but it is in JSON format. I don't know how to parse it but I just provided this information so you may point me at the right direction just in case extracting data from a dynamic web page using XML HTTP request is not possible.</p>
  <p>I hope you can spend a couple of minutes to see my code and where I might have done it incorrectly.</p>
  <p>Thank you all very much. I'd really appreciate your help.</p>
  <p>Here's the basic idea of what I'm trying to do:</p>
  <p><strong>With XML (Unable to extract the data needed):</strong></p>
  <pre><code>Option Explicit
  
  Sub dataMinExProject_XML()
  
      Dim xmlPage As MSXML2.XMLHTTP60
      Dim htmlDoc As MSHTML.HTMLDocument
      Dim coName As MSHTML.IHTMLElement
      Dim secSym As MSHTML.IHTMLElement
      Dim closePrice As MSHTML.IHTMLElement
      Dim URL As String
      
      URL = &quot;https://www.pse.com.ph/stockMarket/companyInfo.html?id=260&amp;security=468&amp;tab=0&quot;
      
      Set xmlPage = New MSXML2.XMLHTTP60
      With xmlPage
          .Open &quot;POST&quot;, URL, False
          .send
      End With
      
      Do Until xmlPage.ReadyState = 4
          DoEvents
      Loop
      
      Set htmlDoc = New MSHTML.HTMLDocument
      htmlDoc.body.innerHTML = xmlPage.responseText
      
      Set coName = htmlDoc.getElementById(&quot;comTopInfoHead&quot;).Children(0)
      Set secSym = htmlDoc.getElementById(&quot;secSymbol&quot;)
      Set closePrice = htmlDoc.getElementById(&quot;headerLastTradePrice&quot;)
      
      Debug.Print &quot;Company Name: &quot;, &quot;&quot;&quot;&quot; &amp; coName.innerText &amp; &quot;&quot;&quot;&quot;
      Debug.Print &quot;Security Symbol: &quot;, &quot;&quot;&quot;&quot; &amp; secSym.innerText &amp; &quot;&quot;&quot;&quot;
      Debug.Print &quot;Closing Price: &quot;, &quot;&quot;&quot;&quot; &amp; closePrice.innerText &amp; &quot;&quot;&quot;&quot;
      
      xmlPage.abort
      Set xmlPage = Nothing
      MsgBox (&quot;alright!&quot;)
      
  End Sub
  </code></pre>
  <hr />
  <pre><code>Immediate Window
  
  Company Name:               &quot;BDO Unibank, Inc.&quot;
  Security Symbol:            &quot;&quot;
  Closing Price:              &quot;  &quot;
  </code></pre>
  <p>Upon examining the Immediate Window, it shows that the <code>Security Symbol</code> and the <code>Closing Price</code> were not extracted.</p>
  <p>Just for comparison, and to prove that the data to be extracted exists, I also provided my code here that uses an instance of IE.</p>
  <p><strong>With IE (Data extracted but runs relatively slow):</strong></p>
  <pre><code>Option Explicit
  
  Sub dataMinExProject_IE()
  
      Dim ieApp As SHDocVw.InternetExplorer
      Dim htmlDoc As MSHTML.HTMLDocument
      Dim coName As MSHTML.IHTMLElement
      Dim secSym As MSHTML.IHTMLElement
      Dim closePrice As MSHTML.IHTMLElement
      Dim URL As String
      
      URL = &quot;https://www.pse.com.ph/stockMarket/companyInfo.html?id=260&amp;security=468&amp;tab=0&quot;
      
      Set ieApp = New SHDocVw.InternetExplorer
      With ieApp
          .Navigate (URL)
          .Visible = vbTrue
      End With
      
      Do Until ieApp.ReadyState = READYSTATE_COMPLETE
          DoEvents
      Loop
      
      Set htmlDoc = ieApp.Document
      
      Set coName = htmlDoc.getElementById(&quot;comTopInfoHead&quot;).Children(0)
      Set secSym = htmlDoc.getElementById(&quot;secSymbol&quot;)
      Set closePrice = htmlDoc.getElementById(&quot;headerLastTradePrice&quot;)
      
      Do Until secSym.innerText &lt;&gt; vbNullString And closePrice.innerText &lt;&gt; vbNullString
          Loop
      DoEvents
      
      Debug.Print &quot;Company Name: &quot;, &quot;&quot;&quot;&quot; &amp; coName.innerText &amp; &quot;&quot;&quot;&quot;
      Debug.Print &quot;Security Symbol: &quot;, &quot;&quot;&quot;&quot; &amp; secSym.innerText &amp; &quot;&quot;&quot;&quot;
      Debug.Print &quot;Closing Price: &quot;, &quot;&quot;&quot;&quot; &amp; closePrice.innerText &amp; &quot;&quot;&quot;&quot;
      
      ieApp.Quit
      Set ieApp = Nothing
      MsgBox (&quot;alright!&quot;)
      
  End Sub
  </code></pre>
  <hr />
  <pre><code>Immediate Window
  
  Company Name:               &quot;BDO Unibank, Inc.&quot;
  Security Symbol:            &quot;BDO&quot;
  Closing Price:              &quot;130.50&quot;
  </code></pre>
  <p>Looking at the Immediate Window, it shows that it successfully extracted the data. However, as I've stated earlier, its poor performance made me look at other options..</p>
  <blockquote>
  <p>References:</p>
  <p><a href="https://stackoverflow.com/questions/51124993/web-scrapping-of-masked-url-using-vba/51125552#51125552">Web Scraping of Masked URL Using VBA</a></p>
  </blockquote>