R
ryguy7272
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
92")
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
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
92")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
4")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
").SelectSelection.Delete Shift:=xlToLeft
' Range("A1").Select
'----------------------------------
Next d
Next c
Sheets("ZZZ - USA Firms").Activate
Range("A1:B1").Select
End Sub