The program leaves few companies in the portfolio

Report a problem or error
1

What I want
– I want get all company from one sector
– Add the repurchased shares and research capital(R&D expenses) to the common equity
– Get adjusted common equity for all companies in sector
– Select companies whose market cap to adjusted common equity is less than the median value of the sector, even if they don’t have such expenses

What I made

  • ShowVar(@RD, RandD(0, ANN) + RandD(1, ANN) * 0.9 + RandD(2, ANN) * 0.8 + RandD(3, ANN) * 0.7 + RandD(4, ANN) * 0.6 + RandD(5, ANN) * 0.5 + RandD(6, ANN) * 0.4 + RandD(7, ANN) * 0.3 + RandD(8, ANN) * 0.2 + RandD(9, ANN) * 0.1)
  • ShowVar(@TS, EqPurch(0, ANN) + EqPurch(1, ANN) + EqPurch(2, ANN) + EqPurch(3, ANN) + EqPurch(4, ANN) + EqPurch(5, ANN) + EqPurch(6, ANN) + EqPurch(7, ANN) + EqPurch(8, ANN) + EqPurch(9, ANN) + EqPurch(10, ANN))
  • IsNA(RandD(0, ANN), 0) And IsNA(RandD(1, ANN), 0) And IsNA(RandD(2, ANN), 0) And IsNA(RandD(3, ANN), 0) And IsNA(RandD(4, ANN), 0) And IsNA(RandD(5, ANN), 0) And IsNA(RandD(6, ANN), 0) And IsNA(RandD(7, ANN), 0) And IsNA(RandD(8, ANN), 0) And IsNA(RandD(9, ANN), 0)
  • close(0) > 1 And AvgVol(21) > 105000 And MktCap > 50 And Universe(NOOTC) And !Universe($ADR)
  • (ComEqQ + @RD + @TS) > 0
  • MktCap / (ComEqQ + @RD + @TS) < FMedian(“MktCap / (ComEqQ + @RD + @TS)”, #All, TRUE)
  • InSet(Sector, FINANCIAL)

What I received
The program selects only companies that have expenses for repurchased shares and R&D. Therefore, the portfolio consists of 1-2 companies.

What do I need to do to get what I want?

2

Basically, you need to use IsNA a lot in your formulas. But using LoopSum will help in terms of not having to spell it out ten times per line. So, for example, your first line could be

LoopSum(“(1-Ctr/10)*IsNA(RAndD(Ctr,Ann),0)”,10,0)

Also you should use #Sector instead of #All in the FMedian line. Or use #Previous but move your sector restriction up a line or two.

Please feel free to follow up if you have any questions about this.

3

Yuval,

I think, in your formula, it must be (10-Ctr/10), not “(1-Ctr/10)”. shabolkin wants the last year to be rated highest.

Matthias

4

1 - Ctr/10 gives you 1 for the most recent year, 0.9 for last year, 0.8 for the year before that, etc.