Portfolio tracking spreadsheet

[justin]

I need to develop a spreadsheet to track my portfolio. I primarily use Yahoo! Finance's portfolio tracker which works pretty well. But it doesn't do everything I want it to.

I was hoping some of you might share your spreadsheets with me and/or the forum.

Here's what I'm looking to do:

- record investment purchases and sales
- record capital gains distributions and dividend distributions
- maintain accurate records of purchases and cap gains to establish cost basis for tax purposes - I'd like to sell shares someday and take advantage of designating specific lots for sale instead of using average cost basis (this applies to taxable accounts only)
- be able to calculate annual and/or YTD return (I don't mind doing this manually within the spreadsheet) for all accounts
- get automatic price quote updates
- track multiple accounts and multiple investments

I have a couple of taxable accounts, a few roth and traditional IRAs for me and DW, and two 401k's btw me and DW. I also dollar cost average into some or many of these accounts each month. Almost exclusively mutual funds, but I typically have a total of 20 or so transactions per month.

I'm not sure how to set up my spreadsheet - whether I should organize it by mutual funds or by account type (401k vs IRA vs taxable) or by specific account (my 401k vs DW 401k).

I already have most of my historical data entered into yahoo finance that I can massage into shape when needed.

I know many of you have mentioned you have a spreadsheet that can do some or all of this. Could you post it on here or PM me with it? I'll probably build my own from scratch, but it would be good to see what others have done to assist me with setting up the layout of the spreadsheet in an organized manner. - Thanks!

edited to add: I've taken MSmoney and Quicken for a spin - didn't work out. Too cumbersome, too many complaints, no desire to upgrade every year. No desire to constantly troubleshoot something. No desire to do quality control on data received by money/quicken and QC on the correct classification and tax treatment of transactions.

[wabmester]

Post your spreadsheet when you're done so we can ride your coattails.

And check out gummyland for some macros and other goodies.

Originally Posted by kcowan

If you want to play with Excel, here is a great selection of spreadsheets.

The latest one that I am using is this download that will download anything that Yahoo quotes. He also supplies templates for downloading from other sites such as Morningstar.

[justin]

Originally Posted by wab

Post your spreadsheet when you're done so we can ride your coattails.

And check out gummyland for some macros and other goodies.

I'll post it when I get it done.

I already checked out gummy's site and found his portfolio tracker spreadsheet. Didn't do what I was looking for, but the yahoo! finance quote updater macro was cool.

[cute fuzzy bunny]

What about moneydance, jgnash, gnucash, or some of the other open source financial packages? One of them might meet your needs for free, with no work needed. My favorite combination.

http://www.fatwallet.com/t/52/432281/