Scrape a bloomberg website to get market cap into excel

Question

I am trying to extract market cap from the website "https://www.bloomberg.com/quote/206:HK" which is 1.059B in this case.

I would like to extract the market cap value into an excel column for a list of bloomberg tickers. I would like to do this in VBA and unfortunately not sure where to start from.

Basically I have a column with all the links to bloomberg. I would like to extract market cap values in a column next to it

score 0 · Answer 1 · answered Nov 22 '19 at 13:22

You ca do that with the code below. I use two steps to get the value. One can guess it works also over the css class value__b93f12ea. But the class name include a hex value and I know that this is often the case when such identifiers are dynamically generated.

Sub ScrapMarketCap()

Dim browser As Object
Dim url As String
Dim nodeMarketCapAll As Object
Dim nodeMarketCap As Object

  url = "https://www.bloomberg.com/quote/206:HK"

  'Initialize Internet Explorer, set visibility,
  'Call URL and wait until page is fully loaded
  Set browser = CreateObject("internetexplorer.application")
  browser.Visible = True
  browser.navigate url
  Do Until browser.ReadyState = 4: DoEvents: Loop

  'Get all html elements withh the css class "dataBox marketcap numeric"
  'in a node collection and get the first one by index (0)
  'There will be only one element with this class. But we still need to
  'specify the index, because we need the specific element from the node list
  '
  'We want this html in our dom object
  '<section class="dataBox marketcap numeric">
  '  <header class="title__49417cb9"><span>Market Cap</span></header>
  '  <div class="value__b93f12ea">1.074B</div>
  '</section>
  Set nodeMarketCapAll = browser.document.getElementsByClassName("dataBox marketcap numeric")(0)

  If Not nodeMarketCapAll Is Nothing Then
    'If we got the element
    'We take the value of the market cap from the first div tag
    Set nodeMarketCap = nodeMarketCapAll.getElementsByTagName("div")(0)

    If Not nodeMarketCap Is Nothing Then
      'If we got the div
      'We take the value from it
      MsgBox Trim(nodeMarketCap.innertext)
    End If
  End If
End Sub

Scrape a bloomberg website to get market cap into excel

1 Answers1