Accountant Life Hacks.

• Author: Lenning, Jeff
• Position: Softwaretips&tricks

microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you're typing closing prices into Excel manually, this article may be able to help you get it done faster. Specifically, we'll talk about how the Stock data type retrieves current quotes and related information, and how Power Query can retrieve historical stock quotes from the web. We'll take them one at a time.

Note: This article is presented with Excel 0365 for Windows; not all versions of Excel include the features discussed.

Current Prices with the Stock Data Type Let's say you have a handful of tickers and you want to view their current prices or related information. Perhaps your list is stored in a table (Insert > Table) and looks like Figure 1 Note: converting your ticker list into a fable isn't required, it just makes this feature easier to use).

You can select the tickers, then click the Data > Stocks command in the Data Types group. When you do, Excel attempts to convert those static text values into stocks (Figure 2).

The little icons indicate Excel has successfully found the ticker symbols and converted them into stocks. So, what is so special about the stock data type? It retrieves a rich collection of market data--including current price, volume, high, low. company name, company description, number of employees and much more. To reveal these additional attributes, just click the little stock icon to the left of the company name and you'll see a pop-up card that contains related data. Or, if you want to view the related data in cells, just click the little Add Data icon in the upper right (Figure 3). For example, we can select price, high and low from the list and Excel retrieves the corresponding values (Figure 3).

You can manually refresh the values any time by right-clicking any of the stock icons and selecting Data Type > Refresh. If you had previously used the MSN Money Central Investor Stock Quotes connection, this is the replacement feature and provides much more information.

But what if you want to retrieve historical quotes? For that, we can import dara from a corresponding web page with Power Query.

Historical Prices with Power Query At the time of this article, the Stocks data type contains current quotes only. So, if we want historical quotes, we'll need to turn to the web. Pull up your favorite historical quotes web page or do a web search. In this article. we retrieve historical quotes from Yahoo. The Yahoo service works well...