I am trying to create a series that shows the AVG PE ratio of the lowest decile in a universe. The problem is that when you use FRANK within UNIVAVG it has nested quotes and it does not work. Is there a way around it? I thought someone had mentioned a way but I could not find the post anymore.
I’m trying to do something like:
UnivAvg(“FRANK(“PEEXCLXORTTM”)<10)”,“PeExclXorTTM”)
Thanks!
I think you can use custom formulas to work around this issue.
Thanks wwasilev,
I have tried, but it does not seem to work for FRANK.
Even using a simple custom formula: FRANK(“PeExclXORTTM”) I get error ERROR: Could not find uid 116 in function FRank – map size is 0.
Any other thoughts? Thanks!
Buongiorno iAvanti!
Here is a painful work-around that allows you to compute a single, current average value of your variable:
- Go to Tools / Universe / New and create and save the custom universe with your variable.
- Run your new Universe screen and copy all the tickers in the output.
- Go to Tools / Lists / New, copy all the tickers into the list box, save the list and copy the name of your new list.
- Go to Screen / New / Stock Screen and enter:
Rule1: InList(" Your_New_List_Name ")
Rule2: Enter your variable here (e.g., PeExclXORTTM)
- Run the screen and select “Screen Factors” for the output.
- Download the output in Excel and compute the average.
- Go out for a long run…
All the best, D
Instead of downloading and computing the average manually, you may use Aggregate.
ShowVar(@avg, Aggregate("PeExclXorTTM", #Previous))
The function defaults to excluding the outer 16.5% from the average.
If you want to do a simple average, you must specify it as follows:
ShowVar(@avg, Aggregate("PeExclXorTTM", #Previous, #Avg, 0))
You can see the result in the ‘Screen Factors’ report.
Either screen a custom universe or precede this rule by any sort of rule which filters the stocks for which you want to compute the average.
Take a look at this screen.
Thanks for both your suggestions, what I am trying to get to is the series, not just the current number, so as to see how the numbers have evolved over time.
After trying, I managed to get there by creating a custom universe with all the rules in one line, and the next line being FRANK + #Previous.
That way, the universe is already filtered for the ones I wanted to plot.
The problem is that I would like to plot the ten percentiles for the formula over time, and for this I have to constantly modify the ranking, but I guess this is better than nothing.
Thanks!