Databases
StackOverflow2013
Postsdbo
POExtracting XML attribute using VBA
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtracting XML attribute using VBA
    primarykey
    data
    text
    <p>I'm not a developer and have very limited XML knowledge but for what I've learned the past 3-4 days researching on the web. So apologies in advance for the basic level of this question. I'm trying to wrap up this one time task.</p> <p>I have some VBA Excel knowledge and currently I'm trying to use VBA to extract the SIC code attribute from a given company's page on the SEC filing website. As an example, this is the site for Walmart</p> <p><a href="http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&amp;CIK=0000104169&amp;owner=exclude&amp;count=40&amp;hidefilings=0" rel="nofollow noreferrer">http://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&amp;CIK=0000104169&amp;owner=exclude&amp;count=40&amp;hidefilings=0</a></p> <p>In the blue bar at the top you can see 'SIC: 5331' it's the 5331 I'm trying to return to a VBA variable so I can populate a spreadsheet. When I right click in IE and clich View Source the part of the page that is relevant reads in XML as:</p> <pre><code>&lt;div id="contentDiv"&gt; &lt;!-- START FILER DIV --&gt; &lt;div style="margin: 15px 0 10px 0; padding: 3px; overflow: hidden; background-color: #BCD6F8;"&gt; &lt;div class="mailer"&gt;Mailing Address &lt;span class="mailerAddress"&gt;702 SOUTHWEST 8TH STREET&lt;/span&gt; &lt;span class="mailerAddress"&gt; BENTONVILLE AR 72716 &lt;/span&gt; &lt;/div&gt; &lt;div class="mailer"&gt;Business Address &lt;span class="mailerAddress"&gt;702 SOUTHWEST 8TH ST&lt;/span&gt; &lt;span class="mailerAddress"&gt;BENTONVILLE AR 72716 &lt;/span&gt; &lt;span class="mailerAddress"&gt;5012734000&lt;/span&gt; &lt;/div&gt; &lt;div class="companyInfo"&gt; &lt;span class="companyName"&gt;WAL MART STORES INC &lt;acronym title="Central Index Key"&gt;CIK&lt;/acronym&gt;#: &lt;a href="/cgi-bin/browse-edgar?action=getcompany&amp;amp;CIK=0000104169&amp;amp;owner=exclude&amp;amp;count=40"&gt;0000104169 (see all company filings)&lt;/a&gt;&lt;/span&gt; &lt;p class="identInfo"&gt;&lt;acronym title="Standard Industrial Code"&gt;SIC&lt;/acronym&gt;: &lt;a href="/cgi-bin/browse-edgar?action=getcompany&amp;amp;SIC=5331&amp;amp;owner=exclude&amp;amp;count=40"&gt;5331&lt;/a&gt; - RETAIL-VARIETY STORES&lt;br /&gt;State location: &lt;a href="/cgi-bin/browse-edgar?action=getcompany&amp;amp;State=AR&amp;amp;owner=exclude&amp;amp;count=40"&gt;AR&lt;/a&gt; | State of Inc.: &lt;strong&gt;DE&lt;/strong&gt; | Fiscal Year End: 0131&lt;br /&gt;(Assistant Director Office: 2)&lt;br /&gt;Get &lt;a href="/cgi-bin/own-disp?action=getissuer&amp;amp;CIK=0000104169"&gt;&lt;b&gt;insider transactions&lt;/b&gt;&lt;/a&gt; for this &lt;b&gt; issuer&lt;/b&gt;. &lt;br /&gt;Get &lt;a href="/cgi-bin/own-disp?action=getowner&amp;amp;CIK=0000104169"&gt;&lt;b&gt;insider transactions&lt;/b&gt;&lt;/a&gt; for this &lt;b&gt;reporting owner&lt;/b&gt;. &lt;/p&gt; &lt;/div&gt; &lt;/div&gt; &lt;/div&gt; </code></pre> <p>In trying to understand how VBA might be used to extract the SIC, I found the following post on your site:</p> <p><a href="https://stackoverflow.com/questions/5188999/query-and-parse-xml-attribute-value-into-xls-using-vba?answertab=active#tab-top">Query and parse xml attribute value into XLS using VBA</a></p> <p>I tried to apply barrowc's answer by copy/paste into an Excel module and inserted the path to the Wal Mart filings however when I step through I get the Debug.Print "*****" but I'm not getting anything for n.Text. </p> <pre class="lang-vb prettyprint-override"><code>Sub test4() Dim d As MSXML2.DOMDocument60 Dim i As IXMLDOMNodeList Dim n As IXMLDOMNode Set d = New MSXML2.DOMDocument60 d.async = False d.Load ("http://www.sec.gov/cgi-bin/browse-edgar?company=&amp;match=&amp;CIK=886475&amp;filenum=&amp;State=&amp;Country=&amp;SIC=&amp;owner=exclude&amp;Find=Find+Companies&amp;action=getcompany") Debug.Print "*****" Set i = d.SelectNodes("//div[@id='contentDiv']") For Each n In i Debug.Print n.Text Next n Debug.Print "*****" Set d = Nothing End Sub </code></pre> <p>I've tried various strings in <code>d.SelectNodes()</code>, but I don't know enough about this topic to understand where I'm going wrong. So either a comment on my syntax or a pointer to a resource would be enormously helpful.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmchac
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExtracting XML attribute using VBA
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POExtracting XML attribute using VBA
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COI would say do some research into [xpath selectors](https://developer.mozilla.org/en-US/docs/XPath) to find out how to properly select nodes. Then at least you will have a better understanding of what you may need to select. I would personally select by the `acronym` node and then select its second proceeding sibling. Although you may have trouble because that is HTML and does not appear to be valid xml. You may have better luck parsing it as a string instead of XML. Especially if the structure won't change.
      singulars
      1. POExtracting XML attribute using VBA
      1. USPow-Ian
    2. CODo you only need the SIC from that page, or are you using that as an example of the type of data you need to extract generally?
      singulars
      1. POExtracting XML attribute using VBA
      1. USmwolfe02
    3. COI need just the SIC code from that page and then will repeat for other corps. I've created a list in Excel of firms and have also pulled in their CIK number from the SEC index files. My intention is to create a loop that takes that web address in the sub and iteratively substitutes the CIK number, calls the web page, pulls the SIC from the resulting webpage then inserts the next CIK etc.
      singulars
      1. POExtracting XML attribute using VBA
      1. USmchac
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload