VBA извлекает данные XML из нескольких веб-сайтов

Question

В моем предыдущем вопросе (все, что нужно, по этому вопросу; ссылка приведена здесь для полноты и измерения), я прошу способ получить данные XML в Excel из Интернета. Код, который я получил (любезно предоставлен user2140261) в качестве ответа, находится здесь:

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  

Но у каждой компании есть разные документы экземпляра XML, и каждый период времени компания публикует разные документы экземпляра XML (например, ежеквартально, ежегодно). Таким образом, эти документы могут быть доступны в разных местах в Интернете.

Теперь в предыдущей процедуре мы можем видеть, что нам нужно использовать только выражение

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

... но это когда мы заранее знаем, что нам нужны данные из одного указанного места в Интернете

Что делать, если мы хотим получить некоторые данные для этих 4 различных мест, обозначенных звездочкой (*) на изображении ниже

Как мы могли бы на самом деле ввести наши "координаты" в Excel, скажем, например, в одну из наших пользовательских форм / ячеек, а затем заставить VBA "перемещаться / сканировать" там, просто используя эти координаты так же, как мы перемещаемся там с помощью браузера?

Координаты, которые мы вводим, могут быть:

• Фондовый тикер (например, TSLA для Tesla Motors)
• Тип файлов, например, 10-Q

Вы можете выбрать тип файлов в этих ссылках для BDX и ANN соответственно:

BDX LINK

Энн Линк

Ниже у нас есть 2 веб-сайта для местоположений Instance Document компании BDX и 2 для компании ANN

• Компания BDX

  http://www.sec.gov/Archives/edgar/data/10795/000119312514042815/bdx-20131231.xml http://www.sec.gov/Archives/edgar/data/10795/000119312513318898/bdx-20130630.xml

• Компания ANN

  http://www.sec.gov/Archives/edgar/data/874214/000087421413000036/ann-20131102.xml http://www.sec.gov/Archives/edgar/data/874214/000087421413000027/ann-20130803.xml

Как мы можем извлечь из элемента XML, который существует во всех четырех экземплярах документов, например us-gaap:CommonStockValue просто давая VBA

1. Биржевой тикер
2. Тип документа (10-К, 10-К)

Можно ли это сделать с помощью Microsoft XML Core Services (MSXML) или нам нужна еще какая-то библиотека?

Вы можете видеть, насколько нецелесообразно запускать этот код тысячи раз и каждый раз копировать URL-адрес из веб-браузера в strXMLSite в виде строкового значения....

Answer 1

[Edit1]

В ответ на комментарий:

единственное, что остается для нас, - это понять, как на самом деле меняются URL-адреса, чтобы их можно было предсказывать и манипулировать путем жала? На каком языке кода написан URL?

Короткий ответ: откройте браузер, щелкните правой кнопкой мыши пустое место на интересующей вас веб-странице и выберите View Source из всплывающего меню.

Чтобы повторить пример, приведенный в другом посте VBA href Crawl в исходном коде браузера, сделайте следующее:

Откройте Edgar Online Company Поиск в браузере: https://www.sec.gov/edgar/searchedgar/companysearch.html

Используйте функцию быстрого поиска для поиска тикера CRR, и он дает мне следующий URL: https://www.sec.gov/cgi-bin/browse-edgar?CIK=CRR&Find=Search&owner=exclude&action=getcompany который содержит список общедоступных опилки для Carbo Ceramics, Inc.

Теперь щелкните правой кнопкой мыши на странице, чтобы получить исходный код, и прокрутите вниз до строки 91. Вы увидите этот блок кода:

      <table class="tableFile2" summary="Results">

Это начало таблицы результатов, которая показывает список открытых заявок.

         <tr>
            <th width="7%" scope="col">Filings</th>
            <th width="10%" scope="col">Format</th>
            <th scope="col">Description</th>
            <th width="10%" scope="col">Filing Date</th>
            <th width="15%" scope="col">File/Film Number</th>
         </tr>

Это строка заголовка таблицы с описаниями столбцов.

<tr>
<td nowrap="nowrap">SC 13G</td>
<td nowrap="nowrap"><a href="/Archives/edgar/data/1009672/000108975514000003/0001089755-14-000003-index.htm" id="documentsbutton">&nbsp;Documents</a></td>
<td class="small" >Statement of acquisition of beneficial ownership by individuals<br />Acc-no: 0001089755-14-000003&nbsp;(34 Act)&nbsp; Size: 8 KB            </td>
            <td>2014-02-14</td>
            <td nowrap="nowrap"><a href="/cgi-bin/browse-edgar?action=getcompany&amp;filenum=005-48851&amp;owner=exclude&amp;count=40">005-48851</a><br>14615563         </td>
         </tr>

И это первая строка фактических данных в таблице для подачи SC 13G, Statement of acquisition of beneficial ownership by individuals Acc-no: 0001089755-14-000003 (34 Act) Size: 8 KB, представлены на 2014-02-14,

Итак, теперь вы хотите просмотреть все URL-адреса документов на этой странице, и поэтому вы спрашиваете, на каком языке эти URL-адреса? (Сканировать страницу, другими словами?)

[начать оригинальный ответ]

Как мы могли бы на самом деле ввести наши "координаты" в Excel, скажем, например, в одну из наших пользовательских форм / ячеек, а затем заставить VBA "перемещаться / сканировать" там, просто используя эти координаты так же, как мы перемещаемся там с помощью браузера?

Я погуглил "получить результаты Google как XML", исследуя другой вопрос. Один интересный хит, который вернулся, был этой ссылкой: http://nielsbosma.se/projects/seotools/functions/

Я не представляю достоинств этого инструмента, но, похоже, он обладает нужной вам функциональностью.

Теперь в предыдущей процедуре мы можем видеть, что нам нужно использовать только выражение strXMLSite = " http://www.sec.gov/Archives/edgar/data/10795/000119312513456802/bdx-20130930.xml"... но это когда мы заранее знаем, что нам нужны данные из одного указанного места в Интернете

Да, поэтому, если у вас есть какая-то функция веб-сканирования, которая возвращает список ссылок на документы XML, вам сначала нужно поместить их туда, где их увидит пользователь. Я бы предпочел диапазон на рабочем листе, но вы также можете загрузить список или поле со списком в форме. В любом случае, вы бы изменили Sub GetNode() принять входной параметр на основе выбора пользователя:

Sub GetNode(strUrl as String)
...
strXMLSite = strUrl
...
Worksheets("Sheet1").Range("A1").Value = objXMLNodeDIIRSP.Text
End Sub  

Или, может быть, лучше сделать его функцией, которая возвращает xml в виде текста, который вы можете использовать, как вам бы хотелось:

Function GetNode(strUrl as String) as String
...
strXMLSite = strUrl
...
'return result
GetNode = objXMLNodeDIIRSP.Text
End Function  

Интересный вопрос в целом, и я был рад дать вам отзыв о коде, который вы разместили. На другие ваши вопросы, вероятно, можно будет ответить, выполнив небольшой поиск в Google.