Get Current and Historical Stock Prices into Excel

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 O365 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 Table 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. 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 data 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.com.

The Yahoo service works well with Power Query and has a logical URL structure that is easy to customize for your desired ticker symbol. It uses the format:

https://finance.yahoo.com/quote/YOUR-TICKERHERE/history/

Since Microsoft’s ticker is MSFT, we could type the following URL into any web browser to see the historical quotes:

• https://finance.yahoo.com/quote/MSFT/history/

We test the URL for our desired ticker by entering it into a web browser. If the page works, we’ll see a table full of daily prices. Now, there are a few ways to get data displayed on the web page into Excel. We could select the range on the web page, copy (Ctrl+C) the info, then open an Excel workbook and do a Paste or Paste Special.

Depending on the webpage formatting, this may or may not work well. If it does, this quick copy/paste method may be all we need.

Another option is to download a file from that webpage (if available) and then open it with Excel. This option typically avoids the formatting issues that can occur with copy/paste. In the case of the Yahoo page, there is a Download Data link that exports a CSV file that can be opened with Excel. And, if this was a one-time project, this option may be sufficient.

But, if this was something we needed to update on an ongoing basis, another option is to use Power Query. Going forward, rather than browse to the webpage to view and download or copy the data, we can just click Refresh from within Excel. To do so, click Data > From Web in the Get & Transform group (not the legacy Get External Data group). This will open Power Query’s From Web dialog. You simply paste or type your desired URL (Figure 4).

This will display Power Query’s Navigator dialog where you’re provided a list of importable items, which are essentially various tables that appear on the web page. We click through the list of tables until we see the one we want to import. For example, in our case, Table 2 holds the historical quotes (Figure 5).

With the desired table selected, we just click the down arrow on the Load button and select Load To. In the resulting dialog, we select Table and … the results are loaded into the specified worksheet (Figure 6).

Now, the nice thing about using Power Query is that next time we need to retrieve updated historical quotes, we can simply right-click any cell in the results table and select Refresh. Power Query essentially retrieves updated values from the original URL. In Excel, we see the updated table rows.

I hope these methods provide an efficient way to retrieve current and historical stock quotes into your Excel file. And remember, Excel rules!