The persons who invest their money in the stock market have to always check the latest stock price to gain profit from them. As a result, they go to the stock exchange market to get the update. However, you can easily check the latest stock price of a company using Microsoft Excel from your home. In this article, we will demonstrate to you how to get live stock prices in Excel. If you are interested to know about the process, download our practice workbook and follow us.
How to Get Live Stock Prices in Excel: 4 Easy Methods
To demonstrate the approaches, we consider a dataset of 10 companies. The name of those companies is in the range of cells B5:B14.
1. Using Built-in Stocks Command
In this method, we will use Excel’s built-in Stocks command to get the live stock prices in Excel. Besides it, you can also get more information about the stock by using this feature. The steps of this process are given below:
📌 Steps:
- First of all, select the entire range of cells B5:B14.
- Then, in the Data tab, select Stocks from the Data Types group.
- You will see the companies’ name patterns will change, and it will get the complete name structure. If Excel fails to get any company like cell B10, select your desired company manually by clicking on the Select option from the Data Selector side window.
- Besides it, you will see a small widget pop-up icon will appear at the right corner of the selected names.
- If you click on it, you will get several fields listed there. We are going to add only the Price and Change(%) fields according to our desire.
- For that, click on the widget pop-up icon, scroll down in the Field list with your mouse and select the Price option.
- You will see the Price of all ten companies will add in the range of cells C5:C14.
- Similarly, add the Change(%) field in column D.
- To get the latest update, select the drop-down arrow of the Refresh All option and select the Refresh All or Refresh option.
- You will see the live stock prices and change rates for all the companies.
Finally, we can say that our method worked successfully, and we are able to get live stock prices.
Read More: How to Import Stock Prices into Excel from Yahoo Finance
2. Utilizing Stock Connector Add-in
We can get the live stock price update from Excel’s Add-ins. We will use the Stock Connector Add-ins to get live stock prices in Excel. The procedure is explained below step by step:
📌 Steps:
- To start the approach, go to the Developer tab and click on the Add-ins option from the Add-ins group. If you don’t have that, you have to enable the Developer tab.
- As a result, the Office Add-ins dialog box will appear on your device.
- Now, click on the Office Store option.
- Then, write down ‘stock’ in the Search Engine and click the Search button.
- You will find the Stock Connector. Otherwise, scroll down with your mouse to find it.
- Click Add from the right side of the Stock Connector.
- Another small dialog box will appear and click on Continue.
- Now, you will find the add-ins on the right side of the Home tab.
- Then, click on Launch.
- The Stock Connector will open in a side window on the right side of your screen.
- After that, click on cell B5 and you will see the cell value will show in the empty box.
- Click on Connect.
- There will be a suggestion list for the different branches of that company, Choose your desired one. We are choosing the first one from that sheet.
- As you click on that, in a few seconds, a small dialog box called Select Data will appear.
- Now, delete the selected cell showing in that box to preserve your organization’s name in the dataset and choose C5 with your mouse.
- Click OK.
- The stock price will show in cell C5 and the same information will also show in the add-in.
- After that, click on the Change (%).
- Again, the Select Data dialog box will appear. Delete the selected cell in that box and select cell D5.
- The Change (%) will store in cell D5.
- Similarly, follow the same process for the rest of the companies.
- At last, you will get all the stock prices in your dataset. Besides it, you can also see them in the side window of the Stock Connector.
- Format your dataset for a better outlook.
Thus, we can say that our method worked successfully, and we are able to get live stock prices in Excel.
Read More: How to Get Stock Quote with Excel Add-in
3. Applying STOCKHISTORY Function
In this approach, we will use the STOCKHISTORY and TODAY functions to get the live stock prices. To get the stock using this function, you have to know the proper institution stock symbol for that organization. As we used the Stock Connector Add-in in our previous method, we grabbed the company symbols from there and they are in the range of cells C5:C14. You can also look into Google for it. The steps of this method are given as follows:
📌 Steps:
- At first, we will use the TODAY function in cell G9 to get today’s date. For that, write down the following formula in the cell:
=TODAY()
- After that, write the formula in cell G6 to get the previous date:
=G9-1
- Now, use the STOCKHISTORY function in cell D5 and write down the formula as shown below. Ensure the Absolute Cell Reference for cells G6 and G9.
=STOCKHISTORY(C5,$G$6,$G$9,0,0)
- Press the Enter key.
- First, it will show #Busy! in cell D5.
- Then, after a few seconds, the last working date will appear in cell D5, and the stock price will show in cell E5.
- Next, drag the Fill Handle icon to copy the formula up to cell D14.
- You will get all the stock prices in your dataset.
Finally, we can claim that our formula worked successfully, and we are able to get the live stock price in Excel.
Read More: How to Import Stock Prices into Excel from Google Finance
4. Use of Power Query
The Power Query of Excel can help us to get the stock prices from any external website. In this process, we will show how to do it. The steps of this approach are given below:
📌 Steps:
- First, in the Data tab, select the From Web option from the Get & Transform Data group.
- A small dialog box titled From Web will appear.
- Then, choose the Basic option and paste the website link into the empty box. We are pasting a link from Yahoo Finance.
- Click OK. keep patience, it will take a while.
- Then, the Navigator window will appear in front of you.
- After that, select Table 0 and click on the Load. The panel will also show you a visual representation of that dataset.
- You will see that within a few seconds, the current stock price list will be in your Excel spreadsheet.
- Format the key columns to get a better outlook of your dataset.
At last, we can say that our process worked perfectly and we are able to get live stock prices in Excel.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to get live stock prices in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
