Excel Day Trading Journal: Build a Useful Trade Log

Yes—Excel can track day trades, calculate profit and loss, and help you review whether your strategies and rules are working. Start with one row per completed trade, record consistent trade details, and use formulas for net P&L and risk-based results; treat the workbook as a recordkeeping and analysis tool, not a promise of trading success.

What an Excel day trading journal is for

A day trading journal is a structured log of trades and the decisions around them. Excel is useful because you can tailor the columns to your approach, calculate results consistently, filter records, and summarize performance over time. It can be as simple as a trade list or expanded to include screenshots, market conditions, and notes about your execution.

The journal is most useful when entries are complete and recorded consistently. A list of wins and losses alone does not explain whether a result came from a sound setup, a rule violation, fees, or an unusually large position. A journal cannot establish that a strategy will remain profitable, but it can help you inspect your own records and identify questions worth investigating.

Choose fields that explain each trade

Use one row for each completed position, with a clear header in every column. A practical starting layout is:

Date: the trading date, entered as an Excel date.
Symbol: the stock, ETF, futures contract, or other instrument. Keep contract details clear if a ticker alone is not enough.
Setup: a consistent label for the strategy or pattern you intended to trade.
Side: Long or Short.
Entry, Exit, and Stop: the prices used to assess the position and its planned risk.
Shares or Units: the quantity traded. For contracts or instruments with multipliers, define the quantity convention and account for the multiplier in your calculations.
Fees: total costs assigned to the trade, using one consistent convention.
Gross P&L, Net P&L, Risk per Unit, Planned Risk, and R Multiple: calculated fields described below.
Plan Followed? and Notes: a simple process check and a short explanation of relevant context.

For stocks, a share-based calculation is usually straightforward. Other products may require a contract multiplier, currency conversion, or different fee treatment. Do not apply a stock formula unchanged to a product whose pricing conventions differ. Record whether fees include both entry and exit costs, and use the same treatment for every row.

Build the worksheet and make entries consistent

Put the headers in row 1 and trade records below them. For example, use columns A through N as follows: Date, Symbol, Setup, Side, Entry, Exit, Stop, Shares, Fees, Gross P&L, Net P&L, Risk per Share, Planned Risk, and R Multiple. Keep notes in a separate column if you want written context without changing the calculation layout.

On Excel for Microsoft 365 on Windows, select the header range and choose Home > Format as Table, then confirm that the table has headers. A table makes it easier to filter and sort records, and formulas entered in a calculated column can fill down as the table grows. On Excel for the web, the same table feature is available from the ribbon. On Mac, use Insert > Table. You can also freeze the header row with View > Freeze Panes > Freeze Top Row.

Use Data > Data Validation to make a dropdown for Side with the allowed values Long and Short. Consistent labels matter: formulas and filters may treat Long, long, and L as different entries. Format dates as dates, prices and dollar results as currency, shares as numbers, and R Multiple as a number such as 0.00. Avoid typing currency symbols into numeric cells, since that can turn usable numbers into text.

Calculate gross and net profit or loss

In this example, row 1 contains headers and row 2 is the first trade: D2 is Side, E2 Entry, F2 Exit, H2 Shares, I2 Fees, J2 Gross P&L, and K2 Net P&L. The gross formula below returns 40 for a long trade of 100 shares bought at 25.00 and sold at 25.40. It also handles a short trade by reversing the price difference.

With Side in D2, Entry in E2, Exit in F2, and Shares in H2, put this in J2:

=IF(OR(E2="",F2="",H2=""),"",IF(D2="Long",(F2-E2)*H2,IF(D2="Short",(E2-F2)*H2,"")))

This leaves the result blank when required cells are empty or the Side entry is not recognized. It does not account for slippage, financing, contract multipliers, or other product-specific costs unless you include them separately. Treat it as a basic share-based calculation, not a complete statement of account performance.

With gross P&L in J2 and total trade fees in I2, enter this in K2:

=IF(J2="","",J2-I2)

If the example trade has $2 in total fees, net P&L is $38: $40 gross minus $2. Enter fees as a positive cost. The formula assumes that I2 contains all fees you want deducted, not just a per-side amount that has yet to be totaled. Copy the formulas down or use a table calculated column.

Track planned risk and R multiples carefully

Risk fields can help compare outcomes with the amount planned at entry, but they need a clearly defined stop. For a long position, the planned price risk per share is entry minus stop. For a short position, it is stop minus entry. If the stop is on the wrong side of entry, the formula below leaves the risk blank instead of reporting a misleading positive number.

With Side in D2, Entry in E2, and Stop in G2, put this formula in L2:

=IF(OR(E2="",G2="",D2=""),"",IF(D2="Long",IF(E2>G2,E2-G2,""),IF(D2="Short",IF(G2>E2,G2-E2,""),"")))

With Risk per Share in L2 and Shares in H2, enter this in M2 to calculate planned dollar risk:

=IF(OR(L2="",H2=""),"",L2*H2)

With Net P&L in K2 and Planned Risk in M2, enter this in N2 for the net result divided by planned risk:

=IF(OR(K2="",M2="",M2<=0),"",K2/M2)

For 100 long shares entered at $25.00 with a $24.80 stop, planned risk is $20. If the position exits at $25.40 with $2 in fees, net P&L is $38 and the formula returns 1.9. This is a simplified planned-risk multiple: it uses the stated stop distance and quantity, not necessarily the loss actually experienced. Gaps, slippage, partial fills, and changing stops can make realized losses differ. A blank or inaccurate stop also makes the metric unhelpful.

Review results without hiding important details

Once records are consistent, summaries can help answer specific questions. For example, a win rate counts profitable trades, while average win and average loss describe their size. Neither measure alone says whether a method is profitable after costs. A strategy can win frequently but have occasional losses that outweigh its gains; it can also win less often while its average win is larger than its average loss.

With net results in K2:K501, this formula returns the fraction of nonblank numeric trade results that are positive:

=IFERROR(COUNTIF(K2:K501,">0")/COUNT(K2:K501),0)

Format the result as a percentage. COUNT ignores blank cells, and COUNTIF counts values above zero; if there are no numeric results, IFERROR returns 0. This calculation treats a zero result as neither a win nor a loss. It does not measure expectancy, account growth, or risk-adjusted performance by itself.

With dates in A2:A501, net results in K2:K501, and a real Excel date in P2, this formula totals net results for the date in P2:

=SUMIFS($K$2:$K$501,$A$2:$A$501,P2)

Check that dates in column A do not include time values if you expect an exact-date match. For broader review, filter by symbol, setup, side, or plan-followed status, and compare like with like. A PivotTable can summarize totals by a field such as Setup; use Insert > PivotTable and select the trade-data range or table. Small samples and changing market conditions can make apparent patterns unreliable, so record the number of trades behind a summary and avoid treating a short run as proof.

Make the journal practical to maintain

A journal that is too elaborate can become difficult to keep current. Begin with essential fields and add columns only when they help answer a real review question. Use a short, controlled vocabulary for setups and notes that distinguish observable facts from hindsight. For example, “entered after breakout; stop at prior low” is more useful for later review than “bad trade” alone.

Enter data soon after the trade, while execution details are available from your broker records.
Use the same fee, timestamp, and partial-fill conventions throughout the workbook.
Separate actual fills from planned prices if you want to evaluate execution quality.
Use filters or a PivotTable to inspect a defined period or setup instead of repeatedly editing the raw records.
Keep a dated backup copy and check formulas after adding columns, pasting data, or changing the workbook layout.

Do not assume that a journal automatically imports accurate broker data. If you paste or import a statement, inspect the date, sign, quantity, and fee columns and reconcile totals with the broker’s records. Differences can arise from partial fills, trade corrections, corporate actions, or how the broker reports commissions.

Common spreadsheet mistakes and limitations

One frequent error is mixing long and short trades while using a formula that only calculates long P&L. Another is subtracting fees twice, or not at all. A third is recording the stop as a number without preserving what it represented at the time of the trade. Make the conventions explicit in the headers or a Notes sheet, and test formulas with a known long trade, a known short trade, a losing trade, and blank rows.

Excel can also produce misleading summaries when numbers are stored as text, dates include unexpected times, or a copied formula points to the wrong range. Inspect a few rows manually after pasting data. If the workbook is shared, protect formula cells from accidental edits where appropriate, but remember that worksheet protection is not a substitute for secure storage. Avoid placing account passwords or unnecessary sensitive identifiers in the journal.

Trading involves financial risk, and spreadsheet results do not guarantee future outcomes. A journal is not tax advice or a substitute for official broker statements. Tax rules and reporting requirements vary by jurisdiction and product; consult appropriate records and a qualified tax professional when needed.

Excel compared with dedicated journaling tools

Excel offers flexible columns, familiar formulas, and control over how records are organized. It can suit a trader who wants a manually maintained log or who needs to explore a particular metric. However, spreadsheet users are responsible for maintaining formulas, backups, and data quality. A workbook may not automatically import fills, attach chart images, or calculate product-specific metrics unless those features are built into the workflow.

Dedicated trade-journaling applications may offer broker connections, dashboards, or chart capture, depending on the product and plan. Those features can save manual effort, but availability, pricing, data handling, and supported brokers differ. Compare the actual features and privacy terms rather than assuming an app will interpret every trade correctly. A useful alternative is a broker’s own downloadable transaction history paired with Excel for analysis; verify imported data against the original records.

Frequently Asked Questions

Can I make a day trading journal in Excel?
Yes. Use one row per trade, consistent column headings, and formulas for calculations such as net P&L. Start with a small set of fields and validate results against known trades before relying on summaries.

What should I track in a day trading journal?
At minimum, record date, symbol, setup, side, entry and exit prices, quantity, fees, net result, and notes about whether you followed your plan. Add a stop and planned-risk fields if you want to compare results with initial risk.

How do I calculate day trading profit in Excel?
For a basic stock trade, calculate long gross P&L as (exit price minus entry price) times shares, and short gross P&L as (entry price minus exit price) times shares. Subtract the total costs you intend to include to calculate net P&L.

Is Excel enough for tracking day trades?
It may be enough for a manual journal and basic analysis, but it depends on your needs. Excel does not automatically ensure that imported records are accurate or account for every product’s contract terms, costs, and reporting conventions.

Can Excel calculate a trading win rate?
Yes. Count positive net results and divide by the count of numeric trade results, as in the example above. Decide how to treat breakeven trades and use the same definition whenever you compare periods or setups.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.