Excel-ent Stock Reports

Today TaxMama hears from HD in a TaxQuips comment who wants to know, “I have bought and sold a lot of stock this year. Where can I found how to do the calculations for tax purposes. I don’t like the software that is out there, I use Excel. Can you help me work out how to do this for small player like me?”


Well HD If you’ve really got a lot of transactions, all I can say is PRAY. Pray that your brokerage has a “Tax-Basis Sales Summary”.

If they do, you’re in luck. Big time. All you need to do is to get that printout and look it over carefully to make sure it’s correct.

Be sure that any reinvested dividends are added in. And if any of your stock split – split the total cost among all the shares of the stock you received. Remember to include stock in spin-off companies.

When the companies issue fractional shares, I usually just use -0- as the cost. It’s minor.

f you must do it yourself via Excel, it’s simple, really. Just set up the follow columns:

Description – use that for the name/ticker of the stock and the number of shares sold.

Purchase date – that’s self-explanatory, right? Unless you sold a stock that split.
Then your purchase date is the date you purchased the original stock.

Sale date – that IS self-explanatory. It’s the date on the 1099-B or the year-end summary.

Sales Price – also self-explanatory. It’s the proceeds shown on the 1099-B or the year-end summary

Cost – now that can be difficult for people holding stock long-term. But for short-term traders – it should be pretty straightforward.  It’s the price you paid for the stock, plus commissions. If you bought 100 shares and are only selling 50, simple, divide the total cost by 100 and multiply
by the 50 shares you just sold.

You won’t have to worry about reinvested dividends on short-term holdings.

And if you’re selling stock short, just remember the purchase date will come AFTER the sale date. But the purchase price will still be the price you pay to BUY the stock. So don’t mix those up on the spread sheet. If you’re holding any stock long-term, just set it up on it’s own page on the spreadsheet so you can track splits, reinvested dividends, etc.

And, remember, you’ll find answers to questions about your stocks and other tax issues, free. Where? Where else? At TaxMama.com

  • Ask TaxMama :: Where taxes are fun and answers are free

File Download (2:41 min / 1.9 MB)

Leave a Reply