Exporting Trades - Rebalance & Mgmt Fees

[WeekendRider]

I am currently on Advisor version 12.9. I will be upgrading to v.2020 as soon as I finish my quarterly work. I have taken this over from someone else and I'm fairly green with regards to FM.

I have two basic tasks I'd like to complete:

1) I would like to export trades to rebalance sub-portfolios against a model. I have read how to export rebalance trades but I can't find anything on how to create the "model sub-portfolio." How is this done?

2) I would like to export trades for management fees. I have been able to set everything up to create the Management Fees report but I would like to allocate the fee in proportion to the account holdings and export the trades. Is this possible?

Thanks!

[Mark]

Hi WeekendRider,

1) Use the Portfolio Editor, and set up a new sub-portfolio. Create the investments in there, and record transactions so your portfolio has the desired allocations. Some related tutorials:

Portfolio Editor
Sub-Portfolios
Data Register

2) Yes. If you're not using a "tiered" fee method, assign the management fee method at the account level, so each account will be listed in the Management Fee report. Use "File / Export to CSV..." from within the Management Fee report. If you are using a "tiered" fee method, choose the option "Report Fees at Account Level" in the dialog where you define the fee method. I'm not sure this option was available in 12.9.

[WeekendRider]

If I may, let me concentrate on #1 first. I'm having trouble understanding the thought process. If I want a client to have a certain allocation and I want to rebalance from time to time or change that allocation, how do I build a model portfolio to accomplish this?

Lets say I want the following allocation:

Fund1 - 10%
Fund2 - 20%
Fund3 - 30%
Fund4 - 40%

So I create a sub-portfolio titled Model Portfolios and within that I create a sub-portfolio titled Income Model. Now to Income Model I want to add the investments Fund1,2,3,4 (using New Investment). I add the funds above using the Symbol drop down box. Do I input a number of shares in the Opening Data or do I create a buy transaction later (or does it matter)? Do I make up an account number?

Once I've gotten through the above, the value of each Fund1,2,3,4 is going to change as I import prices, and therefore my desired allocation as stated above will be off. But my goal is to keep the allocation as posted above. This is where I struggle - am I missing something?

[Mark]

Hi WeekendRider,

Do I input a number of shares in the Opening Data or do I create a buy transaction later (or does it matter)?

It doesn't matter. If you use the "Opening Data" that is recording a buy transaction. You can leave the shares blank (or 0) here, and enter a buy later.

Do I make up an account number?

This isn't necessary for a model, you can just leave it blank.

Once I've gotten through the above, the value of each Fund1,2,3,4 is going to change as I import prices, and therefore my desired allocation as stated above will be off. But my goal is to keep the allocation as posted above. This is where I struggle - am I missing something?

There are probably several ways you could handle this. Here are some ideas:

1) Let the funds update prices, and adjust the allocations periodically be recording buys/sells.

2) Turn off "Retrieve Price", so these investments don't get updated prices. You can either use the price as of today, or treat them all like cash, where you put the shares equal to the value, and just leave the price fixed at 1. See "Investment Properties... / Internet Retrieve".

[WeekendRider]

2) Turn off "Retrieve Price", so these investments don't get updated prices. You can either use the price as of today, or treat them all like cash, where you put the shares equal to the value, and just leave the price fixed at 1. See "Investment Properties... / Internet Retrieve".

This looks like the best method for me. Where do I turn off "Retrieve Price"? Is this option for this sub-portfolio/investment only or will it affect other sub-portfolios?

[WeekendRider]

Ok, I found where to turn off "Retrieve Price" but that isn't how I'm getting prices. I'm importing prices from a file I receive. Is there a way to keep the price from importing in a particular sub-portfolio?

[Mark]

Hi WeekendRider,

Unfortunately, there isn't an option to prevent updating when importing prices from a file. What price import command are you using?

[WeekendRider]

I'm using File-Import-Prices-Generic.

I guess I just don't understand how the rebalance functionality as it exists in the software is useful. The most common approach is to decide on an asset allocation and rebalance from time to time as things drift. It is also common to change the asset allocation based on future market forecasts and then rebalance.

The beginnings of a true asset allocation approach are in the software on the Rebalance report. I can define and set an "Allocation Target" but only by Asset Type, Investment Goal, Investment Type, Currency (and Model Portfolio - but I don't see how this can easily be used for this purpose.) Right now I've finagled Asset Type to kind-of work but I can't relate it directly to proxy funds nor export trades on this basis.

One other note, the export trades functionality seems to assume a brokerage account setup in that it calculates buys and sells (sells moving everything to a cash account and buys taking everything from the cash account.) That works for brokerage accounts where there is a cash account but not for mutual fund accounts. It would be helpful to have a choice between a brokerage type setup and a mutual fund type setup. In the mutual fund type setup the trades would need to be exchanges and therefore a direct $x from fund A to fund B, $y from fund A to fund C, etc.

[Mark]

Hi WeekendRider,

I can see adding the ability to prevent the model portfolio's prices from being updated. Is there something else that you'd like to see added/changed to make the Rebalance report more useful?

Thanks for the feedback on the export trades feature. I can see that being a useful option. Maybe an option that required the total of all buys to equal the total of all sells would satisfy this case?

[WeekendRider]

I can see adding the ability to prevent the model portfolio's prices from being updated. Is there something else that you'd like to see added/changed to make the Rebalance report more useful?

I see that as a work-around. Long term it would be nice to add the option to Allocation Target Properties to add the type Investment Symbol and allow the user to assign an allocation using investments in the database. Then add the ability to select either a Model (Target) Sub-Portfolio or an Allocation Target under export Rebalance Trades.

Thanks for the feedback on the export trades feature. I can see that being a useful option. Maybe an option that required the total of all buys to equal the total of all sells would satisfy this case?

For mutual fund accounts it would be helpful if it could do the following (see below for images - I can't get things to line up):

Assume I rebalance and I get:

Investment Actual % Target % Delta % Delta Value
Fund A 39.15 50 -10.85 -507,220.00
Fund B 14.99 15 -0.01 -330.00
Fund C 5.09 5 0.09 4,260.00
Fund D 20.17 15 5.17 241,900.00
Fund E 20.6 15 5.6 261,390.00
John Doe 100 100 0 0.00

I would like to see something like the following exported trades:

ACCT,MM/DD/YY,TRAN,SYMB,OUT,IN
123456789,10/4/2019,EXCH FROM,FUND E,261390.00,
123456789,10/4/2019,EXCH TO,FUND A,,261390.00
123456789,10/4/2019,EXCH FROM,FUND D,241900.00,
123456789,10/4/2019,EXCH TO,FUND A,,241900.00
123456789,10/4/2019,EXCH FROM,FUND C,3930.00,
123456789,10/4/2019,EXCH TO,FUND A,,3930.00
123456789,10/4/2019,EXCH FROM,FUND C,330.00,
123456789,10/4/2019,EXCH TO,FUND B,,330.00

[WeekendRider]

In the image the column heading should be Investment and not Asset Type.

[Mark]

Hi WeekendRider,

I see that as a work-around. Long term it would be nice to add the option to Allocation Target Properties to add the type Investment Symbol and allow the user to assign an allocation using investments in the database.

Okay, thanks for the feedback/idea. I can see that being useful for a more static % among symbols.

Then add the ability to select either a Model (Target) Sub-Portfolio or an Allocation Target under export Rebalance Trades.

You can pick the model sub-portfolio for rebalance trades now. Every sub-portfolio can have an assigned model portfolio. When exporting trades you can either have FM use the assigned model, or when exporting for a particular sub-portfolio you can choose any model from a list of all portfolios. This may have been added after the version you're using now...

I would like to see something like the following exported trades

Okay, so the key feature you're looking for is that the trades are all equal value/opposite? The way it works now you can get the sums of all the trades to net to $0 effect on cash, but in your example all the buys of Fund A would be lumped into a single transaction.

[WeekendRider]

You can pick the model sub-portfolio for rebalance trades now. Every sub-portfolio can have an assigned model portfolio. When exporting trades you can either have FM use the assigned model, or when exporting for a particular sub-portfolio you can choose any model from a list of all portfolios. This may have been added after the version you're using now...

I know - it is in v.12.9. What I was suggesting is that in addition to being able to select a model sub-portfolio for rebalance trades that the software alternatively would allow the selection of an Allocation Target.

Okay, so the key feature you're looking for is that the trades are all equal value/opposite? The way it works now you can get the sums of all the trades to net to $0 effect on cash, but in your example all the buys of Fund A would be lumped into a single transaction.

Yes, that would be awesome!

[Mark]

Hi WeekendRider,

Are you also asking for the rebalance trade feature to be able to rebalance on any allocation target? Currently, it only works when rebalancing against a symbol based target (model portfolio). Rebalancing against other type of models (investment type, asset type, etc) starts to get into too many variables, as the trades must be symbol based, and there are multiple ways you could go about rebalancing in those cases.

[WeekendRider]

Hi WeekendRider,

Are you also asking for the rebalance trade feature to be able to rebalance on any allocation target? Currently, it only works when rebalancing against a symbol based target (model portfolio). Rebalancing against other type of models (investment type, asset type, etc) starts to get into too many variables, as the trades must be symbol based, and there are multiple ways you could go about rebalancing in those cases.

Sorry, only on a symbol based target. I wasn't considering the other types when I wrote that.