Web Query Questions (2)

[braden]

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

[Don Guillett]

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
[email protected]
"braden" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>