Yes, you can build a useful stock-market dashboard in Excel without writing an API integration. The most reliable approach combines Excel’s Stocks linked data type for current or descriptive fields with STOCKHISTORY for daily, weekly, or monthly historical prices.
That combination can power a watchlist, portfolio calculations, KPI cards, performance charts, and date or ticker selectors. It is not a real-time trading terminal: Microsoft says the information is delayed, supplied “as-is,” and not intended for trading or investment advice. See Microsoft’s Stocks guidance and STOCKHISTORY documentation.
As an Amazon Associate I earn from qualifying purchases.
What you will build
A practical workbook should separate presentation from data. Use these worksheets:
Free tools Windows power users keep installed
One-click scans. No signup required.
- Dashboard: KPI cards, charts, selected security, and watchlist summary.
- Watchlist: linked stocks, current fields, holdings, and portfolio calculations.
- History: the dynamic historical-price output used by charts and return calculations.
- Inputs: selected ticker, dates, benchmark, and portfolio assumptions.
- Checks: error flags, matched-instrument verification, refresh notes, and currency warnings.
Check Excel compatibility first
STOCKHISTORY is available in Excel for Microsoft 365 and Excel for Microsoft 365 for Mac, subject to a qualifying subscription such as Microsoft 365 Personal, Family, Business Standard, or Business Premium. Stocks linked data types may also be available in Excel for the web with a free Microsoft Account, but that does not mean the complete STOCKHISTORY workflow is available without Microsoft 365.
Access varies by platform, account, language, organization, and application version. An older Excel installation may open a workbook containing linked data but be unable to refresh or change those data types. Microsoft’s linked-data FAQ lists current access requirements and supported editing languages. Microsoft also says Wolfram data types are no longer supported after June 11, 2023, and organizational data types ended after July 31, 2025; Bing and Power Query remain supported in the linked-data documentation.
1. Create the watchlist
On the Watchlist sheet, enter securities vertically and convert the range into an Excel Table named tblWatchlist. Use a Symbol column, plus optional input columns for shares and average cost.
| Column | Purpose |
|---|---|
| Symbol | Qualified or unqualified ticker entered by the user |
| Company | Matched company, fund, or instrument name |
| Price | Latest available price field |
| Change | Daily price change |
| Change % | Daily percentage change |
| Previous Close | Previous closing price |
| Exchange | Exchange of the matched security |
| 52-Week High / Low | Range fields where available |
| Volume | Latest available volume where available |
| Shares / Average Cost | User-entered portfolio inputs |
When a ticker could refer to more than one security, use an exchange-qualified symbol. Microsoft documents the format as a four-character ISO market identifier followed by a colon, such as:
XNAS:MSFTA bare ticker can resolve to the wrong exchange, share class, fund, or similarly named instrument.
2. Convert tickers to Stocks data types
- Select the ticker cells or the relevant column.
- Choose Data > Stocks in the Data Types group.
- If Excel shows multiple matches, select the correct security.
- Confirm that the cells display the linked-record stock icon.
A question-mark icon means Excel could not confidently identify the instrument. Open the selector, search with the ticker and company name, add an exchange prefix, and verify the resulting company and exchange in visible columns. Microsoft’s stock-quote instructions explain this matching process.
3. Pull current fields into the table
If the linked stock is in A2, dot notation can retrieve available fields:
=A2.Price
=A2.Change
=A2.[Change %]
=A2.[Previous Close]
=A2.Exchange
=A2.[52 Week High]
=A2.[52 Week Low]
=A2.VolumeFor a table, structured references are easier to maintain:
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall=[@Symbol].Price
=[@Symbol].ChangeField names differ by instrument and availability. Do not assume every stock, ETF, fund, or index exposes every field. Type a period after the linked cell and use Excel’s autocomplete, or open the stock card to inspect available fields. Fields containing spaces require brackets. See Microsoft’s guide to formulas referencing data types.
4. Add portfolio calculations
Assume Shares is in column J, Average Cost in K, and Price in C. Add these calculated columns:
Market Value:
=J2*C2
Cost Basis:
=J2*K2
Unrealized Gain/Loss:
=J2*C2-J2*K2
Return %:
=IFERROR((C2-K2)/K2,0)
Portfolio Weight:
=IFERROR([@[Market Value]]/SUM(tblWatchlist[Market Value]),0)These are price-based estimates. They do not automatically account for commissions, taxes, dividends, reinvestment, stock splits, mergers, spin-offs, or foreign-exchange movements.
5. Pull historical prices with STOCKHISTORY
On the Inputs sheet, use:
B2: selected ticker, such asXNAS:MSFTB3: start dateB4: end date
Then place this formula in History!A2:
=STOCKHISTORY(Inputs!$B$2,Inputs!$B$3,Inputs!$B$4,0,1,0,1,5)This requests daily data with headers for date, closing price, and volume. The syntax is:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minute=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])Supported interval values are 0 for daily, 1 for weekly, and 2 for monthly. Supported properties are 0 Date, 1 Close, 2 Open, 3 High, 4 Low, and 5 Volume. Microsoft documents the complete function at STOCKHISTORY.
The result is a dynamic array, so cells beside and below the formula must be empty. Keep the spill range on the History sheet rather than placing it directly over dashboard objects.
6. Calculate returns and daily changes
If the historical output starts in A2 and contains headers, the close prices are typically in column B. A basic period return is:
Rank #3
=LET(
history,DROP(A2#,1),
close,CHOOSECOLS(history,2),
INDEX(close,ROWS(close))/INDEX(close,1)-1
)For a helper range containing consecutive closes, calculate daily return with:
=B3/B2-1For a dynamic comparison chart, rebase the close series to 100:
=B2/$B$2*100Rebased performance is more meaningful than comparing nominal prices when two securities have different price levels. A close-to-close calculation is still not total investment return: dividends, fees, taxes, splits, corporate actions, and currency effects may be missing.
7. Build the dashboard
KPI cards
Useful cards include total market value, total unrealized gain or loss, portfolio return, number of holdings, best performer, worst performer, selected price, and selected-period return.
=SUM(tblWatchlist[Market Value])
=SUM(tblWatchlist[Gain/Loss])
=IFERROR(SUM(tblWatchlist[Gain/Loss])/SUM(tblWatchlist[Cost Basis]),0)
=MAX(tblWatchlist[Return %])
=MIN(tblWatchlist[Return %])Charts
- Price trend: use a line chart with historical dates and closes.
- Relative performance: chart rebased series beginning at 100.
- Volume: use a separate column chart; do not place volume and price on the same axis.
- Allocation: use a horizontal bar chart for many holdings or a doughnut chart for a small portfolio.
Apply conditional formatting to daily change, gain/loss, and return columns. Use positive and negative signs as well as color, because red-green-only designs are less accessible.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →8. Add ticker and date controls
Create a dropdown in Inputs!B2 using the qualified symbols in the watchlist. Add date inputs in B3:B4, and optionally a benchmark ticker in B5. Because the History formula references these cells, changing the selection updates the historical array and its charts.
For reliability, display the selected instrument’s matched name and exchange on the Dashboard. That makes a wrong-match error visible instead of allowing an incorrect chart to look authoritative.
9. Refresh the workbook
To refresh a linked stock cell, right-click it and choose Data Type > Refresh. To refresh linked data types, queries, connections, and PivotTables together, use:
Data > Refresh AllMicrosoft also documents Ctrl+Alt+F5 for Refresh All. Automatic refresh settings may be available under linked-data options, including refresh on open or a five-minute interval, but Microsoft currently labels that interface as an Insiders feature. Do not assume every Excel installation has it, and do not treat a five-minute setting as a guarantee of real-time data.
STOCKHISTORY is historical functionality. It generally updates after a trading day completes, not continuously during the session. A formula using TODAY() may recalculate when the workbook opens if automatic calculation is enabled, but that still does not make the result an intraday quote.
Important data limitations
Delayed and as-is information
Microsoft states that stock information is delayed, provided as-is, and not intended for trading or investment advice. Use this dashboard for organization, education, reporting, and planning—not trade execution or real-time risk management.
Coverage varies
Some instruments may have a Stocks data type but no historical data. Microsoft specifically notes that many popular index funds, including the S&P 500, may not have historical information available through STOCKHISTORY. A corresponding ETF may work, but label it clearly as a substitute rather than silently treating it as the same benchmark.
Currency matters
If holdings span USD, CAD, EUR, GBP, or other currencies, adding market values without conversion produces a misleading portfolio total. Add a currency column, choose a base currency, record the FX source and timestamp, and flag mixed-currency totals until conversion rates are applied.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Professional use
Microsoft identifies LSEG Data & Analytics as a provider of financial data used by these Excel features and describes restrictions affecting professional financial and related uses. Professional users should review the applicable Microsoft financial-data-source terms and obtain appropriately licensed data where necessary.
Troubleshooting
#FIELD!
The field may not exist for that instrument, the field name may be wrong, the linked record may have failed, or the online service may be unavailable. Inspect the stock card, use autocomplete after =A2., try an exchange-qualified ticker, confirm that you are signed in, update Excel, or replace the field with one the instrument supports.
Question-mark icon
Excel could not confidently match the text. Use the selector, search by ticker and company name, and verify the exchange and instrument type.
#NAME? for STOCKHISTORY
The Excel edition, account, or platform may not support the function. Confirm that you are using qualifying Microsoft 365 Excel or Excel for Microsoft 365 for Mac, update the application, check the subscription, or try Excel for the web where appropriate.
Spill error
Clear cells obstructing the dynamic-array output. Select the formula cell and follow the spill-range border. Move the formula to a clear helper sheet if necessary; do not place a large spill formula where tables, notes, or charts need the same cells.
Wrong security
Check the matched company name, exchange, fund type, and share class. Use a qualified ticker and keep those verification fields visible.
Missing or stale history
Try a supported symbol or corresponding ETF, check the market and date range, use Data > Refresh All, and distinguish a provider delay from a workbook-refresh problem. If you need broader coverage, consider Power Query or a licensed market-data source.
When Excel is the wrong tool
Excel is a strong choice for a small, formula-driven watchlist. Consider Power Query or an external API for repeatable imports, larger watchlists, custom fields, data normalization, or controlled ingestion. Consider Power BI for centralized reporting and sharing. Use a dedicated market-data platform when you need intraday or real-time quotes, alerts, screeners, technical analysis, large historical datasets, or professional licensing.
Recommended Free Tools
Those alternatives do not automatically solve data rights, rate limits, authentication, corporate-action adjustments, or currency issues. They simply provide more suitable infrastructure when the workbook has outgrown native Excel features.
Quick Recap
Final checklist
- Use exchange-qualified tickers where ambiguity is possible.
- Verify the matched company, fund, share class, and exchange.
- Use Stocks data types for current fields and
STOCKHISTORYfor historical series. - Keep dynamic-array spill ranges clear.
- Label data as delayed and show a refresh note.
- Check whether every requested field and instrument has coverage.
- Track currency and apply FX conversion before combining totals.
- Separate price return from total return.
- Account separately for dividends, fees, taxes, splits, and corporate actions.
- Do not use the workbook as a real-time trading terminal.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
