Option trade log spreadsheet

Options.

Each Options Trading Journal has (8) modifiable Performance-tracking categories. Uniquely designed layout, yet simple to use, with a wealth of knowledge at your finger tips. Options ‘multiplier’ can be easily modified for different contract sizes (1, 100, 1,000, etc.) Tons of great features, functionality and analysis built in to each product version. “At-a-Glance” view of all performance statistics. View the Options Trading Log ◊ View the Options Analysis sheet Options Trade Input methods For more detailed product information, please visit the TJS Gallery and Info page.

Invest in your Options trading business.

Start analyzing your trades today!

Complete package, One time fee, One Low Price.

Free unlimited support!

Let's start Analyzing your trades, shall we?

Options Trading Journal Spreadsheet.

Trading Journal Spreadsheet, Corp.

Las Vegas, Nevada.

Trade Tracking and Analysis software, for all:

Stocks, Options, Futures, Forex, (UK) Spread Betting, and CFD traders.

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.

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.

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.

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.

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")

Note: The cell L73 would hold my total successful trades.

The cell L74 holds my total failed trades.

Win Ratio = Total Successful Trades/All trades (Sum of successful + unsuccessful trades)

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.

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:

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.

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.

Options Trading.

Related Topics.

Keeping in Touch.

Newsletter Subscription.

Video Store.

Check out these full length videos that contain lots of specific information about trading spread strategies at an excellent value.

Free video tutorials.

I set up and discuss the trades and then follow them up with periodic reviews until they close. For more detail go to the Options Trading Videos page.

Vertical spread tutorial.

Calendar spread tutorial.

Iron Condor tutorial.

Diagonal Spread Tutorial.

Other videos.

Copyright 2008-2014 success-with-options.

All Rights Reserved.

Information on this site is for educational purposes only.

FREE spreadsheet for trading log!

FREE spreadsheet for trading log!

This is a discussion on FREE spreadsheet for trading log! within the Trading Software forums, part of the Commercial category; OK, I finally completed my spreadsheet and I took the time to verify the calculations by hand so I know .

row 1: Equities Paper Trading.

row 2: Ticker symbol.

row 2: Long or short.

row 2: Entry price.

row 2: Exit price.

row 2: N of shares.

row 2: Profit or loss.

(this is a $25 round-trip commission, you can double-click to change the amount)

row 2: Total cost.

row 2: % win or loss.

row 2: Total profit or loss.

(in reality, the calculations for % of gain on winners etc is all done with the results excluding the cost of commissions)

Stops are 4 buses.

i like to split my position into 3 portions, and take profit at 25, 75 pips and then trail the final lot with 75 pips trailing. can you design a spreadsheet for me?

Free Excel trading log template.

Free Excel trading log template.

This is a discussion on Free Excel trading log template within the Trading Journals forums, part of the Reception category; I've put this trading log together in excel - thought some of you might find it useful. Take a look .

At the request of Rogh, the first version of the template referred to above has been removed - as there is a more up to date version - please scroll down!

• The initial entries are fictitious – just used for testing (It will be better to just overwrite them rather than deleting them and risk losing the formulas).

• The yellow columns are the only ones that require data input.

• Cell error messages (e. g. #div/0) relate to empty or ‘0’ content cells and correct themselves on corresponding data entry.

A ratio used by many investors to compare the expected returns of an investment to the amount of risk undertaken to capture these returns. This ratio is calculated mathematically by dividing the amount of profit the trader expects to have made when the position is closed (i. e. the reward) by the amount he or she stands to lose if price moves in the unexpected direction (i. e. the risk). investopedia/terms/r/riskrewardratio. asp.

Commission fees plus stamp duty. (Commission fixed at 2xЈ8. Stamp duty 0.5 of purchase cost)

Profit or loss including commission and fees.

R Multiple: P&L divided by the Initial Risk.

“You want your losses to be 1R or less. That means if you say you’ll get out of a stock when it drops $50 to $40, then you actually GET OUT when it drops to $40. If you get out when it drops to $30, then your loss is much bigger than 1R.

It’s twice what you were planning to lose or a 2R loss. And you want to avoid that possibility at all costs.

You want your profits to ideally be much bigger than 1R. For example, you buy a stock at $8 and plan to get out if it drops to $6, so that your initial 1R loss is $2 per share. You now make a profit of $20 per share. Since this is 10 times what you were planning to risk we call it a 10R profit.”

Average (mean) of the R-multiple.

1. Trader Mike. (Trading journal. xls)

2. Clambill. (Trading Log. xls)

proposes the inclusion of the probability of the trades success into the risk/reward equation - which will make it more effective.

But it's not a log file so looking forward to seeing your log file !