[Ganchrow]

I haven't updated this in a while but here's the generic Kelly spreadsheet I first wrote 3 or 4 years back.

Currently I do all my serious optimization outside of Excel, but this works decently well for small problems with boundary conditions. It does NOT handle correlation. It does of course necessitate Excel Solver (included with Excel by default).

To get it going click "Read Odds" each time you make any changes to any odds, probabilities, edges, or max or min parlay sizes and then click "Solve". It could take a while for larger problems. There's a "Clear Stakes?" dropdown box beneath these two buttons. Setting the value to TRUE will clear stakes from previous optimizations (set to zero) when clicking either "Read Odds" or "Solve".

Should this be done? If you're running a new optimization then you almost certainly should. But if the optimization you're running is very similar to the previous one, the previous results may be provide the optimizer "clues" on starting values and speed up the process. Occasionally, however, this may work against you as these clues may fallaciously convince the optimizer it's seeing convergence when in reality it isn't. So if you don't trust some results, try setting "Clear Stakes?" to TRUE (but in the case of a large optimization you might have to be prepared for it to take longer).

The "Use Edge" dropdown doesn't really do much of anything other than specify when changing the Odds whether this will effect the probability (Use Edge=TRUE) or the edge (Use Edge=FALSE). Also if the edge and probability ever get out of sync Use Edge will tell the optimizer whether to trust the Edge number (TRUE) or the probability number (FALSE). In theory this should never happen.

On the top right you can specify fixed parlay odds for parlays of a given size. This can be used, for example, with parlay cards or with teasers. When using this option, specified odds are ignored when looking at parlays of the given size.

The spreadsheet takes decimal-style odds. To convert from US to decimal odds you can just use the US2DEC() function (included). So for example in cell B3 if you wanted to specify odds of +250 you could either enter 3.5 or =US2DEC(250).

This VBA should make the spreadsheet faster than most pure Excel solutions because it limits the calculations within the spreadsheet to close to the bare minimum necessary.

Let me know if you find any bugs or have any suggestions, but that said, I don't plan to be heavily supporting this.

[Ganchrow]

If anyone felt like a upgrading this the first thing I might suggest would be to add native support for varying acquisition prices (although it would take a lot of additional work to include "take backs" as in my old lines change spreadsheet -- this would probably be outside the reasonable scope of an Excel front end).

[dwaechte]

Thanks Ganch!

[frozen]

When you say 'varying aqcquisition prices' are you talking about buying a position at say -5, and another position on the same game when at -4.5?

Thanks a lot, I had started building an excel calculator doing multiple kelly calcs (so that I wouldn't always have to reference your online one), but it gets quite complicated. This should help plenty

[Ganchrow]

When you say 'varying aqcquisition prices' are you talking about buying a position at say -5, and another position on the same game when at -4.5?

I was referring to varying payout odds, which is a much simpler task than designing for correlated (< 100%) bets.

I described a simple algebraic methodology for handling this in this post.

[Panic]

Very cool, Ganch.

[Ganchrow]

"Clear Stakes" functionality added -- a very minor addition. A new spreadsheet has been uploaded.

See the first post in thread.

[bigbrown]

I haven't updated this in a while but here's the generic Kelly spreadsheet I first wrote 3 or 4 years back.

Currently I do all my serious optimization outside of Excel, but this works decently well for small problems with boundary conditions. It does NOT handle correlation. It does of course necessitate Excel Solver (included with Excel by default).

To get it going click "Read Odds" each time you make any changes to any odds, probabilities, edges, or max or min parlay sizes and then click "Solve". It could take a while for larger problems. There's a "Clear Stakes?" dropdown box beneath these two buttons. Setting the value to TRUE will clear stakes from previous optimizations (set to zero) when clicking either "Read Odds" or "Solve".

Should this be done? If you're running a new optimization then you almost certainly should. But if the optimization you're running is very similar to the previous one, the previous results may be provide the optimizer "clues" on starting values and speed up the process. Occasionally, however, this may work against you as these clues may fallaciously convince the optimizer it's seeing convergence when in reality it isn't. So if you don't trust some results, try setting "Clear Stakes?" to TRUE (but in the case of a large optimization you might have to be prepared for it to take longer).

The "Use Edge" dropdown doesn't really do much of anything other than specify when changing the Odds whether this will effect the probability (Use Edge=TRUE) or the edge (Use Edge=FALSE). Also if the edge and probability ever get out of sync Use Edge will tell the optimizer whether to trust the Edge number (TRUE) or the probability number (FALSE). In theory this should never happen.

On the top right you can specify fixed parlay odds for parlays of a given size. This can be used, for example, with parlay cards or with teasers. When using this option, specified odds are ignored when looking at parlays of the given size.

The spreadsheet takes decimal-style odds. To convert from US to decimal odds you can just use the US2DEC() function (included). So for example in cell B3 if you wanted to specify odds of +250 you could either enter 3.5 or =US2DEC(250).

This VBA should make the spreadsheet faster than most pure Excel solutions because it limits the calculations within the spreadsheet to close to the bare minimum necessary.

Let me know if you find any bugs or have any suggestions, but that said, I don't plan to be heavily supporting this.

Ganchrow: thank you for the very useful postings regarding kelly formula.

I have the following question: how do you optimize kelly when your bet affects the odds. one such example is horseracing when the pools are finite and betting with kelly formula changes the odds.

i would appreciate any reference to a math paper or any math formulas/guidance that can help me here.

you look like a knowledgeable expert and i hope that you can help here.

thank you

[Ganchrow]

how do you optimize kelly when your bet affects the odds.

...

i would appreciate any reference to a math paper or any math formulas/guidance that can help me here.

Given N possible outcome states, each occurring with probability p_i, where i ∈ (1 ... N), M possible bets, each of size x_j, where j ∈ (1 ... M), then if bet x_j pays out in state i at rate of r_i,j(x) (where x refers to the M-element vector of bet sizes), then for a bettor with n-Kelly utility of U(), E(U) would be:

[nbtable][tr][td]E(U) = [/td][td][/td] [td]( p_i * U(1+[/td] [td][/td] [td] x_j*r_i,j(x) ) ) [/td] [/tr] [/nbtable]

Basically, all we're doing is making explicit the functional relationship between payout odds and quantity wagered across bets. This is done via r_i,j(x). Note that if payout odds didn't vary with amount wagered, then r_i,j would just be a constant (as in the JavaScript Kelly Calculator or the spreadsheet).

[bigbrown]

Ganchrow,

thank you very much for your quick reply. do you, by any chance have any pointer to a paper that addresses this in the context of horse racing? or in any context, but addressing this specific problem?

I assume that the solution will be to somehow maximize (convex optimiztion?? ) the utility function with respect to a multi-dimentional vector of bets?

[Ganchrow]

thank you very much for your quick reply. do you, by any chance have any pointer to a paper that addresses this in the context of horse racing? or in any context, but addressing this specific problem?

I do not, no. You might want to check with the quantitative horse betting forums.

I assume that the solution will be to somehow maximize (convex optimiztion?? ) the utility function with respect to a multi-dimentional vector of bets?

Yes, except the optimization won't necessarily be convex.

This is precisely the same technique used in the generic Kelly optimization. As I mentioned, the only difference is that in the latter case the payout is a constant.

"Generic" Kelly Utility:

[nbtable][tr][td]E(U) = [/td][td][/td] [td]( p_i * U(1+[/td] [td][/td] [td] x_j*r_i,j ) ) [/td] [/tr] [/nbtable]

Kelly Utility with payouts given as a function of bet size:

[nbtable][tr][td]E(U) = [/td][td][/td] [td]( p_i * U(1+[/td] [td][/td] [td] x_j*r_i,j(x) ) ) [/td] [/tr] [/nbtable]

Play around with the above spreadsheet, or any of my other linked Kelly spreadsheets, which should provide a good understanding of using Excel Solver to solve Kelly problems. Once you understand how to do this then creating a simple spreadsheet to accomplish what you're looking to do should be quite straightforward.

[calm]

Thanks for posting this Ganchrow, looks great. I know you said you didn't want heavily support this, but here are two problems I've had if you get a chance to take a look...

1) The spreadsheet wouldn't display anything over 6 team parlays, no matter how many individual legs I included and making sure i set the max parlay size high enough. Looking at the VBA code, in the modDefs module I saw 'Public Const c_lMaxParlaySize As Long = 6'. Is there a reason this value is hard-coded? It looks like raising this number to 14 allows the larger parlays to be calculated.

2) I'm sometimes receiving an error message "The formula you typed contains an error" when running the solver. Then it says "Macro error at cell: [SOLVER.XLA]Excel4Functions!A18". Any idea what's causing this? I can't tell for sure, but if I click Continue it looks like the macro continues running properly and eventually reaches the correct solution.

[Ganchrow]

The spreadsheet wouldn't display anything over 6 team parlays, no matter how many individual legs I included and making sure i set the max parlay size high enough. Looking at the VBA code, in the modDefs module I saw 'Public Const c_lMaxParlaySize As Long = 6'. Is there a reason this value is hard-coded? It looks like raising this number to 14 allows the larger parlays to be calculated.

As I vaguely recall it was to save some memory (and time) when allocating array. I should have posted it with it set to a higher default value. By all means, change it to 14.

Just remember, however, that the freebie version of Solver that comes standard with Excel limits you to only 200 independent variables.

Anyway, I modified the constant value of c_lMaxParlaySize in the above posted file to 14.

I'm sometimes receiving an error message "The formula you typed contains an error" when running the solver. Then it says "Macro error at cell: [SOLVER.XLA]Excel4Functions!A18". Any idea what's causing this? I can't tell for sure, but if I click Continue it looks like the macro continues running properly and eventually reaches the correct solution.

Yeah, I've seen this too. It's just representative of some error in the Solver VBA Add-In (which is conveniently password protected by either Microsoft or Frontline).

Other than it being an annoyance, I haven't noticed it causing any real problems. As I recall the button click sequence is just 'OK"-"Continue"-"Continue" and only seems to occur with larger (by Excel Solver standards) optimizations.

[Rich Boy]

When I try to click on "Read Odds" I always get this error

"compile error cant find project or library"

The error comes up in visual basic

[djiddish98]

Do you have solver installed? If not, install it.

Try going to tools->references in VBA and clicking on SOLVER if it is not checked.

[Capybara]

[Rich Boy]

Ya I have it installed, when I tried to open the tools menu, "references" is shaded out, it wont let me select it.

[Rich Boy]

Ok, I can get into references now, it has

"MISSING: SOLVER.XLA"

[Rich Boy]

Ok, I finally figured it out... It took the help of this useful troubleshooting page.

http://www.egr.msu.edu/~lira/supp/macro.htm#pointing

Followed it step by step and it worked out.