I am having trouble with the external data function in Excel. Going
to morningstar.com and typing in tickers one by one and writing down
the market cap type can be very tedious. Instead, I would like to
create a table in Excel to pull this data for me. Rather than pull an
entire stock quote based on the ticker entered into the dialog box
(this example is provided in Excel), I'd like to type a list of
multiple tickers into a column in Excel and have a web query return
the Morningstar style box type into the cell to the right of my list
of tickers. Is it possible to only pull this one phrase "Large Value"
rather than the entire table from the following site?

http://quicktake.morningstar.com/Sto...tab=snapsho t

How could I create such a task?

Thank you ahead of time for your help.

Hi JSD,

Here's some code that should pull that string from a URL:

Public Function sGetValue(rsURL As String) As String
Dim ie As Object
Dim lCharPos As Long
Dim lStartCharPos As Long
Dim lEndCharPos As Long
Dim sHTML As String

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate "http://quicktake.morningstar.com/Stock/Snapshot.asp?"
& _
"Country=USA&Symbol=" & rsURL & "&stocktab=snapshot"
Do While .Busy And .ReadyState < 4
DoEvents
Loop

'/ got page, check it for "Morningstar Style Box"
sHTML = .Document.body.innerhtml

.Quit
End With

Set ie = Nothing

lCharPos = InStr(1, sHTML, "Morningstar Style Box", vbTextCompare)

If lCharPos Then
'/ find data
lCharPos = InStr(lCharPos, sHTML, "msData", vbTextCompare)

If lCharPos Then
lCharPos = InStr(lCharPos, sHTML, "", vbTextCompare)
If lCharPos Then
lStartCharPos = lCharPos + 1
lCharPos = InStr(lStartCharPos, sHTML, "<",
vbTextCompare)
If lCharPos Then
lEndCharPos = lCharPos - 1

sGetValue = Mid$(sHTML, lStartCharPos, lEndCharPos -
_
lStartCharPos + 1)
End If
End If
End If
End If
End Function

You could either use this directly as a worksheet function (it would be very
slow, so I wouldn't recommend it unless you're the only one using it) or you
can iterate through the tickers, calling this function each time. It would
be more efficient to create the instance of IE once, navigate to each page
(grabbing the value each time), then close IE. So maybe you could take in
an array of Strings and return an array of values.

This code isn't very efficient because it works with large strings. You
could probably cut the string down after the first InStr and use only the
chunk.

Alternatively, you could use Excel's built-in Web Query feature, which would
allow you to specify the URL and the table you want. But I don't know if
you can narrow it down to a single cell (and whether it would be any
faster/easier to automate).

--
Regards,

Jake Marx
MS MVP - Excel


JSD wrote:
I am having trouble with the external data function in Excel. Going
to morningstar.com and typing in tickers one by one and writing down
the market cap type can be very tedious. Instead, I would like to
create a table in Excel to pull this data for me. Rather than pull an
entire stock quote based on the ticker entered into the dialog box
(this example is provided in Excel), I'd like to type a list of
multiple tickers into a column in Excel and have a web query return
the Morningstar style box type into the cell to the right of my list
of tickers. Is it possible to only pull this one phrase "Large Value"
rather than the entire table from the following site?


http://quicktake.morningstar.com/Sto...tab=snapsho t

How could I create such a task?

Thank you ahead of time for your help.