Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Can Excel 2007 access web services, specifically Google Finance or Yahoo Finance?

I'd like to be able to have my workbook automatically update share/fund prices from either of these sites.

I know you can import tables from websites (Import Data From Web) but this does not meet my needs. Google Spreadsheets has a GoogleLookup function to lookup prices automatically but I'd prefer to use Excel to make use of pivots, table formatting etc.

If not, does Excel 2010 have this capability?

share|improve this question
    
Now voting to migrate on Stack Overflow. I thought there was a "user" answer, like I described at first, to activate simply the library. However, it seems that the only ways to do it require visual studio, or in general more programming related things, and this question will probably be better on Stack Overflow. –  Gnoupi Jun 30 '10 at 11:22
1  
i think this question, as currently written, is best left here at Super User @gnoupi ... but if the answer really is "do it from Visual Studio", followup questions about specific methods or functions ought to be posted to Stack Overflow. –  quack quixote Jul 1 '10 at 4:25
    
I'm going to investigate the coding route as highlighted by Gnoupi. Will post any further coding type questions on StackOverflow. –  Col Jul 1 '10 at 8:13

3 Answers 3

Here is a link to all the relevant information you will need in order to leverage the "Microsoft Office Soap Type Library v3.0" library. Click here for detailed documentation.. Also here is a sample showing how to consume some stock quote data.

It does look like you will have to do a little bit of VBA programming but there seem to be lots of examples and even sample excel workbooks you can download.

Enjoy!

share|improve this answer
    
This is valid only for versions of Excel prior to 2007, like Excel XP, unfortunately. –  Gnoupi Jul 1 '10 at 17:23
    
Hi Gnoupi, I dont see any reason why this would not work in 2007? I will put together an example later this afternoon / eventing and post it for you. –  Doug Jul 1 '10 at 18:00
    
Like I said more or less in my answer, it will work for 2007, the library is even already included, but you won't have the assistant allowing you to generate the objects directly from a web service. Also, from several sources, it seems that this is not the recommended way to do things anymore in the new versions. For example, this component doesn't handle WS-security. –  Gnoupi Jul 1 '10 at 18:45

Entirely doable through VBA.

Dim objHttp As Object, strURL as string, strText as string

Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")

strURL = "http://www.google.com/finance?q=NASDAQ%3AMSFT"

objHttp.Open "GET", strURL, False
objHttp.setRequestHeader "User-Agent", _
  "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHttp.Send ("")

strText = objHttp.responseText

'And this is where you parse apart the strText 
'(effectively the HTML of the site) to locate your relevant data

Set objHttp = Nothing
share|improve this answer

Edit: This works only for Excel 2003 (though it requires installation of a plugin, also)

It can, yes. You need to activate them in VBA.

  1. Open the Visual Basic Editor (Alt + F11)
  2. Click on Tools > References
  3. In the list appearing, find the component named "Microsoft Office Soap Type Library v3.0" and check it
  4. Click OK, the component is installed.

To generate the methods to access a Web Service, you need to specify the definition file (wsdl) in the "Web Service Reference" menu entry, in tools, which should appear after.

To actually access data from then, you will need VBA programming, and I invite you to ask more questions about it on our sister site dedicated to programming questions, Stack Overflow.


Ok, this method worked in Excel 2003, but it seems it's not the recommended way nowadays. I can't find as well the menu I told you about. The library I referred to is probably used for compatibility reasons.

Now all I can find about it is to do it from Visual Studio 2008, or to follow more pointers at this Stack Overflow question.

share|improve this answer
    
Thanks Gnoupi Have added the reference but no Web Service Reference menu entry appears. I've tried saving, closing and reopening the workbook. –  Col Jun 30 '10 at 8:12
    
@Col - I don't have access to Excel 2007 right now, I will check later. The entry could be different, I gave the name by memory. –  Gnoupi Jun 30 '10 at 9:08
    
Thanks for the links Gnoupi, am particularly interested in the VS2008 option as I'm a C# dev anyway. Was hoping for an easy way to do this that didn't involve coding a la Google Spreadsheets –  Col Jul 1 '10 at 8:12
    
@Col - even with the component I know from Excel 2003, you would have needed at least a bit of programming, to put the results of the web service to the sheet. This component would return the objects defined in the WS, but that's all. –  Gnoupi Jul 1 '10 at 8:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.