FOREX JOURNAL WORKFLOW
How to Build a Forex Trading Journal in Excel
Record pairs, lot sizes, pips, spread, commission and swap in one clean table, convert results to your account currency, and review performance by pair and session.
START WITH THE INSTRUMENT
Why a forex journal needs its own fields
A stock journal can record a ticker, a share count and two prices. A currency trade adds a second currency, a contract size that hides behind the word βlotβ, a price unit called a pip, and costs that arrive in three different forms. If those details are not stored as separate columns, later analysis by pair or session becomes guesswork.
Two currencies, one result
Profit is earned in the quote currency (USD for EUR/USD, JPY for USD/JPY). Store base, quote and the account currency so conversion is explicit.
Lots hide the real size
A standard lot is commonly 100,000 units of the base currency; mini and micro lots are 10,000 and 1,000. Record both lots and units.
Pip size changes by pair
Most pairs quote a pip at 0.0001; JPY pairs use 0.01. A journal that assumes one pip size will misreport every yen trade.
Three cost types
Spread is paid inside the fill price, commission is charged per lot, and swap or rollover is added or deducted for positions held overnight.
If you need the universal foundation first, start with our step-by-step Excel trading journal guide. This article adds the currency-specific layer on top of that structure.
KEEP RAW DATA AUDITABLE
Use four focused workbook tabs
Trade Log
One row per closed position. When a trade is scaled out in stages, use one row per exit or record the volume-weighted average exit price with the total closed lots.
Lists & Rules
Dropdown values for account, pair, session, setup, direction, market condition and rule adherence. Also keep a small pair table with pip size and contract size for lookups.
Pair & Session Report
PivotTables or SUMIFS formulas that summarize net result, pips, win rate and average R by pair, session and setup without touching the source rows.
Weekly Review
A dated log of reconciliation issues, recurring execution mistakes and one clearly defined question to test in the next sample of trades.
THE FOREX TRADE LOG
Essential columns for a forex trading journal
Format the log as an Excel table so new rows inherit formulas and validation. Use dropdowns for repeated labels; βEURUSDβ, βEUR/USDβ and βeurusdβ become three different pairs in a report.
| Group | Suggested columns | Review question |
|---|---|---|
| Identity | Trade ID, account, broker, account currency | Which account and currency does this result belong to? |
| Instrument | Pair, base currency, quote currency, pip size, contract size | Are pip and lot conventions correct for this pair? |
| Timing | Entry time, exit time, holding time, weekday, session | When did the setup occur, in one consistent timezone? |
| Plan | Setup, direction, planned entry, stop (price and pips), target, planned risk | What decision existed before the outcome was known? |
| Execution | Actual entry, actual exit, lots, units, partial exits, slippage in pips | Did the fill differ from the plan? |
| Costs | Spread at entry (pips), commission, swap (signed), conversion rate | How much of the gross move was consumed by costs? |
| Outcome | Pips, gross P&L (quote), net P&L (account), R-multiple, MFE, MAE | What happened, in units that can be compared? |
| Process | Rule followed, market condition, news event flag, screenshot link, notes | Was the process repeatable regardless of the result? |
GET THE UNITS RIGHT FIRST
Pips, lots and pip value
Three relationships drive every forex calculation. Keep pip size and contract size in the pair lookup table so the trade log can pull them with XLOOKUP instead of relying on memory.
Pips = (Exit β Entry) Γ· Pip size Γ DirectionDirection is +1 for a long and β1 for a short. A 0.0060 move on EUR/USD is 60 pips; the same 0.60 move on USD/JPY is also 60 pips because the pip size is 0.01.
Units = Lots Γ Contract size0.5 lot of a pair with a 100,000 contract size is 50,000 units of the base currency. Confirm the contract size for each product with your broker; CFDs and micro accounts can differ.
Pip value (quote) = Pip size Γ UnitsFor 50,000 units of EUR/USD, one pip is worth 0.0001 Γ 50,000 = 5 USD. For 100,000 units of USD/JPY, one pip is 0.01 Γ 100,000 = 1,000 JPY before conversion.
No conversion needed
A USD account trading EUR/USD, GBP/USD or AUD/USD earns pips directly in USD. Gross P&L in the quote currency is already the account result before commission and swap.
Store the conversion rate
A USD account trading EUR/GBP earns pips in GBP; a USD/JPY trade earns yen. Record the quote-to-account rate your broker applied at close and multiply, so the journal matches the statement instead of an estimate.
For pair-by-pair detail, see our guides to point value for forex majors and minors and JPY pairs, plus why a journal can show strange numbers on yen trades.
CALCULATE, THEN VERIFY
Excel formulas for the trade log
The formulas below use structured references inside an Excel table named Trades. Column names are examples; match them to your own headers. They assume a simple spot or CFD position closed in one exit.
| Column | Formula | Notes |
|---|---|---|
| Pip size | =XLOOKUP([@Pair],Pairs[Pair],Pairs[PipSize]) | Lookup from the pair table instead of typing 0.0001 or 0.01 by hand. |
| Direction | =IF([@Side]="Long",1,IF([@Side]="Short",-1,"")) | Blank for any other value so a typo does not silently become a long. |
| Pips | =IFERROR(([@Exit]-[@Entry])/[@PipSize]*[@Direction],"") | Negative pips mean a loss. Round only for display. |
| Units | =[@Lots]*[@ContractSize] | Contract size also comes from the pair table. |
| Gross P&L (quote) | =[@Pips]*[@PipSize]*[@Units] | Result in the quote currency before any cost. |
| Net P&L (account) | =[@[Gross Quote]]*[@ConvRate]-[@Commission]+[@Swap] | ConvRate is 1 when quote and account currency match. Swap is signed. |
| R-multiple | =IFERROR([@[Net PL]]/[@InitialRisk],"") | Initial risk is the planned loss in account currency, not the stop in pips. |
| Holding time | =IF(AND([@ExitTime]>0,[@EntryTime]>0),[@ExitTime]-[@EntryTime],"") | Format as [h]:mm so multi-day swings display correctly. |
WORKED EXAMPLE
One EUR/USD long in a USD account
A trader buys 0.5 lot (50,000 units) at 1.0850 and closes at 1.0910. The broker charges 3.50 USD commission for the round trip and applies a swap of β1.20 USD for one night. The planned stop was 25 pips, so the initial risk was 125 USD.
Illustrative data- Pips
- +60 (1.0910 β 1.0850) Γ· 0.0001
- Pip value
- 5.00 USD 0.0001 Γ 50,000
- Gross P&L
- 300.00 USD 60 Γ 5.00 (quote = USD, rate 1)
- Net P&L
- 295.30 USD 300 β 3.50 + (β1.20)
- R-multiple
- 2.36R 295.30 Γ· 125
- Cost share
- 1.6% 4.70 Γ· 300
Before copying formulas down, compare several rows against the broker's closed-positions statement. Check whether the statement's profit column already includes commission or swap; if it does, the workbook must not subtract them a second time.
SIZE FROM RISK, NOT FROM MARGIN
Calculate lot size from risk per trade
The most useful planning column in a forex journal is the lot size that keeps the planned loss inside a written risk limit. The relationship is simple once pip value is known:
Lots = (Account equity Γ Risk %) Γ· (Stop in pips Γ Pip value per 1.0 lot)
Example: 10,000 USD Γ 1% = 100 USD risk budget. With a 25-pip stop and 10 USD per pip per standard lot, 100 Γ· (25 Γ 10) = 0.40 lot. Round down to the broker's lot step so the actual risk never exceeds the budget.
TRY THE WORKFLOW
Forex lot-size calculator
Enter hypothetical values. Results update in your browser and are not stored or sent anywhere.
Example only. Verify pip value, contract size and minimum lot with your broker.
MEASURE DECISIONS, NOT JUST PAIRS
Review by pair, session and setup
Currency pairs behave differently at different hours, and the same setup can produce a very different result in the London open than in a quiet Asian session. A forex journal earns its keep when the dashboard can slice the same trades three ways.
Net result by pair
Compare pairs after commission and swap. Use pips and R-multiples alongside currency so a large lot size does not disguise a weak setup.
Result by session
Group by London, New York, Asian and overlap labels that come from one timezone rule, then look for sessions where rule adherence drops.
Cost share of gross
Divide commission plus swap by gross profit before costs. Define what happens when gross is zero or negative so the ratio does not mislead.
Rule-adherence rate
Percentage of trades that followed every written entry, sizing and exit rule. A profitable rule break is still a rule break.
- Reconcile with the statementMatch lots, prices, commission and swap for every closed position. Flag partial exits that were merged incorrectly.
- Check pip size and conversionConfirm JPY pairs use 0.01 and cross pairs carry the correct quote-to-account rate.
- Separate news tradesFilter trades opened around scheduled releases and review their slippage and spread separately.
- Compare planned and actual riskWhere actual risk exceeded the budget, note whether the cause was lot rounding, slippage or a moved stop.
- Segment comparable tradesFilter by pair, session, setup and direction before drawing a conclusion. A handful of trades is not a sample.
- Write one testable questionDefine one change, the trades it applies to and the metric you will watch, without editing historical rows.
READY-MADE WORKBOOK
Prefer a forex journal that is already built?
The DIY structure above keeps every assumption visible. If you would rather start from a finished workbook, review the current product page for included files, features and software requirements.
Forex Trading Journal Template for Google Sheets & Excel
The current listing describes a trade log with automatic P/L, R-multiple and holding-time statistics, a pair report that also covers gold pairs, partial exits in up to three stages, a calendar view and a positional lot-size calculator. Confirm the latest details on the product page.
- Works for forex, indices and metals
- Strategy and pair performance reports
- Editable spreadsheet format
Product requirements, included files and compatibility are listed on the product page and may change. Desktop or laptop use is recommended for spreadsheet editing.
Compare all Trading Journal templatesFAQ
Forex trading journal questions
What should a forex trading journal include?
At minimum: account and account currency, pair, direction, entry and exit time, entry and exit price, lots and units, pip size, planned stop and risk, commission, swap, conversion rate, pips, net P&L, R-multiple, setup, session and whether the written rules were followed.
How do I calculate pips in Excel?
Divide the price difference by the pair's pip size and multiply by +1 for a long or β1 for a short: =(ExitβEntry)/PipSizeΓDirection. Keep pip size in a lookup table because JPY pairs use 0.01 while most other pairs use 0.0001.
How is pip value calculated?
Pip value in the quote currency equals pip size multiplied by the number of units. For 50,000 units of EUR/USD that is 0.0001 Γ 50,000 = 5 USD per pip. If the quote currency differs from the account currency, convert at the broker's applied rate.
Should spread be subtracted from profit?
Usually not as a separate line. On most retail platforms the spread is already inside the fill prices, so subtracting it again double-counts the cost. Record it in pips for analysis and deduct only commission and swap that appear on the statement.
How do I handle swap or rollover in the journal?
Store the exact swap amount from the statement as a signed valueβpositive when credited, negative when chargedβand let the net P&L formula add it. Do not estimate it from a published rate.
Can the journal show which pair will be profitable next month?
No. It summarizes trades that already happened and helps you form questions to test. Past results by pair or session do not guarantee future performance.
Sources and further reading
- Investor.gov: Foreign currency exchange (forex)
- National Futures Association: Investor resources
- Microsoft Support: Create and format an Excel table
- Microsoft Support: Apply data validation to cells
- Microsoft Support: Create a PivotTable
Sources and product details checked September 2026. Lot sizes, pip values and fees vary by broker and product. This article provides general educational information and is not investment, tax or legal advice.
