My aim was to be finished with the Excel Texas Holdem Solution by now but as they say: “Life happens”.
For now it’s a bit on hold. I started a new job and my energy needs to go into it until I’ve managed to settle.
So it will still be completed.
Monte.
Looking back it seems easy enough to have created the code up to now. In fact, and for me at least, it was nothing more than ground breaking. I managed to overcome my biggest stumbling block in determining the best possible 5 Card Poker Hand from 5, 6, 7 or even more cards for that matter. The engine I have managed to build can actually be used for so much more than just Texas Holdem Poker. I’m starting to think that I can apply the same engine to other card games based on poker rules. Games like one of my favourite Casino Card games, Let It Ride. It can also be used for the other popular poker games played during the WSOP.
Already the code gives more information than I used to get with other poker programs. But there is still many more functions to be created.
Over the next 2 weeks I will attempt to finish the Hand Analyser up to the point where it can start to pump out data for the next phase in this exercise, the BI Tool. I’m enjoying the coding tremendously but the BI Tool is the part that I’m really looking forward to. This is where the data generated will come alive.
One thing to be said though is that this code will also enable me to create an Excel based Texas Holdem Poker Tutor and an Excel Based Texas Holdem Poker Game, emulating real play for all versions of the game, tournament, cash, etc, and from here it is amazingly easy to build an Excel Version of the very popular Texas Holdem Poker calculator, PokerStove.
Choices, choices. Which to tackle first……
It is exciting to see that without advertising, people from Korea, Canada, the US, UK, Brazil and South Africa are already joining Monte on his quest. I’m looking forward to the day that I can offer an Excel Texas Holdem Poker Game which allows the end user to play around themselves with the Poker Functions and build their own solutions.
I’ve also decided not to advertise Pot On The River at this stage until I’m well into the BI Tool.
Now this is great!
We now have custom Excel Texas Holdem Poker Functions. They are: DealDeck(), DealHoleCards(PackOfCards, Player, TableSize), SortHoleCards(HoleCardsToSort), PlayerHand(PackOfCards, Player, TableSize, BurnCard), DealTheFlop(PackOfCards, TableSize, BurnCard), DealTheTurn(PackOfCards, TableSize, BurnCard), DealTheRiver(PackOfCards, TableSize, BurnCard), CommunityCards(PackOfCards, TableSize, BurnCard), BestHandFlop(PackOfCards, Player, TableSize, BurnCard), BestHandTurn(PackOfCards, Player, TableSize, BurnCard), HoleCardRanking(HoleCard), HandRank(HandValue), HandName(Hand), HandType(Hand), BestHand(Hand) and SortHand(HandToSort).
Below is what it can be used for typically. The layout and supporting Excel functions can be changed as required.
You will find the relevants Functions here.
Texas Holdem Excel Hand Analyser
The content of this blog is free for personal use ONLY. It cannot be used, adapted, copied, or published or used commercially or otherwise without my permission.
Before any hands can be analysed I need to be able to deal the fully shuffled deck to any amount of players and determine which player won and with what type of hand.
Now that the Deck Creator has been completed I can move on to the Dealer, determining the winner and chasing the rabbit for each hand dealt.
“Dead Cards influence Actual Play while Rabbit Cards influence Future Play.” Monte
TRNG = True Random Number Generated Decks.
The web has a lot to say about Pseudorandom Number Generation (PRNG or Computer Generated) and True Random Number Generation (TRNG). I’m not going to repeat it here. The crux of the matter for me is that PRNG represents online or computer poker game play where the computer deals and TRNG represents live poker games where humans deal. The randomness differs as shown in the image below (www.random.org). If you create enough decks using PRNG the decks will repeat itself in the same sequence after a while. Not good for statistics. That is why I had to include truly random number generated decks so that we can compare the results.
Creating the PRNG part of the Deck Creator was a breeze. TRNG will be much more demanding. The Deck Creator engine will stay the same and I can see that the code will change to the effect that an array from PRNG and TRNG will be passed to a separate function. The challenge is more in finding a way to create the true random numbers. I can go and sit and shuffle a million decks by hand and by the time we can travel faster than light be halfway to a million hands or I can source the numbers.
There is hardware available that generates True Random Numbers called True Random Number Generators and they can be USB or Computer Card based. There are also sites that provide you with True Random Generated Numbers and you can download these. I will make use of one such download service and will provide the details once I have decided which one to use.
But first I will have to change and prepare the code already written to accept both PRNG and TRNG numbers for output to the screen.
Amazing? Do you want Mozart to be your dealer, or maybe Bon Jovi, a high resolution photo of your family or the Avatar movie? The code I’ve written actually allows you to pick ANY file as the source for your randomly shuffled decks. True, the rhythmic sounds of a song might proof not to be the best source if you want true randomness but it is intriguing to think that you can take a any Hubble photo of the Universe and use it as your source for shuffled decks.
The point I’m trying to make is that you can choose your PRNG source and use it.
I downloaded my random files from the ANU Quantum Random Numbers Server. They have 10MB,100MB and 1GB torrent files with true random numbers created by quantum fluctuations of the vacuum.
Like ANU, HotBits also provides a download stream service. Typically you will use these for single deck shuffling. This option will be available in the Practice Play section so that you can choose your dealer; PRNG, TRNG (HotBits or ANU), Artists, etc.
During my TRNG research I discovered last night that my laptop has it’s own True Random Number Generator. How cool is that! It appears that Intel’s “Ivy Bridge Platform” has it built in since early 2012. Never late than never I guess. Chances are that if you bought a Intel based PC after that date you might sit with a TRNG yourself. To access the new RdRand instruction set you have to use an API. The Visual Studio 2010 source code is available and it must be compiled.
It seems that we will then have a few sources to choose from for True Random Generated Numbers.
You can get the Deck Creator code here.
The content of this blog is free for personal use ONLY. It cannot be used, adapted, copied, or published or used commercially or otherwise without my permission.
PRNG = Program Random Number Generated or Pseudo Random (Machine Generated).
Creating the Deck Creator basically requires using the random function in Excel while making sure that no numbers repeat in a deck. The numbers (1 to 52) are then given Card Names and these are then written out to the Excel Sheet as a string in a one cell.
To ensure that the same deck is not used more than once during the BI phase, each deck is assigned its own GUID. Duplicate Decks, if any, can then be removed at a later stage. I will add a Check For Duplicates function later. I add this only as a precaution against duplicate information.
Note that, although the deal is random, it is still possible to have duplicate decks. This is scarce, 8.0658X10^67 scarce or 1 in 8 undecillion vigontillion scarce, but normal. Why scarce? There are 80,658,175,170,943,878,571,660,636,856,403,766,975,289,505,440,883,277,824,000,000,000,000 Shuffling Combinations for a 52 Card Deck (52!, factorial notation). It is highly unlikely to pick a random deck more than once with that amount of combinations. Sometimes you feel that the same deck have been dealt again while playing online poker. It may just seem similar since only the first 26 cards of the deck is used in 9 handed play.
On my Intel i7, 8GB, 64bit, 2.2 GHz Laptop it took:
Note on TRNG: If you think 1 ½ Hours is long you’d be surprised to know that it took me more than 5 full days previously getting it online from http://qrng.anu.edu.au/API/jsonI.php?length=1024&type=hex16&size=1. You can read more about that in the next post Deck Creator (TRNG).
21 July 2013
None of us knows everything there is to know about any one subject. There is always people who can help with improving the way we do things. My code was very cumbersome in creating decks. Stephen Dunn from Michael Marin’s Blog – The Ultimate Poker Challenge pointed that out to me. Using VBA’s Collection capability I managed to save nearly an hour on TRNG and 6 minutes on creating 1 Million PRNG Decks. Thanks Stephen.
This image below is a representation of what the Deck Creator looks like. As we progress, the filename will change to something more appropriate. The Ribbon in the Excel Workbook currently shows buttons for the Deck Creator in the Rabbit Hunting TAB. More Buttons/Groups/TABs will be added to the Ribbon Bar as more functionality is developed; Hand Analysis, Statistics (BI), Practice Play, Real Play (PokerStars), Replay Hand History, etc.
The code for the Deck Creator does not include the Custom Tab in the Ribbon. Should you require the Excel Workbook for the Deck Creator you will need to request it via email.
Click on image for a bigger picture
The content of this blog is free for personal use ONLY. It cannot be used, adapted, copied, or published or used commercially or otherwise without my permission.
In order to deliver a flexible Excel Based Texas Holdem Poker Environment I am going to need to create the following functionalities in VBA:
01 A Deck Creator (COMPLETE)
– Able to create fully shuffled card decks. DONE
– Decks must be computer random generated shuffled decks (PRNG). DONE
– Decks must also be truly random shuffled decks (TRNG). DONE
02 A Dealer (COMPLETE)
– Able to deal the shuffled deck of cards to 2 to 10 players, DONE
– Make provision for the burn or not, DONE
– Determine which player won with what type of hand. DONE
03 Hand Analyser (80% Complete)
– Able to analyse hands based on the following criteria: DONE
– Analysis covered must be per player and also the community cards:
04 A Business Intelligence Tool
– Able to produce different kind of statistics based on the analysis the Hand Analyser performed. Stats for graphs like: NOTE: The graphs below is just a tiny portion of the graphs actually possible/available.
The 4 quadrants represent from top left to bottom right, Pre-Flop, Flop, Turn, River. The quadrants show the starting hand’s average position on that specific street. 
shows that this starting hands wins more as a pair. The doughnut chart clockwise gives; Hand Strength Overall, Winning % on the Flop, Winning % on the Turn and Winning % on the River. The Centre number indicates the average position overall.
You hold AA in your hand. Have you ever wondered with what type of hand you are most likely to win with and how this compares on average with the other starting hands?
The content of this blog is free for personal use ONLY. It cannot be used, adapted, copied, or published or used commercially or otherwise without my permission.
You are most probably here because you are looking for an Excel Based Version of Texas Holdem Poker that you can customise, generate your own stats from and create your own Texas Holdem Poker Game or Texas Holdem Poker Trainer. That is what I was looking for and couldn’t find. Yes, I did find source code for Texas Holdem Poker that I could compile if I had Visual Studio or a Java Compiler or many other compilers. All good, but like you, I don’t want to go on a course on C++ or Java Programming. I’m very comfortable with the Excel Environment and since I could not find any Excel based Texas Holdem Solution I decided to create my own.
If this is what you are looking for you are at the right place. You might at this stage think that is all Excel Formulas. It can be and I’ve done it previously and it works. The problem is that the formulas become so complex and long that it is difficult to maintain. It also hampers the flexibility in creating what you want in Excel. Subsequently I decided to create Excel Custom Formulas (VBA Functions) instead. At least if you have Excel, you have its Programming Environment as well. I also believe that the VBA will be easy to follow.
It will be great to have an Excel Formula like:
=DealHoleCards(PackOfCards, Player, TableSize)
where you provide a Deck of cards (no need for it to be 52 cards) or Excel creates a Deck of shuffled cards and you ask for any Players Hole Cards on any size table.
What about Excel Formulas that can deal the flop, turn, river, decide which player won, with what on which street, are the cards suited, connected and with burning cards or not?
Another reason why I wanted something in Excel might also be the same reason why you are here. I find that the statistics I get from standard poker programs and books are lacking in the detail that I require. Whilst creating the Texas Holdem “Program” it will also be possible to obtain statistics from it.
All in all I intend to create a flexible poker environment which I can use to teach myself more about Texas Holdem Poker on my own terms and without the limitations of other poker programs.
Rabbit Hunting - Hold'em or not 