Security Cross-Reference Report?

[stevetone]

Howdy:

I am running a small RIA firm and my regulatory agency requires a report that lists, by security: client name, account number, and shares owned. I have tried to create a suitable report, but have not figured it out (yet).

I'm probably missing something here...any suggestions? Is it possible?

Thanks for your help!

Steve

[Mark]

Hi Steve,

There are a couple of ways you can do this, depending on how you want it formatted.

1) Set up your client's sub-portfolios with their name and account number. When you report on this sub-portfolio, this information will be in the report header. You can then do any type of report, like the Portfolio Value report, which lists shares of each item. You can optionally sub-total by any lower level sub-portfolios, like if they have accounts beneath.

Example sub-portfolio hierarchy:

Code: Select all

My RIA Firm
  Client XYZ
     Acct 12345
     Acct 6789
  Client ABC
     ....


When you create a report, report on "Client XYZ", and turn on sub-totals by sub-portfolio. List by investment.

2) If you wanted to see the account number on each line, you can do this by creating a custom report, and adding this field to the report.

[stevetone]

Mark:

I have the portfolios structured as in your example but I don't think that the suggested report will do what I need. I'm picturing a report that is something like:

Code: Select all

Security A
   Client XYZ     Acct12345    100 shares
   Client ABC     Acct67890    150 shares

Security B
   Client XYZ     Acct12345    300 shares
   Client QRS     Acct45678    100 shares

This cross-reference would be by security across all sub-portfolios.

Can do?

Steve

[Mark]

Hi Steve,

Try a report with these settings:

Sub-Portfolio: "My RIA Firm" (or your topmost sub-portfolio)
Type: Custom
Custom fields included: "Portfolio Parent", "Account", "Shares (ending)"
Sub-total: On, by Symbol
List By: Investment

[stevetone]

Hi Steve,

Try a report with these settings:

Sub-Portfolio: "My RIA Firm" (or your topmost sub-portfolio)
Type: Custom
Custom fields included: "Portfolio Parent", "Account", "Shares (ending)"
Sub-total: On, by Symbol
List By: Investment

Mark:

Thanks for the fast and friendly (and patient) help. That report looks like it will do what I want. I just need to rename my sub-portfolios a little differently to make sure that the needed information is all on the report. Will also need to add Account #s to all the investment properties (only had it enter on the sub-portfolio's properties, which kind of makes sense, no?)...unless I can get the Parent Portfolio's Account # on the report detail???

Keep up the great work!

Steve

[Mark]

...unless I can get the Parent Portfolio's Account # on the report detail???Steve

As I was writing my last reply, I had the same thought. I've documented that we should add a new custom field "Sub-Portfolio Account #", that works in the manner you're looking for.

Another possible work-around for now is to just include the account number in the sub-portfolio name, and don't include the "Account" field in your report. When you're renaming your sub-portfolios, you could do something like this: "Client XYZ (Account 12345)". It might be easier the recording account numbers for all the investments.