1

A tricky question on VBA Please see pictures for your accurate view of the problem

If you type on Google "SEC EDGAR" Step 1 , and you enter BDX in the Company Search field Step 2 click Search and then in the search results you click the first Document button Step 3 and you choose 10-K you get to the filing Detail. From there if we choose XBRL INSTANCE DOCUMENT Step 4 . Finally inside the XML document in the elements Million Pains-For-Me Question as you can see in the last picture we have the element and it's name.

Is there anyway to pull this thing by using VBA and put it in the Excel cell of our preference? I am a beginner in VBA so please be kind to also explain the code a little more and also what References are we adding from Tools in VBE and why.

What i actually am looking for is a code block which grabs the data between the XML elements (**Important: The XML Instance document should be in the Web location provided.) **For example in <Current> 5 </Current)> something that will pull the 5 and populate a cell designated in the code block and some instructions so i can hold on to it and study the code****

THIS QUESTION IS ANSWERED. IN ORDER FOR THE CODE IN THE ANSWER TO WORK YOU MUST HAVE TURNED ON THE Microsoft XML, V6.0 on the VBE's Tools-->References

ExoticBirdsMerchant
  • 1,466
  • 8
  • 28
  • 53
  • 2
    Yes it is possible. Look at the [HtmlDocument Class Reference](http://msdn.microsoft.com/en-us/library/System.Windows.Forms.HtmlDocument(v=vs.110).aspx), as it contains most of what you'll need. More specifically, look at the `GetElementsByTagName` method. Maybe also look at this [previous question](http://stackoverflow.com/questions/7393236/reading-web-pages-using-excel-vba) – Bernard Saucier Jan 28 '14 at 17:57
  • 2
    I think that you could just use a URL and cut out 2/3rd's of these screenshots in your question. – RBarryYoung Feb 07 '14 at 16:33
  • 1
    Agree with @RBarryYoung. This is possible in almost any programming language. Personally, I can do this in 3 languages, with varying difficulties. It's called web scraping/extraction and what you're aiming to do is not really "extremely" difficult, as your bounty states. It's just a bit of work. In Excel, for example, it can be done using simple DOM work, or via Selenium, and you won't even need more than 200 lines of code. :) – WGS Feb 07 '14 at 16:48
  • @BK201 Can we chat about that? – ExoticBirdsMerchant Feb 07 '14 at 16:53
  • 1
    We can, but I must admit that I won't be spending time unless you understand very well the concept of web scraping. I suggest you find one example here in SO, undertand it, and come back. ;) – WGS Feb 07 '14 at 17:27

1 Answers1

1

You should still try and learn about Microsoft XML Core Services (MSXML) but to get you started you can start with the following and modify it as needed. If you need a lot of values it would be worth looping the ChildNodes of objXMLNodexbrl as a lot of the nodes also seem to be duplicated.

instead of using <Current> in my sample I used an actual value for us-gaap:DebtInstrumentInterestRateStatedPercentage (the one you had circled in your question)

Sub GetNode()
Dim strXMLSite As String
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim objXMLDoc As MSXML2.DOMDocument
Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
Dim objXMLNodeDIIRSP As MSXML2.IXMLDOMNode

Set objXMLHTTP = New MSXML2.XMLHTTP
Set objXMLDoc = New MSXML2.DOMDocument

strXMLSite = "http://www.sec.gov/Archives/edgar/data/10795/000119312513456802/bdx-20130930.xml"

objXMLHTTP.Open "POST", strXMLSite, False
objXMLHTTP.send
objXMLDoc.LoadXML (objXMLHTTP.responseText)

Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("xbrl")

Set objXMLNodeDIIRSP = objXMLNodexbrl.SelectSingleNode("us-gaap:DebtInstrumentInterestRateStatedPercentage")

Worksheets("Sheet1").Range("A1").Value = objXMLNodeDIIRSP.Text
End Sub
user2140261
  • 7,855
  • 7
  • 32
  • 45