Excel is so interesting because there are so many different functions and so I fully expected that there would be some interesting functions that would be added to Excel 2013 apart from those that were already announced. Today, I found one of them called “webservice”. It is a somewhat advanced function but it is really worth looking into. Basically, the webservice function in excel makes it easy to make API queries. Many big websites or services such as Yahoo finance, Twitter, eBay, Google Maps, LinkedIn, etc.
Basically, when you get the documentation of an API, you can understand how to modify the code to get the accurate data. For example, with Yahoo finance, I could easily get information about the stock, historical data, charts, news, etc. It does require some work upfront but once you get the hang of it, you can build something that is quite powerful. Over the next couple of weeks, I will be building spreadsheets that can display a couple of different types of data through this webservice API.
As you can imagine, I’m very interested in the idea of building a strong financial spreadsheet that could help me improve my analysis but also my portfolio analysis.
Understanding API “Docs”
In general, understanding API’s can be done by going to a website that explains how it works. Here is an example for the Yahoo finance API that I feel is very well done and easy to understand:
https://code.google.com/p/yahoo-finance-managed/wiki/YahooFinanceAPIs
One unfortunate aspect is that the webservices only work on the desktop version of Excel 2013. I repeat myself but I do hope that at some point both versions end up being identical (or as close to it as possible).
Microsoft did publish an example of live weather on a spreadsheet which you can see here:
Obviously, like any other web data in Excel , it does not refresh “automatically” and must be done either manually or through a macro.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
Keith Howard
Hello,
Do you know if you can pass data to a web service call? I am looking to build a concatenate function, so I want to pass a range, and get a concatenation of each cell in the range returned back. Most of the web service information I have read about seems to be just a one way download of information, rather than passing an object to the web service.
Many thanks.
Keith