Setting up my portfolio

[macandal]

Hello. I'm a newbie here and this is my first post.

I want to set up my portfolio as an Excel or Numbers (mac) spreadsheet. The fields I was thinking of including (to keep it simple) are:

• Name
• Symbol
• Shares
• Cost basis
• Mkt value
• Gain
• Gain %
• Overall return

Can someone help me set up the Cost Basis field. This is not as easy as it seems as several factors affect the cost basis. I'm sure someone here has done something like this before. I used to keep my portfolio in Google but that function doesn't always work. Besides, I don't mind updating something like this myself every month. Thanks.

[jIM_Ohio]

I would manage this differently

you are tracking the right things, but trying to track them on one sheet will be tough.

Sheet 1, list account numbers and how you access each account

Sheet 2 list a high level view (year over year) of each account listed on page 1

yearly beginning value
yearly ending value
deposits
withdraws
rate of return

sheet 2 is high level only

sheet 3 list all deposits and withdraws for fund abc in account 1

sheet 4 list all deposits and withdraws for fund def in account 1

sheet 5 list all deposits and withdraws for fund xyz in account 2

Sheets 3-4-5 are formatted very differently than 1 and 2
you can pull information for sheet 2 from the other sheets after it.

[macandal]

Like I said, I'm a total newbie. This is my very first time doing anything like this, so I need a lot of help.

Sheet 1, list account numbers and how you access each account

There is only one account, so... One account that has 5 or 7 companies.

Also, what do you mean by "...and how you access each account"?

Sheet 2 list a high level view (year over year) of each account listed on page 1

yearly beginning value
yearly ending value
deposits
withdraws
rate of return

Some of these things I don't know how to set up. For example, when you say beginning/ending values, you mean the market value (at market's closing)? Rate of return is what I'm calling here "Overall return", right?

sheet 3 list all deposits and withdraws for fund abc in account 1

So, in my case, this will only have one account.

sheet 4 list all deposits and withdraws for fund def in account 1

sheet 5 list all deposits and withdraws for fund xyz in account 2

N/A

Sheets 3-4-5 are formatted very differently than 1 and 2
you can pull information for sheet 2 from the other sheets after it.

Can you explain to me how they are different and how to set them up?

[jIM_Ohio]

Like I said, I'm a total newbie. This is my very first time doing anything like this, so I need a lot of help.There is only one account, so... One account that has 5 or 7 companies.

Also, what do you mean by "...and how you access each account"?

Some of these things I don't know how to set up. For example, when you say beginning/ending values, you mean the market value (at market's closing)? Rate of return is what I'm calling here "Overall return", right?

So, in my case, this will only have one account.

N/A

Can you explain to me how they are different and how to set them up?

How you access each account would be a link to website

this way if bookmarks get hosed, you still have where the the website is...
and eventually you will have a 401k provider, IRA provider, brokerage... you need to plan for expansion.

If you are tracking annual return, the portfolio value is the Dec 31 value for year end value.

"return" is misleading unless a time stamp is put on it...

for example I can get you a 100% return on your money- I can guarantee it actually.

However if you want a 100% ANNUAL return, I cannot guarantee that once you put a time requirement on it.

[macandal]

How you access each account would be a link to website

this way if bookmarks get hosed, you still have where the the website is...
and eventually you will have a 401k provider, IRA provider, brokerage... you need to plan for expansion.

I'm not tracking ALL OF MY ACCOUNTS. I'm only tracking my personal investing account, the one I set up myself. This is not through work (457 & pension) or anything else. I only want to track my own investing account.

[macandal]

How you access each account would be a link to website

In any case, I don't know how to do what you suggest. Connecting my spreadsheet (or notebooks) to an external (online) site.

I guess what I had in mind was something, a spreadsheet I would set up manually, entering my monthly buys by hand, etc.

[Penny Pincher]

Don't most of the on-line stock trading houses do this for you for free?

[macandal]

Don't most of the on-line stock trading houses do this for you for free?

Yes, maybe, but I want to set this up myself.

Penny, sorry, that may have sounded like a smart-ass remark on my part, but I want to assure you that was not the case. Let me explain more.

I am a very hands-on, learn-by-doing person. I can read in a book that 1 + 1 equals 2 and I may, sort of, get it, but I won't really understand the concept and apply it beyond 1+1 until I take a pencil and a piece of paper and do the arithmetic myself. That's just how I'm wired.

Yes, my online trader, sharebuilder.com, does it for me, well, now that I think about it, no, I don't think they do, but Google's "My Portfolio" did. However, I found Google to be moody. It would act up at times and I lost confidence in it. That, plus my statement above about being a hands-on person.

That's why I want to do it myself, set up my own spreadhseet and enter my monthly buys on my own, so I can learn more.

I hope that clarifies things and my apologies if my previous statement seemed disrespectful.

[macandal]

Well, did I stump you?

I know people don't generally do profit margin themselves, instead they use a service to do this for themselves, but, no one here has ever done a spreadsheet/book like the one I want to make?

[isralexba]

I would add other items that are usually not that common but do add a lot of insight (in my opinion at least)

add the reason you thought you should buy this paper (or sell) - in a post review of your portfolio you will be able to evaluate WHY you decided to buy or sell

Add your expactation for the increase in the value of the asset (I guess that if you are expecting it to fall - the you wouldnt buy it ) - this will help you to sell when the asset has maxed out its potential.

Cheers.

Alex.

[jIM_Ohio]

In any case, I don't know how to do what you suggest. Connecting my spreadsheet (or notebooks) to an external (online) site.

I guess what I had in mind was something, a spreadsheet I would set up manually, entering my monthly buys by hand, etc.

I was suggesting a link
not download info from link to spreadsheet

for example I have the T Rowe Price url in my spreadsheet, so if wife needs to access accounts, she has all links to all financial institutions in 1 place.

[jIM_Ohio]

You can track what you stated originally... most people figure out what to track once they start tracking and want more information.

Best of success to you.

[nest egg investor]

Unrealized Gain Loss Spreadsheet

I believe Yahoo Finance may offer all of these fields. I am curious why you say that the cost is tricky. Is this because of commissions or because you are buying into an investment at different times?
I use a spreadsheet provided by my broker that tracks all of this. Then I keep an Excel spreadsheet with bi-annual updates, or as needed.
Hope this helps. I have a spreadsheet I would be happy to share, but do not see how to do so.
Good luck!

[macandal]

I am curious why you say that the cost is tricky. Is this because of commissions or because you are buying into an investment at different times?

Both. Plus, splits, dividend reinvestment, sales, etc.

[EEinNJ]

macandal, the spreadsheet fields you describe are exactly what I've used for several years now. I don't bother with YTD returns, that's easy enough to check when you get an updated quote. I make a new copy of the spreadsheet each year, and over time I've dressed it up with pie charts and line graphs to visualize allocation and overall performance over time.

Advantages of doing this yourself vs. using on-line tools is integrating multiple accounts, better customization, not having to rely on web access or some sites flaky/changing tools, security.