By Sierra Spreadsheets are a lifesaver, and I’m a strong advocate for using them for everything. I religiously use a spreadsheet to track my net worth and expenses, as well as for managing credit cards to maximize their lucrative rewards. I’m an avid collector of credit cards and points, as detailed in my comprehensive guide on travel hacks and credit cards, which explains everything you need to know about using credit cards to maximize travel.
I’ve always traded stocks and invested my money long-term as part of my FIRE (Financial Independence, Retire Early) lifestyle. Finding good spreadsheet options to track my growing list of stock positions has been a challenge.
I achieved financial independence in 2020, which was a significant milestone for me. Last year, I traveled the world for a year, and my net worth increased. Selling options has helped me manage my expenses and income during early retirement.
Options trading is part of my wealth-building strategy. I use this spreadsheet to track my net worth and expenses.
If you’re more advanced and want to trade options, I’ve also created this spreadsheet for tracking options trading.
Check out my ultimate spreadsheet for tracking all your credit cards, including sign-up bonuses and annual fees.
Additionally, see my travel itinerary planning spreadsheet, which is perfect for organizing and planning your trips.
Don’t forget to check out my Restaurant List Tracker spreadsheet for keeping track of all the restaurants you’ve visited, whether at home or abroad.
For a list of all my posts related to credit cards and travel hacks, click here.
Why Use a Spreadsheet for Your Stock Portfolio?
While your brokerage in 2021 and beyond will do a good job of tracking your investments, I believe a well-designed spreadsheet offers unparalleled benefits. Chase and Robinhood are my primary brokerages, and they display my positions in a digestible format.
However, I firmly believe that no substitute matches a good spreadsheet that consolidates all the information I need into one accessible and analyzable window.
Feel free to tweak or add any specific details to suit your needs!
The Ultimate Spreadsheet for Tracking Stock Portfolios and Investments
This post isn’t about how to trade stocks, which stocks to buy, or how to trade them. Instead, it’s about creating a comprehensive spreadsheet to track your investments in one place. While some might find using a spreadsheet excessive when their brokerage already tracks the same information, others may appreciate having a concise overview of their investments on a single page.
I love using Google Sheets because I can access it from anywhere. It provides a snapshot of all my stocks, including essential financial data such as market capitalization, EPS, P/E ratio, earnings dates, and more, all in one place. Often, I find myself checking my brokerage account and opening separate tabs for stock information. This spreadsheet helps streamline that process and saves a few clicks.
What Information to Track for Stocks
Stocks are straightforward investments. For most investors, it’s simply about buying and holding. Some might trade in and out of stocks within a few days, while others trade large quantities daily. This spreadsheet works for any of these types of investors. I like to keep track of the following information:
• Entry Date: Track the day you entered a trade.
• Stock Ticker: The symbol for the stock.
• 90-Day Trend: Google Sheets has a nice mini-chart feature that allows me to view a quick trend of the stock over the past 90 days.
• Current Price: The current market price of the stock.
• % Change in Price: The percentage change in the stock price.
• Purchase Price: The price at which you bought the stock.
• Cost Basis: How much you paid for the position (purchase price * number of shares).
• Current MTM (Mark-to-Market): The current value of your position (current price * number of shares).
• Unrealized P&L: The amount of money you’ve gained or lost on the position. This isn’t realized until you sell the stock.
• Status: The current status of the stock position.
• Closing Price: Once you close a position (sell it), you’ll have a realized gain or loss. I include additional rows to track this.
I also like to keep basic financial information on stocks that I find useful for aggregated analysis:
• Market Capitalization: Gives an idea of the company’s size.
• EPS (Earnings Per Share): A measure of a company’s profitability.
• P/E Ratio (Price-to-Earnings): Indicates how much investors are willing to pay per dollar of earnings.
• 52-Week High/Low: The highest and lowest prices the stock has reached over the past year.
• Dividend Yield: The percentage of dividend payout.
• Earnings Date: Dates when earnings reports are expected, which can affect stock volatility.
• Ex-Dividend Date: The next expected dividend payment date if the stock pays dividends.
Stock Trading and Investment Spreadsheet
Many old-school traders keep detailed trading journals. While I wouldn’t call myself old-school, I use strategies for consistent income both during my working years and after early retirement.
For this spreadsheet, I primarily use Google Finance functions to fill in data about stocks, prices, and finances. You can also use the Google API to customize and add your own fields as desired. I use the importXML command to gather dividend and earnings data from Yahoo Finance. Google Finance is an incredibly powerful tool, and I explained this function in detail in my Google Finance spreadsheet post.
Download the Spreadsheet
The spreadsheet is simple yet effective. Make sure to update it regularly. I recommend spending 5 to 10 minutes updating the spreadsheet once a month with your figures. While I don’t always get the exact amounts for various expenses because it’s time-consuming, that’s up to you. You can use this spreadsheet whether you’re single or a couple (just sum everything up!).
Please note that this spreadsheet also tracks my personal expenses. I will make live updates and add features over time, so be sure to check back for updates!
Download Stock Trading Spreadsheet
The spreadsheet is in Google Sheets so you can easily access it from anywhere with an internet connection. I prefer this over Microsoft Excel, but it’s also easy to download and use with Excel if needed.
How to Download and Use the Stock Portfolio Spreadsheet
To download for offline use, click the red button above, then go to File > Download as > Microsoft Excel.
To use it with your own Google profile, simply go to File > Make a Copy.
Using the Stock Portfolio Spreadsheet
As someone who regularly works with spreadsheets, I’ve included features like conditional formatting and formulas that a beginner in Excel might not understand. I’ll explain everything you need to make the most out of the stock portfolio tracking spreadsheet.
For the most part, anyone with Excel experience should be able to follow along and complete the spreadsheet accordingly. Avoid altering the cells with formulas as they are interlinked. All cells you need to update are color-coded in gray.
Shaded Cells
All shaded cells in this spreadsheet contain formulas. They are not mandatory, but I find them useful for my own tracking. Feel free to modify or add your own formulas as needed.
• % of Total Portfolio: This shows the percentage that your stock position represents of the total portfolio. It should always sum up to 100% in the bottom row.
• % Discount from 52-Week High: Tracks how close your stock is to its 52-week high price.
• Days Held: Indicates how long your position has been on the books. This is more useful for options trading, but for simple stocks, it’s good for tracking purposes, especially for tax reasons if you decide to sell.
Adding New Trades
Unfortunately, the spreadsheet isn’t highly sophisticated. When adding a new trade, simply copy and paste the previous row and update the ticker and option information accordingly.
Allow the formulas to complete automatically, and it should update as needed.
Closing Trades and Tracking Gains and Losses
When selling stocks, you’ll want to record the closing date and the closing price. This allows you to track the gains or losses you’ve realized.
This will generate tax events, so be aware that it will be taxed as a long-term capital gain if held for more than one year. Otherwise, it will be taxed as regular income if held for less than a year.
Options Trading Tracking
For tracking options trading, you can use the spreadsheet I created specifically for this purpose. It includes functions and formulas tailored to options trading.
Selling Covered Call Options and Cash-Secured Put Options (or the Options Wheel) is an excellent way to generate “relatively” secure income during retirement. The options wheel is one of the methods I use to achieve consistent and relatively stable income.
It is one of the main methods I use to generate a small extra income during retirement. Although it’s not necessary for me, as the Trinity Study already indicates that I have enough portfolio to live off the interest, I am open to taking a bit more risk and find that selling puts is quite interesting.
Be sure to also check out my post on the options spreadsheet to better understand these strategies!