Track real-time or historical stock prices in Excel to enhance your financial analysis. This helps with investment strategies and portfolio tracking. Excel has built-in data types and connections. These features let you easily get the latest stock data in your spreadsheet. This guide shows four ways to get stock prices in Excel. We’ll cover built-in tools and external APIs, with step-by-step explanations. This guide is ensuring reliable, actionable information for every level of Excel user.
How to Get Stock Prices in Excel?
Using Excel’s Built-in “Stocks” Data Type (Best for Simplicity)
Excel for Microsoft 365 has a strong “Stocks” data type. It connects directly to Microsoft’s data provider. This provider is powered by Refinitiv or other financial sources.
Step-by-step:
- Type a company name or stock ticker (e.g., AAPL, GOOG) into a cell.
- Select the cell, go to the Data tab, and click on Stocks under the “Data Types” group.
- Excel converts the cell into a rich data type linked to live financial data.
- Click the small icon in the top-right corner of the converted cell. This will show a list of available fields, like price, market cap, and P/E ratio.
- Use the Insert Data button to pull the specific field into an adjacent cell.
Example Formula:
=A2.Price
Pros:
- Easy setup
- Updates automatically
- Built-in formatting
Limitations:
- Only available in Excel 365
- No control over data provider source
- Limited customization
Using Power Query and Web Sources (Great for Advanced Users)
Power Query lets you get stock prices from sites like Yahoo Finance and MarketWatch. You can do this by scraping tables or using APIs.
How to use Power Query to get stock prices:
- Open Excel and go to Data > Get & Transform Data > From Web.
- Paste a link to a financial data source.
- Click “OK” and let Power Query detect tables.
- Select the table containing the price (usually marked as “summary table” or similar).
- Clean the table (remove unnecessary columns) using Power Query Editor.
- Click “Close & Load” to add it to your worksheet.
Pro Tip: Use dynamic web URLs in Power Query to automate the pull for multiple tickers.
Pros:
- More control over source
- Works in all Excel versions with Power Query
Limitations:
- Web structure changes can break queries
- Not real-time (pulls data during refresh)
Using VBA and Yahoo Finance API (For Programmers & Developers)
You can use VBA (Visual Basic for Applications) to automate stock price extraction. Access Yahoo Finance’s API or its unofficial endpoints for this task.
Sample VBA Script:
Function GetStockPrice(ticker As String) As Double
Dim http As Object
Dim url As String
Set http = CreateObject(“MSXML2.XMLHTTP”)
url = “https://query1.finance.yahoo.com/v7/finance/quote?symbols=” & ticker
http.Open “GET”, url, False
http.Send
Dim response As String
response = http.responseText
Dim price As Double
price = CDbl(Split(Split(response, “regularMarketPrice””:”)(1), “,”)(0))
GetStockPrice = price
End Function
Usage:
=GetStockPrice(“AAPL”)
Pros:
- Full automation
- Customizable data fields
Limitations:
- API may change
- Not officially supported by Yahoo (not guaranteed stable)
Using Excel Add-ins (Best for Portfolio Management)
There are premium and free Excel Add-ins that integrate stock data:
- MarketXLS
- Tiingo Excel Plugin
- Alpha Vantage Excel Add-in
Steps:
- Go to Insert > Get Add-ins
- Search and install your preferred financial plugin.
- Connect to the provider using an API key if required.
- Use built-in formulas like:
=MXL_Price(“AAPL”)
Pros:
- Rich data: live price, fundamentals, technical indicators
- Works across large watchlists
Limitations:
- Some are paid tools
- May require registration or API keys
How to Get Stock Prices in Excel
Frequently Asked Questions
Can I get real-time stock prices in Excel?
Yes. Excel 365 with the “Stocks” data type offers near real-time updates. For more frequent or custom data, use Power Query or API-based methods.
Is the Stocks data type available in all Excel versions?
No. It is only available in Microsoft 365 and Excel Online. Older versions (like Excel 2016 or 2013) won’t support this feature.
Can I track multiple stocks at once?
Absolutely. Use a column of tickers and convert them all into “Stocks” data type, or loop through them using Power Query or VBA.
Are there any free Excel stock price APIs?
Yes. Yahoo Finance (unofficial), Alpha Vantage (free tier), and Tiingo provide free API access, though there are some limits on usage.
Do these methods work on Mac?
The “Stocks” data type and many add-ins work on Excel for Mac. However, Power Query has limited features compared to Windows.
Conclusion: Which Method Should You Use?
These methods let you analyze stock data efficiently. You won’t have to jump between Excel and financial websites. Combining methods, like Power Query and VBA, gives professionals great flexibility and automation.
Pro Tip: Always double-check your data sources. This is key when you use third-party tools or APIs. It helps ensure accuracy and meets your financial analysis needs.
Ready to take your investment tracking to the next level? No matter if you’re new to Excel or a pro, these tools can help. They’ll turn your spreadsheet into a live stock tracker—smart, powerful, and always current.