Tracking Stock Investments In MS Excel
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.
- 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”
Don’t know any Excel? No problem – use my template!
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.
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: