It appears that you have a very large workbook with a lot of overhead and
excess of queries and names. Send it to me if you like and I will have a
look. Or, I can send you (ONLY if you ask OFF list) my
GetYahooMultipleHistory97a
which will get the history for as many as you like. There is also a nice
graph that will graph each upon a double click on the symbol.
--
Don Guillett
SalesAid Software

"ryguy7272" wrote in message
...
I submitted a post on this DG a few days ago, and got some help at that
time,
but now I've hit a wall again... I am looking up data for about 100 stock
symbols on finance.yahoo.com. I am importing information for these stocks
and, approximately 90% of the time the data is fine, but the rest of the
time, I seem to access the wrong data. The problem is with something
called
WebTables. A recorded macro will yield something like .WebTables =
"48,53"
and this usually gives me what I want, but for certain stock symbols, the
Web.Tables are slightly different, like .WebTables = "46,51" or .WebTables
=
"47,52". The WebTables data should correspond to data from "KEY
STATISTICS"
and data from "ANALYST". (see: http://finance.yahoo.com/q?s=pfe). The
Yahoo people always report the relevant information in "KEY STATISTICS"
and
"ANALYST", but the WebTables are sometimes numbered differently (i.e.,
"48,53", or "47,52", or "46,51"). Do I need to use XML to reference the
correct "KEY STATISTICS" and "ANALYST" information or can VBA do it for
me?
If so, how do I do this (I don't know anything about XML). Any assistance
would be Gretel appreciated!

My code is listed below:

Sub HistData()

Application.ScreenUpdating = False

Dim str1 As String
Dim str2 As String
Dim c As Range
Dim d As Range

Dim bFound As Boolean
Dim ws As Worksheet

For Each c In Sheets("ZZZ - USA Firms").Range("D3:D92")


bFound = False
For Each ws In Worksheets
If ws.Name = c.Value Then
bFound = True
Exit For
End If
Next ws

If bFound = False Then
Worksheets.Add.Name = c.Value
End If

'----------------------------------------------------------

Sheets(c.Value).Select
Cells.Select
Range("A1:IV50000").ClearContents

str1 = "URL;http://finance.yahoo.com/q/hp?s=" & _
c.Value & "&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"
str2 = "hp?s=" & c.Value & "a=00&b=1&c=2007&d=02&e=14&f=2007&g=d"

With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("A1"))

.Name = str2

.Name = "hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d "

.FieldNames = True
.RowNumbers = False
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 11.14

Cells.Select
With Selection
.MergeCells = False
End With

'----------------------------------

Columns("C:C").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft

For Each d In Sheets("ZZZ - USA Firms").Range("D3:D4")

str1 = "URL;http://finance.yahoo.com/q?s=" & _
c.Value
str2 = "q?s=" & c.Value

With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("I1"))

.Name = str2

.Name = "hp?s=KFT&a=00&b=1&c=2007&d=02&e=14&f=2007&g=d "

.FieldNames = True
.RowNumbers = False
.WebTables = "48,53"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 11.14

Cells.Select
With Selection
.MergeCells = False
End With

Range("H:D").Select
Selection.Delete Shift:=xlToLeft

' Range("A1").Select
'----------------------------------
Next d
Next c

Sheets("ZZZ - USA Firms").Activate
Range("A1:B1").Select

End Sub

--
RyGuy
PS, thanks for the solution to the looping problem Merjet!!