Quantopian's community platform is shutting down. Please read this post for more information and download your code.
Back to Community
Get rid of multiple share classes in fundamentals query
AimFor Clarity
posted Apr 27, 2015
Using Data Value

I'd like to get the say 20 largest market cap securities, but the problem i have with the query is that it returns them multiple times in diff share classes.
Eg, the code below returns:
0: AAPL
3: BRK_A
4: GOOG_L
5: GOOG
6: BRK_B
16: RDS_B
18: RDS_A
36: CMCS_A
37: CMCS_K

Does anyone know how to get around this problem? id like the the regular retail investor classes. maybe there's a special flag in the query?

def before_trading_start(context):  
    """  
        Called before the start of each trading day (handle_data) and updates our universe with the securities and values found from fetch_fundamentals  
    """  
    #: Reference fundamentals in a shorter variable  
    f = fundamentals  
    #: SQLAlchemy  
    fundamental_df = get_fundamentals(  
        query(  
            f.valuation.market_cap,  
            f.valuation_ratios.pe_ratio,  
            f.asset_classification.morningstar_sector_code,  
            f.operation_ratios.roa,  
            f.cash_flow_statement.operating_cash_flow  
        )  
#        .filter(fundamentals.valuation.market_cap != None)  
        .order_by(fundamentals.valuation.market_cap.desc())  
        .limit(40)  
    )  
    # the problem here is we get duplicate multple calsses of the shares  
    secs = np.array([s.symbol for s in fundamental_df.columns.values])  
    for i, s in enumerate(secs):  
        if s[-2] is '_':  
            if s[:-2] in secs:  
                 raise('drop duplicate class shares')  
    #: Set our fundamentals into a context variable  
    context.fundamental_df = fundamental_df  
    #: Update our universe with the values  
    update_universe(fundamental_df.columns.values)
5 responses
Josh Payne
Apr 27, 2015

Maybe the is_primary_share field will help? https://www.quantopian.com/help/fundamentals

There are some other potentially helpful fields in the share class reference namespace.

Disclaimer

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 Quantopian. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. No information contained herein should be regarded as a suggestion to engage in or refrain from any investment-related course of action as none of Quantopian nor any of its affiliates is undertaking to provide investment advice, act as an adviser to any plan or entity subject to the Employee Retirement Income Security Act of 1974, as amended, individual retirement account or individual retirement annuity, or give advice in a fiduciary capacity with respect to the materials presented herein. If you are an individual retirement or other investor, contact your financial advisor or other fiduciary unrelated to Quantopian about whether any given investment idea, strategy, product or service described herein may be appropriate for your circumstances. All investments involve risk, including loss of principal. Quantopian 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.

Simon Thornington
Apr 29, 2015

Is there a way to filter out all MLPs and pass-throughs and keep only C-corps?

Cesar Correa
Apr 29, 2015

Hi there,

Not sure it yields the shares you really want but, this (.filter(fundamentals.share_class_reference.is_primary_share == True)) kind of works for me filtering just the main traded shares (e.g. BRK_B only, instead of BRK_B + BRK_A)

Regards.

AimFor Clarity
Apr 29, 2015

Based on a few other posts, I've been doing it like this: 

#: Reference fundamentals in a shorter variable  
    f = fundamentals  
    #: SQLAlchemy  
    fundamental_df = get_fundamentals(  
        query(  
            f.valuation.market_cap,  
            f.valuation_ratios.pe_ratio,  
            f.asset_classification.morningstar_sector_code,  
            f.operation_ratios.roa,  
            f.cash_flow_statement.operating_cash_flow  
        )  
        # No no ADR or PINK, only USA  
        .filter(fundamentals.share_class_reference.is_depositary_receipt == False)  
        .filter(fundamentals.share_class_reference.is_primary_share == True)  
        .filter(fundamentals.company_reference.primary_exchange_id != "OTCPK")  
        .filter(fundamentals.valuation.market_cap > 10000)  
        .filter(fundamentals.valuation.shares_outstanding > 0)  
        .filter(fundamentals.balance_sheet.common_stock_equity>0)  
        .order_by(fundamentals.valuation.market_cap.desc())  
        .limit(100)  
    )
AimFor Clarity
Apr 29, 2015

Based on a few other posts, I've been doing it like this: 

#: Reference fundamentals in a shorter variable  
    f = fundamentals  
    #: SQLAlchemy  
    fundamental_df = get_fundamentals(  
        query(  
            f.valuation.market_cap,  
            f.valuation_ratios.pe_ratio,  
            f.asset_classification.morningstar_sector_code,  
            f.operation_ratios.roa,  
            f.cash_flow_statement.operating_cash_flow  
        )  
        # No no ADR or PINK, only USA  
        .filter(fundamentals.share_class_reference.is_depositary_receipt == False)  
        .filter(fundamentals.share_class_reference.is_primary_share == True)  
        .filter(fundamentals.company_reference.primary_exchange_id != "OTCPK")  
        .filter(fundamentals.valuation.market_cap > 10000)  
        .filter(fundamentals.valuation.shares_outstanding > 0)  
        .filter(fundamentals.balance_sheet.common_stock_equity>0)  
        .order_by(fundamentals.valuation.market_cap.desc())  
        .limit(100)  
    )
About Quantopian
Careers
Community
Events
Documentation
Academia
Lectures
Status
Facebook
Twitter
LinkedIn
YouTube
Terms of Use
Privacy Policy

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 Quantopian.

In addition, the material offers no opinion with respect to the suitability of any security or specific investment. No information contained herein should be regarded as a suggestion to engage in or refrain from any investment-related course of action as none of Quantopian nor any of its affiliates is undertaking to provide investment advice, act as an adviser to any plan or entity subject to the Employee Retirement Income Security Act of 1974, as amended, individual retirement account or individual retirement annuity, or give advice in a fiduciary capacity with respect to the materials presented herein. If you are an individual retirement or other investor, contact your financial advisor or other fiduciary unrelated to Quantopian about whether any given investment idea, strategy, product or service described herein may be appropriate for your circumstances. All investments involve risk, including loss of principal. Quantopian 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.