Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Portfolio tracking spreadsheet
Old 01-24-2007, 12:03 PM   #1
Thinks s/he gets paid by the post
 
Join Date: Jun 2005
Posts: 4,005
Portfolio tracking spreadsheet

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

__________________
justin is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Re: Portfolio tracking spreadsheet
Old 01-24-2007, 12:13 PM   #2
Thinks s/he gets paid by the post
wabmester's Avatar
 
Join Date: Dec 2003
Posts: 4,459
Re: Portfolio tracking spreadsheet

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

And check out gummyland for some macros and other goodies.

Quote:
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.
__________________

__________________
wabmester is offline   Reply With Quote
Re: Portfolio tracking spreadsheet
Old 01-24-2007, 12:18 PM   #3
Thinks s/he gets paid by the post
 
Join Date: Jun 2005
Posts: 4,005
Re: Portfolio tracking spreadsheet

Quote:
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.

__________________
justin is offline   Reply With Quote
Re: Portfolio tracking spreadsheet
Old 01-24-2007, 12:43 PM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
cute fuzzy bunny's Avatar
 
Join Date: Dec 2003
Location: Losing my whump
Posts: 22,697
Re: Portfolio tracking spreadsheet

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/
__________________
Be fearful when others are greedy, and greedy when others are fearful. Just another form of "buy low, sell high" for those who have trouble with things. This rule is not universal. Do not buy a 1973 Pinto because everyone else is afraid of it.
cute fuzzy bunny is offline   Reply With Quote
Old 12-31-2009, 09:49 AM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
calmloki's Avatar
 
Join Date: Jan 2007
Location: Independence
Posts: 5,459
Kind of a dummy on this stock stuff - have a problem tracking actual gains and losses. F'rinstance, in Morningstar stocks can be tracked, but when a big loser gets sold it can be left in the portfolio so we can watch its stratospheric gains (that we're not actually participating in; or deleted (and its as if the loss never happened and our annual gains look juuuust super! wanna buy more stocks?). Surely there is a program that one can enter stock purchases and sales in, that can track gains or losses while one owns the stock but doesn't forget the gains or losses when the stock is sold, right? Looked at Gummy's list of excel thingies, which is extensive and baffling. Whatn'ells a Raff-regression or a Kelly-curvature?

Is this what I'm looking for?
my Portfolio

Oh - excel? I don't do him so very much....
__________________
calmloki is offline   Reply With Quote
Old 12-31-2009, 10:21 AM   #6
Thinks s/he gets paid by the post
grumpy's Avatar
 
Join Date: Jul 2004
Posts: 1,321
My spreadsheet is quite large and is structured as follows:

1. I have sheet for each individual investment which contains a section for each buy or sell. The columns are: date, transaction type (e.g. buy, reinvest div, st gain, etc.), # of shares, price, cash dividends, cost, reinvested dividends. At the top of the sheet I have the dividend rate, and formulas that calculate avg. cost of shares bought, avg cost of shares sold, yield and total annual dividend. The header of the sheet also has the online login id and pw, the phone # for the broker/mutual fund and the ticker symbol.

At the front of the workbook, i have summary sheets:

Total Portfolio with separate totals for taxable and retirement accounts (both $ and %) and subtotalled equities, bonds, cash and tax deferred.

Retirement Account summary - shows each IRA account with columns for # of shares, current price, avg cost, market value, price as of date, and % of the total

Stock summary - lists each stock owned with columns for # of shares, avg price, current price, original purchase cost, value of shares sold, net purchase cost, cash dividends, current value, net return, net return %, total return, effective div yield, annual dividend, yield, and percentage of total stocks.

Prices Sheet - this feeds the other sheets. Has colums for stock/mutual fund name, price, date, current yield. I have this set up in the same order as the portfolio that I maintain on the WSJ on-line site. At the end of the week I export the WSJ portfolio prices to Excel and paste the closing prices into this sheet which updates everything else in the workbook.

Allocation Sheet - this sheets groups all individual investments into categories for Large Cap stocks, mid cap, small cap, international, and specialty and had bond and fixed income sections. I also plug in here the imputed bond equivalent value of our pensions and treat this as a bond holding in calculating overall portfolio allocation.

I updated this spreadsheet once a week on Sat. morning, print any sheets that have changed, and maintain a three ring binder for reference and backup. The whole process takes about 30 mins.

At the end of the year I copy the whole sheet and rename it for the following year and archive the prior years file. I use the year end data to update an Excel bar chart of annual portfolio performance.

I started this spreadsheet in 1992 and it has grown and evolved as I recognized needs for information.
__________________
grumpy is offline   Reply With Quote
Old 05-29-2010, 08:57 PM   #7
Confused about dryer sheets
 
Join Date: May 2010
Posts: 1
Quote:
Originally Posted by grumpy View Post
My spreadsheet is quite large...
Hi Grumpy,

Your spreadsheet sounds very interesting. Would you care to share it with us? [without real data, of course]

Zad
__________________
zad886 is offline   Reply With Quote
Old 05-29-2010, 09:10 PM   #8
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
bbbamI's Avatar
 
Join Date: Dec 2006
Location: Dallas 'burb
Posts: 9,039
Wow...this thread has legs....
__________________
There's no need to complicate, our time is short..
bbbamI is offline   Reply With Quote
Old 06-28-2010, 05:01 AM   #9
Confused about dryer sheets
 
Join Date: Jun 2010
Posts: 1
Hi Grumpy,
Any chance you could send this over to me as its what I have been trying to develop myself?
Thanks, baz
__________________

__________________
sugardaddybaz is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SWR - Amortize your portfolio RgrGd FIRE and Money 5 03-28-2007 11:29 AM
Portfolio tracking software jimhcom FIRE and Money 5 12-28-2006 08:55 AM
More Buckets, Portfolio Allocations, and Rebalancing tomz FIRE and Money 3 10-11-2006 05:25 PM
Income portfolio modeling spreadsheet? BD1 FIRE and Money 1 05-29-2006 06:58 AM
How do you calculate your portfolio performance? Surfdaddy FIRE and Money 13 04-13-2006 08:32 AM

 

 
All times are GMT -6. The time now is 04:45 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.