Announcement

Collapse
No announcement yet.

Excel Poker

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Poker

    So I'm finding a way to look "busy" at work by coding an "Excel Texas Hold 'Em" game using VBA. Right now, I'm constrained by the deck shuffling algorithm I've come up with. It has a sheet with 3 colums - one for the rank, one for the suit, the third to determine if a card has been placed there (and later drawn). In psuedocode:



    Code:
    For i = 1 to 52
    
         Increment CurrentCardRank  **such that you go through A-2 for each suit
         Increment CurrentCardSuit   ** again so that you get 13 of each suit
    
         Do 
              Draw = Random(1,52)
         Loop Until Cells(Draw, 3) = False  ** run through the "slots" until you find one "free"
    
         Cells(Draw,3) = True                   ** mark that slot as taken
         Cells(Draw,1) = CurrentCardRank   ** insert the current rank
         Cells(Draw,2) = CurrentCardSuit    ** insert the current suit
    
    Next i                                            ** run through the 52 card deck
    This gives me a well shuffled deck that I also use the Do...Loop routine to draw a random card from (reversing the condition, of course).

    The problem I run into (due to 1. VBA's extremely crappy random number generator and 2. the inherent limitation in the scheme) is that near the end of the cards to be placed, most of the cells show FALSE (in the sense that there is already a "card" in that slot) and I have to run through a few thousand numbers in order to place the last 10 or so cards.

    This isn't a big deal if I were to play with a few frends and shuffle every 10 minutes or so. But it *is* a big deal if I want to run stats and need a program that runs as fast as possible so I can do several shuffles (hands) *per second*.

    As it refers to the acutal Excel sheet every time it does a condition check (I don't think it's that cycle intensive) I *could* move it all to an array and do it in RAM - but I don't think I've got a lot to gain there. Technically, the Excel sheet is in RAM - it's not like it has to go to the HDD to check a cell's value.

    So - anyone know of a clever way to shuffle a deck?
    Former TGNS admin until WoW blinded me with flashy lights.

  • #2
    Re: Excel Poker

    You might want to post this on usenet. There is a really active poker group that has all kinds of people in it, from world champion players, to internet casino owners/designers, to statisticians, to FNG poker players... There's bound to be someone there that can help you.

    I think it's rec.gambling.poker

    Comment


    • #3
      Re: Excel Poker

      I don't know much VBA but in any other language I would think the most obvious way to create a deck and shuffle it is to use a stack of "Card" objects, preferably as a linked list. That way shuffling would be just a matter of moving some pointers around (probably something like O(n) time if the rand() function is any good) and drawing a card would take O(1) time.

      Comment


      • #4
        Re: Excel Poker

        VBA doesn't really have support for linked lists but you could probably write your own implementation. The best option is probably a dynamic array. It's similar to a linked list but not as powerful since there's no control over the pointers. By doing something similar to Mudshark's algorithm, you can put cards in a dynamic array and randomly copy cards into another dynamic array or place them in the spreadsheet to build the shuffled deck. The removed card can be replaced by the card at the end of the dynamic array so it doesn't get lost when the array is resized.

        - It's who you game with.

        Comment


        • #5
          Re: Excel Poker

          http://msdn.microsoft.com/library/de...etonewitem.asp

          Havn't really looked too closely at it, but isn't this basically saying that object variables in VBA are merely references (pointers) to objects?

          Comment


          • #6
            Re: Excel Poker

            Oh, I forgot about object references. I guess that would be VB's pointers.

            - It's who you game with.

            Comment


            • #7
              Re: Excel Poker

              Originally posted by Mudshark
              http://msdn.microsoft.com/library/de...etonewitem.asp

              Havn't really looked too closely at it, but isn't this basically saying that object variables in VBA are merely references (pointers) to objects?
              From your linked (ha!) article:

              Originally posted by MSDN
              After this statement, objVar contains a pointer to the new member of the className class. Even though you can't manipulate, view, or otherwise work with pointer values as you can in C/C++, the Set/New combination at least gives VBA programmers almost the same functionality that Pascal programmers have always had, although the mechanism is a bit clumsier: you can create pointers only to classes in VBA
              Can't manipulate, view, or otherwise work with pointer values...

              and

              Create pointers only to classes...


              I agree that using a linked list would be best, but... beyond that - I'll have to think about a dynamic array and resizing it everytime I "place" a card.

              VBA's random number generator pulls a seed from the system clock - and I've done some plotting to see how random it really is. By default, it generates a number between 0 and 1, inclusive. I've run tests generating 500,000 (-ish) numbers and the generator is weak in the 0.6 - 0.8 range (statistically significantly fewer draws in this range). So I'll still have a slight problem with the linked list (if it's initially ordered) - the low clubs and high diamonds will tend to "shuffle to the bottom of the deck" somewhat skewing my deck. Some of this is overcome because I won't "deal from the top" (eg I draw random cards from the deck) - but some of it won't.

              Thanks for the help on this!
              Former TGNS admin until WoW blinded me with flashy lights.

              Comment


              • #8
                Re: Excel Poker

                Originally posted by Stupid
                From your linked (ha!) article:



                Can't manipulate, view, or otherwise work with pointer values...

                and

                Create pointers only to classes...
                Just to be clear, in the above example objVar points to a "object" of the "class" not to the class itself, correct? Is the problem that you cannot then set the already instantiated objVar to another instance of the class? i.e. you can only use objVar in conjunction with the new operator? Really, I don't know very much about VB but... that seems kinda, uhh, limited.

                My reading of that passage is that pointers in VBA work like object variables in java. i.e. you cannot do this:
                Code:
                int* i;
                or this:
                Code:
                char* array;
                array = "my Array 0wnz J00";
                while (*array) {
                   putch(*array);
                   array++;
                }
                or this:
                Code:
                void* memory = 0xFFFFFFFF;
                but you can do this:
                Code:
                Object myObj = new Object;
                Object tempObj = myObj;
                You cannot manipulate the actual value of the pointer or have pointers to primitives, or do pointer arithmatic, but you can change what they point to as long as you have another reference to copy.




                Originally posted by Stupid
                I agree that using a linked list would be best, but... beyond that - I'll have to think about a dynamic array and resizing it everytime I "place" a card.

                VBA's random number generator pulls a seed from the system clock - and I've done some plotting to see how random it really is. By default, it generates a number between 0 and 1, inclusive. I've run tests generating 500,000 (-ish) numbers and the generator is weak in the 0.6 - 0.8 range (statistically significantly fewer draws in this range). So I'll still have a slight problem with the linked list (if it's initially ordered) - the low clubs and high diamonds will tend to "shuffle to the bottom of the deck" somewhat skewing my deck. Some of this is overcome because I won't "deal from the top" (eg I draw random cards from the deck) - but some of it won't.

                Thanks for the help on this!
                IMO selecting a card at random from the middle of the deck is the root of the problem that you are trying to solve, no?

                How about this.

                Create a stack out of a fixed array of 52 elements. An integer value points to the top of the stack. Deal from the top and incriment the value. The shuffling is the proc intensive part in this case, not the dealing. That is much better performance since for a 10 player game, you would need to deal a card 25 times but shuffle only once. At minimum you are going to deal 4 cards, but only ever shuffle once per game.

                To shuffle, start at a random point in the array, swap that card with another card a (random int % 52) away in the deck. select another card (random int % 52) away from that card and perform another swap, repeat untill well shuffled. That way (using swaps) you dont need a resizable array. Also, by moving the base of the random number around, you can avoid a good bit of the non-randomness in the RNG. You would need to play around a bit with the number if iterations needed for a good shuffle, but I think eventually you will get enough randomness for your purposes. If it turns out that reshuffling takes too long, you can always just shuffle once at the beginning, and place the dealt cards back into the deck at random positions.
                Last edited by Mudshark; 08-17-2004, 09:04 PM.

                Comment


                • #9
                  Re: Excel Poker

                  *comment here about inserting dealt cards deleted becaue I finally understood what you meant*

                  And you're right - VB *is* limiting and goofy. It is Basic, after all - even if they fancy it up a little bit. TBH - 1) I'm not a coder by profession, 2) Excel makes it look like I'm working, 3) Excel lets me be lazy by being a pre-formatted "array" that I can use to store working variables (one of the traps that got me into this)... so I wasn't too worried about making really tight code (as long as it worked, and looked OK to play with friends, it was good).

                  I got interested in the code once I wanted to generate a stats database with it - that meant running as many rounds of the game as fast as possible - thus my need to optimize (my work computer, a 1GHz, runs all night -- it takes 10 seconds to shuffle, deal out the 25 cards for a 10 seat game, analyze all the hands, declare a winner, and then update the stats datasheets). The self-generated stats I can adjust to determine "good hands," but also tell me which hands are better for chasing to the river, adjust folding rules for opening hands, etc. This is more than a standard poker odds website / book can offer (I think).

                  I took icecold's suggestion and posted on rec.gambling.poker here.

                  I like a number of those suggestions as well.


                  Thanks for the help guys. I'd work on it tonight, but - uh - tomorrow doesn't look like an exciting day at work! I'll let you know how it goes. I'm thinking I just start over with a new hand generator for the stats program (I basically kludged the interface from the "real" program that we use to play at lunch - which includes a bunch of formatting, etc etc) and go with really tight code.
                  Former TGNS admin until WoW blinded me with flashy lights.

                  Comment


                  • #10
                    Re: Excel Poker

                    yeah there are some good suggestions there, but I don't know about the sorting random numbers thing. I guess it depends on how long the RNG takes as to weither it's any faster than my method of swaping cards in an array. 52 is a pretty small number though so i guess it would be. however, if you are unsitisfied with the quality of the RNG then that is not going to solve the issue.

                    Comment


                    • #11
                      Re: Excel Poker

                      Originally posted by Stupid
                      *comment here about inserting dealt cards deleted becaue I finally understood what you meant*

                      And you're right - VB *is* limiting and goofy. It is Basic, after all - even if they fancy it up a little bit. TBH - 1) I'm not a coder by profession, 2) Excel makes it look like I'm working, 3) Excel lets me be lazy by being a pre-formatted "array" that I can use to store working variables (one of the traps that got me into this)... so I wasn't too worried about making really tight code (as long as it worked, and looked OK to play with friends, it was good).
                      Ahh, I see where you are going. I know absolutely zero about the interface between Excel and VB and i am confused about how using it as a preformatted array helps (except that it makes it look like official work... :icon16: ). Even still, I imagine that you could load the cards from excel and keep references to their position in Excel while manipulating them in memory.

                      Oh and 10 seconds at 1GHz?!?! WOAH!!! :icon_eek:

                      Comment


                      • #12
                        Re: Excel Poker

                        Yeah - 10 seconds -- like I said, really non-optimal. It's doing a few other things (that for the stats end really isn't needed) like going through and replacing the "S", "H", etc holders that I have for the suits in the 'Deck' sheet with the actual character from the Arial font, changing colors, resetting the table, bets, pots, yadda yadda yadda.

                        Shuffling takes the most time out of all of that - just due to the fact that it has to hit the *exact* integer of the last slot to place the last card - and apparently that takes a long time.

                        LOL - you do have a long way to go to realize how lazy I am. VBA programs are just macros for Excel that can manipulate items on the spreadsheet (for the most part). They're not like a "program running in the background" that you can interact with (you can, just through stupid dialog boxes). So - you lay out most of what really should be in memory onto an actual Excel worksheet and interact with stuff there - calling another macro when you're ready to move on to the next step.

                        So rather than shuffle a deck and deal all from memory, I have a sheet that has all the cards so that I can do my formatting tricks... so it ends up making me a lazy programmer.

                        You're thinking the "right" way - which happens to be totally backward. Remember - I'm not a programmer using a completely wrong software package to do poor programming. I'm not as pretty as you want me to be!

                        But it's still fun.
                        Former TGNS admin until WoW blinded me with flashy lights.

                        Comment


                        • #13
                          Re: Excel Poker

                          I haven't done much Excel VBA since I learned a bit of it in school but spreadsheets can be heavily programmed with macros. Program or macro, it's driven by code that has a lot of features similar to a real language. I've used VBA for Access databases and it did more than I thought it could.

                          Are you using code to format the output? Maybe using a lookup table on another sheet would be speed things up a bit. If you can get all the shuffling done in memory, it would really help. Outputting to the spreadsheet is going to be the slowest part.

                          - It's who you game with.

                          Comment


                          • #14
                            Re: Excel Poker

                            Originally posted by Stupid
                            LOL - you do have a long way to go to realize how lazy I am.
                            Ok I looked a bit into what you described, and i don't think "lazy" really is the word for it... "masochistic" seems more appropriate. :icon20:


                            There definately is a way to do this in VB and in memory, but i could't tell you how to invoke the events to do the rest of the stats stuff you want to do.

                            Let me know how it turns out though. :icon14:

                            Mudshark

                            Comment


                            • #15
                              Re: Excel Poker

                              The code does the rudimentary stuff - shuffling, dealing, tracking bets, etc. The formatting and pretty part is done by VLOOKUP (an excel function) and conditional formatting in the appropriate cells (making the hearts and diamonds show in a red font).

                              I got two sorting algorithms coded in today - the start for redesigning the shuffle / dealing. Once I get that done, the stats should go much faster.


                              The way I'm doing stats, as I explain in my latest usenet post, is pretty simple -- 13x5 array - 13 rows, one per card rank (acutally I use the 0 row to wrap the Ace around for low straights). Column 0 reads in the number of cards of rank ROW (a pair of Aces would be HAND(13,0) = 2) and the subsequent columns keep track of the suits. From there it's just logic checks to see what kind of hand is there (pairs give HAND(i,0)=2, a full house would have a pair and a HAND(j,0)=3, etc) and determine which is best.

                              After that, it's just marking stuff in another Excel workbook to keep track of 1) which starting hands were played, 2) which starting hand won, 3) what kind of hand won.


                              Out of the 10 seconds, shuffling takes like 7, determining the winner (which is kinda done in a poor way) takes 2.5, and the rest takes 0.5 sec.


                              Today was busier than I thought! Maybe tomorrow... :)
                              Former TGNS admin until WoW blinded me with flashy lights.

                              Comment

                              Connect

                              Collapse

                              TeamSpeak 3 Server

                              Collapse

                              Advertisement

                              Collapse

                              Twitter Feed

                              Collapse

                              Working...
                              X