Over the course of a nearly 10 year long investing journey, I have racked up a rather messy and complicated Excel workbook tracking the trades and performance of the portfolio. Yes the fault is entirely mine, without a structured approach to workbook planning this was bound to happen.
Bad Excel Habits
My investing preference has always been dividend focused, seeking out undervalued or stable dividend payers listed on SGX. This meant that each time there was a corporate action, be it a dividend payout or rights issue, I had to manually track that in the workbook. This is not really a major problem, just a few minutes of work every month or so at the beginning. The story changes as the portfolio scales up, instead of 1-2 companies in the portfolio, this slowly grew to double digits. Making things worse was that a majority of S-REITs have a quarterly payout, but on the bright side I get a tidy bit of cashflow every 3 months.
In order to track the dividend payouts, I had a simple table which listed the XD date, the stock name, DPS and payout amount. Each stock would have its own little table tracking the cumulative dividends paid to date, to help with graphing the ROI trend. A macro manages this aspect. Next, there is a separate table which records the “Hall of Fame”, top dividend payers per corporate action. This was calculated using a LARGE formula to automatically sort out the dividend value by descending order, another column then runs a INDEX-MATCH to locate the stock which paid that amount along with the XD date. The major flaw with this approach is that if we have 2 stocks paying the same amount down to the cent, this solution would incorrectly double count one of the company and ignore the other. I had this concern when designing the table, but went ahead with it thinking what are the chances that we have 2 companies payout the exact same amount?
How wrong could I be? I ended up with 2 separate occurrences of duplicates, but this was temporarily solved by replacing the duplicated formula with a XLOOKUP which sorts in an inverse order to pick up the lagger. One minor drawback is that this inconsistent formula will have to be manually adjusted each time I have a new joiner to the “Hall of Fame” which re-orders the ranking.
Dynamic Arrays to the rescue
Since the introduction of Dynamic Arrays a couple of years ago, I had a few brief encounters with this but never to the point of heavily using it on my workbooks. Most of the time I had to use this, was to utilize the UNIQUE formula to quickly pull out a list rather than use the destructive “Remove Duplicates” method.
Chaining Formulas
How can I replace the messy table with a cleaner dynamic array which would also fix the duplicated value issue? Would this be easier to manage than my current formula? Well after a few minutes of tinkering and Googling, the answer is a resounding YES.
First lets list out the considerations for the new design
- The formula should automatically update itself when a new dividend payer displaces an entry from the current list
- No manual adjustments should be needed to maintain this list
- The source table and the “Hall of Fame” table have different number of columns and do not share the same column order
- There are charts and Pivot Tables which depend on the “Hall of Fame” table, we should not be breaking them
- New design should not be overcomplicated or be too computationlly intensive, the simplier the better
Tackling the considerations one at a time, I had to first choose a dynamic array formula which sorts numbers to replace LARGE. The answer is obviously SORT. We don’t want to list out all the entries going back 10 years, so the TAKE formula takes care of this (no pun intended).
CHOOSECOLS
Having taken care of the biggest problem above, our final task would be to select only the columns we need and re-order them to fit our new table. Here is where CHOOSECOLS come in handy. This formula nicely fits our requirements, by giving me the flexibility to specify which columns I want out of the entire table and also the order by which they should appear.
Using this combination of CHOOSECOLS+TAKE+SORT, this single clean formula replaces a nasty INDEX-MATCH and LARGE mixture which also needed its own helper cells. The elegance of it all is that this only extracts the data that we need instead of repeatedly working with a large dataset. The only drawback is that dynamic arrays are not compatible with Excel tables, so I had to convert the existing table into a range, but luckily Pivot Tables still work with ranges so this does not require further changes to other parts of the workbook which has dependencies on this range.
The journey now continues where we locate inefficiencies and optimize them to the fullest extent possible.
P.S. Yes this site is still alive. Thank you for reading till the end and have a wonderful 2026.