Bet Tracking Spreadsheet – Columns, Formulas and CLV

A bet tracking spreadsheet needs one row per bet with the date, event, market, selection, bookmaker, decimal odds taken, stake and result, plus Pinnacle's closing price on both sides of the market; with those columns, four formulas give you return, ROI, raw closing line value (CLV) and CLV against the fair, no-vig close. Without the closing prices, a spreadsheet only tells you whether you won, not whether you bet good prices.

This guide gives a column layout you can copy into Excel or Google Sheets, the formulas cell by cell, a worked example, and the point where a spreadsheet stops being worth the manual work.

Definition. A bet tracking spreadsheet is a log of every bet you place, one row each, used to measure results (return, ROI) and price quality (CLV). CLV compares the odds you took with the closing price; against Pinnacle's no-vig close it is the most widely used test of whether your prices beat the market.

What columns does a bet tracking spreadsheet need?

Log everything at the moment you bet, except the closing prices, which you add just before the event starts. If you missed a close, see where to find Pinnacle odds history. A log kept every day is also the core of betting discipline.

Keep odds in decimal. If your bookmaker shows American or fractional odds, convert them first with the odds converter; how to read betting odds explains the formats.

Results codes. W = won, L = lost, P = push (stake returned), V = void, HW = half won and HL = half lost for Asian quarter lines such as −0.25 or Over 2.75.

What formulas do you need?

Put these in row 2 and fill down. They work the same in Excel and Google Sheets.

Return (J2):

=IF(I2="W",H2*G2,IF(OR(I2="P",I2="V"),H2,IF(I2="HW",H2/2*G2+H2/2,IF(I2="HL",H2/2,0))))

Net (K2):

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

Raw CLV % (N2): odds taken ÷ closing odds − 1.

=IF(L2="","",G2/L2-1)

Fair close, two-way market (O2): removes Pinnacle's margin with the multiplicative method. The fair price is your side's close × the sum of both sides' implied probabilities.

=IF(OR(L2="",M2=""),"",L2*(1/L2+1/M2))

CLV vs fair close % (P2):

=IF(O2="","",G2/O2-1)

Format N and P as percentages. For a three-way market (football 1X2), add a column for the third closing price and extend the bracket: L2*(1/L2+1/M2+1/X2).

How do you summarise the sheet?

On a second tab, with data in rows 2 to 1000:

Exclude open bets from ROI (filter on a blank result) and split everything by the Tag column. A value bet, an arbitrage and a promo bet in one ROI figure tell you nothing about any of them. ROI and CLV are measurements of past bets, not forecasts.

A worked example

Illustrative numbers, calculated, not a real bet. You take Over 2.5 at 2.30 for a stake of 20. Just before kick-off Pinnacle closes Over 2.5 at 2.12 and Under 2.5 at 1.80.

The match ends 1–0. Result L, return 0, net −20. The bet lost and still had positive CLV: you took a price better than the market's final estimate. One row proves nothing either way; CLV is read over hundreds of bets. The variance simulator shows how wide results spread over a sample.

Note how much the raw figure overstates the edge: +8.5% raw against +5.2% to +5.6% fair. Always track the fair-close column. The CLV calculator checks any single row.

Can a spreadsheet do power devig?

Not with a single formula. The power method raises each implied probability to an exponent k and finds the k that makes them sum to 100%, which needs an iterative solve. In a sheet you can use a goal-seek tool on a helper cell, or check rows with the devig calculator. Multiplicative is a reasonable spreadsheet default; on two-way markets close to even money the methods differ by a fraction of a percentage point, and the gap grows on longshots. Our devig methods guide compares them.

When does a spreadsheet stop being enough?

A spreadsheet is free, private and fully under your control. Its weak point is the close. Every CLV figure depends on recording Pinnacle's price on both sides of the right line just before the start, for every bet. Miss it, record the wrong line, or record it an hour early, and the row is wrong or empty. At a few bets a week that is manageable. At dozens a day, across sports and kick-off times, the missing closes pile up and the average CLV becomes a sample of the bets you remembered.

Other common gaps: odds typed in American format by mistake, Asian quarter lines settled as full wins, and different lines mixed up (Over 2.5 taken, Over 2.25 close). Never compare a price with the close of a different line.

If you outgrow the sheet, see CLV tracking tools compared for the options, with data from each tool's own website.

How Pinnacle Terminal handles this

The Pinnacle Terminal tracker records the odds and stake you actually took, from the terminal or Telegram, and compares them with Pinnacle's closing price automatically, so the "close" columns fill themselves. CLV is measured against the fair close, with Power as the default devig method and the other methods plus a worst case available. Bets settle automatically, and you can import an existing spreadsheet or export your records as CSV. The bets come from the same feed where every pre-match move is graded 0–10 (Big move, Sharp drop, Limit-backed, Downtrend, Rebound, Standard) and live in-play drops are shown filtered. Details on the Tracker & CLV feature page and in the tracker and CLV guide; the concept itself is in closing line value explained.

FAQ

What should a bet tracking spreadsheet include?

Date, event, market, selection, bookmaker, decimal odds taken, stake, result, return and net, plus Pinnacle's closing price on both sides so you can calculate CLV against the fair close.

How do I calculate CLV in Excel or Google Sheets?

Raw CLV is =G2/L2-1 (odds taken ÷ closing odds − 1). For fair CLV, first compute the no-vig close =L2*(1/L2+1/M2) from both closing prices, then =G2/O2-1.

Should I use raw or no-vig closing odds?

No-vig. The raw close includes Pinnacle's margin, so raw CLV overstates your edge. In the example above, +8.5% raw becomes about +5.2% to +5.6% fair.

How do I record Asian handicap half wins in a spreadsheet?

Use separate result codes. A half win returns half the stake at your odds plus half the stake back; a half loss returns half the stake.

Is a spreadsheet or a bet tracker app better?

A spreadsheet is enough for low volume and full control. At higher volume, the manual closing prices are the weak point, and a tracker that records the close automatically avoids missing rows.

Try it

Track every bet against Pinnacle's fair close without typing it in, with every pre-match move graded 0–10 and live in-play drops filtered: start the Pinnacle Terminal 7-day free trial. Nothing is charged before day 8.

18+ · Bet responsibly. Only bet money you can afford to lose.