Excel web Query

You want to query an .xls file which lies on some server?

You can create a DSN entry in ODBC ( odbcad32.exe ) to the desired Excel file. Then you'll be able to query it using a regular ADO syntax.

HTH,
Jigit

can you give me an example of the code you would write to get the P/E ratio from Yahoo?

I would use one of the following connection string:
OLE DB:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""

("HDR=Yes;" indicates that the first row contains columnnames, not data)

ODBC:
"Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=c:\somepath\mySpreadsheet.xls;" & _
           "DefaultDir=c:\somepath"

This makes your code much more portable.  And if you don't have access the DSN on the server then this would be close to impossible.  Alot of times Hosters only allow you to setup DSN entries for Access and CSV files.

After you opened your URL in Excel then is this P/E cell at your disposition.
For example for this URL:
http://finance.yahoo.com/q?s=MSFT&d=t

Was P/E in D17

So what is your question?

It would be easy to look for the cell D17. It would also be easy to just go the webpage and look it up.. However if I wanted to have 50 stocks looked up in a workbook detailling P/E or P/B, Institutional ownershir, or whatever I want then I would have to run 50 individual web queries.  I was looking for a way to pull out only specific information in a batch......

OptionsMM,

Try to check out the link might help...

http://support.microsoft.com/default.aspx?scid=KB;en-us;q189198

http://support.microsoft.com/default.aspx?scid=KB;en-us;q195951

It seams that nobody reads the question :-)

Ok OptionsMM, basically is then your question to know some secret URL parameters sending you only some parts from the page.
Generally is from my observation this detailed parameter not there and if it would be is it a matter of change for next yahoo parameter upgrade.

So from my view you have to manipulated the returned page by yourself.
If you get to this point then you have two options:
1.) Import the web data in Excel and proceed the processing in VBA.
2.) Use web enabled dll's and ActiveX like MSXHTML and get the web data in variables and fish for them in script objects.

For the option number two I could show you a JavaScript example, if you like.

Zvonko

it appears that #2 is going to have to be the one.  What I cold do is have a list of stocks on sheet1.  when i run a macro it can build 50 web queries on sheet2 that look up the tables I need, then fish through the tables for the info with VBA.  

unfortunately since i dont have any special acess to the web page and this seems to be the most logical approach.

Ok, I can do this for 500 points :-)

So give me two or three URLs of your interest and I try to build a macro to fetch the appropriate cells, either by Excel cells or by HTML table cells.

Is this what you want?

Oh, and my approach will work only on workstations which have both installed: Excel and at least IE5.5

IE need not to be running, I only use installed DLLs.

Okay Zvonko you have a deal my friend.

The page of choice is

http://biz.yahoo.com/p/m/mmm.html

Under "Ownership" on the left hand column
..................................................
Ownership
· Insider and 5%+ Owners: 1%
· Over the last 6 months:
 · 4 insider sells; 14.0K shares
  (0.5% of insider shares)
· Institutional: 73% (74% of float)
(2,383 institutions)
· Net Inst. Buying: 6.89M shares (+2.35%)
(prior quarter to latest quarter)
 
..............................................

I would like to pull out the 0.5 number (of insider shares).  If there isn't any insider selling, sometimes this is blank.

I would like to pull out the 74 number (74% of float).

under Statistics at a Glance at the bottom half

I would like

Daily Volume (10-day avg) 2.33M
Market Capitalization $48.9B
Float 387.4M
Annual Dividend (indicated) $2.64  
Dividend Yield 2.11%
Price/Book (mrq) 8.16  
Price/Earnings (ttm) 25.11  
Price/Sales (ttm) 3.04  
Current Ratio (mrq) 1.36  
Debt/Equity (mrq) 0.56  
Total Cash (mrq) $618.0M
Percent of Float 2.0%

Well if you can get one or two that would be plenty if you could show me how you did it.

Thanks :)

I have to break for tonight.

I present you what I have so far.
It does not work well because yahoo breaks its html lines very strange :(

Ok, I put the data in Excel cells like this:
A1: http://biz.yahoo.com/p/m/mmm.html
A2: (<$>% of insider shares)
A3: (<$>% of float)

Here is my Macro code so far:

Option Explicit


Sub getURLdata()
'
'
Dim nextURL As String
Dim webResponse As String
Dim nextEyecatcher As String
Dim i As Integer
Dim j As Integer
    For i = 1 To 3
        nextURL = Cells(i, 1).Value
        If (nextURL = "") Then Exit For
        webResponse = GetWebContent(nextURL, "", "")
        If (webResponse > "") Then
           For j = 2 To 5
              nextEyecatcher = Cells(i, j).Value
              If (nextEyecatcher = "") Then Exit For
              MsgBox ExtractData(webResponse, nextEyecatcher)
           Next
        End If
    Next
 End Sub

Function GetWebContent(ByVal URL As String, ByVal UserID As String, ByVal Password As String) As String
    Dim uid As Variant
    Dim pwd As Variant
    Dim xml As Variant
    uid = UserID
    pwd = Password
    Set xml = CreateObject("Microsoft.XMLHTTP")
    Call xml.Open("GET", URL, False, uid, pwd)
    Call xml.Send
    GetWebContent = xml.responseText
End Function

Function ExtractData(ByVal textArea As String, ByVal eyeCatcher As String) As String
Dim dataPos As Integer
Dim leftPart As String
Dim rightPart As String
Dim nextPos As Integer
Dim nextData As String
Dim i As Integer
    dataPos = InStr(1, eyeCatcher, "<$>")
    If (dataPos > 0) Then
        leftPart = Left$(eyeCatcher, dataPos - 1)
        rightPart = Mid$(eyeCatcher, dataPos + 3)
        MsgBox leftPart & " / " & rightPart
        If (Len(leftPart) > Len(rightPart)) Then
            nextPos = InStr(1, textArea, leftPart)
            If (nextPos > 0) Then
                nextData = Mid$(textArea, nextPos + Len(leftPart), 80)
                MsgBox "R: " & nextData
                nextPos = InStr(1, nextData, rightPart)
                If (nextPos > 0) Then nextData = Mid$(nextData, 1, nextPos - 1)
             End If
          Else
            nextPos = InStr(1, textArea, rightPart)
            If (nextPos > 0) Then
                nextPos = nextPos - Len(rightPart)
                For i = nextPos To nextPos - 80 Step -1
                    If (InStr(i, textArea, leftPart) > 0) Then
                       nextPos = i + 1
                       Exit For
                    End If
                Next
                nextData = Mid$(textArea, nextPos, 80)
                MsgBox "L: " & nextData
                nextPos = InStr(1, nextData, rightPart)
                If (nextPos > 0) Then nextData = Mid$(nextData, 1, nextPos - 1)
             End If
          End If
      End If
      ExtractData = nextData
End Function


See you tomorrow,
Zvonko

why not set up the web query on one sheet and use another sheet which references the PE cell from the web query.

I have a similar excel file that queries gas prices from a variety of source and then formats them on a main page.

The "refresh all" function get all the prices from the various sites at once.

Give now please this code a try:

Sub getURLdata()
'
'
Dim nextURL As String
Dim webResponse As String
Dim nextEyecatcher As String
Dim i As Integer
Dim j As Integer
   For i = 1 To 3
       nextURL = Cells(i, 1).Value
       If (nextURL = "") Then Exit For
       webResponse = GetWebContent(nextURL, "", "")
       If (webResponse > "") Then
          For j = 2 To 5
             nextEyecatcher = Cells(i, j).Value
             If (nextEyecatcher = "") Then Exit For
             MsgBox ExtractData(webResponse, nextEyecatcher)
          Next
       End If
   Next
End Sub

Function GetWebContent(ByVal URL As String, ByVal UserID As String, ByVal Password As String) As String
   Dim uid As Variant
   Dim pwd As Variant
   Dim xml As Variant
   uid = UserID
   pwd = Password
   Set xml = CreateObject("Microsoft.XMLHTTP")
   Call xml.Open("GET", URL, False, uid, pwd)
   Call xml.Send
   GetWebContent = xml.responseText
End Function

Function ExtractData(ByVal textArea As String, ByVal eyeCatcher As String) As String
Dim dataPos As Integer
Dim leftPart As String
Dim rightPart As String
Dim nextPos As Integer
Dim nextData As String
Dim i As Integer
    dataPos = InStr(1, eyeCatcher, "<$>")
    If (dataPos > 0) Then
        leftPart = Left$(eyeCatcher, dataPos - 1)
        rightPart = Mid$(eyeCatcher, dataPos + 3)
        If (Len(leftPart) > Len(rightPart)) Then
            nextData = stringLeft(stringRight(textArea, leftPart), rightPart)
        Else
            nextData = stringLeft(textArea, rightPart)
            nextData = StrReverse(stringLeft(StrReverse(nextData), StrReverse(leftPart)))
        End If
    End If
    ExtractData = nextData
End Function

Function stringLeft(ByVal textArea As String, ByVal searchString As String) As String
Dim nextPos As Integer
Dim nextData As String
Dim i As Integer
    nextPos = InStr(1, textArea, searchString)
    If (nextPos > 0) Then
        nextData = Mid$(textArea, 1, nextPos - 1)
    End If
    stringLeft = nextData
End Function
Function stringRight(ByVal textArea As String, ByVal searchString As String) As String
Dim nextPos As Integer
Dim nextData As String
Dim i As Integer
    nextPos = InStr(1, textArea, searchString)
    If (nextPos > 0) Then
        nextData = Mid$(textArea, nextPos + Len(searchString))
    End If
    stringRight = nextData
End Function

awesome!  this will do very well thank you!

job well done!

You are welcome :-)