by Allen Wyatt
(last updated June 22, 2019)
Tony likes to keep track of his relatively small stock portfolio in an Excel worksheet. He's been manually entering stock values, but now Tony is wondering if there is a way to have stock prices update automatically in the worksheet.
Keeping track of stock information is, for many people, a common task in Excel. If you are using Office 365, Microsoft recently (end of March 2019) made it possible to add dynamic stock information to a worksheet. (The capability was actually added last year, but it wasn't fully rolled out to all Office 365 users until March 2019.) All you need to do is follow these general steps:
Figure 1. Ready to grab stock information in Excel.
Figure 2. The stock symbols are converted to fuller information.
Figure 3. Data available about your stocks.
Figure 4. Stock prices added to the right of the stock symbols.
That's it; you now have your dynamic stock information in your worksheet. That's not all, though. You'll notice that immediately to the left of the stock symbols (in the same cell) there is a small icon. This is called a "card," and if you click on it, you'll see summary information about the stock. (See Figure 5.)
Figure 5. Displaying the card information for a stock.
According to Microsoft, the information provided by this stock capability is real-time. More information about this can be found at this terribly long URL:
You should note that one of the drawbacks of the built-in stocks in Excel is that they won't work with foreign exchanges. They are great, however, for US stock exchanges.
What if you aren't using Office 365 or you need the capability to access foreign stock information? Are you out of luck? Not really, but your available solutions won't be as integrated into Excel as you might like. You can, for instance, use PowerQuery to extract stock information from a website and store it in a worksheet.
You could also add a third-party tool to do the grabbing and analysis for you. ExcelTips subscribers have suggested the following possible tools:
https://www.quotelink.net/ http://www.michael-saunders.com/stocksapp/pages/info.html https://www.powerusersoftwares.com/so/8fMbYWA94
We haven't tried these tools out, but they (and other similar tools) may work for your purposes.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13646) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Need to modify how a cell reference, in a formula, is constructed? The shortcut described in this tip will help you step ...Discover More
Ever had your Excel status bar disappear unexpectedly? Here's some ideas on why this may be happening.Discover More
Excel is a great tool for analyzing financial information. If you want to do some analysis using stock information, it is ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.