in:gaap xbrl Selectsinglenode with multiple attrributes returns nothing in VBA

Question

I am trying to extract the values from an XBRL xml file. I don't have a knack on MSXML capabilities of VBA. The XML file is

    <?xml version="1.0" encoding="UTF-8"?>

    <!-- XBRL Instance Document Created By :- Relyon Softech Ltd. -->

    <!-- Date/time created: 29/10/2013 4:47:38 PM -->
    <xbrli:xbrl xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xmlns:xbrli="http://www.xbrl.org/2003/instance" xmlns:xbrldi="http://xbrl.org/2006/xbrldi" xmlns="http://www.xbrl.org/2003/instance" xmlns:in-ca-roles="http://www.icai.org/xbrl/taxonomy/2012-03-31/in-ca-roles" xmlns:negated="http://www.xbrl.org/2009/role/negated" xmlns:in-gaap="http://www.icai.org/xbrl/taxonomy/2012-03-31/in-gaap" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:iso4217="http://www.xbrl.org/2003/iso4217" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:num="http://www.xbrl.org/dtr/type/numeric" xmlns:net="http://www.xbrl.org/2009/role/net" xmlns:in-roles="http://www.icai.org/xbrl/taxonomy/2012-03-31/in-gaap-roles" xmlns:in-ca="http://www.icai.org/xbrl/taxonomy/2012-03-31/in-ca" xmlns:ref="http://www.xbrl.org/2006/ref" xmlns:in-ci-ent="http://www.icai.org/xbrl/taxonomy/2012-03-31/in-gaap/in-ci-ent" xmlns:link="http://www.xbrl.org/2003/linkbase" xmlns:nonnum="http://www.xbrl.org/dtr/type/non-numeric" xmlns:xbrldt="http://xbrl.org/2005/xbrldt" xmlns:targetNamespace="http://www.icai.org/xbrl/taxonomy/2012-03-31/in-gaap/in-ci-ent">
       <link:schemaRef xlink:href="http://www.mca.gov.in/XBRL/2012/08/02/Taxonomy/CnI/in-ci-ent-2012-03-31.xsd" xlink:type="simple"/>
          <xbrli:context id="D2012">
             <xbrli:entity>
               <xbrli:identifier scheme="http://www.mca.gov.in/CIN">U63010MH1993PLC075480</xbrli:identifier>
             </xbrli:entity>
            .................
          </xbrli:context>
          <in-gaap:RevenueFromSaleOfProducts id="CYTAG60" unitRef="INR" contextRef="D2013" decimals="-3">0</in-gaap:RevenueFromSaleOfProducts>
          <in-gaap:RevenueFromSaleOfProducts id="PYTAG60" unitRef="INR" contextRef="D2012" decimals="-3">0</in-gaap:RevenueFromSaleOfProducts>
            .................
            <in-gaap:ProfitBeforePriorPeriodItemsExceptionalItemsExtraordinaryItemsAndTax id="CYTAG88" unitRef="INR" contextRef="D2013" decimals="-3">22163000</in-gaap:ProfitBeforePriorPeriodItemsExceptionalItemsExtraordinaryItemsAndTax>
            <in-gaap:ProfitBeforePriorPeriodItemsExceptionalItemsExtraordinaryItemsAndTax id="PYTAG88" unitRef="INR" contextRef="D2012" decimals="-3">-14284000</in-gaap:ProfitBeforePriorPeriodItemsExceptionalItemsExtraordinaryItemsAndTax>
            .................
     </xbrli:xbrl>

The "..." contain other irrelevant code. Now the excel file have following cell values: C4 (Element Name) = in-gaap:ProfitBeforePriorPeriodItemsExceptionalItemsExtraordinaryItemsAndTax , D4 = Document location, E4 = Context (say 'D2013'), G4 = Output Value. (should be 22163000)

I want to extract value of element at C4 in G4.

VBA code:

   Sub XbrlExtract()

    Dim LoadFile As String
    LoadFile = Range("D4").Value

    Dim Concept As String
    Concept = Range("C4").Value

    Dim Context As String
    Context = Range("E4").Value

    Dim oDocument As MSXML2.DOMDocument60
    Dim oXbrlNode As MSXML2.IXMLDOMNode
    Dim oNode As MSXML2.IXMLDOMNode

    Set oDocument = New MSXML2.DOMDocument60
    oDocument.DocumentElement
    oDocument.async = False
    oDocument.validateOnParse = False
    oDocument.Load (LoadFile)

    oDocument.setProperty "SelectionNamespaces", "xmlns:xbrli = 'http://www.mca.gov.in/instance' xmlns:in-gaap = 'http://www.xbrl.org/instance'"
    oNode = oDocument.SelectSingleNode("/xbrli:xbrl/" & Concept & "[@contextRef = '" & Context & "']")
    If oNode Is Nothing Then
      MsgBox "No nodes selected"
    Else
     Range("G4").Select
     Debug.Print oNode.Text
    End If
    Set oDocument = Nothing

  End Sub

The code does not detect the element and oNode returns Nothing with msgbox "No nodes selected" and G4 remains blank. I searched a lot of sites but could not resolve it.

score 0 · Answer 1 · answered Aug 12 '14 at 19:41

In your instance document, you have this namespace declaration:

xmlns:xbrli="http://www.xbrl.org/2003/instance"

But you add this namespace to the selectionNamespaces of oDocument

xmlns:xbrli = 'http://www.mca.gov.in/instance'

Is the same prefix but different namespaces.

Tip: By default, MSXML2.DOMDocument2 allows you to use the same namespace prefixes declared in the loaded document, so there is no need to call oDocument.setProperty "SelectionNamespaces" for selectNodes or selectSingleNode to work.

Thanks, problem solved. Though if i remove oDocument.setProperty it throws the error "Reference to undeclared namespace prefix: 'xbrli' ". So I did not remove it. — Yags, Aug 18 '14 at 17:45

in:gaap xbrl Selectsinglenode with multiple attrributes returns nothing in VBA

1 Answers1