Excel is one of the best platforms to do stock quote analysis. However, you traditionally had to pull this data either manually, or using third party tools. Now, you can do this using built-in Excel features if you are running Office 365. In this article, we are going to show how to get Excel stock quotes, along with tips and tricks about the new Data Types feature.

In September 2018, Microsoft has announced new features like the Data Types, and Dynamic Arrays, and dynamic array functions to go with these additions. The Data Types feature allows pulling data dynamically from online sources. Currently, this function has 2 subcategories called: Stock and Geography.

Note: Data Types have been released to all Windows Office 365 subscribers in March 28, 2019. Thus, you need to be an Office 365 subscriber to access this features.

Download Workbook

Step 1: Enter company names or stock codes

All you need to do is enter the companies names or stock codes you want to use. Excel will automatically find the matching stock quote information. Enter names into a column and select them.

Optional: Convert your range into a table by pressing Ctrl + T. Aside from a table‘s own advantages, you will automatically get titles of the information you will get online.

Step 2: Click Stocks in Data tab

Next step is defining the selections as a Data Type. With company names and/or codes selected, go to the Data tab, and then click Stocks.

Step 3: Verify the stock names

After clicking the Stocks icon, you will see cell values replaced with the stock codes. If Excel can’t recognize any of the names, you will see a question mark icon instead. If this happens click on the name and check the suggestions in the right-side panel.

Step 4: Add stock exchange information

You will see a new icon if you select a cell linked with a Data Type. This icon name is Add Column which lists available columns related to Data Type.

Click Add Column and select the information type you want to add your table. The titles of the columns will be added when you do so. This won’t happen if you do not use a table.

(Alternative) Step 5: Add information by formula

You can also add any information by typing in the relevant codes. Select a cell next to your table, and start with an equal sign. Then enter the reference of a Data Type cell, and follow up with a period (.) to get the list of data.

Step 6: Refresh

Real time stock quotes Excel will give you will be a little delayed (currently about 15 minutes), but you can refresh the workbook to get updated values. There are 2 ways to do this:

  1. Click the Refresh All icon under the Data or Table Design tab
  2. Press Ctrl + Alt + F5