Monthly Dividend Report

[larryw]

Is there an easy way to create a report that will show monthly dividends paid for the last 12 months. All dividends have been rolled over and not paid out.

I know I can create one by manually entering date range but there is a limit on how many columns can be created and I'm hoping there is an easier/faster way to do this.

[Mark]

Hi larryw,

Sorry, no there isn't an easy way to get this. You could create a Custom report and write user-defined fields for the 12 different months. Otherwise, you could use up to 6 dates and the built in field for dividends to get 6 in a single report.

[divinest]

Interesting way to get the custom fields to show monthly allowing to overcome the 6 date range limitation. So is the idea to create 12 custom fields, one for each month? Would that be dynamic based on the report date? say if i select the report range is current year, then it would show current year monthly dividends.

[Mark]

Hi divinest,

Yes, that is the idea. You could do it with either explicit date settings using the DATE2DAYS() function, or make them relative to the current report date using the DELTADAYS() function. It is easier to just define them with explicit dates. For example, you could create a user-defined field "January '18 Dividends" with the equation:

EVAL(DATE2DAYS(1/1/2018), DATE2DAYS(1/31/2018), Divdist_btw)

[divinest]

Thanks Mark, that works very nicely. Amazing how FM allows you to customize and get insights.

[divinest]

Did something like this to get 12 months, dynamically assuming the end date is 12/31/XXXX,
Question, is there an option to mask the zero, i.e. if zero just display blank?

EVAL(DELTADAYS(ENDDAYS(), 0 , -12, 0) + 1, DELTADAYS(ENDDAYS(), 0 , -11, 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -11, 0) + 1, DELTADAYS(ENDDAYS(), 0 , -10, 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -10, 0) + 1, DELTADAYS(ENDDAYS(), 0 , -9 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -9 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -8 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -8 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -7 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -7 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -6 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -6 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -5 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -5 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -4 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -4 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -3 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -3 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -2 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -2 , 0) + 1, DELTADAYS(ENDDAYS(), 0 , -1 , 0), Divdist_btw)
EVAL(DELTADAYS(ENDDAYS(), 0 , -1 , 0) + 1, ENDDAYS(), Divdist_btw)

[Mark]

Hi divinest,

Very good. Sorry, no, there is no option to show blank instead of 0.

[divinest]

Okay thanks Mark, was thinking if there was a way using the IF function, but i guess it may not work for this option. In any case the report does provide the details needed, masking zero is more of a cosmetic item. Thanks again.

[larryw]

Just getting around to digging deeper into this but it appears custom fields are not available in the personal version and only the 6 month option is available. Correct?

[Mark]

Hi larryw,

Correct. In the Custom report, only the "built-in" fields are available in the Personal version. There are hundreds to choose from. You are correct that the "user-defined" field feature, where you write your own equations, is only available in the Professional/Advisor versions.