Get real-time stock data in Excel: A Rich Data Types guide

In today's digital age, Artificial Intelligence (AI) is revolutionizing the way we handle data. Excel integrates AI into its functionality to help recognize rich data types beyond numbers and text strings, particularly in tracking real-time information such as stock data. Previously, acquiring stock data required manual entry from sources like financial websites or printed materials, which was time-consuming and error prone. Now, real-time stock data can be easily retrieved directly in Excel. This guide will demonstrate how to use the Rich Data Types in Excel to ensure your financial analysis stays up-to-date with the latest market trends.

• Correcting missing data types
• Changing the data types
• Updating the data types
• Discovering more information with cards

Overview of Excel Rich Data Types

With added AI capabilities, rich data types in Excel go beyond the traditional text and numbers. They allow you to bring in a wealth of structured data about various entities. For example, when you enter a company's ticker symbol into a cell and convert it to the Stock data type, Excel fetches a variety of related information, such as stock prices, company names, and more.

What linked data types are available in Excel?

To know what linked data types are available in Excel, go to the "Data" tab in Excel, then you can find the linked data types in the "Data Types" group.

There are 4 data types in the Data Types group, including "Stocks", "Currencies", "Geography" and "Organization". And each one has its associated data types. To know more, visit this page: List of linked data types in Excel.

In this tutorial, I will illustrate how to use the Stocks data type to get real-time stock data.

Accessing Real-Time Stock Data with Rich Data Types

Suppose you have a list of company ticker symbols, as shown in the screenshot below, and want to get the relevant real-time stock information for each of these ticker symbols. This section will demonstrate how to accomplish this task with the Stocks data type in Excel.

Step 1. Convert the original data range to Table

Select the range containing the ticker symbols (in this case, I select the range A1:A9) and press the "Ctrl" + "T" keys. The "Create Table" dialog box will open. You need to check if the cell references are correct and whether your selected range has headers, then click "OK".

Step 2: Convert the ticker symbols to stock data types

Select the original range, go to the "Data" tab, and select "Stocks" in the "Data Types" group.

The selected ticker symbols are converted to stocks data type (a house icon displays before the texts). See screenshot:

Step 3: Add related information for each ticker symbol

Then you can add real-time information for each ticker symbol. Here I will demonstrate two ways for you.

Method 1: Add fields with the Add Columns button

Click on any cell within the data type, and the "Add Column" button will appear. And then you need to click this button to show the available fields of data. Click on the field name to extract the information of that field. In this case, I select the "Price" field.

Then the Price field is added to the stock data type. You can repeat this step to add more fields as you need.

Method 2: Add fields with formulas

If you are familiar with these fields and their corresponding formulas, you can easily add fields using formulas without having to use the Add Column button.

Take the above stocks data type as an example, to add the Price field to this data type, you can do as follows.

1. Select a cell to output the field (here I select cell B2), enter the following formula and press the "Enter" key.

   =A2.Price

   
2. Select the result cell and double click the Fill Handle (the green square in the lower right corner of the cell) to populate the current column with the same field.
   

Explore More Options

This section demonstrates additional options that may be used when getting real-time stock data in Excel, including correcting missing data types, changing data types, updating date types, and using Cards to discover more information of the data type.

Correcting Missing Data Types

If Excel does not recognize a ticker, it may fail to convert it into a stock data type. As shown in the screenshot below, Excel does not recognize "Mikecro Soft", so it displays a question mark before the text. This section will help you correct the missing data type step by step.

1. Select the cell containing the missing data type, and the "Data Selector" pane will be displayed automatically.
   
2. In the "Data Selector" pane, you need to do as follow.
   1. In such cases, you need to make sure that the ticker symbol is correct. So, try entering the correct ticker symbol in the text box. Here, I changed "Mikecro Soft" to "Microsoft" and then press "Enter" to search for matching results.
   2. You will then get a list of search results. Select a matching result by clicking on the "Select" button under it.
      Tip: You can click on the card above the "Select" button to view the details of this data type.
      

Result

The missing data type has now been corrected. See screenshot:

Changing the Data Types

If the data type converted by Excel does not meet your needs, for example, as shown in the screenshot below, the data type in cell A2 is not what you need, you can change it manually as follows.

1. Right click on the cell you want to change the data type, select "Data Type" > "Change".
   
2. In the "Data Selector" pane, retype the ticker symbol and press the "Enter" key. Then select the desired data type from the list.
   

Updating the Data Types

The linked data type connects to an online data source. Once you convert text to a linked data type, an external data connection is established in the workbook. If the online data changes, you will need to manually update the data to get the latest data. This section describes how to update data types in Excel.

There are three methods you can use to manually update the data types. Click on any cell of the data types, then you need to:

• Right click the selected cell and then select "Refresh" from the context menu.
  
• Go to the "Data" tab, click "Refresh" > "Refresh All" or "Refresh".
  
• Use shortcut keys:
  1. Press "Alt" + "F5" to refresh the cell you selected, as well as other cells with the same data type.
  2. Press "Ctrl" + "Alt" + "F5" to refresh all sources in the current workbook.

Discovering More Information with Cards

After you convert text into a certain data type, an icon will appear before the text. Clicking the icon will open a card with more detailed information about the data type. Let’s see what we can do with the card.

Click the icon of the data type to open the card. See screenshot:

In the card, you can:

• View all fields and corresponding values of the data type.
• Add desired fields from the card by hovering the cursor over the field and then click the "Extract X to grid" (X here represents the field name).
  
• Know where the fields and values come from by scrolling down inside the card and see the “Powered by” note at the bottom of the card.
  

Getting Historical Stock Data

The stock data type doesn’t provide historical data. Sometimes, for some purpose, you may need to get historical stock data. This section will briefly describe how to use the STOCKHISTORY function to get historical stock data in Excel for a specific data range.

Syntax of the STOCKHISTORY function

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Arguments

• Stock (required): A ticker symbol in double quotes, such as "MARA", "JMIA".
• Start_date (required): The start date of the data to be retrieved.
• End_date (optional): The end date of the data to be retrieved. Default is the start_date.
• Interval (optional): The time interval.
  • 0 (default) = Daily
  • 1 = weekly
  • 2 = monthly
• Headers (optional): Specify whether to display headers.
  • 0 = No header
  • 1 (default) = show header
  • 2 = show instrument + header
• Properties (optional): Additional data to retrieve.
  • 0 (default) = Date
  • 1 (default) = Close
  • 2 = Open
  • 3 = High
  • 4 = Low
  • 5 = Volume

Here I will use this function to get the close price of given companies on December 20, 2022.

Select a cell (D2 in this case) next to the original stock list (or select any blank cell you need), enter the following formula and press the "Enter" key.

=STOCKHISTORY(A2,DATE(2022,12,20),,,0)

Select this formula cell and drag its "Fill Handle" down to get the rest of results.

FAQs on Utilizing Excel Rich Data Types

Related Articles