Use four linked tables
| Table | Minimum fields |
|---|---|
| Decision | decision_id, event_id, market, selection, forecast_time, model_version, probability, rule_result |
| Execution | attempt_id, decision_id, operator, displayed_price, requested_stake, accepted_price, accepted_stake, rejection_reason |
| Settlement | attempt_id, settled_time, outcome_state, gross_return, commission, net_profit, rule_version |
| Cash ledger | transaction_id, time, account, deposit, withdrawal, fee, currency, base_value |
Keep passes and rejected attempts. They show whether the published method was executable and prevent successful fills from being selected after the fact.
Checked formulas
For an ordinary fixed-odds back bet:
Gross return = accepted stake * decimal odds when won
Net profit = gross return - accepted stake - commission
For settled records:
Yield = sum(net profit) / sum(accepted stake)
For cash reconciliation:
Closing cash = opening cash + deposits - withdrawals + settled net profit - fees - open cash committed
The exact reconciliation can differ when operator balances include or exclude open stakes. Document that balance convention rather than changing the formula until it fits.
Worked row
An illustrative accepted stake is GBP 12 at decimal odds 2.25 and it wins with no commission.
Gross return = 12 * 2.25 = GBP 27
Net profit = 27 - 12 = GBP 15
If the operator accepted only GBP 8, calculate from GBP 8, not the requested GBP 12: gross return is GBP 18 and net profit is GBP 10.
Validation rules
- Check that IDs are unique and never reused.
- Forecast and rule timestamps precede event start.
- Probability is between zero and one for each defined outcome.
- Accepted stake cannot exceed requested stake without a documented adjustment.
- A settled row links to one execution attempt.
- Net profit recalculates from raw settlement fields.
- Cash transfers never appear as profit.
- Open exposure matches current receipts.
- Historical model, unit, and rule versions are immutable.
Forecast-quality sheet
Store every numeric probability with the corresponding outcome. The original Brier score paper supports probability verification, while calibration guidance explains why reliability needs more than a winner count.
Do not mix probability quality with betting return in one score. A model can forecast well but be paired with poor prices, or return well over a short period through variance.
Reconciliation and review
Lock monthly snapshots, retain source exports, and record every correction with old value, new value, reason, and time. Compare the cash ledger with bank and operator statements. The external budget should use accurate income and outgoings; MoneyHelper provides that separate process.
Add boundary and failure tests
Create test rows before entering live records: a loss, a win, a void, a partial acceptance, a rejected attempt, a zero-stake pass, an exchange liability, and a settlement correction. Verify that each row enters the correct coverage, turnover, profit, and open-exposure denominator. A rejected stake should count toward execution coverage but contribute zero settled stake; a pass should remain a decision without becoming a loss.
Protect formula columns and validate raw inputs. Flag duplicate event-market-selection-time keys, settlement before acceptance, negative accepted stakes, decimal odds below one, and probabilities outside zero to one. Keep a correction log rather than overwriting a receipt. These tests turn a spreadsheet from a diary into an auditable data product.
Next step
Use Value Betting Model Spreadsheet for the next part of this topic.
Continue learning
- Next guide: Betting Budget Plan
- Related guide: Betting ROI Calculator
Assumptions and limitations
The schema is a field specification, not a downloadable financial product. Exchange lay bets, each-way bets, split lines, cash-outs, multi-currency balances, and taxes require additional payoff fields. A correct spreadsheet can reveal errors; it cannot validate an unsupported probability model or prevent harmful gambling.
GambleAware
GamCare
Gambling Therapy
GAMSTOP