[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. US2Par(range of USOdds): Converts an array or Excel range of US-style odds to US-style true parlay odds. So US2Par(-110,-110,-110) ≈ +595.7926.
4. Dec2US(DecimalOdds): Converts decimal odds to US. So US2Dec(1.909090909) = -110.
5. US2Prob(USOdds) or Dec2Prob(DecimalOdds): Converts from US or decimal odds to probability. So US2Prob(+100) = Dec2Prob(2.0000) = 50%.
6. 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%.
7. {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%).
8. ProbUS2Edge(Probability, USOdds) or ProbDec2Edge(Probability, DecimalOdds): Calculates edge based on win probability and US or decimal odds. So ProbUS2Edge(55%,-110) = 5%
9. EdgeUS2Prob(Edge, USOdds) or EdgeDecimal2Prob(Edge, DecimalOdds): Calculates win probability based on edge and US or decimal odds. So ProbUS2Edge(5%,-110) = 55%.
10. ProbEdge2US(Probability, Edge) or ProbEdge2Dec(Probability, Edge): Calculates US or decimal odds based on probability and edge. So ProbEdge2US(55%,5%) = -110.
11. 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).
12. E2S(US exchange odds, exchange commission {default = 1%}):: Shortcut to US2Exch(US Exchange Odds, Commission).
13. 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%).
14. 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.
15. 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).
16. {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.
17. {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".
18. lg(p): Calculates the logit function of probability p, where lg(p) is defined as ln(p) - ln(1-p) ∀ 0 < p < 1.
19. invlg(x): Calculates the inverse logit function of x where invlg(x) is defined as invlg(x) = Exp(x) / (1 + Exp(x)).

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.