Forex Blog

First-hand Forex trading experience and information about foreign exchange market that will be useful to traders

Archives

Forex Trading Journal 3.0 – Multiple Improvements

June 18, 2020 (Last updated on June 26, 2020) by

After using the same Forex trading journal for almost 8 years, I’ve finally decided to create an updated journal template and share it with everyone here.

There are lots of free spreadsheet journals already available online and there are quite a few professional stand-alone or web-based logging solutions. However, I still prefer the simplicity and versatility of an Excel spreadsheet. And I also like it when a journal only calculates and shows the stuff that I need the most.

The resulting trading journal unites my experience on logging trades from three different macro-strategies. It allows easy customization (most non-essential columns can be hidden if not needed) while also providing an in-depth analysis of the trades. The only serious disadvantage such journal has is slow performance because Excel, albeit a great software, does lots of unnecessary calculations each time you change something in the spreadsheet.

Changes

Compared to my Trading Journal 2.0, the new Trading Journal 3.0 features the following changes:

  • Welcome sheet with some basic information (you can safely delete it after reading).
  • Currency Pairs sheet that serves to put in your currency pair settings for quick referencing and for decimal place counts. You can ignore it, but then you won’t be able to benefit from decimal places formatting; also you won’t be able to just enter a small number instead of typing in the entire currency pair name each time.
  • The number of pips for profit and loss is now calculated automatically.
  • Added a Commission column. It can also be used to record rebates if you receive them from your broker.
  • Added a Swap column to record the swaps you pay or get from the broker.
  • Added an Analysis sheet to provide detailed analysis of your trading performance.
  • Scalpers and intraday traders wouldn’t probably need some of the columns (Close date or Swaps for example) but they would maybe want to track the trade length.
  • You can hide Entry, SL, TP, Exit Price fields completely and just enter pip and $ gains/losses manually if they are more relevant to your strategy than exact price levels. This is the case with my news trading strategy, for example.
  • Only first 1,000 rows are filled with formulas. If you hit the limit, just copy and paste formulas from the final row as far down as you need. However, the more filled rows you have, the slower the journal will work. I recommend creating a new trading journal file for each new year (or for each new month if you are scalping).

Explaining the columns

Let’s look at the example journal that I have filled with some random trades.

The first columns include: trade number (clear it when logging non-trading transactions), open date (you can change it to time if it is more relevant to your trading strategy), strategy (the reason for entry), number of the currency pair (you define it via the Currency Pairs spreadsheet), currency pair name (you can enter it manually or have it filled automatically after you fill the currency pair number), trade direction (the choice of Long/Short).

The basic columns of the trading journal

Then follow the price-related parameters of the trade: entry price, initial stop-loss, initial take-profit, IRR (initial risk-to-reward ratio), position size (you can enter it in lots or in units, or you can just hide the column if you don’t want to recored this parameter), exit price, close date.

Price-related columns of the trading journal

After that, you notice the columns that mostly involve the post-trade stuff: profit/loss in pips (calculated automatically based on the difference between the entry and exit price, and the decimal places defined in the Currency Pairs sheet), profit/loss in % (calculated automatically using the currency profit you enter and your previous account balance), profit/loss in USD (you can change the currency name to whatever you use), swap, commission, trade length (it can be either Intraday or the number of days between the entry and the exit).

Profit-related columns of the trading journal

The final columns of the trading journal include: open comment (what you had thought about the trade when you just opened it), close comment (what you think about the trade after closing it), end balance (calculated automatically), chart link (you can insert a link to a local file on your computer, or a URL to some website where you store your trading screenshots, or you can hide this column entirely).

The final columns of the trading journal

Currency Pairs sheet

This is where you define your currency pairs with a quick reference number and a number of decimal places. There are two number fields but the second one is filled automatically when you enter something in the first one. The second column is required for technical reasons:

Analysis

The Analysis sheet becomes the most interesting one after you log some trades. It lets you analyze your performance using a number of metrics and charts.

Metrics

First, come the various basic metrics (totals) and the ratios/indices: net profit (shows your total net profit, which is probably the most important result to look at), trades (for a total number of trades), wins, losses, short trades, long trades, swap (total swap for all trades), commission (total commission paid for all trades), win rate (number wins divided by the total number of trades), ROI (return on investment — how much you would increase/decrease a starting balance in percentage term), expected payoff (how much you can expect to earn/lose from one trade on average), profit factor (how much your winning trades are bigger than your losing trades), standard deviation (the volatility of your returns), Sharpe ratio (how your ROI compares to the volatility of your returns), Ulcer index (how much negative volatility is in your returns).

Analysing the strategy's totals and essential metrics via the trading journal

Then follow the metrics of averages: profit/loss per trade, profitable trade, losing trade, R/R ratio (average risk-to-reward ratio):

Average trade performance in the trading journal

The table ends with the columns that show the metrics of the “maximum” parameters of your strategy: profit, loss, winning streak (longest sequence of uninterrupted profitable trades), losing streak (longest sequence of uninterrupted losing trades), absolute drawdown (how much your balance declined below its starting point), maximum drawdown (largest peak-to-through percentage decline).

Max/min parameters of your trades in the trading journal

Charts

The Analysis sheet also features two charts:

Profit/loss per Trade

Charting profit per trade in the trading journal

Total Accumulated Profit/Loss

Charting total accumulated profit in the trading journal

Downloads

You can download either a pre-filled template journal spreadsheet (you can replace trades with your own or clear unnecessary rows) or an empty spreadsheet (only initial balance transaction is logged). To clear the rows, it is advised to use advanced selection (F5->Special…->Constants). If the journal is empty, an occasional “Excel found a problem with one or more formula references in this worksheet.” error may pop up — you may ignore it; it should disappear once you fill in some trades.

Download Forex Trading Journal 3.0 pre-filled with example trades

Download an empty Forex Trading Journal 3.0

Important note: Enable iterative calculations via File->Options->Formulas. Otherwise, you won’t able to reference currency pairs both by their name and number. You can set the maximum iterations value to 1:

How to enable iterative calculation in Excel for Trading Journal

Update 2020-06-26: Fixed the end balance formula bug in the “empty” journal pointed out by David.

If you have any suggestions or if you find any bugs in this Forex trading journal spreadsheet, please post about them in the form below.

6 Responses to “Forex Trading Journal 3.0 – Multiple Improvements”

  1. David

    Hi,
    This is a wonderful workbook you have put together! In the “empty” Forex workbook, column V seems to be missing the inclusion of the Commissions column (column R). The formula is correct in the “pre-filled with examples” workbook, which includes column R:
    =IF($P5=””,””,$P5+$Q5+ $R5 +LOOKUP(2,1/($V$3:$V4″”),$V$3:$V4))

    Whereas the formula in the empty workbook does not :

    =IF($P5=””,””,$P5+$Q5+LOOKUP(2,1/($V$3:$V4″”),$V$3:$V4))

    Thank you for the awesome material you have put together!

    Warm regards,
    David

    Reply

    Andriy Moraru Reply:

    You are totally right! Thanks for pointing it out – I have now fixed the spreadsheet.

    Reply

  2. Mohammad Reza

    Hello
    Is there anyway to add two part positions to your journal?

    Reply

    Andriy Moraru Reply:

    You could just enter them as two separate positions.

    Reply

  3. romeo

    hi the empty journal says it is a corrupt file and cannot be opened

    Reply

    Andriy Moraru Reply:

    I’ve just downloaded and opened it fine. Which version of Office are you using? It opens in 2016.

    There is also a warning about a problem in the formulas when you open it, but it doesn’t affect anything and will go away once you log two completed trades.

    Reply

Leave a Reply

required
required (will not be published)