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.
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.