Web Query Questions (2)
 

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

--
brade
-----------------------------------------------------------------------
braden's Profile: http://www.excelforum.com/member.php...fo&userid=2522
View this thread: http://www.excelforum.com/showthread.php?threadid=38713

Web Query Questions (2)
 

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


--
Don Guillett
SalesAid Software

"braden" wrote in
message ...

I have been experimenting with Web Queries. Specifically, I am trying
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 I
have.

1. I get a Opening HTTP://finance.yahoo.com ---- message each time
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 improve
the process time? Not a big deal, but if the list get long (and it
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 yr
expected):")
Set webFwdPE = webbk.Worksheets(1).Cells.Find("Forward P/E *")
Set webROE = webbk.Worksheets(1).Cells.Find("Return On Equity
(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("Enterprise
value/EBITDA (TTM)")
Set WebBETA = webbk.Worksheets(1).Cells.Find("Beta")
Set WebDIVYLD = webbk.Worksheets(1).Cells.Find("Dividend Yield
(TTM)")
Set WebShort = webbk.Worksheets(1).Cells.Find("SHORT % OF fLOAT
*")

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 Sub


--
braden
------------------------------------------------------------------------
braden's Profile:
http://www.excelforum.com/member.php...o&userid=25226
View this thread: http://www.excelforum.com/showthread...hreadid=387139