1. #1
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    Overbets and Line Changes -- A Kelly Spreadsheet

    I'll do a writeup explaining this later, but here's an Excel spreadsheet I designed to help determine optimal bet sizes after following line changes, overbets, or some combination of the two. It should be largely self-explanatory.

    The spreadsheet assumes no other positions and logarithmic preferences. In the case of a positive edge, it utilizes the Kelly fraction with numerator of the user-specified ideal position size and denominator of the full Kelly stake. In the case of a nonpositive edge, a user-defined Kelly fraction is used. Play around with the spreadsheet by changing any of the shaded cells.

    And of course, please let me know if you find any errors.
    Attached Files

  2. #2
    Justin7
    Justin7's Avatar Become A Pro!
    Join Date: 07-31-06
    Posts: 8,577
    Betpoints: 1506

    Ganchrow, I'm curious about some of your assumptions... Can I test them with this hypothetical?

    You think you have a 2% edge betting team "A" at +3. You make your bet, risking 1% (you're using half-kelly). Afterwards, the line moves to +3.5. You think the "3" will push 5% of the time.

    what do you do now, with the new line?

  3. #3
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    Quote Originally Posted by Justin7 View Post
    Ganchrow, I'm curious about some of your assumptions... Can I test them with this hypothetical?

    You think you have a 2% edge betting team "A" at +3. You make your bet, risking 1% (you're using half-kelly). Afterwards, the line moves to +3.5. You think the "3" will push 5% of the time.

    what do you do now, with the new line?
    This spreadsheet doesn't yet handle spread changes, only changes in payout changes. I posted a (somewhat less user friendly) spreadsheet that does this (along with a detailed discussion of the assumptions) in this thread.

    But anyway this is what we have (you seem to be implying +100 odds for all bets).

    The +3 wins with probability 48.45%, loses with probability, 46.55%, and pushes with probability 5.00%, for an edge of 2%.

    The +3½ bet wins with probability 53.45% and loses with probability 46.55%, for an edge of 6.90%.

    When you do the computations below, you come up with a bet of 2.4943% of bankroll for the +3½ +100.


    And here are the computations:

    Win both = 48.45%
    Win +3½ and push + 3 = 5.00%
    Lose Both = 46.55%

    Hence:

    max E(U) =
    48.45%*ln(1+(1%+x)*2)
    + 5%*ln(1+(x)*2)
    + 46.55%*ln(1-(x+1%)*2)

    w.r.t. x, s.t. 0 ≤ x < 49%

    Setting E' = 0 gives us:

    48.45%*2 / (1.02+2x) + 5%*2 / (1+2x) - 46.55%*2 / (.98-2x) = 0

    and after some messy algebra we find
    8000x2 + 3808x - 99.96 = 0

    implying x ≈ 2.4943%, the sole positive root of the quadratic. (And as E" < 0 for all real x, x ≈ 2.4943% will maximize expected utility.)
    Last edited by Ganchrow; 03-02-07 at 03:24 PM.

  4. #4
    Justin7
    Justin7's Avatar Become A Pro!
    Join Date: 07-31-06
    Posts: 8,577
    Betpoints: 1506

    Ahhh. You fell right into my trap!

    If you bet a game at +3, and it moves to +3.5, you need to re-evaluate whether your edge was truly 2%. In general, if the market disagrees with you, you are screwed. I would argue that you shouldn't bet more at +3.5. If I had a fair way to get rid of my +3 bet at that point, I would.

    Same thing applies if you bet at 2.0, and the odds move to 2.05. The market just told you that you were wrong...

  5. #5
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    Quote Originally Posted by Justin7 View Post
    Ahhh. You fell right into my trap!

    If you bet a game at +3, and it moves to +3.5, you need to re-evaluate whether your edge was truly 2%. In general, if the market disagrees with you, you are screwed. I would argue that you shouldn't bet more at +3.5. If I had a fair way to get rid of my +3 bet at that point, I would.

    Same thing applies if you bet at 2.0, and the odds move to 2.05. The market just told you that you were wrong...
    (You're treading dangerously close to begging the question here ...)

    In quantitative modeling and programming one often hears the phrase "Garbage in, garbage out." This refers to the fact if you provide a model with shoddy input data, your conclusions will be similarly unreliable.

    Similarly, this methodology makes no assumptions whatsoever about expectations, rather it takes as input operator specified return estimates. This, after all, is a a risk/return optimization model and not a return forecast model.

    I wish you had initially phrased the problem as you did in your last post. For one thing, it would have saved me the effort of demonstrating the calculations. And for another, the problem as you respecified, which demonstrates an overbet, is actually just the sort of problem designed to be solved by the attached spreadsheet. (Although I do still need to include the facility for handling correlations < |1| for it to be strictly applicable. This is just a UI issue.)

    The idea is quiet simple. This is the general case:
    A player makes a bet and at some time later time determines that he bet too much. The rationale for coming to this determination is completely irrelevant. It could just be because he accidentally added an extra zero to his bet size, because the line moves in his favor, because the line moves against him, because the line doesn't move at all, because it starts raining, because it doesn't start raining, or because his second-cousin-once-removed tells him he thinks the bet is definitely going to lose. The point is that return estimates are 100% exogenous to the model.

    Once the determination is made based upon current return estimates (whether or not they've changed since the bet was initially placed) that the player has bet more than the optimal quantity, the spreadsheet can then be used to determine how much (if any) of the bet would be laid off at the current price.
    So that's how it works. If you provide accurate return and/or probability estimates you'll get accurate results. If your estimates are inaccurate, your results will be inaccurate.

    Garbage in, garbage out.

  6. #6
    Justin7
    Justin7's Avatar Become A Pro!
    Join Date: 07-31-06
    Posts: 8,577
    Betpoints: 1506

    Ganchrow -

    I'm not trying to bust your chops. I think you already understand what I was getting at. I wish I understood the concept of "market rejection" of a play years ago... it would have saved me a lot of money.

  7. #7
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    (New spreadsheet available as an attachment to the first post of this thread.)

    OK. So I've modified the spreadsheet so that it now accurately handles bets that are less than perfectly correlated to the original. The bets still need to be based on the same underlying event and bet type. So while bets on the same game's side and total would not be handled, bets on two different spreads (including money lines, which are really just PK spreads) for a single game would.

    The current values in the spreadsheet correspond to Justin's problem as initially posed:
    1. The bettor plays half-Kelly (Kelly Multiplier = 0.5).
    2. The initial bet (Team +3) is offered at odds of +100, has a win probability of 48.45%, a lose prob of 46.55%, and a push probability of 5%, corresponding to an edge of 2%.
    3. The initial bet stake is 1%.
    4. The new bet (the side positively correlated with the initial, in other words Team +3½) is offered at odds of +100, has a win probability of 53.45% and a lose probability of 46.55%, corresponding to an edge of 6.9%.
    5. The opposite side of the new bet (the side negatively correlated with the initial, in other words Other Team -3½) is offered at odds of +100, has a win probability of 46.55% and a lose probability of 53.45%, corresponding to an edge of -6.9%.

    And indeed, clicking the "Calculate Stakes" button yields a stake on the new bet of 2.4943% of bankroll, which is of course the same result as that determined from the calculus above.

    Now let's consider the same initial position but different estimates of probability after the line movement. Specifically let's assume that the line movement convinced the bettor that his initial return estimation was flawed and in fact he now judges the +3½ bet as a 50/50 proposition. So this is what we now have:
    1. The bettor still plays half-Kelly
    2. The initial bet (Team +3) is offered at odds of +100, has a win probability of 45% (enter in Original Position "Win Prob" cell), a lose prob of 50.00%, and a push probability of 5% (enter in Original Position "Push Prob" cell), corresponding to an edge of -5.2632%.
    3. The initial bet stake is 1%.
    4. The new bet (Team +3½) is offered at odds of -110 (enter in Position after Line Move "Odds" cell), has a win probability of 50% (enter in Position after Line Move "Win Prob" cell), and a lose probability of 50%, corresponding to an edge of -4.5455% (which is just the theoretical hold).
    5. The opposite side of the new bet (Other Team -3½) is offered at odds of -110 (enter in Position after Line Move (opposite side) "Odds" cell), has a win probability of 50% and a lose probability of 50%, corresponding to an edge of -4.5455%.

    Clicking the "Calculate Stakes" button yields a stake on both new bets of 0%. This means that given the scenario, the proper decision for a half-Kelly bettor would be to simply sit on the the initial without hedging at all.

    Now let's look at the exact same scenario but with a line of -101 (enter in Position after Line Move (opposite side) "Odds" cell), corresponding to an edge of -0.4950%. Click "Calculate Stakes" again and you see that at -101 the proper hedge quantity would be 0.7041% of bankroll.

    Once again, I hope you'll please let me know if you find any errors.

  8. #8
    Justin7
    Justin7's Avatar Become A Pro!
    Join Date: 07-31-06
    Posts: 8,577
    Betpoints: 1506

    Nice analysis. Are you and I the only math geeks here?

  9. #9
    Dark Horse
    Deus Ex Machina
    Dark Horse's Avatar Become A Pro!
    Join Date: 12-14-05
    Posts: 13,764

    If the line moved in your favor, is it better to sell your position (in an exchange format with enough liquidity) for a relatively small -but not tiny- ensured profit, or play your edge?

    Say you could get 14% profit just by pregame trading. Does a 14% ensured profit translate into a bet with a 57-43 win/loss expectation?

    Or would you factor in that the market 'confirmed' your bet?
    Last edited by Dark Horse; 03-03-07 at 05:58 PM.

  10. #10
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    (New spreadsheet available as an attachment to the first post of this thread.)

    Quote Originally Posted by Dark Horse View Post
    If the line moved in your favor, is itbetter to sell your position (in an exchange format with enough liquidity) for a relatively small -but not tiny- ensured profit, or play your edge?

    Say you could get 14% profit just by pregame trading. Does a 14% ensured profit translate into a bet with a 57-43 win/loss expectation?
    There's really no simple answer to this question. It depends upon the details of the situation, specifically the odds and probabilities of the both the current and initial bets, as well as the Kelly multiplier.

    We can try a couple examples. Enter:
    • Select US odds from "Odds Style" dropdown
    • Original Position
      • Odds = -125
      • Push Prob = 5.0000%
      • Edge = 14.0000%
      • Actual Stake = 1.0000%
      • Implied Kelly multiplier = 0.2500 (quarter-Kelly)
    • Position after Line Move
      • Odds = -160
      • Push Prob = 2.0000%
      • Edge = -2.5000%
    • Position after Line Move (opposing side)
      • Odds = +145


    Click "Calculate Stakes", and Position after Line Move (opposing side) stake size is now recommended at 0.3445% of bankroll. This means that if a quarter-Kelly bettor were initially risking $100 to win $80 on the -125 bet, the optimal bet on the new opposite side of the two market would now be to risk $34.45 to win $49.9525 (meaning he'd win $45.55 were the initial bet to win, and would lose $48.33 otherwise.)

    Now try changing the Implied Kelly multiplier to 1 (full-Kelly). Click "Calculate Stakes", and Position after Line Move (opposing side) stake size is now recommended at 0% of bankroll. This means that a fully-Kelly bettor would not hedge the new bet at all.

    And finally let's try entering this:
    • Select US odds from "Odds Style" dropdown
    • Original Position
      • Odds = +1000
      • Push Prob = 0%
      • Edge = 14.0000%
      • Actual Stake = 1.0000%
      • Implied Kelly multiplier = 0.2500 (quarter-Kelly)
    • Position after Line Move
      • Odds =+800.0
      • Push Prob = 0%
      • Win Prob = 10.3636% (the same win prob as in the initial bet -- you could also just type =OrigWinP
    • Position after Line Move (opposing side)
      • Odds = -900

    Click "Calculate Stakes", and Position after Line Move (opposing side) stake size is now recommended at 8.9874% of bankroll. This means that if a quarter-Kelly bettor were initially risking $100 to win $1,000 at the +1000 bet, the optimal bet on the new opposite side of the market would now be to risk $898.74 to win $99.86 (meaning he would win $101.26 were his initial bet to win and would otherwise lose $0.14).

    Now try changing the Implied Kelly multiplier to 1 (full-Kelly). Click "Calculate Stakes", and Position after Line Move (opposing side) stake size is now recommended at 6.2601% of bankroll. if a full-Kelly bettor were initially risking $100 to win $1,000 on the +1000 bet, the optimal bet on the new opposite side of the market would now be to risk $626.01 to win $69.56 (meaning he would win $373.99 were his initial bet to win and would otherwise lose $30.44).

  11. #11
    Data
    Data's Avatar Become A Pro!
    Join Date: 11-27-07
    Posts: 2,236

    Quote Originally Posted by Ganchrow View Post
    The current values in the spreadsheet correspond to Justin's problem as initially posed:
    1. The bettor plays half-Kelly (Kelly Multiplier = 0.5).
    2. The initial bet (Team +3) is offered at odds of +100, has a win probability of 48.45%, a lose prob of 46.55%, and a push probability of 5%, corresponding to an edge of 2%.
    3. The initial bet stake is 1%.
    4. The new bet (the side positively correlated with the initial, in other words Team +3½) is offered at odds of +100, has a win probability of 53.45% and a lose probability of 46.55%, corresponding to an edge of 6.9%.
    5. The opposite side of the new bet (the side negatively correlated with the initial, in other words Other Team -3½) is offered at odds of +100, has a win probability of 46.55% and a lose probability of 53.45%, corresponding to an edge of -6.9%.

    And indeed, clicking the "Calculate Stakes" button yields a stake on the new bet of 2.4943% of bankroll, which is of course the same result as that determined from the calculus above.
    I enter the numbers above and get 2.4999%. The must be some mistake in the code. If I enter the same numbers as in "Original Position", 48.45% and 5% while the lines are +100 for both sides, I get nonsensical results: the suggested stakes are 1.0001% on one side and 0.9999% on the other side.

    Now let's consider the same initial position but different estimates of probability after the line movement. Specifically let's assume that the line movement convinced the bettor that his initial return estimation was flawed and in fact he now judges the +3½ bet as a 50/50 proposition. So this is what we now have:[list=1][*]The bettor still plays half-Kelly[*]The initial bet (Team +3) is offered at odds of +100, has a win probability of 45% (enter in Original Position "Win Prob" cell), a lose prob of 50%, and a push probability of 5% (enter in Original Position "Push Prob" cell), corresponding to an edge of -5.2632%.
    Typos corrected.

    Once again, I hope you'll please let me know if you find any errors.

  12. #12
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    Quote Originally Posted by Data View Post
    I enter the numbers above and get 2.4999%.
    I am unable to reproduce this. When I enter the values as specified above into the spreadsheet (linked as an attachment from here) and click calculate I get a stake of 2.4943%.

    These should represent the relevant cell values:
    Code:
    Cell B5:	 +100.0
    Cell B6:	48.4500%
    Cell B7:	46.5500%
    Cell B8:	 5.0000%
    Cell B9:	 2.0000%
    Cell B10:	 2.0000%
    Cell B11:	 1.0000%
    Cell B12:	 1.0000%
    Cell B13:	 0.5000
    
    Cell B18:	 +100.0
    Cell B19:	53.4500%
    Cell B20:	46.5500%
    Cell B21:	 0.0000%
    Cell B22:	 6.9000%

    Quote Originally Posted by Data View Post
    The must be some mistake in the code. If I enter the same numbers as in "Original Position", 48.45% and 5% while the lines are +100 for both sides, I get nonsensical results: the suggested stakes are 1.0001% on one side and 0.9999% on the other side.
    These results are not incorrect beyond rounding error. Realize that the optimal solution to the problem you've specified is actually an entire vector of values (as both the "position after line move" and the opposing side" have offsetting prices). With a Solver precision of 0.000001 or less (which should be automatically set by clicking the "Calculate" button) the rounding error should go away to 6 decimal places (or 4 when formatted as a percent) on Excel 2003 with Service Pack 2 with macros enabled.

    Quote Originally Posted by Data View Post
    Typos corrected.
    Thank you, Sir.

  13. #13
    RickySteve
    SBR is a criminal organization
    RickySteve's Avatar Become A Pro!
    Join Date: 01-31-06
    Posts: 3,415
    Betpoints: 187

    You're bumping a year-old post to blame Ganchrow for your failure to properly adjust optimization parameters?

    Maybe you should spend more energy working on your betting.

  14. #14
    Data
    Data's Avatar Become A Pro!
    Join Date: 11-27-07
    Posts: 2,236

    Quote Originally Posted by Ganchrow View Post
    I am unable to reproduce this. When I enter the values as specified above into the spreadsheet (linked as an attachment from here) and click calculate I get a stake of 2.4943%.

    These should represent the relevant cell values:
    Code:
    Cell B5:	 +100.0
    Cell B6:	48.4500%
    Cell B7:	46.5500%
    Cell B8:	 5.0000%
    Cell B9:	 2.0000%
    Cell B10:	 2.0000%
    Cell B11:	 1.0000%
    Cell B12:	 1.0000%
    Cell B13:	 0.5000
    
    Cell B18:	 +100.0
    Cell B19:	53.4500%
    Cell B20:	46.5500%
    Cell B21:	 0.0000%
    Cell B22:	 6.9000%
    I tried it on several computers running Excel 2003 SP3 with the same results, I get 2.4999%. I am guessing you have hacked your solver.xla.


    These results are not incorrect beyond rounding error. Realize that the optimal solution to the problem you've specified is actually an entire vector of values (as both the "position after line move" and the opposing side" are at the same price).
    Rounding aside, both stakes must be zeros. Do not you agree that mutually cancelling stakes make no sense if given as the results?

    Here is a slightly modified scenario which again produces unexpected results. Again, we have the same identical numbers for "Original Position" and for "Position after Line Move". Change Odds in cells B5 and B18 to +200. Change Odds in cell E18 to -200 or less. The ideal stake changes to 13.25%. Since Actual Stake is 1% we would expect to get 12.25% in B23 but we get 11.8613% instead.

    I observe both unexpected results when Kelly Multiplier is set to 0.5. If it is set to 1 then the results are as expected.

  15. #15
    Data
    Data's Avatar Become A Pro!
    Join Date: 11-27-07
    Posts: 2,236

    Quote Originally Posted by RickySteve View Post
    You're bumping a year-old post to blame Ganchrow for your failure to properly adjust optimization parameters?

    Maybe you should spend more energy working on your betting.
    No sir, I need Ganchrow's help with one of the betting tools that he developed and I use. I very much appreciate the help he provides. I doubt there was a hint of a blame in my post. Please enlighten me on adjusting optimization parameters where you say I failed. Thank you in advance.

  16. #16
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    Quote Originally Posted by Data View Post
    I tried it on several computers running Excel 2003 SP3 with the same results, I get 2.4999%. I am guessing you have hacked your solver.xla.
    I'll have to play around with it and see what I can find.

    Quote Originally Posted by Data View Post
    Rounding aside, both stakes must be zeros. Do not you agree that mutually cancelling stakes make no sense if given as the results?
    The point is that mutually canceling stakes solve the problem exactly as specified (the Kelly problem). In the end, because of the equal magnitude and opposite direction of the gradients this is really just an optimizer (or perhaps problem specification) relic. If we wanted, we could include a binary constraint such that the product of the two stakes needed to be zero, but it would obviously have no impact on Kelly utility and it would just represent an exogenous (even if in the real world perfectly reasonable) constraint. This would also cause problems in the case of a pure arbitrage existing between the new and opposite lines.


    Quote Originally Posted by Data View Post
    Here is a slightly modified scenario which again produces unexpected results. Again, we have the same identical numbers for "Original Position" and for "Position after Line Move". Change Odds in cells B5 and B18 to +200. Change Odds in cell E18 to -200 or less. The ideal stake changes to 13.25%. Since Actual Stake is 1% we would expect to get 12.25% in B23 but we get 11.8613% instead.

    I observe both unexpected results when Kelly Multiplier is set to 0.5. If it is set to 1 then the results are as expected.
    Yeah, the whole notion of "ideal stake"/"actual"/"implied" Kelly multipliers were included in the earliest version of this sheet for no good reason applicable to sophisticated bettors. Now that I've read what you just wrote it would seem that when I modified the code to properly handle n-Kelly utility I left some bad stuff in there.

    The idea is that "ideal stake" represents naïve Kelly-utility rather than actual and that seems to be messing with the numbers. An "ideal stake" of 13.25% (naïvely) implies a Kelly multiplier of 0.5. But the proper half-Kelly stake given the stated edge and odds is in fact 12.8613%. So that's what's happening.

    This sheet's been due for an overhaul for some time anyway ... I'll see if I can't fix it up later.

  17. #17
    Ganchrow
    Nolite te bastardes carborundorum.
    Ganchrow's Avatar Become A Pro!
    Join Date: 08-28-05
    Posts: 5,011
    Betpoints: 1088

    OK. I uploaded a new version of the sheet.

    The 2.439% figure was indeed incorrect, stemming from the naïve Kelly approximation. That was obviously fixed some time ago (hence the correct 2.499% figure Data was seeing in the spreadsheet -- I had just been pulling an old sheet from my browser cache.) I just never updated the above post to reflect the corrected 2.499% stake.

    The ideal stake cell is now no longer editable. The only way to 100% correctly handle that would be to solve the following for κ, the Kelly multiplier exponent:
    KellyStake = ((f*p)κ - (1-p)κ) / ((f*p)κ + f*(1-p)κ)
    where f refers to the fractional odds (i.e., decimal odds minus 1), and p the win probability. Good luck.

    Another possibility would be to use Solver to back out a Kelly multiplier from the Ideal Stake. But that just seems exceedingly silly and a tremendous waste of time (both my time and that of the user.)

    The option to switch odds styles from US to decimal now works properly again.

    "Mutually canceling stakes" should now gneerally be less of a problem in typical cases.

    Still, by changing the odds on the "bad" side just slightly this won't be a problem. In the example Data had given:
    Quote Originally Posted by Data View Post
    same identical numbers for "Original Position" and for "Position after Line Move". Change Odds in cells B5 and B18 to +200 [and odds in E18 to -200].
    we find a value for "position after line move stake" of 11.9239% and an "opposing side" stake of 0.1253%.

    Change the -200 in E18 to -200.1 and the "opposing side" stake drops to 0%. Utility of course remains unchanged.

  18. #18
    Data
    Data's Avatar Become A Pro!
    Join Date: 11-27-07
    Posts: 2,236

    Thank you, sir!

    Aside from the intended purpose this spreadsheet can be of a great value to the baseball and soccer bettors. It can be used to size bets between moneyline and runline (asian handicap).



    I guess many bettors face this problem yet do not know that there is a solution.

  19. #19
    roasthawg
    roasthawg's Avatar Become A Pro!
    Join Date: 11-09-07
    Posts: 2,990

    Edit: Nevermind, fixed it... looks like it works great!
    Last edited by roasthawg; 08-03-09 at 02:01 AM.

  20. #20
    Dark Horse
    Deus Ex Machina
    Dark Horse's Avatar Become A Pro!
    Join Date: 12-14-05
    Posts: 13,764

    This looks like an amazing tool. Is it possible to add this to the betting tools for easier accessibility?

Top