5

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.

lars20070
  • 53
  • 1
  • 1
  • 4

5 Answers5

5

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

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

where %5E is ^ ( ^GSPC )

Magno C
  • 166
  • 1
  • 2
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

Bence Mélykúti
  • 121
  • 3
  • 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 '20 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. – Bence Mélykúti Sep 24 '20 at 19:58
2

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/

jayp
  • 121
  • 2
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')
rangeseeker
  • 113
  • 2
  • Thanks @rangeseeker. I previously looked into yfinance. But since I am working from R, I would prefer a REST API. – lars20070 Sep 25 '20 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 '20 at 02: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}'
Eugene Gr. Philippov
  • 101
  • 2