How to Track Mutual Funds and Stock Portfolios Using Excel

by Patrick Gleeson

    Investors increasingly manage their own stock portfolios using a wealth of stock and mutual fund research and guidance from brokerage websites. The stock-tracking software available from online brokerages is less impressive, generally delivering a minimum of information and analysis. Commercially available stock trackers allow you to track and analyze your portfolio of stocks and mutual funds in more detail. You can also construct one yourself using Excel, adding features as needed as you become more familiar with the program.

    Open a new Excel spreadsheet. In the "File" menu, go to "Page Setup" and click on "Landscape." Close the "Page Setup" menu. Go to row 1, column A, and enter "Equity." In the adjacent box to the right, 1B, enter "Symbol." In 1C enter "Date Acquired." In 1D enter "Per Share." In 1E enter "No. Shares"; in 1F enter "Amount"; in 1G enter "Present Value"; in 1H enter "PV Date"; in 1I enter "PV Amount" and in 1J enter "Gain/Loss."

    Format each column by clicking on the capital letter at the top of each column. The entire column will turn a light blue, indicating it is ready to be formatted. Go to the "Menu" bar, click on "Format," then in the format menu, click on "Cells." When the "Format Cells" menu becomes available, click on "Number," and in the "Decimal" places box, click on 2. Do this for each of the following columns: D, F, G,H. I and J. In column J, also select a negative number format: (1234.10) Click on column B, "Acquired." Go to the menu bar, click on "Format," then in the format menu click on "Cells," then click on "Date" and select the date format. Repeat the process for column H and PV Date. Columns A, B and E do not require formatting.

    In A1, enter your first equity, for example, Widget Corp. In 1B, enter Widget's symbol, say, WCI. In 1C, enter the date. In 1D, enter the price per share you paid for the equity, say, 10.41. In 1E, enter the number of shares, say, 1,000. In 1F, enter the formula: =D2*E2. Click on the total, which is 10,410.00, and copy it for 20 or 30 more cells below F1. You can repeat this process whenever you need to enter more equities. In G1, put in the present value of the equity, say, 12.08. In H1, put in the present date, say, 08/12/13. In 1I, put in the formula for present value amount, which is: =G2*E2. Copy this for 20 or 30 more cells below I1. In 1J, copy for the formula, calculating gain or loss, which is: =I2*F2.

    You can download all present values of your equities with a single operation. Go to an online source, such as an online brokerage, and create a watchlist. Enter each of the equities in your spreadsheet in the watchlist, being careful to enter them in the same order as on your spreadsheet. Access your watchlist online, showing you the present value of each equity. Copy the entire list of values and copy it into the "Present Value" column of your spreadsheet.

    Photo Credits

    • Comstock Images/Comstock/Getty Images

    About the Author

    Patrick Gleeson received a doctorate in 18th century English literature at the University of Washington. He served as a professor of English at the University of Victoria and was head of freshman English at San Francisco State University. Gleeson is the director of technical publications for McClarie Group and co-manages a small stock investment fund.

    Zacks Investment Research

    is an A+ Rated BBB

    Accredited Business.