free REST API for daily end-of-day S&P 500 index

Question

I am looking for a free API to retrieve the daily end-of-day S&P 500 index for the past year.

There are plenty of APIs which provide the individual stocks, but the indices are not for free. There are also many historic S&P 500 index datasets, but they do not cover the very recent couple of days.

Answer 1

By looking into yFinance source code I've found this query:

https://query2.finance.yahoo.com/v8/finance/chart/%5EGSPC

where %5E is ^ ( ^GSPC )

Answer 2

The official website of the index is https://www.spglobal.com/spdji/en/indices/equity/sp-500/#overview.

If you hover with your mouse over Export above the graph, then you can recover a link, with which I can download the last ten years' worth of closing prices up until yesterday in XLS format (I've removed the hostIdentifier part):

https://www.spglobal.com/spdji/en/idsexport/file.xls?redesignExport=true&selectedModule=PerformanceGraphView&selectedSubModule=Graph&yearFlag=tenYearFlag&indexId=340

Answer 3

The St. Louis Fed has a very easy-to-use API for this. The format is:

https://api.stlouisfed.org/fred/series/observations?series_id=SP500&api_key=KEY_GOES_HERE&file_type=json&observation_start=YYYY-MM-DD&observation_end=YYYY-MM-DD

A visualized graph of it is here: https://fred.stlouisfed.org/series/SP500

API docs here: https://fred.stlouisfed.org/docs/api/fred/series_observations.html

Sign up for a FRED account (to get your API key) here: https://fredaccount.stlouisfed.org/login/secure/

Answer 4

Sorry that I have to "answer" you question when I should actually "comment" to ask if you're okay with using a python module instead of a REST API (not enough reputation).
Anyway, I love playing with stock data, here is my method for getting it for free in python and exporting it to a csv.

pip install yfinance

import yfinance as yf
import pandas as pd

sp500 = yf.Ticker('^GSPC')
sp500_hist = sp500.history(period='1y')
sp500_hist.to_csv('sp500_1y.csv')

This will provide you with daily data, open high low close and volume. Not sure how familiar you are with pandas, but if you only want closing prices, subset your dataframe like this:

sp500_hist[['Close']].to_csv('sp500_1y.csv')

or if you want volume as well:

sp500_hist[['Close', 'Volume']].to_csv('sp500_1y.csv')

Answer 5

Here's a sample function to extract some data cell based on the answer by Bence Mélykúti:

from requests import Request, Session
from requests.exceptions import ConnectionError, Timeout, TooManyRedirects
import pandas as pd

def fetch_sp500_index():
    spglobal_hostid = get_parameter('spglobal_hostid')
    url = f'https://www.spglobal.com/spdji/en/idsexport/file.xls?hostIdentifier={spglobal_hostid}&redesignExport=true&languageId=1&selectedModule=PerformanceTableView&selectedSubModule=Daily&indexId=340'
    parameters = {}
    headers = {}
    try:
        print('fetch_sp500_index: get url=' + url, flush=True)
        session = Session()
        session.headers.update(headers)
        response = session.get(url, params=parameters)
        df = pd.read_excel(response.content)
        print(f"{__name__}: df:\n{df}")
        print(f"{__name__}: df.keys():\n{df.keys()}")
        print(f"{__name__}: col count:\n{len(df.keys())}")
        price_row_found = False
        price_col_found = False
        price_row_index = None
        price_col_index = None
        col_index = 0
        for col_name in df.keys():
            col = df[col_name]
            row_index = 0
            for cell in col:
                value = f'{cell}'.strip()
                if value == 'Price Return\nS&P 500':
                    price_row_found = True
                    price_row_index = row_index
                    print("price_row_index: ", price_row_index)
                else:
                    # print(f"val: '{value}'")
                    if value == 'Index Level':
                        price_col_found = True
                        price_col_index = col_index
                        print("price_col_index: ", price_col_index)
                if price_row_found and price_col_found:
                    break
                row_index = row_index + 1
            if price_row_found and price_col_found:
                break
            col_index = col_index + 1
        if not price_row_found or not price_col_found:
            return f'S&P500 Fetch Error: Unrecognized Format'
        else:
            return f'S&P500 Index: {"{:0,.2f}".format(float(df.iloc[price_row_index, price_col_index]))}'
    except (ConnectionError, Timeout, TooManyRedirects) as e:
        print(__name__, e, flush=True)
        return f'S&P500 Fetch Error: {e}'