STT/SNG Spreadsheet - a beginners guide

[robilaruk]

Hi All

If you play STT's/SNG's or whatever other TLA you use there is a fantastic spreadsheet here

http://www.bluefeetpoker.com/2p2SNGspreadsheet.zip


Okay how to use it

first off you need Excel (I did say this was a beginners guide)

second off you will be asked about macro's - you need to enable them for it to work (don't worry it is not virus corrupted) - if you don't then just delete it now as it is 11meg of shite sitting on your desktop

Okay we have it open now, loads of tabs at the bottom, click on SNG1, this is our first table buy-in - each SNG sheet is for different buy-ins - this is important you can't mix $5 buy-ins with $10 buy-ins on the same tab - OK!

right I am talking cell references now, so A1 is top left hand cell.

in B5 you see base buy in - you input into D5 the cost of the tourny so 5 for a $5 tourney, next is the vigourish - this is the juice/tax/fee, so for $5 this is probably 50c, so in goes .50, next number of players in D7 (now look in cells I4-I8 and the figures have changed

If you are playing short tables then you will need to change D8 to correct places paid - you will also need to change cells G4 - G8 (so a five table on sporting pays 70%/30%) - again input 70 and 30 and 0 and the payout changes

Okay so you have now input the first SNG buy-in - click on the MONEY tab (you can circle through the tabs by using the arrows in the bottom left hand corner of the spreadsheet) you will see that in cell B3 there is Jan 1, change it to 'todays' date e.g 10/6/6 then input starting bankroll in C3 e.g. 212.33. If you look in cell L3 you notice you are already $5.50 down - don't worry, after you play your first game that will sort itself out. The rest of MONEY is fairly self explanatory you DO NOT input into any other the other cells in the L column you will balls up your sheet!!

Right back to SNG 1 - right click on the SNG1 tab at the bottom of the sheet select 'rename' (left click) and type in the buy in ($5 STT) - go back to MONEY and the SNG1 title is now $5 STT - this is IMPORTANT if you play multiple buy-in tourneys as you will see when we get to playing them......

If you play other buy ins, then select the next SNG tab, and do the same as above

Okay you have done all this (it sounds complicated but isn't) and are now ready to play you first $5 STT - when the table loads on your site click on the already opened spreadsheet (ALWAYS boot your spreadsheet first as it takes a few seconds to load) and click on ANY SNG buy in tab (it makes no difference) you will see in cells K3-K5 a big button that says START RECORDING TOURNAMENTS click on this and a pop up box appears with a selection of options - these are the names of each tab, which is WHY you have renamed them - select the tourny you are playing and the spreadsheet kicks in (this is the macro working and is why it NEEDS TO BE ENABLED)

you play the tourny and at some point you are finished - go back to the spreadsheet and the pop up box has a box at the top saying END EXISTING, if you are only playing one tourney, only one buy in is showing
and, click END EXISTING and a box appears saying ENTER PLACE YOU FINISHED, type the number (hopefully 1) and it closes - you can at this point click EXIT on the pop-up box and the spreadsheet will calculate your place (see bottom left hand corner, you see it 'calculating cells') and on the $5 STT tab it has filled in row 13 with date, day, time, duration and finish - and the MONEY tab will also update as well showing profit/loss from that tourney.

Ok - if you have 2 tourneys running and one finishes, you hit the END EXISTING button and it asks you for which tourny out of 8 you want to finish - you will need to remember which one it is - how I do it is table no 1 is positioned at the top left of my monitor, table 2 top right, table 3 bottom left etc so I know what table is finished - so type the tourny number and then you get the FINISHING POSITION box, type your position and carry on playing - DO NOT EXIT the program yet - do that after all the tourneys are finished. After EXIT it calculates all the games and changes the spreadsheet

Have you got all this? - great, don't worry about anything else at the minute - will post how to manually input other details on the extra tab later, and also about adding notes during play, and what the STATS tabs show

Lesson 1 finished

Any questions then just ask

Cheers

Damo

[GotaFancy?]

Nice one mate

I never fiddled with the buttons and did it all manually - looks like I made it difficult for myself!!! Will try with the buttons next time

[robilaruk]

my pleasure - will post other stuff tomorrow

Damo

Originally Posted by GotaFancy?

Nice one mate

I never fiddled with the buttons and did it all manually - looks like I made it difficult for myself!!! Will try with the buttons next time

[staffy]

Nice 1 Damo

[robilaruk]

Lesson two

MACRO's

the spreadsheet works on a macro (a program that perfoms actions when certain other things happen - don't ask, its programming and boring, but VERY VERY clever)

Some macro's contain a VIRUS - anyway this macro is not a virus so enable it to get the spreadsheet to work - you can do this every time you boot it, or alternatively go to (in EXCEL)

Tools
Macro
Security
choose low - it will run the spreadsheet without asking about the macro

but will also run other macro's without asking, hence the virus bollox from dodgy emails and porn sites - be it on your own head folks!!! (in other words, don't open macro's from people you don't know or that look dodgy, and get a virus checker)

The macro runs when you

start tournament and end tournament and fills in all the blanks for you

Damo

[moFace]

how do you enable macros then?

lol didnt get to last post sorry


however i mostly play mtts, is this still worth using this?

[2damnhype]

Excellent - thanks Damo

Got the macro working - just need some decent results worth recording - lol

[Oskar]

Many thanks for the spreadsheet, it looks a serious piece of kit

[Valiant23]

Problem
I have macro's allowed. I started an STT on Poker stars today, and followed your instructions as stated, before the tourney started. It lasted for 38 minutes, and when I finished (2nd for $9) I clicked on the "End Existing" button.

I expected that once I exited from that all the calculations would be taken care of, and I could start another tourney (not multi-tabling til I get used to this spreadsheet).

Despite the tourney being correctly recorded (duration, position etc.) the other tabs such as Stats and Money have not changed.

What did I do wrong?

Thanks in advance.


Ahhh. First thing I did wrong was enter "2nd" into the box. You must enter an integer.

However, the extra data still has not been recorded.

Scrap that. Its working now, thank you.

[robilaruk]

Lesson Three

click on the EXTRA'S tab - various column's (bonus/ring/rakeback etc) here - these can be renamed if you wish and it updates on the MONEY tab (so I have one for champions league, Heads up etc) - you simply manually input your buy-ins and winnings and it updates the MONEY tab and affects the bottom line

I tend to input it like this for a $20+$2 HU game

<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0 x:str><COLGROUP><COL style="WIDTH: 42pt" width=56><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2944" width=69><COL style="WIDTH: 42pt" width=56><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1365" width=32><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>date</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>net amount</TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=88 colSpan=2>notes</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: silver" height=16 x:num="38892">24-Jun</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right x:num="-22">($22.00)</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; mso-ignore: colspan" colSpan=2>$20 Heads up</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: silver" height=16 x:num="38892">24-Jun</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right x:num="40">$40.00 </TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; mso-ignore: colspan" colSpan=2>$20 Heads up</TD></TR></TBODY></TABLE>



So I input the loss (buy-in) and the winnings seperate, again it gives me an idea of how I am doing in certain areas - it works for me and it doesn't balls up your spreadsheet by you inputting the winning total alone (which should be $18, not $20 in this case - trust me its easy to input the wrong figures by being 'clever'...... ).

To input your loss put a minus sign at the front of the figures (you don't need to put in the $) and it appears it red as above - if you just type it into the box, it automatically becomes a positive (as the $40 above), again no $ needed, just numbers.

Thats about it regarding setting up the sheet and inputting figs etc

As you can see there is loads of info on the stats tabs - make of them what you wish!

Hope thi helps to give people a REAL idea of how they are doing, and how much they are spending each month on buy-ins!

Hope you are getting rakeback folks

Cheers

Damo

[daftpegasus]

This is a cracking spreadsheet Damo, thanks for it.

I've just spent the time to put in all my historical data going back to when receords began (1st May 2006) and one thing becomes immediately clear....

If I want to increase my profits I should give up all PL exclusive events!

Currently trying to work into the spreadsheet a method of adding the site (other than in the notes section) so that I can analyse (need a spell checker on this site) which I am most profitable on.

However from this I have set myself a target of $100 profit each month (on average), so it will be interesting.

[daftpegasus]

PS Damo,

What is the Risk of Ruin?

My risk of ruin is 30,930,071,873.6% - should I be worried? That's either very good or very, very bad. It is early days yet because I do not play many STT's.
<table x:str="" style="border-collapse: collapse; width: 136px; height: 35px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 102pt;" width="136"><col> <tbody></tbody><tbody><tr style="height: 12.75pt;" height="17"></tr></tbody></table>

[Valiant23]

Thank you thank you thank you DP, you've reminded me the question I wanted to ask too.

What is "Desired ROR" (I assume its something along the lines of Return on Roll). If it is, whats its purpose, as my Desired ROR is 1,000,000% (bit silly, but you get my drift)?

[robilaruk]

thats bad......

however I bet you have 10 games so far? maybe 20? when you get 1000 games in them the ROR is more relevant - so don't worry about that yet

however, it might also be because you are playing above your limit compared to your bankroll - I would advise having about 50 buy-ins for any level you are playing - more is better, so if you have $50 and you are playing $5 buy-ins then your ROR will be MASSIVE

Damo

Originally Posted by daftpegasus

PS Damo,

What is the Risk of Ruin?

My risk of ruin is 30,930,071,873.6% - should I be worried? That's either very good or very, very bad. It is early days yet because I do not play many STT's.
<TABLE style="WIDTH: 136px; BORDER-COLLAPSE: collapse; HEIGHT: 35px" cellSpacing=0 cellPadding=0 border=0 x:str=""><COLGROUP><COL style="WIDTH: 102pt" width=136><COL><TBODY></TBODY><TBODY><TR style="HEIGHT: 12.75pt" height=17></TR></TBODY></TABLE>

[robilaruk]

well what is your projected ROR - how much are you prepared to go bust chasing money?

its set at 0.5%, which means if you play solid poker within your bankroll you should never go bust - this is for statistical analysis with the emphasis being on the first four letter of analysis......

I wouldn't worry about ROR until you have at least a 1000+ game sample and its still a LARGE value

Just leave it and see what happens

Damo
ps in other words its for the REAL statto's out there, but not me and not something I fully understand or am bothered by!!

Originally Posted by Valiant23

Thank you thank you thank you DP, you've reminded me the question I wanted to ask too.

What is "Desired ROR" (I assume its something along the lines of Return on Roll). If it is, whats its purpose, as my Desired ROR is 1,000,000% (bit silly, but you get my drift)?

[GotaFancy?]

There is a "read me" tab which explains some of the terms too

[daftpegasus]

Originally Posted by robilaruk

thats bad......

however I bet you have 10 games so far? maybe 20? when you get 1000 games in them the ROR is more relevant - so don't worry about that yet

however, it might also be because you are playing above your limit compared to your bankroll - I would advise having about 50 buy-ins for any level you are playing - more is better, so if you have $50 and you are playing $5 buy-ins then your ROR will be MASSIVE

Damo

Thanks for the answer. I've played less than 10 STT games so far all at low stakes so I'll simply ignore that cell for another few months/years.

Cheers

[Hodgey]

Thanks for posting this up Damo. It is indeed a nice looking tool and for me being one who loves stats, this should keep me occupied for a while! I had already devised my own spreadsheet to keep records, but I think going forward I shall be using this one. Cheers.

[GotaFancy?]

Originally Posted by daftpegasus

My risk of ruin is 30,930,071,873.6% - should I be worried? That's either very good or very, very bad. It is early days yet because I do not play many STT's.
<TABLE style="WIDTH: 136px; BORDER-COLLAPSE: collapse; HEIGHT: 35px" cellSpacing=0 cellPadding=0 border=0 x:str=""><COLGROUP><COL style="WIDTH: 102pt" width=136><COL><TBODY></TBODY><TBODY><TR style="HEIGHT: 12.75pt" height=17></TR></TBODY></TABLE>

Have you entered your opening bankroll in cell C3 on the Money tab? If you haven't, that would significantly increase your ROR.....

[GotaFancy?]

I've noticed some rather strange definitions of times (probably from a Time Zone conversion somewhere?) - for example, Afternoon is defined as 9am to 1pm!!! (at least on my version)

Have changed the "Lookup" sheet to the details below to correct this....

<TABLE style="WIDTH: 503pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=670 border=0 x:str><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3584" width=84><COL style="WIDTH: 42pt" span=2 width=56><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3925" width=92><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 4352" width=102><COL style="WIDTH: 42pt" span=5 width=56><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right width=84 height=15 x:num="0">12:00 AM</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=56 x:num="4.1666666666666664E-2">1:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>E</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=92></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=102 x:num="0">12:00 AM</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=56 x:num="0.16666666666666666">4:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=56></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=56 x:num="0">12:00 AM</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=56 x:num="4.1666666666666664E-2">1:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>E</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="4.1666666666666664E-2">1:00 AM</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.20833333333333334">5:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">N</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.16666666666666666">4:00 AM</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.33333333333333331">8:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="4.1666666666666664E-2">1:00 AM</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.20833333333333334">5:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">N</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="0.20833333333333334">5:00 AM</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.375">9:00 AM</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">M</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt