Export Portfolio & Prices

[Milli]

I'm looking for a portfolio system so I'm testing the Fund Manager. I have a few issues.

When exporting data from Fund Manager there are different lengths on the exported time series. If I e.g. export the example portfolio TD Ameritrade using the supplied data there's 115 more records in the Portfolio than in the instruments (exporting prices) that form the portfolio.

Further more there are different formats in the data fields. When exporting Prices in .csv the resulting format in Excel can't be changed (i.e. it's not a date format). However when exporting the portfolio in .txt (only available) then the date format can be changed within Excel.

Can sombody please explain how and why the portfolio has more records than it's constituents.

It's pretty easy to write a routine in Excel that handle these problems, so this is more an issue of trust in the system. Can I really trust what comes out of the system? Is there a way to syncronise the dates used in the different portfolios (e.g. a date master) except between dates which apparently dosent work?

Milli

[Mark]

Hi Milli,

Thanks for trying out Fund Manager.

How are you determining that there are 115 more records? I exported prices for this sample TD Ameritrade sub-portfolio to a CSV. It exported 17,308 prices. Looking through the file, it seems all 5 investments have pricing data exported from 1/2/96 - 9/28/09, which matches what is recorded in Fund Manager. The export process will export whatever data is in the investments in the sub-portfolio you choose.

If you create a Custom report, and add the "Historical price count (between)" field, and set the date range to span the complete history, you will see each of the 5 investments contains 3461 prices, except MSFT which contains 3464 prices. This totals to the 17,308 number exported.

You can change the format of the column containing the dates in Excel. Select that column, and choose "Format / Cells...". Change it to one of the Date formats.

[Milli]

Hi Mark,

I've done following:
1.
For the data I've used Export/Prices/Comma Separated../TD Ameritrade

When opening in Excel I get 5 instruments. INTC, IBM, DIS and DELL each contain 3180 records and MSFT contains 3183 records. All starting 1/2/1996 and ending 8/15/2008. Of some reasom there's 3 more records for Microsoft.
The date CAN NOT be changed from within Excel. If I want to chage the date I have to write a macro in Excel.

2.
For the Portfolio I've done following:
Export/Other/Portfolio Value/TD Ameritrade.
When opening in Excel I get 2 columns containing 3294 records starting 1/2/1996 and ending 8/15/2008, i.e. 114 resp. 111 more records.
In this file the date column can be changed from within Excel.

Milli

[Mark]

Hi Milli,

1) When you did the export, did you leave "All Dates" checked? When I did the export, it exported everything, all the way to 9/28/09.

I was able to change the format of these date cells okay in Excel. I just selected the column, and did a "Format / Cells..." and selected a Date format. What happens when you try?

2) When I do this I get 3585 values, from 1/2/96 - 9/28/09.

Maybe you are using an old version of Fund Manager, where the sample data ended on 8/15/08. If you open up an investment in the Data Register, and select "Prices" for the data type, what is the date of the last price?

What version of Fund Manager are you using? My information is based on the samples shipped with version 10.1.

[Milli]

I had version 9.7. I've installed 10.1

However the issue remains. There's still 3 more MSFT records. I left "All dates" checked.
Now I get more data from 1/2/1996 to 9/28/2009. The date in the "Price" export can't still be canged for format in Excel.

The portfolio has an additional 121 resp. 124 records. Start and end date is exactly the same as for the prices i.e. 1/2/1996 to 9/28/2009 but as said 121 resp 124 additional records. The dates from the Portfolio export can be changed in Excel just as in version 9.7.

The TD Ameritrade is composed from the 5 shares DELL, MSFT, DIS, IBM and INTC. When exporting all I get 3461 resp. 3464 price records yet the portfolio contains 3585 records within exactly the same time span.
How can that be? It seems that Fund Manager add dates to the portfolio that don't exist in the price files. I left the "Exclude weekends" checked in the portfolio export.

Do you have any explanation?

[Mark]

Hi Milli,

The date in the "Price" export can't still be canged for format in Excel.

Can you describe what you're doing, and what happens (or isn't happening) to make you state this? If you can provide specifics, I can probably help better. I am able to change the format of the date column fine.

I think I see the confusion... It sounds like you are expecting there to be a 1:1 daily match between the portfolio value export and the price export. This is often not going to be the case. The portfolio value export is done every day, whether or not there is any pricing data recorded. The price export generates a data point for each actual recorded price in each investment. In the sample data the 4 investments have 3461 recorded prices, and MSFT has 3464 recorded prices, for a total of 17,308. The portfolio value export will have 3585 data points, as that is how many weekdays there are between the first recorded price and the last recorded price. The sample data does not have a data point recorded for every single weekday in the range, thus the reason 3461 is less than 3585. It is just sample data, it is probably missing a few days, and also doesn't have prices on holidays that land during the week, etc... For example, the sample IBM investment doesn't have a price recorded on Friday, 4/10/1998 (Good Friday), but the portfolio value will still export a data point on this date, as it is a weekday.

Are you trying to use the 2 different sets of data (prices / portfolio value) together somehow? Is this why you want there to be a 1:1 match?

[Milli]

Ok I understand how the sets work, there's a difference of about 10 holidays a year between actual trading days and portfolio days. Seems fair enough.

Yes I need a 1:1 daily match between portfolio and price. The reason for that is I'm calculating risk values like Performance Attribution, VaR, CAPM figs etc.
I've written a little macro that use a master date file to map portfolio values to prices, so that does the trick.

However the date supplied with the Price export can not be changed in Excel, I had to write a macro to get that sorted. The dates supplied with the Portfolio export on the other hand works fine to change withing Excel. I use Excel 2003 with a Vista OS.

[Mark]

Hi Milli,

I'm using Excel 2002 SP3 with Vista, maybe that is the reason for the different results. Here is what I did:

1) From within Fund Manager, do a "File / Export / Prices / Comma Separated...".

2) Start Excel, do a "File / Open..." and open the file I exported in step #1.

3) Hilight the date column (column C), and select "Format / Cells...". The dialog that comes up shows the "Number" tab. There is nothing selected in the "Category" list, and some text displaying "General format cells have no specific number format".

4) I select "Date" in the Category list, and then I'm given the choice of a bunch of different date formats in the "Type" list. I choose "03/14/01", and press OK.

5) My column C changes to the format I just selected, so the C1 cell is showing 01/02/96. If I select a specific cell in column C, and go back to "Format / Cells... / Number" the "Date" category is selected, and the same "Type" is still selected, just as I previously set in step #4.

I don't have Excel 2003 to test with. If you try to do this in Excel 2003 do you get some error, or are you not allowed to select "Date", or specifically what is preventing you from changing to the Date format for these cells?

[Milli]

Yes, that is excactly what I do plus there's a "Text to Columns" routine which I guess you do as well.
The C column is not, and can not, be formatted into date format.

As I said before, when exporting the portfolio values there's no problem changing the date format - so why does the portfolio export work and not the price export?

I use Excel 2003 SP3

[Mark]

plus there's a "Text to Columns" routine which I guess you do as well

I do not use this. I just do a "File / Open..." and select the .CSV file, and the fields automatically get put into columns.

The C column is not, and can not, be formatted into date format.

I think we're going around in circles on this one... How are you determining this? If you follow the instructions in my last post, do you get the same results? Isn't cell C1 displaying as 01/02/96? If you select cell C1 and choose "Format / Cells..." what does it show is the cell's format? It will help if you can provide a reason why you are saying it can't be formatted into the date format.

[Milli]

Is there any other way to communicate, I can show you the flow with screendumps. Email?

[Mark]

Hi Milli,

Sure. I've just sent you an email. You can reply to that with screendumps.

[Mark]

Hi Milli,

Thanks for the screen shots. I see what you mean. I'm not sure why your format specification isn't being applied. In Excel 2002 it works as you would expect. One difference between the price and the value export is that all fields have double quotes around them in the price export. This is the only difference. Both formats are valid CSV formats though. You can look at the CSV file in Notepad or any other text editor to see the raw data.

Also, in my copy of Excel when you open a CSV file it is not necessary to use the Text to Columns conversion tool, it automatically puts the fields into their own columns.