22 November, 2022 seen 43,399
There is a super simple and easy way to track your stock portfolio with real-time price, cost basis, market value,…
I've been using Google Spreadsheets literally for everything that can be counted or measured (dividend income, stock trades, body weight and so on).
And so did when I decided to build an option trading journal I built it using Google Spreadsheets.
Everything good here, except I decided to count how many trades there have been, how many are open, how many are closed, how many are winning, how many are losing and based on that make a simple calculation to figure out what is my winning rate
Here is the final result:
Win ratio calculation using Google Spreadsheets
COUNT DUPLICATES WITH COUNTIF
COUNTIF is a relatively basic Google Sheets function that counts cells that include numbers or text based on a specified condition. The syntax is simple; you only need to provide a cell range and criterion for which cells to count. You can enter the COUNTIF function in the fx bar with the syntax: `=COUNTIF(range, criterion)`. Source: How To Count Duplicates in Google Sheets
Spreadsheet setup
- Column L - Trade status (open/closed)
- Column M - Winner / Loser
Now to make count for open trade status:
=COUNTIF(L:L, "open")
closed:
=COUNTIF(L:L, "closed")
winning trades:
=COUNTIF(M:M, "winner")
losing trades:
=COUNTIF(M:M, "loser")
Works like a charm.