New VBA Sports Betting Functions Excel Demo

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Ganchrow
    SBR Hall of Famer
    • 08-28-05
    • 5011

    #1
    New VBA Sports Betting Functions Excel Demo
    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.
    Attached Files
  • PRC
    SBR Wise Guy
    • 10-22-09
    • 576

    #2
    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?
    Comment
    • Ganchrow
      SBR Hall of Famer
      • 08-28-05
      • 5011

      #3
      Originally posted by 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?
      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:

      Code:
      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
      Comment
      • lasker
        SBR MVP
        • 01-27-10
        • 1683

        #4
        ---------->
        Comment
        • durito
          SBR Posting Legend
          • 07-03-06
          • 13173

          #5
          Gracias.
          Comment
          • rise
            SBR Sharp
            • 03-01-09
            • 372

            #6
            great stuff thanks
            Comment
            • MadTiger
              SBR MVP
              • 04-19-09
              • 2724

              #7
              Pointed.
              Comment
              • benjy
                SBR MVP
                • 02-19-09
                • 2158

                #8
                Cool! Thanks!
                Comment
                • Premierr23
                  SBR Wise Guy
                  • 01-16-10
                  • 511

                  #9
                  wow nice
                  Comment
                  • JohnAnthony
                    SBR Hall of Famer
                    • 04-30-09
                    • 5110

                    #10
                    Wow, so Granchow's back?
                    "I have never seen a wild thing feel sorry for itself. A little bird will fall dead, frozen from a bough, without ever having felt sorry for itself."

                    - D.H. Lawrence
                    Comment
                    • Ganchrow
                      SBR Hall of Famer
                      • 08-28-05
                      • 5011

                      #11
                      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:
                      Code:
                      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);
                      }
                      Comment
                      • Blax0r
                        SBR Wise Guy
                        • 10-13-10
                        • 688

                        #12
                        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)).
                        Comment
                        • Ganchrow
                          SBR Hall of Famer
                          • 08-28-05
                          • 5011

                          #13
                          Originally posted by Blax0r
                          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.
                          Comment
                          • Blax0r
                            SBR Wise Guy
                            • 10-13-10
                            • 688

                            #14
                            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.
                            Last edited by Blax0r; 10-20-10, 06:41 PM. Reason: fixed spelling
                            Comment
                            • byronbb
                              SBR MVP
                              • 11-13-08
                              • 3067

                              #15
                              Thanks these functions have been very useful and saved me a lot of time and swearing.
                              Comment
                              • punintended
                                SBR Hustler
                                • 11-29-11
                                • 90

                                #16
                                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?
                                Comment
                                • RickySteve
                                  Restricted User
                                  • 01-31-06
                                  • 3415

                                  #17
                                  Not sure but possibly incompatible with your version of Excel.
                                  Comment
                                  • AlwaysDrawing
                                    SBR Wise Guy
                                    • 11-20-09
                                    • 657

                                    #18
                                    I get the same error.
                                    Comment
                                    • Big Tebowski
                                      SBR Sharp
                                      • 12-13-11
                                      • 269

                                      #19
                                      I also get the same error
                                      Comment
                                      • matekus
                                        SBR Rookie
                                        • 07-26-07
                                        • 39

                                        #20
                                        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

                                        Click image for larger version

Name:	MatrixDllReplace.jpg
Views:	1
Size:	38.9 KB
ID:	29120393.
                                        Comment
                                        SBR Contests
                                        Collapse
                                        Top-Rated US Sportsbooks
                                        Collapse
                                        Working...