Custom Benchmark

[ahmcc]

I'm a new user of the professional version. I would like to create a custom benchmark and need help on the best way to do so.

My benchmark consists of about 500 US listed equities and I need to record its performance since 6/30/2011. I want each stock to be equal weighted at inception. Ideally, I would like to be able to track both the appreciation and the dividend return of the benchmark. Can I just import the list of tickers or do I have to assign an initial value of say $1.00 to each "position"? Will the program handle the resultant fractional shares?

I want to use the benchmark as the reference for calculating the advanced portfolio statistics for my actual portfolios and be able to include the benchmark for comparison purposes in the various reports and graphs. Does it need to be included as a subportfolio within my master portfolio of actual accounts or is it better to maintain it as a separate portfolio?

Thanks for the help!

[Mark]

Hi ahmcc,

As you've described, you can create your own custom benchmark for use in calculating advanced statistics. You need to get this into a single investment file to assign this as your benchmark. If you want to create a custom benchmark from multiple investments you can use the "File / File Operations / Create Investment from Portfolio..." command. This command takes the daily market value of a selected sub-portfolio and assigns this as the daily price for your newly created investment. This means that if you want to create a weighted benchmark you need to buy the desired amount of each of the investments in your benchmark portfolio.

I can't think of a real easy way to buy an equal value amount of 500 different investments with varying purchase prices. When you record a purchase you have to specify any 2 of the 3 fields: value, shares, and price. You could do this manually, but it would be some work...

As far as the organization of this in your sub-portfolio hierarchy, it is usually best to keep this separate. For example:

Code: Select all

- Master
  - Model Portfolios
  - My Accounts
    - Acct 1
    - Acct 2

This way, you can report on your actual accounts, without including any of the model portfolios, or other items you don't actually own. You can set up your hierarchy in any way you want, and even set it up in multiple ways by using linked sub-portfolios if you want groupings in multiple ways.

For calculating the advanced statistics relative to your benchmark, the benchmark can be located anywhere.

If you want your benchmark index or portfolio to be included in a report, you would need to have it in the hierarchy where you are reporting (exception is the Executive Summary report in the Advisor version). For example, you could have a hierarchy like:

Code: Select all

  - Report Helper
    - Index
    - Acct 1

and report at this "Report Helper" level, so you see both the index and your account in the same report. You can turn on on the option to "Hide Totals in Footer", so you don't get the "Report Helper" numbers, which aren't meaningful in this case. Alternatively, you can keep your index investment separately, and just run 2 reports, one for the index, and one for your actual account.

[ahmcc]

Mark,

Thanks for such a comprehensive reply! It sounds like if I put all the 500 stocks in my benchmark into a single subportfolio, as in your example, I should be okay.

I do have two additional questions. Why do I have to use the “Create investment from portfolio” command? Based upon your description and the nature of the command, they sound identical.

Secondly, with regards to weighting the benchmark, the equal weighting would only be done at inception (6/30/11). After that point, the only changes would be for IPOs and buyouts. As far as coming up with the easiest way to equal weight 500 stocks, is there any way to import the unadjusted closing prices for all the stocks as of 6/30/11? My thought would be to use this in combination with an identical initial value for each stock to provide the required two fields.

Thanks!

Alex

[Mark]

Hi Alex,

Why do I have to use the “Create investment from portfolio” command?

For the advanced statistics you have to choose a "Benchmark Investment", not a benchmark portfolio. From within a report, see "Options / Report Preferences... / Benchmark". You have to select any investment you want to use as your benchmark, and it must be in the form of a single investment.

is there any way to import the unadjusted closing prices for all the stocks as of 6/30/11?

You can retrieve historical prices using "Yahoo (Historical)" to get closing prices for any date you want. This updates the "closing" prices of your investments, and is independent of your transaction prices. You could manually record transactions after retrieving prices, and the new transactions will default to the closing price on your selected transaction date.

[ahmcc]

Mark,

Thanks for the clarification on "Create investment from portfolio".

I'm still trying to come up with a way to avoid entering 500 transactions to set up my initial benchmark. The documentation says that the program can import transaction data in CSV format. I already have all the tickers in a spreadsheet. If I add additional columns in that spreadsheet with an identical initial date and position value for each stock, can I use the import function to get that data into the program? Then I would use retrieve historical prices to get the price data for the 2nd of the required two fields. My only problem is that I don’t know how the information needs to be presented to be read properly by the CSV import.

Thanks again!

Alex

[Mark]

Hi Alex,

You can import transactions from a CSV file, but at that time you need to provide it with both a price paid and number of shares purchased (and commission). You do this with "File / Import / Transactions / Generic...". Press the "Help..." button in this dialog for detailed instructions/examples. You can't import a transaction with only a value, and later retrieve a price. If you don't have prices, you could do something (slightly complicated, but at least automated) like this:

1) Create a CSV file of your symbols and a fake price of $1 for everything. Import this with "File / Import / Prices / Generic..." and allow it to create your 500 investments, each with 0 shares, and a closing price of $1 for a selected date (choose a weekday).

2) Decide on a date where you will record all your purchases. Retrieve historical prices from the internet covering this desired date, to update all 500 investments, overwriting that $1 price you imported.

3) Create a Portfolio Value report listing by symbol for the decided date, and export it to a CSV file using "File / Export to CSV...". Modify this exported CSV file, so you have the Symbol, price, and quantity, and a zero commission. You can do this in Excel, calculating the quantity based off a purchase value.

4) Import this modified file using "File / Import / Transactions / Generic...".

This is a few steps, but it allows you to get all the prices from Yahoo, and use those for your purchase price in a bulk manner, so you don't have to do anything investment by investment.

[ahmcc]

Mark,

Thanks for the solution! Taking several automated steps to get to the end point is as easy as I hoped. I just wasn't sure of the order in which things needed to done.

Alex

[ahmcc]

Mark,
Sorry I keep having questions, but I seem to need a bit more help. I can’t get my CSV file to import properly as I keep getting an error message that says “Could not find all the specified fields in the importing file.” I think my CSV file is set up as you suggested – below are the first few lines I see when I open it using Notepad:

SYMBOL,UD,TRAN,ANUM,BNUM,CNUM
AAN,11/06/30,BUY,1.00,0,0
AAP,11/06/30,BUY,1.00,0,0
ABCO,11/06/30,BUY,1.00,0,0
ABFS,11/06/30,BUY,1.00,0,0
ABG,11/06/30,BUY,1.00,0,0
ABM,11/06/30,BUY,1.00,0,0
ACAT,11/06/30,BUY,1.00,0,0

The input format is specified as: SYMBOL,UD,TRAN,ANUM,BNUM,CNUM. I created a .mm4 file and am trying to import the CSV file into a subportfolio called custom benchmark.
Thanks very much!

Alex

[Mark]

Hi Alex,

3 issues I see...

1) Use SYMB instead of SYMBOL

2) The UD key matches on YYMMDD, not YY/MM/DD, so change your date to this format or use the YY, MM, and DD keys instead of UD.

3) Your shares are all 0 on BUY transactions, so they will be ignored. If you're trying to follow the steps I previously outlined, you would want to first do the generic price (not transaction) import, to create the investments with 0 shares. Then, retrieve prices, save a Portfolio Value report to a CSV file, and modify this so you have the price/shares you want for each investment. You will have to do a little manipulation in Excel. So, if you want to invest $100 in each of your 500 investments, create a column with 100 in all the cells, and then divide that by the price column, to get the shares, and then save this to your CSV file for transaction importing, so you have the price/shares, and a 0 field for commission.

So, when you get to the transaction import, it should look something like this:

SYMB,UD,TRAN,ANUM,BNUM,CNUM
AAN,110630,BUY,26.78,3.734130,0

or if you want to use the other date format, like this:

SYMB,YY/MM/DD,TRAN,ANUM,BNUM,CNUM
AAN,11/06/30,BUY,26.78,3.734130,0

where the price of AAN is 26.78, and if you buy $100 worth of it, you buy 3.734130 shares.

[ahmcc]

Mark,

Thanks!!! I got the import to work but still have one problem. I guess I wasn't paying enough attention when I did the initial import of the prices and that data ended up in my Master Portfolio instead of the Custom Benchmark subportfolio. I have all the historical prices from inception (6/30/11) thru 12/31/13 in the master, but can't get the subportfolio to access that data. Do I have to import all the historical prices again or can I move it? I guess I don't understand how the program is structured - isn't the price data global?

I really appreciate all the time you have been taking to help a novice get up to speed!!

Alex

[Mark]

Hi Alex,

Use the Portfolio Editor window, and drag/drop those investments into their intended location. To do this, select the Master Portfolio on the left, so you see these 500 investments on the right. Select all of them on the right side that you want to move (use the Shift key to select a range), and then drag the selected investments from the right, over onto their intended sub-portfolio on the left, and then let go.