Create Your Own Portfolio Update Tool with Excel

Portfolio Planning Article

Charles Schwab & Co., Inc.

Call us at 866-232-9890

Send us an email

Printer-friendly
Type Size: A A A
Email to a friend

Create Your Own Portfolio Update Tool with Excel
by Rande Spiegelman, CPA, CFP®, Vice President of Financial Planning, Schwab Center for Financial Research
March 11, 2005

If you take a self-directed approach to investing, chances are you use some kind of portfolio software to track your investments. There are lots of online tools and third-party software packages out there that can help you crunch the numbers. For example, Schwab clients can log in and access their own Portfolio Checkup tool.

But if you’re a real hardcore do-it-yourselfer and have a basic familiarity with spreadsheets, you might be interested to know that it’s relatively simple and painless to customize your own portfolio update and review tool using the Web Query function in Microsoft Excel. Even if you use an advisor to help you with your investments, you may still want to give it a try. After all, the more you know about what's going on with your portfolio, the better.

Assuming you're already familiar with how Excel works, here's an easy eight-step process for setting up your own automated portfolio quote tool. (This applies to Excel 2002, but the steps are similar for earlier or later versions. Check the Help function, as there may be some minor differences.)

1. Create an Excel file with two sheets. Label the first sheet "Portfolio" and the second "Quotes."

2. On the Portfolio sheet, type your stock and mutual fund ticker symbols in
column A.

3. On the Quotes sheet, place your cursor in cell A1. Click on Data in the menu bar, select Import External Data, and then select Import Data. A list of Web sites will appear. Choose MSN MoneyCentral Investor Stock Quotes and select Open. The Import Data box should appear.

4. Click on Properties in the Import Data box and make sure the following are selected with a check mark or dot:

Save query definition
Enable background refresh
Adjust column width
Insert entire rows for new data, clear unused cells

Click OK.

5. Click on Parameters in the Import Data box and then choose Get the value from the following cell. Then, click on the little square icon at the right of the text box. Now go to your Portfolio sheet and highlight the range of cells that contain your ticker symbols. Hit the Enter key on your keyboard and then click OK.

6. You're now back at the Import Data box. Click OK to load your query.

7. Now, go back to your Portfolio sheet and select the cell in column B next to your first ticker symbol. Type in "=" and then go to your Quotes sheet. Click on cell D4 (should be the Last price for your first ticker symbol). Hit the Enter key. The most recent price for your first security should now appear. Copy that cell down to fill in the most recent quote for your other securities.

8. Save your spreadsheet (call it My Portfolio or whatever you like) and you're done.

Anytime you're online and want to check your portfolio's value, simply open your spreadsheet, go to your Quotes page, click on Data in the menu bar and select Refresh Data. If you ever want to add additional ticker symbols, just follow the steps through No. 5 to change the parameters. Of course, you'll need to manually enter the values for any holdings that don't have unique ticker symbols.

Now you're ready to customize your portfolio tool by adding additional columns to contain various data and formulas. For example, you can add a Shares column and input the number of shares you own for each security. Then, simply multiply that number by the price in column B to create a Total column.

If you like, you can also set up a Gain/Loss percentage column [(Total - Cost) ÷ Cost] for each of your securities and for your portfolio as a whole. You can also create an Asset Allocation section where you add up all your stock, bond and cash holdings by category and divide by your total to automatically update your allocation percentages at the click of a button.

If you're really ambitious, you can even break down your equity holdings by market cap, style and sector. Have fun!

The information provided here is for general informational purposes only and should not be considered an individualized recommendation or personalized investment advice. The type of securities mentioned may not be suitable for everyone. Each investor needs to review a security transaction for his or her own particular situation. Data contained here is obtained from what are considered reliable sources; however, its accuracy, completeness or reliability cannot be guaranteed.

(0305-7255)

Return to Top