After introducing the new webservice function that was added to Excel 2013, I’m looking to build my first spreadsheet today and I thought it’d be interesting to build a spreadsheet that can access the Yahoo finance API. Let’s start off by building a sheet that will display information about a specific stock which I can change. I will start off by adding a place where I’d have that stock’s ticker:

Then, when I go to the Yahoo API explanation page, I see that I can get access to the stock’s price with this URL:

http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=nsl1op&e=.csv

I will simply change that “GOOG” reference to the cell C2 and look at the result:

Then, I could simply access the price from that cell using the “mid” function. Let’s try something else though. I would like to pull the close of every day since January 2012, to build myself a nice chart. Using the function found in:

https://code.google.com/p/yahoo-finance-managed/wiki/csvHistQuotesDownload

I can try using the following:

http://ichart.yahoo.com/table.csv?s=GOOG&a=0&b=1&c=2000&d=0&e=31&f=2010&g=w&ignore=.csv

Again, I will replace “GOOG” with a reference to cell C2:

Again, some formatting would be required but I think this gives you an idea of how poerful these webservices are as you can easily get in a ton of data about any stock. As a last addition for today, I will add news about Apple using the Yahoo news RSS for Apple. First, I went to Yahoo’s Apple page and got the URL of its RSS news here:

Then, I went into Excel, in the developer tab and clicked on source where I added that and dragged it into my spreadsheet as you can see here:

There are many other ways all of this can be done but it does give you an idea of the possibilites offered thanks to XML and webservices functions in Excel 2013… More on this very soon!

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:

http://blogs.office.com/b/microsoft-excel/archive/2013/03/21/add-weather-data-and-other-dynamic-web-content-into-excel-2013-using-webservice-functions.aspx

Obviously, like any other web data in Excel , it does not refresh “automatically” and must be done either manually or through a macro.

This is some very interesting stuff. I really think that integration with other Microsoft products such as Bing maps (in this example) will turn out to be key in the future of Microsoft and how it’ll be able to compete with cloud based solutions such as Google Docs.

Just take a look at this video that Microsoft just release:

One of the most important and useful parts of Excel as you surely know is the improvement and automation of processes. One interesting feature is the ability to setup different “control panels” that get data from a few sources and generate alerts.

Today, I wanted to look at an example where someone would have different currency risks and would like to easily see when one of those risks approaches or breaches a limit. First, take a look at the table in this example:

I’d like to have any data that is at 80% to 99% of the limit become orange. Then, if it breaches the limit, I’d like that exposure to become red. To do so, I simply select the cells then select: conditional formatting:

Here is the new result:

It’s very simple right? As the exposure (which could be linked to other sheets, etc). You could even have a script that checks periodically and sends out an email if a limit is breached or brings up a popup.

You can download the spreadsheet here!