New VBA Sports Betting Functions Excel Demo

[Ganchrow]

A few people have have asked if and if I still planned to post an update to http://forum.sbrforum.com/handicappe...tml#post256487.

The short answer is yes, but only after I put together some better documentation.

In the meantime, however, interested parties can download the attached demo spreadsheet. I've recoded many of the functions in C++ , so if want to check it out you'll also need the matrix.dll library included in the .zip file. Copy the DLL to your \Windows\System32\ directory and you'll be ready to go.

Feel free to copy the VBA functions and wrapper code into your own spreadsheets. You can also call the DLL functions directly from other programming languages. Not all the included functions are demoed and most won't be of use to 99%+ of posters. Many of the functions I personally use all the time, others I virtually never use.

The main reason I rewrote much of the was to speed up large matrix operations. Inversions are much faster than with the Excel built-in function (and without any size limits) as are calculations involving nested matrix operations (as would be the case, for example, with generalized regressions). I also coded an Eigenvalue/vector solver for symmetric matrices which should useful to anyone delving into Principal Component Analysis.

Anyway, this comes with absolutely no warranties, ... , etc. etc. If anyone finds any of this useful -- great.

[PRC]

Incredible! Thanks ganch! Do you need Windows to run the DLL and/or your old .xlt template? And what are the chances you could add a tab that factors in the Matchbook commissions?

[Ganchrow]

Incredible! Thanks ganch! Do you need Windows to run the DLL and/or your old .xlt template? And what are the chances you could add a tab that factors in the Matchbook commissions?

Well it's specifically compiled as a Win32 DLL.

There are a few Matchbook conversion functions not demoed.

The VBA wrapper functions (found in the modSBGanch_Exchanges module) are:

Public Declare Function MB2US Lib "matrix.dll" Alias "g_MBUS2US" ( _
    ByVal dUSOdds As Double, _
    ByVal dCommission As Double _
) As Double

Public Declare Function MB2DEC Lib "matrix.dll" Alias "g_MBDEC2DEC" ( _
    ByVal dDecimalOdds As Double, _
    ByVal dCommission As Double _
) As Double

Public Declare Function MBUS2US Lib "matrix.dll" Alias "g_MBUS2US" ( _
    ByVal dUSOdds As Double, _
    ByVal dCommission As Double _
) As Double

Public Declare Function MBDEC2DEC Lib "matrix.dll" Alias "g_MBDEC2DEC" ( _
    ByVal dDecimalOdds As Double, _
    ByVal dCommission As Double _
) As Double

Public Declare Function MBUS2DEC Lib "matrix.dll" Alias "g_MBUS2DEC" ( _
    ByVal dUSOdds As Double, _
    ByVal dCommission As Double _
) As Double

Public Declare Function MBDEC2US Lib "matrix.dll" Alias "g_MBDEC2US" ( _
    ByVal dDecimalOdds As Double, _
    ByVal dCommission As Double _
) As Double

These provide the following functions.

• MB2US(US Odds, Commission) -- US Odds input, returns Effective US Odds After Commission (alias: MBUS2US)
• MB2DEC(Decimal Odds, Commission) -- Decimal Odds input, returns Effective Decimal Odds After Commission (alias: MBDEC2DEC)
• MBUS2DEC(US Odds, Commission) -- US Odds input, returns Effective Decimal Odds After Commission
• MBDEC2USDecimal Odds, Commission) -- Decimal Odds input, returns Effective US Odds After Commission

[lasker]

---------->

[durito]

Gracias.

[rise]

great stuff thanks

[MadTiger]

Pointed.

[benjy]

Cool! Thanks!

[Premierr23]

wow nice

[JohnAnthony]

Wow, so Granchow's back?

[Ganchrow]

I was wondering if you would share the source code for your kelly calculator at:

http://www.sbrforum.com/betting+tool...alculator.aspx

Better than that is the C++ source underlying the routine called by the VBA KellyVector() wrapper function in the above posted spreadsheet. Follows here:

double DLLPEXPORT KellyVector(
        double *dProbs,                // array of independent event probabilities (input not changed)
        double *dOdds,                // array of independent event odds (input not changed)
        double *dKellyOutVector,    // array of Kelly stakes (output)
        long lEvents,                // number of events (input not changed)
        double dKellyMult,            // Kelly multiplier > 0 (input not changed)
        long lOddsType                /* odds type (input not changed)
                                       0 ==> all odds > 0 are decimal, all odds < 0 US
                                       1 ==> all odds are US
                                       2 ==> all odds < 0 or >=100 are US
                                    */
){
    long i, j;
    long lParlaySize, lParlayNum, lThisAND, lOutcomes, lBetIdx, lResIdx;

    lOutcomes = 1<<lEvents;

    long *lCombins = (long*)malloc( (lEvents+1) * sizeof(long));
    long *lBetsPerSize = (long*)malloc( (lEvents+1) * sizeof(long));
    long *lMapV = (long*)malloc( lOutcomes * sizeof(long));
    double *dSingKellyV = (double*)malloc(lEvents * sizeof(double));

    if(lCombins==NULL || lBetsPerSize==NULL || lMapV==NULL || dSingKellyV==NULL)
        // memory allocation error
        goto END;

    
    for(i=0; i<lEvents; i++) {
        if(i) {
            lBetsPerSize[i] = combin(lEvents, i);
            lCombins[i] = lCombins[i-1] + lBetsPerSize[i];
        } else {
            lCombins[0] = 0;
            lBetsPerSize[0] = 0;
        }

        lMapV[i] = 1<<(lEvents - i - 1);

        if (dOdds[i] < 0) {
            dOdds[i] = g_US2DEC(dOdds[i]);
        } else {
            switch(lOddsType) {
                case ODDS_ARE_DECIMAL :
                    break;
                case ODDS_ARE_US :
                    dOdds[i] = g_US2DEC(dOdds[i]);
                    break;
                case ODDS_ARE_MIXED :
                default :
                    if(dOdds[i] >= 100)
                        dOdds[i] = g_US2DEC(dOdds[i]);
            }
        }
        if(dOdds[i] == NULL) goto END;
        dSingKellyV[i] = SBKelly(dProbs[i], dOdds[i], dKellyMult);
    }

    lMapV[lOutcomes-1] = 0;
    lBetsPerSize[lEvents] = 1;
    lCombins[lEvents] = lOutcomes-1;
    for(i=1; i<lOutcomes; i++) {
        lParlaySize = lParlayNum = 0;
        for(j=0; j<lEvents; j++) {
            lThisAND = i & lMapV[j];
            lParlayNum += lThisAND;
            lParlaySize += lThisAND ? 1 : 0;
        }

        lResIdx = lCombins[lParlaySize-1] + lBetsPerSize[lParlaySize] - 1;
        lMapV[lResIdx] = lParlayNum;
        lBetsPerSize[lParlaySize]--;
        dKellyOutVector[lResIdx] = 1;
    }

    for(lResIdx=0;lResIdx<lOutcomes-1;lResIdx++) {
        for(i=0; i<lEvents; i++) {
            lBetIdx = 1<<(lEvents - i - 1);
            if(lMapV[lResIdx] & lBetIdx)
                dKellyOutVector[lResIdx] *= dSingKellyV[i];
            else
                dKellyOutVector[lResIdx] *= (1 - dSingKellyV[i]);
        }
    }

END:
    // clean up
    free(lCombins);
    free(lBetsPerSize);
    free(lMapV);
    free(dSingKellyV);

    return dKellyOutVector;
}

inline double SBKelly(double dWinProb, double dDecOdds, double dKellyMult){
    double dWinQuant, dKellyStake;
    if(dDecOdds <= 1 || dWinProb > 1 || dWinProb < 0 || dKellyMult <= 0)
        return NULL;

    dWinQuant = dDecOdds - 1;
    dKellyStake = (pow(dWinQuant * dWinProb, dKellyMult) - pow(1 - dWinProb, dKellyMult)) / (pow(dWinQuant * dWinProb, dKellyMult) + dWinQuant * pow(1 - dWinProb, dKellyMult));
    return dKellyStake>0 ? dKellyStake : 0;
}

inline double DLLEXPORT g_US2DEC(double dUSOdds){
    if(dUSOdds<0)
        return 1-100/dUSOdds;
    else if(dUSOdds>0)
        return 1+dUSOdds/100;
    else
        return 1;
}

inline long perm(long n, long r){
    long i;
    long lAns = 1;

    if (n < 0) n = 0; 
    if (r > n)
        r = n;
    else if (r < 1)
        r = 1;

    for(i = n; i > r; i--)
        lAns *= i;

    return lAns;
}

inline long DLLEXPORT combin(long n, long r){
    if (n > r+r)
        return perm(n,n-r)/perm(r,1);
    else
        return perm(n,r)/perm(n-r,1);
}

[Blax0r]

One nitpick:

I only quickly skimmed through the code, but I think if r > n (for your perm function), you should indicate an error w/ the 0 return value (or something else). The function shouldn't automatically "fix" a user's mistake; excel works that way too (try something like =Combin(2,50)).

[Ganchrow]

I think if r > n (for your perm function), you should indicate an error w/ the 0 return value (or something else). The function shouldn't automatically "fix" a user's mistake

No argument here.

Call it legacy code -- I swiped those two functions unmolested out of something I wrote as a clueless grad student way back in the 90s.

I'm much better now.

[Blax0r]

Ah gotcha; either way, I'm sure this .dll has paid/will pay dividends for its users. Also, I think a cool feature (that would save a step) would be an extra param for the bankroll, but may not be worth the effort since it's easy enough to just multiply the output.

[byronbb]

Thanks these functions have been very useful and saved me a lot of time and swearing.

[punintended]

Having some trouble with this ..
When I open the file and enable macros I get the error:
Error #53 in Kelly Functions. File not found: matrix.dll

Help?

[RickySteve]

Not sure but possibly incompatible with your version of Excel.

[AlwaysDrawing]

I get the same error.

[Big Tebowski]

I also get the same error

[matekus]

Possibles Solution

Possible solution:

1. Copy FunctionDemo.xls and matrix.dll to same directory (NOT "C:\Windows\System32").
2. Open FunctionDemo.xls and access VBA editor (Alt-F11).
3. Use Ctrl-H to open Replace dialog box (see attached).
4. Replace "matrix.dll" with ".\matrix.dll" (73 locations).
5. Save FunctionDemo.xls.

John

.