Back to Community
Potential bug in the Fundamentals and Morningstar APIs, receiving NAN.

Is there a bug with the APIs?

I'm receiving NAN for the EBITDA in FCAU when it shouldn't according to https://financials.morningstar.com/income-statement/is.html?t=0P0000JGWS&culture=en-US&ops=clear

If I try the same with AAPL the numbers are right. https://financials.morningstar.com/income-statement/is.html?t=0P000000GY&culture=en-US&ops=clear

Am I missing something?

Loading notebook preview...
11 responses

Clone this, change peg_ratio to ebitda, you'll see that FCAU is 1 of 301 companies where ebitda only changes once in that year while 1584 companies change 4 times and 286 change 5 times. If someone knows of some sort of software magic wand to make fundamentals worthy of trust, I'd like to hear about it.

BTS:

log.info('FCAU {}'.format(c.out.ix[sid(47888)]))

Debugger:

c.out[c.out.counts == 1.0]  
c.out[c.out.index == sid(47888)]

A similar problem occured for me: total liabilities are NaN before 2010.

More details in my yesterday's post:
https://www.quantopian.com/posts/fundamentals-dot-total-liabilities-dot-latest-always-nan-before-2010

I think it's a very important issue. How Blue Seahawk pointed in the comment above, it's essential to trust the data, otherwise the result will be garbage in -> garbage out.

I've attached a notebook to reproduce the problem with the total liabilities NaN before 2010.
Any feedback from the Q-Team?

Loading notebook preview...

thanks for the answers!

i found as well that the data in fundamental and morningstar it's kind of bad.

i'm using a pipeline to run a simulation and for AAPL i'm getting some odd numbers between 2018-05-01 and 2018-07-26.

2018-04-23 12:58  PRINT ('AAPL', 'ebit', '27.764', 'ebitda', '30.509')  
2018-04-24 12:58  PRINT ('AAPL', 'ebit', '27.764', 'ebitda', '30.509')  
2018-04-25 12:58  PRINT ('AAPL', 'ebit', '27.764', 'ebitda', '30.509')  
2018-04-26 12:58  PRINT ('AAPL', 'ebit', '27.764', 'ebitda', '30.509')  
2018-04-27 12:58  PRINT ('AAPL', 'ebit', '27.764', 'ebitda', '30.509')  
2018-04-30 12:58  PRINT ('AAPL', 'ebit', '27.764', 'ebitda', '30.509')  
2018-05-01 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-02 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-03 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-04 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-07 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-08 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-09 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-10 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-11 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-14 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-15 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-16 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-17 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-18 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-21 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-22 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-23 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-24 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-25 12:58  PRINT ('AAPL', 'ebit', '0.044766', 'ebitda', '0.047546')  
2018-05-29 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-05-30 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-05-31 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-01 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-04 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-05 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-06 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-07 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-08 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-11 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-12 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-13 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-14 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-15 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-18 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-19 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-20 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-21 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-22 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-25 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-26 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-27 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-28 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-06-29 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-02 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-03 09:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-05 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-06 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-09 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-10 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-11 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-12 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-13 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-16 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-17 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-18 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-19 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-20 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-23 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-24 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-25 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-26 12:58  PRINT ('AAPL', 'ebit', '0.047567', 'ebitda', '0.050472')  
2018-07-27 12:58  PRINT ('AAPL', 'ebit', '16.96', 'ebitda', '19.699')  
2018-07-30 12:58  PRINT ('AAPL', 'ebit', '16.96', 'ebitda', '19.699')  
2018-07-31 12:58  PRINT ('AAPL', 'ebit', '16.96', 'ebitda', '19.699')  
2018-08-01 12:58  PRINT ('AAPL', 'ebit', '16.96', 'ebitda', '19.699')  
2018-08-02 12:58  PRINT ('AAPL', 'ebit', '16.96', 'ebitda', '19.699')  
2018-08-03 12:58  PRINT ('AAPL', 'ebit', '16.96', 'ebitda', '19.699')  
2018-08-06 12:58  PRINT ('AAPL', 'ebit', '14.13', 'ebitda', '16.795')  
2018-08-07 12:58  PRINT ('AAPL', 'ebit', '14.13', 'ebitda', '16.795')  
2018-08-08 12:58  PRINT ('AAPL', 'ebit', '14.13', 'ebitda', '16.795')  
2018-08-09 12:58  PRINT ('AAPL', 'ebit', '14.13', 'ebitda', '16.795')  

as you can see these don't match https://financials.morningstar.com/income-statement/is.html?t=0P000000GY&culture=en-US&ops=clear

From 2018-05-01 to 2018-07-26 they are completly wrong, but before and after they match the the quarterly data from the Morningstar site (all fundamentals in Quantopian are quarterly):

EBITDA  
2017-06,2017-09,2017-12,2018-03,2018-06  
 14.264, 17.067, 30.509, 19.699, 16.795  

The Q-Team should take this issue into serious consideration, with such bad data it's impossibile to realiably backtest an algorithm.
I wonder if the algo which received an allocation used fundamentals data.

(all fundamentals in Quantopian are quarterly)

Meanwhile 172 companies whose peg_ratio changed 239 times in a year here, tough to square with quarterly.
What other fundamental data sources exist and what would happen in an algo side-by-side comparison? Possibly night to day.

Maybe other companies have a trial period. If one contacts Morningstar on their feedback form with a question, their auto-reply second paragraph addresses the possibility of it being one of those "in regards to a cancellation". Rather bad sign.

for "fundamentals" I meant the data from financial statements, not the ratios based on price.

Q provides only the quarterly timeframe. I asked many times also for annually and TTM, but it's not in their plan, one has to do it manually summing the quarterly data.

Anyway as you rightly pointed out in many posts, the problem is the reliability of such data! quite bad at the time :-(

Hi @Costantino and @Blue,
Thanks for this thread and your comments. I keep having problems of various kinds with fundamentals data and unfortunately Q's "Contact Support" have not provided answers to my questions. Blue's comment: "If someone knows of some sort of software magic wand to make fundamentals worthy of trust, I'd like to hear about it". came as a disappointing shock to me. I had not realized it was actually THAT bad, but still its better to be informed rather than naievely imagining things are OK when they are not!

I have one comment and one question, as follows.
Comment: With regard to Costantino making a clear distinction between fundamentals data from financial statements vs derived ratios based on price, I think this distinction is important and correct. It is my expectation that these two different types of "fundamentals data" SHOULD behave very differently. Reported corporate data can (or should) only be updated when the company itself updates them, which in most cases is usually Quarterly, or approx 4 (sometimes 5) times per year. However any ratios that involve price might quite reasonably be expected to change, or at least to have the potential to change, whenever price itself changes. PEG ratio contains 3 parts, namely price per share (which changes DAILY), EPS (which is expected to change quarterly based on corporate reporting) and Earnings Growth (which is also expected to change quarterly). So, depending on exactly how PEG is calculated, I don't see it as necessarily being a problem if PEG changes anywhere up to 252 times a year (i.e. daily).

My question: There could be many different reasons why some data are reported as NaN. Maybe the company just didn't report it, or maybe Morningstar were too lazy to enter it, or maybe the database is corrupted. In many cases we have no hope of backing out what NaN values should be, but in some cases we can. For example if if company does not pay any dividends, then the dividend amount ($) or dividend yield (%) could potentially be shown as either 0 or as NaN. For practical purposes these are equivalent and could both best be replaced by 0. Now I know this is probably a trivial python question, but i'm not very good at python, so please could someone guide me here. When looking at dividend or dividend yield data, i would like to replace all NaNs with value 0 as default. I thought the coding for this should be trivial, but when I try it my runs seem to crash. Please can someone advise me: For all the stocks in my universe, how can I replace all the dividend-related NaN entries with zero value?

Cheers best regards, TonyM.

Hi Tony,

of course it could be perfectly legitimate that some values are NaN (like in your example of dividend yield for companies without dividend).
Anyway it's sure that total liabilities wasn't NaN for those companies in that period. First I checked the data from another datasource (tagnifi.com) and secondly an old notebook of mine has such data.

Therefore it must be a bug and the Q-Team has to give us some feedback.

Regarding replacing NaN with some value, please take a look at https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html
Apply .fillna at the result of the Pipeline or just to the column with the dividend.

Like .fillna(0.0) on a pandas dataframe in before_trading_start. Then within custom factors which are numpy, it would have some counterpart and I'm not sure what it is there, I usually like to forward fill nans in custom factors with nanfill().

Tony clarifies some details, and about it: Here's that fcounts link again (except this time to the specific post) where there were also 1076 peg_ratio that only changed once while we would expect them to change nearly every day with changing prices. Unless there were 1076 that were nan all year, also unsettling. Tony's comments touching on that too. I wasn't sure what to do with nans, looks like I resorted to making a dataframe in FCount() to be able to use .fillna(0) on them (and a couple of commented lines were some other approaches considered).

Ideally maybe someone could be interested in digging into that code and improve on it?
Could also keep a count of nans or maybe something involving asof_date or or ...

Hi Everyone,

I join the request, I have been testing some fundamentals strategies, and found some NaN data and inconsistencies in general.

Well used fundamental data brings a lot of opportunities at the time of filtering, combining it with others stadistical strategies

I think Q'team has been doing a great job including Morgninstar Fundamental Data. However, finding this kind of specific bugs in bigs companies, make yourself doubt about the consistency of the entire data universe, mainly in companies not as 'famous' as Apple or Fiat Chrysler.

Hope they could solve those issues soon.