Open Lots Report - "Open Price" equivalent for Custom

[ThomasK]

Hi Mark,

I need to see a consolidated report, where all Open Lots of a specific symbol are consolidated on one line. I'm guessing I need to do this in a Custom Report, yes?

So, I think I can figure out how to do that, but I want to use the "Open Price" as in Open Lots Report in a Custom Report...what is the exact 'setting' I should use from the available fields in Custom Report Settings?

Or do I have to code it in Define Fields? Can you kindly provide the equation so I can paste it in?

By the way, I like the ability to filter on memo, that it does not have to be exact, so that I can put it a simple prefix to an existing memo.

Thank you.
TK

[Mark]

Hi TK,

The Open Lots report is listing "Lots", or individual transactions. The Custom report does not list by transactions. It can list by several different objects, such as "Investments", but it doesn't list by transaction. So, there is no exact equivalent for the Custom report. You can use something that consolidates all the outstanding purchase prices, such as:

Tax basis per share, Assigned Method
Average out of pocket purchase price (current)

[ThomasK]

Thanks Mark,

Perhaps in a future release we can have the ability to consolidate multiple lots? That most likely sounds to me like another built-in report, rather than an option on the existing one, where we the user acknowledge that we are looking at a "Family or Household Report".

Most of the time the different accounts under the same family roof so to speak hold the same stock but from different purchase dates/prices/amounts...and being able to run such a "Family or Household Report" on different levels of the portfolio hierarchy allows for ultimate flexibility in reporting performance, etc.

Thanks for considering,
ThomasK

p.s. having such a Household Report as 'built-in' of course puts limits on what headings we can select from and how you would have them consolidate values, for example Days Owned...you could average or median or min or max. Perhaps that would be an option for the user, at least to override defaults?

[Mark]

Hi ThomasK,

You can get a consolidation of lots by listing by Investment, Symbol, Asset Type, etc in any of the reports that offer this option, such as the Custom report. For example, if you want to know the average price you paid for all shares of a security across all your accounts, use the "Average out of pocket purchase price (current)" field in a Custom report, and set your report settings to list by "Symbol". If you report at your top-level sub-portfolio, and turn off sub-totals, this will calculate this average price for each unique symbol in your top level sub-portfolio. If you just want to know the consolidated average within each investment, list by investment.

[ThomasK]

Getting back to the idea of a custom report to consolidate the transactions...I can get what I want (almost) by the reporting settings below...
Subtotal by: Symbol
List by: None (totals only)

The "almost" is that I cannot seem to sort alphabetically by the "Investment Symbol", and the "Sort" command is greyed out on the menu.

For example, the report is listing ABC stock on one line fine, but the lines themselves are not in alphabetical order...such as
!CASH CAD
LLY
BB.TO
TA.TO
AEM
!CASH USD
IBM
et cetera

First, I would like to see the stock symbols in order.

Secondly, I do want the !CASH CAD and !CASH USD to be together. I gave CASH with the "!" to sort the cash on top of everything...I don't mind the CAD and USD cash lines to appear on the top or bottom, but at least together.

Perhaps the issue is that I gave suffixes to the stock names?? Even if a stock is only in 1 account it would still get the account's 'suffix' on the back end when I created the investment. For example, Eli Lilly is held in two accounts and thus Named in Properties as:
ELI LILLY [rrif]
ELI LILLY [open]

Thanks for the help...I'm down to the final 'presentation', just have to get the sorting done.

[Mark]

Hi ThomasK,

If you sub-total by Symbol, the symbols will be listed in the order of the investment stack. You can sort the investment stack using "Investments / Sort By" and then choosing "Symbol" to sort by symbol.

A better solution is to probably change your report settings to:

Subtotal By (OFF)
List By: Symbol

You can then use the top level "Sort" command, and you won't have a bunch of blank spaces between each sub-total section when listing by nothing.

The investment "name" won't matter when listing by symbol. Everything with a common symbol (no matter what the name) will be grouped together.

[ThomasK]

Perfect!

Just any idea as to why the other way was not displaying alphabetically? Bug?

I'm thinking I had previously selected a Sort based on another stack FIRST, then ran the report but I tested "pre-sorting" then running the report as Subtotal by: Symbol, List by: None and I get the previous result, even when I start from scratch.

However, when I run it to Subtotal by "Asset Type", the order is how I have it in the Category Labels window, so all good, but most likely that's not the problem.

Thanks,
ThomasK

[Mark]

Hi ThomasK,

If you sub-total by Symbol, the symbols will be listed in the order of the investment stack. You can sort the investment stack using "Investments / Sort By" and then choosing "Symbol" to sort by symbol. If your investments are not in alphabetical order in the stack, they won't be in alphabetical order when sub-totaling by symbol. This is not a bug, it is the intended operation. You can view your actual investment stack order under "Investments / Order...".

When sub-totaling by Asset Type the investment stack order doesn't matter, in this case the order you've specified under "Options / Category Labels..." is used.

[ThomasK]

Cool. Awesome actually. Didn't know it existed.

I've never used it...perhaps it is something I'm doing that is putting my Loblaws right between my Pan Am Silver? I'll adjust now for sure, easy breezy, but I'd like to correct any usage issue I might be doing.

Most likely it is my recent moving of investments around in the Portfolio Editor. Somehow it looks like some of them got 'displaced'...any ideas? Most likely won't happen again and now I know where to look.

Thanks much.

[Mark]

Hi ThomasK,

When you create a new investment, it gets placed on top of the investment stack. You can use the "Investments" menu to order your investment stack however you want, and when you save your portfolio, the investment stack order is saved with it. You can read more about this in the online help topic:

https://www.fundmanagersoftware.com/help/invstack.html