Forum Discussion
Stock data type does not report correct Performance metrics
Hello,
I've been using the stock data type in Excel 365 and I've noticed that some of my mutual funds are reporting dramatically different Performance metrics as compared to Yahoo and others.
For instance, for mutual fund VMCIX, Excel is reporting 1/3/5-yr annual returns as 16.9%, 3.6%, and 10.1%, respectively.
But Yahoo is reporting for the same metrics, 32.50%, 6.61%, and 11.96%.
Can anyone explain the discrepancy? Are there any solutions to get more accurate (preferably, free) data in Excel?
Thanks!
David
Thanks, Kidd_Ip , but I think I figured out the problem: the Refinitiv data being used by the Excel stock data types are pulling day-end trailing returns for each timespan, while the default data being displayed by Yahoo and Morningstar are the month-end trailing returns. It would be nice if that was a little more clear in all places.
5 Replies
Just for the record, Google Finance has different results than either Excel or Yahoo.
I strongly suspect that SergeiBaklan has the right explanation. It's not that any one of them is "right" and the others "wrong" -- you just need to get behind how the calculations are done.
My guess we speak about different metrics. STOCK returns average returns, and indicator from YAHOO are for Load Adjusted Returns.
If we take 5-year average return on YAHOO
it's practically the same which STOCK gives for today. YTD return at the end of December is close to 1-year average return, i.e. about 17% which STOCK shows. And that's not 32% of 1-year adjusted return.
Thanks, Kidd_Ip , but I think I figured out the problem: the Refinitiv data being used by the Excel stock data types are pulling day-end trailing returns for each timespan, while the default data being displayed by Yahoo and Morningstar are the month-end trailing returns. It would be nice if that was a little more clear in all places.
Consider below:
- Data Source Differences: Excel and Yahoo Finance might be pulling data from different sources or using different methodologies to calculate returns. This can lead to variations in reported performance metrics.
- Update Frequency: The frequency at which data is updated can also cause discrepancies. Yahoo Finance might update its data more frequently than the data source Excel is using.
- Calculation Methods: Different platforms might use slightly different methods to calculate annual returns, especially if they are accounting for dividends, fees, or other factors differently.
To get more accurate and consistent data in Excel, you can try the following solutions:
- Use a Reliable Add-in: Consider using a financial data add-in for Excel, such as the Yahoo Finance Add-in or Morningstar Add-in. These can provide more accurate and up-to-date financial data directly within Excel.
- Manual Data Entry: For critical data points, you might manually enter the data from a reliable source like Yahoo Finance or Morningstar.
- Check Excel's Data Source: Verify the data source that Excel is using for its stock data type. Sometimes, switching to a different data provider within Excel can resolve discrepancies.
- Cross-Reference Multiple Sources: Regularly cross-reference data from multiple sources to ensure accuracy. This can help you identify and correct any discrepancies.
I think I figured out the problem: the Refinitiv data being used by the Excel stock data types are pulling day-end trailing returns for each timespan, while the default data being displayed by Yahoo and Morningstar are the month-end trailing returns. It would be nice if that was a little more clear in all places.
