Excel Stock History Formula: Complete Guide to Historical Stock Data

Excel has one built-in stock history formula, =STOCKHISTORY(), which is available only in Microsoft 365. It returns daily, weekly, or monthly open, high, low, close, and volume for a ticker and date range. With the MarketXLS add-in you get three more options: =GetHistory("AAPL","2024-01-01","2024-12-31","daily") spills a full price table, =QM_GetHistory("AAPL") returns history from the QuoteMedia feed, and =Close_Historical("AAPL",DATE(2024,5,13)) returns a single closing price for one date.

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

This guide gives the syntax for each method, shows when to use a spilled table versus a single-date formula, and walks through the Excel formulas for returns, volatility, beta, and drawdown that you build on top of the history.

Why You Need an Excel Stock History Formula

Historical stock data is the foundation of nearly every type of investment analysis:

• Performance measurement — Calculate how much a stock has returned over any time period.
• Trend analysis — Identify long-term trends, support levels, and resistance levels.
• Volatility calculation — Measure historical volatility using standard deviation of daily returns.
• Backtesting — Test trading strategies against actual historical prices before risking real capital.
• Comparative analysis — Compare the performance of multiple stocks or a stock against a benchmark.
• Financial modeling — Feed historical data into DCF models, regression analysis, or Monte Carlo simulations.
• Portfolio attribution — Understand which holdings drove portfolio performance over specific periods.

An Excel stock history formula eliminates the tedious process of manually downloading CSV files from financial websites and importing them into your workbook. Instead, you get live connections to historical data that update automatically.

Methods for Pulling Historical Stock Data into Excel

Comparison of Methods

---

Method 1: Microsoft STOCKHISTORY Function

If you have Microsoft 365, you can use the built-in STOCKHISTORY function.

Syntax

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties])

Parameters:

• stock — Ticker symbol or cell reference (e.g., "AAPL")
• start_date — Start of the date range
• end_date — (Optional) End of date range; defaults to today
• interval — 0 = daily, 1 = weekly, 2 = monthly
• headers — 0 = no headers, 1 = show headers, 2 = instrument + headers
• properties — Bit field: 0 = date, 1 = close, 2 = open, 4 = high, 8 = low, 16 = volume

Example

=STOCKHISTORY("AAPL", "2024-01-01", "2024-12-31", 0, 1, 0+1+2+4+8+16)

This returns daily OHLCV data for AAPL for the full year 2024 with headers.

Limitations

• Only available in Microsoft 365 (not Excel 2019 or earlier).
• Data comes from a single source (Microsoft/Refinitiv) — no provider choice.
• Spill range can interfere with existing data.
• Limited to basic price data — no fundamentals, no options, no custom fields.

---

Method 2: Power Query CSV Import

You can download historical data as CSV files from financial websites and import them using Power Query.

Steps

1. Download a CSV file with historical prices from a financial data provider.
2. In Excel, go to Data → Get Data → From File → From CSV.
3. Select your file, preview the data, and click Load.
4. The data appears as an Excel Table that you can refresh.

Automating with Web Source

Instead of downloading files manually, you can point Power Query at a URL that returns CSV data:

1. Data → Get Data → From Web
2. Enter the API URL or download link.
3. Configure transformations (data types, column renames, filtering).
4. Load into your worksheet.

Limitations

• URLs and page structures change frequently, breaking your queries.
• Many free data sources have rate limits or require API keys.
• No real-time or intraday data.
• Requires manual setup for each new ticker.

---

Method 3: MarketXLS =GetHistory()

The MarketXLS =GetHistory() function returns a table of OHLCV price data for a ticker, a start date, an end date, and an optional periodicity. Check the function's platform label in the formula library before building a shared workbook, and add the mxls. prefix in the Microsoft 365 add-in (Excel for Mac and the web).

Syntax

=GetHistory("ticker", start_date, end_date, periodicity)

Formula documentation: GetHistory

Parameters:

• ticker — Stock symbol (e.g., "AAPL", "MSFT", "SPY")
• start_date — Start date in "YYYY-MM-DD" format or a cell reference
• end_date — End date in "YYYY-MM-DD" format or a cell reference
• periodicity — "daily", "weekly", or "monthly"

Examples

Daily Data for One Year

=GetHistory("AAPL", "2024-01-01", "2024-12-31", "daily")

Formula documentation: GetHistory

Returns approximately 252 rows of daily OHLCV data for AAPL.

Weekly Data for Five Years

=GetHistory("MSFT", "2020-01-01", "2024-12-31", "weekly")

Formula documentation: GetHistory

Returns approximately 260 rows of weekly price data.

Monthly Data for a Decade

=GetHistory("GOOGL", "2015-01-01", "2024-12-31", "monthly")

Formula documentation: GetHistory

Returns approximately 120 rows of monthly price data.

Using Cell References

=GetHistory(A2, B1, C1, "daily")

Formula documentation: GetHistory

Where A2 contains the ticker, B1 the start date, and C1 the end date. This makes it easy to change parameters without editing the formula.

Output Format

=GetHistory() returns a spill array with columns typically including:

• Date
• Open
• High
• Low
• Close
• Volume
• Adjusted Close

The data spills downward from the cell where you enter the formula, filling as many rows as needed.

---

Method 4: MarketXLS =QM_GetHistory()

=QM_GetHistory() returns historical prices from the QuoteMedia data feed and uses QuoteMedia symbols. It is available in both the Windows desktop add-in and the Microsoft 365 add-in (as =mxls.QM_GetHistory("AAPL")).

Syntax

=QM_GetHistory("ticker")

Formula documentation: QM_GetHistory

When to Use

• When you want data from a specific provider (QuoteMedia).
• As a fallback if =GetHistory() does not return data for a particular symbol.
• When your analysis requires cross-referencing data from multiple feeds.

---

Practical Applications of the Excel Stock History Formula

1. Calculating Total Return

Once you have historical data from =GetHistory(), calculate the total return over any period:

Total Return = (Ending Price - Starting Price) / Starting Price

In Excel:

=(LAST_CLOSE - FIRST_CLOSE) / FIRST_CLOSE

Where LAST_CLOSE and FIRST_CLOSE reference the appropriate cells from your =GetHistory() output.

2. Computing Daily Returns

Daily returns are essential for volatility analysis and risk measurement:

Cell G3: =(F3 - F2) / F2

Where column F contains closing prices. Copy this formula down for all rows.

3. Calculating Historical Volatility

Standard deviation of daily returns gives you historical volatility:

=STDEV.S(G3:G253) * SQRT(252)

This annualizes the daily standard deviation (252 trading days per year).

4. Building a Moving Average

Use Excel's AVERAGE function on the closing prices from =GetHistory():

50-Day SMA: =AVERAGE(F2:F51)
200-Day SMA: =AVERAGE(F2:F201)

Or use MarketXLS's built-in function for current moving averages:

=SimpleMovingAverage("AAPL", 50)
=SimpleMovingAverage("AAPL", 200)

Formula documentation: SimpleMovingAverage

5. Creating Stock Price Charts

Select the Date and Close columns from your =GetHistory() output, then:

1. Go to Insert → Chart → Line Chart.
2. Excel creates a price chart automatically.
3. Add a secondary series for volume if desired.
4. Add moving average trendlines via the chart's "Add Trendline" option.

6. Comparing Multiple Stocks

Pull history for multiple tickers and normalize to a common starting point:

=GetHistory("AAPL", "2024-01-01", "2024-12-31", "daily")
=GetHistory("MSFT", "2024-01-01", "2024-12-31", "daily")
=GetHistory("GOOGL", "2024-01-01", "2024-12-31", "daily")

Formula documentation: GetHistory

Normalize each series: divide every close by the first close, then multiply by 100. This shows relative performance from a common base of 100.

7. Analyzing Seasonal Patterns

Use monthly data over multiple years to identify seasonal trends:

=GetHistory("SPY", "2015-01-01", "2024-12-31", "monthly")

Formula documentation: GetHistory

Then calculate average monthly returns across years to see if certain months consistently outperform or underperform.

---

Combining Historical and Current Data

The real power of the Excel stock history formula emerges when you combine historical data with current market data from MarketXLS:

Current Price with Historical Context

Current price:        =Last("AAPL")
P/E ratio:            =PERatio("AAPL")
52-week history:      =GetHistory("AAPL", TODAY()-365, TODAY(), "daily")

Formula documentation: Last, PERatio, GetHistory

Building a Stock Fact Sheet

Create a comprehensive single-stock analysis sheet:

This gives you a complete picture: current valuation, income characteristics, technical signals, and historical price data — all from Excel formulas.

---

Advanced Techniques

Dynamic Date Ranges

Use Excel date functions to create rolling date ranges:

Start date (1 year ago):  =TODAY()-365
End date (today):         =TODAY()

=GetHistory("AAPL", TODAY()-365, TODAY(), "daily")

Formula documentation: GetHistory

This always returns the most recent year of data, regardless of when you open the workbook.

Multiple Periodicities in One Workbook

Create separate sheets for different time frames:

• Daily sheet: =GetHistory("AAPL", TODAY()-90, TODAY(), "daily") — last 90 days
• Weekly sheet: =GetHistory("AAPL", TODAY()-365, TODAY(), "weekly") — last year
• Monthly sheet: =GetHistory("AAPL", TODAY()-3650, TODAY(), "monthly") — last 10 years

Calculating Beta

Beta measures a stock's sensitivity to market movements. Using historical data from =GetHistory():

1. Pull daily returns for the stock and the benchmark (e.g., SPY).
2. Use Excel's SLOPE function:

=SLOPE(stock_returns, market_returns)

This gives you the stock's beta relative to the market.

Building a Correlation Matrix

Pull historical data for multiple stocks and calculate the correlation of their returns:

=CORREL(AAPL_returns, MSFT_returns)

A correlation matrix helps with portfolio diversification — you want assets that are not highly correlated.

---

Building a Historical Analysis Dashboard

Combine multiple Excel stock history formula functions into a single comprehensive dashboard for any stock.

Single-Stock Analysis Template

Create a workbook with these sheets:

Sheet 1: Summary

Sheet 2: Daily History

=GetHistory(Summary!B1, TODAY()-365, TODAY(), "daily")

Formula documentation: GetHistory

This spills approximately 252 rows of daily OHLCV data. Use this for:

• Daily price chart with volume bars
• 50-day and 200-day moving average overlays
• Daily return distribution histogram
• Maximum drawdown calculation

Sheet 3: Weekly History

=GetHistory(Summary!B1, TODAY()-1825, TODAY(), "weekly")

Formula documentation: GetHistory

Five years of weekly data for medium-term trend analysis and pattern identification.

Sheet 4: Monthly History

=GetHistory(Summary!B1, TODAY()-3650, TODAY(), "monthly")

Formula documentation: GetHistory

Ten years of monthly data for long-term performance assessment and cyclical analysis.

Multi-Stock Comparison Dashboard

Build a dashboard that compares multiple stocks side by side:

Setup

1. List tickers in cells A2:A11 (up to 10 stocks).
2. For each ticker, pull 1-year daily data on separate sheets.
3. On the comparison sheet, calculate:

Return Calculations from Historical Data

Once you have historical data from =GetHistory(), here are the key return calculations:

Cumulative Return

=(Last_Close - First_Close) / First_Close

Annualized Return (CAGR)

=((Last_Close / First_Close) ^ (365 / Total_Days)) - 1

Maximum Drawdown

Maximum drawdown measures the largest peak-to-trough decline:

1. Calculate running maximum: =MAX($F$2:F2) (where F is the close price column)
2. Calculate drawdown: =(Running_Max - Close) / Running_Max
3. Maximum drawdown: =MAX(Drawdown_Column)

This is a critical risk metric that tells you the worst historical loss an investor would have experienced.

Rolling Returns

Calculate 30-day, 90-day, or 252-day rolling returns to see how returns vary over time:

30-day rolling return: =(F32 - F2) / F2

Shift this formula down one row at a time to create a rolling return series. Chart this to visualize return variability.

Dividend-Adjusted Returns

When analyzing historical performance, raw price data does not account for dividends. For stocks that pay dividends, use the adjusted close price column from =GetHistory() output to calculate total returns that include dividend income.

The difference between price return and total return can be significant for high-dividend stocks over long periods. As a rough guide, a stock yielding 3% adds about 3 percentage points a year to total return compared with price return alone, and the gap widens with compounding.

Exporting and Sharing Historical Data

Once you have historical data in Excel, you can:

• Export to CSV for use in other tools (Python, R, trading platforms).
• Create PDF reports with charts and analysis for clients or personal records.
• Build automated reports that refresh and regenerate each month.
• Share workbooks with colleagues who also have MarketXLS installed.

Performance Attribution

Use historical data to understand what drove your portfolio's performance:

1. Pull =GetHistory() for each holding and for your benchmark (e.g., SPY).
2. Calculate each holding's contribution to total return based on its weight and individual return.
3. Decompose total portfolio return into allocation effect (sector weights) and selection effect (stock picking).

This type of analysis is standard in professional portfolio management and is straightforward to implement with Excel stock history formula data from MarketXLS.

Method 5: Point-in-Time Historical Functions

=GetHistory() and =QM_GetHistory() spill a whole table. Sometimes you only want one number for one date - the close on the day before an earnings release, or the open on the day you bought. MarketXLS exposes each OHLC field as a single-cell historical function.

Point-in-time functions are the right tool when your workbook is organized one row per position with a purchase date, because each row needs a different date and a spilled array cannot do that. =DividendHistory() and =SplitHistory() matter for a different reason: they let you audit why an adjusted close differs from a raw close on a given day.

When to use adjusted close vs raw close

Use adjusted close for any return calculation that spans more than one date, and raw close for what traded on a single day. Mixing the two in one return calculation is a common source of wrong numbers.

Use raw close when:

• You are studying single-day behavior, like the close on the day before an earnings release.
• You are reproducing what a trader would have seen on the screen at that moment.
• You are working inside a single trading day and corporate actions are not a factor.

Use adjusted close when:

• You are calculating returns across any corporate action - splits, special dividends, regular dividends.
• You are computing CAGR, alpha, beta, volatility, or any statistic that requires a continuous return series.
• You are comparing performance across stocks that have different dividend policies.

Default to adjusted close for any cross-date calculation. Keep the raw close columns for inspection, not for arithmetic.

Edge Cases You Will Hit

A few situations come up often enough to mention.

Weekends and holidays. If your reference date is not a trading day, =Close_Historical() returns NA by default. Add "Yes" as the third argument, for example =Close_Historical("SPY",DATE(2024,1,13),"Yes"), to get the most recent available close instead. You can also build a helper column with WORKDAY and an exchange holiday list.

Tickers that have changed. Symbols change after mergers, spin-offs, and listing transfers. If a historical function returns a blank or an error for an old date, check whether the ticker was different at that point in time. The corporate action history can usually point you to the previous identifier.

Indices vs ETFs. For broad market reference, prefer SPY over the S and P 500 index symbol. ETFs have clean historical data, dividends, and a daily NAV that lines up with what an investor could actually have held. The same historical functions accept index tickers, but ETFs and indices diverge slightly due to tracking error and dividend timing.

International tickers. MarketXLS covers US, Canadian, UK, German, and Australian markets. QM_ functions use QuoteMedia symbol formats for non-US listings; see the MarketXLS docs for the exact suffix or prefix per exchange. Prices are returned in the listing's local currency.

Manual notes. If you embed a manually researched note in a cell - for example, a one-line comment about an unusual move - keep it in a clearly labeled comment column. Separating live formulas from static notes makes a workbook age well.

Download the Historical Price Workbooks

Two ready-made workbooks cover the whole flow described above - snapshot dashboard, historical prices, return analysis, dividends and splits, and a volatility and drawdown sheet:

• - live formulas you repoint to any ticker
• - values pre-filled so you can read the layout without the add-in

Troubleshooting Excel Stock History Formula Issues

No Data Returned

• Verify the ticker symbol is correct. Use standard exchange symbols.
• Check that dates are in the correct format and represent valid trading days.
• Ensure MarketXLS is installed, activated, and connected to the internet.

Incomplete Data

• Some newer stocks may not have data for older date ranges.
• Weekends and holidays are excluded from daily data.
• If you expect 252 rows for a year but get fewer, holidays and market closures account for the difference.

#SPILL! Error

• The output area for =GetHistory() is blocked by existing data. Clear the cells below your formula to allow the data to spill.

Slow Performance

• Large date ranges (10+ years of daily data) produce thousands of rows. Use weekly or monthly periodicity for long-range analysis to reduce data volume.
• Avoid placing multiple =GetHistory() calls on the same sheet if each returns large datasets. Use separate sheets.

---

Frequently Asked Questions

Which Excel stock history formula should a beginner use?

Microsoft 365 users can start with the built-in =STOCKHISTORY() function at no extra cost. If you also need fundamentals, options, or a single close on a specific date in the same workbook, MarketXLS adds =GetHistory(), =QM_GetHistory(), and =Close_Historical().

Can I get intraday historical data with an Excel stock history formula?

Not with =GetHistory(), which supports daily, weekly, and monthly periodicity. For intraday bars, MarketXLS has QuoteMedia functions such as =QM_GetIntradayDataOneMinutes(), =QM_GetIntradayDataFiveMinutes(), and =QM_GetIntradayDataOneHour(). For live ticks, use =QM_Stream_Last() on a plan with real-time data (Advanced or Business for US stocks).

How far back can I pull historical stock data?

The available date range depends on the ticker and data source. Long-listed US stocks generally have the deepest history; newer listings and some international tickers have shorter histories. Test the earliest date you need with =Close_Historical() before building a long backtest.

Does the Excel stock history formula work with ETFs and mutual funds?

Yes. =GetHistory() and =QM_GetHistory() work with ETFs (e.g., SPY, QQQ, VTI) and many mutual funds. Enter the fund's ticker symbol just as you would a stock symbol.

How do I update historical data automatically?

Historical data updates when you click the Refresh button in the MarketXLS ribbon or when Excel recalculates. You can set up automatic recalculation intervals or use a simple VBA timer to refresh periodically.

Can I combine historical data from multiple stocks in one chart?

Yes. Pull =GetHistory() for each stock on separate sheets or in separate columns, then create a chart that references multiple data series. Normalize the prices to a common starting point (100) for fair visual comparison.

---

Getting Started with MarketXLS

MarketXLS adds =GetHistory(), =QM_GetHistory(), the single-date historical functions, and the rest of its 1000+ function library to Excel. Plans start at $70/month billed annually (Standard).

1. Check the pricing page and pick a plan.
2. Install the add-in: the Windows desktop add-in, or the Microsoft 365 add-in from Microsoft AppSource for Excel on Mac and Excel for the web (see choose your version).
3. Activate with your license key (Windows) or sign in with your MarketXLS account (Microsoft 365), then enter a history formula.

For the full data-table and validation workflow, continue with Historical Stock Data in Excel.

Disclaimer: None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.