Web Query Questions (2)

[braden]

I have been experimenting with Web Queries. Specifically, I am tryin
to pull a bunch of statistics on a list of stocks from Yahoo Finance.
Below is the code i have. It works, HOWEVER, there are 2 questions
have.

1. I get a Opening HTTP://finance.yahoo.com ---- message each tim
the loop runs. Is there any way to deactivate this message?
2. It seems to take about 3-5 second per item to run. Can I improv
the process time? Not a big deal, but if the list get long (and i
can) this could take some time.

Any suggestions on how to improve this would be greatly appreciated.


Sub YahooFinance()

Dim webbk As Workbook
Dim webrng As Range
Dim webFwdPE As Range
Dim webROE As Range
Dim webTrailPE As Range
Dim webPS As Range
Dim webPB As Range
Dim webEVEBITDA As Range
Dim WebBETA As Range
Dim WebDIVYLD As Range
Dim WebShort As Range

Set webbk = Workbooks.Open("http://finance.yahoo.com/q/ks?s="
ActiveCell.Value)
Set webrng = webbk.Worksheets(1).Cells.Find("PEG Ratio (5 y
expected):")
Set webFwdPE = webbk.Worksheets(1).Cells.Find("Forward P/E *")
Set webROE = webbk.Worksheets(1).Cells.Find("Return On Equit
(TTM)")
Set webTrailPE = webbk.Worksheets(1).Cells.Find("Trailing P/E (TTM
Intraday)")
Set webPS = webbk.Worksheets(1).Cells.Find("Price/Sales (TTM)")
Set webPB = webbk.Worksheets(1).Cells.Find("Price/Book (MRQ)")
Set webEVEBITDA = webbk.Worksheets(1).Cells.Find("Enterpris
value/EBITDA (TTM)")
Set WebBETA = webbk.Worksheets(1).Cells.Find("Beta")
Set WebDIVYLD = webbk.Worksheets(1).Cells.Find("Dividend Yiel
(TTM)")
Set WebShort = webbk.Worksheets(1).Cells.Find("SHORT % OF fLOA
*")

Windows("secondrunatyahoo.xls").Activate
ActiveCell(1, 6) = webrng.Offset(0, 1).Value
ActiveCell(1, 5) = webFwdPE.Offset(0, 1).Value
ActiveCell(1, 14) = webROE.Offset(0, 1).Value
ActiveCell(1, 10) = webEVEBITDA.Offset(0, 1).Value
ActiveCell(1, 7) = webPS.Offset(0, 1).Value
ActiveCell(1, 8) = webPB.Offset(0, 1).Value
ActiveCell(1, 9) = WebBETA.Offset(0, 1).Value
ActiveCell(1, 4) = webTrailPE.Offset(0, 1).Value
ActiveCell(1, 15) = WebShort.Offset(0, 1).Value

Windows("KS").Close

End Sub

Sub MoveDownRow()

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1) = Run("Yahoofinance")
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Sub GetQuote()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("A2").Select
Run ("Movedownrow")
Application.ScreenUpdating = True
End Su

 

[Don Guillett]

Goto http://groups.yahoo.com/group/xltraders/
and look for author donalb36 and download my FREE files to do just this.