Put the following formula in A1
of the Sheet Dividend Tracker
and delete everything else.
It will generate your Monthly and Annual tables.
Use from
to specify the first year, and yr_count
to specify the number of years to include.
=LET(rng,'Roth IRA Transactions'!G4:J, from,2023, yr_count,8,
array,FILTER(rng, LEN(INDEX(rng,,1))),
yr_label,SEQUENCE(1,yr_count,from),
dates,INDEX(array,,1),
stock,INDEX(array,,2),
div,INDEX(array,,4),
months,INDEX(DATE(from, SEQUENCE(12),1)),
monthly,BYROW(months, LAMBDA(m,
{TEXT(m, "mmmm"), BYCOL(yr_label, LAMBDA(y,
IFERROR(1/(1/SUMPRODUCT(div,
EOMONTH(dates,-1)+1)=DATE(y,MONTH(m),1)))))})),
yearly,BYROW(SORT(UNIQUE(stock)), LAMBDA(s,
{s, BYCOL(yr_label, LAMBDA(y,
IFERROR(1/(1/SUMPRODUCT(div, YEAR(dates)=y, stock=s)))))})),
IFNA(VSTACK({"Months", yr_label}, monthly, "",
"", "", {"Stocks", yr_label}, yearly)))
Explanation
- LET is used to store intermediate calculations for reuse.
- There are just three named variables that store the source data values, and the rest of the calculations are based on those three:
rng
is the source data range
start_yr
is the first year for results.
yr_count
is the total number of years on which to report.
array
is the source data rng
after using FILTER and LEN to remove blank rows.
yr_label
is a SEQUENCE of yr_count
years beginning with start_yr
.
- The data columns required from
array
are returned using INDEX combined with the appropriate column number:
dates
from column 1.
stock
from column 2.
div
from column 4.
months
stores a SEQUENCE of 12 dates representing the first of the month for each month of start_yr
.
- These dates are used not only to generate the month labels ("January, February, etc.) but also in calculations.
- The year is included because these are dates, however, which year is irrelevant so
start_yr
is used since it's readily available.
monthly
returns the monthly dividend table results without the year headings row:
- BYROW passes each date from
months
, one by one, into a LAMBDA function that stores the current month in m
- For each
m
, the LAMBDA's formula prepends the name of the month, onto the result of a BYCOL function.
- The BYCOL passes each year in
yr_label
, one by one, into another LAMBDA function that stores the current year in y
.
- For each
y
, the LAMBDA's formula uses SUMPRODUCT to return the total of div
where the year and month of dates
matches the current year y
and the current month, month(m). This is compared as follows:
- To compare only the year and month of two dates, a normal date comparison is performed,
dateA=dateB
, after first setting the day of both dates to 1.
- To do this for
dates
, EOMONTH is applied with -1
which returns the last day of the previous month, to which 1 day is added moving the date to the first day of the original month.
- For the current month being filtered on, the DATE function is used with
y
as the year, MONTH(m)
as month, and 1
as the day.
- This process is repeated for each year
y
for the current month, and then again for the next month, etc.
yearly
returns the yearly dividends by stock using a similar approach to monthly
- BYROW passes each UNIQUE stock, one by one, into a LAMBDA function which stores the current stock in
s
.
- For each
s
the LAMBDA's formula For each m
, the LAMBDA's formula prepends the name of the stock to the result of a BYCOL function.
- The BYCOL passes each year in
yr_label
, one by one, into another LAMBDA function that stores the current year in y
- For each
y
, the LAMBDA's formula uses SUMPRODUCT to return the total of div
where the year of dates
matches y
, and stock=s
.
- This process is repeated for each year
y
for the current stock s
then again for the next stock s
, etc.
- Lastly, VSTACK stacks the two arrays with their heading rows.
Some Notes:
The stock list is dynamically generated for any stock listed in 'Roth IRA Transactions'!H4:H)
in yearly
and monthly
, blanks are returned instead of zeros by dividing 1 by the inverse of the SUMPRODUCT function wrapped in IFERROR. The following behavior is being leveraged:
# ISNUMBER(x) and x<>0
1/(1/x)=x IFERROR(1/(1/x))=x
# ISBLANK(x) or x=0
1/(1/x)=#DIV/0! IFERROR(1/(1/x))=[empty]
# ISTEXT(x)
1/(1/x)=#VALUE! IFERROR(1/(1/x))=[empty]
If you prefer to return zeros, using the above as a guide simply remove everything but x
which in this case is equivalent to the SUMPRODUCT function:
# Change this
IFERROR(1/(1/SUMPRODUCT(..)))
# Into this
SUMPRODUCT(..)