Excel Stock History Formula: Complete Guide to Historical Stock Data

Excel stock history formula functions give you the power to pull years of historical stock prices directly into your spreadsheet for analysis, charting, and backtesting. Whether you are calculating long-term returns, building performance charts, or testing a trading strategy against historical data, having reliable historical stock data inside Excel is essential.

This guide covers every approach to getting historical stock data in Excel — from Microsoft's built-in STOCKHISTORY function to MarketXLS's professional-grade =GetHistory() and =QM_GetHistory() formulas. You will learn the exact syntax, parameters, practical examples, and how to combine historical data with current market data for comprehensive stock analysis.

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 =GetHistory() function in MarketXLS is the most flexible Excel stock history formula for pulling historical price data. It returns a complete table of OHLCV data for any ticker and date range.

Syntax

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

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

Returns approximately 252 rows of daily OHLCV data for AAPL.

Weekly Data for Five Years

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

Returns approximately 260 rows of weekly price data.

Monthly Data for a Decade

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

Returns approximately 120 rows of monthly price data.

Using Cell References

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

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() is an alternative Excel stock history formula that retrieves data from the QuoteMedia data feed.

Syntax

=QM_GetHistory("ticker")

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)

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

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

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

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

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

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

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

Sheet 4: Monthly History

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

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. For example, a stock with a 3% dividend yield held for 10 years would have approximately 30% more total return than price return alone, before 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.

Adjusted Close vs Raw Close: The Single Most Common Mistake

Most analysts who blow up a return number do it by mixing adjusted and raw closes. The fix is mechanical.

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, the historical functions return the next available trading session's value. If you need the most recent close on or before that date, use the date one day earlier, or 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 supports exchange-prefixed tickers for most major venues. Pass them as a single string just like a US ticker. The historical functions return the local currency price unless you explicitly use the FX-converted helpers.

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

What is the best Excel stock history formula for beginners?

For Office 365 users, the built-in =STOCKHISTORY() function is the simplest starting point. For more control over date ranges, periodicity, and data quality, MarketXLS's =GetHistory() is the most versatile Excel stock history formula available.

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

=GetHistory() supports daily, weekly, and monthly periodicity. For intraday analysis, use =Stream_Last() for real-time data or check the MarketXLS functions library for intraday-specific functions.

How far back can I pull historical stock data?

The available date range depends on the ticker and data source. Most major US stocks have daily data going back 20+ years. Newer stocks or international tickers may have shorter histories.

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

Ready to use the most powerful Excel stock history formula tools available? MarketXLS gives you =GetHistory(), =QM_GetHistory(), and over 400 other financial functions — all inside Excel.

1. Visit MarketXLS.com and check the pricing page for subscription options.
2. Download and install the add-in.
3. Activate your license and start pulling historical stock data instantly.

From historical prices to real-time quotes to fundamental analysis, MarketXLS is the complete financial data solution for Excel users.

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.