Building A Stock Spreadsheet In Excel 2013 Using The Webservice Function (Part 1)
Excel 2013, Excel function tutorials
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!