ExcelInsider Team
If you are an investor, you might be interested in getting the updated stock prices regularly. In Microsoft Excel, there are numerous methods you can use to check the live stock prices. People who are working at a stockbroker agency, or in the financial department of a company, will find it helpful to know how to get live stock prices in excel.
➤ Write the stock names in a column.
➤ Select the names and go to the Data tab.
➤ Click on Stocks from the Data Types group.
➤ After Excel fills in the company names, click on Insert Data to add your desired stock information to the sheet.
➤ Click Refresh All from the Queries & Connections section of the Data tab to refresh the live data.
That is not the only way of getting livestock prices in excel. There are other methods, and each one has its own limitations. In this article, we will learn all the ways you can get the live stock prices in Excel. Therefore, stick around and read the whole tutorial to learn everything there is to know about the topic.
Using the Excel Data Conversion
In a spreadsheet program like Excel, Microsoft surely knew that people would be working with stock prices. That is why there is a dedicated option in Microsoft Excel to get the stock prices. However, this option uses Bing search to look for the stock prices and other information.
In order to use an online option like Bing search, you must sign in to your Microsoft account. If you are not signed in, the option will not even show up in Microsoft Excel. In case you don’t have an account, sign up for one or ask your organization if you are working in an office setting.
For this tutorial, we have a list of 10 companies we want to find the stock information about. We have written the ticker symbols in the spreadsheet; however, you can use full names if you want. Let’s see how we find the livestock prices for these companies:
➤ Select the company names/ticker symbols. Here, we are selecting A2:A11 cells because those are the cells where we put the names.
➤ Go to the Data tab and find Stocks in the Data Types section.
➤ After clicking that button, Excel automatically replaces the symbols with actual company names. The names, however, flowed out of the cells. Let’s adjust that first by resizing the column.
➤ The cells are still selected here, but if they’re not in your computer, select them again. Now, click on the small icon that says Insert Data when the mouse cursor is hovered over it.
➤ A big context menu will appear. Scroll down and select Price.
➤ Now the livestock prices are visible on the sheet in column B. We are going to give it a heading named Price and add borders to the other cells.
➤ If you need to update the stock prices to live, press Ctrl + Alt + F5 , or go to the Data tab, and select Refresh All from the Queries & Connections group.
Getting Live Stock Prices from the Web
If you have a favorite website where you check the stock prices, you can import data from that website to excel as well. We will be using the same Data tab as before, but we will import the data from a website this time instead of inserting company names. Follow the steps below:
➤ Go to the Data tab and select the From Web icon from the Get & Transform Data section. Here is an image for you if you cannot recognize the icon.
➤ A new dialog box will open. Insert your link in the edit box. We are using https://www.wsj.com/market-data/stocks . Press OK afterwards.
➤ If this is your first time importing data from the web in Microsoft Excel, the following window will pop up. Just click Connect to continue. If this window does not show up, do not worry, it is ok.
➤ Excel will show the tables that you can import from the web page. In this example, we can see that the first table is what we want to import. You might want to check if there are other tables you would rather have. After you make up your mind, select Load.
➤ Excel will load the data in the worksheet. If you want to refresh the live prices, right-click on the data and select Refresh.
Making Use of the Stock Connector Add-In
Instead of using the functions readily available in excel, you can use an add-in to get the live stock prices as well. There are numerous add-ins for excel, but we will be talking about the Stock Connector add-in for this tutorial. If you use this, you don’t have to refresh manually. Instead, you can set a timer by which the stock prices will automatically be refreshed. Here is how to do that:
➤ From the Home tab, select Add-Ins.
➤ Search for Stock Connector and select Add.
➤ A panel will open on the right for the Stock Connector. We will be looking for the stock prices of Microsoft. Write MSFT and hit Connect.
➤ A small pop-up box will ask you to select the cell where you want the stock price to show up. Select any cell you want. We will be selecting B2 for this example.
➤ Now the stock price will show up in the B2 cell. You can change the refresh timer using the up and down arrows in the stock connector panel.
➤ For the next time, you can launch the Stock Connector Panel from the Home tab.
Using VBA and the Alpha Vantage API
This method is a bit complex, but if you take the time to learn, it will be worth it. We will use the Alpha Vantage API to get stock prices in Microsoft Excel. Follow the steps below carefully:
➤ Go to https://www.alphavantage.co/ to get an API key from Alpha Vantage.
➤ Fill in the required information, and you will receive a key.
➤ Alpha Vantage provides data in JSON format. Excel cannot parse JSON directly, so we need to use the VBA-JSON library for that. Download it from this link: https://github.com/VBA-tools/VBA-JSON/releases
➤ Right-click on the downloaded zip (Shift+Right-click if you are on Windows 11), and select Extract All…
➤ Extract the files by clicking Extract
➤ Go to your spreadsheet and press Alt + F11 to open the VBA IDE.
➤ Press Ctrl + M and select JsonConverter.bas from the extracted folder.
➤ Go to Tools > References to add a new reference to VBA.
➤ Check the box called Microsoft XML, v6.0. If you cannot find the box, scroll down a little bit; the names are sorted alphabetically. Press OK to continue.
➤ Now go to Insert > Module, and write this code:
Sub GetStockPrice()
Dim xml As New MSXML2.XMLHTTP60
Dim url As String
Dim stockSymbol As String
Dim stockPrice As String
Dim apiKey As String
Dim jsonData As Object
stockSymbol = "MSFT"
apiKey = "API_KEY"
url = "https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=" & stockSymbol & "&apikey=" & apiKey
xml.Open "GET", url, False
xml.Send
Dim jsonString As String
jsonString = xml.responseText
Dim json As Object
Set json = JsonConverter.ParseJson(jsonString)
stockPrice = json("Global Quote")("05. price")
Range("B2").Value = stockPrice
End Sub➤ Replace API_KEY with your API key, B2 with the cell where you want the price to show up, and MSFT with the tick symbol of the company you want to know the prices of.
➤ Go to Run > Run Sub/UserForm to run the code.
➤ Go back to your spreadsheet to see the results.
Frequently Asked Questions
How to fetch live data in Excel?
Head to the Data tab and select Get Data from the Get & Transform Data section. After you open the drop-down menu, select From Other Sources > From Web. You can insert a web page link and import the live data from that site. If you have an older version of Microsoft Excel, the option might be called Get External Data or Import External Data instead.
How to get live stock prices in sheet?
Google sheets provides a function called GOOGLEFINANCE to get the live stock prices. Here is how you write the formula:
=GOOGLEFINANCE(“NASDAQ:MSFT”, “price”)
Here, MSFT is the ticker of Microsoft Corporation that we are using to pull the prices from NASDAQ. You can change price to some attribute if you want other attributes of the stock.
Can Excel update in real time?
It depends on what you want to update in Excel. If you have a form that puts the results in an Excel sheet, you can use Power Automate to add real-time responses to your Excel workbook.
Is GOOGLEFINANCE realtime?
Yes, but also no. The GOOGLEFINANCE function tries to import real-time data into Google Sheets. However, sometimes the data can be around 15 to 20 minutes late. The prices are never exactly real-time when imported using the GOOGLEFINANCE function in Google Sheets.
How to use Googlefinance function in Excel?
You cannot. Only Google Sheets can make use of the GOOGLEFINANCE function. Excel has other functions that can be used to import stock data into the spreadsheet. However, you can create your own function in VBA and name it GOOGLEFINANCE to use it in Excel.
Wrapping Up
In this article, we have learned how to get live stock prices in excel. We tried our best to add all the methods you can use to do this operation without breaking your bank. If you like this tutorial, you can show your appreciation by writing a comment below. Don’t forget to download the workbook we used; it’s free.
