Different Quote Servers = Different Yields ...

[Septaj]

Hi Mark -

Perhaps you could help to explain this:

I downloaded historical prices from three quote servers for the same (not-owned) stock: AFLAC (AFL)

The quote servers were:

1) Yahoo Historical (from which distributions were also downloaded)
2) Yahoo Adjusted Historical
3) Bloomberg

I graphed each of these for the period Dec 28 2007 - Mar 12 2012 and observed the annualized TWR (%) for each graph as:

1) - 2.26
2) - 4.81
3) - 7.31

Question: Why the huge (and very significant) discrepancies in TWR?

Thx

[Mark]

Hi Septaj,

One factor would be that if you retrieve from Yahoo (Historical) and grab dividends, and then retrieve from Yahoo (Adjusted Historical) you will be double counting your dividends. When you retrieve from Yahoo (Historical) and choose to retrieve dividends you get actual dividend transactions recorded. The Yahoo (Adjusted Historical) does not record any dividend transactions, but all back prices are adjusted down for dividends and splits.

Besides this, I would take a look at your recorded prices. You didn't specify which yield term you were looking at, but if you're looking at the "gph" yield above a graph for Dec 28, 2007 - Mar 12, 2012, then look at your investment's pricing data for Dec 27, 2007 and Mar 12, 2012. Those are the 2 pricing points that affect the yield term for that time period. You can see this in the Data Register under the "Prices" data type.

[Septaj]

1) No to your first thought: each set of price quotes was retrieved independently.

2) Yes to your second idea: each of the three prices for both dates is distinct. But the variation in annualized return for 'gph' is enormous.

- why are the quotes different for different sources?
- isn't this a rather serious problem?

3) Does Fund Manager not include the effects of distributions when calculating TWR? Your help file for Time-Weighted Return suggests that it does.

[Mark]

Hi Septaj,

1) What do you mean by they were retrieved independently? Do you mean each one was retrieved into a separate investment file? Are all the other transactions/properties identical between these 3 independent investments? You could attach your 3 investment files to a post, and I can take a closer look.

2) If you retrieve the same security from different sources, they should be the same. Are you certain you're retrieving the same item? Did you retrieve for the same date range? Did all the prices retrieve okay? Some servers also use different symbols, so you want to make sure you are using the right symbol for the server you are retrieving from.

All servers except Yahoo (Adjusted Historical) retrieve actual closing prices. The Yahoo adjusted server is unique in that it retrieves back adjusted prices for dividends/splits. If you use Yahoo adjusted, you do not also want to have dividends and splits recorded, or you will be double counting them.

We have not identified the problem is yet..., so saying it is rather serious is premature.

3) Yes, TWR includes distributions.

Why don't you post or email us your 3 investment files illustrating this problem, so I can possibly help better.

[Septaj]

1) Yes, each to a separate investment. Unless I inadvertently retrieved "over" the Yahoo: Distribs with Yahoo: Adjusted, each is completely independent. I am attaching the 3 data files.

... "File is too big, maximum allowed size is 256 KiB." Hmmm.

So, Bloomberg Aflac is attached here; others to follow ...

2) Yes.

3) So how does FM handle Yahoo: Distribs vs. Yahoo: Adjusted when calculating TWR?

[Septaj]

Data files for each Yahoo apparently too large to attach (each exceeds 256 kB) ...

Perhaps you could repeat the experiment: Dates I am using Dec 28 2007 - Mar 12 2012

=-=-=-=

Plotting the three AFLACS on the overlay graph is interesting: on Share Price Overlay, the Yahoos are virtually identical, with Bloomberg significantly lower.

Share Price + Distibs Overlay: Yahoo distribs much higher than Yahoo Adjusted which is much higher than Bloomberg.

So this suggests the TWR will be different for each data source. (?)

[Mark]

Hi Septaj,

The TWR does not know/care where the data came from. The TWR uses your closing prices on the beginning of the yield term, and at the end of the yield term. It also uses your recorded distributions. I will send you an email, which you can reply to with the 3 investment files attached.

[Septaj]

"The TWR does not know/care where the data came from."

Problem is, not all sources include the same data (distributions).

[Mark]

Hi Septaj,

To get accurate TWR you need correct closing prices and distribution data. You can get closing prices from any server, they should all report the same closing prices for securities they cover. For distributions, you can either manually record your distributions you received, import them, retrieve them from the broker, or in the case of Yahoo (Historical) you have the option of retrieving them from Yahoo. It doesn't matter where the distribution data comes from, it should be the same data. Now, if you double count distributions, have the wrong closing prices, or have wrong distribution data you will get incorrect TWR. You just have to look at your data, and see what is wrong. Again, the important points are beginning/ending closing prices, and distributions. If you compare these in your 3 investments you should be able to spot the problem. If you can't, please email me your 3 example investments, and I can try to spot the problem for you.

[Septaj]

I've emailed you the 3 aflac data files. I'll also add that each of the other 5 TWR calculations (along the top of the graphs; for different time periods) were also not in agreement.

In the meantime, I think you should consider:

1) adding the functional Yahoo Historical server to Fund Manager Personal.

2) if you are unwilling to do this, then, at least, make it explicitly clear in the program that the ROI & TWR yield calculations - i.e. performance calculations, reports and graphs - are all invalid until the user ensures distributions have been added, and that none of the quote servers FM Personal makes available may be used to download these essential distributions.

Many of your FM Personal users are novice investors like me and you should not be placing the burden of making this critical discovery on your customers - many of whom may be making important investment decisions based upon your program.

[Mark]

Hi Septaj,

Thanks for emailing the 3 files. You must look at the beginning price, ending price, and all the distributions to compare them, and see why they are different. They are different. For the date range you mentioned, 12/28/07 - 3/12/12:

AFL.dat: (Your "Yahoo (Historical)" investment)
Beg Price: 55.32
End Price: 44.39
distribution transactions are recorded
TWR: -2.55

AFL1.dat: (Your "Yahoo (Adjusted Historical)" investment)
Beg Price: 54.53
End Price: 43.76
distribution transactions are NOT recorded
TWR: -5.09

AFLUS.dat: (Your "Bloomberg (Historical)" investment)
Beg Price: 62.49 * (You don't have a price for 12/27/07, only 12/28/07)
End Price: 44.73
distribution transactions are NOT recorded
TWR: -7.64

If I retrieve historical prices from the 3 servers in question, here is what I see for these same dates:

Yahoo (Historical) 62.14 - 44.73
Yahoo (Adjusted Historical) 54.53 - 43.76
Bloomberg (Historical) N/A - 44.73 (Doesn't have prices back to 12/27/07.)

So, it looks like your "Bloomberg" server got it's starting price from Yahoo (Historical), and then only updated the ending price from Bloomberg (Historical). Your "Yahoo (Historical)" server has distributions from Yahoo (Historical), but the prices don't match any that I retrieved from these 3 servers. Your Yahoo (Adjusted Historical) investment matches what I retrieved.

You cannot "mix" retrieving from these different servers that deal with back adjusted historical prices. Both "Yahoo (Adjusted Historical)" and "Bloomberg (Historical)" back adjust their historical prices for distributions/splits. If you get distribution transactions from Yahoo (Historical) you do not also want adjusted prices for distributions from Yahoo (Adjusted Historical) or Bloomberg (Historical) as you would be double counting distributions. Since Bloomberg doesn't have pricing back as far as you've requested here, you can't get historical TWR for that period by using that server.

[Mark]

Hi Septaj,

Thanks for your feedback, but I do not agree with your statement:

none of the quote servers FM Personal makes available may be used to download these essential distributions

You can use the Yahoo (Adjusted Historical) to get the effects of distributions in investments you do not own. For investments you own you can record your actual distributions to calculate accurate yields. You can retrieve this information from your broker, import from a file, or enter manually.

[Septaj]

You've evidently deleted my lengthy reply.

Very disappointing.

[Mark]

Hi Septaj,

The language in your prior reply was not professional, so it was removed. I emailed you a copy of your deleted reply, along with reasons why it was deleted. You're welcome to re-post that, correcting the reasons why it was deleted.

[Mark]

Hi Septaj,

To try and resolve the different yields I think it is easier to look at a smaller time frame which covers a single distribution. I used your security (AFL) and retrieved from the 3 quote servers:

Yahoo (Historical)
Yahoo (Adjusted Historical)
Bloomberg (Historical)

The last dividend in AFL was on 11/9/12 for $0.35/share, so I will look at a TWR yield for the date range of 11/1/12 - 1/4/13. Since this yield term starts with the closing price on 10/31/12, I retrieved historical prices from all 3 servers for the range 10/31/12 - 1/4/13. For the Yahoo (Historical) server I chose to retrieve distributions and record them as reinvested. Here were the results:

Yahoo (Historical)
11/1/12 - 1/4/13 TWR: 5.28%
10/31/12 - 1/4/13 Prices: $49.78 - $52.04
Distribution recorded on 11/9/12 for $0.35/share

Yahoo (Adjusted Historical)
11/1/12 - 1/4/13 TWR: 5.28%
10/31/12 - 1/4/13 Prices: $49.43 - $52.04
No recorded distributions

Bloomberg (Historical)
11/1/12 - 1/4/13 TWR: 4.54%
10/31/12 - 1/4/13 Prices: $49.78 - $52.04
No recorded distributions

So, as you can see the Yahoo (Historical) and Yahoo (Adjusted Historical) servers gave the same TWR. If you subtract the $0.35 dividend from the Yahoo (Historical) starting price of $49.78 you get the starting price of $49.43 seen with Yahoo (Adjusted Historical).

Looking at the Bloomberg (Historical) prices they are not adjusted for dividends as we describe on our quote server page:

http://www.fundmanagersoftware.com/intr ... #Bloomberg (Historical)

The Bloomberg (Historical) server seems to retrieve actual closing prices, just as with Yahoo (Historical). I checked Bloomberg for a couple other quotes, and none of them were showing dividend adjustments. It appears they've changed this, so we will update our quote server documentation page to reflect the current operation of this server. Since Bloomberg (Historical) does not include distributions this is why the TWR is lower.