WebQuery Extracting from a table

[robgrant]

Hey guys,

I want to create a spreadsheet that would used web query data from yahoo finance. I have three problems right now.

1) When I run a query, I'm presented with an entire table as shown below> I only need the data highlighted in yellow; possibility also the data highlighted in purple as a total to appear in one cell. Is there a way to extract this data?

Excel Workbook
	E	F	G	H	I
4
5	Earnings Est	Current Qtr.	Next Qtr.	Current Year	Next Year
6		40,461.00	11-Jan	11-Jul	12-Jul
7	Avg. Estimate	0.40	0.42	1.73	1.99
8	No. of Analysts	36.00	35	42	27
9	Low Estimate	0.38	0.4	1.65	1.8
10	High Estimate	0.43	0.45	1.86	2.17
11	Year Ago EPS	0.36	0.4	1.61	1.73
12
Sheet1

Excel 2007


2) The data should be dynamic, and I also need to have a list of stock symbols in the left hand column. The current stock symbol shown in the example is CSCO. How do I get this to change for each line?

3) I'm also going to need to extract data from another table on a different page to make my calculation. Does this present any problems?

 

[SydneyGeek]

Here's a starting point, from adapting some recorded code.

New worksheet, with stock codes in column H.
Place this code in a new module (Alt+F11, Insert>Module, paste)

Run by returning to the spreadsheet, pressing Alt+F8, and double-clicking the macro name

Sub GetQuote()
    Dim c As Range
    
    For Each c In Range("H:H").SpecialCells(xlCellTypeConstants, xlTextValues)
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://finance.yahoo.com/q/ae?s=" & c.Value & "+Analyst+Estimates", Destination:= _
            Range("$A$1"))
            .Name = "ae?s=CSCO+Analyst+Estimates_1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "9"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        c.Offset(0, 1).Resize(1, 4).Value = Range("B3:E3").Value
    Next c
End Sub

Denis