Excel Stock Price Formula: Today or Any Specific Date

Excel stock price formula for a specific date: use =STOCKHISTORY("AAPL", DATE(2024,1,2)) in Microsoft 365, or =Close_Historical("AAPL", DATE(2024,1,2)) with the MarketXLS add-in. STOCKHISTORY is built into Microsoft 365 and returns the date and closing price. MarketXLS Close_Historical returns a single closing price and, with a third argument of "Yes", returns the last available close when the date falls on a weekend or market holiday (for example =Close_Historical("SPY", DATE(2024,1,13), "Yes") returns the prior Friday's close).

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

The sections below show each method step by step, including Google Sheets' GOOGLEFINANCE, dynamic dates with TODAY(), and how the three options compare on platform, data coverage and cost.

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

There are three main ways to pull a stock price for a specific date into a spreadsheet: Excel's STOCKHISTORY function, GOOGLEFINANCE in Google Sheets, and the MarketXLS add-in. Each is shown step by step below.

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: no header row
• 0 and 1: the properties to return (0 = date, 1 = close; 2 = high, 3 = low, 4 = open, 5 = volume)

Step 3: Press Enter. Excel returns the date and closing price for that trading day. Add more property numbers to return high, low, open or volume.

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

MarketXLS is an Excel add-in that adds financial-data functions to Excel. Unlike the built-in functions, it covers stocks, ETFs, mutual funds, options and fundamental data, and it handles weekend and holiday dates.

Step 1: Install MarketXLS. Choose a plan on the pricing page and install the add-in. Windows users install the desktop add-in; Mac and Excel for the web users install the Microsoft 365 add-in from Microsoft AppSource, where functions take an mxls. prefix (for example =mxls.Last("AAPL")). Some functions differ by platform, so check the formula list. See choose your version.

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

=Close_Historical("AAPL", DATE(2024,1,2))

Formula documentation: Close_Historical

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. By default, Close_Historical returns "NA" for a weekend or market holiday. Add "Yes" as the third argument to get the most recent available close instead:

=Close_Historical("AAPL", DATE(2024,1,1), "Yes")

Formula documentation: Close_Historical

Since January 1 is a market holiday, this returns the closing price from the previous trading session (December 29, 2023).

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

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

Formula documentation: Close_Historical

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

---

MarketXLS Formulas for Historical Stock Prices

These MarketXLS functions cover current prices, single-date historical prices and full price-history tables:

Get the Current (Last Traded) Price

=Last("AAPL")

Formula documentation: Last

Returns Apple's most recent traded price as a snapshot that updates when Excel recalculates. On the Standard plan, US stock quotes are 15-minute delayed; Advanced and Business plans get real-time data.

You can also use the QuoteMedia on-demand function:

=QM_Last("AAPL")

Formula documentation: QM_Last

Pull a Full Price History Table

=QM_GetHistory("AAPL")

Formula documentation: QM_GetHistory

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")

Formula documentation: GetHistory

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", DATE(2024,6,14)): Microsoft's opening price on a specific date.
• =High_Historical("TSLA", DATE(2024,3,1)): Tesla's intraday high on a specific date.
• =Volume_Historical("AMZN", DATE(2024,7,22)): Amazon's trading volume on a specific date.
• =Adjusted_Close_Historical("AAPL", DATE(2020,1,2)): split- and dividend-adjusted close, for long-term return calculations.
• =PERatio("AAPL"): Apple's current price-to-earnings ratio.
• =DividendYield("MSFT"): Microsoft's current dividend yield.

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 modeling, portfolio tracking or options analysis, MarketXLS adds options chains, fundamentals and holiday handling that the built-in functions do not have.

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 need more than price lookups inside Excel: historical prices with holiday handling, options chains with Greeks, fundamental and technical functions, and real-time streaming quotes (Advanced or Business plan).

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. On-demand MarketXLS functions return a snapshot that updates when Excel recalculates. For prices that update automatically, use streaming functions such as =QM_Stream_Last("AAPL") (real-time streaming requires the Advanced or Business plan).

---

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. The alternatives are: (1) install the MarketXLS add-in, which works as a native-style function (type =Last("AAPL") for the current price or =Close_Historical("AAPL", DATE(2024,1,2)) for a past date), or (2) use a web query or Power Query to import data from a financial website.

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, Close_Historical returns "NA" by default; pass "Yes" as the third argument to return the last available trading day's price. For example, =Close_Historical("AAPL", DATE(2024,12,25), "Yes") 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. The MarketXLS Windows desktop add-in does not require a Microsoft 365 subscription.

How accurate is the historical stock price data in MarketXLS?

MarketXLS quotes come from QuoteMedia; historical and fundamental data come from multiple sources that are cleaned and served through the MarketXLS API. Close_Historical returns the actual traded close for that date. For split- and dividend-adjusted prices, use Adjusted_Close_Historical.

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 as a snapshot (15-minute delayed on the Standard plan, real-time on Advanced and Business), or the last close if the market is closed. =Close_Historical("AAPL", DATE(2024,1,2)) 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

MarketXLS adds 1000+ financial functions to Excel, including historical prices, fundamentals, options chains and technical indicators, with no VBA or API keys to manage. US plans are Standard ($70/month billed annually, 15-minute delayed stock quotes), Advanced ($125/month billed annually, real-time streaming) and Business ($200/month billed annually).

Compare MarketXLS plans →