How to Add Stock Data Type in Excel (2 Effective Methods)

Get FREE Advanced Excel Exercises with Solutions!

We store numerous data and perform various operations in an Excel worksheet. We can also retrieve important information from different Excel workbooks or any other sources by applying some links. Such an example is the Stocks Data Type. If you want to know about the stock information of any company, you can easily do it in Excel. For that purpose, you’ve to add the stock data type first and have an internet connection for fetching data. In this article, we’ll show you the effective methods to Add Stock Data Type in Excel.

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents multiple Companies, and their Stock Symbols (Ticker). Here, we’ll show how to add the Stocks Data Type in Excel and also how to get the price info.

add stock data type in excel


1. Adding Stock Data Type Through Built-in Stocks Data Feature in Excel

The easiest process to add the Stocks Data Type is to use the built-in Stocks Data feature. Therefore, follow the steps below to carry out the operation.

STEPS:

  • Firstly, go to File ➤ Options.
  • As a result, the Excel Options dialog box will pop out.
  • Then, add the Data Types from the Data section in the Customize Ribbon tab.

Add Stock Data Type Through Built-in Stocks Data Feature

  • Now, press OK.
  • Next, select the range C5:C8.
  • After that, select Data ➤ Stocks.

Add Stock Data Type Through Built-in Stocks Data Feature

  • Consequently, you’ll see the conversion of the symbols (C5:C8) into the stock data.
  • See the following picture to have a better understanding.

Add Stock Data Type Through Built-in Stocks Data Feature

  • Again, select the range C5:C8 and you’ll get an Insert Data icon as shown below.
  • Select the icon and choose any of your required fields.
  • In this example, select Price.

Add Stock Data Type Through Built-in Stocks Data Feature

  • As a result, it’ll return the price (in the next column) of the companies in a particle stock exchange market.

Add Stock Data Type Through Built-in Stocks Data Feature

  • Moreover, click the Show card icon (marked in a red square box) and detailed information about that particular company will emerge.

Add Stock Data Type Through Built-in Stocks Data Feature

  • However, to see the price in a different stock exchange market, select the symbol and right-click on the mouse.
  • Then, select Data Type ➤ Change.

  • Thus, you’ll see the Data Selector pane on the right side.
  • In the first box, delete everything inside the first bracket including the bracket (this will leave with the official name of the company only), and click the search icon.
  • Subsequently, press the Select button for your desired exchange. Here, select London Stock Exchange.

  • Hence, you’ll see the updated Price.

  • Lastly, to refresh the data for getting the updated info, click Data ➤ Refresh All.


2. Inserting Formula to Add Stock Data Type in Excel

Additionally, we can Add Stock Data in Excel using the STOCKHISTIRY function. This function can retrieve historical quote data of a company for a specific date range. So, learn the following steps to perform the task.

STEPS:

  • Select cell D5 at first.
  • Then, type the formula:
=STOCKHISTORY(C5,"23-May-2022",,1,0,1)
  • Press Enter.

Insert Formula for Adding Stock Data Type in Excel

  • At last, use the AutoFill tool to complete the rest of the list.

Read More:  How to Download Historical Stock Data into Excel


Things to Remember

  • For now, the Stocks Data Type feature is available only in Excel 365. The other versions of Excel will get this feature gradually.
  • Make sure you have the latest version of MS Office or have updated it for the latest version. The older versions will have a problem seeing this feature.
  • You have to set English as the Editing Language in Excel. Otherwise, you may not get the Stocks Data Type feature.

Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Add Stock Data Type in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Stocks In Excel| Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo