Stock Ticker For Excel Download Data Link

If youre typing closing prices into Excel manually, this article may be able to help you get it done faster.

Specifically, well 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.

Perhaps your list is stored in a table (Insert Table) and looks like Figure 1.

When you do, Excel attempts to convert those static text values into stocks (Figure 2).

So, what is so special about the stock data type It retrieves a rich collection of market dataincluding 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 youll 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).

If you had previously used the MSN Money Central Investor Stock Quotes connection, this is the replacement feature and provides much more information.

So, if we want historical quotes, well 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.

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 (CtrlC) the info, then open an Excel workbook and do a Paste or Paste Special.

This option typically avoids the formatting issues that can occur with copypaste.

Stock Ticker For Excel Download Data Link

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.

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

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

In the resulting dialog, we select Table and the results are loaded into the specified worksheet (Figure 6).

Stock Ticker For Excel Mac