It appears that you are drawing in ONE symbol data and then trying to find
the data for another sheet.
Market Cap (intraday): 142.27B
Enterprise Value (20-Mar-07)3: 153.53B
Trailing P/E (ttm, intraday): 15.47
Forward P/E (fye 31-Dec-08) 1: 12.79
PEG Ratio (5 yr expected): 1.26

=======
You are really making this a lot more difficult than it should be. Instead
of creating a new query for each symbol, I would do this by establishing the
query and just using a refresh to get the data (or multiple symbols in a
loop). Then simply use an INDIRECT VLOOKUP formula in the other sheet. Super
quick and a loop could be used so that it would look like my table in my
file available free at the yahoo group xltraders.GetYahooMultipleHistory97a
Or, I can email one.
It also appears that you are not deleting the query name created with each
web fetch. Maybe there is something I don't understand. Send me your file if
you like and I will take a look.
--
Don Guillett
SalesAid Software

"rbnorth" wrote in message
...
Don,

Thanks for your comment

What does this procedure do? After importing a webquery from yahoo.com
containing tables of stock statistics, it runs down the page and pulls
the
statistics into a variable array that can then be used for calculating
various stock evaluation criteria.

You could do the same thing using the 'find' command and I guess not have
to
keep track of rows and columns. Although you would have to know where the
find was because you need to pull the data from the adjacent cell.

Using the find command might also allow you to pull in the whole page
(using
.WebSelectionType = xlAllTables) in the web query. This would make the
whole
procedure a lot more stable. I'll have to try it and see what happens. How
do
you identify the row and column when the find command finds what your
looking
for?

This code could be cleaned up as well by placing the 'searched for' text
in
an array and then looping through the array. To be honest, it was faster
for
me to code using a cut and paste, and once it worked, I moved on to other
things. I'm not a professional programmer. Sloppy but it works

Thanks again for your comments


"Don Guillett" wrote:

I don't think I have ever seen anything quite like this. Quite
cumbersome,
indeed.
What does it do?
What is wrong with FIND (look in vba help)

--
Don Guillett
SalesAid Software

"rbnorth" wrote in message
...
Your on the right track. I start with a counter row=1 to 100 and use
the
Instr() function (that way you dont have to worry about the dates).
When
you
find the data you just say for example

If Instr("Annual EPS Est ",cells(row,1).value) then
AnnEPS=cells(row,2).value

Now having said that, the procedure I have is quite cumbersum and there
are
always spots where exceptions have to be corrected. I've included my
code
for
KeyStatistics below. I AM going to look very closely at Ryguy7272's
procedures

The code:

Sub WebRetreiveStockKeyStatistics(stksym)
'
' GetStockKeyStatistics Macro
' Macro recorded 3/7/2004 by R. Bruce North

' Clear Sheet

Sheets("Web Data").Select
'Cells.Select
'Selection.ClearContents

Const KeyStatTables = "17,22,25,28,31,34,37,47,50,53"

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/ks?s=" & stksym,
Destination:=Range("A1"))
.Name = "ks?s=" & stksym
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
' .WebSelectionType = xlAllTables note: do not use
.WebFormatting = xlWebFormattingNone

.WebTables = KeyStatTables ' rbn note ketstat tables are
currently

.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


Columns("A:A").ColumnWidth = 37
Columns("B:B").ColumnWidth = 18.14
Columns("B:B").HorizontalAlignment = xlCenter

For Row = 1 To 100

If InStr(Cells(Row, 2).Value, "N/A") 0 Then
ElseIf InStr(Cells(Row, 1).Value, "Market Cap") 0 Then
StockStatistics(1).MarketCap = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).MarketCap
ElseIf InStr(Cells(Row, 1).Value, "Enterprise Value (") 0 Then
StockStatistics(1).EnterpriseValue = Text2Num(Cells(Row,
2).Value)
Cells(Row, 3).Value = StockStatistics(1).EnterpriseValue
ElseIf InStr(Cells(Row, 1).Value, "Trailing P/E") 0 Then
StockStatistics(1).TrailingPE = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).TrailingPE
ElseIf InStr(Cells(Row, 1).Value, "Forward P/E") 0 Then
StockStatistics(1).ForwardPE = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ForwardPE
ElseIf InStr(Cells(Row, 1).Value, "PEG Ratio (5 yr expected)") 0
Then
StockStatistics(1).PEGRatio = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).PEGRatio
ElseIf InStr(Cells(Row, 1).Value, "Price/Sales") 0 Then
StockStatistics(1).PriceSales = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).PriceSales
ElseIf InStr(Cells(Row, 1).Value, "Price/Book") 0 Then
StockStatistics(1).PriceBook = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).PriceBook
ElseIf InStr(Cells(Row, 1).Value, "Enterprise Value/Revenue") 0
Then
If Cells(Row, 2) = "Na%0" Then Cells(Row, 2) = 0
If Cells(Row, 2) = "NaN" Then Cells(Row, 2) = 0
StockStatistics(1).EntValueRevenue = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).EntValueRevenue
ElseIf InStr(Cells(Row, 1).Value, "Enterprise Value/EBITDA") 0
Then
StockStatistics(1).EntValueEBITDA = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).EntValueEBITDA

ElseIf InStr(Cells(Row, 1).Value, "Fiscal Year Ends") 0 Then
StockStatistics(1).FiscalYearEnds = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).FiscalYearEnds
ElseIf InStr(Cells(Row, 1).Value, "Most Recent Quarter") 0 Then
StockStatistics(1).MostRecentQuarter = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).MostRecentQuarter

ElseIf InStr(Cells(Row, 1).Value, "From Operations") 0 Then
StockStatistics(1).CashFlowFromOps = Text2Num(Cells(Row,
2).Value)
Cells(Row, 3).Value = StockStatistics(1).CashFlowFromOps
ElseIf InStr(Cells(Row, 1).Value, "Free Cashflow") 0 Then
StockStatistics(1).FreeCashFlow = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).FreeCashFlow



ElseIf InStr(Cells(Row, 1).Value, "Profit Margin") 0 Then
StockStatistics(1).ProfitMargin = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ProfitMargin
ElseIf InStr(Cells(Row, 1).Value, "Operating Margin (ttm):") 0
Then
StockStatistics(1).OperatingMargin = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).OperatingMargin
ElseIf InStr(Cells(Row, 1).Value, "Return on Assets (ttm):") 0
Then
StockStatistics(1).ReturnonAssets = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ReturnonAssets
ElseIf InStr(Cells(Row, 1).Value, "Return on Equity (ttm):") 0
Then
StockStatistics(1).ReturnonEquity = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ReturnonEquity
ElseIf InStr(Cells(Row, 1).Value, "Revenue (ttm):") 0 Then
StockStatistics(1).Revenue = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).Revenue

ElseIf InStr(Cells(Row, 1).Value, "Revenue Per Share") 0 Then
StockStatistics(1).RevenuePerShare = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).RevenuePerShare
ElseIf InStr(Cells(Row, 1).Value, "Operating Margin (ttm):") 0
Then
StockStatistics(1).OperatingMargin = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).OperatingMargin
ElseIf InStr(Cells(Row, 1).Value, "Return on Assets (ttm):") 0
Then
StockStatistics(1).ReturnonAssets = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ReturnonAssets
ElseIf InStr(Cells(Row, 1).Value, "Return on Equity (ttm):") 0
Then
StockStatistics(1).ReturnonEquity = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ReturnonEquity
ElseIf InStr(Cells(Row, 1).Value, "Revenue (ttm):") 0 Then
StockStatistics(1).Revenue = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).Revenue

ElseIf InStr(Cells(Row, 1).Value, "Revenue Per Share (ttm):") 0
Then
StockStatistics(1).RevenuePerShare = Text2Num(Cells(Row,
2).Value)
Cells(Row, 3).Value = StockStatistics(1).RevenuePerShare
ElseIf InStr(Cells(Row, 1).Value, "Revenue Growth (") 0 Then
StockStatistics(1).RevenueGrowth = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).RevenueGrowth
ElseIf InStr(Cells(Row, 1).Value, "Gross Profit (ttm):") 0 Then
StockStatistics(1).GrossProfit = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).GrossProfit
ElseIf InStr(Cells(Row, 1).Value, "EBITDA (ttm):") 0 Then
StockStatistics(1).EBITDA = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).EBITDA
ElseIf InStr(Cells(Row, 1).Value, "Net Income Avl to Common (ttm):")

0
Then
StockStatistics(1).NetIncomeAvltoCommon = Text2Num(Cells(Row,
2).Value)
Cells(Row, 3).Value = StockStatistics(1).NetIncomeAvltoCommon

ElseIf InStr(Cells(Row, 1).Value, "Diluted EPS (ttm):") 0 Then
StockStatistics(1).DilutedEPS = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).DilutedEPS
ElseIf InStr(Cells(Row, 1).Value, "Earnings Growth") 0 Then
StockStatistics(1).EarningsGrowth = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).EarningsGrowth
ElseIf InStr(Cells(Row, 1).Value, "Total Cash (mrq):") 0 Then
StockStatistics(1).TotalCash = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).TotalCash
ElseIf InStr(Cells(Row, 1).Value, "Total Cash Per Share (mrq):") 0
Then
StockStatistics(1).TotalCashPerShare = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).TotalCashPerShare
ElseIf InStr(Cells(Row, 1).Value, "Total Debt (mrq):") 0 Then
StockStatistics(1).TotalDebt = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).TotalDebt

ElseIf InStr(Cells(Row, 1).Value, "Total Debt/Equity (mrq):") 0
Then
StockStatistics(1).TotalDebttoEquity = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).TotalDebttoEquity
ElseIf InStr(Cells(Row, 1).Value, "Current Ratio") 0 Then
StockStatistics(1).CurrentRatio = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).CurrentRatio
ElseIf InStr(Cells(Row, 1).Value, "Book Value Per Share (mrq):") 0
Then
StockStatistics(1).BookValuePerShare = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).BookValuePerShare
ElseIf InStr(Cells(Row, 1).Value, "Beta:") 0 Then
StockStatistics(1).Beta = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).Beta
ElseIf InStr(Cells(Row, 1).Value, "52-Week Change") 0 Then
StockStatistics(1).Wk52Change = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).Wk52Change

ElseIf InStr(Cells(Row, 1).Value, "S&P50052-Week Change") 0 Then
StockStatistics(1).Wk52ChangeRelativetoSP500 = Cells(Row,
2).Value
Cells(Row, 3).Value =
StockStatistics(1).Wk52ChangeRelativetoSP500
ElseIf InStr(Cells(Row, 1).Value, "52-Week High") 0 Then
StockStatistics(1).Wk52High = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).Wk52High
ElseIf InStr(Cells(Row, 1).Value, "52-Week Low") 0 Then
StockStatistics(1).Wk52Low = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).Wk52Low
ElseIf InStr(Cells(Row, 1).Value, "50-Day Moving Average") 0 Then
StockStatistics(1).MovingAverage50day = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).MovingAverage50day
ElseIf InStr(Cells(Row, 1).Value, "200-Day Moving Average") 0 Then
StockStatistics(1).MovingAverage200day = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).MovingAverage200day

ElseIf InStr(Cells(Row, 1).Value, "Average Volume (3 month)") 0
Then
StockStatistics(1).AverageVol3month = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).AverageVol3month
ElseIf InStr(Cells(Row, 1).Value, "Average Volume (10 day)") 0
Then
StockStatistics(1).AverageVol10Day = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).AverageVol10Day
ElseIf InStr(Cells(Row, 1).Value, "Shares Outstanding:") 0 Then
StockStatistics(1).SharesOutstanding = Text2Num(Cells(Row,
2).Value)
Cells(Row, 3).Value = StockStatistics(1).SharesOutstanding
ElseIf InStr(Cells(Row, 1).Value, "% Held by Insiders") 0 Then
StockStatistics(1).PcntHeldInsiders = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).PcntHeldInsiders
ElseIf InStr(Cells(Row, 1).Value, "% Held by Institutions") 0 Then
StockStatistics(1).PcntHeldInstitutions = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).PcntHeldInstitutions

ElseIf InStr(Cells(Row, 1).Value, "Shares Short") 0 Then
StockStatistics(1).SharesShort = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).SharesShort
ElseIf InStr(Cells(Row, 1).Value, "Daily Volume") 0 Then
StockStatistics(1).DailyVolume = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).DailyVolume
ElseIf InStr(Cells(Row, 1).Value, "Short Ratio") 0 Then
StockStatistics(1).ShortRatio = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ShortRatio
ElseIf InStr(Cells(Row, 1).Value, "Short % of Float") 0 Then
StockStatistics(1).ShortPcntofFloat = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ShortPcntofFloat
ElseIf InStr(Cells(Row, 1).Value, "Shares Short (prior month)") 0
Then
StockStatistics(1).SharesShortPrior = Cells(Row, 2).Value
Cells(Row, 3).Value =
Text2Num(StockStatistics(1).SharesShortPrior)

ElseIf InStr(Cells(Row, 1).Value, "Float") 0 Then
StockStatistics(1).Float = Text2Num(Cells(Row, 2).Value)
Cells(Row, 3).Value = StockStatistics(1).Float

ElseIf InStr(Cells(Row, 1).Value, "Forward Annual Dividend Rate")
0
Then
StockStatistics(1).ForAnnualDividendRate = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ForAnnualDividendRate
ElseIf InStr(Cells(Row, 1).Value, "Forward Annual Dividend Yield")
0
Then
StockStatistics(1).ForDividendYield = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ForDividendYield
ElseIf InStr(Cells(Row, 1).Value, "Trailing Annual Dividend Yield")
0
Then
If Cells(Row, 2) = "Na%0" Then Cells(Row, 2) = 0
If Cells(Row, 2) = "NaN%" Then Cells(Row, 2) = 0
StockStatistics(1).TrailDividendYield = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).TrailDividendYield
ElseIf InStr(Cells(Row, 1).Value, "Trailing Annual Dividend Rate")
0
Then
StockStatistics(1).TrailAnnualDividendRate = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).TrailAnnualDividendRate
ElseIf InStr(Cells(Row, 1).Value, "5 Year Average Dividend Yield")
0
Then
StockStatistics(1).AvgDivYield5Yr = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).AvgDivYield5Yr
ElseIf InStr(Cells(Row, 1).Value, "Payout Ratio") 0 Then
StockStatistics(1).PayoutRatio = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).PayoutRatio
ElseIf InStr(Cells(Row, 1).Value, "Ex-Dividend Date") 0 Then
StockStatistics(1).ExDividendDate = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).ExDividendDate
ElseIf InStr(Cells(Row, 1).Value, "Dividend Date") 0 Then
StockStatistics(1).DividendDate = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).DividendDate
ElseIf InStr(Cells(Row, 1).Value, "Last Split Factor") 0 Then
StockStatistics(1).LastSplitFactor = Cells(Row, 2).Value
Cells(Row, 3).Value = StockStatistics(1).LastSplitFactor
ElseIf InStr(Cells(Row, 1).Value, "Last Split Date") 0 Then