Stock Portfolio Tracker Yearly and Monthly Dividends Report Fixed

Investment Moats have provided a FREE Stock Portfolio Tracker that automatically updates daily prices from Google and Yahoo finance. You will be able to track your long term holdings and review your unrealized and realized gains as well as dividends collected. (Get the spreadsheet FREE here)

The Problem

I would like to thank some users who have implemented my spreadsheet and have recently found some bugs.

We provide two sheets titled “Yrly” and “Mthly”. These two sheets lets you review your dividends by month and year.

Apparently a few days ago the formulas stop working and all the cells start shows #NUM! – Argument is blank.

The Cause

The issue here is that Google actually fixed a bug in the MONTH() and YEAR() formula used. You can read more about the problem here (ArrayFormula stopped working)

What it boils down to is that a bug with MONTH() and YEAR() has been apparently fixed, whereby if these functions were referencing a blank cell, they would return 12 and 1899 respectively, as if the cell they were referencing was actually zero.  Now it seems they are (correctly) returning a #NUM! error when this is done.

As both sheets use MONTH() and YEAR() extensively the problem occurs

How to solve this

The solution is to surround the formulas with an IFERROR(), which automatically handles the error and make the formula to work.

So the follow formula in Mthly:

=ARRAYFORMULA(SUMPRODUCT(1*(year(Transactions!$A$2:$A)=$A2)*(month(Transactions!$A$2:$A)=$B2)*(Transactions!$B$2:$B="Div")*(Transactions!$O$2:$O)))

Becomes:

=ARRAYFORMULA(SUMPRODUCT(1*(iferror(year(Transactions!$A$2:$A)=$A2))*(iferror(month(Transactions!$A$2:$A)=$B2))*(Transactions!$B$2:$B="Div")*(Transactions!$O$2:$O)))

Do this for columns C,D,E,F in “Mthly” and columns B,C,D,E in “Yrly”

Do tell me if there are anymore problems