Page 1 of 1

FAQ: Custom Statistics and Reports

PostPosted: Fri Jun 20, 2008 6:35 am
by WhiteRider
Please see the Documentation for Custom Statistics and Reports.

Here are a few links to posts that I and Kraada have written:

Really good walkthrough for C-Bet success stat by Kraada.

In Position on the flop:

Kraada's Full Tilt Rakeback stat. ... p?t=227628

Tournament net amount of money won

Fold to flop cbet after defending blinds:

Not a walkthrough, but a discussion with attached built stats for preflop limp/fold.

Seeing 'normal' player stats per session - building a custom report with a row for each session:

Stats are split into sections which can be used in different types of reports/situations.
to use a stat on the HUD it must be of type 'Holdem Cash Player Statistics' - this is also the section for most reports.
to use a stat on the Known Starting Hands Detail report it must be of type 'Holdem Cash Hand'.

Discussion of how a stat from the Repository is constructed.

How to filter a custom report to show lifetime stats for players who have played hands 'today'.

Hole card database field values
I wrote a post about the database values which store hole cards:

id_x_hand_strength field values
Kraada wrote a post about the database values for the id_f_hand_strength, id_t_hand_strength and id_r_hand_strength fields in the holdem_hand_player_combinations table:

Formatting Custom Stats

PostPosted: Wed Sep 17, 2008 6:17 am
by WhiteRider
Formatting Custom Stats

format_bool( value, format )
format_bool( flg_p_ccall, 'check' )
..shows a boolean (true/false) value as a checkbox.

format_number( value, decimal places, commas, color )
format_number( value, 1, false, false )
..shows a number (value) with 1 decimal place, no commas, not colored (green for positive, red for negative).
'value' will normally be something like:
(cnt_p_ccall / cnt_p_ccall_opp) * 100 which case this will be a % formatted to 1 decimal place.

This is more commonly done by entering this in the Format Expression field:
..which formats the value of the statistic to 2 decimal places.

format( format_str , ... )
Now here's where it gets interesting. (if you like that kind of thing)
You can add as many numbers as you want to a formatted string, by adding {1}, {2}, etc. These will be replaced by the first, second, etc. value you add instead of the ...

This allows you to format strings with numbers in them, so you can construct stats with multiple values.
e.g. If a=37 and b=19, this Format Expression:
format( 'a={1}, b={2}', a, b )
..would give a stat output of:
a=37, b=19

or, more realistically..
format( 'You have played {1} hands and been in the blinds {2} times', cnt_hands, cnt_blind )
would look like:
You have played 100 hands and been in the blinds 25 times

Showing number of opportunities
And better still, you can use this to construct stats showing a percentage and counts as a fraction in one stat, like this: 75% (3/4).
The format string for this is a little complicated. This is for cold calls again, and I've formatted to 0 decimal places.
format( '{1}% ({2}/{3})', format_number((cnt_p_ccall / cnt_p_ccall_opp) * 100 ,0,false,false), cnt_p_ccall, cnt_p_ccall_opp )
Which would give an output like:
33% (3/9)

If you want to try this - select the CCPF stat in 'holdem cash player statistics' section of the Configure Stats window, and click the 'Dup' button to make your own copy you can edit.
Rename the stat, e.g. CCPF2, then click the 'format' tab and replace the content with the above format string.
Better still, experiment with your own version so you understand how it works.

NOTE: This formatting still works and is still useful for reports but you can now format stats like this in the HUD without needing to do any formatting in the stat itself (as described here). Instead you can set the 'Show Times/Opportunities' property to True in the HUD Statistic Properties.

Further formatting cleverness
-- 1 --
I stumbled across a useful little formatting trick related to use of checkbox (boolean) stats while experimenting with blind steal success.
I wanted some way to show when I'd attempted a steal as well as when it succeeded, and realised that I could use an IF statement in the 'format expression' field to determine whether the checkbox was even drawn.

format expression = if ( flg_steal_attempt, format_bool(flg_steal_success,'check'), '' )

This checks to see if a steal was attempted:
"if ( flg_steal_attempt"
..and if so it draws the checkbox to show whether the steal succeeded:
..but if not it doesn't show anything (empty single quotes):
So if a steal was attempted and succeeded you get a checked checkbox, if a steal was attempted but failed you get an unchecked checkbox, if no steal was attempted you get an empty cell.
This makes the instances that are relevant stand out much more clearly.

-- 2 --
You can do a similar thing with 'normal' numeric stats.
Some stats are quite rare and will sometimes have zero opportunities (as an example, cold calling preflop, and then betting the flop if checked to) - and looking down a long list of these it's easy to miss a case where the stats are 0/1 instead of 0/0 (even if you have formatted the stat "p% (x/y)" as described above). My solution to this is to add a colour condition to check for zero opportunities and colour the stat in pale grey so that those cases with non-zero opportunities stand out.

See this post for an example of this in use.

Constructing Percentage Stats - Sum( if[ ... ] )

PostPosted: Wed Sep 17, 2008 7:40 am
by WhiteRider
Constructing Percentage Stats

Quite a lot of the Statistics in the 'Holdem Cash Player Statistics' section show the percentage of times a player takes a certain action.
The way these stats are constructed can appear a bit complicated at first glance, so I'll try to explain how they are made.

We'll start from the end and work backwards, so that you can see the reason we're doing what we do.
I'll use the built-in "3bet preflop" stat as an example, because it's very simple and shows things very clearly.

1. The final stat shows the percentage of times a player makes a 3-bet preflop out of the opportunities to 3-bet (i.e. when they face a raise).
To calculate the percentage, we divide the number of times they 3-bet by the number of times they had the chance to 3-bet. To get the percentage we just multiply the fraction by 100.
( times 3-bet / opportunities to 3-bet ) * 100

2. We can't access the database fields directly from a Statistic, so we have to build a Column to give us each of the counts ("times 3-bet" and "opportunities to 3-bet").
These Columns are called "cnt_p_3bet" and "cnt_p_3bet_opp".
The names of the Columns are by convention.
cnt_ means it's a count; "p_" means it's a preflop stat; "3bet" is what the stat is about; "_opp" means opportunities.
Open the Custom Statistics window (Configure -> Configure Stats), click 'Holdem Cash Player Statistics', then click the Statistics tab; click "3Bet Preflop" in the list on the left.
You will see the Statistic's 'Value Expression' is defined as:
(cnt_p_3bet / cnt_p_3bet_opp) * 100

3. Now to construct the Columns "cnt_p_3bet" and "cnt_p_3bet_opp".
They are basically built the same way, so I'll just discuss the first one: "cnt_p_3bet".
What we need to do is find out how many of the player's hands he 3-bet with preflop. In the 'holdem cash player statistics' section, the 'Expression' will run through every hand the player played, so we need to count how often the database field which stores whether or not the player 3-bet is set to 'true'.
The way we do this is using the "Sum" and "if" Functions.

4. Let's start with the 'if' part. The structure of the 'if' function is:
if [ expr, a, b ]
This evaluates 'expr', and if it's true then the if statement evaluates to the value 'a', if it's false it uses the value 'b'.
In cnt_p_3bet you can see that the Expression is:
if [ holdem_hand_player_statistics.flg_p_3bet, 1, 0 ]
So this is saying, if the database field "holdem_hand_player_statistics.flg_p_3bet" is true we will use the value 1, but if it's false we will use the value 0.
Remember, this is evaluated for every hand for the player, so we will end up with a load of 1's and 0's; where:
(number of 1's = how often the player 3-bet)
(number of 1's) + (number of 0's) = (number of hands)

5. To give us the count of how many hands the player 3-bet, we sum up the 1's, using the Sum function.
(It's not actually a count, we're adding up the 1's and 0's - you can use Sum to add other numbers too, but I'll explain that at the end.)
The structure of the 'Sum' function is:
Sum( val )
This will add together 'val' for every hand the player played. In our example val = if [ holdem_hand_player_statistics.flg_p_3bet, 1, 0 ], as discussed above, so the full Expression for cnt_p_3bet is:
sum(if[holdem_hand_player_statistics.flg_p_3bet, 1, 0])

Click the Columns tab and find "cnt_p_3bet" in the list on the left, and select it to see this expression.

6. Similarly, the Expression for the second Column, cnt_p_3bet_opp is:
sum(if[holdem_hand_player_statistics.flg_p_3bet_opp, 1, 0])

7. As an example, let's say you play 3 hands (h1, h2, h3).
h1 is folded to you and you fold too. Both holdem_hand_player_statistics.flg_p_3bet and holdem_hand_player_statistics.flg_p_3bet_opp are false, so the 'if' statement for both Columns evaluates to 0.
h2 there is a single raise to you, and you re-raise (3-bet). Both holdem_hand_player_statistics.flg_p_3bet and holdem_hand_player_statistics.flg_p_3bet_opp are true, so the 'if' statement for both Columns evaluates to 1.
h3 there is a single raise to you, and you fold. holdem_hand_player_statistics.flg_p_3bet=false, so flg_p_3bet:val=0, but holdem_hand_player_statistics.flg_p_3bet_opp=true, so flg_p_3bet_opp:val=1.
cnt_p_3bet = Sum( 0, 1, 0 ) = 0 + 1 + 0 = 1
flg_p_3bet_opp = Sum( 0, 1, 1 ) = 0 + 1 + 1 = 2.
Then the Statistic's Value Expression =
( cnt_p_3bet / cnt_p_3bet_opp ) * 100 = ( 1 / 2 ) * 100 = 50%


Example 2:
As an addition, to further clarify use of the "Sum" function, let's say you want to work out the total amount of money you have put in preflop, across all hands. This is stored for each hand in the database field "holdem_hand_player_detail.amt_bet_p"
so the Column you would construct would be:
amt_bet_P_total =
Sum ( holdem_hand_player_detail.amt_bet_p )

Example 3:
Let's say you want to know how much you put in preflop when you have the opportunity to open the pot. You could use an if statement to determine whether you had the opportunity to open the pot:
if [ holdem_hand_player_statistics.flg_p_open_opp, ...
Now if you opened the pot we want to add how much you put in preflop, but if you didn't then we want to add zero, so we finish off the if statement using a=holdem_hand_player_detail.amt_bet_p and b=0, thus:
if [ holdem_hand_player_statistics.flg_p_open_opp, holdem_hand_player_detail.amt_bet_p, 0 ]
Then we use "Sum" to add all the values up:
amt_bet_p_with_open_opp =
Sum( if [ holdem_hand_player_statistics.flg_p_open_opp, holdem_hand_player_detail.amt_bet_p, 0 ] )

Using the Repository

PostPosted: Tue Oct 14, 2008 8:23 am
by WhiteRider
Custom Stats and Reports can be shared with other users via the Repository.

Importing from the Repository
Once you have downloaded a Custom Stat or Report and saved the file on your computer, you need to import it into PT3 before you can use it.
If the file you downloaded is compressed (.zip for example) then you must extract the stat files first. Right click the file and choose 'Extract All'.

To import a Report, go to the Texas Holdem -> Cash (or Tournaments) -> Reports tab, and click the 'Import' button in the bottom left.
Once the Report has imported, a message box will tell you which section the Report is in, and it will then be selected so that you can run it using the 'Run' button.

To import a Statistic, go to Configure -> Configure Stats -> Statistics tab, and click the 'Import' button in the bottom right.
When you have imported the file you downloaded, it will tell you which section the Stat is in.
If you want to examine the stat you have imported you then need to go to the 'Sections' tab and select the relevant section before going back to the 'Statistics' tab and finding the new stat in the list.
Once the stat is imported you can add it to reports of the correct type (e.g. you can only use a 'holdem cash player statistics' stat in reports which show player statistics, not reports showing individual hands or session totals); or you can add it to the HUD the next time you start it (again, only if it is a 'holdem cash [or tournament] player statistics' stat).

For information on adding your newly imported stat to the HUD please see the Advanced HUD Guide or the Tutorial: Managing HUD Groups.

Exporting Custom Statistics and Reports
Once you have a stat that you are happy with, and want to keep a copy of or send to a friend or the Repository, you can click the 'Export' button (on the Statistics tab) and save the file.
Similarly for Reports, just click the 'Export' button on the Reports tab.

If you have made a stat or report that you want to share, please Export it and visit the 'Upload' section of the Repository to submit it.
They have to be manually approved, so please be patient!

Using sub-strings

PostPosted: Sat Oct 18, 2008 3:32 pm
by WhiteRider
A new mechanism has been added which allows you to compare and format using substrings (shortened parts of strings).
The format of this command is:

substring( <string> from x for y )

You can only use this in the 'Columns' tab of Custom Stat creation, not the 'Stats' tab.

For instance, if you want to see the first few characters of players' names on the HUD, you could create a Custom Statistic.
First create a new column, duplicated from the str_player_name column.
str_player_name_short = substring( player.player_name from 1 for 5 )
This starts at the first character, and includes 5 characters.
You would then create a new Statistic by duplicating the 'Player' stat and use this column instead.

NOTE: using this column directly in a Statistic means (for an as-yet unknown reason) that the Statistic isn't available in the HUD.
However, you can create a Variable to access the Column and then a Statistic to access the Variable and this will then work in the HUD.
(Thanks to jgberaud in this thread)

This stat is attached here:
(415 Bytes) Downloaded 423 times

You could also use this to check for player names starting with certain characters.
e.g. you could do this:
if [substring( player.player_name_search from 1 for 3 ) = 'abc', doThis, elseThis]

Another new database field is:
..which contains a string of the actions you made on each street.
Actions are: X=check, F=fold, C=call, B=bet, R=raise.
So, for instance if you want to count hands where you check-raised on the flop, you could do this:
sum( if [substring(lookup_actions_f.action from 1 for 2) = 'XR', 1, 0] )

There is another way to check parts of strings which is slightly 'neater'.
You can also check like this:
if [lookup_actions_f.action LIKE 'abc%', doThis, elseThis]

One advantage with this is that you can also check the end of the string without knowing how long it is, such as if you want to know what your last action of a round was.
lookup_actions_p.action LIKE '%R'
..will identify when you were the preflop aggressor. (If your last preflop action was a raise, then you were the preflop aggressor, unless you raised all-in).

You can also do things like:
lookup_actions_p.action LIKE '_R' to see when your second action was a raise.

See the Postgres documentation for more information.

Building a pair of stats for player and hand

PostPosted: Wed Mar 18, 2009 10:52 am
by WhiteRider
Most stats that we build are in the Holdem Cash Player Statistics (HCPS) section as these are the stats that can be used in the HUD and in reports about players' overall stats.
All of the following applies to the appropriate Tournament sections too, but I will just describe the cash case for simplicity.

When you build one of these stats you will want to check that it is giving you the correct results and I find the best way to do this is to create a matching stat in the Holdem Cash Hands (HCH) section.
This pair of stats will be based on the same expression, but the way they are constructed is slightly different.

HCPS stats are often based on the number of times a certain action or event occurred, out of the number of times it could have happened, across all of the player's hands. This is normally displayed as a percentage. (You can build other types of stats here too, but these are most common.)
The construction of these 'percentage' stats is described in detail earlier in this thread.

HCH stats are based on individual hands and indicate whether the action or event occurred in each specific hand. This is normally displayed as a checkbox to indicate true or false.

As an example I'm going to build a HCH stat to go with the built in HCPS "Steal Success" stat.

The HCPS stat shows the percentage of times the player's steal raises "succeeded", by which I mean the raise won with no further action.
So if the player made 10 steal raises and won immediately 3 times the stat will show "30".

You can add this statistic to the built-in reports on the Positions tab, for instance - either or both the Position Statistics and Position Hands reports.
This will show how often the steal raises succeeded, but to see which 10 specific hands were the steal raises, and which 3 succeeded we need to create a stat we can show in the Position Hands Detail report.

The HCH stat will show a checkbox if the player made a steal raise; the checkbox will be checked if the steal succeeded and unchecked if it didn't.

The HCPS "Steal Success" stat is constructed like this:
( cnt_steal_success / cnt_steal_att ) * 100

The columns are:
cnt_steal_success =
Code: Select all
sum(if[holdem_hand_player_statistics.flg_steal_att AND NOT(holdem_hand_player_statistics.flg_f_saw) AND NOT(holdem_hand_player_statistics.flg_p_face_raise), 1, 0])

cnt_steal_att =
Code: Select all
sum(if[holdem_hand_player_statistics.flg_steal_att, 1, 0])

To build the HCH stat we need to recreate these columns in the Holdem Cash Hands section, but with one crucial difference.
The HCPS stats uses the sum(if[...]) construct to count how often the steal raise occurred and how often it succeeded across all the player's hands.
The HCH section works on individual hands so we don't need the sum(if[...]) part, but the rest is the same, so we create new columns:

flg_steal_success =
Code: Select all
holdem_hand_player_statistics.flg_steal_att AND NOT(holdem_hand_player_statistics.flg_f_saw) AND NOT(holdem_hand_player_statistics.flg_p_face_raise)

flg_steal_att =
Code: Select all

Note that I change the names slightly too - they are now boolean (true/false) values so by convention we name them flg_ which indicates a 'flag'.

Now to construct the actual HCH statistic we go to the Statistics tab and click 'New'.
Enter your stat name "Steal Success".
The Value Expression can be flg_steal_success - i.e. did the steal succeed or not.

The Format tab is where the real work is done. To make it really clear which hands are the ones where a steal was attempted we're only going to show the checkbox at all if a steal was attempted (i.e. when flg_steal_att is true).
So the Format Expression is:
Code: Select all
if ( flg_steal_att, format_bool( flg_steal_success, 'check' ), '' )

This says if flg_steal_att is true then show a checkbox based on the true/false value flg_steal_success otherwise show nothing (''=empty string). See earlier in this thread for more information.
Make sure you fill in the Title and Width fields as well, or the stat won't show in your reports.

The Positions tab then looks like this when I add the stats to all 3 reports:

steal success report.png

Here you can easily see which hands were steal attempts and which succeeded.
I have included the similar "Steal Attempted" stats for clarity - you will notice that there is one hand where a steal could have been attempted but wasn't, and this isn't included in the steal success stat.

I have also added "x/y" versions of the HCPS "Steal Success" and "Steal Attempted" stats to the top two reports so that you can easily see how many attempts and successes there were.
These are built by duplicating the built in stats and just changing the Format Expression. For example the Format Expression for "Steal Success (x/y)" is:
Code: Select all
format( '{1}% ({2}/{3})', format_number(100* cnt_steal_success / cnt_steal_att, 0, false, false), format_number(cnt_steal_success,0,false,false), format_number(cnt_steal_att,0,false,false) )

There is more information on formatting stats like this earlier in this thread.

Villain stats vs hero

PostPosted: Sat May 30, 2009 6:18 pm
by WhiteRider
Filtering a Custom Report to only those hands the player played against 'hero' (or any specific player)

Currently this requires an advanced filter but it will be made much more simple in the future.

Select the player you want to see stats for and go to the Reports section. Change the 'Section' drop down to 'Holdem Cash Player Statistics'. Add the stats to the report that you wish to see. Click on the 'Filters' link under the report stats list. In the 'Filter Expression' box, add the following:

Code: Select all
( 0 < (SELECT count(hhps2.id_hand) FROM holdem_hand_player_statistics hhps2 WHERE hhps2.id_hand = holdem_hand_player_statistics.id_hand AND hhps2.id_player = (SELECT p.id_player FROM player p WHERE player_name='name')) )

Replace the 'name' with your screen name, but keep the quotes around it
Save that report.
Now when you run it it will show stats based on just the hands the player played against you (or whichever player name you specified in the filter).

Aliased players

If you have aliased some of your player names together, the query is slightly different.
Again, copy the following into the 'Filter Expression' and this time replace 'name' with the screen name of the alias which actually played the villain:

Code: Select all
( 0 < (SELECT count(hhps2.id_hand) FROM holdem_hand_player_statistics hhps2 WHERE hhps2.id_hand = holdem_hand_player_statistics.id_hand AND hhps2.id_player = (SELECT p.id_player_alias FROM player p WHERE player_name='name')) )

e.g. If 'StarsPlayer' is your main alias, but you played against the villain at Party with your aliased screen-name 'PartyPlayer' then replace 'name' with 'PartyPlayer'.

villain v hero filter.png

Note: the same filters work in the Holdem Cash Hands section if you want to see the hands this player played against you.

Custom Report - All-in tournament hands

PostPosted: Wed Jul 01, 2009 2:29 pm
by WhiteRider
Custom Report - All-in tournament hands

This is a custom report example which displays all the all-in hands in a tournament by any player.

On the Tournaments > Reports tab select the 'Holdem Tournament Hands' section and add whatever stats you like - you'll want at least 'Player' and 'Hand #' with 'Amount Won' so that you can double click to access the hand history and play back the hand.
I'd suggest also "Hole 1' and 'Hole 2' so you can see the hole cards (if they are known).
    Note: You will need to add 'Amount Won' to the report or it won't filter correctly. I don't yet know why this is, but it's under investigation.

Do NOT run the report until you have added a filter - this report will show every hand from every player's point of view at the moment!

Click the Filters button above the report and on the Actions tab select Preflop > Raise All-In and Add Selected to Filters - repeat this for each street you're interested in, then on the right hand side select all of the filters and click the 'OR selected button. (you can't go all-in on the flop AND turn for instance..)
Now this will only show the hands where someone went all-in, but that will still be a huge report so go to the Touranments > Tournaments tab and double-click the tournament that you are interested in so that it shows in bold.

Go back to the Reports tab and run your report.
This should show all the all-in hands from that tournament, from the point of view of the player who went all in.

select tournament.png

report - all in hands.png