Back to Community
Using the Fetcher with Quandl

I have a short algorithm that allows you to obtain some extra information about companies. It uses the fetcher to access fundamental stock data from Quandl. There isn't much strategy in this algorithm, it's more of a display of how to use the fetcher with Quandl to obtain info such as P/E ratio, EBITDA, Capital Expenditures, and a ton more.

For convenience of testing out the different ratios, right now the algorithm just checks out the maximum and minimum ratios of the stocks considered, but it would be easy to look at an absolute value instead.

Any suggestions, comments, or questions are welcome!

Clone Algorithm
742
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
import pandas, numpy as np

def rename_col(df):
    df = df.fillna(method='ffill')
    log.info(' \n %s ' % df.head())
    return df

def get_ratio(sec,code):
    url = 'http://www.quandl.com/api/v1/datasets/OFDP/DMDRN_{0}_{1}.csv'.format(sec.symbol,code)
    fetch_csv(url,
        date_column='Date',
        symbol=sec.symbol,
        post_func=rename_col,
        date_format='%Y-%m-%d')
    
def initialize(context):
    # this is a dictionary of the available ratios, keyed by a description of the ratio
    # (the description is also the column header)
    context.ratios = {'Number of Shares Outstanding' : 'FLOAT',
                      'Insider Holdings' : 'INSIDER',
                      'Capital Expenditures' : 'CAPEX',
                      'Net Margin' : 'NET_MARG',
                      'Invested Capital' : 'INV_CAP',
                      'Price to Sales Ratio' : 'P_S',
                      'Return on Capital' : 'ROC',
                      'Stock Price' : 'STOCK_PX',
                      'Market Debt to Equity Ratio' : 'MKT_DE',
                      'Correlation with the Market' : 'CORREL',
                      'Forward PE Ratio' : 'PE_FWD',
                      'Previous Year Growth in Revenues' : 'REV_GRO',
                      'EBIT for Previous Period' : 'EBIT_1T',
                      'Dividends' : 'DIV',
                      'Forward Earnings Per Share' : 'EPS_FWD',
                      'Change in Non-Cash Working Capital' : 'CHG_NCWC',
                      'Cash as Percentage of Firm Value' : 'CASH_FV',
                      'Institutional Holdings' : 'INST_HOLD',
                      'Effective Tax Rate' : 'EFF_TAX',
                      'Cash as Percentage of Total Assets' : 'CASH_ASSETS',
                      'Ratio of Fixed Assets to Total Assets' : 'FIXED_TOT',
                      'Value Line Beta' : 'BETA_VL',
                      'Book Value of Assets' : 'BV_ASSETS',
                      'Book Value of Equity' : 'BV_EQTY',
                      'Free Cash Flow to Firm' : 'FCFF',
                      'Cash as Percentage of Revenues' : 'CASH_REV',
                      'Market Capitalization' : 'MKT_CAP',
                      'Effective Tax Rate on Income' : 'EFF_TAX_INC',
                      'EV To Sales Ratio' : 'EV_SALES',
                      'Total Debt' : 'TOT_DEBT',
                      'Ratio of Intangible Assets to Total Assets' : 'INTANG_TOT',
                      'PE to Growth Ratio' : 'PE_G',
                      'Reinvestment Rate' : 'REINV_RATE',
                      'Book Debt to Capital Ratio' : 'BOOK_DC',
                      'Expected Growth in Earnings Per Share' : 'EPS_GRO_EXP',
                      'EV to EBIT Ratio' : 'EV_EBIT',
                      'Current PE Ratio' : 'PE_CURR',
                      'Market Debt to Capital Ratio' : 'MKT_DC',
                      'Non-Cash Working Capital as Percentage of Revenues' : 'NCWC_REV',
                      'Trailing 12-month Revenues' : 'REV_12M',
                      'Expected Growth in Revenues' : 'REV_GRO_EXP',
                      'Trailing Revenues' : 'REV_TRAIL',
                      'Return on Equity' : 'ROE',
                      'EV to EBITDA Ratio' : 'EV_EBITDA',
                      'Earnings Before Interest Taxes Depreciation and Amortization' : 'EBITDA',
                      '3-Year Regression Beta' : 'BETA',
                      'Depreciation' : 'DEPREC',
                      'EV to Trailing Sales Ratio' : 'EV_SALESTR',
                      'Growth in Earnings Per Share' : 'EPS_GRO',
                      'Price to Book Value Ratio' : 'P_BV',
                      'Trailing Net Income' : 'NET_INC_TRAIL',
                      'Trailing PE Ratio' : 'PE_TRAIL',
                      'Pre-Tax Operating Margin' : 'OP_MARG',
                      'Firm Value' : 'FIRM_VAL',
                      '3-year Standard Deviation of Stock Price' : 'STDEV',
                      'Trading Volume' : 'TRAD_VOL',
                      'Cash' : 'CASH',
                      'Dividend Yield' : 'DIV_YLD',
                      'Revenues' : 'REV_LAST',
                      'Net Income' : 'NET_INC',
                      'EV to Book Value Ratio' : 'EV_BV',
                      'Reinvestment Amount' : 'REINV',
                      'Earnings Before Interest and Taxes' : 'EBIT',
                      'EV to Invested Capital Ratio' : 'EV_CAP',
                      'Payout Ratio' : 'PAYOUT',
                      'Hi-Lo Risk' : 'HILO',
                      'All Financial Ratios' : 'ALLFINANCIALRATIOS',
                      'Sales General and Administration Expenses' : 'SGA',
                      'Enterprise Value' : 'EV'}
    
    context.ratio_of_interest = 'PE to Growth Ratio'
    
    context.stocks = [sid(24), sid(5061), sid(26578), sid(14848), sid(38989)]
    for stock in context.stocks:
        get_ratio(stock,context.ratios[context.ratio_of_interest])
        
    context.max_long = 1000
    context.max_short = -1000
    
    context.high_ratio = 1
    context.low_ratio = 1

def handle_data(context, data):
    values = []
    for stock in context.stocks:
        if context.ratio_of_interest in data[stock.symbol]:
            values.append(data[stock.symbol][context.ratio_of_interest])
    
    if len(values) > 0:
        min = np.min(values)
        max = np.max(values)
        
        if min != max:
            for stock in context.stocks:
                if context.ratio_of_interest in data[stock.symbol]:
                    if (data[stock.symbol][context.ratio_of_interest] == min and
                        context.portfolio.positions[stock.symbol] < context.max_long):
                        order(stock, -10)
                    if (data[stock.symbol][context.ratio_of_interest] == max and
                        context.portfolio.positions[stock.symbol] > context.max_short):
                        order(stock, 10)
This backtest was created using an older version of the backtester. Please re-run this backtest to see results using the latest backtester. Learn more about the recent changes.
There was a runtime error.
7 responses

Sam, very cool algorithm, thanks for posting. It would be cool if you refactored the code so that the codes and the calls to fetcher were encapsulated in a class. Then you could instantiate the class in the initialize method to retrieve the data you want. We have a special fetch_estimize method, maybe we should have a fetch_quandl method that works along the lines you have outlined here.

thanks,
fawce

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.

Hey,
Very nice algo! Just a question: are the data fetched static? Does the algo use the current P/E when he trades in 2003 or does it get the 2003 P/E?

Thanks,
Martin

It uses the 2003 data when it trades in 2003 - that's the beauty of the whole thing!

When you load a time series using Fetcher, the timestamps on the data is integrated into the backtester just like the stock data is - no look ahead bias. Data is used only when it is the correct time.

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.

Thanks a lot for your quick reply! Another question if I may, is there any way to make the algo select the stocks based on the fetched data? For example: sell the stocks whose P/E ratios are among the 10% higher of sector i...
Martin

Not yet, but it's a feature I'd love to build.

--- Errors..... 'fetch_csv' only permitted within initialize function

Quandl's OFDP is deprecated or removed. I wonder what the equivalent would be for today.