A new change went into effect last night to handle NA’s in line items that are specifically sent to us by Compustat. These are rare cases where a past value for a line item is being expired and replaced with an N/A (all point-in-time of course). Our logic didn’t like replacing non N/A values with N/As, so the old value was left as is. This is not a decision for us to make. If compustat wants to have N/A, there must be a good reason.
The case that brought this to light is this one . I asked them why they are stuffing a annual value in an interim (and making previous interim na’s) and their answer was:
So we are now using NA’s that Compustat sends us. Your backtests might produce different results. Please let us know of any questions or concerns.
Just want to make sure I understand how this functioned in the past and will function now. Let me know if my interpretation is correct given the following example
I am using the Sales(0,QTR,KEEPNA) in my sim/screen
Old Method: Compustat changes the current quarter’s Sales for company XYZ to NA. Portfolio123 will not change the Sales(0,QTR) value to N/A instead keeping the old value until a new one is provided, therefore the value returned by a reference to Sales(0,QTR,KEEPNA) does NOT change.
New Method: Portfolio123 will change the Sales(0,QTR) to N/A until a new value is available, therefore the value returned by reference to Sales(0,QTR,KEEPNA) will now be NA, causing it to potentially fail my screen… or possibly be sold from my portfolio as a consequence.
Good so far??
How about if I am using Sales(0,QTR,FALLBACK)
Old Method: Looks the same as above.
New Method: Instead of returning N/A, Sales(0,QTR,FALLBACK) will return the Sales number from the previous quarter.
Am I interpreting this correctly?
What about for a variable like DIVPS which doesn’t allow you to specify how NA’s are handled (btw how are they handled), how do we know what scenario is taking place.
Just so others are aware, there is some very unusual behavior that has shown up from yesterday to today (I expect due to the changes made over night). In particular, I use a custom formula in my simulations that does the following
Fairly benign formula that hasn’t given me any trouble before. Since yesterday a number of companies are returning the following error when this formula is evaluated (I have found ~30 based on the current date’s information).
-1.#J or 1.#J
This error is also effecting the results of historical simulations as I can compare the Buys and Sells from Yesterday to Today and the companies that are no longer being purchased are not being purchased because they return the 1.#J error. Some specific examples of this are the following
Really wish you guys would check for stuff like this before you start make over night changes with no warning. Don’t appreciate having to waste an entire day of my time trying to chase this error down.
dnevin123 , this fix should not affect fallback. Fallback is only enabled for the most recent statement (whether ANN or QTR) and only if the statement is preliminary (CompleteStmt=false). Anything else we take the NAs Compustat gives us.
We’ve only noticed valid values being replaced by NAs with re-statements (due to writeoffs, restructuring, etc). Fallback is not used in re-statements.
Thanks Marco, looking at the data last night I also noticed that error showing up in the ROI variables as well. Let me know if you want the specific company names, and I can go look them up again. Apologies for flaming out a little bit in my post, was a bit frustrated after a tedious day.
We found a bug in the way we treat subsequent NA’s. We’ll have a fix later today. We go through a verification process when we release new versions to make sure the differences are legit. But in this case there were several hundred, and the manual spot-check missed the ones that were not legit. Again, sorry about that.
NOTE: We also found cases where Compustat is sending us subsequent N/A (NULL in database speak) that seem suspect. We are double checking additional cases with them to make sure that their intentions are clear: they are in fact telling us that previous values should be changed to N/A.
Thanks for the update. I confirmed in my simulations that I wasn’t seeing the -1.#J errors anymore, however, I am seeing some unusual behavior (positions selling that don’t trigger the sell screen). Can I get someone’s time next week to try to work through what is going on?
Since you appear to have made some changes last night, I would like to report strange behaviour for my screens. On Thursday and Friday week several screens were selecting NPTN in position 1. Now, today and for all of last week, NPTN is missing. I have also rerun some backtests today that I ran recently (same date ranges) and the annual performance has dropped by about 10%. Since I can’t reproduce my previous results, I have no idea what has caused this change.
Did you save your runs from before the change? What I did was to work up the transaction list from the earliest date in the simulation and find the first place where the old differed from the new. I was then able to run a screen on that date and try to figure out what might have changed. That’s how I found the -1#J error, which they fixed. However I expect this change on the way NA’s are being handled has more consequences that just that. Depending on how you do NA handling in your formulas the sudden appearance of an NA can trigger a sell, where in the past you might have held the position for longer. IMHO we might need a way to tell the simulation to ignore these new NAs that are popping up, especially since they are transitory. Frankly, I think the old way of handling things is what I would prefer for my own models.
Unfortunately I haven’t got a saved run. However, I do use IsNA in several places, to provide an alternative if the variable is NA. However the change I am seeing happened last night (4/5 March). I haven’t rerun many screens, but the performance appears to be worse by about 10%. To make it worse, I have no idea how to fix the problem. Are others seeing a problem?
The fix should not affect preliminary reports since they are the first ones that come in. The NAs in question are being sent to us to replace previous values, therefore likely affect completed statements. There are hundreds of individual examples , but it is a very small proportion due to special conditions. For example ticker RATE in latest period has SalesQ=370 and SalesPQ=NA. This is due to ongoing restatements due to discontinued operations.
Compustat line items are not stored like in a traditional database table where every item has a pre-designated column and , if missing, the column is empty. Every item has a start/end date, a value, other flags, etc. Therefore the NAs we are talking about here are very clear directives from Compustat.
I believe I understand, but I still believe the previous method for handling these changes was superior. My opinion on this is based on my view of what an actual active portfolio manager would do, which is in the absence of new data he would continue to use the old data until such time that new data is available. In particular, I would not choose to sell a security just because a restatement was in process, buy would instead wait until my analysts had finished updating the information so that I could make an informed decision. As such, I believe your old process better reflected the behavior that would be exhibited by an individual.
Daniel, we have to use what they tell us , even if it’s NAs, otherwise it’s a bottomless pit. They’ve been doing this for 20+years. We also cannot give a “choice” to users. We are not a database and it would complicate the system specification too much.
However RATE is also flagged as preliminary, which we show as CompleteStmt=FALSE. If you are designing a fully automated portfolio it’s probably best to avoid buying/selling stocks with CompleteStmt = FALSE . But it depends on your system. Maybe the factors you use are not so sensitive to preliminary data. Alternatively, if you are doing manual reviews of recs, looking at stocks with CompleteStmt=0 is the best place to start. In other words, there’s no ideal way to deal with NA’s, restatements, prelims. But with the tools we provide there should be workarounds for different approaches.
Thanks Marco, I can see the difficulties. With regard to my models I use quite a bit of ZERONA, which should take care of some of this. However, one variable that I have recently started using after the PayRatio5YAvg definition was changed is DivPS. Unfortunately, the definition of DivPS doesn’t specify how NA’s are handled or give you an option for controlling it like most of the other fundamental data functions. Can you tell me how DivPS handles NA’s? Based on the behavior I am seeing after the change my expectation is that it does not ZERONAs.