Microsoft Office 2013 is rolling out now, and is finally adding some functionality that will enable stock quotes to be dynamically updated in our excel worksheets. Currently, stock prices must typically be updated manually by hand, or through the use of clunky web queries and/or macros. Office 2013 incorporate the addition of Office Apps, one of which is called Bing Finance and automatically connects to Morningstar to download stock data for the selected tickers. Let’s have a look and see how this works.
Real-time stock data is one area where Google Docs spreadsheets have a distinct advantage over excel. In Google Docs, stock data can be retrieved and shown in any cell with a built-in function called GoogleFinane(). For example, if I want to show the last trade price for Microsoft in a cell, we can use the following formula:
It’s as simple as that. We’re not quite there yet with Microsoft Excel, but the Bing Finance app which is now available for Office 2013 is a step in the right direction. The latest version of the app can be downloaded here. Here’s a screenshot of what the app looks like by default (click to enlarge):
The app is split into two sections. On the left is the news pane, which automatically displays a chart, data, and recent news pulled from the web for any stock ticker that is either typed into the search box in the pane, or highlighted in a cell on the worksheet to the right. The worksheet itself is a pivot table that automatically updates to the latest stock data pulled from Morningstar. Adding new tickers is as simple as adding a row and typing the new stock symbol. Columns can be added or deleted quite easily using the news pane.
Since the worksheet is really just a normal table, all of the excel functionality such as filtering, sorting, and formatting can be applied which is great. What I want to be able to do however is some detailed analysis on selected stocks, and for that I want to use separate worksheets from the primary sheet. As I mentioned above, I don’t believe there’s functionality to directly specify a cell to retrieve Bing Finance data to yet (and I’m not sure it’s coming), but what we can do is lookup stock data from our main portfolio sheet for use in our analysis spreadsheets. Let’s look at a simple example for how to do this.
Because the ability to sort data in the portfolio worksheet we don’t want to use a normal cell reference to pull our data into the analysis sheets, for example by using something like =D20. If we were to try this, then when the portfolio is sorted we would still be referencing cell D20 and would be looking at the price for a different stock than we originally intended. Instead we want to use lookup tables to ensure we always pull the correct data we’re after.
Let’s create a simple analysis worksheet for Ford (F):
- Create a new sheet and rename it appropriately (ie. Ford)
- Pull appropriate data into the analysis worksheet via the VLOOKUP function. In this case we’ll just pull the company name, price, and EPS.
- Perform any desired analysis on the data, for example calculation of the current PE.
While not as convenient as Google Docs, we at least now have a means of reliably importing stock data into MS-Excel. I’ve attached a copy of the workbook for you to experiment with here: Bing Finance basic analysis.xlsx
Enjoy, and please let me know what you think in the comments below.
The image, “Stock Market Cube” is copyright © 2012 DesignContest.com and made available under a CC Attribution 3.0 license.