I have a watchlist where a column returns either true or false. I would like to count the number of rows in the column that displays the value of True. I tried using the ACC function and also the SCRIPT function, but I have been unsuccessful. Please see the picture. I want to know how many of the stocks in the watchlist are returning true in the column where I scripted is the closing price above its 20 day exponential moving average. Thank you very much.
To do this i would suggest right clicking on the header of the column and selecting Group By Column
Once that is done, right click on the summary headers which display, and adjust the Summary Type to Count:
In the above example you can see 22 were True and 8 were False.
Hi Matthew. Thank you for this explanation. You are very helpful as always, and this worked like a charm.
Please help me take the next step. I have attached a picture. It is watchlist of the current S&P 500 stocks in the XLC sector. I would like to know how to create a script for the Show View tool to display the number of true results each day, not cumulative, which can sit under a daily chart of the XLC. For example, The returned true value here is 13. I would like to have a plot that returns the number 13 under the candlestick belonging to today. How would I script this? Thank you, Louis.
You would need to use the custom breadth module under Data > Market Breadth to create a time series that can then be displayed below the chart.
Create a symbol and name for the data, eg XLC20EMA and under Codes to Include select S&P500 Communication Services from the Lists tab. When a condition is true it has a value of 1, so use the Sum option for the Breadth Action and it will calculate the number of true events for each day (alternatively you can use Percent).
Once the breadth data has been built, it can then be added to a chart of XLC using the Breadth Data tool to display under the chart (select it from the Custom source list).
Hi Darren and Matthew,
Spot on! Your instructions were clear and worked perfectly.
Just to make sure I understand and not to bother you further on this point: there is no way for a script to reference a watchlist, however Market Breadth can look at a lists I import from excel which would be how I make measurements of this type on lists when there is no Optuma pre-built list.
thank you, thank you!
Exactly - watchlists are a snapshot, but the breadth engine will build the time series off of any Symbol List.