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. [img]/images/graemlins/smile.gif[/img]

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. [img]/images/graemlins/smile.gif[/img] 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 and buy StatKing

you wont be sorry

timmer

[CurryLover]

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?