Notebook
In [5]:
from sqlalchemy import or_

fundamentals = init_fundamentals()

sp_500 = get_fundamentals(
                        query(fundamentals.valuation.market_cap,
                              fundamentals.company_reference.primary_exchange_id)
                        .filter(fundamentals.valuation.market_cap > 4e9)
                        .filter(fundamentals.company_reference.country_id == "USA")
                        .filter(or_(fundamentals.company_reference.primary_exchange_id == "NAS", 
                                    fundamentals.company_reference.primary_exchange_id == "NYS"))
                        .order_by(fundamentals.valuation.market_cap.desc())
                        .limit(500),
                        '2009-01-02') # so we're not biased by survivorship, use the first date in the range
    
print len(sp_500.columns)
sp_500
    

74
Out[5]:
Equity(5061 [MSFT]) Equity(26578 [GOOG_L]) Equity(1900 [CSCO]) Equity(24 [AAPL]) Equity(3951 [INTC]) Equity(368 [AMGN]) Equity(6295 [QCOM]) Equity(1637 [CMCS_A]) Equity(3212 [GILD]) Equity(1638 [CMCS_K]) ... Equity(4485 [LLTC]) Equity(24124 [WYNN]) Equity(19725 [NVDA]) Equity(14014 [CTXS]) Equity(9156 [FLIR]) Equity(8352 [XRAY]) Equity(36929 [DISC_B]) Equity(8857 [ORLY]) Equity(3460 [HAS]) Equity(33718 [TFSL])
market_cap 1.79868e+11 9.68341e+10 9.54379e+10 8.23776e+10 8.15389e+10 6.04643e+10 5.55742e+10 4.86252e+10 4.65635e+10 4.65223e+10 ... 4.4235e+09 4.39557e+09 4.3341e+09 4.28153e+09 4.26208e+09 4.20642e+09 4.18798e+09 4.13516e+09 4.06046e+09 4.00351e+09
primary_exchange_id NAS NAS NAS NAS NAS NAS NAS NAS NAS NAS ... NAS NAS NAS NAS NAS NAS NAS NAS NAS NAS

2 rows × 74 columns

In [6]:
sp_500 = get_fundamentals(
                        query(fundamentals.valuation.market_cap,
                              fundamentals.company_reference.primary_exchange_id)
                        .filter(fundamentals.valuation.market_cap > 4e9)
                        .filter(fundamentals.company_reference.country_id == "USA")
                        .filter(or_(fundamentals.company_reference.primary_exchange_id == "NAS", 
                                    fundamentals.company_reference.primary_exchange_id == "NYS"))
                        .order_by(fundamentals.valuation.market_cap.desc())
                        .limit(500),
                        '2015-04-01') # so we're not biased by survivorship, use the first date in the range
    
print len(sp_500.columns)
sp_500

498
Out[6]:
Equity(24 [AAPL]) Equity(26578 [GOOG_L]) Equity(46631 [GOOG]) Equity(8347 [XOM]) Equity(1091 [BRK_A]) Equity(11100 [BRK_B]) Equity(5061 [MSFT]) Equity(4151 [JNJ]) Equity(8151 [WFC]) Equity(8229 [WMT]) ... Equity(26758 [DLR]) Equity(8383 [FL]) Equity(26335 [ALNY]) Equity(46694 [IMS]) Equity(166 [AES]) Equity(41872 [VER]) Equity(3472 [HBAN]) Equity(559 [ASH]) Equity(7715 [UDR]) Equity(13176 [CAM])
market_cap 7.36073e+11 3.81921e+11 3.75724e+11 3.59191e+11 3.59162e+11 3.40923e+11 3.36027e+11 2.82359e+11 2.8219e+11 2.6601e+11 ... 9.05799e+09 9.03812e+09 9.02767e+09 8.99118e+09 8.95156e+09 8.92211e+09 8.91838e+09 8.91505e+09 8.90154e+09 8.87439e+09
primary_exchange_id NAS NAS NAS NYS NYS NYS NAS NYS NYS NYS ... NYS NYS NAS NYS NYS NAS NAS NYS NYS NYS

2 rows × 498 columns

In [ ]: