Excel can automatically pull and refresh stock prices using its built-in Stocks data type, which connects to online financial data and updates whenever you open the spreadsheet or click refresh. If you need historical prices, the STOCKHISTORY function pulls daily, weekly, or monthly data into a range of cells with a single formula. Here’s how to set up both approaches and keep your portfolio or watchlist current without manual entry.
Use the Built-In Stocks Data Type
The fastest way to get live stock prices into Excel is the Stocks data type, available to Microsoft 365 subscribers and free Microsoft account holders. You type a ticker symbol into a cell, convert it to a linked data type, and Excel connects that cell to an online financial data source. From there you can pull in the current price, market cap, 52-week high, P/E ratio, and other fields that update automatically.
To set it up, type your ticker symbols in a column (one per cell). Select the cells, then go to the Data tab and click “Stocks” in the Data Types group. Excel will recognize the symbols and display a small stock icon next to each one. If it can’t match a ticker, it will show a question mark so you can pick the correct company from a list.
Once your cells are converted, click any one of them and a small “Insert Data” icon appears to the right. Click it to choose which field you want (price, volume, previous close, etc.), and Excel places a formula in the adjacent cell that references that field. You can also type formulas directly. If cell A2 contains a converted stock, entering =A2.Price in cell B2 returns the current price.
To refresh the data, go to the Data tab and click “Refresh All,” or right-click any converted cell and choose “Refresh.” Excel also refreshes linked data types when you reopen the file. You need an active internet connection for any refresh to work. One requirement to keep in mind: your Office language preferences must include English, French, German, Italian, Spanish, or Portuguese for the Stocks data type to appear.
Pull Historical Prices With STOCKHISTORY
If you need more than just the latest quote, the STOCKHISTORY function returns a table of historical prices that spills across multiple cells. The basic syntax is:
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties])
At minimum, you supply a ticker symbol (as text in quotes or a cell reference) and a start date. For example, =STOCKHISTORY("MSFT", "1/1/2025") returns daily closing prices from January 1, 2025, through today. The results spill downward automatically, with dates in one column and prices in the next.
The interval argument controls the frequency: 0 for daily (the default), 1 for weekly, and 2 for monthly. So =STOCKHISTORY("AAPL", "1/1/2024", "12/31/2024", 2) gives you monthly prices for all of 2024. Setting the headers argument to 1 adds column labels at the top of the output.
You can also pull additional properties beyond the closing price. The function accepts up to six property arguments covering open, high, low, close, and volume. This makes it straightforward to build a price chart or volatility analysis entirely from one formula. Because the function pulls from the same online source as the Stocks data type, refreshing your workbook updates the historical data too.
Automate Refreshes on a Schedule
By default, Excel refreshes linked data when you open the file or manually click Refresh All. If you want prices to update on a timer while the workbook is open, you can use a connection property setting or a short VBA macro.
For external data connections (like those powering Power Query or web queries), go to Data, then Connections, select the connection, click Properties, and check “Refresh every X minutes.” This works well if you’re pulling data through Power Query (described below) but doesn’t directly apply to the Stocks data type.
For the Stocks data type, a simple VBA macro handles timed refreshes. Open the VBA editor (Alt + F11), insert a new module, and paste something like this:
Sub RefreshStocks()
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:05:00"), "RefreshStocks"
End Sub
Run the macro once, and it will refresh all data connections every five minutes. Change the TimeValue to whatever interval you prefer. Keep in mind that the underlying data source updates on a slight delay, so refreshing every few seconds won’t give you real-time tick data. For most portfolio tracking, refreshing every 5 to 15 minutes is plenty.
Use Power Query for More Control
Power Query, built into Excel under the “Get Data” menu on the Data tab, can pull stock prices from a web source and transform them into a clean table. This approach gives you more flexibility over formatting, filtering, and combining data from different sources.
The general process: go to Data, then Get Data, then From Web. Paste the URL of a financial data page that lists the prices you need (Yahoo Finance quote pages work for this). Power Query will detect the tables on that page and let you select which one to import. You can then rename columns, filter rows, change data types, and load the result into your worksheet.
The advantage of Power Query is the built-in refresh scheduling. After loading the data, go to Data, then Connections, select your query, click Properties, and set it to refresh at a fixed interval or every time the file opens. Power Query also handles errors more gracefully. If a web source changes its layout, you can edit the query steps without rebuilding your entire spreadsheet.
Third-Party Add-Ins for Advanced Data
Excel’s native tools cover basic price data well, but if you need real-time streaming quotes, options chains, fundamental data, or coverage of smaller exchanges, a third-party add-in may be worth it. The landscape ranges from free to enterprise-priced:
- Bloomberg Terminal and Refinitiv Eikon are the professional standard, offering real-time and historical data across global markets with deep Excel integration. Both are expensive (thousands per year) and aimed at institutional users.
- FactSet and S&P Capital IQ provide extensive financial data with Excel plug-ins that let you pull company fundamentals, estimates, and market data directly into cells. These are also subscription products geared toward analysts.
- Morningstar Direct and YCharts focus on investment research, including mutual fund and ETF analytics alongside stock data.
- FRED (Federal Reserve Economic Data) offers a free Excel add-in for economic indicators like interest rates, GDP, and inflation. It won’t give you individual stock prices, but it’s useful for macro context alongside your portfolio data.
For individual investors tracking a personal portfolio, the built-in Stocks data type and STOCKHISTORY function usually cover everything needed. Add-ins become valuable when you need deeper fundamentals, broader asset class coverage, or true real-time feeds.
Troubleshooting Common Issues
If the Stocks option doesn’t appear on your Data tab, confirm you have a Microsoft 365 subscription or a free Microsoft account and that one of the supported editing languages is enabled in your Office language settings. The feature is not available in older perpetual-license versions of Excel (like Office 2019 or earlier).
When a ticker returns a question mark instead of converting, it usually means Excel can’t match the text to a known security. Try using the full company name instead of the ticker, or click the question mark to search manually. For stocks on non-U.S. exchanges, you may need to include the exchange code (like “LON:BATS” for a London-listed stock).
STOCKHISTORY errors often come from date formatting. Make sure your start and end dates are recognized as dates by Excel, not stored as plain text. If the formula returns a #VALUE! error, try wrapping dates in the DATE function, like DATE(2025,1,1), instead of typing them as strings.
If your refresh stops working, check your internet connection first. Linked data types require an active connection every time they update. Workbooks saved to a local drive with no connectivity will display the last cached values until you reconnect and refresh.