| Index | Recent Threads | Who's Online | User List | Search |
|
|
 |
Portfolio123 » List all forums » Forum: Errors » Thread: IsNA not accepting replacement values other than 0 |
|
Total posts in this thread: 7 |
[Request a Feature]
[Post new Thread] [Add To My Favorites] [Watch this Thread] |
| Author |
|
|
sthorson
Advanced Member
|
When I try using IsNA it will not replace an N/A with anything but a zero. I seem to be unable to produce any other number as a replacement. I have also found that at times instead of replacing the N/A with the zero value the function will cause the stocks with the N/A value to be screened out completely. I was attempting to clean up the output download for easier use in a database and would like to replace every N/A with a outlier number such as 99999 so that I show a number rather than a string (NA) to the database. This would alleviate type conversion issues (text among numbers). |
||
|
|
marco
|
Steve, I did a quick test yesterday and all seems fine. Can you give a precise example: which screen and what output you expect vs what you are getting? Regards ---------------------------------------- Portfolio123 Staff |
||
|
|
sthorson
Advanced Member
|
Try this IsNA( Yield,99). Run on all stocks it will give you N/A's rather than 99. Using any digits other than zero leaves the N/A's Then run IsNA( Yield,0) and you will get only 2490 stocks. It eliminates any stock with N/A instead of replacing it with a 0. Try IsNA(Yield, -99999) and it gives an error. Any reason I can't put a negative value there?? A thought in addition might be to all an option in the "Keep NA's" to make N/A return blank... ---------------------------------------- [Edit 1 times, last edit by sthorson at Apr 22, 2008 12:02:09 PM] |
||
|
|
marco
|
The screener screens out a stock if any of the rules evaluates to 0. This explains your IsNA( Yield,0) The IsNa(Yield,99) will allow all stocks to pass, except the ones that, for some reason, have the Yield ratio set to 0 since 0 is not NA. Normally Reuters will put NA for companies with no Yield, but there are currently 38 that have it explicitly set to 0. To do what you want you'll need to create a Custom Formula in Home->MyP123->Formulas->New. Create one called $ShowYield, for ex, with the following: IsNa( Eval (Yield=0,NA,Yield)) The screen report will show you the $ShowYield column with the Yield or 99 when its 0 or NA. The IsNA(Yield, -99999) seems to have a problem negating a number by itself. The work-around is IsNA(Yield, 0-99999). We'll investigate. ---------------------------------------- Portfolio123 Staff |
||
|
|
sthorson
Advanced Member
|
The IsNA(Yield,0-99999) does not work either. Should replace any N/A with -99999, correct? In the description for IsNa it states that if the data item specified has the value of N/A then it will be replaced with the requested value... Right?? So, if I write IsNa(Yield,1234) then if the yield is N/A screener should return 1234 as I understand it. No? This is not what is happening... My interest was to return a value for any N/A so that when downloaded to a spreadsheet the calculations can occur without first performing the spreadsheet task of replacing NA with a blank. Thanks for your effort with this one but I stand by my perception that the IsNa function does not work as described. |
||
|
|
marco
|
The 1234 value is used internally, within the calculation of the rule(s). It is working otherwise the NA stocks wold get screened out. But you will not see 1234 in the screener report column just by having that rule, it will show you "NA" yields. The only way to see either the Yield value of 1234, is to create a custom function. However why not just have Excel replace the "NA" strings in the Yield colum with something like F5=IF(E5="NA","",E5) I tried it and it works. NOTE: For the moment do not use -99999. There is a problem with putting a '-' on a number by itself. Use any another value like 99999 or 1234 ---------------------------------------- Portfolio123 Staff |
|||
|
|
sthorson
Advanced Member
|
I see... the old "can't see function results in the screener output" problem. Sure wish you could make that happen. I was able to get the results of custom formula at Reuters from within the body of the screen... I have been able to create a custom formula that works for this. Example: IsNA(Yield,0-99999). This returns the value -99999 for each N/A and does not eliminate any stocks from the universe. It is used in the Report rather than as a screen item. Thanks again, I think we've beaten this one enough. ---------------------------------------- [Edit 2 times, last edit by sthorson at Apr 23, 2008 10:06:25 AM] |
||
|
| [Show Thread Printable Version] [Post new Thread] |
Re: IsNA not accepting replacement values other than 0
