How to Get Live Stock Prices in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Using Built-in Stocks Command to Get Live Stock Prices

  • 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.

Using Built-in Stocks Command to Get Live Stock Prices

  • 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.

Using Built-in Stocks Command to Get Live Stock Prices

  • You will see the Price of all ten companies will add in the range of cells C5:C14.

Using Built-in Stocks Command to Get Live Stock Prices

  • Similarly, add the Change(%) field in column D.

Using Built-in Stocks Command to Get Live Stock Prices

  • To get the latest update, select the drop-down arrow of the Refresh All option and select the Refresh All or Refresh option.

Using Built-in Stocks Command to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • 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.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • Format your dataset for a better outlook.

Inserting Stock Connector Add-ins to Get Live Stock Prices

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()

Applying STOCKHISTORY Function to Get Live Stock Prices

  • After that, write the formula in cell G6 to get the previous date:

=G9-1

Applying STOCKHISTORY Function to Get Live Stock Prices

  • 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.

Applying STOCKHISTORY Function to Get Live Stock Prices

  • Next, drag the Fill Handle icon to copy the formula up to cell D14.
  • You will get all the stock prices in your dataset.

Applying STOCKHISTORY Function to Get Live Stock Prices

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.

Use of Power Query to Get Live Stock Prices

  • 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.

Use of Power Query to Get Live Stock Prices

  • 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.

Use of Power Query to Get Live Stock Prices

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!

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo