Share New Research 
I developped a Google Sheets workbook that generates the S&P 500 members dating back to 2010, using the information provided by Wikipedia (see link). I'm using a slightly modified version of the sample Dropbox Universe Selection template provided here. In this sample backtest, I'm using LINQ in the On Data section to simply select the top 10 most expensive securities from the S&P 500 and buy/rebalance equal portions of each.
On the Info worksheet of the Google doc, the links to automatically download the CSVs are provided. You need to copy in the worksheet ID (from the URL) of your own version of the Google doc or the links will be incorrect.
Make sure you enter your backtest start date on the Info sheet of the Google doc or your backtest will most likely start will blank data for the first few months.
Note: There is definitely a couple discrepancies in the data. For example, Wikipedia tells us DLPH was added to the Index on December 21, 2012, and does not provide a date where it was removed. However, DLPH does not show up on the active members list. Overall, it's very close to the 500, and the error is always less than 5. I put a graph on the Info worksheet that tells you how many companies are tracked at each point of time, to give you an idea of you big/small the error is. The most recent count is 505, which is correct.
The spreadsheet can be found at this link. I recommend copying it to your own drive, and updating the worksheet ID.
Petter Hansson
This is awesome! Getting ahold of S&P500 universe is indeed not cheap normally.
AutomatedMachine
Thanks! Hopefully this comes in use for somebody. As a long time Excel user, I only fairly recently discovered the magic power of Google Sheets QUERY and ARRAYFORMULA functions. Truly awesome stuff.
I forgot to mention that the spreadsheet downloads data directly from Wikipedia, so it will reflect any recent changes added to the site. This also means that if the website tables are significantly changed, the spreadsheet will break. Something to keep in mind. I've already got a hard copy of the CSV backed up on my Google drive, just in case.
Quant Trader
I resolved it manually as it is a pitty QuantConnect does not support it;
// Select S&P500 stocks only: // Market capitalization must be greater than or equal to $6.1 billion USD // Annual dollar value traded to float-adjusted market capitalization is greater than 1.0 // Minimum monthly trading volume of 250,000 shares in each of the six months leading up to the evaluation date public IEnumerable<Symbol> CoarseSelectionFunction(IEnumerable<CoarseFundamental> coarse) { //const decimal MarketCapitalization = 2100000000m; //^not supported by Quantconnect; const decimal DollarVolume = 20000000m; if (!_rebalanceFlag) return Enumerable.Empty<Symbol>(); //https://www.barchart.com/ List<string> sp500StockList = new List<string>() { "A", "AAL", "AAP", "AAPL", "ABBV", "ABC", "ABT", "ACN", "ADBE", "ADI", "ADM", "ADP", "ADS", "ADSK", "AEE", "AEP", "AES", "AET", "AFL", "AGN", "AIG", "AIV", "AIZ", "AJG", "AKAM", "ALB", "ALGN", "ALK", "ALL", "ALLE", "ALXN", "AMAT", "AMD", "AME", "AMG", "AMGN", "AMP", "AMT", "AMZN", "ANDV", "ANSS", "ANTM", "AON", "AOS", "APA", "APC", "APD", "APH", "APTV", "ARE", "ARNC", "ATVI", "AVB", "AVGO", "AVY", "AWK", "AXP", "AYI", "AZO", "BA", "BAC", "BAX", "BBT", "BBY", "BDX", "BEN", "BF.B", "BHF", "BHGE", "BIIB", "BK", "BLK", "BLL", "BMY", "BRK.B", "BSX", "BWA", "BXP", "C", "CA", "CAG", "CAH", "CAT", "CB", "CBG", "CBOE", "CBS", "CCI", "CCL", "CDNS", "CELG", "CERN", "CF", "CFG", "CHD", "CHK", "CHRW", "CHTR", "CI", "CINF", "CL", "CLX", "CMA", "CMCSA", "CME", "CMG", "CMI", "CMS", "CNC", "CNP", "COF", "COG", "COL", "COO", "COP", "COST", "COTY", "CPB", "CRM", "CSCO", "CSRA", "CSX", "CTAS", "CTL", "CTSH", "CTXS", "CVS", "CVX", "CXO", "D", "DAL", "DE", "DFS", "DG", "DGX", "DHI", "DHR", "DIS", "DISCA", "DISCK", "DISH", "DLR", "DLTR", "DOV", "DPS", "DRE", "DRI", "DTE", "DUK", "DVA", "DVN", "DWDP", "DXC", "EA", "EBAY", "ECL", "ED", "EFX", "EIX", "EL", "EMN", "EMR", "EOG", "EQIX", "EQR", "EQT", "ES", "ESRX", "ESS", "ETFC", "ETN", "ETR", "EVHC", "EW", "EXC", "EXPD", "EXPE", "EXR", "F", "FAST", "FB", "FBHS", "FCX", "FDX", "FE", "FFIV", "FIS", "FISV", "FITB", "FL", "FLIR", "FLR", "FLS", "FMC", "FOX", "FOXA", "FRT", "FTI", "FTV", "GD", "GE", "GGP", "GILD", "GIS", "GLW", "GM", "GOOG", "GOOGL", "GPC", "GPN", "GPS", "GRMN", "GS", "GT", "GWW", "HAL", "HAS", "HBAN", "HBI", "HCA", "HCN", "HCP", "HD", "HES", "HIG", "HII", "HLT", "HOG", "HOLX", "HON", "HP", "HPE", "HPQ", "HRB", "HRL", "HRS", "HSIC", "HST", "HSY", "HUM", "IBM", "ICE", "IDXX", "IFF", "ILMN", "INCY", "INFO", "INTC", "INTU", "IP", "IPG", "IQV", "IR", "IRM", "ISRG", "IT", "ITW", "IVZ", "JBHT", "JCI", "JEC", "JNJ", "JNPR", "JPM", "JWN", "K", "KEY", "KHC", "KIM", "KLAC", "KMB", "KMI", "KMX", "KO", "KORS", "KR", "KSS", "KSU", "L", "LB", "LEG", "LEN", "LH", "LKQ", "LLL", "LLY", "LMT", "LNC", "LNT", "LOW", "LRCX", "LUK", "LUV", "LYB", "M", "MA", "MAA", "MAC", "MAR", "MAS", "MAT", "MCD", "MCHP", "MCK", "MCO", "MDLZ", "MDT", "MET", "MGM", "MHK", "MKC", "MLM", "MMC", "MMM", "MNST", "MO", "MON", "MOS", "MPC", "MRK", "MRO", "MS", "MSFT", "MSI", "MTB", "MTD", "MU", "MYL", "NAVI", "NBL", "NCLH", "NDAQ", "NEE", "NEM", "NFLX", "NFX", "NI", "NKE", "NLSN", "NOC", "NOV", "NRG", "NSC", "NTAP", "NTRS", "NUE", "NVDA", "NWL", "NWS", "NWSA", "O", "OKE", "OMC", "ORCL", "ORLY", "OXY", "PAYX", "PBCT", "PCAR", "PCG", "PCLN", "PDCO", "PEG", "PEP", "PFE", "PFG", "PG", "PGR", "PH", "PHM", "PKG", "PKI", "PLD", "PM", "PNC", "PNR", "PNW", "PPG", "PPL", "PRGO", "PRU", "PSA", "PSX", "PVH", "PWR", "PX", "PXD", "PYPL", "QCOM", "QRVO", "RCL", "RE", "REG", "REGN", "RF", "RHI", "RHT", "RJF", "RL", "RMD", "ROK", "ROP", "ROST", "RRC", "RSG", "RTN", "SBAC", "SBUX", "SCG", "SCHW", "SEE", "SHW", "SIG", "SJM", "SLB", "SLG", "SNA", "SNI", "SNPS", "SO", "SPG", "SPGI", "SRCL", "SRE", "STI", "STT", "STX", "STZ", "SWK", "SWKS", "SYF", "SYK", "SYMC", "SYY", "T", "TAP", "TDG", "TEL", "TGT", "TIF", "TJX", "TMK", "TMO", "TPR", "TRIP", "TROW", "TRV", "TSCO", "TSN", "TSS", "TWX", "TXN", "TXT", "UA", "UAA", "UAL", "UDR", "UHS", "ULTA", "UNH", "UNM", "UNP", "UPS", "URI", "USB", "UTX", "V", "VAR", "VFC", "VIAB", "VLO", "VMC", "VNO", "VRSK", "VRSN", "VRTX", "VTR", "VZ", "WAT", "WBA", "WDC", "WEC", "WFC", "WHR", "WLTW", "WM", "WMB", "WMT", "WRK", "WU", "WY", "WYN", "WYNN", "XEC", "XEL", "XL", "XLNX", "XOM", "XRAY", "XRX", "XYL", "YUM", "ZBH", "ZION", "ZTS"}; // Market capitalization must be greater than or equal to $6.1 billion USD, Traded shares x Price var filtered = from x in coarse join SP500 in sp500StockList on x.Symbol.Value equals SP500 let momentums = _momentums.GetOrAdd(x.Symbol, sym => new MomentumSelectionData(MomentumWindow, StockMovingAverageWindow)) // Update returns true when the indicators are ready, so don't accept until they are where momentums.Update(x.EndTime, x.Price) && momentums.AnnualizedSlope > 0 where x.DollarVolume >= DollarVolume && //x.Volume > 250000 && x.HasFundamentalData //&& //x.Price > 20 //orderby x.DollarVolume descending orderby momentums.AnnualizedSlope descending select x; var topCoarse = filtered.Take(_universeSelectMaxStocks); return topCoarse.Select(x => x.Symbol); }Quant Trader
Thank you @AutomatedMachine
I used your work / Excel with some modifications. I backtested with a fixed SP500 range through 2000 - 2018 and without using the actual SP500 the results are less, than using the SP500 in that period. So I recommend backtesting with accurate SP500 stock universe. I do use your list in the coarsefunction a bit different. Here is my code.
/// <summary> /// Get SP500 symbols through the years /// Credits to AutomatedMachine /// </summary> /// <param name="dateTime"></param> /// <returns></returns> public List<string> GetSP500SymbolList(DateTime dateTime) { if (LiveMode) return _sp500FixedStockList; if (_sp500List == null) { _sp500List = new Dictionary<DateTime, List<string>>(); //url with sp500 csv file string url = @"https://docs.google.com/spreadsheets/d/12lUYUHYWNYhLBvJGcsXp2ZBXdJ7g3-Sspwvg0L62NAk/gviz/tq?tqx=out:csv&sheet=Backtest_CSV"; using (var client = new WebClient()) { // fetch the file from dropbox var file = client.DownloadString(url); file = Regex.Replace(file, "\"", string.Empty); // split the file into lines and add to our cache foreach (var line in file.Split(new[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries)) { var csv = line.ToCsv(); var date = DateTime.ParseExact(csv[0], "yyyy-MM-dd", null); //2017-09-18 var symbols = csv.Skip(1).ToList(); _sp500List[date] = symbols; } } } var result = _sp500List.OrderByDescending(x => x.Key).FirstOrDefault(x => x.Key < dateTime).Value; if(result == null) result = _sp500List.OrderBy(x => x.Key).FirstOrDefault().Value; return result; }And my CoarseFunction:
#region data selection /// <summary> /// Select S&P500 stocks only: /// Market capitalization must be greater than or equal to $6.1 billion USD /// Annual dollar value traded to float-adjusted market capitalization is greater than 1.0 /// Minimum monthly trading volume of 250,000 shares in each of the six months leading up to the evaluation date /// </summary> /// <param name="coarse"></param> /// <returns></returns> public IEnumerable<Symbol> CoarseSelectionFunction(IEnumerable<CoarseFundamental> coarse) { //const decimal MarketCapitalization = 2100000000m; //^not supported by Quantconnect; var sp500List = GetSP500SymbolList(Time.Date); const decimal DollarVolume = 21000000m; //if (!_rebalanceFlag) return Enumerable.Empty<Symbol>(); // Market capitalization must be greater than or equal to $6.1 billion USD, Traded shares x Price var filtered = from x in coarse where x.HasFundamentalData where x.DollarVolume >= DollarVolume //where x.Volume > 250000 where x.Price > 10 join SP500 in sp500List on x.Symbol.Value equals SP500 select x; var topCoarse = filtered.Take(_universeSelectMaxStocks); return topCoarse.Select(x => x.Symbol); } //Select S&P500 stocks only: //Not possible at quantconnect so try to select common stock, primary share and dividend share public IEnumerable<Symbol> FineSelectionFunction(IEnumerable<FineFundamental> fine) { //if (!_rebalanceFlag) return Enumerable.Empty<Symbol>(); var filtered = from x in fine where x.SecurityReference.SecurityType == "ST00000001" && x.SecurityReference.IsPrimaryShare select x; return filtered.Select(x => x.Symbol); } #endregion data selectionAutomatedMachine
The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.
To unlock posting to the community forums please complete at least 30% of Boot Camp.
You can continue your Boot Camp training progress from the terminal. We hope to see you in the community soon!