Monthly Performance by Symbol report?

[williamg85]

Is there a way to get the monthly performance of each ticker in a sub portfolio?
As far as I can tell, I can do it on sub portfolio level, but not symbol/ticker level. Am I missing something, or is there a work-around?

End goal: I'm creating a new allocation formula in Excel and would like to export monthly performance (including reinvested dividends/interest) to workbook for each ticker to have it calculate allocation. Excel data import only has prices but not dividends, which is a rather important factor in certain investments (ie fixed income ETFs) and I'm hoping to use Fund Manager to capture this data.

[Mark]

Hi williamg85,

You could get 6 months at a time per symbol by using a Custom report with the field:

ROI Yield (between)

List by: Symbol
Date Ranges: 6 (and enter the 6 months you want)

[williamg85]

Mark,

Thank you for the suggestion.

My model is based on monthly (total) returns and the report suggested gives the ROI as one number, not monthly figures. I was hoping to get 5 years of data (60 months) which would allow for a more nuanced calculation.

WG

[Mark]

Hi WG,

The report I was suggesting would give you 6 of these monthly returns, because you would have 6 date ranges, 1 month each.

Another option is to create user-defined fields for each of these 60 months. For example, the formula:

EVAL(DATE2DAYS(1/1/2022), DATE2DAYS(1/31/2022), ROI_btw)

Would give you the ROI yield for Jan 2022. You could write 60 of these, and add them to your report. In this case, the date of your report doesn't matter, and you'd just need 1 date range, and you'd get all 60 of the monthly yields. Export to CSV with "File / Export to CSV...".

[williamg85]

Mark,

Ok, I didn't understand that. I've never done a custom report. I'll read the instructions and try to follow your suggestion. Getting 60 months in one shot would be amazing!

[williamg85]

Mark,

Is there some way to create the formula you gave as dynamic formula that bases itself off one end date given. This way I can re-run the report at anytime and get most recent 60 months of data. I would imagine it would be something like using the "end date" and then running one formula to be 0-30 days from that date, and the next formula would be 31-60 days from that date.

I'm seeing something about ENDDAYS in the documentation, but when I put that in instead of DATE2DAYS in the formula it doesn't work. I must be inputting it incorrectly.

[Mark]

Hi williamg85,

Yes, you can do something like this:

EVAL(DELTADAYS(ENDDAYS() , 0 , -1 , 0) + 1, DELTADAYS(ENDDAYS() , 0 , 0 , 0), ROI_btw)

and if you set the end date of your report to 1/31/2022, that would get you the ROI yield between 1/1/2022 and 1/31/2022, the same as this equation:

EVAL(DATE2DAYS(1/1/2022), DATE2DAYS(1/31/2022), ROI_btw)

So, if you wanted to create the most recent 60 months, you would have 60 user-defined fields, with equations like:

EVAL(DELTADAYS(ENDDAYS() , 0 , -1 , 0) + 1, DELTADAYS(ENDDAYS() , 0 , 0 , 0), ROI_btw)
EVAL(DELTADAYS(ENDDAYS() , 0 , -2 , 0) + 1, DELTADAYS(ENDDAYS() , 0 , -1 , 0), ROI_btw)
EVAL(DELTADAYS(ENDDAYS() , 0 , -3 , 0) + 1, DELTADAYS(ENDDAYS() , 0 , -2 , 0), ROI_btw)
EVAL(DELTADAYS(ENDDAYS() , 0 , -4 , 0) + 1, DELTADAYS(ENDDAYS() , 0 , -3 , 0), ROI_btw)
etc...

For more details, see the documentation on DELTADAYS.

[williamg85]

Mark,

Thank you so much!
I already ran sample report (5 months), exported to make sure it all worked. Worked just perfectly!

Thank you very much!
WG