Mursalin Ibne Salehin
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.
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.
- In the second step, go to the Data tab and select the ‘More’ icon. It will open more data types.
- From the data types, select Stocks.
- 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.
- Now, click on the ‘Insert Data’ icon and select Price.
- As a result, you will see the stock prices of the companies.
- Similarly, you can follow the above steps to add another field to the dataset. We have added the Change (%).
- 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.
- 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.
- In the Data Selector box, delete the (XNAS:AMZN) part and click on the search icon.
- 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.
- After selecting the stock exchange, the data will also update.
- 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.
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)
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.
