S&P 500 Historical Data: How to Download and Analyze in Excel

The fastest way to get S&P 500 history into Excel is the built-in STOCKHISTORY function on Microsoft 365, or a free CSV from Stooq or FRED. One catch matters more than the method: most free sources give price-only data that excludes dividends, which understates true returns by roughly two percentage points a year.

Key takeaways

• Excel 365's STOCKHISTORY pulls prices with a single formula, but it does not reliably cover the S&P 500 index itself. It works best on a fund proxy like SPY.
• Yahoo Finance no longer offers a CSV download for the index (^GSPC) because of index licensing, though SPY and individual stocks still download fine.
• Stooq and FRED are the most dependable free CSV sources. Stooq has the deepest history; FRED's SP500 series only goes back about 10 years and is price-only.
• Nearly every free feed reports the price index, not total return. Add the SP500TR series or use a fund's adjusted close if you want dividends included.

Where to get the data

Download a CSV from Yahoo Finance

Yahoo dropped the download link for the index, so use the SPY ETF as a close stand-in. SPY tracks the S&P 500 and its adjusted close already folds in dividends.

1. Go to Yahoo Finance and search for SPY.
2. Open the Historical Data tab.
3. Set your date range and frequency (daily, weekly, or monthly).
4. Click Apply, then Download to save the CSV.
5. In Excel, open the Data tab, choose From Text/CSV, select the file, and click Load.

If you need the raw index rather than a fund, pull ^SPX from Stooq instead. Stooq serves a direct CSV file, so you can even paste its download URL straight into Power Query for refreshable data.

Pull data with STOCKHISTORY in Excel 365

STOCKHISTORY is the cleanest option if you have a qualifying Microsoft 365 subscription (Personal, Family, Business Standard, or Business Premium). It does not exist in the one-time-purchase versions like Excel 2021 or 2024, and Microsoft notes that many indices, including the S&P 500, are not available as historical data even when a quote shows. For that reason, point it at SPY.

The syntax is:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], ...)

A working example that returns daily dates and closes for 2024:

=STOCKHISTORY("SPY", "1/1/2024", "12/31/2024", 0, 1, 0, 1)

1. Click an empty cell where the top-left of the results should land.
2. Type the formula, using 0 for daily, 1 for weekly, or 2 for monthly in the interval slot.
3. Set the property codes: 0 Date, 1 Close, 2 Open, 3 High, 4 Low, 5 Volume.
4. Press Enter. The result spills into a dynamic array you can chart or feed into formulas.

Because the output refreshes with the workbook, you get a live history without re-downloading anything.

The price-versus-total-return trap

This is the mistake that quietly corrupts most spreadsheet analysis. The headline S&P 500 number is a price index. It tracks share prices only and ignores the dividends those 500 companies pay out. Total return counts price gains plus reinvested dividends, and over the long run dividends have added on the order of two percentage points a year. Compound that gap across decades and a price-only backtest can understate real growth by a wide margin.

Two clean fixes:

• Use the SP500TR total return series on FRED instead of the price-only SP500 series.
• Use a fund's adjusted close (SPY on Yahoo Finance), which bakes dividends back into the price line.

Whenever you compare the index against another asset or benchmark, make sure both sides use the same basis. Mixing a price index against a total-return figure is one of the most common ways a comparison ends up wrong. It is the same reason a fund's return can look different from the index it tracks, a point worth keeping in mind when you weigh SPY against the S&P 500 or run a longer study like the S&P 500 versus the Nasdaq 100.

Once your data is in Excel, the analysis is straightforward: compute period returns, CAGR, drawdowns, and moving averages from the close column. For more background on the index itself and how it fits a broader portfolio, start with our S&P 500 guide and the wider investing hub.

Frequently asked questions

What are the most reliable free sources for S&P 500 historical data?

Stooq and FRED are the most dependable free CSV sources. Stooq has the deepest history and serves a direct CSV file for the index, while FRED's SP500 series only goes back about 10 years and is price-only. Yahoo Finance no longer offers a CSV download for the index itself, though SPY and individual stocks still download fine.

Can Excel's STOCKHISTORY function pull the S&P 500 index?

STOCKHISTORY does not reliably cover the S&P 500 index itself; Microsoft notes many indices are unavailable as historical data even when a quote shows, so point it at the SPY fund instead. It also requires a qualifying Microsoft 365 subscription and does not exist in one-time-purchase versions like Excel 2021 or 2024.

Why does price-only S&P 500 data understate returns?

Price-only data tracks share prices and ignores dividends, which have added on the order of two percentage points a year over the long run, so a price-only backtest can understate real growth by a wide margin. Fix it by using the SP500TR total return series on FRED, or a fund's adjusted close like SPY on Yahoo Finance, which bakes dividends back in.

How do you download SPY historical data from Yahoo Finance into Excel?

Search Yahoo Finance for SPY, open the Historical Data tab, set your date range and frequency, click Apply, then Download to save the CSV. In Excel, open the Data tab, choose From Text/CSV, select the file, and click Load. SPY's adjusted close already folds in dividends, making it a close stand-in for the index.