I built myself an excel spreadsheet that allows me to count cards perfectly and make the optimal move in every situations depending on the remaining cards in the deck. The only problem is that i don't get the exact same results as the Wizard in his Appendix 2b.
For example, for dealer's chance to bust depending on the upcard, for any numbers of decks i get:
U can see that my results are a bit flawed compared to those of the Wizard, but this is because my program is based on an infinite deck and does not account for the effect of card removal, unless u start the count and remove certain cards.
I would like my program to account for the effect of card removal so i can see the TRUE house edge and not just the estimated one based on an infinite deck, but the Wizard doesn't show his process ..
Anybody have an idea?
If you are interested in exact results, you need to basically "brute force" the game.
Since the dealers strategy is fixed all times, you can quickly generate all dealer's hand probabilities (which will depend on the composition of the remaining shoe).
The player strategy is not fixed, hence you must recursively traverse each possible strategy decision until you bust, and then backward dice at every branch which strategy option (stand, hit, double, split, surrender) you would need to take.
This is rather simple to do in a C program, but I doubt it is feasable for an excel sheet.
The only thing that buggs me is that it is based on an infinite deck, and i want to change that. For instance, the chance of the dealer hitting a blackjack when his upcard is an Ace is actually calculated as 16/52, but it is supposed to be 16/51 because we need to account for the Ace. Thats what i want to change. It seems pretty easy to do for the blackjack, but i tried do this for the dealer hitting 17 for instance, and i get lost in all the combos of hands possible. MAybe im doing it wrong so thats why im looking for someone who already did it or that knows how to do it.
It takes ages to work out all the permutations (I tried it once for 10 2 vs 4) but imagine if you can work out all the combinations of cards that a dealer can draw and their associated totals, then this "database" can be used given the cards you know to have gone or be in your hand. So for instance in the above example you would compare 10 2 A against a starting 4, 10 2 2 etc. I think it was 57 million things to check!
The easier one is 16 vs 10 as there aren't many combinations of hands the dealer can get (he can only get a maximum of six more cards 2AAAAx).
I did once write a program to analyse Pai Gow (cards) on the fly, especially interesting is two pair, but really it is programming rather than using spreadsheets.
But in the end you have to ask yourself: why are you trying to perfect the analysis? You will never be able to account for all of this at the table, and even if you are able to, you will get backed off very quickly from a single deck game. Expend your creative energies somewhere else.
The spreadsheet is already made. I calculated all the dealers probability depending on his upcard, i calculated the EV of hitting, standing, doubling and splitting on every situation possible and made a strategy table that take all that into account. I linked everything to another board that keep track of the cards remaining in the deck so when i pull out some cards, the dealer's probability and the strategy table change accordingly.
Well yes, this is an infinite deck analysis. It will depend on your (remaining) deck size if you will get good results with it. The rule of thumb is "there is either single deck or infinite deck", meaning that the card removal of a few cards do not dramatically change all probabilities if the shoe is sufficient large.
If you want to do exact compositional play - forget about excel. While it's surely not impossible to do (no, you don't need "millions" of cells, but a few thousand would do) - it's simply not a good tool for this kind of analysis. If excel is your only programming skill - it is time to learn a real programming language (I like C) - or stick to infinite deck analysis.