How to Get Stock Price On Specific Date in Excel

Why This Task Matters in Excel

Tracking historical stock prices is a foundation of many financial workflows. Analysts need accurate close prices to calculate total return, auditors must verify transactions against market values, accountants revalue foreign-listed securities at month-end, and individual investors review entry and exit points. With capital markets data widely available online, Excel remains the go-to environment for slicing, filtering, and presenting that data because:

• It is readily accessible on almost every corporate and personal computer.
• Users can blend price history with internal metrics, budgets, or forecasts in the same workbook.
• Built-in financial functions and dynamic arrays make modelling and scenario analysis fast.

Imagine a treasury department rebalancing a pension fund. They require the official closing price of each holding on the last business day of every month. If the team cannot pull those numbers quickly, reporting deadlines slip, dashboards become outdated, and investment decisions lag behind market movements. Likewise, a CPA producing year-end statements must capture share values on the balance-sheet date; mispricing even by a single day can materially misstate asset values.

Excel offers several paths to retrieve a stock price on a specific date. The modern STOCKHISTORY function (Microsoft 365 and Excel 2021) is purpose-built for exactly this. Earlier versions can rely on Power Query, web connectors, or VBA downloads from services such as Yahoo Finance or Alpha Vantage. Choosing the correct method saves hours of manual copy-paste, eliminates transcription errors, and enables refreshable models that remain accurate over time. Mastering this task dovetails with other Excel skills—dynamic arrays, XLOOKUP, data validation, and dashboard design—and forms the backbone of professional-grade financial analytics.

Best Excel Approach

For most users on Microsoft 365 or Excel 2021, the single best method is the STOCKHISTORY function. It delivers verified historical data from Microsoft’s cloud service directly into your sheet, supports daily, weekly, or monthly intervals, and outputs an array you can further shape with INDEX, XLOOKUP, FILTER, or TAKE. It avoids manual scraping, respects market holidays, and updates automatically when you press Data ▸ Refresh All.

Basic one-day syntax:

=STOCKHISTORY("AAPL","2022-01-03","2022-01-03",2,0,1)

Explanation of key arguments

• Symbol – the ticker string or cell reference, e.g., \"AAPL\" or B2.
• Start date – the earliest date you want (can be the same as end date).
• End date – latest date; using identical dates returns one row.
• Interval – 0 daily, 1 weekly, 2 monthly. Daily is typical for a specific trading day.
• Headers – 0 suppresses header row, 1 shows headers.
• Properties – choose which columns to return; 0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume. Listing only 1 brings back the close price.

Why this is superior

• Simplicity: one formula yields the exact number.
• Reliability: data source maintained by Microsoft and Nasdaq.
• Refreshable: schedule or manual refresh keeps values up to date.
• No extra add-ins or APIs needed.

When to consider alternatives

• You run Excel 2016 or earlier.
• You need intraday quotes (hour, minute).
• You require adjusted close for splits/dividends (Power Query with Yahoo Finance may be better).

Common prerequisites

• Microsoft 365 subscription or Excel 2021 perpetual license.
• Internet connection (the formula queries Microsoft’s online service).
• Regional date system set correctly, or supply ISO date strings \"YYYY-MM-DD\".

Parameters and Inputs

To avoid the dreaded #VALUE! or #N/A errors, pay attention to the following inputs:

Ticker symbol

• Accepts stock, ETF, mutual fund, or currency pair codes.
• Use fully qualified tickers for non-US exchanges, e.g., \"7203.T\" for Toyota on TSE.

Dates

• Accept ISO strings \"2022-12-30\" or cell references with proper date serials.
• If the requested date falls on a weekend or market holiday, STOCKHISTORY returns the next earlier trading day when used with other logic or may output #N/A if outside available range. Handle this by wrapping in XLOOKUP with search mode set to find next smaller match (see Example 3).

Interval

• 0 = Daily (most granular for this task).
• 1 = Weekly and 2 = Monthly aggregate to period end; avoid if you need the exact day.

Headers

• 1 returns column headers. Drop them with 0 when using the result in further calculations to stop header rows from interfering with numerical functions.

Property codes

• You may include multiple property numbers. For a single closing price, use 1 only.
• Example [0,1,5] returns Date, Close, Volume.

Data preparation

• Place the formula where your model has sufficient spill range; array results will overwrite whatever sits to the right and down.
• Format output cell as Currency, Accounting, or Number with desired decimal places.

Edge cases

• Symbols with limited history (recent IPO) may fail for earlier dates.
• Delisted tickers can result in #VALUE! due to unavailable source.
• Mismatched locale (comma versus period decimal separators) can break numeric formatting—set workbook locale correctly.

Step-by-Step Examples

Example 1: Basic Scenario – One Ticker, One Date

Suppose you want Apple’s closing price on 3 January 2022, the first trading day of 2022.

1. Enter \"AAPL\" in cell B2.
2. Enter the date 3-Jan-2022 in cell C2 (ensure it is a valid date).
3. In cell D2, input:

=STOCKHISTORY(B2,C2,C2,0,0,1)

4. Press Enter. STOCKHISTORY spills one cell with the close price (e.g., 182.01).
5. Format D2 as Currency with two decimals.

Why it works
STOCKHISTORY returns an array consisting of one row [Date, Close] since both start and end dates match and we only requested property 1 (Close). Because headers = 0, no column labels appear. The function automatically recognises that 3-Jan-2022 is a valid US trading day.

Variations

• If you need the opening price instead, change the last argument to 2.
• To pull close and volume together, specify [1,5] in the property list:

=STOCKHISTORY(B2,C2,C2,0,0,1,5)

Troubleshooting

• If you type the date as text \"03/01/22\" but your system expects day-month first, Excel may misinterpret. Confirm by checking that C2 displays as 03-Jan-2022 in the formula bar.
• If you accidentally leave headers = 1 and use the result in another calculation, subsequent formulas may reference the string \"Close\" instead of the numeric price. Set headers = 0 or offset the numeric cell with INDEX.

Example 2: Real-World Application – Month-End Portfolio Valuation

A fund manager monitors three holdings—Microsoft (MSFT), Toyota Motor (7203.T), and Nestlé (NESN.SW). They need the closing price on the last business day of each month to mark the portfolio to market.

Data layout

• Column A: Symbol list in A4:A6.
• Row 3: Month-ends (31-Dec-2021, 31-Jan-2022, 28-Feb-2022) in B3:D3.

Approach

1. Create a dynamic STOCKHISTORY formula that uses INDEX to pick the price relative to symbol and date.
2. Enter in B4 and copy across and down:

=LET(
   sym,$A4,
   dt,B$3,
   hist,STOCKHISTORY(sym,dt,dt,0,0,1),
   INDEX(hist,1,2)
)

Explanation

• LET assigns readable labels: sym, dt, hist.
• hist spills a [1,2] array: first column date, second column close price.
• INDEX(hist,1,2) extracts row 1, column 2 (the number) so the sheet grid shows only the price, not the date.
• Copying across/down fills a 3x3 matrix of values.

Business impact
This grid feeds directly into a larger valuation model where quantities per holding are multiplied, subtotaled, and reported to senior management. When month-end dates roll forward, you can simply add a new date in row 3, copy the formula, and refresh. No manual downloads, no pivot tables, no risk of outdated numbers.

Integration tips

• Use Data Validation drop-downs for the symbol list to avoid typos.
• Protect the sheet so formulas are not overwritten by accident.
• Combine with SUMPRODUCT to compute portfolio totals.

Performance notes
Pulling nine data points is negligible, but if you expand to hundreds of symbols across many months, consider placing STOCKHISTORY formulas on a dedicated sheet and referencing the resulting range to avoid repeated calls.

Example 3: Advanced Technique – Automatic Previous Trading Day Handling

Occasionally you need the price for a specific calendar date that falls on a non-trading day (weekend or holiday). The goal is to return the last available close before that date.

Scenario

• Cell B2 holds the ticker \"GOOG\".
• Cell C2 contains an arbitrary date entered by the user.

Formula solution

=LET(
   sym,$B$2,
   target,$C$2,
   table,STOCKHISTORY(sym,EDATE(target,-1),target,0,0,1),
   /* table's first column is Date, second column Close */
   XLOOKUP(target,INDEX(table,,1),INDEX(table,,2),,0,-1)
)

How it works

1. We fetch up to one month of daily history ending at the requested date, ensuring we cover any gap due to holidays.
2. XLOOKUP searches the first column (dates) for an exact match; if none exists, the search mode −1 instructs Excel to return the next smaller item (the most recent prior trading day).
3. The corresponding close price is returned from the second column.

Edge case management

• If the user enters a date earlier than the earliest date in the month-long window, XLOOKUP yields #N/A. Adjust EDATE range further back or trap with IFERROR.
• For performance, the one-month window prevents excessive data retrieval yet covers most holiday gaps.

Professional enhancements

• Wrap the entire LET in IFERROR to return \"No data\" instead of an error message.
• Replace hard-coded months with variable offsets driven by another cell.

Tips and Best Practices

1. Use ISO date strings \"YYYY-MM-DD\" directly in formulas to avoid regional misinterpretation.
2. Keep STOCKHISTORY outputs on a hidden \'Data\' sheet and reference them with named ranges; this declutters model layouts and isolates network refresh delays.
3. When combining with other dynamic array functions like FILTER, lock references with the @ operator or INDEX to extract a scalar, preventing unintended spillage.
4. Refresh data just before printing or exporting reports to guarantee the latest values, especially intraday when markets are live.
5. Document your workbook: note the data source (Microsoft Financial Data) and retrieval date in a cell so auditors know where numbers came from.
6. For large watchlists, stagger formulas (e.g., split into multiple sheets) to reduce simultaneous web calls that might throttle.

Common Mistakes to Avoid

1. Forgetting to include the end date argument. Omitting it returns today’s date, not the day you need. Always supply start and end even when they match.
2. Assuming every calendar date is a trading day. Requesting a Saturday leads to #N/A. Use Example 3’s technique or check with WORKDAY.INTL beforehand.
3. Mixing text and numeric dates. If your date cell contains the string \"2022-01-03\" rather than a true date serial, STOCKHISTORY cannot parse it. Convert with DATEVALUE or re-enter the value.
4. Overlooking spill range. If adjacent cells contain data, STOCKHISTORY shows #SPILL!. Clear the obstructing cells or place the formula in a blank area.
5. Using STOCKHISTORY in Excel 2016 or Google Sheets and expecting it to work. The function is exclusive to Microsoft 365 and Excel 2021. Use Power Query or external APIs instead.

Alternative Methods

When STOCKHISTORY is unavailable or insufficient, consider these options:

Choose the alternative when you need features like adjusted close (Power Query) or you work in a company stuck on Excel 2013. However, when you upgrade to Microsoft 365, migrate to STOCKHISTORY for simplicity and dependability.

FAQ

When should I use this approach?

Use STOCKHISTORY whenever you need a historical price within Microsoft 365/Excel 2021, require fast implementation, and trust Microsoft’s market data for compliance. It is the quickest way to populate audit-ready numbers.

Can this work across multiple sheets?

Yes. Enter STOCKHISTORY on a hidden Data sheet and reference it from Summary sheets using INDEX or XLOOKUP. Dynamic arrays spill only within their host sheet, so link scalar extractions for cross-sheet use.

What are the limitations?

Historical range goes back roughly ten years for many tickers. Some small-cap or delisted securities may be missing. Intraday intervals (hour, minute) are not supported, and adjusted close inclusive of dividends is not provided.

How do I handle errors?

Wrap formulas in IFERROR. For non-trading days, use XLOOKUP with search mode −1 or −1 to pick the closest previous date. For network issues, Excel shows #CONNECT!. Refresh later or check firewall settings.

Does this work in older Excel versions?

No. STOCKHISTORY needs Microsoft 365 or Excel 2021. In Excel 2016 and earlier, replicate functionality with Power Query web downloads or VBA. Alternatively, upgrade to Microsoft 365 to unlock the function.

What about performance with large datasets?

Large watchlists (500+ tickers) can introduce noticeable refresh delays. Mitigate by batching calls, storing retrieved history once, and reusing it with XLOOKUP rather than repeating STOCKHISTORY for every cell. Schedule refresh during off-peak hours.

Conclusion

Knowing how to pull a stock’s price on a specific date transforms Excel from a simple calculator into a robust market data workstation. With STOCKHISTORY, you can automate month-end valuations, back-test strategies, or simply verify past trades in seconds. This skill integrates beautifully with dynamic arrays, LOOKUP functions, and dashboards, rounding out your financial modelling toolkit. Practise the examples above, explore alternative methods if you are on older Excel builds, and you will accelerate your analysis while eliminating manual lookup errors. Happy modelling!