Back to Community
Referencing fundamental data of previous period

Hi,

I'm having trouble setting up a SQLAlchemy code that would reference a fundamental metric from the past. For example, say I want to buy stocks whose average earnings-per-share (EPS) in the last 4 quarters are greater than the average EPS of the preceding 4 quarters. How would I use the get_fundamentals function to filter stocks like that?

Thank you very much in advance.

5 responses

Right now you'd have to save each current value as you encounter it. So you'd have to wait 8 quarters until you had enough history. I imagine they'll add the ability to reference historical fundamentals at some point.

Hi guys,

Here's an algorithm that creates a "history" of fundamental data by creating a Pandas Panel with the dates as the index that you can reference data from

Give it a shot and let me know if you have any questions

Seong

Clone Algorithm
Loading...
Backtest from to with initial capital
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
"""
    Creating an algorithm based off the Piotroski Score index which is based off of a score (0-9)
    Each of the following marks (-) satisfied means one point. And in the end we'll long the stocks with a score of >= 8

    Profitability 
    - Positive ROA
    - Positive Operating Cash Flow
    - Higher ROA in current year versus last year
    - Cash flow from operations > ROA of current year

    Leverage
    - Current ratio of long term debt < last year's ratio of long term debt
    - Current year's current_ratio > last year's current_ratio
    - No new shares issued this year

    Operating Efficiency
    - Higher gross margin compared to previous year
    - Higher asset turnover ratio compared to previous year


    This algorithm demonstrates how to grasp historical fundamental data by storing it in a Pandas Panel similar to how the Quantopian 'data' is currently structured
"""

import pytz
from datetime import datetime, timedelta

import pandas as pd
    
"""
    Initialize and Handle Data
"""
    
def initialize(context):
    
    #: context.days holds the number of days that we've had this algorithm
    context.days = 0
    
    #: context.fundamental_dict holds the date:dictionary reference that we need
    context.fundamental_dict = {}
    
    #: context.fundamental_data holds the pandas Panel that's derived from the fundamental_dict
    context.fundamental_data = None

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
    
    #: Query for the data that we need from fundamentals
    fundamental_df = get_fundamentals(query(
                                    f.valuation.market_cap,
                                    f.operation_ratios.roa,
                                    f.cash_flow_statement.operating_cash_flow,
                                    f.cash_flow_statement.cash_flow_from_continuing_operating_activities,
                                    f.operation_ratios.long_term_debt_equity_ratio,
                                    f.operation_ratios.current_ratio,
                                    f.valuation.shares_outstanding,
                                    f.operation_ratios.gross_margin,
                                    f.operation_ratios.assets_turnover,
                                    )
                             .filter(fundamentals.valuation.market_cap != None)
                             .filter(fundamentals.operation_ratios.roa != None)
                             .filter(fundamentals.cash_flow_statement.operating_cash_flow != None)
                             .filter(fundamentals.cash_flow_statement.cash_flow_from_continuing_operating_activities != None)
                             .filter(fundamentals.operation_ratios.long_term_debt_equity_ratio != None)
                             .filter(fundamentals.valuation.shares_outstanding != None)
                             .filter(fundamentals.operation_ratios.current_ratio != None)
                             .filter(fundamentals.valuation.shares_outstanding != None)
                             .order_by(fundamentals.valuation.market_cap.desc())
                             .limit(40)
                             )
    
    #: Set our fundamentals into a context variable
    context.fundamental_df = fundamental_df
    
    #: Update our universe with the values
    update_universe(fundamental_df.columns.values)  
    
def handle_data(context, data):
    
    #: Only run every 25 trading days
    if context.days % 25 == 0:
        
        #: Insert a new dataframe into our dictionary 
        context.fundamental_dict[get_datetime()] = context.fundamental_df
        
        #: If it's greater than the first trading day
        if context.days > 0:
            context.fundamental_data = pd.Panel(context.fundamental_dict)
            scores = get_piotroski_scores(context.fundamental_data, get_datetime())
            
            #: Only rebalance when we have enough data
            if scores != None:
                rebalance(context, data, scores)
    
    #: Log our current positions
    if (context.days - 1) % 25 == 0:
        
        #: Portfolio position string
        portfolio_string = "Current positions: "
        
        #: Don't log if we have no positions
        if len(context.portfolio.positions) != 0:
            
            #: Add our current positions to a string
            for pos in context.portfolio.positions:
                portfolio_string += "Symbol: %s and Amount: %s, " % (pos.symbol, context.portfolio.positions[pos].amount)
        
            #: Log all our portfolios
            log.info(portfolio_string)
    
    
    context.days += 1
            
"""
    Defining our rebalance method
"""

def rebalance(context, data, scores):
    """
        This method takes in the scores found by get_piotroski_scores and orders our portfolio accordingly
    """
    
    #: Find which stocks we need to long and which ones we need to short
    num_long = [stock for stock in scores if scores[stock] >= 8]
    num_short = [stock for stock in scores if scores[stock] <= 2]
    
    #: Stocks to long
    for stock in num_long:
        if stock in data:
            log.info("Going long on stock %s with score %s" % (stock.symbol, scores[stock]))
            order_target_percent(stock, 1.0/len(num_long))
    
    #: Stocks to short
    for stock in num_short:
        if stock in data:
            log.info("Going short on stock %s with score %s" % (stock.symbol, scores[stock]))
            order_target_percent(stock, -1.0/len(num_short))
    
    #: Exit any positions we might have
    for stock in context.portfolio.positions:
        if stock in data and stock not in num_long and stock not in num_short:
            log.info("Exiting our positions on %s" % (stock.symbol))
            order_target_percent(stock, 0)
    
    record(number_long=len(num_long))
    record(number_short=len(num_short))
    
"""
    Defining our methods for the piotroski score
"""

def get_piotroski_scores(fundamental_data, current_date):
    """
        This method finds the dataframe that contains the data for the time period we want
        and finds the total Piotroski score for those dates
    """
    all_scores = {}
    all_dates = fundamental_data.items
    
    utc = pytz.UTC
    last_year = utc.localize(datetime(year=current_date.year - 1, month = current_date.month, day = current_date.day))
    
    #: If one year hasn't passed just return None
    if last_year < min(all_dates):
        return None
    
    #: Figure out which date to use
    for i, date in enumerate(all_dates):
        if i == len(all_dates) - 1:
            continue
        if last_year > date and last_year < all_dates[i + 1]:
            break
        elif last_year == date:
            break
        
    #: This is pretty robust so just set last_year to whatever date currently is when you broke
    #: or ended the for loop
    last_year = date
    old_data = fundamental_data[last_year]
    current_data = fundamental_data[current_date]
    
    #: Find the score for each security
    for stock in current_data:
        profit = profit_logic(current_data, old_data, stock)
        leverage = leverage_logic(current_data, old_data, stock)
        operating = operating_logic(current_data, old_data, stock)
        total_score = profit + leverage + operating
        all_scores[stock] = total_score
        
    return all_scores

def profit_logic(current_data, old_data, sid):
    """
        Define our profitability logic here
    """
    
    #: Positive ROA
    positive_roa = current_data[sid]['roa'] > 0
    #: Positive Operating Cash Flow
    positive_ocf = current_data[sid]['operating_cash_flow'] > 0
    #: Current ROA > Last Year ROA
    current_last_roa = current_data[sid]['roa'] > old_data[sid]['roa']
    #: Cash flow from operations > ROA
    cash_flow_roa = current_data[sid]['cash_flow_from_continuing_operating_activities'] > current_data[sid]['roa']
    
    return int(positive_roa) + int(positive_ocf) + int(current_last_roa)+ int(cash_flow_roa)
    
def leverage_logic(current_data, old_data, sid):
    """
        Define our leverage logic here 
    """
    
    #: Current ratio of long-term debt < last year's ratio of long-term debt
    long_term_debt = current_data[sid]['long_term_debt_equity_ratio'] > old_data[sid]['long_term_debt_equity_ratio']
    #: Current year's current_ratio > last year's current_ratio
    current_ratio = current_data[sid]['current_ratio'] > old_data[sid]['current_ratio']
    #: No new shares
    new_shares = current_data[sid]['shares_outstanding'] <= old_data[sid]['shares_outstanding']
    
    return int(long_term_debt) + int(current_ratio) + int(new_shares)
    
def operating_logic(current_data, old_data, sid):
    """
        Define our operating efficiency logic here
    """
    
    #: Higher gross margin compared to previous year
    gross_margin = current_data[sid]['gross_margin'] > old_data[sid]['gross_margin']
    #: Higher asset turnover ratio compared to previous year
    asset_turnover = current_data[sid]['assets_turnover'] > old_data[sid]['assets_turnover']
    
    return int(gross_margin) + int(asset_turnover)


  
    
    
We have migrated this algorithm to work with a new version of the Quantopian API. The code is different than the original version, but the investment rationale of the algorithm has not changed. We've put everything you need to know here on one page.
There was a runtime error.
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.

@Seong: Sorry for reviving this old thread, but why do you convert the context.fundamental_dict into a Pandas Panel? You can also reference the data by context.fundamental_dict[date][stock]['whatever'], so what kind of advantage does context.fundamental_data = pd.Panel(context.fundamental_dict) provide?

Is this still the best way to compare fundamental data to a previous period? Does the new Pipeline API allow a more intuitive way of applying this functionality?

Regards,
Mark

Hey guys, I implemented a very similar strategy in this thread. I'll share the backtest here as well for convenience. Let me know if you have any questions!

Clone Algorithm
98
Loading...
Backtest from to with initial capital
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
from quantopian.algorithm import attach_pipeline, pipeline_output
from quantopian.pipeline import Pipeline
from quantopian.pipeline import CustomFactor
from quantopian.pipeline.data.builtin import USEquityPricing
from quantopian.pipeline.data import morningstar
from quantopian.pipeline.factors import SimpleMovingAverage
import numpy as np

# Create custom factor subclass to calculate change in EPS
class EPSChange(CustomFactor):

    # Use basic_eps from the morningstar fundamentals data
    inputs = [morningstar.earnings_report.basic_eps]
    # Set the window length to be big enough to capture the end of the last quarter
    window_length = 150

    # Compute EPS change since end of last quarter
    def compute(self, today, assets, out, eps):
        out_list = []
        
        # For each security in the universe
        for i in range(eps.shape[1]):
            
            # Store the column of each day's eps for the security in eps_col
            eps_col = eps[:,i]
            
            # Since the basic_eps only changes at the end of each quarter, np.unique
            # will get us the unique EPS values in the 150-day window. return_index
            # is set to True so that the most recent unique values can be referenced later.
            _, idx = np.unique(eps_col, return_index=True)
            
            # Get the 2 most recent unique values in the 150-day value.
            prev_and_curr_eps = eps_col[np.sort(idx)[-2:]]
            
            # If there was only 1 distinct EPS value in the last 150-days,
            # duplicate it so that numpy can create a 2*num_stocks to output
            # in out[:]
            if len(prev_and_curr_eps) == 1:
                prev_and_curr_eps = np.append(prev_and_curr_eps, prev_and_curr_eps[0])
            
            # Store the 2 most recent unique EPS values for the stock in out_list
            out_list.append(prev_and_curr_eps)
            
        # Convert out_list to a 2*num_stocks numpy array
        all_prev_and_curr_eps = np.transpose(np.array(out_list))
        
        # Return the most recent EPS minus the previous EPS
        out[:] = all_prev_and_curr_eps[-1] - all_prev_and_curr_eps[-2]


def initialize(context):

    # Create the pipe
    pipe = Pipeline()
    attach_pipeline(pipe, 'eps-change-example')

    # Construct the custom factor
    eps_change = EPSChange()

    # Add the EPS change factor to the pipe
    pipe.add(eps_change, 'eps_change')
    
    # Create and apply a filter representing all stocks where the current EPS is
    # greater than the previous one
    eps_better_top_100 = eps_change.top(100)
    pipe.set_screen(eps_better_top_100)

def before_trading_start(context, data):
    context.output = pipeline_output('eps-change-example').sort(['eps_change'], ascending=False)

    update_universe(context.output.index)

def handle_data(context, data):

    print "SECURITY LIST"
    log.info("\n" + str(context.output))
There was a runtime error.
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.