Stock Quotes in Excel with Office 2013

Stock market quotes on cubeMicrosoft 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:

=GoogleFinance(“MSFT”; “price”)

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):

  1. Create a new sheet and rename it appropriately (ie. Ford)
  2. Pull appropriate data into the analysis worksheet via the VLOOKUP function. In this case we’ll just pull the company name, price, and EPS.
  3. 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.

About Dustin Small

Dustin Small is the founder and editor of Stockodo and is a regular contributor to Seeking Alpha. You can learn more about him here and connect with Dustin on Twitter and Facebook.

Comments

  1. Thanks Dustin. That was very useful. Just wished to point out that those Stocks whose prices are in 4 figures, say 1423.95, Bing Finance imports the prices into the table as “1.4k”. This then results in the error in all other cells where a formula is entered using this cell.. Any idea how to tackle this?

    • Hi Kannan,

      Yes, I was able to replicate the same problem you’re experiencing. I did some research and it looks like others are having the same problem as well. At first I thought it was maybe just custom formatting adding the “K” to the end, but it is actually storing the entire cell as text instead of a number which is why the formulas are causing errors. Bing Finance is still in beta so hopefully they’ll fix this in a future version, but in the meantime we should be able to work around this by using excel functions to manually split up the text to remove the K. I’ll see what I can figure out but I think it will involve using the LEFT and/or RIGHT functions.

      • Ok, I figured out a simple way to remove the “k” from the price and convert it to a true number that can be used in calculations. Use the following excel formula:

        =VALUE(SUBSTITUTE(A1,”k”,””))*1000

        In the above, change A1 to the cell that contains the price that you want to remove the k from.

  2. Hitesh Patel says:

    I am glad I got stumble upon this website… I like simplicity and clarity … I would like to share with you program called XLQ (www.qmatix.com)… for people who are comfortable using Excel… this is a GREAT program and at reasonable price for Individual Investors…. Also if you are a member of AAII (www.aaii.com) and use their product called Stock Investor Pro you can combine these programs and that becomes the Top Notch Product… I personally use them and extremely satisfied and would recommend to all.

  3. Actually Excel has imported stock quotes seamlessly since Excel ’95, (and even prior to that if you downloaded the free Internet connectivity kit in 1994). From Excel 2003, select Data, Get Data, Existing Connections and select the Stock Quote query option. This Works in Excel 2007 and 2010 too, although the menu selections are slightly different.

    My question is why did Microsoft get rid of this in Excel 365 2013 Home Premium Edition.

  4. Lee Gomes says:

    Hi Dustin
    I downloaded your sample spreadsheet, into Excel 2013 with the Bing add-in. It works for F and MSFT in the B1 cell, but doesn’t seem to accept other ticker symbols that I type in B1, at least any of the ones I’ve tried. (A bunch.) I get a #N/A
    in the cell instead of the right info? Any idea what might be happening?
    Many thanks

    • Hi Lee,
      Yes, I think I know what is happening. The spreadsheet pulls data from the “Portfolio” tab in the same workbook, so any stocks you want information on need to be added to the portfolio first. If you switch to that tab and add the tickers you’re interested in, you should then be able to return to the 2nd tab and have it work properly. Hope this helps!

      • Lee Gomes says:

        Hi Dustin
        Thanks for the reply. Yes, that was the issue exactly, as I probably could have discovered for myself if I had bothered to read the manual. ;)
        One more question, though, and one that will expose by beginner-ness. Where exactly is the query that generates the table in “Portfolio” in the first place? It doesn’t seem to be in any particular cell. You have a note on how to delete a stock, but suppose I wanted to add one? What would I need to edit?
        Separately, considering your interests, I thought you might enjoy this recent piece from Barron’s, about the utter futility of all “market timing” efforts. http://online.barrons.com/article/SB50001424052748704836204578354851782592898.html#articleTabs_article%3D1
        Depressing but quite convincing.
        Best,
        Lee

  5. stanley jones says:

    Is there any other program/add-in that can be used to insert stock quotes in excel 2013? I think the Bing Finance app is horrible. I used to use the micorosoft stock add-in but that doesn’t work with the 2013 version of excel. thanks.

    • I agree that the bing finance app leaves a lot to be desired. It’s been around for several months now and I had hoped they would have improved it more than they have. I’ve actually switched to using Google Docs rather than excel for spreadsheets where I want to pull in real-time market data…. much easier than excel.

  6. Richard says:

    I think for the versions below 2013 the following works well for me…
    http://technitya.com/content/stock-quotes-excel

  7. I have been using the plugin below for quite some time. Works great for me. It use Yahoo API and integrate it seamlessly with Excel as exposed custom functions.

    http://www.technitya.com/content/stock-quotes-excel

Share Your Thoughts

*