Trade Log

The trade log allows me to track the overall success of my system by measuring key numbers like total gain/loss, number of successful vs unsuccessful trades. To manage any successful trading system, it is important to continuously monitor success rate, win/loss amounts, and another statistic called expectancy.

Trade Log example

The above example is an early version of a trade log that I set up in Exel. It tracks the essential items I mentioned. Notice one of the columns is a gain/loss per contract. The one thing that any analysis tool like this needs is a point of reference. Ideally I am risking the same amount (by percentage) each time. I could measure a gain/loss per contract or I could measure a %gain/%loss amount. For my purposes, I chose to standardize around gain/loss per contract.

Now, consider the following calculations related to the above entries.

Trade Log expectancy
Using some fairly basic formulas, I can measure total successful trades vs total failed trades. I can measure average gain (per contract) vs average loss (per contract). From these numbers, I can then calculate win loss ratio, reward/risk ratio and expectancy.

Let's break these down further...

Win/Loss Ratio

Win/Loss is should actually be called the win rate based on the calculation I use. The win ratio is calculated by taking the total number of successful trades and dividing by the total. In the above case, the win rate is 75%. By inference then the loss rate is 25%. What that tells me is that for the trades I measured in my trade log, 75% of them are successful. This number could also be considered the probability of having a winning trade.

In my initial trade log, I chose to use a success/fail indicator to allow me to flag a trade as successful or failed independent of whether it made or lost money. My initial thought was that I could have a successful trade that lost money if I felt I successfully followed my trading rules. I've since eliminated this column and have gone with a simple measure of success. My trade is successful when it makes money and a failed trade when it loses money.

Reward/Risk Ratio

For reward/risk ratio, this is actually more of a ratio expressed as a percentage. It is simply the average $ gain amount divided by the average $ loss amount. What this tells me is for each trade, what my gain amount is as a percentage of the total risk. This total risk amount should be roughly equivalent to the percentage amount I was willing to risk (i.e. 1-2% of my portfolio) for each trade. So, another way to think about this number is my return on risk as a percentage.

A lower number doesn't necessarily have to be a bad thing. This ratio doesn't factor in the win/loss rate. So, even if I have a lower reward/risk ratio, if my win rate is fairly high, I may still have a viable options trading system system.


Expectancy is an indication of how much (on average) can be expected to be made for every $1 risked. This calculation factors in both the return on risk (reward/risk) and the win rate (probability of a winning trade). Ideally the expectancy will be positive for my option trading system. A casino will typically have a negative expectancy. If my expectancy is negative or very low over a reasonably large number of trades, it's time to come up with another system.

Expectancy can be calculated using a somewhat complex formula I'll share in a moment. The nice thing about using a spreadsheet is that once the formula has been set up, I rarely have to re-visit it.

Expectancy = (% winning trades x win amount %) - (% losing trades x loss amount %)

One final point... expectancy becomes more valid with a larger number of trades. I wouldn't consider expectancy calculated over 5-10 trades to be indicative of the success or failure of a trading system. However, expectancy calculated over 100 trades will be more meaningful. That's why my trade log includes a way to track my trades over a longer period of time.

Setting up a trade log

A trade log is simply a place to record individual trades and measure overall success. You could do this on a piece of paper, in a ledger or other manual process. However, spreadsheets are very well suited for this sort of thing.

What to capture

There are a lot of interesting things that could be captured with regard to a trade. For this trade log, I prefer to track only information that is useful in giving me a longer term perspective and that can support the various calculations I outlined above.

I will capture some information that doesn't directly support the calculations like the date entered & closed as well as a description of the trade. I also capture a short comment where I can note anything special about the trade.

Some of the required information I capture includes the number of contracts traded, the debit/credit on entry as well as the debit/credit on exit. I also capture the entry & exit commission since this should figure into my overall profit or loss.

From the numbers captured, I can calculate values like net credit/debit and total gain/loss.

How to do formulas

For each trade, I can calculate the net debit/credit of the trade entry as (# contracts x 100 x credit/debit per contract) - commission for entry

For the overall gain/loss, I calculate this as the net debit/credit on entry - (# contracts x 100 x credit/debit on exit) - commission for exit.

Because I use an Excel spreadsheet, all this can be done very simply with a set of formulas.

Calculating the overall statistics like win ratio, reward/risk ratio an expectancy are slightly more complicated. For me, it was easier to break the calculations down.

First, I calculate total successful trades and total failed trades. To do this, I use a simple Excel formula that essentially counts the number of rows that have a positive value.

Total Successful Trades = COUNTIF(L2:L68,">0")

Total Failed Trades = COUNTIF(L2:L68,"<0")

The range I'm specifying above should specify all the rows where I've entered trades. I don't count the first row because it is just a header.

Now I can calculate the average successful gain and average failed loss.

Avg Successful Gain = (SUMIF(L2:L68,">0)/L73)
Note: The cell L73 would hold my total successful trades.

Avg Failed Loss = (SUMIF(L2:L68,"<0)/L74)
The cell L74 holds my total failed trades.

From here, I can now do all my other calculations.
Win Ratio = Total Successful Trades/All trades (Sum of successful + unsuccessful trades)

Reward/Risk ratio = Avg successful gain/Avg Failed Loss
Note: This presumes that I typically take the max loss when I have a losing trade. Alternatively, I can create a column for each trade that represents the total risk in the trade.

Expectancy = (Win ratio * Reward Risk ratio) - (Loss ratio)
Notice this differs somewhat from the formula I listed above for expectancy. I've made several assumptions based on the way my risk is calculated. As a rule, I expect my average failed amount to remain fairly constant and should be roughly equal to my risk in each trade I take. I mentioned for reward/risk ratio that I could also easily use another column to capture max risk in the trade and then I could calculate % loss amount relative to this value. Instead, what I assume is that my average failed loss amount always represents 100% of my risk. So the formula may look like this:

Expectancy = (win ratio * reward/risk ratio) - (Loss ratio * 100%)

For my Excel calculation this simplifies to ((L77*L78-(1-L77) where:
L77 = my win ratio (or win %)
L78 = my reward/risk ratio
Since the total of my win % + loss % has to equal 100%, I can calculate my loss % as 1 - win %.

The cell locations may be different for your spreadsheet based on the number of rows & columns and where you chose to do your calculations for your trade log.

Using multiple worksheets in Excel

While it is possible to enter all trades on one page of a spreadsheet, it starts becoming impractical when the number trades tracked gets too large. When I first set up my trade log, I tracked my trades by quarter. I did this because I typically only made about 50 trades per quarter, which is fairly manageable on a spreadsheet. As my trading volume increases, I moved to tracking trades per month.

Excel supports the ability to have multiple worksheets accessed by tabs at the bottom of the spreadsheet. What I did then was create a worksheet for each month. I labeled each worksheet by the name of the month it tracked. In order to keep a running total of all my important statistics, I now have a separate worksheet that collects that information. My formulas get slightly more complicated because not only do I need to specify the cell but also the worksheet. So, for example, here is how I do some of my yearly calculations.

Total successful trades = SUM(January!L80,February!L68,March!L73, etc)

Notice that to reference the cell of a worksheet, the format is

worksheet label!cell

Once I have calculated my yearly totals for total successful trades, total failed trades, average successful gain and average failed loss, I can directly calculate my ratios and expectancy from these numbers without having to reference all the worksheets of the spreadsheet.

Maximizing the benefit of the trade log

To be useful, I've found it is important to be diligent to capture my trades as I enter and exit them. I need to make sure all my trades are captured, including my failures. I've been tempted in the past to not enter trades I regret making... or trades that I wish I'd have exited differently.

This trade log must be an accurate reflection of my trading against my trading plan. What can it tell me?

  • It can help me determine the effectiveness of my option trading system
  • It can help me determine how consistently I'm following my trading plan
  • It can help me review my trading rules for entry & exit
  • Longer term, it can help me determine how consistent my gains and losses are
To be effective, I've found I need to review the trade log periodically. I've made this part of my monthly routine. At the end of the options cycle for a given month, I make sure I have all my trades entered in the trade log for a the options month just expired. I also make sure I have all my trade journals together for each trade I entered.

I like to sit in a coffee shop on the Saturday following expiration and review my trades collectively by reviewing my trade log for the month and zeroing in on any trades that stand out. From this process, I've been able to identify areas where I'm not consistently following my rules or where my rules need to be made more concrete.

[?] Subscribe To This Site

follow us in feedly
Add to My Yahoo!

Newsletter Subscription



I keep this private

Subscribe now to receive your free promotional package.