How to Track Mutual Funds and Stock Portfolios Using Excel

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.

Structure

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."

Formatting

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.

Data Entry

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.

Downloading Data

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.