Elite Trading Journal Development
Budget: £250 – £750 GBP
1) Goal
Build a professional spread-betting journal that:
Ingests new CMC CSVs quickly.
Auto-populates a normalized Fact_Trades (no hand typing).
Handles partial closes / scaling.
Uses dropdown tagging from dimension tables.
Is Power BI–ready (clean star schema).
2) Sheets & Tables
Create sheets as Excel Tables / Sheets Named Ranges (same names in both apps):
Import_Raw → tblImportRaw (read-only after load)
Partial_Closes → tblPartialCloses (one row per execution/leg)
Fact_Trades → tblFactTrades (one row per completed parent trade)
DimDate → tblDimDate (DateKey, Year, Month, DayOfWeek, etc.)
DimAsset → tblDimAsset (AssetKey, Asset, Asset Class, Quote CCY, PointSize/PipSize, PointValue)
DimSetup → tblDimSetup
DimSession → tblDimSession (Session, TimeBucket, StartTime, EndTime; UK time)
DimDirection → tblDimDirection (“Long”, “Short”)
DimOutcome → tblDimOutcome (Win/Loss/BE, Grade, Emotion…)
Lists_Backend (UNIQUE/SORT ranges for Data Validation)
README (how to import CSV + refresh)
3) Data Ingestion
Excel: Power Query → import single file or folder of CSVs; enforce types; do not aggregate; load to tblImportRaw. “Refresh All” updates downstream.
Google Sheets: Either
File ▸ Import CSV into Import_Raw (overwrite only that range), or
Apps Script to watch a Drive folder and paste into Import_Raw.
All downstream sheets update by formulas only.
4) Keys & IDs
BetID (from CMC) is the parent grouping key whenever present.
TradeID:
If BetID exists, set TradeID = BetID.
Else generate yyyymmdd-Asset-### (per first open timestamp for that asset/day). A stable GUID helper in Import_Raw is also acceptable.
5) Partial Closes (execution-level)
tblPartialCloses fields (min):
BetID, OrderID, Parent TradeID, Asset, Direction, Stake ( £/pt ), Price, Timestamp, Action (“Open”/“Close”), Fees/Commission, Leg PnL.
Populate from tblImportRaw (one row per execution). Use helper flags IsOpen, IsClose.
Leg PnL (generic)
=IF(Direction="Long",(Exit-Entry)*Stake, (Entry-Exit)*Stake)
(If close legs only have close price, compute against average entry; see #6.)
6) Aggregation to Fact_Trades (one row per parent trade)
In tblFactTrades (all formula-driven):
Entry Date/Time = earliest “Open” for TradeID
Excel/Sheets:
=INDEX(SORT(FILTER(tblPartialCloses[Timestamp],(tblPartialCloses[TradeID]=[@TradeID])*(tblPartialCloses[Action]="Open")),1,TRUE),1)
Exit Date/Time = latest “Close” for TradeID
=INDEX(SORT(FILTER(tblPartialCloses[Timestamp],(tblPartialCloses[TradeID]=[@TradeID])*(tblPartialCloses[Action]="Close")),1,TRUE),COUNTA(FILTER(...)))
Size-Weighted Avg Entry Price (all Open legs)
=SUMPRODUCT(
FILTER(tblPartialCloses[Price],condOpen),
FILTER(tblPartialCloses[Stake],condOpen)
) / SUM(FILTER(tblPartialCloses[Stake],condOpen))
where condOpen = (tblPartialCloses[TradeID]=[@TradeID])*(tblPartialCloses[Action]="Open")
Size-Weighted Avg Exit Price (all Close legs; full close) — same pattern with Action="Close".
Executions = COUNT of legs for TradeID.
Gross P&L = SUM of Leg PnL where Action="Close".
Commissions = SUM of fees for TradeID.
Net P&L = Gross – Commissions.
Holding Time (mins) = (Exit−Entry)2460.
Stake / Size = choose Max Net Exposure (preferred) or Sum of opens.
If XLOOKUP isn’t available, use INDEX/MATCH. All above functions (FILTER, SORT, UNIQUE, SUMPRODUCT) work in Excel 365 and Google Sheets.
7) Spread-Bet Maths (points/pips)
Maintain per-asset PointSize/PipSize and PointValue in tblDimAsset.
Point/Pip Gain
=IF([@Direction]="Long",
([@[Exit Price]]-[@[Entry Price]])/XLOOKUP([@Asset],tblDimAsset[Asset],tblDimAsset[PointSize]),
([@[Entry Price]]-[@[Exit Price]])/XLOOKUP([@Asset],tblDimAsset[Asset],tblDimAsset[PointSize]))
(INDEX/MATCH alt: replace XLOOKUP with INDEX(tblDimAsset[PointSize],MATCH([@Asset],tblDimAsset[Asset],0)))
Gross P&L (if Stake is £/pt):
= ([@[Exit Price]]-[@[Entry Price]]) * IF([@Direction]="Long",1,-1) * [@Stake]
Or via point value:
= [@[Point/Pip Gain]] * [@Stake] * XLOOKUP([@Asset],tblDimAsset[Asset],tblDimAsset[PointValue])
8) Time Buckets & Sessions (auto)
tblDimSession defines rows like:
TimeBucket: Open, Mid-Day, Close
StartTime, EndTime (as time values)
Session: London, New York, Crossover
TimeBucket (by Exit time):
=INDEX(
FILTER(tblDimSession[TimeBucket],
(MOD([@[Exit Date]],1)>=tblDimSession[StartTime])*
(MOD([@[Exit Date]],1)< tblDimSession[EndTime])),
1)
Session:
=INDEX(
FILTER(tblDimSession[Session],
(MOD([@[Exit Date]],1)>=tblDimSession[StartTime])*
(MOD([@[Exit Date]],1)< tblDimSession[EndTime])),
1)
DayOfWeek: =TEXT([@[Exit Date]],"ddd")
Executions/hr: =[@Executions] / MAX(([@[Exit Date]]-[@[Entry Date]])*24, 1/60)
9) Fact_Trades Columns (minimum)
TradeID, BetID, Entry Date, Exit Date, Asset, Asset Class, Direction, Stake, Entry Price, Exit Price, Executions, Gross P&L, Commissions, Net P&L, Point/Pip Gain, RRR (planned), RRR Realized, R-Multiple, Account Size (optional), TP/SL/Original TP Hit (Y/N), Highest/Lowest Price (for MFE/MAE), TimeBucket, Session, DayOfWeek, Preparation/Trend/Entry/Patterns/Fib/Env/Strategy/Emotion/Grade/PCP (dropdowns), Comments.
Populate text/categorical fields via dropdowns; numeric fields via formulas.
10) Dropdowns (Data Validation)
Source lists from Dim tables or Lists_Backend:
=SORT(UNIQUE(tblDimAsset[Asset])), etc.
No hard-typed categories in Fact_Trades.
11) Power BI Readiness
DateKey (INT): =VALUE(TEXT([@[Exit Date]],"yyyymmdd")) in Fact + matching column in DimDate.
Use consistent keys: AssetKey, SetupKey, SessionKey, DirectionKey, OutcomeKey.
No merged cells; column names stable; one row per fact.
Relationships: Dim → Fact (one-to-many). Measures computed in PBI.
12) Summary (Open / Mid-Day / Close)
Provide a “Summary” sheet (Pivot or QUERY) by Date and TimeBucket:
Gross & Net P&L, Executions, Positive/Negative Trades, Win Rate, Avg Win, Avg Loss, Executions/hr.
Include slicers/filters: Date, Asset, Session, Setup.
13) Helper Columns (why)
In Import_Raw: IsOpen, IsClose, ExecIndex (per BetID sequence), CleanAsset.
In Partial_Closes: Leg PnL per close leg, Fees normalized.
These stabilize lookups and aggregation.
14) Protection & UX
Lock formula cells in Partial_Closes and Fact_Trades; leave dropdown cells unlocked.
Freeze headers; consistent table styles.
Excel: optional “Refresh All” button. Sheets: simple macro/menu for “Import CSV”.
15) Acceptance Tests
Drop a new CMC CSV → Import_Raw updates → Partial_Closes & Fact_Trades change automatically (no manual edits).
Partial close example (3 opens, 2 closes) shows correct SWA Entry/Exit, Executions=5, Gross/Net P&L.
Exit times map correctly to Open/Mid-Day/Close and London/NY/Crossover via tblDimSession.
Adding an Asset to DimAsset makes it immediately available in dropdowns.
Power BI connects with clean one-to-many relationships and no ambiguity.
16) Deliverables
One workbook (Excel or Google Sheets) named Elite_Trading_Journal_v2 with all sheets/tables above.
Documented import method (PQ query or Sheets import/script).
10–20 seed trades covering single-shot and multi-leg cases.
Notes on Compatibility
Prefer FILTER, SORT, UNIQUE, SUMPRODUCT, TEXT, INDEX/MATCH; use XLOOKUP where available (supply INDEX/MATCH fallback).
Avoid LET/LAMBDA for cross-compat unless requested.
Time math uses serial times (MOD(DateTime,1)), valid in both apps.
Build a professional spread-betting journal that:
Ingests new CMC CSVs quickly.
Auto-populates a normalized Fact_Trades (no hand typing).
Handles partial closes / scaling.
Uses dropdown tagging from dimension tables.
Is Power BI–ready (clean star schema).
2) Sheets & Tables
Create sheets as Excel Tables / Sheets Named Ranges (same names in both apps):
Import_Raw → tblImportRaw (read-only after load)
Partial_Closes → tblPartialCloses (one row per execution/leg)
Fact_Trades → tblFactTrades (one row per completed parent trade)
DimDate → tblDimDate (DateKey, Year, Month, DayOfWeek, etc.)
DimAsset → tblDimAsset (AssetKey, Asset, Asset Class, Quote CCY, PointSize/PipSize, PointValue)
DimSetup → tblDimSetup
DimSession → tblDimSession (Session, TimeBucket, StartTime, EndTime; UK time)
DimDirection → tblDimDirection (“Long”, “Short”)
DimOutcome → tblDimOutcome (Win/Loss/BE, Grade, Emotion…)
Lists_Backend (UNIQUE/SORT ranges for Data Validation)
README (how to import CSV + refresh)
3) Data Ingestion
Excel: Power Query → import single file or folder of CSVs; enforce types; do not aggregate; load to tblImportRaw. “Refresh All” updates downstream.
Google Sheets: Either
File ▸ Import CSV into Import_Raw (overwrite only that range), or
Apps Script to watch a Drive folder and paste into Import_Raw.
All downstream sheets update by formulas only.
4) Keys & IDs
BetID (from CMC) is the parent grouping key whenever present.
TradeID:
If BetID exists, set TradeID = BetID.
Else generate yyyymmdd-Asset-### (per first open timestamp for that asset/day). A stable GUID helper in Import_Raw is also acceptable.
5) Partial Closes (execution-level)
tblPartialCloses fields (min):
BetID, OrderID, Parent TradeID, Asset, Direction, Stake ( £/pt ), Price, Timestamp, Action (“Open”/“Close”), Fees/Commission, Leg PnL.
Populate from tblImportRaw (one row per execution). Use helper flags IsOpen, IsClose.
Leg PnL (generic)
=IF(Direction="Long",(Exit-Entry)*Stake, (Entry-Exit)*Stake)
(If close legs only have close price, compute against average entry; see #6.)
6) Aggregation to Fact_Trades (one row per parent trade)
In tblFactTrades (all formula-driven):
Entry Date/Time = earliest “Open” for TradeID
Excel/Sheets:
=INDEX(SORT(FILTER(tblPartialCloses[Timestamp],(tblPartialCloses[TradeID]=[@TradeID])*(tblPartialCloses[Action]="Open")),1,TRUE),1)
Exit Date/Time = latest “Close” for TradeID
=INDEX(SORT(FILTER(tblPartialCloses[Timestamp],(tblPartialCloses[TradeID]=[@TradeID])*(tblPartialCloses[Action]="Close")),1,TRUE),COUNTA(FILTER(...)))
Size-Weighted Avg Entry Price (all Open legs)
=SUMPRODUCT(
FILTER(tblPartialCloses[Price],condOpen),
FILTER(tblPartialCloses[Stake],condOpen)
) / SUM(FILTER(tblPartialCloses[Stake],condOpen))
where condOpen = (tblPartialCloses[TradeID]=[@TradeID])*(tblPartialCloses[Action]="Open")
Size-Weighted Avg Exit Price (all Close legs; full close) — same pattern with Action="Close".
Executions = COUNT of legs for TradeID.
Gross P&L = SUM of Leg PnL where Action="Close".
Commissions = SUM of fees for TradeID.
Net P&L = Gross – Commissions.
Holding Time (mins) = (Exit−Entry)2460.
Stake / Size = choose Max Net Exposure (preferred) or Sum of opens.
If XLOOKUP isn’t available, use INDEX/MATCH. All above functions (FILTER, SORT, UNIQUE, SUMPRODUCT) work in Excel 365 and Google Sheets.
7) Spread-Bet Maths (points/pips)
Maintain per-asset PointSize/PipSize and PointValue in tblDimAsset.
Point/Pip Gain
=IF([@Direction]="Long",
([@[Exit Price]]-[@[Entry Price]])/XLOOKUP([@Asset],tblDimAsset[Asset],tblDimAsset[PointSize]),
([@[Entry Price]]-[@[Exit Price]])/XLOOKUP([@Asset],tblDimAsset[Asset],tblDimAsset[PointSize]))
(INDEX/MATCH alt: replace XLOOKUP with INDEX(tblDimAsset[PointSize],MATCH([@Asset],tblDimAsset[Asset],0)))
Gross P&L (if Stake is £/pt):
= ([@[Exit Price]]-[@[Entry Price]]) * IF([@Direction]="Long",1,-1) * [@Stake]
Or via point value:
= [@[Point/Pip Gain]] * [@Stake] * XLOOKUP([@Asset],tblDimAsset[Asset],tblDimAsset[PointValue])
8) Time Buckets & Sessions (auto)
tblDimSession defines rows like:
TimeBucket: Open, Mid-Day, Close
StartTime, EndTime (as time values)
Session: London, New York, Crossover
TimeBucket (by Exit time):
=INDEX(
FILTER(tblDimSession[TimeBucket],
(MOD([@[Exit Date]],1)>=tblDimSession[StartTime])*
(MOD([@[Exit Date]],1)< tblDimSession[EndTime])),
1)
Session:
=INDEX(
FILTER(tblDimSession[Session],
(MOD([@[Exit Date]],1)>=tblDimSession[StartTime])*
(MOD([@[Exit Date]],1)< tblDimSession[EndTime])),
1)
DayOfWeek: =TEXT([@[Exit Date]],"ddd")
Executions/hr: =[@Executions] / MAX(([@[Exit Date]]-[@[Entry Date]])*24, 1/60)
9) Fact_Trades Columns (minimum)
TradeID, BetID, Entry Date, Exit Date, Asset, Asset Class, Direction, Stake, Entry Price, Exit Price, Executions, Gross P&L, Commissions, Net P&L, Point/Pip Gain, RRR (planned), RRR Realized, R-Multiple, Account Size (optional), TP/SL/Original TP Hit (Y/N), Highest/Lowest Price (for MFE/MAE), TimeBucket, Session, DayOfWeek, Preparation/Trend/Entry/Patterns/Fib/Env/Strategy/Emotion/Grade/PCP (dropdowns), Comments.
Populate text/categorical fields via dropdowns; numeric fields via formulas.
10) Dropdowns (Data Validation)
Source lists from Dim tables or Lists_Backend:
=SORT(UNIQUE(tblDimAsset[Asset])), etc.
No hard-typed categories in Fact_Trades.
11) Power BI Readiness
DateKey (INT): =VALUE(TEXT([@[Exit Date]],"yyyymmdd")) in Fact + matching column in DimDate.
Use consistent keys: AssetKey, SetupKey, SessionKey, DirectionKey, OutcomeKey.
No merged cells; column names stable; one row per fact.
Relationships: Dim → Fact (one-to-many). Measures computed in PBI.
12) Summary (Open / Mid-Day / Close)
Provide a “Summary” sheet (Pivot or QUERY) by Date and TimeBucket:
Gross & Net P&L, Executions, Positive/Negative Trades, Win Rate, Avg Win, Avg Loss, Executions/hr.
Include slicers/filters: Date, Asset, Session, Setup.
13) Helper Columns (why)
In Import_Raw: IsOpen, IsClose, ExecIndex (per BetID sequence), CleanAsset.
In Partial_Closes: Leg PnL per close leg, Fees normalized.
These stabilize lookups and aggregation.
14) Protection & UX
Lock formula cells in Partial_Closes and Fact_Trades; leave dropdown cells unlocked.
Freeze headers; consistent table styles.
Excel: optional “Refresh All” button. Sheets: simple macro/menu for “Import CSV”.
15) Acceptance Tests
Drop a new CMC CSV → Import_Raw updates → Partial_Closes & Fact_Trades change automatically (no manual edits).
Partial close example (3 opens, 2 closes) shows correct SWA Entry/Exit, Executions=5, Gross/Net P&L.
Exit times map correctly to Open/Mid-Day/Close and London/NY/Crossover via tblDimSession.
Adding an Asset to DimAsset makes it immediately available in dropdowns.
Power BI connects with clean one-to-many relationships and no ambiguity.
16) Deliverables
One workbook (Excel or Google Sheets) named Elite_Trading_Journal_v2 with all sheets/tables above.
Documented import method (PQ query or Sheets import/script).
10–20 seed trades covering single-shot and multi-leg cases.
Notes on Compatibility
Prefer FILTER, SORT, UNIQUE, SUMPRODUCT, TEXT, INDEX/MATCH; use XLOOKUP where available (supply INDEX/MATCH fallback).
Avoid LET/LAMBDA for cross-compat unless requested.
Time math uses serial times (MOD(DateTime,1)), valid in both apps.