RE: Import from finance.yahoo.com

Yes, yes, Yes, search the rows for the text that defines the data... I
thought of that too. How do you do it?

I have a macro that creates spreadsheets and then imports the data to the
relevant ***. I need to loop through each *** and find certain strings,
such as "Forward P/E (1 yr):", "PEG Ratio (5 yr expected):", "Annual EPS Est
(Aug-07):" (the (Aug-07) part is certain to create obvious problems unless I
can set this up to search for "ESP" within the string), etc. Then I have to
find the value to the right of this string (perhaps offset (0 ,1)).
Everything is summarized on my “Summary ***”. I can’t tell the Summary
Sheet to reference other sheets because they haven’t been created yet, but
after they are created I want to identify the Forward P/E, etc. on each ***
and copy/past each value to my Summary ***… Any ideas…

Regards,
RyGuy




"rbnorth" wrote:

I do the same thing to gather stock data and have had the same problem. I
solved it by setting the webtables to read .webtables=46,47,48,51,52,53 In
otherwords, I import a range of tables. This does require, however, that when
you go in to pick out data that your data collection is row independent,
because sometimes a value appears on row 15 and next time it may be on row
23. You can test and see how stable it is for you. In my case I search the
rows for the the text that defines the data (which generally imports to
column A) and then pick off the value from column B next to it. Hope this
helps.

"ryguy7272" wrote:

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 Work***

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 Active***.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 Active***.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!!
.

Loading