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.

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

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

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

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

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

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.

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

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

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

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

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

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

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

• Format your dataset for a better view.

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:

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

• Use the STOCKHISTORY function in cell D5 by entering the formula as shown below. Ensure the Absolute Cell Reference for cells G6 and G9.

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

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

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.

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

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

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