Search code examples
google-sheetsgoogle-sheets-formulaformula

Sum if a date and a month match for dividend tracker

I am trying to create an automatic dividend tracker that will add values from my transaction lists (dividends received) in Google Sheets, but I can't seem to get the right formula to work. I am trying to get help on two different formulas.

My source data is in a table in 'Roth IRA Transactions'!G4:J

G H I J
3 Date Received Ticker Symbol Shares Amount
4 Dec 29, 2023 Cash 0.25 0.25
5 Jan 31, 2024 Cash 11.64 11.64
6 Feb 29, 2024 Cash 11.29 11.29
7 Sep 20, 2024 VXUS 0.015 0.92
8 Mar 27, 2024 VTI 0.021 5.37
9 Mar 28, 2024 Cash 12.11 12.11

Formula 1

In my sheet 'Dividend Tracker' I would like to return the total amount of dividends by Month and by Year. For example, using the sample source data above, my results would be:

A B C
1 Months 2023 2024
2 January 11.64
3 February 11.29
4 March 17.48
5 April
6 May
7 June
8 July
9 August
10 September 0.92
11 October
12 November
13 December 0.25

Formula 2

In the same sheet 'Dividend Tracker' I would like to return the total dividends by ticker symbol and by Year. For example, using the sample source data, my results would be:

A B C
16 Stocks 2023 2024
17 Cash 0.25 35.04
18 VTI 5.37
19 VXUS 0.92

Link to example Google spreadsheet.

Solution

  • 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

    1. LET is used to store intermediate calculations for reuse.
    2. There are just three named variables that store the source data values, and the rest of the calculations are based on those three:
      1. rng is the source data range
      2. start_yr is the first year for results.
      3. yr_count is the total number of years on which to report.
    3. array is the source data rng after using FILTER and LEN to remove blank rows.
    4. yr_label is a SEQUENCE of yr_count years beginning with start_yr.
    5. The data columns required from array are returned using INDEX combined with the appropriate column number:
      1. dates from column 1.
      2. stock from column 2.
      3. div from column 4.
    6. 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.
    7. monthly returns the monthly dividend table results without the year headings row:
      1. BYROW passes each date from months, one by one, into a LAMBDA function that stores the current month in m
      2. For each m, the LAMBDA's formula prepends the name of the month, onto the result of a BYCOL function.
      3. The BYCOL passes each year in yr_label, one by one, into another LAMBDA function that stores the current year in y.
      4. 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:
        1. 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.
        2. 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.
        3. 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.
        4. This process is repeated for each year y for the current month, and then again for the next month, etc.
    8. yearly returns the yearly dividends by stock using a similar approach to monthly
      1. BYROW passes each UNIQUE stock, one by one, into a LAMBDA function which stores the current stock in s.
      2. 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.
        1. The BYCOL passes each year in yr_label, one by one, into another LAMBDA function that stores the current year in y
        2. 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.
        3. This process is repeated for each year y for the current stock s then again for the next stock s, etc.
    9. 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(..)