Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
MS query - 2 questions | Excel Discussion (Misc queries) | |||
External Query questions | Excel Programming | |||
Query questions (I think) | Excel Worksheet Functions | |||
Web Query & Array Questions! | Excel Programming |