Excel 2007 using web services (specifically Google Finance)

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?

Col

Posted 2010-06-30T07:42:05.737

Reputation: 121

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 – 2010-06-30T11:22:39.720

1i 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 – 2010-07-01T04:25:21.210

I'm going to investigate the coding route as highlighted by Gnoupi. Will post any further coding type questions on StackOverflow. – Col – 2010-07-01T08:13:37.253

Answers

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!

Doug

Posted 2010-06-30T07:42:05.737

Reputation: 183

This is valid only for versions of Excel prior to 2007, like Excel XP, unfortunately. – Gnoupi – 2010-07-01T17:23:44.897

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 – 2010-07-01T18:00:13.033

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 – 2010-07-01T18:45:10.713

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

variant

Posted 2010-06-30T07:42:05.737

Reputation: 1 662

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

Open the Visual Basic Editor (Alt + F11)
Click on Tools > References
In the list appearing, find the component named "Microsoft Office Soap Type Library v3.0" and check it
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.

Gnoupi

Posted 2010-06-30T07:42:05.737

Reputation: 7 909

Thanks Gnoupi

Have added the reference but no Web Service Reference menu entry appears. I've tried saving, closing and reopening the workbook. – Col – 2010-06-30T08:12:19.260

@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 – 2010-06-30T09:08:56.190

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 – 2010-07-01T08:12:13.573

@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 – 2010-07-01T08:38:12.873