Here’s a small sample dataset that demonstrates how you can extract stock prices in Excel.
Method 1 – Combining STOCKHISTORY and TODAY Functions in Excel
Steps
- Input the stock symbol or the company ticker symbols in Excel.
- Enter the symbol AAPL in cell D5 for the Apple corporation.
- Enter the symbol MSFT and SONY from Microsoft and Sony corporations, respectively, in cells D6 and D7.
- Enter the following formula in the cell E5:
=STOCKHISTORY(D5,TODAY(),,2,0,1)
- Drag the Fill Handle icon to cell E7.
Breakdown of the Formula
TODAY(): Returns today’s date.
STOCKHISTORY(D5,TODAY(),,2,0,1): This function returns the history of stocks, from a specific opening date to another specific closing date. If one single date is used, today’s date, it returns only that day’s stock price.
Note:
- You can only get the price information of the Stock data.
- Need to be connected online in order to fetch data.
- This option is only available in Excel 365 or the online version of Excel.
Method 2 – Get Stock Prices by Utilizing the Built-in Stocks Command
Steps
- Select the company name and go to the Data tab.
- Click on the Stocks icon.
- All of the company names now change to their official full form with the Stock symbol or the Ticker symbol with them.
- The company may be listed in multiple exchanges in different parts of the world at once. Excel will open a sliding side menu in that case.
- Select the stock exchange you intend to use. We chose NYC here for example.
- The range of cells is now filled with the company’s name with the appropriate ticker symbol.
- There is also an Add-column icon on the top corner of the cell. You can use this to get various stock information about different companies.
- Click on the add column sign.
- Click on Price.
- You’ll get a new column to the right with the updated value of all stocks presented.
- Click on the add column sign and select Change.
- You’ll get a new column with a percentage change of stock values.
- Click on the add column and select the Beta option.
- You’ll get the information about the stock’s beta value.
- Clicking the stock icon on the left of each row in the company column provides more information.
- If you want to refresh the stock data, go to the Data tab and click the Refresh All command.
How to Choose a Different Stock Exchange
- Select the cells of which companies exchange you want to alter, then right-click and select Data Type.
- Click on Change.
- In the options panel, delete the ticker symbol and the identifier at the end of the Company Name.
- Click the search icon.
- Choose an exchange and click on Select.
- Here’s the result.
Note:
Beta value denotes the volatility of a stock. If a stocks’ beta value is above 1, then the stock is more volatile.
Read More: How to Import Stock Prices into Excel from Yahoo Finance
Method 3 – Using the Stock Connector Add-in to Get Stock Prices
Steps
- Input the company names in the range of cell B5:B7.
- From the Insert tab, click on the Get Add-ins icon on the Add-ins group.
- You will be taken to the Office Add-ins store.
- Search for Stock.
- In the search result, look for Stock Connector.
- Click on the Add button on the corner of the Stock Connector add-in.
- The stock connector add-in is now available on your Excel sheet in the Home tab.
- Copy the contents of cells B5:B7 to cells C5:C7.
- In the Home tab, click on the Stock Connector Launch icon.
- The Stock Connector will launch in the side panel.
- Select the company name cells in the worksheet.
- Click on Connect.
- You will be asked where would be the output value will be placed.
- Select cell C5 and click OK.
- The stock price of Microsoft Corporation is now showing in cell C5.
- Repeat the same process for other companies.
- For some companies, the add-ins may ask you which stock exchange value of the selected company you want to show.
- For example, in our case, Apple Inc is listing multiple stock exchanges. We selected the US stock exchange.
- We have the stock prices of different companies in the range of cells C5:C7.
- The values will update every 30 seconds automatically in the cells.
- In the sliding panel, we can also see the percentage of their stock price increase.
Download the Practice Workbook
Related Articles
- How to Get the Current Stock Price of India in Excel
- How to Import Stock Prices into Excel from Google Finance
<< Go Back to Stocks In Excel| Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Please help me find the following funds Stock data link
INVESTEC WORLD AXIS PCC INVESTEC CORE FUND IWCF
Satrix MSCI World ETF SWIX
Satrix RESI ETF SATRIXRESI
Coronation Global Capital Plus [ZAR] Feeder CPLSZ
Ninety One Equity Fund NINEQ
No amount of permutations work
Rob Davidowitz
Greetings Rob,
Thanks a lot for commenting on this article. Unfortunately, we managed to find only the last three companies and the INVESTEC CORE FUND IWCF among the companies you metioned in the database. Rest of them are not enlisted in the database. I have given the dataset link containing the stock informations of the Satrix RESI ETF SATRIXRESI, Coronation Global Capital Plus [ZAR] Feeder CPLSZ, Ninety One Equity Fund NINEQ, INVESTEC CORE FUND IWCF etc.
Thanks and Regards
Rubayed Razib
Dataset Link:https://www.exceldemy.com/wp-content/uploads/2023/01/Get-Stock-Prices-in-Excel.xlsx
Why would a NYSE ticker (SNOW for Snowflake) not be included in the excel database? Is there a way to get around this?
Hello JZ,
NYSE ticker SNOW for Snowflake is included in Excel database.
To get the data follow the steps below.
Insert SNOW in any cell then click on Stocks from Data tab.
You will get all the data by selecting the fields.
Regards
ExcelDemy
There is a HUGE problem with the data feed for the Excel Stock tool. I want to look up prices, etc. for some very commonly traded ETFs on US exchanges, but often I get a quote from some small company on a foreign exchange.
For example – try using the symbol TLT for the ETF iShares 20+ Year Treasury Bond ETF. The Stock function returns data for THERALASE TECHNOLOGIES INC. (XTSX:TLT). Or try VTV for the Vanguard Value ETF and instead you get VTV THERAPEUTICS INC. (XNAS:VTVT).
Is there anyway to override this and have it find the US traded ETFs under the specified symbols? If not this function is basically useless.
Hello Mark Muntean,
Thank you for bringing this up! The issue you’re encountering with the Stock data type misidentifying certain ticker symbols is a known limitation. Excel’s Stock tool relies on Microsoft’s data feed, which sometimes matches symbols to incorrect entries, especially when multiple assets share similar tickers across global exchanges.
To work around this, you can:
1. Specify the full name of the ETF or company (e.g., “iShares 20+ Year Treasury Bond ETF” instead of “TLT”) in the cell before converting it to the Stock data type.
2.If that doesn’t resolve the issue, you can manually edit the linked data source:
2.1. Right-click the cell containing the Stock data.
2.2. Select Data Type >> Change and choose the correct entry from the list.
3. For a more reliable solution, consider using a dedicated financial add-in like “Stock Connector,” which allows you to pull data directly from US exchanges using specified symbols.
We understand this limitation can be frustrating, and we hope these tips help improve your experience!
Regards
ExcelDemy
I’ve been using method 2 in Excel 365 for a while now, but I’m wondering, which of the non-subscription versions of Excel support these methods?
Hello Bernhard K.,
Thank you for your comment! Method 2, which uses the built-in Stocks data type, is available in Excel 2019 and later versions. However, please note that some features may be limited compared to Excel 365, which receives regular updates.
Regards
ExcelDemy