how do i get stock quotes in excel Guide
How do I get stock quotes in Excel
Many users ask: how do i get stock quotes in excel to monitor portfolios, build charts, backtest strategies, or power alerts? This guide explains practical methods for pulling both equity and crypto prices into Microsoft Excel — from the simplest built‑in data types to API integrations, Power Query, add‑ins, and automation. Methods vary by Excel version (Microsoft 365 versus older perpetual licenses), by whether you need historical series or intraday ticks, and by whether you prefer free data or paid real‑time feeds.
Requirements and prerequisites
Before you start, confirm these basics so the workflows below will work for you:
- Excel version and subscription: linked data types and STOCKHISTORY are available in Microsoft 365 (desktop and Excel for the web) and may not be available in older perpetual versions. If you use Office LTSC or older Excel 2016/2019 without updates, some features will be missing.
- Internet access and a Microsoft account: online data types and queries require internet connectivity and, in some setups, a signed‑in Microsoft account to access linked data services.
- API keys or paid subscriptions: for many third‑party web services (financial APIs, premium quote feeds, streaming vendors, or some add‑ins), you will need an API key or a paid subscription to get more fields, higher rate limits, or real‑time pricing.
- Exchange symbol conventions: know the correct ticker format for your data source (exchange suffixes, crypto pairs). Some sources require ISINs or exchange codes; others tolerate plain tickers.
- Security settings: if you plan to embed API keys, use secure storage (environment variables, encrypted workbook properties, or Power Automate secure connectors) rather than plaintext cells in shared workbooks.
As of 2025-12-30, according to Microsoft Support, the Stocks linked data type and STOCKHISTORY function are actively supported in Microsoft 365 and Excel for the web, but coverage and refresh behavior depend on region and account sign‑in state.
Built‑in Excel features
For many users the easiest answer to how do i get stock quotes in excel is to use Excel’s built‑in capabilities: the Stocks linked data type and the STOCKHISTORY function. These provide quick, low‑friction access to common quote fields and historical series without code.
Stocks linked data type (Data → Stocks)
The Stocks data type converts ticker text into a rich linked record that exposes fields such as current Price, Change, Exchange, and Last Trade Time.
Quick steps to use it:
- Type tickers or company names in a column (for example: MSFT, AAPL, or company full names). If you manage crypto tickers, verify whether the built‑in data type recognizes them — often it focuses on equities.
- With the cells selected, go to the Data tab and choose Stocks. Excel attempts to match each entry to a market entity and converts the cells into the Stocks data type.
- Click the expand icon in the cell to pick fields to add as new columns: Price, Change, Market Cap, Exchange, Currency, and more.
- Use the new columns directly in formulas, pivot tables, or charts. The data type behaves like a linked record; you can reference fields with the dot notation in modern Excel.
Why use Stocks data type? It’s fast for casual tracking, portfolio overviews, and when you need a few fields quickly. It’s also convenient for learners because it requires no API key or Power Query knowledge.
STOCKHISTORY function
When your question is how do i get stock quotes in excel for historical prices, STOCKHISTORY is often the right choice. It returns historical series (open, high, low, close, volume) across a date range for supported tickers.
Key aspects:
- Syntax supports single dates, date ranges, and frequency (daily, weekly, monthly).
- It can return OHLC and volume columns for charting and backtesting.
- Availability: STOCKHISTORY is available in Microsoft 365 Excel and Excel for the web; older Excel versions do not include it.
Example use cases: create an automated historical chart for the last 90 trading days or export a series to another tool for analysis. For beginners: enter a ticker in a cell, then call STOCKHISTORY referencing that cell and the date range.
Data refresh behavior and settings
Understanding refresh is essential when you ask how do i get stock quotes in excel and expect timely updates:
- Default behavior: linked data types and some data connections refresh periodically in the background (modern Excel often refreshes every few minutes when the workbook is open and online).
- Manual refresh: use the Data → Refresh All button to force an update.
- Adjust refresh settings: in the Queries & Connections pane you can edit refresh frequency, background refresh, and refresh on file open for Power Query connections.
- Workbooks stored in cloud services may have different refresh rules when opened in the browser versus desktop Excel.
If you need higher refresh cadence or streaming tick data, built‑in features may not suffice and a third‑party feed or add‑in is required.
Importing quotes from the web (Power Query / Get Data)
Power Query is a universal, no‑code route for users asking how do i get stock quotes in excel from web sources. With Get Data → From Web you can scrape table data from finance pages or call web APIs that return HTML, CSV, or JSON.
Typical workflow:
- Data → Get Data → From Web and paste the page or API endpoint URL (some APIs require authentication headers or query keys).
- Power Query previews the data; use the Query Editor to transform, filter, rename columns, parse JSON or XML, and shape the table.
- Load the clean table back into Excel as a Table or connection-only query.
- Set refresh schedule or configure Refresh on Open and periodic refresh options to keep quotes updated.
Power Query is powerful because it can parse JSON responses from APIs, handle paging, and perform repeated transformations so your workbook stays maintainable. However, web scraping pages can break when website layouts change, and some sites block automated requests.
Using web services and formulas
If you prefer formulas rather than Power Query, classic Excel functions can call web services. Use WEBSERVICE to fetch raw text from an HTTP endpoint, then FILTERXML to parse XML. For JSON, Power Query gives a more robust parsing path because native Excel lacks a stable JSON parsing formula.
Common APIs used with Excel include free and paid services that return JSON or CSV. To use them safely:
- Obtain an API key where required and keep it secure.
- Respect rate limits; implement caching or scheduled refresh rather than frequent on‑sheet polling.
- Combine WEBSERVICE with text functions for small, lightweight endpoints, but prefer Power Query for complex JSON parsing.
Third‑party add‑ins and providers
For users asking how do i get stock quotes in excel and needing richer fields or real‑time streaming, commercial add‑ins are available. These add‑ins can provide deeper coverage, fundamentals, multiple exchanges, and premium support.
Trade‑offs to consider:
- Cost: premium feeds and add‑ins typically charge subscription fees for real‑time data and higher API quotas.
- Latency and licensing: true real‑time data often involves exchange licensing and per‑user fees; delayed quotes may be free but unsuitable for active trading.
- Integration complexity: some add‑ins are simple Excel plugins; others require installation of desktop components or configuration of data links.
When recommending platforms or wallets in this guide, Bitget is highlighted as a recommended exchange for users wanting an integrated trading and data experience. For wallet needs, prefer the Bitget Wallet for secure custody and integration with Bitget services.
Getting cryptocurrency quotes in Excel
Question: how do i get stock quotes in excel when 'stock' means crypto? Excel’s Stocks data type may have limited or no crypto coverage. For crypto prices, use APIs or Power Query against JSON endpoints from market data providers. Common approaches:
- Use a crypto data API (public or authenticated) that returns JSON for pair prices (for example, a provider that exposes /ticker endpoints). Parse the JSON with Power Query and load pairs such as BTC‑USD, ETH‑USD.
- Some third‑party add‑ins support crypto quotes directly inside Excel tables and offer paired historical data for charting.
- Remember symbol conventions: some APIs use BTCUSD, BTC‑USD, or BTC/USDT. Confirm the format before importing.
For integrated exchange access and trading, recommend users explore Bitget’s platform and Bitget Wallet as part of an end‑to‑end workflow (data → custody → execution), while keeping security best practices in mind.
Real‑time vs delayed vs historical data: licensing and accuracy
When you ask how do i get stock quotes in excel, clarify the timeliness you need.
- Free public sources often provide delayed quotes (commonly 15–20 minutes for exchange data) and are adequate for casual valuation and portfolio views.
- Real‑time streaming requires paid vendor feeds and may include per‑user or redistribution licensing; exchanges impose rules and fees for real‑time distribution.
- Historical data is usually easy to obtain via STOCKHISTORY or API CSV/JSON endpoints; verify completeness (adjusted vs unadjusted close, corporate actions).
Accuracy: verify any provider’s coverage and reconciliation against an authoritative source before using data for critical decisions. Do not assume parity across providers — ask whether prices are mid‑spread, last trade, or aggregated.
Automation, alerts and integration
Excel can be the center of simple automation and alerting systems when you know how do i get stock quotes in excel in automated form. Options include:
- Workbook refresh schedules: configure query refresh intervals and refresh‑on‑open to keep tables up to date.
- VBA macros: set up code to refresh queries and evaluate thresholds, then show a popup or write a flag cell when criteria are met.
- Office Scripts and Power Automate: in Microsoft 365, create scripts that refresh data, run logic, and notify via email or Teams when conditions occur.
- Conditional formatting and formulas: use simple cell formulas and formatting to highlight price changes, crossovers, or drawdown levels without code.
For production alerting or trade execution, integrate Excel outputs with secure automation platforms and authenticated APIs; for trading on Bitget, ensure API keys have the minimal required permissions and are stored securely.
Advanced options
If you need low‑latency streaming or programmatic workflows beyond Excel’s native scope, consider advanced setups:
- Specialized streaming plugins or vendor SDKs that push tick data into Excel tables for algorithmic monitoring (usually paid and licensed).
- Database integration: store quotes in a SQL database and connect Excel to query the stored series for complex reporting and backtesting.
- Programmatic approaches: use Python or R to fetch and preprocess market data, then write CSV exports that Excel imports; libraries and tools allow scheduled jobs and reproducible pipelines.
- Hybrid cloud solutions: combine Excel front‑end with cloud functions and APIs to reduce local compute and improve scaling.
Common errors and troubleshooting
Below are frequent issues when users try to get quotes and how to fix them:
- Unmatched tickers or "No data" in Stocks type: ensure the ticker text exactly matches the provider’s symbol or use the search in the type selector to pick the right entity.
- #FIELD or record errors: expand the linked data record to pick supported fields; not all fields are available for every security type.
- Missing exchange coverage: if Excel or your API does not cover a local exchange, use Power Query with a provider that does, or a paid add‑in with broader coverage.
- API rate limits and HTTP errors: monitor API usage, implement caching, and respect rate limits; if you hit limits, switch to scheduled refreshes or upgrade your plan.
- Parsing failures for scraped pages: site layout changes break scrapers; prefer stable APIs or build resilient parsing logic with Power Query.
- Refresh conflicts: multiple simultaneous refreshes can conflict; use sequential refresh in macros or schedule refresh windows to avoid contention.
Security, privacy and best practices
Protect your credentials and data when you implement any solution for how do i get stock quotes in excel:
- Never hard‑code API keys or secrets in shared workbook cells. Use protected storage, environment variables on automation agents, or the secure connectors offered by cloud automation services.
- Audit data sources and confirm licensing; do not redistribute licensed feeds beyond permitted use.
- Ensure privacy for personal data; when sharing workbooks, strip any sensitive account or key information.
- Keep Excel and add‑ins up to date to receive security patches, especially when connecting to external services.
Quick recipes / example workflows
Short recipe blurbs to answer practical "how do i get stock quotes in excel" tasks:
- Show current Price and Change: convert a ticker column to the Stocks data type and add the Price and Change fields as columns.
- Pull last‑close or range for charting: use STOCKHISTORY(ticker, start_date, end_date, 0, 1) to return closing prices over a date range for chart plotting.
- Import a table from the web: use Data → Get Data → From Web, point to a finance page with a table (or API endpoint), transform in Power Query, and load as a table with scheduled refresh.
- Fetch JSON from an API (crypto example): call the crypto API endpoint in Power Query, use the JSON parser to expand the nested objects, keep price and timestamp fields, and set refresh frequency to your desired cadence.
When to use which method (decision guide)
Compact decision tree to help decide:
- Casual tracking and quick lookups: use Stocks linked data type.
- Historical series for charts/backtests: use STOCKHISTORY or Power Query for larger ranges and more control.
- Custom fields, unusual tickers, or cross‑exchange data: use Power Query + API endpoints.
- Real‑time trading or low latency: choose a paid data feed or add‑in with proper exchange licensing and consider direct integration with a trading platform such as Bitget for execution.
Further reading and references
For step‑by‑step walkthroughs and official behavior, consult the platform documentation and reputable how‑to guides. As of 2025-12-30, Microsoft Support documents the Stocks data type and STOCKHISTORY usage in Microsoft 365. For Power Query and API integrations, consult tutorial resources focused on web queries, JSON parsing, and API authentication.
Notes and disclaimers
Availability of features and coverage depends on your Excel version, region, subscription, and the data provider. Market data may be delayed, and this article does not provide investment advice. Verify data accuracy and licensing before using any feed for trading or compliance purposes.
Practical next steps
If you want to get started quickly: open Excel, type a few tickers, convert them to the Stocks data type and add Price and Change columns. If you need historical series, try STOCKHISTORY for a short date range. For more advanced needs — APIs, scheduled refresh, or secure execution — consider Power Query and secure API keys, and explore Bitget’s platform and Bitget Wallet for an integrated trading and custody solution.
To explore deeper examples (step‑by‑step Power Query queries, sample STOCKHISTORY formulas, and a template workbook for portfolio monitoring), tell me which sections you want expanded and I’ll provide downloadable templates and exact formulas tailored to Microsoft 365.
