Home › Templates › Forex trading journal
Forex Trading Journal Template
A free forex trading journal for Excel and Google Sheets that turns each trade's prices into pips (×100 for JPY pairs, ×10,000 for the others), converts pips to profit or loss in your account currency with your pip value, subtracts swap and commission, and works out the R-multiple. A summary tab groups the results by pair, session, setup, mistake and month, with an equity curve.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no broker connection.
| Closed | Pair | Side | Session | Lots | Entry | Stop | Exit | Pips | Pip value / lot | Swap | Commission | Net P/L | R |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 03 Aug | EUR/USD | Long | London | 1.00 | 1.08500 | 1.08300 | 1.08860 | +36.0 | 10.00 | 0.00 | $7.00 | $353.00 | +1.77R |
| 05 Aug | USD/JPY | Short | Tokyo | 0.80 | 153.400 | 153.800 | 153.020 | +38.0 | 6.50 | −$4.10 | $5.60 | $187.90 | +0.90R |
| 05 Aug | GBP/USD | Long | New York | 1.00 | 1.27200 | 1.26950 | 1.26950 | −25.0 | 10.00 | 0.00 | $7.00 | −$257.00 | −1.03R |
| 07 Aug | EUR/JPY | Long | London | 0.70 | 165.200 | 164.850 | 165.650 | +45.0 | 6.50 | 0.00 | $4.90 | $199.85 | +1.25R |
| 11 Aug | AUD/USD | Short | Sydney | 1.20 | 0.65800 | 0.66000 | 0.65650 | +15.0 | 10.00 | −$1.80 | $8.40 | $169.80 | +0.71R |
| 12 Aug | USD/CAD | Long | New York | 1.00 | 1.37500 | 1.37250 | 1.37250 | −25.0 | 7.30 | 0.00 | $7.00 | −$189.50 | −1.04R |
| 13 Aug | EUR/USD | Short | London | 1.00 | 1.09100 | 1.09300 | 1.08800 | +30.0 | 10.00 | 0.00 | $7.00 | $293.00 | +1.47R |
| 17 Aug | GBP/JPY | Long | Tokyo | 0.50 | 195.500 | 195.200 | 195.000 | −50.0 | 6.50 | 0.00 | $3.50 | −$166.00 | −1.70R |
| 20 Aug | USD/JPY | Long | New York | 1.00 | 154.100 | 153.700 | 154.700 | +60.0 | 6.50 | $3.20 | $7.00 | $386.20 | +1.49R |
| 21 Aug | EUR/GBP | Short | London | 1.00 | 0.85400 | 0.85550 | 0.85550 | −15.0 | 13.00 | 0.00 | $7.00 | −$202.00 | −1.04R |
Not trading forex? For stocks and other markets the general trading journal template fits better, and the day trading journal adds intraday sessions and a daily loss limit.
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, definitions, session note, colour key, limits, disclaimer |
| Journal | Starting balance and account currency once, then one row per closed trade (1,000 rows) | Pip factor, pips, stop in pips, risk, gross P/L, net P/L, R-multiple, trade #, month, balance, drawdown, result |
| Summary | Nothing | 16 totals; tables by pair, session, setup, mistake tag and month (36 months); equity curve and P/L-by-session chart |
| Example Journal / Example Summary | Already filled | 31 fictional trades so you can see every formula working |
| Lists | Optional edits | 12 pairs, sessions, setups and mistake tags for the dropdowns; a session reference table |
The forex fields
| Field | Why it is there |
|---|---|
| Pair | Dropdown with 12 common pairs. You can type another pair (the sheet warns once, then accepts it). The name decides the pip factor. |
| Lots | Size in standard lots: 1.00 = 100,000 units of the base currency, 0.10 = a mini lot, 0.01 = a micro lot. |
| Pip value per 1.00 lot | What one pip on one standard lot is worth in your account currency. You type it, because it depends on the pair and the exchange rate. |
| Swap (+/−) | Overnight financing as shown on your statement: positive if credited, negative if charged. |
| Commission | Round-trip commission as a positive number. Leave blank on spread-only accounts. |
| Session | Sydney, Tokyo, London or New York, picked by you (see the session note below). |
| Setup, mistake tag | Dropdowns, so the Summary can group results. Use "None" for a clean trade. |
How to use it
- On Journal, type your starting balance in C3 and your account currency in C4 (for your own reference; the sheet does not convert currencies).
- When a trade closes, add a row: open and close date, pair, Long or Short, session, setup, lots, entry, stop and exit price.
- Type the pip value per 1.00 lot in your account currency. Copy it from your broker's platform or a pip value calculator at the time of the trade.
- Add swap and commission from your statement, and a mistake tag.
- Leave the grey columns alone. They fill in pips, risk, net P/L, R-multiple and the running balance.
- Once a week or month, read Summary: which pairs, sessions and setups made or lost money, what swap and commission cost, and how the equity curve moved.
Pips, pip value and costs
By market convention a pip is the fourth decimal place for most currency pairs (0.0001) and the second decimal place for pairs quoted in Japanese yen (0.01). The journal turns a price move into pips with one factor:
| Pair type | One pip | Factor | Example from the sample |
|---|---|---|---|
| Pairs without JPY (EUR/USD, GBP/USD, AUD/USD …) | 0.0001 | × 10,000 | EUR/USD long 1.08500 → 1.08860 = +36.0 pips |
| JPY pairs (USD/JPY, EUR/JPY, GBP/JPY …) | 0.01 | × 100 | USD/JPY short 153.400 → 153.020 = +38.0 pips |
Many brokers quote one more digit (a fraction of a pip). The journal keeps one decimal: 12.5 pips is fine.
Pip value. One standard lot is 100,000 units of the base currency, so one pip is worth 100,000 × 0.0001 = 10 units of the quote currency (100,000 × 0.01 = 1,000 yen for JPY pairs). If the quote currency is your account currency, as with EUR/USD in a USD account, the pip value is a fixed 10.00 per lot. If it is not, the value moves with the exchange rate, which is why the journal asks you to type it rather than guess it. The example uses fixed made-up values: 6.50 for the JPY pairs, 7.30 for USD/CAD, 12.50 for USD/CHF and 13.00 for EUR/GBP.
Swap and commission. Net P/L = gross P/L + swap − commission. In the example these costs added up to −$1.90 of swap and $200.90 of commission over 31 trades, so net P/L ($2,224.55) is $202.80 less than gross P/L ($2,427.35).
Forex sessions: a convention, not a timetable
The spot forex market has no single exchange with official opening hours, so "sessions" are named after the main trading centres. The hours below are the rough local business day in each centre. Brokers, data vendors and trading books draw the lines differently, so the template does not tag sessions from a clock; you pick one per trade, using the same rule each time.
| Session tag | Centre | Rough local hours | Daylight saving time |
|---|---|---|---|
| Sydney | Sydney, Australia | about 8:00–17:00 local | Yes, on southern-hemisphere dates |
| Tokyo | Tokyo, Japan | about 9:00–18:00 local | No |
| London | London, UK | about 8:00–17:00 local | Yes (UK dates) |
| New York | New York, US | about 8:00–17:00 local | Yes (US dates, different from the UK) |
Daylight saving caveat. Because the UK, the US and Australia change their clocks on different dates and Japan does not change them at all, each session and each overlap (for example London with New York) moves by an hour against UTC at several points in the year. If you tag by UTC time, check the current offsets first.
The formulas, explained
Exact formulas from the file, row 7 of Journal. Columns: A open date, B close date, C pair, D Long/Short, G lots, H entry, I stop, J exit, K pip value per lot, L swap, M commission. C3 = starting balance.
| Field | Formula |
|---|---|
| Pip factor (P) | =IF(C7="","",IF(ISNUMBER(SEARCH("JPY",C7)),100,10000)) |
| Pips (Q) | =IF(OR(C7="",H7="",J7=""),"",ROUND((J7-H7)*P7*IF(D7="Short",-1,1),1)) |
| Stop in pips (R) | =IF(OR(C7="",H7="",I7=""),"",ROUND(ABS(H7-I7)*P7,1)) |
| Risk (S) | =IF(OR(R7="",G7="",K7=""),"",R7*K7*G7) |
| Gross P/L (T) | =IF(OR(Q7="",G7="",K7=""),"",Q7*K7*G7) |
| Net P/L (U) | =IF(T7="","",T7+N(L7)-N(M7)) |
| R-multiple (V) | =IF(OR(U7="",N(S7)=0),"",U7/S7) |
| Month (X) | =IF(U7="","",DATE(YEAR(IF(B7="",A7,B7)),MONTH(IF(B7="",A7,B7)),1)) |
| Balance after trade (Y) | =IF(U7="","",N($C$3)+SUM($U$7:U7)) |
| Drawdown (Z) | =IF(Y7="","",Y7-MAX(N($C$3),MAX($Y$7:Y7))) |
| Summary: profit factor (B13) | =IF(B6=0,IF(B5>0,"no losses",""),SUMIF(Journal!$U$7:$U$1006,">0")/-SUMIF(Journal!$U$7:$U$1006,"<0")) |
| Summary: trades for one pair (B23) | =COUNTIFS(Journal!$C$7:$C$1006,$A23,Journal!$W$7:$W$1006,">0") |
| Summary: average R for one pair (F23) | =IF(COUNTIFS(Journal!$C$7:$C$1006,$A23,Journal!$S$7:$S$1006,">0",Journal!$W$7:$W$1006,">0")=0,"",SUMIFS(Journal!$V$7:$V$1006,Journal!$C$7:$C$1006,$A23)/COUNTIFS(Journal!$C$7:$C$1006,$A23,Journal!$S$7:$S$1006,">0",Journal!$W$7:$W$1006,">0")) |
| Summary: next month (H24) | =IF(H23="","",IF(EDATE(H23,1)>MAX(Journal!$X$7:$X$1006),"",EDATE(H23,1))) |
| Summary: balance at month end (N23) | =IF(H23="","",N(Journal!$C$3)+SUMIFS(Journal!$U$7:$U$1006,Journal!$X$7:$X$1006,"<="&H23)) |
The pip factor uses SEARCH("JPY", pair), so it works for any pair with JPY on either side. Pips are rounded to 0.1 so that floating-point noise (35.99999…) does not show. The balance uses a range that grows row by row ($U$7:U7, then $U$7:U8 …), so log trades in the order they closed. The month list on Summary starts at the earliest close month and adds one month with EDATE until the last one.
Worked example from the sample data
One EUR/USD trade and one USD/JPY trade
- Trade 1, EUR/USD long, 1.00 lot. Entry 1.08500, stop 1.08300, exit 1.08860. Pips = (1.08860 − 1.08500) × 10,000 = 36.0. Risk = 20 pips × $10.00 × 1.00 = $200.00. Net P/L = 36 × $10.00 × 1.00 − $7.00 commission = $353.00, or +1.77R.
- Trade 2, USD/JPY short, 0.80 lot. Entry 153.400, stop 153.800, exit 153.020. Pips = (153.020 − 153.400) × 100 × −1 = 38.0. Gross = 38 × $6.50 × 0.80 = $197.60. Swap −$4.10 and commission $5.60 bring net P/L to $187.90 (+0.90R against $208.00 risk).
- Trade 8, GBP/JPY, mistake "Moved stop". The planned stop was 30 pips away, but the trade closed 50 pips down, so the loss was −$166.00, or −1.70R. Typing the planned stop, not the moved one, is what makes this visible.
The 31 trades together
Pips alone can mislead: the same pip count is worth different amounts on different pairs and lot sizes. That is why the Summary shows pips and money side by side.
| Pair | Trades | Win rate | Pips | Net P/L | Avg R |
|---|---|---|---|---|---|
| EUR/USD | 6 | 67% | +106.0 | $936.90 | +0.85R |
| GBP/USD | 5 | 20% | −60.0 | −$583.60 | −0.51R |
| USD/JPY | 4 | 75% | +123.0 | $658.90 | +0.68R |
| USD/CHF | 2 | 50% | +15.0 | $173.50 | +0.35R |
| AUD/USD | 3 | 67% | +7.0 | $75.80 | +0.08R |
| USD/CAD | 2 | 50% | −5.0 | −$50.50 | −0.14R |
| EUR/GBP | 2 | 50% | +3.0 | $25.00 | +0.06R |
| EUR/JPY | 3 | 67% | +75.0 | $325.45 | +0.68R |
| GBP/JPY | 3 | 67% | +110.0 | $344.00 | +0.48R |
| AUD/JPY | 1 | 100% | +50.0 | $319.10 | +1.40R |
| Session | Trades | Win rate | Pips | Net P/L | Avg R |
|---|---|---|---|---|---|
| Sydney | 4 | 75% | +57.0 | $394.90 | +0.41R |
| Tokyo | 5 | 60% | +43.0 | $236.00 | +0.07R |
| London | 13 | 77% | +399.0 | $2,728.45 | +1.05R |
| New York | 9 | 22% | −75.0 | −$1,134.80 | −0.52R |
| Month (close date) | Trades | Win rate | Pips | Swap + commission | Net P/L | Balance at month end |
|---|---|---|---|---|---|---|
| Aug 2026 | 13 | 54% | +119.0 | −$89.10 | $653.25 | $10,653.25 |
| Sep 2026 | 11 | 64% | +178.0 | −$68.90 | $898.60 | $11,551.85 |
| Oct 2026 | 7 | 57% | +127.0 | −$44.80 | $672.70 | $12,224.55 |
In this fictional sample, New York trades lost money while London trades made the most, and the trades tagged with a mistake lost $1,522.60 in total. 31 trades are far too few to rely on. Read tables like these as questions to check over months of your own data, not as findings.
Common mistakes
- Using the wrong pip size for JPY pairs. A 0.50 move on USD/JPY is 50 pips, not 5,000. The template handles this from the pair name, so spell the pair with JPY in it.
- Treating pip value as fixed for every pair. It is a fixed 10.00 per lot only when the quote currency is your account currency. For other pairs, type the value at the time of the trade.
- Leaving out swap and commission. On a short trade they look small. Over many trades they can turn a break-even record into a loss, as trade 29 in the example shows.
- Typing commission as a negative number. Enter 7.00, not −7.00. Swap keeps its sign; commission does not.
- Typing the moved stop instead of the planned one. R-multiple measures the result against the risk you planned to take.
- Judging a pair or session after a few trades. Wait for a meaningful sample. See why win rate alone doesn't matter.
Before you trade forex
The US Commodity Futures Trading Commission's fraud advisory on foreign currency trading says: "The forex market is volatile and carries substantial risks." It also warns: "Margin trading can make you responsible for losses that greatly exceed the dollar amount you deposited." A journal does not change those risks. It helps you see what your trades actually cost and returned.
FAQ
What should a forex trading journal include?
For each trade: open and close date, pair, long or short, session, setup, lot size, entry, stop and exit price, pip value, swap, commission and a mistake tag. From those a journal can work out pips, risk, gross and net P/L in your account currency and the R-multiple. Then group the results by pair, session, setup and month.
How do you calculate pips on JPY pairs?
For pairs quoted in Japanese yen, such as USD/JPY, a pip is 0.01, so multiply the price change by 100. For most other pairs a pip is 0.0001, so multiply by 10,000. In the example, USD/JPY sold at 153.400 and bought back at 153.020 is 38.0 pips. The template picks the factor itself: 100 if the pair name contains JPY, otherwise 10,000.
How do you calculate profit in forex from pips?
Multiply the pips by the pip value per lot and by the number of lots, then add swap and subtract commission. For EUR/USD in a USD account, one pip on 1.00 standard lot (100,000 units) is 100,000 × 0.0001 = $10.00. The first example trade made 36 pips on 1.00 lot: 36 × $10.00 = $360.00, minus $7.00 commission = $353.00.
What are the main forex trading sessions?
Traders usually split the 24-hour week into Sydney, Tokyo, London and New York sessions, named after the main trading centres. These are conventions, not official hours, and sources draw the lines differently. The UK, US and Australia change clocks on different dates and Japan does not use daylight saving time, so the sessions shift in UTC during the year.
Should swap and commission be in a forex trading journal?
Yes, if your broker charges them. Swap (overnight financing) can be a credit or a charge, and commission is a cost on every trade. In the example, trade 29 closed at 0.0 pips but still lost $3.70 after a $1.20 swap credit and a $4.90 commission.
Can I use this forex journal in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Drive and open it with Google Sheets. The formulas use functions that exist in Excel 2016+, Google Sheets and LibreOffice (SUMIFS, COUNTIFS, AVERAGEIF, EDATE, SEARCH).
Related tools and templates
- Position size calculatorSize for a set risk and stop
- Risk reward calculatorTarget vs stop before you enter
- Risk of ruin calculatorOdds of a deep drawdown
- Trading journal templateFor stocks and other markets
- Day trading journalIntraday sessions and daily loss limit
- Trading plan templateWrite the rules you journal against
- Understanding expectancyWin rate and payoff together
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. Leveraged forex trading carries substantial risk, and on margin losses can exceed the amount deposited. Example data is fictional: all prices, pip values, swaps, commissions and results are made up. Session hours are a market convention, not official hours. CFTC material was retrieved on 29 October 2026.