PDA

View Full Version : Sleeper: Picking up the speed?



bradedi
07-28-2005, 12:18 PM
Hello.

I appreciate the help I received yesterday regarding automatically Clicking "Yes". After some additional testing, discussing with colleagues and a reference to a website, I started playing with the following code:


'run this sub off a button to loop down a list of tickers
Sub GetReutersValue()
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1) = Run("ReutersTest")
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Private Const msBETA As String = "Beta"
Private Const msROE As String = "Return On Equity (TTM)"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

' This sub is the meat of the code
Sub ReutersTest()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://www.investor.reuters.com/MG.aspx?ticker=" & _
ActiveCell & "&target=/stocks/financialinfo/ratios/valuation"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop
s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing
' Get Statistics that I need for example
'/ get Beta
nStart = InStr(1, s, msBETA, vbTextCompare)
If nStart Then
nStart = nStart + Len(msBETA)
nEnd = InStr(nStart, s, vbCrLf)
ActiveCell(1, 2) = Val(Mid$(s, nStart, nEnd - nStart))
End If
End Sub

'run this sub off a button to loop down a list of tickers
Sub GetReutersValue()
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1) = Run("ReutersTest")
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This seems to avoid the login problem, but it seems to take for ever to cycle down a list of 15 tickers. This code assumes you start on Sheet1, A1, and have a list of stock running down column A.

It seems to me that I am overdoing something in the code. Any thoughts?

Any help would be greatly appreciated.

OBP
07-28-2005, 03:16 PM
Can you copy the reuters data in to Excel and interogate there?

Bob Phillips
07-28-2005, 05:06 PM
Hello.

I appreciate the help I received yesterday regarding automatically Clicking "Yes". After some additional testing, discussing with colleagues and a reference to a website, I started playing with the following code:


'run this sub off a button to loop down a list of tickers
Sub GetReutersValue()
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1) = Run("ReutersTest")
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Private Const msBETA As String = "Beta"
Private Const msROE As String = "Return On Equity (TTM)"
Private Const msPE_RATIO As String = "P/E Ratio"
Private Const msDIVIDEND_RATE As String = "Indicated Dividend Rate"

' This sub is the meat of the code
Sub ReutersTest()
Dim ie As Object
Dim s As String
Dim nStart As Integer
Dim nEnd As Integer
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://www.investor.reuters.com/MG.aspx?ticker=" & _
ActiveCell & "&target=/stocks/financialinfo/ratios/valuation"
Do Until Not .Busy And .ReadyState = 4
DoEvents
Loop
s = ie.Document.body.innertext
.Quit
End With
Set ie = Nothing
' Get Statistics that I need for example
'/ get Beta
nStart = InStr(1, s, msBETA, vbTextCompare)
If nStart Then
nStart = nStart + Len(msBETA)
nEnd = InStr(nStart, s, vbCrLf)
ActiveCell(1, 2) = Val(Mid$(s, nStart, nEnd - nStart))
End If
End Sub

'run this sub off a button to loop down a list of tickers
Sub GetReutersValue()
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 1) = Run("ReutersTest")
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This seems to avoid the login problem, but it seems to take for ever to cycle down a list of 15 tickers. This code assumes you start on Sheet1, A1, and have a list of stock running down column A.

It seems to me that I am overdoing something in the code. Any thoughts?

Any help would be greatly appreciated.

Accessing the web is inherently slow compared to say the HDD, but you could make one major saving but not creating an IE object every query, but do it outside the Reuters module, seta public variable, and access that same instance every time.