Add stock quotes and real-time metrics to Excel (2023)

Add stock quotes and real-time metrics to Excel (1)

We finally did it!

On June 5, 2019, Microsoft released a feature for Office 365 that allows Excel users to insert real-time stock quotes into their spreadsheets. This feature was never really supported in Excel, although many Excel users use Yahoo! Financing until end of performance in 2018 (thanksVerizon!).

Excel now has the ability to pull data from stocks, bonds, currencies, and even cryptocurrencies like Bitcoin. Microsoft is partnering with Nasdaq and Refinitiv to provide up-to-date financial data in a new feature called "type of data.

Add stock quotes and real-time metrics to Excel (2)

What are linked data types?

Linked data types contain a connection to an online data source. They allow you to take an entity like the country of France and automatically pull up-to-date data about it (ie population, executives, GDP, etc.).

Add stock quotes and real-time metrics to Excel (3)

Excel currently has two types of linked data types:

  1. Behavior

  2. Geography

There are plans to add more data types in the future. I could see some interesting uses for medical information, vocabulary, and sports statistics.

What is available in stock?

Currently, these are the categories of data that can be accessed through the inventory data type:

  • Behavior

  • Link

  • investment funds

  • coins


Convert stock symbols to stock data

How do you start to include inventory data in your spreadsheet? There are two methods you can use to set it up.

Start typing actions

A decent performance that cametype of dataExcel now has the ability to recognize when you are working with inventory information. After you enter three consecutive stock names or symbols, Excel will probably recognize what you're trying to do and prompt you to convert the cells to a stock data type.

Add stock quotes and real-time metrics to Excel (4)

When you clickconvert into sharesYour cells will be immediately renamed to the official stock name stored in Microsoft source data, and the cell also contains Excel filesStock data type iconto the left of the action name.

Add stock quotes and real-time metrics to Excel (5)

Tell Excel that your data is inventory data

You also have the option of manually telling Excel to convert the data in your cell to the inventory data type. Simply select the cells containing the stock/ticker symbol names and navigate todata guideon the Excel ribbon. Then clickshare buttoninsidedata type group.

After clickingBehaviorclick, Excel will try to convert as many values ​​in the selected cell to value data types as possible. If Excel needs help, it offers suggested options for the value entered in the right pane.

Add stock quotes and real-time metrics to Excel (6)

Add metrics/stock information

now that you haveData Types for Linked ActionsSet up on the table, you can start adding fields that contain metrics and information about specific actions in your data set. If you select a cell linked to stock data, you will notice a smalladd fieldicon in the upper right corner of the selected cell. When you click on the icon, you'll see a list of different fields you can add that are tied to that specific stock. Select one and the field will be added to the right of your current record.

Add stock quotes and real-time metrics to Excel (7)

using formulas

If you rely more on memory, there is a naming formula you can write to bring this data back. Not all fields are available for all data types, so you may want to group your formulas with IFERROR formulas when comparing separate data types (ie, for currencies, the Number of Employees field would not be available).

Add stock quotes and real-time metrics to Excel (8)

no titles

Unfortunately, no header is auto-completed with the fields you add. You must manually add them yourself.

Also note that the new field data is only added to the originally selected inventory. You must drag the formulas to fill in the rest of the data with all your actions.

Add stock quotes and real-time metrics to Excel (9)

To avoid this, you can put your data in an Excel spreadsheet (ctrl+t) and the formulas will be pasted automatically by default (only after you have downloaded the first few formulas).

Add stock quotes and real-time metrics to Excel (10)

How do I update the data?

a cunning

The Stocks and Geography data types are consideredconnectedtype of data. This is because they refer to an online data source. After the text is converted to a linked data type, an external data connection is established in the workbook. That way, if the data changes online, you can update it by refreshing it in Excel. To update the data, right-click a cell with the linked data type and clicktype of data>Update. This updates the selected cell, as well as all other cells with the same data type.

If you want to update all related data types and data connections that may be in the workbook (including queries, other data connections, and pivot tables), clickData>update allor pressCtrl+Alt+F5.

automatically when open

There is also a way to include VBA code to automatically update the file's inventory data each time you open your workbook. Just paste the following code inthese workbooksMicrosoft Excel Objects folder in the Visual Basic Editor (make sure to double-click ThisWorkbook to properly open its code page).

Private SubWorkbook_Open()
PURPOSE: Run the Update All function from the Data tab


MsgBox "Inventory data has been updated!"'Optional

movie Sub

Add stock quotes and real-time metrics to Excel (11)

There may be cases where you enter a company name and Excel may have multiple results for it. In that case, a blue question mark will appear next to your entry. You need to click on it to clarify which specific action you want to include in your spreadsheet.

Add stock quotes and real-time metrics to Excel (12)

After clicking the blue question mark icon, the data selection panel should appear on the right side of the window. Just scroll through the list of suggestions and select the stock symbol you want to track.

Add stock quotes and real-time metrics to Excel (13)

The information from the selected teletype must be automatically entered into your calculation sheet.

Add stock quotes and real-time metrics to Excel (14)

common cards

Bound data types also have a nice feature calledcards(I know, creative name right?!). Maps are displayed by clicking onBank-Symbolto the left of the action name. Maps can be a great way to answer ad hoc questions without having to bring that data into your spreadsheet.

Add stock quotes and real-time metrics to Excel (15)

More Action Related Links

  • Get historical stock data in Excel

  • How to extract cryptocurrency prices in Excel

  • VBA Guide to Update Data Connections

What's next for stocks?

Microsoft has taken a big step in the right direction by providing a highly requested feature that actually works very well. Next, Microsoft is working on adding the ability to pull historical information into our spreadsheets based on date ranges. As you can imagine, this is the skill that everyone really wants, as it gives you the potential to create comprehensive models and in-depth analysis tailored to your specific investment strategies. I hope this feature will be released soon!

Add stock quotes and real-time metrics to Excel (16)

About the Author

Hey! My name is Chris and I run TheSpreadsheetGuru website in my spare time. By day, I'm actually a finance professional who relies heavily on Microsoft Excel in the corporate world. I love sharing the things I'm learning in the "real world" with everyone here on this site, so you too can become a spreadsheet guru in your business.

My years in the corporate world have allowed me to seize opportunities to improve working with Excel and create countlessExcel add-ons, from inserting scale symbols to automatically copy/paste from Excel to PowerPoint. If you want to stay up to date with the latest Excel news and get the best Excel tips I've learned over the years straight to your email, you canSign up for my free newsletter. I hope I was able to offer you something of added value today and hope to see you around again soon!


Top Articles
Latest Posts
Article information

Author: Nicola Considine CPA

Last Updated: 03/18/2023

Views: 5881

Rating: 4.9 / 5 (49 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Nicola Considine CPA

Birthday: 1993-02-26

Address: 3809 Clinton Inlet, East Aleisha, UT 46318-2392

Phone: +2681424145499

Job: Government Technician

Hobby: Calligraphy, Lego building, Worldbuilding, Shooting, Bird watching, Shopping, Cooking

Introduction: My name is Nicola Considine CPA, I am a determined, witty, powerful, brainy, open, smiling, proud person who loves writing and wants to share my knowledge and understanding with you.