Simple VBA Sports Betting Functions Template for Excel

[Ganchrow]

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?

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?

[dday0707]

Ok. Find attached an excel sheet withan example.

THX

[Ganchrow]

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.

[dday0707]

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

[Pancho sanza]

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.

[Ganchrow]

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.

[Pancho sanza]

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.

[Ganchrow]

Let d_open = decimal odds on open bet
Let d_new = 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*(d_open-1) - X = X*(d_new-1) - M
X = M * d_open / d_new

So in the examples you gave:

M = $5,800
d_open ≈

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

new ML = +105
d_new =
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.

[Pancho sanza]

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

[Ganchrow]

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

To win = 2*d_open - 1 - d_open / d_new
At risk = d_open - 1 - d_open / d_new

So the win to risk ratio would be:
d_open * d_new / ( d_open + d_new - d_open * d_new) - 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.

[Pancho sanza]

Excellent thanks.

[Data]

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://forum.sbrforum.com/handicappe...readsheet.html

[Ganchrow]

... and using the spreadsheet posted in this thread: http://forum.sbrforum.com/handicappe...readsheet.html

One might think I'd be more familiar with my own spreadsheets ...

[Ganchrow]

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).

[Ganchrow]

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

[Pancho sanza]

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.

[Ganchrow]

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

[Pancho sanza]

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.

[Ganchrow]

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.

[Pancho sanza]

Got it, thanks again.

[Kostrhoun]

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

[Ganchrow]

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

[1]

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?

[Ganchrow]

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?

[1]

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"

[1]

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?

[roasthawg]

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!

[Ganchrow]

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):

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

[hasnat]

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

[SteveAvery33]

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.*

[RickySteve]

Don't expect any help.

[SteveAvery33]

Don't expect any help.

Care to elaborate on that? Is there a reason I shouldn't expect any help?

[RickySteve]

He doesn't post here any more.

[VBOMBER]

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?

[shantystar]

VBA function for converting gross US Matchbook odds to net US Odds (i.e., odds after commission):

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!