Tracking Stock Investments In MS Excel

I hear from many people and read their posts on the Internet about how they just don’t have an adequate tool for tracking their investments. Last year I was hired to help just such a client and what resulted was a powerful investment tracking template that makes it easy for someone who doesn’t know anything about MS Excel to track their investments in the stock market.

Does it take a lot of time to figure your capital gains and losses when you go to file your taxes?

It doesn’t have to. If you know just a little bit of Excel you can probably set up your own template to track this. You just need the columns set up so you can keep track of the information. The most important thing is figuring out the right basis when you go to sell a particular security. If you don’t have a lot of activity then this should not be too difficult. All you have to do is track your investment activity in MS Excel, sort it by security and figure your gains and losses each time you sell something by looking at the previous purchases and sales and calculating how many share you have left at each price you bought the security at. Then determine which lot to deduct your shares sold from to figure your capital gain or loss on that investment. Once you have a template for tracking your investments set up in Excel as long as you keep it current it will not be too much work going forward.

Here are the columns you need to set up in MS Excel in order to be able to track this:
  • Date
  • Company
  • Symbol
  • Transaction (Buy/Sell)
  • Quantity Bought / (Sold)
  • Cost per share (for Buys)
  • Total Cost [Quantity * Cost Per Share] or [Original Cost of shares on a “Sell” transaction]
  • Selling Price per share (for Sells)
  • Total Selling Proceeds [selling price * Quantity]
  • Gain/Loss [Total Selling Proceeds – Total Cost]
  • Optional – Quantity on hand to keep track of how many shares are left in a particular lot / “Buy”
Even if you are going to do this yourself, I would read on and watch the video so you can see how I laid mine out. Of course mine is more complex (it automates a lot of what you will have to do manually unless you can write formulas like the ones I wrote to do this). The real trick and challenge of it is getting it to keep track of the quantity on hand for each lot (buy).

Don’t know any Excel? No problem – use my template!

Add to Cart

If you can enter a date, choose from a drop down, and enter a few simple numbers on a form then you can use my template and you can get the information you need out of if quickly.

Other options
There are those who use Quicken to track their investments and say they love it and that is good. If that works for you then you should stick with that. But if that is giving you trouble then you might want to consider other options.

Many people say it can’t be done in QuickBooks. I proved that theory wrong in my video on How To Track Stock Investments In QuickBooks – I know it’s a catchy title.

Then there are those who might take me up on my suggestion to us QuickBooks to track their investments, especially if every other aspect of their financial lives is already tracked in QuickBooks. Makes sense right?

Then there are people who aren’t already using either one of these programs and just need a way to track their investments such that they can keep track of their gains and losses with respect to the correct “lots” to be sure the capital gains are calculated correctly. You would think your brokerage firm would do this for you right? Even if they did you would want to double check it.

Oh one last thing! If you like the stock market then you will probably LOVE the project I launched this year called Analysts Online. I am looking for contributors. People who can write about the stock market at the Macro level. If that is something that interests you and you don’t mind writing and making a little money on the ad revenue share then  be sure and watch the video below on that opportunity.

Please enjoy the video and please post a comment below – let me know what you got out of this.

Analysts Online – Become A Contributor

Apply Here if you are interested:

Become A Contributor