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:

I had read about the fact that creating charts was now much easier in the new Excel version so today I decided to do something quite straightforward. I downloaded closing prices for a few dozen days for 3 different stocks, Apple (AAPL), Amazon (AMZN) and Google (GOOG) and this what I had in my spreadsheet:

Then, I simply selected that data:

Clicked on “insert” and then the type of chart I was looking for:

And here is the result:

WOW! Now of course, I’d need to change the title and perhaps a few details but overall, it’s very close to what I was looking for and took me about 30 seconds… This is certainly one of the more interesting aspects of Excel 2013.

I continue to dive into Microsoft Excel 2013 looking for new features, functions, differences and in the past few days, I’ve been diving into a new aspect that is truly fascinating. What is it? Quite simply, Microsoft Excel has added an incredible number of templates. How do you access them? It’s quite simple. You start off by simply opening Microsoft Excel and then click on:

File/New/Templates

Then, you can simply select the template you’d like to try or search for it:

You can get more details about the actual spreadsheet then press on “create”:

You can then get a great step ahead with a spreadsheet that includes formulas, charts, apps (as you can see on the left, which needs to be downloaded):

The best part is that there are dozens if not hundreds of different template categories which can give you ideas or a start no matter what you’re trying to build. I’ll certainly be looking into some of these templates to discuss the Excel 2013 opportunities.

Have any of you tried to work with templates? How has your experience been so far?