How to view current stock prices and other quotes in Excel

Excel 2016
There are countless reasons why you might need to have stock prices in your Excel spreadsheet. In the past, you had to copy and paste from Yahoo Finance or another financial service, but at the moment, Microsoft offers a new way to add and update some stock information such as stock prices, changes of price, etc.
Stocks prices and other quotes in Excel 365

To add the actual and updatable information about stocks, do the following:

   1.   Type ticker symbols or company names for which you want to add stock information.

   2.   Select these cells.

For example:

Stocks names in Excel 365

   3.   On the Data tab, in the Data Types group, click Stocks:

Stocks Data Type in Excel 365

Excel will try to convert the selected text to the Stocks data type. If the transformation was successful (Excel found in the Microsoft online source a matching stock data for the value in the cell), the cell data have the stock icon Stock icon in Excel 365:

Stocks data in Excel 365

Note: For some data (in this example, for Facebook), Excel can't find the appropriate stock record in the Microsoft online sources. It can happen due to spelling mistakes or when Excel needs your help to interpret the stock correctly. In this case, click on the question icon to open the Data Selector pane:

Data Selector pane in Excel 365
  • Modify the name in the field on the top, if there is a spelling error and click the find icon Stock find icon in Excel 365.
  • Select the appropriate stock data, if there are several stock markets/exchanges for the stock ticker or name:
Stocks Data Type in Excel 365

   4.   To add the stock data, do the following:

   4.1.   Select one or more cells with the Stocks data type (with the stock icon Stock icon in Excel 365) and click the Add Column button Add Column button in Excel 365:

Stocks Data Type columns in Excel 365

   4.2.   In the drop-down list, select the data that you want to add to the spreadsheet.

   4.3.   Click the Add Column button again and add more data columns.

For example:

Stocks information in Excel 365

Notes:

  1. To see all of the fields available for a company or fund, click the stock icon ( Stock icon in Excel 365 ) or select the cell and press Ctrl+Shift+F5:
    Available Stock information in Excel 365
  2. To refresh stock data, do one of the following:
    • On the Data tab, in the group Queries & Connections, click the Refresh All button:
      Refresh All in Excel 365
    • Right-click the stock data (cells with stock icon Stock icon in Excel 365) and choose Data Type -> Refresh in the popup menu:
      Refresh in popup menu Excel 365

    This refreshes all stock data in the workbook, not only in the current spreadsheet!

  3. To fix or change the Stocks data type to the regular text, do the following:
    1. Select the data that you want to change (cells with stock icon Stock icon in Excel 365).
    2. Right-click on the selection and choose Data Type -> Convert to Text in the popup menu:
      Convert to Text in popup menu Excel 365

    This converts the Stocks data type to the regular text, and all depended data don't be shown anymore:

    Not Stocks data type in Excel 365

    To save all depended data, before converting the Stocks data type, do the following:

    1. Select all depended data that you want to save, copy it to the Clipboard (by clicking the Copy button on the Home tab, in the Clipboard group, or by pressing Ctrl+C).
    2. On the Home tab, in the Clipboard group, click Paste, and then select one of the Paste Values buttons:
      • Paste Values in Excel 365   Values (V) pastes the results of formulas. The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values
      • Paste Values and Number Formatting in Excel 365   Values & Number Formatting (A) pastes the results of formulas, plus the number formatting
      • Paste Values and Source Formatting in Excel 365   Values & Source Formatting (E) pastes the results of formulas, plus all formatting
  4. Stock information is delayed, provided "as-is", and is not for trading purposes or advice.
  5. To work with stock information, you need to have an online access to the Internet and an appropriate version of Microsoft Excel.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more