Legitimate businesses have accountants who spend their days looking over cash flow. To grow any company and increase profits, you have to have a clear understanding of where you make your profit. And what accounts for your losses. Your poker exploits are no exception. Without keeping notes on all of the sessions you play, it's simply not possible to truly analyze your game. You need to understand where you make your money and how much money each aspect of your game makes you.
Know Your Limit, Stay Within It
Almost all winning poker players become losing players at some specific limit. If the only number you keep track of is your total bankroll you'll never know if any specific limit is a money pit for you.
For example: If you make $20/hour playing a $5/$10 game but lose $10 an hour playing $10/$20, as long as you play more hours of $5/$10 during the month at the end of it you'll ended up with a profit.
Because you see the month as being in the black you'll continue to play $10/$20 without realizing how much money it's costing you to do so.
What Stats to Track in Poker
The more information you record and track in poker the more interesting reports you can create with that information. It's up to you to decide exactly how in-depth you care to go.
No matter what you choose, some items are vital for all players to track. For every session you should record:
- Hours spent at the table
- Total buy-in amount (including all top-ups and rebuys)
- Total cash-out amount
This information lets you to see your profits/losses, hourly rate, BB/hour ratio, yearly trends (what months are more or less profitable) and how many buy-ins deep you go in for on average. If you want your records to be more informative you can also keep track of:
- Specific players at the table
- Day of the week
- Time of day
Recording this data will help you figure out:
- Where you make the most money
- What days of the week/times of day you tend to play best
- How your mood affects your results
- How specific opponents affect your results
- Which variants are most profitable for you
These are all important pieces of information for a professional poker player to know. If you're a casual player the first set of results will do you just fine.
How to Use Excel for Poker
The best way to track these numbers for most players is to write them down in a little book at the table. When you get home put them into an Excel spreadsheet. This makes it simple to get instant reports and results and allows for more in-depth analysis.
If you've never used Excel before there's no better time to start. Grab some online tutorials and figure the program out. Once you have even a rudimentary knowledge of the program you can start to create your stat-tracking spreadsheet.
Here's an example of a very simple stat-tracking setup:
The first five columns (A-E) need to be filled out by you but the last two can be populated by Excel automatically; to do that you'll need to put a formula into each field. If you click on a field and enter an equal sign into that field Excel will discern that you're creating a formula.
- Result: =SUM(E2-D2)
- $/h: =PRODUCT(F2/B2)
Now when you add a new row of data into row three all you have to do is highlight the column with a formula you want to reuse, copy it and paste it into the new cell.
Better yet you can use the Fill Down function. Use the mouse to select the cell with the formula, and all the cells below it you want the formula in, and hit CTRL-D (or use the "File" menu to select the option "Fill Down"). Now you have a simple spreadsheet. To easily build other basic formulas use the built-in formula builders in Excel. All you have to do is follow the instructions, point and click.
How to Calculate BB/hour
If you want to get more advanced, do a little Googling and you can figure out how to run almost any report you can think of. For example, here's the formula to calculate your BB/h (based on a table layout like the one above):
- =PRODUCT(_(F2/(RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE (C2,"-","*",LEN(C2)-LEN(SUBSTITUTE(C2,"-","")_)_)_)_)_)/B2)_)
*NOTE: All underscores ( _ ) need to be removed for this formula to work.
This formula is a fancy way of doing this: ((Result/BB)/Hours). All the stuff in the middle is just to get the big blind (or big bet if you're playing limit) from the Limit field. If you had two fields (Low-Limit and High-Limit) this formula would be much simpler. Regardless of how you choose to do it start keeping track of every session you play. If you already do keep track of your sessions, start tracking more info about them. Used logically and truthfully the reports you generate will present you with practical ways to maximize your ROI.
Excel Tricks for Poker Stat Tracking
A block of solid data can be hard to read at a glance and forces the reader to invest time comprehending what they're actually reading. Since we're lazy, here's how to add some formatting to make the spreadsheet easier to read at a glance.
In this first picture you'll see negative values are in red. This is done with standard formatting which you likely know how to do. If you don't know how to format the text in a cell you need to spend some time with Excel, or pick up a beginners guide. This is better than nothing, but why not make it even more obvious? By using the conditional formatting tool we can change the red text to this:
To do this you need to highlight the cell(s) you want to format and choose "Conditional Formatting" from the main "Home" toolbar. Now we want to highlight the boxes if they're negative, so choose "Highlight Cells Rule" then "Less Than." Then put 0 in the text box and choose red as the color.
Using the Excel Drop Down Box
For some entries you only ever choose one from a small selection of options. Rather than type these in each time (which can result in formatting errors and typos, which can break calculation codes), we can create a drop down box with your selection choices. The easiest way to do this is to use a named range. The first thing you need to do is click on a tab at the bottom of your spreadsheet to go to a new page of the same document. As you can see my document has three parts:
We want to focus on the data page for this task. The first thing you want to do is create a list with all the values you want. Even though it's not required I recommend you do the same. Click on the column header and where is says B1 write in the name of that list; I chose Limits for the Limits List.
Now go back to the sessions tab. Click on the box (or boxes) where you want the drop down and choose the data tab from the main toolbar. Click on the option named "Data Validation."
From here change the Allow field to "List" and in the source box write =Limits. If you named your range something else replace =Limits with =Whateveryouchoseasyourname. Now you'll have a dropdown with everything in that named range. If you like you can enter an input message when in the data validation dialog box. That will show up like this:
Conditional Equations, or IF Statements
If you're anything like me having all of your calculated fields defaulting as 0 or #VALUE! will annoy the shit out of you. I'm really not sure why M$ hasn't come up with a more elegant solution for this problem yet but in the meantime we're going to use a simple IF statement. For the programmers out there this will be a breeze. For the rest of you don't worry, it's easy. Here's how IF statements work in Excel:
=IF(condition,output if true, output if false)
Here are some other things you need to know:
= : Equal to
<> : Not equal to
"" : Null, or no input
AND(condition1,condition2) : Both conditions must be true to return a true result
OR(condition1,condition2) : Either of the conditions must be true to return a true result
So, using the result field as my example, the equation in the box reads as:
This means that if both F2 and G2 has anything in them (they <> (do not equal) "" (blank)) then output G2 minus F2 to give us our result. If either box is empty, a standard formula will return an error, so with this IF statement it returns "" (blank) instead. Excel is able to do very detailed equations, in my Reports page I use nested IF statements, such as this:
As you can see, you can make it as complex as you like, depending on how fancy you want to get. The SUMIF function is a pretty useful little tool. Here's how it works:
=SUMIF(range to pick from, condition, range to add up)
Range to pick from - range of cells to use in formula, typically A2:A11, or a named range like I used.
Condition - Anything you want. If you wanted just results for one limit your condition would be "$2-$5".
Range to Add Up - This is what actually gets added. The range to pick from are dates, but we want to add up the results for those dates.
As you might have expected this is only scraping the surface of what you can do with the program. But for formatting poker stats, this is about all you need to know. If you have any questions, drop them in the comments and I'll see what I can do.