Excel Stock Price on a Specific Date: How to Get Historical Prices With Formulas

Excel stock price lookups are one of the most common tasks for investors and analysts. In this guide, you will learn how to extract a specific date's share price in Excel using simple functions such as STOCKHISTORY or MarketXLS Historical Functions. The data from Excel and the MarketXLS plug-in offers fascinating insights even if the chosen date was a stock market holiday!

How to get specific date stock price in Excel?

To get a specific date's stock price in Excel, you'll need the stock's ticker symbol. Use the "STOCKHISTORY" function. Start by typing "=STOCKHISTORY(" in a cell. Then, input the ticker symbol in quotation marks, followed by the date you want in the format "YYYYMMDD". It should look like this "=STOCKHISTORY("AAPL", 20200101)". Press "Enter" and Excel will display the stock price on that specific date.

MarketXLS Historical Functions

When using MarketXLS, you can access historical stock market data easily through commands like Open Historical, Close Historical, High Historical, and Volume Historical. All that's needed is the stock ticker name. For instance, if you need details about Microsoft's closing price on May 2, 2024, the command would be =Close_Historical("MSFT","2024-05-02″). Also, flexibility is provided as the date can be a cell holding the desired date. This makes data analysis on MarketXLS user-friendly and efficient.

*/}(https://marketxls.com/blog/wp-content/uploads/2024/05/image-1.png)

What if the date was a stock market holiday?

MarketXLS Close_Historical functions offer flexible handling of stock market holidays. This function accepts additional arguments and specific instructions on how to manage such dates. You can use the "Include Holidays" argument and set it to True. If done, the formula computes results based on the final price from the previous trading session. By default, the function will showcase an "NA". This indicates that the specified date was a market holiday. Hence, this function allows users to manage non-trading days effectively.

See the Video below.

The video below show the historical functions of MarketXLS.

Relative historical dates (with =Today()) function

MarketXLS Historical functions allow users to input the date in two different formats. One can either use a string in the "YYYY-MM-DD" format, such as "2024-04-15", or a dynamic date i.e., "=TODAY()-5". The latter represents a date that is five days earlier than the current day. This dynamic approach is beneficial as it automatically updates the referred date relative to the present date whenever you refresh your sheet. This auto-updating feature saves time and increases efficiency.

Summary

To extract a specific date's stock price in Excel, MarketXLS Historical Functions can be used. For example, typing "=STOCKHISTORY("AAPL", 20200101)" into a cell will display Apple's stock price on January 1, 2020. Similarly, "=Close_Historical("MSFT","2024-05-02″)" will show Microsoft's closing price on May 2, 2024. The functions can also handle stock market holidays, automatically using the final price from the previous trading day if instructed to include holidays. MarketXLS Historical functions also allow for dynamic dates which automatically update relative to the current day.

How to Get Any Stock Price on a Specific Date in Excel

Getting a stock price for a particular date is one of the most common tasks for investors and analysts working in Excel. Whether you are back-testing a trading strategy, reconciling portfolio values, or building a financial model, you need reliable historical price data inside your spreadsheet. Here is a step-by-step walkthrough of the three main approaches.

Method 1: Excel's Built-In STOCKHISTORY Function (Microsoft 365)

If you have a Microsoft 365 subscription, the STOCKHISTORY function is available natively. It pulls data from Microsoft's financial data providers directly into your cells.

Step 1 — Open a blank worksheet. Click on an empty cell where you want the result.

Step 2 — Enter the formula. Type:

=STOCKHISTORY("AAPL", "2024-01-02", "2024-01-02", 0, 0, 0, 1)

The arguments are:

• "AAPL" — the ticker symbol
• First date — the start date
• Second date — the end date (same date for a single-day lookup)
• 0 — daily interval
• 0 — include headers or not (0 = no headers)
• 0 — property order (0 = date, close, open, high, low, volume)
• 1 — sort ascending

Step 3 — Press Enter. Excel returns a spill range containing the date, close, open, high, low, and volume for that trading day.

Limitations of STOCKHISTORY:

• Only available in Microsoft 365 (not Excel 2019 or earlier).
• Data source is limited — some international exchanges and smaller-cap stocks may not be covered.
• You cannot pull real-time or streaming prices.
• No options data, no fundamental data, and no custom indicators.

Method 2: GOOGLEFINANCE in Google Sheets

If you work in Google Sheets instead of Excel, the GOOGLEFINANCE function is the equivalent tool:

=GOOGLEFINANCE("AAPL", "close", DATE(2024,1,2))

This returns Apple's closing price on January 2, 2024. You can also pull "open", "high", "low", and "volume" by changing the second argument.

Limitations of GOOGLEFINANCE:

• Only works in Google Sheets — you cannot use it in desktop Excel.
• Data can be delayed or occasionally inaccurate for certain markets.
• No options pricing, no fundamental ratios, and limited historical depth.

Method 3: MarketXLS Add-In for Excel (Recommended)

MarketXLS is a professional-grade Excel add-in that connects your spreadsheet to institutional-quality financial data. Unlike the built-in functions, MarketXLS covers stocks, ETFs, mutual funds, options, and fundamental data — all from within Excel on your desktop.

Step 1 — Install MarketXLS. Download and install the add-in from marketxls.com. It works with Excel 2016 and later on Windows.

Step 2 — Use the historical price formula. In any cell, type:

=Close_Historical("AAPL", "2024-01-02")

This returns Apple's closing price on January 2, 2024. You can also use =Open_Historical, =High_Historical, =Low_Historical, or =Volume_Historical for other data points.

Step 3 — Handle holidays automatically. Add a third argument to include holiday handling:

=Close_Historical("AAPL", "2024-01-01", TRUE)

Since January 1 is a market holiday, MarketXLS returns the closing price from the previous trading session (December 29, 2023) instead of an error.

Step 4 — Use dynamic dates. Instead of hard-coding a date, reference today's date minus an offset:

=Close_Historical("AAPL", TODAY()-30)

This always returns the closing price from 30 days ago, updating automatically every time you refresh.

MarketXLS Formulas for Historical Stock Prices

MarketXLS provides hundreds of Excel functions for financial data. Here are the most useful ones for working with stock prices:

Get the Current (Last Traded) Price

=Last("AAPL")

Returns Apple's most recent traded price. This is a snapshot price that updates when you refresh the add-in.

You can also use the QuoteMedia variant:

=QM_Last("AAPL")

Both return the same last-traded price but use different data routing internally.

Pull a Full Price History Table

=QM_GetHistory("AAPL")

This spills a full table of historical OHLCV (open, high, low, close, volume) data for Apple. You can use this to build charts, calculate returns, or feed other formulas.

For more control over the date range and periodicity, use:

=GetHistory("AAPL", "2023-01-01", "2024-01-01", "Daily")

This returns daily OHLCV data for Apple between January 1, 2023 and January 1, 2024. You can change the periodicity to "Weekly" or "Monthly".

Additional Useful Formulas

• =Open_Historical("MSFT", "2024-06-15") — Microsoft's opening price on a specific date.
• =High_Historical("TSLA", "2024-03-01") — Tesla's intraday high on a specific date.
• =Volume_Historical("AMZN", "2024-07-22") — Amazon's trading volume on a specific date.
• =PERatio("AAPL") — Apple's current price-to-earnings ratio.
• =DividendYield("MSFT") — Microsoft's current dividend yield.
• =MarketCapitalization("GOOG") — Alphabet's current market capitalization.

These formulas work directly inside Excel cells, just like any native function. No VBA, no macros, no APIs to configure.

Comparison: STOCKHISTORY vs GOOGLEFINANCE vs MarketXLS

Choosing the right tool depends on your platform and data needs. Here is how the three options compare:

For serious financial modeling, portfolio tracking, or options analysis, MarketXLS provides significantly more depth and accuracy than either built-in function.

Who Should Use Each Tool?

Choose STOCKHISTORY if you already have Microsoft 365, you only need basic price lookups for major US stocks, and you do not need real-time data or options pricing. It is free with your subscription and requires no installation.

Choose GOOGLEFINANCE if you work primarily in Google Sheets, need quick price checks for personal investing, and do not require desktop Excel compatibility. It is the most accessible option for casual users who want a free solution.

Choose MarketXLS if you are a professional analyst, active trader, or portfolio manager who needs comprehensive financial data inside Excel. MarketXLS is the right choice when you need historical prices across decades, real-time streaming quotes, full options chains with Greeks, over 1,100 fundamental and technical functions, and reliable holiday handling. It is the only solution that turns Excel into a complete financial analysis platform.

Tips for Working With Historical Stock Prices in Excel

Here are some practical tips to make your historical price workflows more efficient:

Use named ranges for ticker symbols. Instead of typing "AAPL" in every formula, define a named range (e.g., Ticker) that references a cell containing the symbol. This lets you change the stock you are analyzing by updating a single cell.

Build a date input cell. Create a dedicated cell for your target date and reference it in all your historical price formulas. This makes it easy to shift your entire analysis to a different date without editing multiple formulas.

Combine with conditional formatting. Highlight cells where prices changed by more than a certain percentage compared to the previous day. This makes it easy to spot significant price moves in large datasets.

Create a price comparison dashboard. Use =Last("AAPL") alongside =Close_Historical("AAPL", TODAY()-365) to show current price versus one year ago in the same row. Add a simple formula to calculate the year-over-year return percentage.

Refresh data regularly. MarketXLS data updates when you click the Refresh button in the add-in ribbon. For real-time prices, use the streaming functions like =Stream_Last("AAPL") which update automatically throughout the trading day.

Frequently Asked Questions

How do I get a stock price in Excel without Microsoft 365?

If you do not have Microsoft 365, the STOCKHISTORY function is not available. Your best alternatives are: (1) install the MarketXLS add-in, which works with Excel 2016 and later, or (2) use a web query or Power Query to import data from a financial website. MarketXLS is the simplest option because it works just like a native Excel function — type =Last("AAPL") and get the price instantly.

Can I pull historical stock prices for multiple dates at once?

Yes. With MarketXLS, use =QM_GetHistory("AAPL") to spill an entire table of historical OHLCV data. You can also use =GetHistory("AAPL", "2023-01-01", "2024-12-31", "Daily") to specify a custom date range. The data fills down automatically into adjacent cells.

What happens if I request a stock price on a weekend or holiday?

With the native STOCKHISTORY function, Excel returns an error or blank for non-trading days. With MarketXLS, you can pass a third argument (TRUE) to the historical functions to automatically return the last available trading day's price. For example, =Close_Historical("AAPL", "2024-12-25", TRUE) returns the closing price from December 24 (the last trading session before Christmas).

Does STOCKHISTORY work in Excel 2019 or Excel 2021?

No. The STOCKHISTORY function is exclusive to Microsoft 365 subscribers. If you are using Excel 2019 or Excel 2021 (perpetual license), you will not have access to this function. MarketXLS works with Excel 2016 and all later versions, including perpetual licenses.

How accurate is the historical stock price data in MarketXLS?

MarketXLS sources its data from QuoteMedia and other institutional-grade data providers. The historical price data covers decades of history for US equities and is the same data used by professional trading platforms. Prices are adjusted for splits and dividends by default.

Can I use MarketXLS to track options prices on specific dates?

Yes. MarketXLS includes a full options data suite. Use =QM_GetOptionChain("AAPL") to pull the entire option chain, or construct specific option symbols with =OptionSymbol("AAPL", "2026-03-21", "C", 200) and then price them with =QM_Last(). Historical options data is also available for back-testing strategies.

How do I build a stock price history chart in Excel?

First, use =QM_GetHistory("AAPL") or =GetHistory("AAPL", "2023-01-01", "2024-12-31", "Daily") to pull historical OHLCV data into a range of cells. Then select the date and close price columns, go to Insert > Chart, and choose a Line chart type. Excel will automatically plot the price history over time. You can customize the chart with titles, axis labels, and formatting. For a candlestick chart, select the date, open, high, low, and close columns and choose the Stock chart type under Insert > Chart > All Charts > Stock.

What is the difference between Last and Close_Historical in MarketXLS?

=Last("AAPL") returns the most recent traded price — essentially a real-time snapshot of where the stock is trading right now (or as of market close if the market is currently closed). =Close_Historical("AAPL", "2024-01-02") returns the official closing price for a specific historical date. Use Last when you need the current price, and use Close_Historical when you need to look up what the stock was trading at on a particular date in the past.

Get Started With Excel Stock Price Formulas

Ready to pull any stock price — historical or real-time — directly into Excel? MarketXLS gives you access to over 1,100 financial functions, including historical prices, fundamentals, options chains, and technical indicators. No VBA required. No API keys to manage. Just install the add-in and start typing formulas.

Start your free trial at MarketXLS →

Visit the MarketXLS pricing page to compare plans and find the right fit for your workflow. Whether you are tracking a handful of stocks in a personal portfolio or building complex financial models for professional clients, MarketXLS has a plan that scales with your needs. Every plan includes access to historical price functions, real-time quotes, and the full library of over 1,100 Excel functions for stocks, ETFs, options, and fundamental analysis.