How to Get Stock Quotes in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to get stock quotes in Excel. A stock quote represents the most recent price of a stock on an exchange. We can use 2 easy ways to get the stock quotes in Excel. Both methods apply to Excel 365 only. After reading the article, you will be able to extract the updated stock information easily. So, without further delay, let’s start the discussion.


Download Practice Book

Download the practice book here.


2 Easy Ways to Get Stock Quotes in Excel

To explain the methods, we will use a dataset that contains some company names. We will try to get the stock quotes of these companies in Excel.

Add Data Type to Get Stock Quotes in Excel


1. Add Data Type to Get Stock Quotes in Excel

In the first method, we will use the default data types feature of Excel. In Excel, you can convert text into a data type and extract necessary information from the internet. Here, we will convert our texts into Stocks data types. It is considered a linked data type because it gets information from an online source. However, the steps to get stock quotes are simple. Let’s follow them below to learn the process.

STEPS:

  • In the first place, you need to type the stock symbol or tickers in place of the company name. Each company has a unique ticker or stock symbol. Also, if you want to extract information using the company name, it can introduce errors in the results.
  • After typing the symbols or tickers, select them.

Add Data Type to Get Stock Quotes in Excel

  • In the second step, go to the Data tab and select the ‘More’ icon. It will open more data types.

Add Data Type to Get Stock Quotes in Excel

  • From the data types, select Stocks.

Add Data Type to Get Stock Quotes in Excel

  • Instantly, the symbols or tickers will convert into Stocks data type and you will see results like below. Here, XNAS represents the name of the stock market.

Add Data Type to Get Stock Quotes in Excel

  • Now, click on the ‘Insert Data’ icon and select Price.

Add Data Type to Get Stock Quotes in Excel

Add Data Type to Get Stock Quotes in Excel

  • Similarly, you can follow the above steps to add another field to the dataset. We have added the Change (%).

Add Data Type to Get Stock Quotes in Excel

  • One more thing, you can view all the available information about a company with a single click.
  • For that purpose, click on the ‘Show card’ icon or press the Ctrl + Shift + F5 keys together.

Add Data Type to Get Stock Quotes in Excel

  • In the following step, we will learn to get stock quotes from another stock exchange.
  • To do so, right-click on the stock symbol or ticker.
  • After that, select Data Type and select Change. It will open the Data Selector box on the right side of the screen.

Add Data Type to Get Stock Quotes in Excel

  • In the Data Selector box, delete the (XNAS:AMZN) part and click on the search icon.

Add Data Type to Get Stock Quotes in Excel

  • After the search, you will get new stock exchange markets.
  • Select your desired one to import the stock price. We have selected the London Stock Exchange here.

Add Data Type to Get Stock Quotes in Excel

  • After selecting the stock exchange, the data will also update.

Add Data Type to Get Stock Quotes in Excel

  • Finally, to get the latest stock quotes you need to refresh the dataset.
  • In order to do that, go to the Data tab and click on the Refresh button.

Add Data Type to Get Stock Quotes in Excel

Read More: How to Get Historical Data of NSE Stocks in Excel (2 Effective Ways)


2. Use Excel Formula to Obtain Stock Quotes

In the second method, we will use a formula to obtain stock quotes in Excel. To extract the last price of a stock, we need to use the STOCKHISTORY function. The STOCKHISTORY function gets the stock quote data of a symbol or ticker for a specific date. Unfortunately, only Excel 365 users can use this function. Let’s pay attention to the steps below to learn the technique.

STEPS:

  • First of all, type the tickers or symbols of the companies in the Symbol column.

  • Secondly, select Cell D5 and type the formula:
=STOCKHISTORY(C5,TODAY(),,2,0,1)

Use Excel Formula to Obtain Stock Quotes

Here, the STOCKHISTORY function displays today’s last close price of the stock. To enter today’s date, we have used the TODAY function. If you view the last price of 8-May-2021, then you need to type:

=STOCKHISTORY(C5,"8-May-2021",,2,0,1)
  • In the third step, press Enter to see the result.

  • After that, put the cursor on the bottom right side of Cell D5.
  • The mouse arrow will change into a plus (+) sign.

  • In the end, double-click there and see the last available close price of all stocks.

  • Sometimes, today’s close price may not be available. Then, the formula will return the #VALUE error.
  • To avoid that, you can use the formula below:
=LOOKUP(TODAY()+1,STOCKHISTORY(C8,TODAY()-7,TODAY()))

Here, we have used a combination of the LOOKUP, TODAY, and STOCKHISTORY functions. Here, the LOOKUP function retrieves information from the last available data from the stock history.

Read More: How to Download Historical Stock Data into Excel (with Easy Steps)


Get Stock Quotes in Excel 2019 or Earlier Versions

Unfortunately, you can’t use the above methods in Excel 2019 or earlier versions. Still, you can get the stock quotes in your Excel sheet by following some tricks.

STEPS:

  • Firstly, open Google Sheets in the browser and create a new spreadsheet.
  • After that, select a cell and type the formula:
=GOOGLEFINANCE(A5)
  • Press Enter to see the result.

Here, you need to insert the ticker or symbol inside the function.

  • In the next step, go to the File and select Download >> Microsoft Excel (.xlsx).

  • Finally, you can get the stock quotes in an Excel file after finishing the download. But you can’t update the data automatically because it has no connection to an online source.

Conclusion

In this article, we have demonstrated 2 easy methods to Get Stock Quotes in Excel. Here, we have used practical datasets to explain the process. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo