Stat Tracking: Excel Formatting and Tricks

Share:
4 February 2010, Created By: Sean Lind
Stat Tracking: Excel Formatting and Tricks

Yes, I know there are lots of online or downloadable programs for tracking your poker results, but if you’re old school like me, or just like to do shit yourself, this is the article for you.

Excel is a far more powerful of a program than most people know. It has the ability to do things that you’ve never even thought of.

For example, I once worked as a tech-guy/website developer/network engineer… read GEEK for a large retail company.

One aspect of this company is having multiple locations across Canada and the U.S.A, all with their own inventory and full service physical locations. The business also makes a lot of money from its website selling the same stuff, but online.

Now, to keep shipping times and costs as low as possible, all physical locations ship product for web orders, rather than having one warehouse dedicated to online ordering. This is a cost effective way to operate, but requires some in-depth solutions to inventory management and control.

To manage this we created an excel spreadsheet which automatically updated with the actual inventory count for each product for each store location.

It did this buy parsing the accounting software end of day sales figures. On top of this it stores months of sales results allowing forecasting and trending of product sales, all updated automatically and fully configurable.

If Excel can do something like this, it can certainly handle your poker stats with flying colors, you just have to learn how to use it. Here are some quick tips and tricks you can use to make your spreadsheet a little more rad.

 

Conditional Formatting

A block of solid data can be hard to read at a glance, and forces the reader to invest time comprehending what he's actually reading. Since we're lazy, you want to add some formatting to make the spreadsheet easier to read at a glance.

conditional before

In this first picture you'll see negative values are in red, this is done with standard formatting, which I'm sure you all 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 article is not for you.

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're going to want to highlight the cell(s) you want to format and choose "Conditional Formatting" from the main "Home" toolbar (this is in Office 2007. For earlier versions it will be in the toolbar somewhere. Go and find it).

Now, we want to highlight the boxes if they're negative, so choose "Highlight Cells Rule" then "Less Than". Then it's simple, put 0 in the text box, and choose red as the color.

Another thing you can do is highlight cells along a gradient scale depending on the value of their amounts. Here's an example using BB/h. I set the scale as red for -50 (or beyond), blue for 0 and green for +50 (or beyond) resulting in something like this.

To do this you need to choose "Conditional Formatting" then "New Rule". In the input box change 2-color scale to 3-color scale, set the values to numbers, input your numbers, choose colors and you're done.

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:

  • Sessions
  • Reports
  • Data

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. As you can see I have the title for my lists to the left of each list, with an ascii arrow. I did this to make my life easier. Even though it's not required I recommend you do the same.

Now, 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:

drop down box msg

Getting Rid of 0 Filled Boxes (Conditional Equations, or IF Statements)

If you're anything like me (and if you're reading this blog, I assume you are) 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:

=IF((AND(F2<>"",G2<>"")),(G2-F2),"")

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:

=IF(B4<>"",(SUMIF(DatePlayed,B4,Results)/SUMIF(DatePlayed,B4,Hours)),IF(A4<>"",(SUMIF(YearPlayed,A4,Results)/SUMIF(YearPlayed,A4,Hours)),""))

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.

-Lind

Share:

Please fill the required fields correctly!

Error saving comment!

You need to wait 3 minutes before posting another comment.

Wavey 2014-08-17 10:14:25

Out of date and links don't work.

Chaz L 2010-04-16 16:29:12

This article warmed my heart. Built an Excel based tool for predicting hand ranges back in November. Given your affinity for SUMIFs, I have a feeling you might find it interesting. Check it out at http://pokeitmethod.com/

Ron L 2010-02-12 14:43:34

Thanks for your reply! You've brought up some stuff for me to think about and play around with. (You dern near wrote an article for your reply. I really appreciate it!) There's a good chance I'll eventually be picking up HEM or PokerTracker, but for now I'll see what I can play with.

Thanks again!

Sean Lind 2010-02-11 22:50:35

Also,

The easiest way to get back stats for things such as "how often do I fold before the flop" is to have a field secifically for that in your database. I would create a field called "finalStreet" and have it filled out with

0-preflop
1-flop
2-turn
3-river
4-showdown

That way you can get % results (or build a graph) out of specific hands, how often you fold, how often you make it to showdown etc.

Also this will allow you to get much more in-depth with your other queries.

Unfortunately, since this data isn't plainly laid out in the txt files, you'll have to create your own import script with rules, something like:

IF ($meAction == "Folds")
{
Switch ($lineCount)
{
case <10:
$finalStreet = 0;
break;
case >10<20:
$finalStreet = 1;
break;
}
$query = "UPDATE handsDB SET finalStreet=".$finalStreet." WHERE handID =" $handID;
}
ELSE
$query = "UPDATE handsDB SET finalStreet=4 WHERE handID =" $handID;


Something like that. Although that was a simple (incomplete) example in PHP (since PHP is what I remember best at this time). You'd probably want to create a simple VB Script to do the same thing.

NOTE: My php script above isn't being displayed as it was written, apparently I've successfully broken the comment code on my own site with PHP code in the comments... awesome.

Like I said, this is a serious can of worms. If you simply parse data on CSV values, you're going to be unable to do a lot of the queries you'd like to do.

In short, unless you really like to spend your time doing geek projects like this, $60 for HEM is a pretty good deal. This will take you 5-40 hours, depending on your level of geek, so you really need to assess what your time is worth. If you're so much of a geek that you can get this done in $5 or less, chances are you're worth a lot more than $12 an hour.

Sean Lind 2010-02-11 20:56:28

Ron L,

You really are opening a can of worms on this one. The goal here is to walk the line between doing things efficiently (and effectively) without having to put in far too much work.

Firstly, although excel will work for all data collection, accumilation, and parsing, I think it would be daft to do it this way.

Since I'm assuming if you have Excel, you also have Microsoft Access?

If you're going to be inputting hand history information to be parsed and queried, you should use Access to save all of the information. The reason for this is simple:

Access is an entry level database program designed to store, index and sort massive amounts of information. Although Excel will do most of this, Excel is simply not designed for the data load you're going to put it under, causing lag and problems down the road.

Once you create a database, Access will import your text files the same way Excel would, using tab or comma delimitation to put the appropriate values into the appropriate fields.

Once you have that working, you can link the access database into excel, and use excel for all your front end queries and reports.

Regardless of if you use Access or not, you're going to be using mostly the SUMIF() and AVERAGEIF() functions in excel.

This way you can get the profit/loss for any specific category you want (as long as you have your data parsed properly). Getting results based on limits, games, opponents etc. will be easy. Also if you parse your data into as many feilds as you can think of, you'll have more options.

Results by hand, hand vs hand comparisons, positional queries. You name it, it can be done.

This is not a project for the faint of heart, and what I'm explaining is exactly what programs like HEM do. They use a custom import script to parse the txt files and input the values into a DB (HEM uses postgreSQL while you'll use Access), then instead of Excel, they have created their own GUI which simply uses SQL queries to return values to the user.

Excel saves you having to code a GUI, Access saves you having to read SQL or mySQL for dummies.

I'll think about creating my own "hack-stat" how-to using Access and Excel, but it's a lot of work, I'll have to see if there's enough interest first.

The good news, it's not all that difficult to do, and you can literally get all of the same reports from Excel as you can get from HEM. HEM and Poker Tracker use the same formulas and ideas that we've already talked about, they just do it in a more user-friendly and polished manner.

Ron L 2010-02-11 13:21:48

Please forgive me for opening this can of worms.

I've been reading and enjoying your Excel articles. You've been giving us a lot of great information. I for one really appreciate them!

In this article, you mention but don't cover Parsing. Yes, you've probably guessed it by now, I'm asking about hand histories.

I'm just a beginner, and I don't have all that many histories built up. I don't have the $60+ to spend on a nice program to pull out all of the information I'd ever want, either. I'm just looking for some basic information like:
- How many times do I fold before the flop?
- How many times do I win before the flop?
- How many times do I fold before the River?
- How many times do I win before the River? ...after the River?

...etc.

I'm not ready to pull out quantities of chips just yet, I'm just looking to see how tight / loose I'm playing. I can figure out how to do more when I have more data later on. For this stuff, it seems like I just need to count the number of occurrences of certain strings from each hand summary. I know how to import text and use delimiting to make columns, and I can do a little more fancy stuff with Excel.

The question is how do I pick out and count the strings I'm looking for? Or what's my starting point? What function do you recommend using? Or is there a better way?

If you can point out any good resources, I'll be happy to do my own research. (Yes, I know about the tutorials at the Microsoft site - they're a pain in the *bleep* to search through.) If it's of any interest, I'm playing on PokerStars and automatically saving my hand histories to my computer.

Thank you!

PS - just a suggestion, but maybe you could write a series on computing stats, or maybe PokerListings could set up a whole Strategy section on stat computation.

Bloggers
The Poker Reporter Blog

The Poker Reporter Blog

Poker news, gossip, parties, donkstrikery and functioning illiteracy with the PL.com crew!
Latest Posts 's Page  
WSOP Blog

WSOP Blog

WSOP live blog from the tournament floor w/ poker pro interviews, photos and side action from Las

Latest Posts 's Page  
The Guest Blog

The Guest Blog

A menagerie of poker pros, celebrities, poker writers and industry figures.
Latest Posts 's Page  
Battle of Malta Blog

Battle of Malta Blog

Photo essays, player profiles, travel tips, off-the-cuff interviews and more from Battle of Malta!
Latest Posts 's Page  
×

Sorry, this room is not available in your country.

Please try the best alternative which is available for your location:

Close and visit page