The BEST Stock Portfolio Tracker Spreadsheet Ever! – How I Keep Track of My Dividend Growth Portfolio Using Google Sheets (Part 2 – Trade Log)
For part 2 of this series, I will show you how to create a trade log for all your transactions.
The trade log functions as the database for your tracker because this is where the spreadsheet will get most of its data to populate your other sheets.
I lay out my trade log like so.
- Date
- Action (Buy/ Sell)
- Ticker
- Account that purchase the stock
- Shares bought
- Price of shares
- Commission/ Adjustment
- Total Price
- Note
No need to go fancy because this is where you will manually input your transactions. In fact, it should be as simple as possible so that you can manually input each transaction as efficient as possible.
Action
For “Action”, I include the options Buy or Sell because later on I will use a nested argument in my “Stock Portfolio Overview” sheet (Don’t worry about it for now)
Commission/ Adjustment
I have this column because my broker used to charge me per transaction and I would like to include my transaction fee in my cost basis.
Now that most brokers offer free transaction fee, I leave this column in for adjustment. Sometimes when you enroll in DRIP (Dividend Reinvestment Program), the brokers will use 4 decimal points in their price for the shares reinvested but round up the total price to 2 decimal points which create a discrepancy in the cost basis between your sheet and their record. For example:
If I don’t include the $0.01 in the first row for SCHD, my purchase price for that transaction will be $8.96, however in my TD Ameritrade account, it shows my purchase price for the transaction to be $8.97, and so I have to add the penny.
You don’t have to do this, but I am a little OCD about having as close cost basis as possible to the data in my brokerage. If you don’t have this column, your cost basis will probably only differs around a few pennies with your broker’s.
Total Price
For the total price column just use the formula
=IF(B2=”Buy”,(E2*F2)+G2,(E2*F2)-G2)
B = Action Column
E = # of Shares Column
F = Price of shares column
G = Commission/ Adjustment Column
By inputting this formula, you are telling the cell
if B2 shows “Buy”, then (# of Shares) x (Price of Shares) + (Adjustment).
if B2 shows anything other than “Buy”, then (# of Shares) x (Price of Shares) – (Adjustment).
You don’t have to use the IF formula, but it comes in handy if you have a broker that charges you different rate whether you are buying or selling shares.
In the next post, I will show you how to create a Stock Portfolio Overview sheet that pulls data from the Trade Log that we have created.
As usual, leave a comment or hit me up if you have an suggestions on how you think I can do better or to show how you create your Trade Log.
If you can’t wait and you want to get your hands on the final product as soon as possible, this template is available to purchase from Gumroad.
Also, don’t forget, when you use my referral links, you will be able to claim your free stocks from Robinhood and WeBull.
Update #1: Continue to Part 3 (“Creating a Stock Portfolio Overview”) HERE!
