S&P 500®
Working with S&P 500 data on Nuvolos

S&P 500® index level history

There are multiple possible sources to obtain S&P 500® index level history when working on Nuvolos:

S&P Dow Jones Indices (SPDJI) [license required]

If you have access to the SPDJI dataset on Nuvolos, the INDEX_LEVELS and INDEX_LEVELS_V1 tables contain the official S&P 500® index level history, please distribute these two tables from the latest available SPDJI dataset snapshot to your working instance.
You can use the following query to obtain the time series:
1
SELECT DATE_OF_INDEX, INDEX_VALUE
2
FROM INDEX_LEVELS
3
WHERE INDEX_CODE='500'
4
UNION
5
SELECT DATE_OF_INDEX, INDEX_VALUE
6
FROM INDEX_LEVELS_V1
7
WHERE INDEX_CODE='500'
8
ORDER BY DATE_OF_INDEX ASC;
Copied!

Compustat North America or Compustat Global [license required]

If you have access to Compustat on Nuvolos, please distribute the IDX_DAILY table from the latest dataset snapshot to your working instance.
You can use the following query to obtain the time series:
1
SELECT DATADATE, PRCCD
2
FROM IDX_DAILY
3
WHERE GVKEYX='000003'
4
ORDER BY DATADATE ASC;
Copied!

CRSP US Stock and Indexes [license required]

If you have access to CRSP US Stock and Indexes on Nuvolos, please distribute the DSI table from the latest dataset to your working instance.
You can use the following query to obtain the time series:
1
SELECT DATE, SPINDX
2
FROM DSI
3
WHERE SPINDX IS NOT NULL
4
ORDER BY DATE ASC;
Copied!

Python and Yahoo Finance

Please start a Jupyter or Visual Studio Code application in Nuvolos and run the following command to install the pandas-datareader package in Nuvolos:
1
mamba install -y -c conda-forge --freeze-installed pandas-datareader
Copied!
Then you can use the following code to retrieve historical S&P 500® index levels as a pandas DataFrame:
1
from pandas_datareader import data as pdr
2
from datetime import date
3
4
df = pdr.get_data_yahoo("^GSPC", start="1928-01-03", end=date.today())
Copied!

S&P 500® index historical constituents

There are multiple possible ways to obtain the historical constituents of the S&P 500 index when working on Nuvolos:

S&P Dow Jones Indices (SPDJI) [license required]

If you have access to the SPDJI dataset on Nuvolos, the CONSTITUENT_DETAILS and CONSTITUENT_DETAILS_V1 tables contain the official S&P 500® constituents history, please distribute these two tables from the latest available SPDJI dataset snapshot to your working instance.
You can use the following query to obtain the time series:
1
SELECT
2
SEQUENCE_NUMBER,INDEX_NAME,INDEX_CODE,INDEX_KEY,EFFECTIVE_DATE,COMPANY,RIC,BLOOMBERG_TICKER,CUSIP,ISIN,SEDOL,TICKER,GV_KEY,STOCK_KEY,GICS_CODE,MIC,COUNTRY AS COUNTRY_OF_DOMICILE,REGION,SIZE,CAP_RANGE,CURRENCY_CODE,LOCAL_PRICE,FX_RATE,SHARES_OUTSTANDING,MARKET_CAP,IWF,AWF,GROWTH,VALUE,INDEX_SHARES,INDEX_MARKET_CAP,INDEX_WEIGHT,DAILY_PRICE_RETURN,DAILY_TOTAL_RETURN,DIVIDEND,SOURCE_FILE_NAME
3
FROM CONSTITUENT_DETAILS_V1
4
WHERE INDEX_CODE='500'
5
UNION
6
SELECT
7
SEQUENCE_NUMBER,INDEX_NAME,INDEX_CODE,INDEX_KEY,EFFECTIVE_DATE,COMPANY,RIC,BLOOMBERG_TICKER,CUSIP,ISIN,SEDOL,TICKER,GV_KEY,STOCK_KEY,GICS_CODE,MIC,COUNTRY_OF_DOMICILE,REGION,SIZE,CAP_RANGE,CURRENCY_CODE,LOCAL_PRICE,FX_RATE,SHARES_OUTSTANDING,MARKET_CAP,IWF,AWF,GROWTH,VALUE,INDEX_SHARES,INDEX_MARKET_CAP,INDEX_WEIGHT,DAILY_PRICE_RETURN,DAILY_TOTAL_RETURN,DIVIDEND,SOURCE_FILE_NAME
8
FROM CONSTITUENT_DETAILS
9
WHERE INDEX_CODE='500'
10
ORDER BY EFFECTIVE_DATE ASC, SEQUENCE_NUMBER ASC;
Copied!

Compustat North America (up to 2020-03-31) [license required]

If you have access to Compustat NA on Nuvolos, please distribute the SPIDX_CST table from the latest dataset snapshot to your working instance.
You can use the following query to obtain the time series:
1
SELECT DATADATE, GVKEY, IID, CUSIPX
2
FROM SPIDX_CST
3
WHERE INDEXID='500'
4
ORDER BY DATADATE ASC;
Copied!

CRSP US Stock and Indexes [license required]

If you have access to CRSP US Stock and Indexes on Nuvolos, please distribute the INDEX_MEMBERSHIP and NAME_HISTORY tables from the latest dataset to your working instance.
CRSP makes the following note in their documentation on working with CRSP S&P 500® indices:
Due to differences in handling mergers, reorganizations, and other major corporate actions, CRSP data and the S&P 500® universe do not always have a one-to-one mapping. In some cases this results in a short period where CRSP is missing prices or has multiple prices per company listed by S&P.
The Count of Securities Used is not always 500 (90 prior to March 1957) due to missing prices. Known reasons for missing prices are when-issued trading, halts, and suspensions.
You can use the following query to obtain the time series:
1
SET
2
NUM_DAYS = (
3
SELECT
4
DATEDIFF(DAY,
5
TO_DATE('1926-01-03',
6
'YYYY-MM-DD'),
7
CURRENT_DATE()+ 1));
8
9
WITH DATES AS (
10
SELECT
11
DATEADD(
12
DAY,
13
'-' || ROW_NUMBER() OVER (
14
ORDER BY NULL),
15
DATEADD(DAY,
16
'+1',
17
CURRENT_DATE())
18
) AS DATE
19
FROM
20
TABLE (GENERATOR(ROWCOUNT => ($NUM_DAYS))))
21
SELECT
22
D.DATE,
23
IM.KYPERMNO,
24
NH.TICKER,
25
NH.COMNAM,
26
NH.NCUSIP,
27
NH.NCUSIP9
28
FROM
29
DATES D
30
INNER JOIN INDEX_MEMBERSHIP IM
31
ON
32
D.DATE BETWEEN IM.MBRDT AND IFNULL(IM.MBRENDDT, '2099-12-31')
33
INNER JOIN NAME_HISTORY NH
34
ON
35
IM.KYPERMNO = NH.KYPERMNO
36
AND D.DATE BETWEEN NH.NAMEDT AND IFNULL(NH.NAMEENDDT, '2099-12-31')
37
WHERE
38
IM.KEYSET = 1100500
39
ORDER BY
40
D.DATE,
41
IM.KYPERMNO;
Copied!
Last modified 14d ago