14 Download Mutual Fund Data





Download Mutual Fund Data

figs/moderate.gif figs/hack14.gif

Mutual fund companies feed their features and statistics to the companies that provide you with mutual fund reports, so you won't find much of a difference between mutual fund numbers from web site to web site. However, each web site tweaks its presentation of data to make it easier to find data. After you decide which web site you prefer, use Excel web queries [Hack #7] to download data into a spreadsheet, refreshing the data whenever you want or grabbing data for another mutual fund.

1 Picking the Data Behind Door Number One or Door Number Two

Two web sites duke it out as the top dogs of mutual fund data, and they both offer data from Morningstar. Whether you choose Morningstar (http://www.morningstar.com/Cover/Funds.html) or Yahoo! Finance (http://biz.yahoo.com/funds/) depends on the analysis you want to perform and the comparisons you want to make. In fact, because the data that these sites offer is so similar, you can download data from either site, collecting the data from the site that makes it easier to get what you want.

It's no surprise that Morningstar.com offers Morningstar data. Morningstar offers two versions of its data—one for free and the other by subscription. You can analyze mutual funds quite well with Morningstar's free data, so we'll focus on that data in this hack. When you evaluate mutual funds, the salient features boil down to expenses, turnover, manager tenure, and total returns (tax-adjusted, load-adjusted, or as is). As it turns out, you can't get everything you want for a thorough mutual fund study from one site, so Figure summarizes what each site offers.

Morningstar's paid subscription includes much more than a few more fund statistics [Hack #20]. You can receive analyst reports, including in-depth studies of stocks and mutual funds, plus analyst picks and pans. The Premium membership also offers some cool portfolio tools such as the Portfolio X-Ray® [Hack #77], which analyzes your portfolio allocation and diversification for the individual stocks, bonds, and investments held by the mutual funds you own.


Morningstar.com and Yahoo! Finance each offer presentations of Morningstar mutual fund data

Mutual fund data

Morningstar.com

Yahoo! Finance

Category

Morningstar category

Morningstar category

Expenses

Expenses are broken down into twelve items with the category average for the total expense ratio

Expenses are broken down into seven items with the category average for six of them

Manager tenure

Manager start date

Manager start date

Turnover

Annual holding turnover percentage

Annual holding turnover percentage

Total return summary

Trailing returns for one-year, three-year, five-year, and ten-year periods, and short-term average returns

Trailing returns for one-year, three-year, and five-year periods, and some short-term average returns

Annual total returns

Seven years of annual total returns with comparisons to average returns for the category and a comparable index

Ten years of annual total returns with comparisons to average returns for the category and a comparable index

Tax-adjusted returns

Tax-adjusted returns for three-year, five-year, and ten-year periods; the tax cost ratio; and potential capital gains exposure percentage

No tax-adjusted returns


2 Building a Mutual Fund Data Workbook

For analysis, mutual funds require less data than stocks call for, but there's enough data that you don't want to copy it all manually. You can use Excel web queries to capture the mutual fund data you want and then evaluate that data in a mutual fund analysis spreadsheet [Hack #71].

You can heave all your mutual fund data onto the same Excel worksheet if you want to store all the data for one mutual fund in the same place. Because Morningstar.com and Yahoo! Finance distribute mutual fund data across several web pages, your worksheet holds several web queries. You can also create each web query on a separate worksheet, as demonstrated in Figure.

Segregating web queries on different worksheets helps organize data
figs/oih_0304.gif


Follow the instructions in [Hack #7] to define web queries for mutual fund data. To navigate to the starting point for the web queries, follow these instructions:


The Morningstar Mutual Funds Page

Navigate to http://www.morningstar.com/Cover/Funds.html. Type the fund ticker symbol in the box immediately below the top toolbar and click Quotes/Reports.


Yahoo! Finance Mutual Funds Center

Navigate to http://biz.yahoo.com/funds/, type the fund ticker symbol in the Enter Fund Symbol box, and then click Get Quote.

The sample Excel mutual fund workbook includes seven worksheets, each containing a different type of mutual fund data. Cells in the Fund Summary worksheet contain the ticker symbols that feed into the parameters for the web queries on the other worksheets. Figure includes instructions for specifying the tables to query to collect the mutual fund data you want.

Define web queries for each type of mutual fund data

Mutual fund data

Web site to use

Instructions

Snapshot

Morningstar.com

In the New Web Query dialog box, select the Snapshot tab. Click the yellow table indicator to the left of the Morningstar Category label to obtain the category, minimum investment, and fund manager's start date. Scroll down to the Portfolio Analysis section and click the yellow table indicator to the left of the Annual Turnover % label to obtain the annual turnover.

Average returns

Morningstar.com

Select the Total Returns tab. In the Trailing Total Returns section, click the yellow table indicator that points to the Total Return % label to obtain total returns over a number of periods for the fund and indexes with similar investment strategies.

Annual returns

Yahoo! Finance

On the Quotes & Info page for a mutual fund, click the Performance link under the Fund heading in the navigation bar. In the Annual Total Return (%) History section on the Performance page, click the yellow table indicator that points to the Year label to obtain the annual returns for the last ten years for the fund and the fund category.

Tax analysis

Morningstar.com

Select the Tax Analysis tab. In the Tax Analysis section, click the yellow table indicator that points to the 3-Yr Avg % label to capture Morningstar's tax analysis measures.

Expenses

Morningstar.com

Select the Fees & Expenses tab. In the Fees and Expenses section, click the yellow table indicators to the left of the Initial, Total Cost Projections, Administrative, and 12b-1 labels.

Risk

Morningstar.com

Select the Risk Measures tab. In the Volatility Measures section, click the yellow table indicator to the left of the Standard Deviation label. In the Modern Portfolio Theory Statistics section, click the yellow table indicator that points to the Standard Index label.


Be sure to check the Refresh automatically when cell value changes checkbox when you specify the cell for the query parameters so that the web queries retrieve data from the Web each time you type a new ticker symbol in the ticker symbol cell on the Excel summary worksheet.



     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows