1. #36
    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 dday0707 View Post
    I tried the enumcombin formula but it's not working. Can u pls elaborate and give another simple example. I tried for example to list the following 5 teams Man Utd, Liverpool, Chelsea, Arsenal & Aston Villa.
    Could you please provide an example of what you've tried as well as be more specific about what you're looking to accomplish?

    Are you sure you've properly entered the formula as an array function?

    Quote Originally Posted by dday0707 View Post
    Also is there a way let say if I want to cover 2 options i.e either each team to win or draw. so for 5 teams with 2 options will make me 32 possibilities. Can we have your formula to do it.
    Again, could you please be more specific about what you're looking to accomplish? For what sort of output are you looking?

  2. #37
    dday0707
    dday0707's Avatar Become A Pro!
    Join Date: 02-16-09
    Posts: 3

    Ok. Find attached an excel sheet withan example.

    THX
    Attached Files

  3. #38
    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 dday0707 View Post
    Ok. Find attached an excel sheet withan example.

    THX
    Makes use of a new (still undocumented) function I just added: {=ENUMCOMBIN3(Range, Size, "WinLabel", "DrawLabel")}.

    WinLabel and DrawLabel default to values of "WIN" and "DRAW" respectively.

    Output dimensions are {Range size} columns x {2^Size} rows.

    Sample output (transposed) found in A19:H21.
    Attached Files

  4. #39
    dday0707
    dday0707's Avatar Become A Pro!
    Join Date: 02-16-09
    Posts: 3

    Quote Originally Posted by Ganchrow View Post
    Makes use of a new (still undocumented) function I just added: {=ENUMCOMBIN3(Range, Size, "WinLabel", "DrawLabel")}.

    WinLabel and DrawLabel default to values of "WIN" and "DRAW" respectively.

    Output dimensions are {Range size} columns x {2^Size} rows.

    Sample output (transposed) found in A19:H21.
    HI,

    That's COOL. It's working like charm.
    THANK YOU VERY MUCH
    U R A GENIUS
    Last edited by dday0707; 02-21-09 at 08:41 AM.

  5. #40
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    Whats the quick formula to determine which middle to choose?

    Example 1

    Spurs -3.5 -116
    Cavs +5.5 -113

    Pays +1379

    Example 2

    Spurs -3.5 -130
    Cavs +4.5 +105

    Pays +1786

    Whats a quick formula to calculate the payout ratios, assume im buying enough of both sides so that the position is neutral vig wise.

    The formula im currently using is a bit bulky.

  6. #41
    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 Pancho sanza View Post
    Whats the quick formula to determine which middle to choose?

    Example 1

    Spurs -3.5 -116
    Cavs +5.5 -113

    Pays +1379

    Example 2

    Spurs -3.5 -130
    Cavs +4.5 +105

    Pays +1786

    Whats a quick formula to calculate the payout ratios, assume im buying enough of both sides so that the position is neutral vig wise.

    The formula im currently using is a bit bulky.
    I'm sorry Pancho, but I don't understand your question.

  7. #42
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    Lets say San Antonio is playing Cleveland.

    I get an alert 15 minutes prior to tip saying Lebron is out for the Cavs.

    Lets say I lay all the spurs I can find knowing I will later be able to take Cleveland for a +ev middle.

    The market has now settled, I add up all the spurs i got down for, lets assume i got down 5800/5000 at -3.5, so im sitting with an open position of San Antonio -3.5 -116 to win 5k.

    I now look at the market and only have a few minutes to close my middle.

    I can either take Cleve +6.5 -110, Cleve +5.5 +105

    What is a quick formula to determine how much cavs i need to take back at either price assuming I want to pay equal vig should the middle not hit.

    I have a homemade solver type formula that solves for this but it sometimes doesn't round properly.

    I think the us2hold formula will solve for this somehow.

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

    Let dopen = decimal odds on open bet
    Let dnew = decimal odds on new bet
    Let M = money wagered on open bet
    Let X = necessary money to wager on new bet in order to equalize losses

    M*(dopen-1) - X = X*(dnew-1) - M
    X = M * dopen / dnew

    So in the examples you gave:
    M = $5,800
    dopen

    new ML = -110
    dnew
    X ≈ $5,800 * 1.86207 / 1.90909 ≈ $5,657.15

    new ML = +105
    dnew =
    X ≈ $5,800 * 1.86207 / 2.05 ≈ $5,268.30

    That said, equalizing losses should not generally be the goal of the advantage middle player. Ostensibly, your first bet was +EV, while the second was at-market.

    As such, you'd likely want to risk less on the second wager than indicated above, leaving yourself some additional exposure to the first wager. The decision of exactly how much to wager on the second could best be answered by appealing to a staking strategy such as Kelly.

  9. #44
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    Excellent, thank you.

    Now lets say i wanted to solve for the payout ratios (assuming the middle hits) from the vig on each leg.

    Option 1 (taking +6.5 -110) pays +1544

    Option 2 (taking +5.5 +105) +3925

    How would I solve for those ratios given I know the vig on both ends?

    Lets assume I entered the following on my excel sheet.

    A1 -116
    A2 -110

    A1 -116
    A2 +105
    Last edited by Pancho sanza; 02-28-09 at 11:38 AM.

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

    If by payout ratio you mean the synthetic fractional odds on the middle then:

    To win = 2*dopen - 1 - dopen / dnew
    At risk = dopen - 1 - dopen / dnew

    So the win to risk ratio would be:
    dopen * dnew / ( dopen + dnew - dopen * dnew) - 1

    Or in your Excel example:
    =US2DEC(A1)*US2DEC(A2)/(US2DEC(A1)+US2DEC(A2)-US2DEC(A1)*US2DEC(A2))-1

    Generally speaking, computations are much easier when you maintain your odds in decimal format.

  11. #46
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    Excellent thanks.

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

    Quote Originally Posted by Ganchrow View Post
    That said, equalizing losses should not generally be the goal of the advantage middle player. Ostensibly, your first bet was +EV, while the second was at-market.

    As such, you'd likely want to risk less on the second wager than indicated above, leaving yourself some additional exposure to the first wager. The decision of exactly how much to wager on the second could best be answered by appealing to a staking strategy such as Kelly.
    ... and using the spreadsheet posted in this thread: http://www.sportsbookreview.com/forum/handicappe...readsheet.html

  13. #48
    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
    ... and using the spreadsheet posted in this thread: http://www.sportsbookreview.com/forum/handicappe...readsheet.html
    One might think I'd be more familiar with my own spreadsheets ...

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

    Added:

    • MB2US(US Matchbook Exchange Odds, Commission {default=1%})
    • MB2Dec(Decimal Matchbook Exchange Odds, Commission {default=1%})


    These calculate sportsbook equivalent US or Decimal odds given US or Decimal Matchbook exchange odds and commission. This references a commission structure where the player pays a set percentage of the lesser of risk or win irrespective of bet outcome. So =US2MB(-110, 1%) would refer to the sportsbook equivalent odds of betting at -110 given 1% Matchbook commission (~-112.12).

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

    Added for Version: 1.0.1.13:

    • SBRVer(): Displays current template version number.
    • {US2Fair(range of US Odds)} or {Dec2Fair(range of Decimal Odds)}: (array function) Returns an array of fair value zero-vig odds based on an Excel range of US or decimal odds. So if cells A1 and A2 are -200 and +176, respectively, and if B1 and B2 were set to the array formula {=US2Fair(A1:A2)}, B1 and B2 would display the values of -184 and +184, respectively.


    Full function list and download in first post of thread

  16. #51
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    One more for Ganchrow.

    Lets say I know the edge on a middle and the hit frequency, kelly says risk X on the middle, lets assume X = $1000 in this case.

    I then have to leg the position, risking $1000 total.

    These the lines on the game im middling:

    Spurs -4.5 -114
    Pacers +7.5 -121

    What is the formula to determine how much of each I grab so that my net risk on the middle is $1000, assume im taking enough of each to equalize the loss should the middle not hit.

  17. #52
    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 Pancho sanza View Post
    These the lines on the game im middling:

    Spurs -4.5 -114
    Pacers +7.5 -121

    What is the formula to determine how much of each I grab so that my net risk on the middle is $1000, assume im taking enough of each to equalize the loss should the middle not hit.
    Putting aside Kelly, if you were simply looking to equalize losses should your middle not hit, you'd wager as follows:

    Spurs -4.5 -114
    =10000*us2dec(-121)/(us2dec(-121)+us2dec(-114))
    = $4,931.49
    Pacers +7.5 -121
    =10000*us2dec(-114)/(us2dec(-121)+us2dec(-114))
    = $5,068.51

  18. #53
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    How come you used 10,000 Ganch?

    Doesn't quite equalize the losses:

    Spurs Risk/Win @-114 = 5621.9/4931.49
    Pacers Risk/Win @ -121 = 6132.89/5068.51

    Spurs win by 6 net profit = 10,000 (+4931.49 +5068.51)
    Spurs win by 20 net loss = -1201.4 (+4931.49 -6132.89)
    Pacers win by 20 net loss = -553.39 (+5068.51 -5621.9)

    OK, what did i do wrong here.

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

    Those are risk quantities not win quantities.

    Spurs Risk/Win @-114 = $4,931.49/$4,325.87
    Pacers Risk/Win @ -121 = $5,068.51/$4,188.85

    Spurs win by 6 net profit = +$8,514.72 ($4,325.87 + $4,188.85)
    Spurs win by 20 net loss = -$742.64 ($4,325.87 - $5,068.51)
    Pacers win by 20 net loss = -$742.64 ($4,188.85 - $4,931.49)

    But I misunderstood your question and thought you were looking to risk $10,000 total across both bets.

    If you're looking to win $10,000 should the middle hit then you'd wager as follows:

    Spurs -4.5 -114:
    =10000*us2dec(-121)/(2*us2dec(-121)*us2dec(-114) - us2dec(-121) - us2dec(-114))
    =$5,791.72 to win $5,080.46

    Pacers +7.5 -121:
    =10000*us2dec(-114)/(2*us2dec(-121)*us2dec(-114) - us2dec(-121) - us2dec(-114))
    =$5,952.64 to win $4,919.54

    This way you'd stand to win $5,080.46 + $4,919.54 = $10,000 should your middle hit and stand to lose -$5,791.72 + $4,919.54 = $5,080.46 - $5,952.64 = -$872.18 should your middle miss.

  20. #55
    Pancho sanza
    Pancho sanza's Avatar Become A Pro!
    Join Date: 10-18-07
    Posts: 386

    Got it, thanks again.

  21. #56
    Kostrhoun
    Kostrhoun's Avatar Become A Pro!
    Join Date: 06-15-09
    Posts: 64
    Betpoints: 721

    Hi there,

    would it be please possible to prepare excel formula for "evaluating" asian handicap bets? For me it is quite complicated, but it would be very useful for those of us, who bet soccer.

    Thank you!

    Tom

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

    Not sure exactly for what you're looking but do check out http://www.sportsbookreview.com/forum/handicappe...tml#post332398 and Asian Handicap Middles.

  23. #58
    1
    Update your status
    1's Avatar Become A Pro!
    Join Date: 07-05-09
    Posts: 30

    Hello Ganch - I have applied the VBA SBF to Excel 2007, works great. Thank you for sharing. Question - I am trying to point a cell with ProbUS2Edge to cells with existing values. I.E. =ProbUS2Edge(E38,I38). E38=.5754 I38=(-120) the result is .0549. If appling this to a cell that has been inserted the cell displays "0" otherwise works fine Can this be altered?

  24. #59
    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 1 View Post
    Hello Ganch - I have applied the VBA SBF to Excel 2007, works great. Thank you for sharing. Question - I am trying to point a cell with ProbUS2Edge to cells with existing values. I.E. =ProbUS2Edge(E38,I38). E38=.5754 I38=(-120) the result is .0549. If appling this to a cell that has been inserted the cell displays "0" otherwise works fine Can this be altered?
    I'm not actually following.

    Could you be a bit more specific?

    Have you tried hitting the F9 (Calc Now) key?

  25. #60
    1
    Update your status
    1's Avatar Become A Pro!
    Join Date: 07-05-09
    Posts: 30

    Yes. F9 = nothing
    The correct result (.0549) is shown in any cell the formula is entered except in a cell that has been posterior inserted to the worksheet. Formula bar shows =ProbUS2Edge(E38,I38) cell result displays "0"

  26. #61
    1
    Update your status
    1's Avatar Become A Pro!
    Join Date: 07-05-09
    Posts: 30

    Ganch - Another false alarm. Sorry! The cell width was small and the result was under the adjacent cell except for the zero before the decimal. I guess I will have some penance?

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

    Awesome! The only question I had was on the formula used to calculate matchbook odds as I've been having difficulty figuring that out. Thanks a lot!
    Last edited by roasthawg; 08-05-09 at 12:38 AM.

  28. #63
    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 roasthawg View Post
    Awesome! The only question I had was on the formula used to calculate matchbook odds as I've been having difficulty figuring that out. Thanks a lot!
    VBA function for converting gross US Matchbook odds to net US Odds (i.e., odds after commission):
    Code:
    Public Function MB2US(ByVal dUSOdds As Double, Optional ByVal dCommission = 0.01) As Double
        Application.Volatile
        If dUSOdds < 0 Then
            MB2US = Dec2US( _
                        1 + (1 - dCommission) / _
                            (-dUSOdds / 100 + dCommission) _
                    )
        Else
            MB2US = Dec2US( _
                        1 + (dUSOdds / 100 - dCommission) / _
                            (1 + dCommission) _
                    )
        End If
    End Function

  29. #64
    hasnat
    hasnat's Avatar Become A Pro!
    Join Date: 08-25-09
    Posts: 1

    i dont need most of them but some are very handy to me
    thanks

  30. #65
    SteveAvery33
    SteveAvery33's Avatar Become A Pro!
    Join Date: 05-18-08
    Posts: 42
    Betpoints: 24

    First off, I want to say that these excel commands are excellent and save me a bunch of time. However, I'm running into a problem with the SBKelly() function. Any time I use it in my spreadsheet, it instantly makes Solver run exponentially slower. What confuses me is that my solver set up is on a different sheet. The SBKelly() function isn't even using the numbers generated by Solver. But if I use the SBKelly() function over just 60 rows, it triples the time it takes me to open and run Solver. Any ideas?

    *As it turns out, Excel is slowing down any time I use some other functions from your template. Just adding one column of simple us2dec() calcs is making Solver crawl.*
    Last edited by SteveAvery33; 10-20-09 at 08:45 PM.

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

    Don't expect any help.

  32. #67
    SteveAvery33
    SteveAvery33's Avatar Become A Pro!
    Join Date: 05-18-08
    Posts: 42
    Betpoints: 24

    Quote Originally Posted by RickySteve View Post
    Don't expect any help.
    Care to elaborate on that? Is there a reason I shouldn't expect any help?

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

    He doesn't post here any more.

  34. #69
    VBOMBER
    VBOMBER's Avatar Become A Pro!
    Join Date: 01-02-08
    Posts: 228

    Quote Originally Posted by RickySteve View Post
    He doesn't post here any more.
    Did Ganchrow = pags11????

    No, in all seriousness, what happened to him? I thought he went to CR to help out SBR?

  35. #70
    shantystar
    shantystar's Avatar Become A Pro!
    Join Date: 11-13-05
    Posts: 7,299
    Betpoints: 117

    Quote Originally Posted by Ganchrow View Post
    VBA function for converting gross US Matchbook odds to net US Odds (i.e., odds after commission):
    Code:
    Public Function MB2US(ByVal dUSOdds As Double, Optional ByVal dCommission = 0.01) As Double
        Application.Volatile
        If dUSOdds < 0 Then
            MB2US = Dec2US( _
                        1 + (1 - dCommission) / _
                            (-dUSOdds / 100 + dCommission) _
                    )
        Else
            MB2US = Dec2US( _
                        1 + (dUSOdds / 100 - dCommission) / _
                            (1 + dCommission) _
                    )
        End If
    End Function
    right say!
    Points Awarded:

    Matt Rain gave shantystar 5 SBR Point(s) for this post.


First 1234 Last
Top