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.
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.
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:
- Click the Refresh All icon under the Data or Table Design tab
- Press Ctrl + Alt + F5