Looping through HTML table to populate Excel

[Excel Monkey]

I know you can loop through HTML elements to populate Excel from a table in a
website. I am assuming that the HTML can ID the values using some form of
class. When looking at the HTML code below from www.morningstar.com (i.e. a
microsoft stock quote: "MSFT") I want to be able to pick out all the values
in a desired pricing table. For example:

<td class=Text><div id="QuoteMktCap">179,144</div></td> <td
class=Text><div id="QuoteYield">2.55</div></td>
<td class=Text><div id="QuoteAvgVolume">70,758,231</div></td>

I know that I can loop through the IHTMLElements in an
IHTMLElementCollection and test to see if the IHTMLElement.className = "some
class". However I cannot tell from the HTML code what this class is. Any
ideas?

Below is a snapshot of the HTML with the full detail of the desired table.
If this does not produce well go to:

http://quote.morningstar.com/Quote/Quote.aspx?ticker=MSFT&TimeFrame=D1&byrefresh=yes

'**********************************************
</table>
<table style="table-layout:fixed" width=273 border=0 cellspacing=0
cellpadding=0>
<col width=84><col width=101><col width=88>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=5></td>
</tr>
<tr>
<td class=TextHeader>Last Price</td>
<td class=TextHeader>Day Change</td>
<td class=TextHeader>Volume</td>
</tr>
<tr>
<td class=TextBold><div id="QuoteLastPrice">20.15</div></td>
<td class=Text><div id="QuoteDayChange"><img
src='http://im.morningstar.com/im/arrowdown1.gif' width=10 height=7><img
src='http://im.morningstar.com/im/dot_clear.gif' width=2 height=7><span
class=ColorDown>0.25 (-1.23%)</span></div></td>
<td class=Text><div id="QuoteVolume">26,755,047</div></td>
</tr>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=10></td>
</tr>
<tr>
<td colspan=3 class=BackSep><img
src="http://im.morningstar.com/im/dot_clear.gif" height=1></td>
</tr>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=5></td>
</tr>
<tr>
<td class=TextHeader>Open Price</td>
<td class=TextHeader>Day Range</td>
<td class=TextHeader>52-Wk Range</td>
</tr>
<tr>
<td class=Text><div id="QuoteOpenPrice">20.25</div></td>
<td class=Text><div id="QuoteDayRange">20.27 - 19.86</div></td>
<td class=Text><div id="QuoteYearRange">30.53 - 14.87</div></td>
</tr>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=10></td>
</tr>
<tr>
<td colspan=3 class=BackSep><img
src="http://im.morningstar.com/im/dot_clear.gif" height=1></td>
</tr>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=5></td>
</tr>
<tr>
<td class=TextHeader>Mkt Cap<span
class=TextHeader2> (mil)</span></td>
<td class=TextHeader>Yield</td>
<td class=TextHeader>Avg Volume</td>
</tr>
<tr>
<td class=Text><div id="QuoteMktCap">179,144</div></td>
<td class=Text><div id="QuoteYield">2.55</div></td>
<td class=Text><div id="QuoteAvgVolume">70,758,231</div></td>
</tr>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=10></td>
</tr>
<tr>
<td colspan=3 class=BackSep><img
src="http://im.morningstar.com/im/dot_clear.gif" height=1></td>
</tr>
<tr>
<td colspan=3><img
src="http://im.morningstar.com/im/dot_clear.gif" height=5></td>
</tr>
<tr>
<td class=TextHeader>P/E</td>
<td class=TextHeader>P/S</td>
<td class=TextHeader>P/CF</td>
</tr>
<tr>
<td class=Text><div id="QuotePriceEarn">10.80</div></td>
<td class=Text><div id="QuotePriceSale">3.00</div></td>
<td class=Text><div id="QuotePriceCash">9.17</div></td>
</tr>
</table>

Thanks

EM

 

[Don Guillett]

Have you considered an external query. Right click on the table
desired>import to excel>>>

 

[Excel Monkey]

Yes I could do that. But I actually want to understand how to do this in VBA
too.

Thanks

EM

 

[Don Guillett]

That's my point. Create the external query and set up a macro to refresh it
with whatever parameters are desired. And, use the macro to extract the data
wherever desired. I do this often for my clients.

 

[Excel Monkey]

Nice! So if I understand what you have done is you have identified the
start/stop posiiton of where the relevant tags are in the HTML for the
desired table. You do this my pulling the entire string into a variable and
then you split it up in an array using the Split function using the "div id="
as your delimiter. Then you define start/stop positions within each element
of the array.

Quick question. What does the "div id=" denote?

Thanks

EM

 

[oitbso]

Nice!  So if I understand what you have done is you have identified the
start/stop posiiton of where the relevant tags are in the HTML for the
desired table.  You do this my pulling the entire string into a variable and
then you split it up in an array using the Split function using the "div id="
as your delimiter.  Then you define start/stop positions within each element
of the array.

Yes, that is correct.

Quick question.  What does the "div id=" denote?

I think it represents a division or section id within the source code,
maybe someone else knows more...Ron

 

[oitbso]

Nice!  So if I understand what you have done is you have identified the
start/stop posiiton of where the relevant tags are in the HTML for the
desired table.  You do this my pulling the entire string into a variable and
then you split it up in an array using the Split function using the "div id="
as your delimiter.  Then you define start/stop positions within each element
of the array.

Yes, that's correct.

Quick question.  What does the "div id=" denote?

I think it identifies the id for a section or division of source
code...Ron