The Kelly criterion is the gold standard for bet sizing when you have an edge. But most bettors either wing it or use some janky online calculator that doesn't pull live odds. Today, we're building a Google Sheets Kelly criterion calculator for sports betting that automatically pulls current market data and tells you exactly how much to bet.
This isn't another theoretical walkthrough. We're building a working sheet with real formulas that connect to live sportsbook odds via the MoneyLine API. By the end, you'll have a tool that calculates optimal bet sizes based on your edge estimates and current bankroll.
Why Kelly Criterion Matters for Sports Bettors
The Kelly criterion solves the fundamental question every sharp bettor faces: given my edge and bankroll, what's the optimal bet size? Bet too small and you leave money on the table. Bet too large and you risk ruin even with positive expected value.
The basic Kelly formula is simple: f = (bp - q) / b, where:
- f = fraction of bankroll to bet
- b = odds received (decimal odds - 1)
- p = probability of winning (your estimate)
- q = probability of losing (1 - p)
But implementing this with live odds requires pulling current market data, calculating implied probabilities, and handling edge cases where Kelly suggests betting more than you're comfortable with. That's where our Google Sheets setup comes in.
Most bettors use fixed units or gut feelings for bet sizing. The math shows this is suboptimal. Our EV analysis proves that proper position sizing amplifies your edge exponentially over time.
Building the Kelly Calculator Spreadsheet
Start with a new Google Sheet. We'll build this in sections: bankroll tracking, odds input, probability estimation, and Kelly calculation.
Basic Sheet Structure
Create these columns in row 1:
- A1: "Game"
- B1: "Bet Type"
- C1: "Market Odds"
- D1: "True Odds"
- E1: "Win Prob"
- F1: "Kelly %"
- G1: "Bet Size"
- H1: "Expected Value"
In A2, we'll start with a sample bet: "Lakers vs Warriors". B2 gets "Lakers ML". Now we need to pull live odds for C2.
Connecting to Live Odds Data
Google Sheets doesn't natively support API calls, but Google Apps Script does. Go to Extensions → Apps Script and create this function:
function getMoneyLineOdds(game, market) {
const apiKey = 'your_moneyline_api_key';
const baseUrl = 'https://mlapi.bet';
try {
const response = UrlFetchApp.fetch(
`${baseUrl}/v1/odds?game=${encodeURIComponent(game)}&market=${encodeURIComponent(market)}`,
{
'headers': {
'Authorization': `Bearer ${apiKey}`,
'Content-Type': 'application/json'
}
}
);
const data = JSON.parse(response.getContentText());
// Return best available odds
if (data.odds && data.odds.length > 0) {
return data.odds[0].price;
}
return 'No odds found';
} catch (error) {
return 'API Error';
}
}
function getImpliedProbability(odds) {
if (typeof odds !== 'number' || odds <= 0) {
return 'Invalid odds';
}
// Convert to decimal if American odds
let decimal;
if (odds > 0) {
decimal = (odds / 100) + 1;
} else {
decimal = (100 / Math.abs(odds)) + 1;
}
return 1 / decimal;
}
Save the script, then return to your sheet. In C2, enter: =getMoneyLineOdds(A2, B2). This pulls live odds for your Lakers ML bet.
Kelly Calculation Formulas
Now for the core formulas. In D2 (True Odds), enter your estimated fair odds. This is where your handicapping comes in - what do you think the true odds should be?
E2 (Win Prob) converts your true odds to probability: =1/D2
F2 (Kelly %) is the money formula: =MAX(0, (E2*C2 - 1)/(C2-1))
This formula includes a MAX function to prevent negative Kelly values, which would suggest betting the other side.
G2 (Bet Size) multiplies Kelly by your bankroll: =F2*$K$1 (assuming K1 contains your total bankroll)
H2 (Expected Value) calculates your edge: =(E2*C2 - 1)*G2
Advanced Features and Risk Management
Raw Kelly can be aggressive. Most pros use fractional Kelly to reduce volatility.
Fractional Kelly Implementation
Add a "Kelly Fraction" cell (say, K2) with a default of 0.25 (quarter Kelly). Modify your bet size formula to: =F2*$K$1*$K$2
This reduces your bet sizes by 75%, dramatically lowering your risk of ruin while still capturing most of the growth.
Bankroll Updates
Create a running bankroll tracker. In column I, track your results:
- I1: "Result"
- I2: Enter actual profit/loss from each bet
In K1 (Total Bankroll), use: =K3+SUMIF(I:I, ">0")-SUMIF(I:I, "<0") where K3 is your starting bankroll.
Multiple Sportsbooks
The MoneyLine API returns odds from dozens of sportsbooks. Modify your Apps Script to find the best available price:
function getBestOdds(game, market) {
const apiKey = 'your_moneyline_api_key';
const response = UrlFetchApp.fetch(
`https://mlapi.bet/v1/odds?game=${encodeURIComponent(game)}&market=${encodeURIComponent(market)}`,
{
'headers': {
'Authorization': `Bearer ${apiKey}`
}
}
);
const data = JSON.parse(response.getContentText());
let bestOdds = -Infinity;
let bestBook = '';
data.odds.forEach(odd => {
if (odd.price > bestOdds) {
bestOdds = odd.price;
bestBook = odd.sportsbook;
}
});
return `${bestOdds} (${bestBook})`;
}
Automating Your Betting Workflow
Set up triggers to refresh odds automatically. In Apps Script, go to the clock icon (Triggers) and create a time-driven trigger that runs refreshAllOdds() every 15 minutes during betting hours.
function refreshAllOdds() {
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
for (let i = 2; i <= lastRow; i++) {
const game = sheet.getRange(i, 1).getValue();
const market = sheet.getRange(i, 2).getValue();
if (game && market) {
const odds = getMoneyLineOdds(game, market);
sheet.getRange(i, 3).setValue(odds);
}
}
}
This keeps your Kelly calculations current with market movements. When odds shift in your favor, your bet size adjusts automatically.
For serious bettors tracking multiple sports, our arbitrage detection system shows how to scale this approach across hundreds of games simultaneously.
Common Kelly Calculator Mistakes to Avoid
Mistake 1: Using market-implied probabilities as true probabilities. Your edge comes from disagreeing with the market. If you think the market is perfectly efficient, Kelly suggests never betting.
Mistake 2: Ignoring correlation. Kelly assumes independent bets. If you're betting correlated markets (team total over + game over), your effective Kelly percentage is higher than calculated.
Mistake 3: Not accounting for vig. When calculating true odds, factor in the sportsbook's edge. A fair coin flip at -110 both sides implies each outcome has a 52.4% probability, not 50%.
Mistake 4: Betting full Kelly. Even with perfect probability estimates, full Kelly produces massive swings. Quarter or half Kelly preserves most of the growth with much lower volatility.
Mistake 5: Static bankroll assumptions. Update your bankroll regularly. As you win (or lose), optimal bet sizes change proportionally.
Frequently Asked Questions
What if Kelly suggests betting more than my maximum bet size?
Cap your bet at whatever you're comfortable with. Kelly assumes you can make infinite bets at the given odds, which isn't realistic. Many pros never bet more than 5% of bankroll regardless of what Kelly suggests.
How accurate do my probability estimates need to be?
Kelly is sensitive to probability inputs. A 5% error in win probability can dramatically change the recommended bet size. Focus on games where you have the strongest conviction about your edge.
Should I use American or decimal odds in the formula?
The formula works with either, but decimal odds are cleaner. If pulling American odds from the API, convert them: positive odds become (odds/100) + 1, negative odds become (100/abs(odds)) + 1.
Can I use this for player props and totals?
Absolutely. The Kelly formula works for any two-outcome bet. For three-way markets (win/lose/push), you need a modified Kelly formula that accounts for the push probability.
How often should I refresh the odds data?
For live betting, every few minutes. For pre-game betting, hourly updates are usually sufficient. More frequent updates cost more API credits but give you better timing on line movements.
The MoneyLine API comparison shows response times across different providers if latency matters for your use case.