[Ganchrow]

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. US2Dec(USOdds) (usage 1): Converts US-style to decimal. So US2Dec(-110) = 1.909090909
2. US2Dec(range of USOdds) (usage 2): Converts an array or Excel range of US-style odds to decimal parlay odds. So US2Dec(-110,-110) = 3.644628099
3. Dec2US(DecimalOdds): Converts decimal odds to US. So US2Dec(1.909090909) = -110.
4. US2Prob(USOdds) or Dec2Prob(DecimalOdds): Converts from US or decimal odds to probability. So US2Prob(+100) = Dec2Prob(2.0000) = 50%.
5. US2Hold(range of US Odds) or Dec2Hold(range of Decimal Odds): Calculates theoretical hold based on an Excel range of US or decimal odds. So if cells A1 and A2 are both -110, US2Hold(A1:A2) = 4.54545%.
6. {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%).
7. ProbUS2Edge(Probability, USOdds) or ProbDec2Edge(Probability, DecimalOdds): Calculates edge based on win probability and US or decimal odds. So ProbUS2Edge(55%,-110) = 5%
8. EdgeUS2Prob(Edge, USOdds) or EdgeDecimal2Prob(Edge, DecimalOdds): Calculates win probability based on edge and US or decimal odds. So ProbUS2Edge(5%,-110) = 55%.
9. ProbEdge2US(Probability, Edge) or ProbEdge2Dec(Probability, Edge): Calculates US or decimal odds based on probability and edge. So ProbEdge2US(55%,5%) = -110.
10. US2Exch(US Exchange Odds, Commission) or Dec2Exch(Decimal Exchange Odds, Commission): Calculate sportsbook equivalent US or decimal odds given US or decimal exchange odds and commission. So =US2Exch(-110, 1%) would refer to the sportsbook equivalent odds of betting at -110 given 1% sportsbook commission (~111.11).
11. E2S(US exchange odds, exchange commission {default = 1%}):: Shortcut to US2Exch(US Exchange Odds, Commission).
12. 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. 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%).
13. KUtil(bankroll, Kelly multiplier {default = 1}): Calculates Kelly criterion utility for a given bankroll (expressed in percent terms) and Kelly multiplier. So =KUtil(1.05, 0.5) would yield half-Kelly utility for a bankroll of 105% of initial.
14. 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. So =InvKutil(KUtil(X, KellyMult),KellyMult) would just equal X (provided X > 0).
15. {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. 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.

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). (If the file already exists you shouldn't overwrite 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.

Download Book.xlt here

[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]

Originally Posted by Ortho

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:

US2Exch(US Odds, Commission) or Dec2Exch(Decimal Odds, Commission): Calculates sportsbook equivalent lines given either US or decimal odds and sports exchange commission.

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

Originally Posted by Utah

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 ∈ [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