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.
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.
- 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.
- Now, press OK.
- Next, select the range C5:C8.
- After that, select Data ➤ Stocks.
- Consequently, you’ll see the conversion of the symbols (C5:C8) into the stock data.
- See the following picture to have a better understanding.
- 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.
- As a result, it’ll return the price (in the next column) of the companies in a particle stock exchange market.
- Moreover, click the Show card icon (marked in a red square box) and detailed information about that particular company will emerge.
- 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.
- Select cell D5 at first.
- Then, type the formula:
- Press Enter.
- At last, use the AutoFill tool to complete the rest of the list.
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.
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.