How to Get Live Stock Prices in Excel: 4 Easy Methods

To demonstrate how to get live stock prices in Excel, we’re creating a dataset of 10 companies in our example. The names of those companies is in the range of cells B5:B14.


Method 1 – Using Built-in Stocks Command to Enter Live Stock Prices in Excel

This feature also allows you to gather more information about the stock.

Steps:

  • Select the entire range of cells B5:B14.
  • 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 change, showing 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

  • Beside it you will see a small widget pop-up icon appear at the right corner of the selected names.

  • Click on the icon to see several fields listed there. We are going to add only the Price and Change(%) fields.  You can add whatever fields you desire.
  • To add the Price and Change fields, 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 appear in the range of cells C5:C14.

Using Built-in Stocks Command to Get Live Stock Prices

  • Add the Change(%) field in column D in the same way.

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.

Read More: How to Import Stock Prices into Excel from Yahoo Finance


Method 2 – Using the Stock Connector Add-in to Get Live Stock Prices in Excel

Steps:

  • Go to the Developer tab and click on the Add-ins option from the Add-ins group. If you don’t have the Developer Tab, you will need to enable it.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • The Office Add-ins dialog box will appear on your device.
  • Click on the Office Store option.

  • Enter ‘stock’ in the Search Engine and click the Search button.
  • If Stock Connector does not appear, 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

  • Click Continue in the small dialog box that appears next.

  • Add-ins will appear on the right side of the Home tab.
  • 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.

  • Click on cell B5 and you will see the cell value appear in the empty box.
  • Click on Connect.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • You will see a suggestion list for the different divisions of that company. Choose the first one from that list.

Inserting Stock Connector Add-ins to Get Live Stock Prices

  • After a few seconds a small dialog box called Select Data will appear.
  • Delete the selected cell showing in that box to preserve your organization’s name in the dataset and choose cell C5.
  • 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

  • Click on the Change (%).
  • The Select Data dialog box will appear again. 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

  • Follow the same process for the rest of the companies.
  • You will get all the stock prices in your dataset. You will also be able to 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 view.

Inserting Stock Connector Add-ins to Get Live Stock Prices


Method 3 – Applying the STOCKHISTORY Function to Get Live Stock Prices in Excel

To get the stock prices using this function, you have to know the proper institution stock symbol for that organization. Since 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 Google that information.

Steps:

  • First, we will use the TODAY function in cell G9 to get today’s date. Enter the following formula in the cell:

=TODAY()

Applying STOCKHISTORY Function to Get Live Stock Prices

  • Enter the formula in cell G6 to get the previous date:

=G9-1

Applying STOCKHISTORY Function to Get Live Stock Prices

  • Use the STOCKHISTORY function in cell D5 by entering 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.
  • It will initially show #Busy! in cell D5.

  • 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

  • Drag the Fill Handle icon to copy the formula through cell D14.
  • You will get all the stock prices in your dataset.

Applying STOCKHISTORY Function to Get Live Stock Prices

 


Method 4 – Using the Power Query to Get Live Stock Prices in Excel

Steps:

  • 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.
  • Choose the Basic option and paste the website link into the empty box. We are pasting a link from Yahoo Finance.
  • Click OK. Be patient as it will take a while.

  • The Navigator window will appear.
  • Select Table 0 and click 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 appear in your Excel spreadsheet.

  • Format the key columns to get a better view of your dataset.

Use of Power Query to Get Live Stock Prices

 


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


 

<< Go Back to Stocks In Excel| Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

2 Comments
  1. Thank you for the great work.
    For the section “Use of Power Query,” how would I connect to my Yahoo Finance watchlist?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 25, 2024 at 11:58 AM

      Hi ED,

      Thank you for your query. Unfortunately, directly importing your Yahoo Finance watchlist into Excel for stock details retrieval is not feasible due to technical limitations.

      Reason:

      Yahoo Finance watchlists are dynamic web pages primarily rendered by JavaScript, while Excel’s Data > From Web feature captures static HTML snapshots. This fundamental difference prevents Excel from capturing the interactive and user-specific nature of watchlists.

      As an alternative solution, I recommend downloading the template from this link. Add your company names from the watchlist to this template. This template facilitates tracking and decision-making for your listed stocks within Excel.

      We trust that this solution aligns with your interests and needs.

      Best regards,

      Ishrak Khan

      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo