4

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.

4 Answers 4

2

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

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

where %5E is ^ ( ^GSPC )

2
  • Great. Works for me. That allows me to get the S&P 500 from any language including R. Thanks a lot!
    – lars20070
    May 8 at 5:40
  • Jeez! Are you looking for this since 2020?
    – Magno C
    May 9 at 16:43
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

2
  • Thanks. But I need a REST API, csv or json. Or can you please explain how you imported the data with Python or R? Thanks.
    – lars20070
    Sep 24, 2020 at 17:33
  • 2
    In Python, you can download files using the package urllib.request. Using Pandas, you can easily open XLS files: pd.read_excel(). There you probably need the parameters header, index_col. Sep 24, 2020 at 19:58
0

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')
2
  • Thanks @rangeseeker. I previously looked into yfinance. But since I am working from R, I would prefer a REST API.
    – lars20070
    Sep 25, 2020 at 10:50
  • yfinance is certainly the best way to get the S&P 500 index in Python. Yahoo decommissioned their REST API and yfinance somehow fixed that. Wrapping yfinance with reticulate might be the best way to get the S&P 500 into R. Seems there is no clear REST API.
    – lars20070
    Sep 26, 2020 at 2:43
0

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}'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.