Not bragging. Just saying. I’ve been investing for over 30 years. I started investing in about 1977. Thank you US Steel. Your stock helped me buy my first camera. (And, thanks to my parents for showing me how to buy and sell.) The records of those early trades may only exist in the IRS’ files. It would be six years before I had a computer for tracking my investments. Since then, a spreadsheet has grown that tracks almost three decades of trades. I didn’t realize until recently that it might be useful for others. A friend asked for a copy as a template to build theirs. While I am comfortable including the summaries in my book, Dream. Invest. Live., I am at least private enough to not pass along the raw data. If the spreadsheet is useful to them, it might be useful to you; so, for the data geeks out there, here’s how I track my stocks.

A bit of an introduction first. All I am passing along are the column headings, because they convey the majority of the practicality of the spreadsheet. Most of the columns are simple data entries like buy and sell dates. Most of the math is simple arithmetic like addition and subtraction, with a bit of multiplication and division. The opportunities for compound interest calculations I leave to those who actually know enough to ask the question, because most of them already know how to perform the calculation, or at least how to get the spreadsheet to do that work. I don’t find fine detailed analyses to be necessary because a trade was either obviously good, obviously bad, or close enough to market performance that luck could explain much of the difference.

Okay, a bit more of an introduction. As I’ve said before, I am not a financial professional. Your professional advisor or your broker should be able to provide you this same information. But. I found having such a spreadsheet to be handy because over the decades I’ve changed brokerages, or had them changed for me; and because the realities of life mean inevitable complexities that can’t be foreseen by automated and institutionalized external entities. Life happens.

Enough with the introductions. Here are the column headings as of January 11, 2015. The original file was simpler. The file will undoubtedly change, unless something bizarre happens. Bizarre happens. Stay tuned.

Column Headings

• stock (Duh. Simple except that names change, even without mergers and acquisitions.)
• date acquired (I use the trade date instead of the settlement date.)
• date sold (I use the trade date instead of the settlement date.)
• days held (To check for long term capital gains, and my curiosity.)
• sales price (The total value of the sale, not the per share price, including cost of commissions.)
• cost basis (The total price of the purchase, not the per share price, including cost of commissions.)
• profit (For tax purposes, but also because I also want to see how I did in terms of real, spendable dollars.)
• % return (This is the total % gain, regardless of how long I held the shares.)
• simple ROI (I use the ridiculously simple version for Return On Investment based on the profit divided by years held because reality can be too complex. It usually is not as simple as buy a share sell a share)
• shares bought (If life is simple, this is the number of shares bought. And yes, it can get more complex than that.)
• shares sold (The shares sold may not equal the shares bought if I sell only a portion of them.)
• total sale (Note, the sales columns come before the buy columns because I like to subtract from left to right so profits are positive. It is simple, but I aim for simplicity. The total sale may involve selling shares from different purchase dates – all of the same stock, of course.)
• total buy (The total buy is the sum of the purchases when I bought the shares. If I am selling everything, I want to see the total purchase cost, regardless of trade date.)
  profit (For tax purposes, but also because I also want to see how I did in terms of real, spendable dollars. This column is for the total purchase and sale, not the individual positions as above. Frequently, they are the same.)
• % return (This is the total % gain, regardless of how long I held the shares.)
• splits (Splits happen, and many of the descriptions about simple calculations are because splits, mergers, acquisitions, and other weirdnesses can change the number of shares between purchase and sale.)
• shares held (The list of shares in each position or purchase accounting for splits.)
  sum (The total number of shares held or sold, which is also useful for checking against the brokerage’s portfolio report.)
• portfolio then (Shares can shift between portfolios as brokerages change, or as life events like rollovers or divorces shift shares.)
• portfolio now (Shares can shift between portfolios as brokerages change, or as life events like rollovers or divorces shift shares.)
• purchase price derived (The total of all the purchases divided by the total number of shares held, also known as the overall cost basis.)
• average price (Once upon a time I had an idea for this column. What was it?)

Lessons Learned

• Managing the data is necessary for tax reporting, if it is outside something like an IRA.
• Brokerages are not infallible, and if a position was brought over from another portfolio, they won’t have complete records. As a responsible investor, I find it valuable to keep records that are as complete as is reasonable.
• Current average of the averages of time between purchase and sale is 3.44 years. I’ve said I was LTBH. My current record was a sale after 6,500 days or 17.8 years. Sadly, that was a loss; but it taught me much about the realities of investing. (See the book for details.)
• Despite my current portfolio’s performance (as of January 11, 2015) my overall performance is very encouraging. My strategy of investing in small, disruptive companies is risky enough to record dozens of losses, but the gains are substantially greater. Rationally, I recognize both possibilities: my patience will be rewarded, and past performance is no guarantee of future performance – in either direction.)
• While I usually hold stocks for years, I’ve also sold if a stock moved faster than I thought it should, and I’ve also sold within months if I uncovered reasons to sell.
• Patience is not always rewarded.
• By buying long instead of selling short, I’ve never lost more than 100% and have gained far more than 100%. The record so far is in excess of 2,400%.
• I started small, a few hundred dollars, and learned more from those trades than I did from any book or lecture.
• Investing can be intimidating, but can be approached at any pace; and, given enough time, much can be learned.
• Figuring out profits and losses sounds easy when the exact same number of shares are bought and sold without splits. I frequently buy more than once, and each time can be a different number of shares. I frequently sell more than once, each time involving a different number of shares. Profits and losses, therefore require averaging costs, profits, and hold times. In a portfolio that is nothing but purchases, each row can be a stock. In a portfolio with combined or fractional sales, it becomes necessary to add rows for subtotals and tracking selling parts of purchases. That’s complicated enough to warrant its own post; but, I’ll wait for feedback before devoting time to chronicling those details.
• Past performance is not much solace if current holdings are performing atrociously. And yet . . optimism remains.

I don’t know if anyone else will find this useful. My friend found it so, and found it a bit intimidating because of the nuances induced by the realities of selling. Undoubtedly more sophisticated spreadsheets and analyses are available. I contend, however, that investing should only be a part of life, not an all-consuming devotion. I update my spreadsheet whenever I make a purchase or a sale. That means, on average, about five minutes per transaction. Considering my average hold time of 3.44 years, it means I don’t have to spend much time massaging the rows and columns.

My book is called Dream. Invest. Live. because investing is only a middle step between dreaming and living that dream. This spreadsheet is a stepping stone, something that helps me keep my balance, as I follow that path. I hope it helps you too.