The Ultimate Stock And Investments Portfolio Tracking Spreadsheet

Spreadsheets are life and I am a fan of using them for everything. I religiously use a spreadsheet to track my net worth and expenses as well as when I collect credit cards for their lucrative rewards.  I am an avid credit card and points collector as you can read from my comprehensive guide to travel hacking and credit cards which explains everything you ever needed to know about how to use credit cards to maximize traveling.

I’ve always traded stocks and invested my money over the long term as a way to pursue the FIRE (FInancial Independence Retire Early) lifestyle. I couldn’t find many good spreadsheet options to track the list of all my stock positions which turned out to be ever increasing.

I reached Financial Independence in 2020 which was quite the milestone for me. In the last year, I’ve traveled the world for a year and my net worth actually increased. Option selling has helped me manage my expenses and income during early retirement.

• Trading options is all a part of my net worth building regimen. I use this spreadsheet to track net worth and expenses.
• If you’re more advanced and want to trade options, I’ve also created this spreadsheet to track options trading
• The ultimate spreadsheet to track all your credit cards, sign on bonuses, and annual fees.
• Also, check out my travel itinerary planning spreadsheet which is perfect for organizing and planning your trip!
• As well as my Restaurant List Tracker spreadsheet for keeping track of all the restaurants you have been to in your hometown or abroad.
• For a list of all my credit card and travel hack related posts, click here.

Why use a spreadsheet to your stock portfolio?

---

Your brokerage in 2021 and beyond will do more than a sufficient job of tracking your investments. I use Chase and Robinhood as my main brokerages and I know they display my positions in an easily digestible format.

However, I firmly believe you just can’t substitute for a nice spreadsheet which puts all the information that I need in one window that I can easily access and analyze.

This post is not about how to trade stocks and which stocks you should buy or how you should trade them. This is simply a spreadsheet to track your investments in one sheet. Some people may find it excessive and unnecessary to use a spreadsheet when your brokerage is already tracking the same thing. However, others might like having a more one-page concise view of their investments.

I like keeping a Google Sheets that I can access from everywhere that will have information on all my stocks, as well as basic stock financials like Market Cap, EPS, P/E, Earnings Date etc. in one concise look. Often times, I find myself looking through my brokerage and having to open a separate tab to get the information about the stocks. This just saves me a few clicks.

What information to keep track of for stocks?

Stocks are simple investments. It’s just simply buy and hold for most investors. Wallstreetbets type of folks will trade in and out of stocks within a few days. Others will day trade large quantities of stocks. This spreadsheet will work for any of the above categories of people. I like to keep the following information on my spreadsheet.

• Entry date: Without a doubt, you’ll want to keep track of the day you entered a trade
• Stock ticker: Self explanatory
• Trend (90d): Google Sheets has a very nice sparkline feature so I can see a quick trend of the stock over the past 90 days
• Current Price: Self explanatory
• % Change in Price: Self Explanatory
• Purchase Price: Self Explanatory
• Cost Basis: How much you paid for the position (Purchase Price * # of shares)
• Current MTM: The current value of your position (Current Price * # of shares)
• Unrealized P&L: This is simply how much money you have made or lost on the position. This is unrealized because until you hit the sell button, it is not a real loss and technically have not lost money yet.
• Status: Self Explanatory
• Closing Price: Once you close a position (sell it), then you will realize a loss or gain. I have extra rows to track this.

I also like to keep some basic financial information for the stocks which I find useful to look at on an aggregate level

• Market Cap: Not super useful but I like to just see how big companies are
• EPS: Earnings Per Share
• P/E: Price Earnings Ratio
• 52w high/Low: Self explanatory
• Divi Yield: The % dividend payment
• Earnings Date: You can always expect increased volatility during earnings release so this is something to watch out for
• Ex-Dividend Date: If the stock pays a dividend, this is the next expected payment date

Stock and Investment Trading Spreadsheet

---

I think a lot of old school traders keep a detailed journal for trading. I wouldn’t say I am old school because I’m not that experienced of a trader at all. However, I have been using a few strategies to generate consistent income while I was working and after I retired early.

For the purpose of this spreadsheet, I used mostly almost entirely Google Finance functions to populate stock information, pricing, financials etc. You can also use the Google API to customize and add fields of your own to your liking. I also use an importXML command to obtain information from Yahoo Finance on dividends and earnings related data. Google Finance is an incredibly powerful function and I’ve explained in detail about this function in my Google Finance spreadsheet post.

Download spreadsheet

The spreadsheet is simple, yet effective. Just make sure you update it on a regular basis. I would recommend spending 5-10 minutes updating the spreadsheet once a month with your figures. I don’t always do the exact amount for different expenses because it takes a lot of time but that is also up to you. You can use this spreadsheet if you’re a single person as well as if you’re a couple (just add everything up!).

Keep in mind that this spreadsheet is also what I use to track my own expenses. I will be making updates live and will add additional features to this spreadsheet as time goes on so make sure to check in for updates!

Download Stock Trading Spreadsheet

The spreadsheet is in Google Sheets because if I update it in the future, it will be easy to access from anywhere with an internet connection. I prefer this to Microsoft Excel. It’s easy to download it and use with Microsoft Excel as well.

To download offline, click the red button above, then click file > download as > Microsoft Excel

To use it with your own Google profile, just click file > make a copy

[vc_row][vc_column][mvc_infobox info_style=”mega_info_box_2″ info_opt=”show_icon” icon_size=”20″ icon_radius=”5px” title_color=”#000000″ font_icon=”fas fa-exclamation” icon_color=”#0a0a0a” hoverclr=”#0a0a0a” icon_bg=”#e5e5e5″ hoverbg=”#c9c9c9″ caption_url=”” info_title=”Constantly Changing” title_size=”25″]Note that this spreadsheet is constantly changing. This spreadsheet may look different depending on when you’re reaching this post![/mvc_infobox][/vc_column][/vc_row]

Using the Stock Portfolio Spreadsheet

---

As someone that works with spreadsheets regularly, I’ve included some functionality like conditional formatting and formulas that an Excel novice might not understand. I will explain everything necessary for those wanting to get the full use out of the stock portfolio tracker spreadsheet.

For the most part, anyone with any excel experience should just follow my spreadsheet and populate accordingly. Do not touch cells with formulas because everything is intricately linked. All cells that you should update have been color coded with gray cells.

The shaded cells

All shaded cells on this spreadsheet are formulas. They are not mandatory but I find them helpful for my own trading purposes. You may or may not depending on your style.

Of course, you can modify them or add your own formulas to your use.

• % of Total Portfolio: Simply the percentage your one stock position makes up of the entire portfolio. It should always sum up to 100% at the bottom row.
• % off 52 week high: This tracks how close your stock is to its 52 week high price.
• Days Held: How long your position has been on the books. This is more useful for trading options but I find it for simple stocks, it is good to keep track for tax purposes if you decide to sell.

 

Adding new trades

The spreadsheet is not super fancy unfortunately. If you are adding a new trade, simply copy and paste the previous row and update the ticker and option information accordingly.

Let the formulas auto-populate accordingly and it should look find otherwise.

Close the trades and keep track of your realized P&L

When selling stocks, you’ll want to record your closing date and your closing price. This way you can track the profit or loss you’ve realized.

This will generate tax events so keep in mind it will be long term gains tax rate if the position was held for over 1 year. Otherwise, it will be taxed as regular income if it was held under a year.

Trading Options next

---

Selling covered calls and cash secured puts (or the options wheel) is a great way to generate “relatively” safe income during retirement. The option wheel is something I use to generate consistent and relatively stable income.

It is one of the primary methods I use to generate a little extra income during retirement. While not necessary for me since the Trinity principle already states I have enough of a portfolio to live off the interest, I am okay with a little more risk and find selling options to be quite interesting.

Make sure to also read my options spreadsheet post to better understand these strategies!

Continue Reading:

• The Ultimate Google Finance Spreadsheet
• The Ultimate Spreadsheet For Options Trading And Tracking
• The Ultimate Monthly Expenses And Budgeting Spreadsheet
• The Ultimate Personal Finance And Budgeting Spreadsheet
• The Perfect Mortgage Spreadsheet With Amortization Schedule and Profit Calculator
• The Ultimate Expense Splitting Spreadsheet
• Ultimate Call Spread And Put Spread Option Strategy Guide
• Ultimate Covered Call Strategy Guide: Passive Income Generator
• Ultimate Guide To Selling Puts (Cash Secured Puts)
• The Ultimate Net Worth, Budgeting, And FIRE Spreadsheet
• The Ultimate Travel Expenses And Budgeting Spreadsheet
• Generating Retirement Income From Selling Covered Calls