How To Update Excel Portfolio Mutual Fund Prices

How to Track Mutual Funds and Stock Portfolios Using Excel

Create an Excel spreadsheet to track your investment portfolio. — Create an Excel spreadsheet to rails your investment portfolio.

Comstock Images/Comstock/Getty Images

Investors increasingly manage their ain stock portfolios using a wealth of stock and common fund inquiry and guidance from brokerage websites. The stock-tracking software available from online brokerages is less impressive, more often than not delivering a minimum of information and analysis. Commercially bachelor stock trackers let you to track and clarify your portfolio of stocks and common funds in more detail. You can also construct 1 yourself using Excel, adding features as needed as yous become more familiar with the program.

Construction

Open a new Excel spreadsheet. In the "File" card, get to "Folio Setup" and click on "Landscape." Close the "Folio Setup" bill of fare. Go to row one, column A, and enter "Equity." In the side by side 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 "Nowadays 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 upper-case letter at the elevation of each column. The unabridged column volition turn a light blue, indicating it is ready to exist 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. Practise this for each of the following columns: D, F, Grand,H. I and J. In column J, also select a negative number format: (1234.10) Click on column B, "Caused." Go to the carte bar, click on "Format," then in the format menu click on "Cells," then click on "Appointment" and select the date format. Repeat the procedure for column H and PV Date. Columns A, B and Eastward practise not crave formatting.

Data Entry

In A1, enter your get-go disinterestedness, for example, Widget Corp. In 1B, enter Widget's symbol, say, WCI. In 1C, enter the date. In 1D, enter the price per share y'all paid for the disinterestedness, say, ten.41. In 1E, enter the number of shares, say, i,000. In 1F, enter the formula: =D2*E2. Click on the total, which is x,410.00, and copy it for 20 or 30 more cells beneath F1. You can repeat this process whenever you need to enter more equities. In G1, put in the present value of the disinterestedness, say, 12.08. In H1, put in the present appointment, say, 08/12/13. In 1I, put in the formula for nowadays value amount, which is: =G2*E2. Re-create this for 20 or 30 more than cells beneath I1. In 1J, copy for the formula, computing proceeds 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, beingness careful to enter them in the same club equally on your spreadsheet. Admission your watchlist online, showing you the nowadays value of each equity. Copy the unabridged list of values and copy information technology into the "Present Value" column of your spreadsheet.

References

Resources

Writer Bio