Track Stock Trades with Google Sheets (and this FREE Template!):
Tracking your trades is a KEY part of long-term stock market success… And… while diligent record keeping isn’t the most exciting part of trading stocks, it’s an important step you don’t want to ignore.
So in this article…
I’m going to show you exactly how you can Track Stock Trades with Google Sheets! And the great thing is, not only are these templates are available free on mobile, desktop and tablet, they also update your stock prices in real time with data from Google Finance!
And don’t worry. It’s actually VERY easy and I’ll show you exactly how to do it. Now by the way…
“If you’re ALSO looking for which stocks to trade: Click here to sign up FREE for my weekly trade ideas!“
Another benefit of tracking your stock trades Google sheets, is they’re stored in the Google Drive cloud. So unlike Excel files they’re much more portable and you don’t have to worry about losing them if your hard drive crashes!
This means you can very easily track your trades in only a few minutes per day… without having to update price data… from anywhere in the world!
It’s a fast, easy and efficient way to stay on top of your trading activity.
I’ll even show you the EXACT template I use to track my trading positions. And of course you’re welcome to make a copy for yourself and use it to track your own trades.
Then if you combine that with a stock screening strategy to find breakout trade ideas, you should be well on your way to success!
I want to talk a little bit more about the benefits of accurately tracking your stock trades…
Why Tracking Your Trading is a Key to Stock Market Success:
Carefully tracking your stock trades allows you to stay on top of all your positions, helps reduce trading mistakes and provides data you can use to improve your trading results.
So what’s not to love about that?
If you’re still not convinced though…
Here are some of the main benefits to tracking your trades:
- Staying organized to reduce errors: trading stocks can be a fast moving endeavour, especially when you have multiple positions on the go. If you aren’t careful, you can lose track of your entries, stop-losses and exits. And before you know it, you end up making expensive trading errors that undermine your long-term performance. By staying organized and on top of your portfolio you can improve your profits.
- Tracking trades forces you to be deliberate: Another implication of fast-moving markets is that it can be easy to trade impulsively. But these emotional urges to immediately act on an interesting news headline or hot momentum stock can also undermine your trading results. Instead, by deliberately tracking each trade in a spreadsheet, you’re forced to slow down. You’re forced to evaluate your risk and consciously decide what you want to do.
- Get an easy way to review your results: One of the biggest benefits of tracking your trades in a Google Spreadsheet is that you have a detailed record of your decisions, which you can review and learn from. It’s a great way to get started journalling your trades, which can help you improve over time. Besides, if you’re tracking your entry and exit prices, it’s easy to add some notes explaining why you made your trade. Then, over time you can review your results to identify and eliminate common mistakes (whether it’s position sizing, exiting too early or something entirely different.
Of course there are more benefits to tracking your trades properly. But these are the ones that have made the biggest impact for me.
I know what you might be thinking…
“Can’t I just track my trades in my brokerage account?!”
Well… sure… you can.
And your brokerage records are always a good back-up to have. But in my experience there are a few big reasons that the trade results and reporting provided by your brokerage are not enough.
Let me explain.
Three Big Problems with Brokerage Position Tracking:
These days, most online brokers provide pretty robust trading records. They’ll tell you what stocks you bought, how many shares, as well as your entry and exit price. They’ll even tell you the execution and settlement dates.
But… if you ask me… that’s not enough!
In my experience, there are three big problems that come with brokerage position tracking. In particular:
(1) You can’t see legs of the trade. Maybe your broker is different, but for me I can only see the total position size for any individual stock ticker. So if I buy 100 shares of AAPL at $120, then another 50 shares at $125, I only see a blended total. Instead though, I prefer to see the P&L on the individual orders. This way I can get a more granular feel for the profitability of my trading system. I’m also able to learn
(2) You can’t see open risk. Since I always have a plan to sell my stocks, with a hard stop-loss and proper position size, I like to know my open risk. That means, I want to be able to see how much money I could potentially lose if my positions go against me and I get stopped out. Unfortunately, my broker doesn’t provide this critical view so I choose to track my trades manually. This way I can easily see both my open profits and my percent of capital at risk. Frankly, I don’t know how I’d be able to trade without having this information at a glance.
(3) Automatic brokerage tracking is NOT deliberate. One of the biggest benefits of tracking your trades is that you must deliberately review all your trades. You have to slow down and think about what you’re doing, as you type in the details of your trade to a spreadsheet. Now I know this sounds simple. But for me personally, it’s made a huge difference. It’s just too easy to hit the buy button in the heat of the moment. On the other hand, when you methodically document everything you’re doing you’re less likely to make an impulsive mistake.
For me, these factors are just too big to overlook. So that’s why, many years ago, I started tracking my trades. At first…
I used to manually update all my trade data in Excel. Then I linked Excel to Yahoo Finance to speed things up. But today, I’ve evolved to use the free Google Sheets and free Google Finance data.
As I mentioned…
Not only is this approach very easy to use, it’s also available from anywhere (even on your smart phone!) Plus, when you combine it with proper position sizing and plan for when to sell, you’re well on your way to having a proper approach to risk management.
Now let me show you exactly how I track my trades. I’ll also share the template I use so that you can also get started tracking your trades, all by the time you’re done reading this article!
How to Track Your Trades Using a Google Spreadsheet:
Getting started tracking your stock trades with a Google Spreadsheet is quite easy. First though, you need to set up a Google Account. I imagine most of you already have one, but if not, it only takes a few seconds to setup.
Once you’ve got your trusty Google account, you can make yourself a copy of my template below.
But first, let me show you what it looks like.
My Google Trade Tracking Spreadsheet, Revealed:
As promised, below is a screenshot of my Google Spreadsheet that I use to track stock trades. Notice that if a ticker is on there twice, it represents two separate orders (see BCR in the example below).
So as you can see, it’s really quite simple. I’m primarily keeping track of entry prices, stop losses and open profits.
But just to be clear, here are the main columns of my open-position tracking sheet:
- Ticker: of course you need to know what stock position you have trading!
- Entry price: what price did your buy order get filled at?
- Market price: what’s the current price of the stock? (Using the formula in the sheet, this will update automatically)
- Lot size: how many shares did you buy?
- Stop loss: what’s your worst-case exit price?
- Position value: Populates automatically, by multiplying lot size by entry price.
- Market Value: Populates automatically by multiplying lot size by market value.
- Profit/Loss Amount: Populates automatically by subtracting the market value from the position value.
- P&L/Share: Populates automatically by subtracting the market price from the entry price.
- Daily Change %: Populates automatically using data from Google Finance.
- Max Loss: Populates automatically by subtracting (lot size x entry price) – (lot size x stop loss).
And by the way…
If you’re at all confused by any of the columns above I encourage you to download the spreadsheet below and give it a try for yourself.
Once you start playing around with it, it al becomes pretty intuitive. So let me show you how to get your copy…
Google Spreadsheet Template for Tracking Position Sizing:
Easy! Once you’re there, just navigate to the “File” menu, and then click “Make a Copy” to save a copy of this to your Google account.
Here’s a view of what that looks like:
And by the way, I strongly encourage you to customize this template to work for you even better. For example, you may decide to add other columns, such as:
- Trading system/strategy: if you have different trading systems that you’re using you can add a column to indicate this. Then when you’re reviewing your trades you can sort them by trading system to see the results for different strategies.
- Account: I like to manage multiple accounts from this spreadsheet. So if that’s the case for you, just add a column to help you specify which account the trade belongs to.
- Entry/Exit date: When I close my trades, I move them to another sheet and indicate the entry and exit date (as provided by my broker). This can be helpful for seeing when money is tied up for long periods of time, which can help you adjust your trading system to be more efficient.
- R multiple: If you are using a concept like R-Multiples to track your risk, you can add a column equal to your risk (R), divided by your open profit/loss. This will help you see the R-multiple of the current trade.
Of course, this is just a starting point. There are all kinds of other criteria you could add to your trade tracking spreadsheet.
My only word of warning would be that you don’t want the spreadsheet to become too laborious. The reason I like these Google spreadsheets in the first place is because they’re easy to use and only take a couple minutes to update.
The other thing I should mention is that when I close a trade, I move it to another worksheet in the same file. This way, I always have a record of my closed trades. At this point, I also add some more information about each trade, including the dates of entry and exit as well as a note about how the trade went.
This ease of use isn’t trivial, because it helps me stick to the system over time. Make sense?
Now, with this in mind you should have everything you need to track your trades using Google Spreadsheets! Don’t forget to get your own copy. And feel free to send me an email if you ever have any questions about this!
Still want more?
Here are some great related resources on tracking your stock trades and staying on top of your investment portfolio…
More Resources for Tracking Your Stock Trades:
- The Best Free Stock Portfolio Tracking Spreadsheet from Old School Value.
- What are the Easiest Ways to Track my Investments from Joshua Kennon, via The Balance
- Using Excel to Track Your Stock Portfolio from How The Market Works
Still not enough? Sign up with the form below to get a detailed breakdown of how I use technical and fundamental analysis to pick stocks!