Spreadsheet for session records

[guidoguru]

Does anyone have a good spreadsheet that they use to keep track of their poker sessions? Im not very familiar with excel, otherwise i'd make one. Im looking for one that will calculate win rates at different games etc.

[megabit]

I put all of my data into SQL Server and then run queries against it to get those answers. I think a database is the appropriate tool for the task, if using excel is a challenge thou SQL Server is likely out of the question. /images/graemlins/smile.gif

If you are interested I could post the table structure I use and the stored procedures that tally the various results.

Mike

[guidoguru]

that would be awesome if you could post that. and it's not that excel is a "challenge", i just have never played around with it at all, and im feeling lazy. /images/graemlins/smile.gif but rest assured, i am an uber-dork through and through. i dream in BINARY!

[megabit]

Ok, here it is in all it's SQL glory:

/* This SQL DDL script was generated by Visio Enterprise (Release Date: LOCAL BUILD). */

/* Driver Used : Visio Enterprise - Microsoft SQL Server Driver. */
/* Document : C:\Program Files\Common Files\System\MAPI\1033\nt\drawing1. */
/* Time Created: January 09, 2004 9:25 AM. */
/* Operation : From Visio Generate Wizard. */
/* Connected data source : No connection. */
/* Connected server : No connection. */
/* Connected database : Not applicable. */

-- sp_who2
-- kill 55
/* Create Ledger database. */
use master

go

drop database ledger
go

create database Ledger

go

use Ledger

go

/* Create new table Game. */
/* Game : Table of Game */
/* GameID : GameID identifies Game */
/* Name : Name is of Game */
/* Type_Stakes : Type_Stakes is of Game */
create table Game
(
GameID int identity not null,
Name char(25) null,
Type_Stakes varchar(50) null
)

go

alter table Game
add constraint Game_PK primary key (GameID)


go

/* Create new table Location. */
/* Location : Table of Location */
/* LocationID : LocationID identifies Location */
/* Location : Location is of Location */
/* City : City is of Location */
/* State : State is of Location */
create table Location
(
LocationID int identity not null,
Location char(40) null,
City char(25) null,
State char(2) null
)

go

alter table Location
add constraint Location_PK primary key (LocationID)


go

insert Game values ( 'Hold-Em' , 'Tournament Limit' )
insert Game values ( 'Hold-Em' , 'Tournament No Limit' )
insert Game values ( 'Hold-Em' , 'Limit 1 2' )
insert Game values ( 'Hold-Em' , 'Limit 2 4' )
insert Game values ( 'Hold-Em' , 'Limit 3 6' )
insert Game values ( 'Hold-Em' , 'Limit 4 8' )
insert Game values ( 'Hold-Em' , 'Limit 5 10' )
insert Game values ( 'Hold-Em' , 'Limit 6 12' )
insert Game values ( 'Hold-Em' , 'Limit 8 16' )


insert Location values ( 'Pechanga' , 'Temecula' , 'CA' )
insert Location values ( 'Oceans 11' , 'Oceanside' , 'CA' )
insert Location values ( 'Casino Puama' , 'Puama Valley' , 'CA' )
insert Location values ( 'Sycuan' , 'El Cajon' , 'CA' )
insert Location values ( 'Viejas' , 'Alpine' , 'CA' )
insert Location values ( 'Colorado Belle' , 'Laughlin' , 'NV' )
insert Location values ( 'Bicycle Club Casino' , 'Bell Gardens' , 'CA' )
insert Location values ( 'Commerce Casino' , 'Commerce' , 'CA' )
insert Location values ( 'Hustler Casino' , 'Gardena' , 'CA' )
insert Location values ( 'Lake Elsinore Resort and Casino' , 'Lake Elsinore' , 'CA' )

go

/* Create new table Ledger. */
create table Ledger
(
ID int identity not null,
LocationID int null,
GameID int null,
BuyIns money null,
CashOut money null,
Start datetime null,
"End" datetime null,
TablesStart int null,
TablesOut int null,
Notes varchar(400)
)


go

alter table Ledger
add constraint Ledger_PK primary key (ID)


go

/* Add foreign key constraints to table Ledger. */
alter table Ledger
add constraint Location_Ledger_FK1 foreign key (
LocationID)
references Location (
LocationID)

go

alter table Ledger
add constraint Game_Ledger_FK1 foreign key (
GameID)
references Game (
GameID)

go

go


/* This is the end of the Visio Enterprise generated SQL DDL script. */

[megabit]

I wrote a very crude data entry program in dot net too, but I wouldn't feel right giving it out as it is quit crude. I do most of the real work in Query Analyzer.

Getting anything you want back out of it is just a mater of writting the proper query.

Here is my basic get results query:



-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'CMSP_GetResults'
AND type = 'P')
DROP PROCEDURE CMSP_GetResults
GO

CREATE PROCEDURE CMSP_GetResults
/************************************************** ************************************************** *******
** Name : CMSP_GetResults.SQL
** Description : This is a Stored Procedure
** Author : Mike
**
** Change Log
** Date By Notes
** ---------- ---------- ---------------------------------------------------------------------------
** 09/29/2004 MikeT Intial Dev
**
**
************************************************** ************************************************** *******/

AS
BEGIN



select result = sum(result)
,hours =sum(lenght) / 60
,[$PerHour] = sum(result) /(sum(lenght) / 60)
, type_stakes
from
(
select result = (buyins * -1) + cashout,
lenght = datediff(mi, start, [end]),
gameid
from ledger
) t
join game g on t.gameid = g.gameid
group by name, type_stakes, g.gameid
order by 3

select Overall_Result = sum(result)
from
(
select result = (buyins * -1) + cashout, gameid
from ledger
) t
join game g on t.gameid = g.gameid


END --CREATE PROCEDURE CMSP_GetResults
GO

[megabit]

By the way I leave for a week on the High Seas in Baja with Card Player Cruises. I'll try to remember to check this thread when I get back. You can PM me if I don't respond in a week or so.

Good Luck,

Mike

[timmer]

GOTO www.conjelco.com (http://www.conjelco.com) and buy StatKing

you wont be sorry

timmer

[CurryLover]

www.pokercharts.com (http://www.pokercharts.com) is what I use for keeping my records. It's free to use and calculates your hourly win rate and things like that. You can export the data easily as well, so if you decide to buy another program like StatKing, or design your own posh spreadsheet, you will not need to re-type everything.

[billuhbong]

how the hell do u use that sql script? i wanna use it but i have no clue how to put that into access or excel or wutever it requires

[franco]

wanted to try pokercharts. are you afraid they might use your informationto play against you?

[Rasputin]

[ QUOTE ]
how the hell do u use that sql script? i wanna use it but i have no clue how to put that into access or excel or wutever it requires

[/ QUOTE ]

What version of Access do you have?

I'm considering using that script myself in access, which would require a little modification, and could post the modified version after.

If I do it, which I'm not sure about.

[CurryLover]

[ QUOTE ]
wanted to try pokercharts. are you afraid they might use your informationto play against you?

[/ QUOTE ]

No. I only keep B&M records since I don't play online seriously. Even if I was a regular online player I think I'd resist that particular paranoia.

[TimmyMayes]

Does anyone know of palm os software for this. A poker stats tracker, not spreadsheet I already use documents to go to keep track of some of this stuff.

Cheers,
Tyler

[Suited Deuce]

I like www.pokercharts.com (http://www.pokercharts.com), it's free and easy to use. Eventually I may go to StatKing.

[SheridanCat]

If you're happy with Poker Charts, there's really no need to switch to StatKing. I use StatKing and it's nice because it's very quick to input into, but I don't see a lot there that you don't see on Poker Charts.

Regards,

T

[megabit]

[ QUOTE ]
how the hell do u use that sql script? i wanna use it but i have no clue how to put that into access or excel or wutever it requires

[/ QUOTE ]

The script is for SQL Server. It might be possible to make it work with Access but I don't have it installed to try. You could go to the MSDN (http://www.microsoft.com/downloads/details.aspx?familyid=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en) website and get the desktop version of SQL Server (free) then the script should run. Remember you will still need to get the data into the database, that will require creating a user interface. I may try to clean up mine to the point were I would feel ok giving it out. Problem is I'd rather be playing poker than writting more code. /images/graemlins/smile.gif

Mike

[EvlG]

Timmy,

I use a program called Poker Journal. It's free, and even hotsyncs into CSV files that are roughly compatible with StatKing! I don't use that feature, however, as I found that the StatKing importer can be very buggy. It almost hosed my database completely.

Instead, about once a week, I sit down for a few minutes and manually transfer the data from the CSV files to StatKing.

But having the Palm program is very useful to manage B+M play, or even online multitabling.

You can download it here (http://poker.ddeuce.com/)

[ricdaman]

If you really want an excel one, go to http://www.scott4jesus.com/ricdaman/files/ and you can use the one I created for myself. One is for cash games, the other is for tournaments. What I do is make multiple copies of the Poker Log, and use one for each site I play on. That way I know exactly how much money I have in each of my accounts, and I also know how much I am making at each site.

HOW TO USE:

Keep the areas in light blue up to date each day. If you did not play in a given day, leave the Hours and Minutes columns at 1 and 0 respectively. Update the medium blue Start column only if you cashed out from or deposited to the site. Everything will carry over automatically from day to day and month to month. The only thing that will not carry over are the parts in Orange. These need to be updated manually at the beginning of each year.

TO GET STARTED:

Set it for the current year:
Type in the first day of each month (for January, type in 01/01/2005). Press Enter. Put your mouse over the bottom, right corner of the box for the first day of the month. Click and drag your mouse to the last day on the sheet (A2 --> A32). Excell will automatically update all of the days for you. Do this for each month.

Update the parts in Orange:
Specifically, type in your winnings / losses for all time, and the amount that you have in your account.

Everything else should be self explainitory, but if you have any questions, feel free to PM me.

DISCLAIMER: I MAKE NO PROMISES, EXPRESSED OR IMPLIED, AS TO THE ACCURACY AND FUNCTIONALITY OF THIS PROGRAM. IF IT DOESN'T WORK, OR IF IT MESSES UP YOUR COMPUTER, IT'S NOT MY FAULT.