Filter specific elements per Instance

Question

i managed to pull Data from EDGAR DB. However the code i have pulls all data from all Instance documents. No matter how much I try to find a way to only pick selected elements documents from selected Instance elements I cannot find a way. The code is below:

Sub READSITE()

    Dim IE As InternetExplorer
    Dim els, el, colDocLinks As New Collection
    Dim lnk, res
    Dim Ticker As String
    Dim colXMLPaths As New Collection
    Dim XMLElement As String
    Dim fillingType As String

    Set IE = New InternetExplorer

    IE.Visible = False

    Ticker = Worksheets("Sheet1").Range("A1").Value

    fillingType = Worksheets("Sheet3").Range("L1").Value

    LoadPage IE, "https://www.sec.gov/cgi-bin/browse-edgar?" & _
                  "action=getcompany&CIK=" & Ticker & "&type=" & fillingType & _
                  "&dateb=&owner=exclude&count=20"

    Set els = IE.Document.getelementsbytagname("a")
    For Each el In els
        If Trim(el.innertext) = "Documents" Then
            colDocLinks.Add el.href
        End If
    Next el

    For Each lnk In colDocLinks
        LoadPage IE, CStr(lnk)
        For Each el In IE.Document.getelementsbytagname("a")
            If el.href Like "*[0-9].xml" Then
                Debug.Print el.innertext, el.href
                colXMLPaths.Add el.href
            End If
        Next el
    Next lnk

    XMLElement = Range("C1").Value

    'For each link, open the URL and display the Debt Instrument Insterest Rate
    For Each lnk In colXMLPaths
        res = GetData(CStr(lnk), XMLElement)
        With Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .NumberFormat = "@"
            .Value = Ticker
            .Offset(0, 1).Value = lnk
            .Offset(0, 2).Value = res
        End With
    Next lnk

End Sub

Function GetData(sURL As String, sXMLElement As String)
    Dim strXMLSite As String
    Dim objXMLHTTP As New MSXML2.XMLHTTP
    Dim objXMLDoc As New MSXML2.DOMDocument
    Dim objXMLNodexbrl As MSXML2.IXMLDOMNode
    Dim objXMLNodeElement As MSXML2.IXMLDOMNode
    Dim objXMLNodeStkhldEq As MSXML2.IXMLDOMNode

    '''''''''''''''''''''
    Dim userBeanList As MSXML2.IXMLDOMNodeList
    Dim userbean As MSXML2.IXMLDOMNode
    Dim beanChild As MSXML2.IXMLDOMNode
    Dim i As Long
    '''''''''''''''''''''

    ' In Sheet 3 determine if Row 2 is free of data and set start row to 2. Else get the last free row in column b
    Sheets("Sheet3").Select
    Sheets("Sheet3").Range("B2").Select
    If ActiveCell.Value = "" Then
        i = 2
    Else
        Sheets("Sheet3").Range("B1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, -1).Range("A1").Select
        i = ActiveCell.Row
    End If


    'Get tge XML from SEc

    GetData = "?" 'No data from XML
    objXMLHTTP.Open "GET", sURL, False  '<<EDIT: GET the site
    objXMLHTTP.send
    objXMLDoc.LoadXML objXMLHTTP.responseText
    objXMLDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.xbrl.org/2003/instance'"

    Set objXMLNodexbrl = objXMLDoc.SelectSingleNode("r:xbrl")

    'Get a single element value from the returned XML
    Set objXMLNodeElement = objXMLNodexbrl.SelectSingleNode(sXMLElement)

    If Not objXMLNodeElement Is Nothing Then
        GetData = objXMLNodeElement.Text
    End If

    'Print all nodes name and value for each Element in the XML
    Set userBeanList = objXMLDoc.SelectNodes("r:xbrl")
    For Each userbean In userBeanList
    Worksheets("Sheet3").Cells(i, 1).Value = sURL
        For Each beanChild In userbean.ChildNodes
                With Worksheets("Sheet3")
                    .Cells(i, 2).Value = beanChild.nodeName
                    .Cells(i, 3).Value = beanChild.Text
                End With
                i = i + 1
        Next beanChild
    Next userbean

End Function

Sub LoadPage(IE As Object, url As String)
    IE.Navigate url
    Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
End Sub

How can instead of pulling all the data per Instance document just select say 15 elements per instance document?

Answer 1

If you are looking to only get a maximum of 15 elements per instance add a condition to your loop / add an if statement that will kick out of the loop after 15 iterations. like you use i = i + 1 to control the line you are printing on, use a new variable (x, y it doesn't matter) to count the number of times you run through the beanChild loop. If you need actual code let me know, but if you have done all this coding yourself you seem proficient enough to figure it out :)

EDIT:

Okay so here is an example of what I was getting at. If you want to max out at 15 elements per source you could do this:

For Each userbean In userBeanList
Worksheets("Sheet3").Cells(i, 1).Value = sURL

x = 0  

For Each beanChild In userbean.ChildNodes
     If x < 15 then
           With Worksheets("Sheet3")
                .Cells(i, 2).Value = beanChild.nodeName
                .Cells(i, 3).Value = beanChild.Text
            End With
            i = i + 1
            x = x + 1
    Next beanChild
    Else
        Exit For
    End If
Next userbean