Simple VBA Sports Betting Functions Template for Excel

[Ganchrow]

Latest Version: 1.0.2.1

Download here

If you're like me, you frequently reuse many of the same excel sports betting formulas. I've compiled a few related to odds manipulation into this Excel template file as VBA functions. Eventually, I plan to greatly expand on these.

Here are the included functions:

1. SBRVer(): Displays current template version number.
2. US2Dec(USOdds) (usage 1): Converts US-style to decimal. Example: US2Dec(-110) ≈ 1.909090909
3. US2Dec(range of USOdds) (usage 2): Converts an array or Excel range of US-style odds to decimal parlay odds. Example: US2Dec(-110,-110) ≈ 3.644628099
4. US2Par(range of USOdds): Converts an array or Excel range of US-style odds to US-style true parlay odds. Example: US2Par(-110,-110,-110) ≈ +595.7926.
5. Dec2US(DecimalOdds): Converts decimal odds to US. Example: US2Dec(1.909090909) = -110.
6. US2Win(USOdds, WagerQuantity {{default = 1}) or Dec2Win(DecOdds, WagerQuantity {{default = 1}): Converts US or Decimal odds and wager size to potential win quantity. Note that by using the default value of 1 for wager size, these two functions effectively convert from US/decimal odds to fractional odds. Example, US2Win(-120,120) = 100, or US2Win(-110) ≈ 0.90909.
7. US2Res(USOdds, WagerQuantity {{default = 1}, Result) or Dec2Win(DecOdds, WagerQuantity {{default = 1}, Result): Converts US or Decimal odds, wager size, and result (where "WIN", "W", or "1", corresponds to a win; "LOSS", "L", or -1 corresponds to a loss; and "PUSH", "P", or 0 corresponds to a push). Example, US2Res(-120,120,"P") = 0, or =US2Res(-110, 200,"Win") ≈ 181.82.
8. US2Prob(USOdds) or Dec2Prob(DecimalOdds): Converts from US or decimal odds to probability. Example: US2Prob(+100) = Dec2Prob(2.0000) = 50%.
9. US2Hold(range of US Odds) or Dec2Hold(range of Decimal Odds): Calculates theoretical hold based on an Excel range of US or decimal odds. Example: if cells A1 and A2 are both -110, US2Hold(A1:A2) = 4.54545%.
10. {US2Real(range of US Odds)} or {Dec2Real(range of Decimal Odds)}: (array function) Returns an array of zero-vig probabilities based on an Excel range of US or decimal odds. Example: if cells A1 and A2 are both -110, if B1, B2, and B3 were set to the array formula {=US2Real(A1:A2)}, B1 and B2 would both have the value of 50%, and B3 would have the value of the theoretical hold (4.54545%).
11. {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. Example: 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.
12. ProbUS2Edge(Probability, USOdds) or ProbDec2Edge(Probability, DecimalOdds): Calculates edge based on win probability and US or decimal odds. Example: ProbUS2Edge(55%,-110) = 5%
13. EdgeUS2Prob(Edge, USOdds) or EdgeDec2Prob(Edge, DecimalOdds): Calculates win probability based on edge and US or decimal odds. Example: ProbUS2Edge(5%,-110) = 55%.
14. ProbEdge2US(Probability, Edge) or ProbEdge2Dec(Probability, Edge): Calculates US or decimal odds based on probability and edge. Example: ProbEdge2US(55%,5%) = -110.
15. USRisk2Win(USOdds, RiskQuantity {default=1}) or DecRisk2Win(DecimalOdds, RiskQuantity {default=1}): Calculates resultant win quantities given US/Decimal odds and risk quantity. (These functions are also aliased as USR2W(·) and DecR2W(·), respectively). Example: USRisk2Win(-110,22) = USR2W(-110,22) = $20.
16. USWin2Risk(USOdds, WinQuantity{default=1}) or DecWin2Risk(DecimalOdds, WinQuantity {default=1}): Calculates required risk given US/Decimal odds and desired risk amount. (These functions are also aliased as USW2R(·) and DecW2R(·), respectively). Example: USWin2Risk(-110,20) = USW2R(-110,20) = $22.
17. Exch2US(US Exchange Odds, Commission {default = 2%}) or Exch2Dec(Decimal Exchange Odds, Commission {default = 2%}): Calculate sportsbook equivalent US or decimal odds given US or decimal exchange odds and commission. Example: Exch2US(-110, 1%) would refer to the sportsbook equivalent odds of betting at -110 given 1% sportsbook commission (~-111.11).
18. E2S(US exchange odds, exchange commission {default = 2%}): Shortcut to Exch2US(US Exchange Odds, Commission).
19. ExchUS2Hold(range of US Odds, Commission) or ExchDec2Hold(range of Decimal Odds, Commission): Calculates theoretical hold including sports betting exchange commissions based on an Excel range of US or decimal odds. Example: if the values of cells A1 and A2 both equal -102 ExchUS2Holds(A1:A2,2%) would equal the theoretical hold theoretical on the -102/-012 market inclusive of 2% exchange commission (a value of 1.961%).
20. KUtil(bankroll, Kelly multiplier {default = 1}): Calculates Kelly criterion utility for a given bankroll (expressed in percent terms) and Kelly multiplier. Example: KUtil(1.05, 0.5) would yield half-Kelly utility for a bankroll of 105% of initial.
21. InvKUtil(utilily, Kelly multiplier {default = 1}): The inverse Kelly Utility function. Calculates the bankroll (expressed in percent terms) implied by a given Kelly criterion utility and Kelly multiplier. Example: InvKutil(KUtil(X, KellyMult),KellyMult) would just equal X (provided X > 0).
22. SBKelly(Probability, Odds, Kelly Multiplier {default = 1}, Decimal Odds Flag {default = FALSE}): Calculates single bet Kelly stake given an expected win probability, paypout odds, and optional Kelly Multiplier. If the "Decimal Odds Flag" isn't set or is set to FALSE, then the function will use a "best guess" as to whether the odds specified are US or decimal-style (if absolute value < 100, it will assume decimal). Setting the flag to TRUE will cause the function to always assume decimal-style odds (this could be helpful when using decimal-style odds at very high payout levels).
23. {P2L(range of win probabilities)}: (array function) Returns an array of likelihoods such that the i^th element of the output array (i ∈ [0, 1, 2, ..., n], where n = the number of probabilities in the input range) corresponds to to the likelihood of exactly n-i wins and i losses given the n event win probabilities in the input range. Example: if cells (A1, A2, A3) = (75%, 70%, 65%), and cells B1, B2, B3, and B4 were set to the array formula {=P2L(A1:A3)}, cell B1 would correspond to the probability of 3 wins and 0 losses (~ 34.13%), cell B the probability of 2 wins and 1 loss (~44.38%), B3 the probability of 1 win and 2 losses (~18.88%), and B4 the probability of 0 wins and 3 losses (2.63%). Note that this function may be rather slow to calculate for large input sets.
24. {EnumCombin(range of items, size)}: (array function) Returns a 2-D array of every possible combination of of the specified size of the input range. Example: if cells (A1, A2, A3, A4, A5) = ("A", "B", "C", "D", "E"), then {=ENUMCOMBIN(A1:A5, 2)} would return the 6-row, 2-column array of {("A","B"),("A","C"),("A","D"),("B","C") ,("B","D"),("C","D")}. Note that this function may be rather slow to calculate for large input sets. EnumComin is short for "Enumerate Combinations".
25. lg(p): Calculates the logit function of probability p, where lg(p) is defined as ln(p) - ln(1-p) ∀ 0 < p < 1.
26. invlg(x): Calculates the inverse logit function of x where invlg(x) is defined as invlg(x) = Exp(x) / (1 + Exp(x)).
27. MB2US(US Matchbook Exchange Odds, Commission {default=1%}) or MB2DEC(Decimal Matchbook Exchange Odds, Commission {default=1%}): Calculate sportsbook equivalent US or Decimal odds given US or Decimal Matckbook 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. Example: MB2US(-110, 1%) would refer to the sportsbook equivalent odds of betting at -110 given 1% Matchbook commission (~-112.12).
28. {Bets2Stats(range of Odds, range of Wager Quantities {default=1}, range of Outcomes, range of Edges {default=0%), Decimal Odds Flag = {default = FALSE})}: Array function. Takes a range of betting odds (US odds are the default, but will take decimal odds if the Decimal Odds Flag argument) is set to TRUE, an optional range of wager quantities (if not provided then 1 unit per wager is assumed), a range of outcomes (1 or a string starting with 'W' for a win, -1 or a string starting with 'L' for a loss, anything else for a push/no action), and a range of expected edges (defaults to 0). Returns an array with the following values:
    1. Number of Non-Pushed Bets
    2. Number of Wins
    3. Win %
    4. Unit Return
    5. % Return
    6. Unit Standard Deviation
    7. % Standard Deviation
    8. Standard Score
    9. p-value (from t-distribution)
    
    

If you want these functions to be available every time you start Excel you'll need to save the Book.xlt template file in your Excel XLStart directory ("C:\Program Files\Microsoft Office\OFFICE11\XLSTART" by default for Excel 2003 -- \Office12\XLSTART\ for Excel2007, \Office10\XLSTART\ for Excel 2002, and \Office\XLSTART\ for Excel 2000 and 97). If the file already exists you shouldn't overwrite it unless you know the preexisting file to be empty but instead choose a different name as in the next paragraph. Alternatively, if you know what you're doing, you could manually add the Excel VBA functions or module to your preexisting Book.xlt file.

If you want these functions to only be available only by request then save the file under a different name in the XLStart directory. For example, if you saved the file as Ganchrow.xlt, then by clicking "New" on the "File" menu, you'd be able to select the template "Ganchrow" and have all the above functions available.

[Ortho]

{US2Real(range of US Odds)} or {Dec2Real(range of Decimal Odds)}: (array function) Returns an array of zero-vig probabilities based on an Excel range of US or decimal odds. So if cells A1 and A2 are both -110, if B1, B2, and B3 were set to the array formula {=US2Real(A1:A2)}, B1 and B2 would both have the value of 50%, and B3 would have the value of the theoretical hold (4.54545%).

This one doesn't work for me. If A1 and A2 are both -110, =us2real(a1:a2) gives me .52381 in each of b1 and b2, and b3 gives -0.00527. Am I using it correctly?

Thanks for this, btw. It is extremely helpful in cleaning up my ghetto spreadsheet with calculation cells everywhere. This is very helpful!

[Ganchrow]

This one doesn't work for me. If A1 and A2 are both -110, =us2real(a1:a2) gives me .52381 in each of b1 and b2, and b3 gives -0.00527. Am I using it correctly?

Yeah that was my bad (although I'm not sure why you'd see the -0.00527. Just remember to enter as an array formula -- using ALT-ENTER).

I've fixed this it in the latest version, which you can re-download from the same location.

[Ganchrow]

Added two sets of functions to calculate sportsbook-equivalent lines and theoretical hold inclusive of sports exchange commissions:

Exch2US(US Odds, Commission {default = 2%}) or Exch2Dec(Decimal Odds, Commission {default = 2%}): Calculates sportsbook equivalent lines given either US or decimal odds and sports exchange commission.

ExchUS2Hold(range of US Odds, Commission {default = 2%}) or ExchDec2Hold(range of Decimal Odds, Commission {default = 2%}): Calculates theoretical hold including sports betting exchange commissions based on an Excel range of US or decimal odds. So if the values of cells A1 and A2 both equal -102 ExchUS2Holds(A1:A2,2%) would equal the theoretical hold theoretical on the -102/-012 market inclusive of 2% exchange commission (a value of 1.961%).

Download Link to Book.xlt download post here

[jjgold]

Nice work

[Utah]

If you want these functions to only be available only by request then save the file under a different name in the XLStart directory. For example, if you saved the file as Ganchrow.xlt, then by clicking "New" on the "File" menu, you'd be able to select the template "Ganchrow" and have all the above functions available.

Download Book.xlt here

Cool. I just downloaded it. I see that it has the solver reference. When I wrote a couple last week I couldnt get them to work with the solver until I found a note that the solver reference needs to be set. It was a major pain figuring it out since I am not a programmer.

[Ganchrow]

Cool. I just downloaded it. I see that it has the solver reference. When I wrote a couple last week I couldnt get them to work with the solver until I found a note that the solver reference needs to be set. It was a major pain figuring it out since I am not a programmer.

Good catch. This template doesn't actually use solver at all, so the reference is probably just an artifact from some old spreadsheet of mine.

Feel free to de-select the reference if you like.

EDIT: I'm not seeing a VBA reference to Solver when I load up a blank spreadsheet. Are you possibly referring to the Excel Add-in? (The presence or absence of Excel Add-in are set by the user for the application and aren't saved with Excel workbooks or templates.)

[Ganchrow]

New function added:

{P2L(range of win probabilities)}: (array function) Returns an array of likelihoods such that the i^th element of the output array (i &isin; [0, 1, 2, ..., n], where n = the number of probabilities in the input range) corresponds to to the likelihood of exactly n-i wins and i losses given the n event win probabilities in the input range. So if cells (A1, A2, A3) = (75%, 70%, 65%), and cells B1, B2, B3, and B4 were set to the array formula {=P2L(A1:A3)}, cell B1 would correspond to the probability of 3 wins and 0 losses (~ 34.13%), cell B the probability of 2 wins and 1 loss (~44.38%), B3 the probability of 1 win and 2 losses (~18.88%), and B4 the probability of 0 wins and 3 losses (2.63%). Note that this function may be rather slow to calculate for large input sets.

This function may be useful for (among other things) estimating season win total likelihoods, and in value-at-risk calculation.

See the first post in in this thread for complete function list and download link.

[jjgold]

Excellent work Ganch

[Ganchrow]

If anyone cares to redownload I added a few more minor functions.

• US2Par(range of USOdds): Converts an array or Excel range of US-style odds to US parlay odds. So US2Par(-110,-110,-110) ≈ +595.7926. (Previously this HAD needed to be done in two steps =DEC2US(US2DEC(-110,-110,-110)).)
• {EnumCombin(range of items, size)}: (array function) Returns a 2-D array of every possible combination of of the specified size of the input range. So if cells (A1, A2, A3, A4, A5) = ("A", "B", "C", "D", "E"), then {=ENUMCOMBIN(A1:A5, 2)} would return the 6 row column array of {("A","B"),("A","C"),("A","D"),("B","C") ,("B","D"),("C","D")}. Note that this function may be rather slow to calculate for large input sets. EnumComin is short for "Enumerate Combinations". (I've found this function to be useful in conjunction with table lookups can be quite useful in calculating complex parlay exposures under various scenarios).
• lg(p): Calculates the logit function of probability p, where lg(p) is defined as ln(p) - ln(1-p) ∀ 0 < p < 1. (Often useful for simplifying the expression of Bayes's Theorem such as with log5.)
• invlg(x): Calculates the inverse logit function of x where invlg(x) is defined as invlg(x) = Exp(x) / (1 + Exp(x)).

Full function list and download in first post of thread

[dwaechte]

First time I've seen this Ganch, excellent stuff.

[bbyhill]

The template would not download for me

[Ganchrow]

The template would not download for me

Could you be more specific as to what actually occurred when you attempted your download?

[bbyhill]

Click on book.xlt and when excel comes up it says Macros has been disabled.It is just a blank sheet

[bbyhill]

Click on book.xlt and when excel comes up it says Macros has been disabled.It is just a blank sheet

nothing in the cells

[Ganchrow]

Because it's not an Excel spreadsheet (an .xls file), but rather an Excel template (an .xlt file). You don't open the file you save it as described above in the initial post after the function description. The functions are then available each time you start Excel.

If it's not allowing you to enable Macros (in other words it's not giving you the option when loading a file) you need to reduce your Macro Security to Medium.

You'd do this by going to Tools => Options => Security => Macro Security and select "Medium". This should generally be set by default.

[bbyhill]

it gives me the option to enable the content and still wont work

using Firefox and Office 2007

[Ganchrow]

it gives me the option to enable the content and still wont work

using Firefox and Office 2007

Firefox has nothing to do with it.

What do you mean when you say "it won't work"? What happens when you open a new spreadsheet, enable Macros, and attempt to use one of the functions?

I assume you're saving it in the correct directory as described above? (That'd be "C:\Program Files\Microsoft Office\OFFICE12\XLSTART\" for Office 2007).

[cap]

I have Exel 2007 it doesn work too

[bbyhill]

Saving it in the correct place.

[Ganchrow]

I have Exel 2007 it doesn work too

You'll need to be more specific. Just saying "it doesn't work" doesn't really give me much to go on, I'm afraid.

In what manner doesn't it work? What happens when you attempt to use in the manner prescribed in the first post of this thread? Does it give you an error message or a cell return value of any sort? Does your computer explode?

[Ganchrow]

Saving it in the correct place.

Ok ... and then what happens when you open a new spreadsheet, enable Macros, and attempt to use one of the functions?

[bbyhill]

Enabled Macros it shows a blank sheet.

[Ganchrow]

Enabled Macros it shows a blank sheet.

I don't think you're understanding me. The cells in that file will always be blank.

That's an Excel template file and not a worksheet. The file isn't intended to ever be opened by users.

Instead the template file contains new Excel functions (detailed in the first post of this thread) related to sports betting. Once you've saved the file in the proper location you'll have all these functions at your disposal every time you open Excel.

So try this: Close Excel completely (don't save any changes you've made to Book.xlt) and then reopen a blank worksheet.

Type the following in cell A1:

=us2prob(-110)

Hit enter. If the result is 0.52381 then you know the function is working properly. In this context, 0.52381 of course represents how frequently one would need to win when betting at odds of -110 in order to be a breakeven bettor.

[bbyhill]

Works good.Just thought there would be something in the cells.

[Ganchrow]

Works good.

Glad to hear it.

Just make sure not to make any changes to the Book.xlt file (or even open it in the first place) unless you absolutely positively know what you're doing.

[bbyhill]

Thanks a lot Ganchrow.I am new to excel,learning to build some things and trackers.The new tools will be much appreciated.

[icelancer]

I'm having problems with it - I have Excel 2008 (OFFICE12) and saved the file in XLSTART as well as the OFFICE10/XLSTART directory. Neither existing spreadsheets nor new ones work with the macros using your example. Any idea what the problem might be, or where I can see if it's enabled? (Macros are enabled.)

[pico]

nice work. i wonder if you trade forex.

[Ganchrow]

I'm having problems with it - I have Excel 2008 (OFFICE12) and saved the file in XLSTART as well as the OFFICE10/XLSTART directory. Neither existing spreadsheets nor new ones work with the macros using your example. Any idea what the problem might be, or where I can see if it's enabled? (Macros are enabled.)

I assume you mean Excel 2007.

From http://support.microsoft.com/kb/822107:

Folders that Excel uses at startup
If you install Excel in the default location, Excel opens files from the following paths:

In Microsoft Office Excel 2007, the path is C:\Program Files\Microsoft Office\Office12\Xlstart
C:\Documents and Settings\User_name\Application Data\Microsoft\Excel\XLSTART

In this path, User_name is your logon user name.
The folder that is specified in the At startup, open all files in box

Note: To find the At startup, open all files in box in Excel 2007, click the Microsoft Office Button, click Excel Options, and then click Advanced. The At startup, open all files in box is under General.

And then place the Book.xlt file in the above folder as appropriate.

To see if it's working open up a new workbook (allowing Macros) and in an empty cell type:

=us2dec(-110)

If the return value is something like 1.909090909, then you're in good shape.

[sprasad03]

Thanks Ganchrow, I see you a big help to alot of ppl. I was just a regular bettor that had great success just picking teams right a yr ago, looking at trends, etc. but not as into it as in mathematical analysis. So i thought if i can get all sides factored into it, I will be better off, because this year i happened to pick a lot of right teams but the one team i would get wrong would usually mess up my parlays and i ended up in a loss for this NFL. So i am trying to get more math orientated throughout sports betting.

I know this might take you a few mins but hopefully you will. I understand the calculations but I dont understand the meaning or significance. I really dont understand how 6-16 are significant and what the final number stand for. If you could help me, it would be really appreciated.

[fiveteamer]

I downloaded this, now what?

I don't understand.

[icelancer]

I assume you mean Excel 2007.

From http://support.microsoft.com/kb/822107:


And then place the Book.xlt file in the above folder as appropriate.

To see if it's working open up a new workbook (allowing Macros) and in an empty cell type:

=us2dec(-110)

If the return value is something like 1.909090909, then you're in good shape.

Ganch,

I think I love you.

[solobass]

tools from the gods, thank you. my spreadsheets are so cluttered and damn near unwieldy at times thanks!

[dday0707]

1. {EnumCombin(range of items, size)}: (array function) Returns a 2-D array of every possible combination of of the specified size of the input range. So if cells (A1, A2, A3, A4, A5) = ("A", "B", "C", "D", "E"), then {=ENUMCOMBIN(A1:A5, 2)} would return the 6-row, 2-column array of {("A","B"),("A","C"),("A","D"),("B","C") ,("B","D"),("C","D")}. Note that this function may be rather slow to calculate for large input sets. EnumComin is short for "Enumerate Combinations".

Hi,
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.

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.
Thanks in advance