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
Kyith is the Owner and Sole Writer behind Investment Moats. Readers tune in to Investment Moats to learn and build stronger, firmer wealth foundations, how to have a Passive investment strategy, know more about investing in REITs and the nuts and bolts of Active Investing.
Readers also follow Kyith to learn how to plan well for Financial Security and Financial Independence.
Kyith worked as an IT operations engineer from 2004 to 2019. Currently, he works as a Senior Solutions Specialist in Insurance Start-up Havend. All opinions on Investment Moats are his own and does not represent the views of Providend.
You can view Kyith's current portfolio here, which uses his Free Google Stock Portfolio Tracker.
His investment broker of choice is Interactive Brokers, which allows him to invest in securities from different exchanges all over the world, at very low commission rates, without custodian fees, near spot currency rates.
You can read more about Kyith here.
- New 6-Month Singapore T-Bill Yield in Early-December 2023 Should be Slightly Higher at 3.85% (for the Singaporean Savers) - November 30, 2023
- Have the World or Emerging Market Healthcare Stocks Outperform the World and EM Index? - November 26, 2023
- Retirement Spending Can Vary from 25% to 100%. Not your usual 2% to 3% a Year. - November 23, 2023
DAVID
Tuesday 4th of August 2020
I would like to get the Monthly sheet
Thanks, David
Kyith
Thursday 6th of August 2020
Hi David, i took out the monthly sheet, but you can modify the yearly sheet to show it monthly.
Ryan Tan
Wednesday 11th of July 2012
Hi there, I have some problems with your spreadsheet. I did the change in formula as instructed, but I notice that the chart in Mthly does not reflect values on column E. Besides that, there seems to be some error in Yrly too.
Drizzt
Saturday 14th of July 2012
hi Ryan, i checked mine and it doesn't seem to have an issue. perhaps you can authorize me temperory to see if i can help you figure out