Could this difference be because ebitda and enterprise_value that use to calculate ev_to_ebitda are quarterly numbers while the ev_to_ebitda field in fundamentals is for the trailing 4 quarters ?

Which then begs the question, which of the fields are quarterly numbers and which are annual numbers ?

And if annual are they trailing annual or the last annual(last financial year end) ?

Sarvi

I just confirmed that the ebitda that used to calculate was quarterly.
just to compare I made my formula enterprise_value/(ebitda*4) and the values are atlease roughly comparable
Just shortcut to compare, since I am yet figure out how to arrive at fundamentals for trailing X quarters

1. Which brings me to a new question, what is the best way to at these fundamental values for the trailing X quarters.

I do the querying in
def before_trading_start(context):

The SQLAlchemy query brings up the data for single bar it looks like
2011-01-04PRINTDataFrame: security Security(5061 [MSFT])
symbol MSFT
ebit 7.275e+09
ebitda 7.969e+09
ev_to_ebitda 7.2806
roic 0.099229
enterprise_value 194625027247

and does not contain historical data.
2. so is there a way to have access to historical fundamental data in before_trading_start() to make trading decisions

I presume I could add this to data and access historical() in handle_data or a scheduled function.

1. What is the best way to query/find find the last X quarter's data for a fundamental field. At any bar, in handle_data, I think I can have access to the entire historicals for the past, but then quarterly data gets reported on different dates for each quarter and some times even gets update with additional filings, so I presume the fundamental fields get updated on those dates. So I am confused as to the best way to arrive at the fundamental field(say ebitda) for the last 4 quarters for example.

Thanks,
Sarvi

Hi Sarvi

Thanks for all this legwork. I started in on this topic, this afternoon, and you definitely beat me to the punch on the initial question.

With respect to your question on accessing previous values for fundamental metrics, that's an item that's certainly on our near term roadmap. One short-hand way we refer to this feature is "history() for fundamentals" (https://www.quantopian.com/help#api-history). Today, history() only works for pricing data.

Seong provided a quick solution in this post: https://www.quantopian.com/posts/referencing-fundamental-data-of-previous-period

In this case, for fundamentals, a history() function has some extra complexity that the above example doesn't yet help with (and we're open to feedback in advance of doing the work).

1) Seong's example is tracking data from each day. In reality, fundamental data typically changes quarterly. Would you only want those sparse data points (each quarter's number?) Or do you want each daily value?
2) How do we handle restatements of earnings and other metrics? If a previous quarter's earnings has been restated, should we modify the historical data only after the restatement date in the back test?

For 1: For this problem I want each trailing quarter's numbers. Just like how we do 'd' or 1 'b' for historical data. I would like history function that returns 'q' trailing quarters or 'y' trailing years. The daily values are not helpful to me as they are all the same for within a quarter. So I want the sparse data points.
Note: This history is from the perspective of handle_data/before_trading/start which single event. For all calculations from this perspective I don't see value for daily.

That said, I have another use case, which so far as I understand has not been covered well in quantopian so far, and that is batch/incremental training/optimization of statistical model(think neural network model).

In a neural model, I would like to
1. initalize/train a neural model some where in initialize or earlier once for the entire back test/trading, with say 5-10 years of history data.
2. Update the model parameters daily or once a month or quarter with say the last 2 years or 2 months of historical data

When training neural models, in say batch or online mode, I would like iterate over say, the last 1000 bars/steps(daily or minute) of history and for each training step I use as input X bars(daily or minute) of price data with which I can train the neural network model. because for each datapoint i.e bar(minute or daily) that I train the neural network, I might use as input N previous bars as input to predict my buy/sell/hold signal for the day. Now, when I add fundamental inputs to this model, for each training step I would like to give preferably the trailing annual(or some arithmetic of a few fundamental fields over a few quarters) of as input.
Meaning for each bar(daily or minute) in the history data I want say ev_to_ebitda over the trailing annual(trailing 4 quarters combined to arrive the trailing ev_to_ebitda)

So one good way that I can think of implementing history for such things is as follows implement fundamental history on bar basis but provide callback function but provide prefunc/postfunc like functions which have access to the trailing X quarters of fundamental fields as well as the current bars price data. This would allow me to define my own enterprise_value or ev_to_ebitda fields for each bar as a prefunc(trailing X quarters of fundamental fields, Y bars of prices)

On the topic of restatement of earnings, when looking at fundamentals from the perspective of a single bar(daily or input), I would like to get the latest updated numbers for a quarter. I.e for 4 quarters I get only entries into my prefunc() that is the latest available/updated numbers, even if say for 1 of the quarters the fields were updated due to restatement of earnings.

To summarize,
1. For each history bar I would like to see fundamental fields from the sqlalchemy query, as well as additional computed fields that I can define through my own function.
2. Such a compute function should X quarters of fundamental field data as of that bar to do its computation add/replace the fields of fundamental data the bar should have.
3. The X quarters of fundamental data would have the most current/update values for fundamental fields that may have been reported to the SEC as of that bar.

May be a bit too detailed than you hoped for :-)
But hope that helps

Sarvi

Another suggestion to consider is about querying and what fields/data is available for querying.

Today you can only query on existing fields in the fundamental data and not on calculated data

For example. If I want to query today for today's value of ev_to_ebitda or ev_to_ebit(which doesn't exist as a field in the data base I cant)

The ev_to_ebitda in the database was calculate when the last 10-Q was reported an the price and hence enterprise_value on that date, NOT today.
More importantly if I wanted to query for ev_to_ebit(instead of ev_to_ebitda) the field is not even available to query.

So ideally when working fundamental data and querying for a list of stocks, It would be helpful to
1. allow for querying of calculated fields
2. Allow the calculated field to query based on, to involve both price data on the day of query as well historical fundamental quarterly data for the different fields, trailing for N quarters.

Thanks,
Savi

One good API I can think of for this as follows, more to describe more clearly what I am looking for.

# Custom function provided by the developer  
def fundamental_field_update(price_history, fundamental_history):  
     #price_history has N bars of price history data, where N is specified in the update initializer shown below  
     # fundamental history has M quarters of fundamental data fields, where M and the list of fundamental data fields is specified in the update_initializer show below  
    ....... calculate new or updates to fields  
     fundamental_history.column_add/remove/update(..............)  
    # Note that the above can add update fields that may be price based as well, such as 50-MA, 200-MA, or a MACD oscilator as calculated fields that can be queried on using SQLAlchemy query API

def inititialize(context):  
      fundamental_update(calcfunc=fundamental_field_update,  
                                        fill=ffill, # Fill forward  
                                        price_history=5, price_bars='d',   # N=5 bars of price history is provided to calcfunc where each bar is 1 day  
                                        fundamental_history=4,          # M=4 quarters of history data is available to calcfunc  
                                        input_fields=[                           # List fields available to calcfunc  
           fundamentals.share_class_reference.symbol,  
            fundamentals.income_statement.ebit,  
            fundamentals.income_statement.ebitda,  
            fundamentals.valuation_ratios.ev_to_ebitda,  
            fundamentals.operation_ratios.roic,  
            fundamentals.valuation.enterprise_value,])

You can think of fundamental_field_update() as being called to calculate new fundamental fields, update existing fields based on some price and fundamental history, for each bar(daily or minute) and forward fill if necessary.

These new or updates are then available to the existing get_fundamentals() SQLAlchemy query operations.

Why updating existing fields?
It allows for updating fields like "enterprise_value" or ev_to_ebitda to reflect todays values instead of the value calculated on the day of the 10-Q filing.
So that a universe filter query done at the beginning of the trading day filters based on todays value as opposed to a month ago.

I would also extend todays SQLAlchemy Query operation to query based on price, high/low, calculated fields(50 day price moving average), as fields that can be queried on

Sarvi