Web Tables (Querying, Using a Looping Macro)

[Guest]

Between recording a macro and creating a couple of loops (thanks to Merjet
from this Discussion Group), I came up with a way of querying the web site
finance.yahoo.com. I set up to loop to import historical stock prices into
Excel as well as certain information on the ‘Summary’ screen. The only
problem is that the information on the Summary screen is not consistent.
Deep within my code, I tell Excel to look for the following:

..WebTables = "48,53"

Usually this yields the desired results, but sometimes, I get the wrong
information because the wrong WebTables are being queried for certain stocks.
When I get these occasional errors, I can turn on the macro recorder and
identify the WebTables that I really need (and they could be something like
..WebTables = "46,51" or .WebTables = "47,52") but this defeats the purpose of
using a looping macro. Does anyone know why this occurs? More importantly,
does anyone know who to resolve this issue? The Yahoo finance people are
getting the right data into the right statistical categories, but they are
not reporting these in the same “WebTablesâ€.

Regards,
RyGuy--

 

[gimme_this_gimme_that]

Use the symbol rss page, not the page for the symbol

Here is the page for Apple Inc.

http://finance.yahoo.com/rss/headline?s=AAPL

Then parse the XML.

 

[Guest]

This may be it, or at least a major step in the right direction. However, I
don't fully understand what you mean? Can you elaborate? Can you give me an
example using the code 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("D392")


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("D34")

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").Select
Selection.Delete Shift:=xlToLeft

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

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

End Sub

 

[Guest]

I tested a few ideas this morning...no success yet. Can anyone she some
light on the topic of ‘WebTables’ and or the topics of 'rss page' and
'parsing XML'? I already copied/pasted my code into this DG.
Regards,
Ryan---