New Penultimate 2005 SNG Spreadsheet

[AleoMagus]

Ok, I am finished. Well finished enough that i'd like others to take a look at this.

My super SNG spreadsheet which tracks 5 different buy-ins and provides overall results is ready for use and/or modification. If some of you could take a look at it and find any other bugs or difficulties I'd be grateful.

It has a very New Year purpose to it as I intend to start recording SNGs anew in 2005 with this sheet. It is designed for a year long use and even has a new year long profit graph.

As ever, I do not trust my own free sites to make it easy for you guys to see this, so if someone with webspace could let me know, I will email this off to you.

There are some things that I wanted to include but couldn't quite figure out (drop down menus for example).

Also, it has a few new features like a time of day split, which I have divided into six four hour blocks as follows:

12:00 AM-4:00 AM (Night)
4:00 AM-8:00 AM (Morning)
8:00 AM-12:00 PM (Forenoon)
12:00 PM-4:00 PM (Afternoon)
4:00 PM-8:00 PM (DInner)
8:00 PM-12:00 AM (Evening)

it is, of course possible to break this down even further but I figured I'd go with six 4hr blocks at least for now so you guys can check it out. This way, there will be a worthy accumulation of SNGs in each block. Otherwise, I think It'd take a long time for it to mean anything (In fact, it already will).

It also has a small user's guide, but not much. This may get expanded later.

Anyways, let me know if anyone wants to put this online for me.

Regards
Brad S

[J-Lo]

i can help you with drop down menus... if u email it to me @ gan_jathar@hotmail.com i will be happy to add drop down menus where u want, or i can give you step by step help over AIM to do drop down menus, my AIM name is GanJAthar
However, i don't have webspace to post it

[CardMinger]

Aleo,

I would be glad to host your spreadsheet on some webspace of mine.

You can PM me of AIM me at b1nkster07.

[eastbay]

It's up at

http://rwa.homelinux.net/poker/SUPER.xls

Somebody test please...

eastbay

[ghostface]

works.

[The Yugoslavian]

OMG, this really is 'super.' Just wanted to let you know all of your hard work is uber-appreciated. It's hard to even think about how difficult and sub-optimal tracking would be without your spreadsheets.

You really outdid yourself this time Aleo, job well done! /images/graemlins/grin.gif

P.S. If I come across any bugs, you'll be the first to know!

[imcastleman]

I really think you should zip that up first and then re-post. Excel zips up VERY WELL!! I think a lot of people are hitting your server right now and may cause you problems.

[eastbay]

[ QUOTE ]
I really think you should zip that up first and then re-post. Excel zips up VERY WELL!! I think a lot of people are hitting your server right now and may cause you problems.

[/ QUOTE ]

You're right. Duh.

http://rwa.homelinux.net/poker/Super-spreadsheet.zip

eastbay

[vindikation]

I reposted a second link for bandwidth issues: http://vindikation.com/misc/SUPER.zip

[1C5]

Thanks for posting, this thing is awesome!! So just by glancing at it, it seems like it can track pretty much everything that pokertracker can...is it fair for me to assume this?

Or would I still benefit from pokertracker as well? (this is for the 10+1 games where I will not be likely to see too many of the same players I am assuming).

[AleoMagus]

Mnay would say pokertracker is still useful, and this thing definitely does not track everything pokertracker does (though it does track some things pokertracker does not)

Pokertracker will give you stats about your play like:

VP$IP
Agg Factor
% preflop raise
etc...

That is all good info and many rely on it.
Pokertracker is also good about updating and I would not be surprised if someone over there incorporates everything in this sheet eventually

That said, I don't use pokertracker anymore
Just too much of a hassle getting hand histories and I really think it's most useful for ring games, which are not useful when it comes to building my bankroll /images/graemlins/smirk.gif

Regards
Brad S

[zephyr]

Thanks for the great spreadsheet. It's a testament to your knowledge of Poker and Excel.

Zephyr

[mart_ph]

Brad,
This spreadsheet really does look good.... but.... can you point me in the direction of an easy to read/understand explanation of exactly what sd, sd/tourney, confidence, etc actually mean!!!
Thanks

[gmunny]

They are statistical terms. SD is Standard Deviation and Confidence is short for Confidence level. These numbers change based on your sample size and results. Generally you want your SD to be small and your confidence level to be high. I will defer to a stat's expert to explain the definitions. Nice update, Brad!
G$

[Marcotte]

Aleo emailed me a copy and I made some mods. I just emailed it back to him. Hopefully he will foward it to the hosters and get it up on the web. It really needs to be field tested.

I made two modifications. First, I added support for exact time tracking of simultaneous tournies, so you can get an accurate $/hr figure.

The bigger change is a user interface. Each 'entry' page of the spreadsheet has a button that launches the UI. From there you can start and stop tournys, and add notes. It will automatically date/time -stamp the tourny and calculate the duration. While the tourny is running you can add notes/hand #s for that tourny. While the UI is running you can still manually access the spreadsheet, but you should refrain from adding tournies manually while it is running, as some data may end up on the wrong row.

It supports up to 8 simultaneous tournys. If someone need support for more, let me know. It shouldn't be too hard to add.

[Dominic]

fantastic! two questions, though:

if I change the # of people to make the SNG 6-handed (I play on UB), is the ITM calulation based on the color of the "place" column (pink or green)? If so, how can I change the colors to represent 2 ITM places and not 3?

Basically, how can I make the best use of the spreadsheet for 6-handed SNGs that pay only 2 places?

Also, how do I use the bubble %+/- figure? What's a good %??

Thanks!

[eastbay]

If you're talking about UIs, you must be doing some VB programming.

If that's the case, what do you think of the idea of parsing hand histories to populate the spreadsheet?

eastbay

[Marcotte]

[ QUOTE ]
If you're talking about UIs, you must be doing some VB programming.

If that's the case, what do you think of the idea of parsing hand histories to populate the spreadsheet?

eastbay

[/ QUOTE ]

Well, that's certainly possible. I'm not the most efficient programmer, but I suppose it would just be a matter of parsing text strings looking for patterns. Unfortunately, I'm not sure how much more time I'll be able to spend on poker at work now, since tomorrow I move to a new desk just outside my boss's office. /images/graemlins/mad.gif However, since I arranged for my monitor to face a wall, I think I may just look into that. /images/graemlins/grin.gif Excel programming does look a lot less suspicious than this forum anyway.

PokerRoom doesn't offer complete tourny histories (those <explitive deleted>). I'll PM you my email - if you send me one I'll see what I can do.

[AleoMagus]

Wow, that is really cool.

If my spreadsheet was penultimate, yours definitely moves things a lot closer to ultimate. I sent this off to eastbay so hopefully he'll be kind enough to get this up for others to look at. Amazing really, from what I've seen so far. Adds a whole new dimension to the functionality of this thing.

Simply put, however good, however useful this spreadsheet was before, it is now WAY better.

Regards
Brad S

[AleoMagus]

[ QUOTE ]
if I change the # of people to make the SNG 6-handed (I play on UB), is the ITM calulation based on the color of the "place" column (pink or green)? If so, how can I change the colors to represent 2 ITM places and not 3?

Basically, how can I make the best use of the spreadsheet for 6-handed SNGs that pay only 2 places?

Also, how do I use the bubble %+/- figure? What's a good %??

[/ QUOTE ]

I didn't come up with the colour coding function, and I suspect it doesn't actually function off ITM but rather on top 3. This will mean that it might come out incorrectly for you. What you could do is just format each cell where you get 3rd and change the colour manually. Or you could just format all the cells once so that there is no colour coding at all. Then again, we could just figure out how it actually works and change it to accomodate all SNG payout structures. Jurollo made that change with his excellent update when he introduced the splits functions, so he will definitely be able to help us out.

As far as using the sheet effectively for 6 player SNGs, it should work pretty well as is, except for a few issues where stats are accumulated for top 3. The splits info will be accurate, but who cares how often you get top 3 on tuesdays right? that can be changed to top 2 also, but will require some work to fix. Still, all that said, ITM values, ROI, SD, and all the rest will work fine.

Bubble % was a stats I came up with a long time ago, and at the time, I figured that a good player would obviously survive on the bubble far more than average. I figured that you would want a positive value, and the higher the better. I figured that 10% would be about the upper bound for a really good player.

Well, I have since changed my mind somewhat. Really strong high stakes players often cite stats which would indicate a negative bubble %. This indicates more agressive gambling play and usually means more firsts also, and sometimes, a higher ROI. So the answer is, you want a high bubble %, but more than this, you want a high ROI.

Regards
Brad S

[AleoMagus]

You might be amazed how little I actually know about excel. Actually, excel experts might look at this sheet and not be amazed /images/graemlins/smirk.gif.

Still, Thanks.

Brad S

[eastbay]

[ QUOTE ]
I sent this off to eastbay

[/ QUOTE ]

Hmm. No email. Maybe it's taking awhile due to size...

eastbay

[eastbay]

Cool new UI features:

http://rwa.homelinux.net/poker/SUPERSNGwUI.zip

Faster server copies appreciated...

eastbay

[spentrent]

Marcotte -- if you agree it's useful -- how complex would it be to:

- Have a start button for each tournament

- Have a dropdown menu for the buy-in for each

- Have a "placed?" text input and an end button for each started tourney

So we'd have:

Tourn 1...$10+1...STARTED 10:30...PLACED [____]...[END]
Tourn 2...[$10+1]...[START]
Tourn 3...[$10+1]...[START]

(the square brackets represent buttons/menus/input)

(also note that I don't suggest a dropdown for "placed?" since it's probably more convenient for a user to simply type a number)

I am of course VERY GRATEFUL for yours and Aleo's work; just thought I'd add my two cents as a fellow designer/coder, sadly, without VBA experience. I find the pop-up dialogs a bit clunky, considering that poker windows will be popping up all over the place as we enter information.

If you think it's a good idea and decide to add it, I shall offer my Perl/PHP services to a project of your choosing. /images/graemlins/cool.gif

[Etaipo]

[ QUOTE ]
Cool new UI features:

http://rwa.homelinux.net/poker/SUPERSNGwUI.zip

Faster server copies appreciated...

eastbay

[/ QUOTE ]

High speed (300kb/s + download speeds) mirror.

Download here (http://flamevault.com/~etaipo/Poker/SUPERSNGwUI.zip)

[MelchyBeau]

Star Office compatibility and this spreadsheet.

I really like the spreadsheets that you have posted, thank you thank you thank you. However, there are a few compatibility issues with it and star office, namely the graphics. I am going to take some time out later on and fix this for myself, just so I can use it on my main machine. The issue is the actual graphs. Would I have your permission to post it if I made it work for star office as well?

Melch

[vindikation]

I have a lot of space so if you guys want me to host files permanently (well at least the next 3 years of my domain registration) I'd be more than happy to do it, just let me know.

http://vindikation.com/poker/SUPERSNGwUI.zip

[SuitedSixes]

Brad-
This really is too neat to just be given away. For those of us who would like to thank you ($) for your hard work, do you have a PayPal account, or something like that? What is your Party name so we can transfer some thanks your way?

[zephyr]

I use excel on a daily basis, although I'm definitely no expert. Nevertheless, I think your spreadsheet is an example of expert usuage of the program. Sure your calculations aren't the most graceful, but with computers that can do 2 billion + calculations per second, why would you waste your time streamlining. Then again, I'm from Saskatchewan and we tend to be a touch on the pragmatic side.

Again thank you.

Zephyr

[Marcotte]

Spent,
Good suggestions. It would definately be more user friendly to have everything encapsulated in one window. The pop-up diaglog boxes (where you enter tourny # and place finished) are the built-in Excel InputBox() function, which is a lot easier to code than the UserForms. I wanted to get something out there quick that worked so that you and others could both use it and tell me how to make it better.

It shouldn't be too hard to make the changes you suggest. Eastbay's got me thinking about loading tourny histories too, so I'll have to budget my time.

[Marcotte]

[ QUOTE ]
but with computers that can do 2 billion + calculations per second, why would you waste your time streamlining.
Zephyr

[/ QUOTE ]

Lol. You'd be surprised how fast an Excel spreadsheet can become a bloated, processor-hogging, monstrosity. Some of my spreadsheets at work are 50+ MB and take over an hour to calculate (which is one of the reasons I have so much time to work on poker spreadsheets /images/graemlins/laugh.gif.

One important thing about the UI - it is set up to only calculate when you click the exit box and close the UserForm. When I was testing it, it took several (15-25) seconds to calculate, so I decided to forgo on the fly calculations and just calculate once at the end of the session. All this means is that you can't look up your ROI etc. while you play, but I figured this wouldn't be a great loss.

[OrcaDK]

This is looking really good! Though i'm having some troubles "cleaning" the sheet.

I wan't it totally cleaned and set up for my own SnG levels. When i delete all the existing entries, it still thinks that i've played a single SnG, which messes up the numbers. Any of you having success with cleaning the sheet of data?

[se2schul]

This spread sheet almost grinds my p2-266 to a halt. I know, I know.... I really need to upgrade....

Great work though!

Steve

[spentrent]

If you need my regex kung fu let me know.

[AleoMagus]

lol.

While the thought of $$$ is nice, it's really hard to accept anything like that considering that others have contributed in big ways.

If you really must unburden your soul and wallet, I suggest playing in one of the 2+2 SNGs or a KOTZ event. If that isn't good enough, or it's still not fair because you are a pro who will likely profit from doing so, offer a big bounty on yourself.

If it is really just me that you'd like to appease, donate money to the red cross. Humanitarian causes in the third world are my favorite.

Regards
Brad S

[AleoMagus]

[ QUOTE ]
Would I have your permission to post it if I made it work for star office as well?

[/ QUOTE ]

Of course.

Anybody with ANY idea how to make this better is welcome to do so.

Regards
Brad S

[(my name it is) Sam Hall]

OK. Stupid question. Wouldn't it be easier to have excel figure out what day of the week it is instead of the dropdown menu?

=TEXT(WEEKDAY(!REF), "ddd")

Sam

[AleoMagus]

[ QUOTE ]
I wan't it totally cleaned and set up for my own SnG levels. When i delete all the existing entries, it still thinks that i've played a single SnG, which messes up the numbers. Any of you having success with cleaning the sheet of data?

[/ QUOTE ]

At first, I wasn't sure what you meant here, as I had no trouble entering new info, and when totally blank it registers zero tourneys.

Just now though, I figured out what you mean. If totally blank, but the buy-in and vig amounts arte left in, the spreadsheet will record your total as 1 buy-in to the negative on each sheet. I will try to fix this for future versions, but for now, a simple solutions is to just make sure that if you aren't using a particular sheet (like if you only play 20+2 and 30+3, for example) that you be sure to make all other entries sheets completely blank.

This means that you need to delete the buy-in and vig values also at the top of the entries sheets.

Regards
Brad S

[CardMinger]

Hello,

I love this spreadsheet and plan on using it along with Poker Tracker. I just read on another thread that it might be possible to export HH's from Poker Tracker into an excel spreadsheet and then move them to AM's SnG sheet. Can anyone elaborate on how to do this?

Thanks!

[SuitedSixes]

Here's how I do it (there's probably an easier way):
1) Save a blank spreadsheet on your desktop (I call mine SNG).
2) Using PokerTracker, on the Tourneys tab, push the [P] next to "Tourney Detail", then the [Export...] button. Be sure Excel is chosen, and browse to the empty spreadsheet on your desktop, and press [Export]. Click [Yes] and [Ok], and your data will be in the SNG folder.
3) What you do next is up to your level of expertise with Excel, but I created a new tab on AM's spreadsheet that I link that data to (tourney date and finish). Before I bring my data to AM's sheet, I sort it by date oldest to newest (not the PT default). PT puts the DATE and TIME in one field, so it is not as clean in AM's sheet, but it still works.

Ta-DAH!

[PE101]

Nice job!

Thanks for sharing your work.

[Dominic]

The spreadsheet is great...except that there seems to be a bug in the ROI/ITM graph of the 50+5 page...on all the other levels, the graph works fine, but on the $50 it doesn't...

anyone else having this problem, and if so, how can I fix it?

[Dominic]

anyone?

[AleoMagus]

[ QUOTE ]
on all the other levels, the graph works fine, but on the $50 it doesn't...


[/ QUOTE ]

I've been looking at it, and I don't think I'm seeing anything out of the ordinary. What is it that makes you say it's not working? What is it doing?

Regards
Brad S

[(my name it is) Sam Hall]

[ QUOTE ]
OK. Stupid question. Wouldn't it be easier to have excel figure out what day of the week it is instead of the dropdown menu?

=TEXT(WEEKDAY(!REF), "ddd")

[/ QUOTE ]

Nevermind. I see how it works now. Just more proof that I'm an idiot /images/graemlins/confused.gif. Thanks for the sheet!

Sam

[Dominic]

[ QUOTE ]
[ QUOTE ]
on all the other levels, the graph works fine, but on the $50 it doesn't...


[/ QUOTE ]

I've been looking at it, and I don't think I'm seeing anything out of the ordinary. What is it that makes you say it's not working? What is it doing?

Regards
Brad S

[/ QUOTE ]

well, the numbers of my ROI and ITM are fine under the poker summary. It says that I've played 58 SNGs and have an ROI of 43.9% and an ITM of 55.5%....but if I look at the graph, it has the two lines starting at the bottom - SNG #3, actually, and both go basically straight up and BACK a little all the way to the top.

Actually, I've noticed that the ROI/ITM graph is messed up in most of the different SNG levels...the profit graph is always fine....I'll try copying and pasting into a new spreadsheet...see if that works.

****

I figured it out - I had put in some finishes from when I first started that I didn't have the dates for, so I just left the daets blank - apparently, however, the graph needs dates in order to calulate ROI/ITM for some reason - when I put in dates the graphs all worked.

/images/graemlins/laugh.gif

[climber]

Ok--I don't get it.

I just cut and pasted all my data into the new "super" spreadsheet. However, my ROI and ITM% are still the same! Doesn't seem very super to me.

[climber]

Cool spreadsheet--thanks guys I particularly like the idea of day of the week and time of day breakdowns.

My time of day still are divide by 0 errors--is this cause I'm only at 38 tourneys?


Also the start importing thing doesn't "launch" anything for me. Is it supposed to?
Thanks

[Tosh]

Did you choose to disable macros?

[Marcotte]

If you don't have any tournys in a time period (eg. 12am to 4am) you will get a #DIV/0 error for that time period.

If you want to use the UI to input data and get the start/stop time stamp, you must enable macros when you open the sheet. If macros are enabled, a dialog box pop open when you click on the button on the spreadsheet.

If you are still having trouble with this, PM me your email address.

[climber]

Hmm, got macros enabled now so now the logger actually launchs. Thanks for that.

I cut and pasted my data from the old spreadsheet which gave me some problems as the day of the week then became a value and not an ouput of a function. Fixed that but I'm still not able to get the time of day stats working. Still divide by zero. I'm guessing this maybe because of cut and pasting the data in that info somehow didn't get written to where it needs to be? any thoughts on how to correct this.

Also any way to make the overall tab stats accurate when I am only playing one or two levels? It seems to include one buyin and loss at each level by default which drastically affects my stats obviously. Especially that $100 one!

Thanks guys!

Also can I enter the data manually or do I have to use the UI. Cause I got my sheet screwed up by accidentally closing the UI window before a tourney was over not realizing it would clean its slate--I then entered the finish and duration manually but it wouldn't compute the stats--total tourneys were still at the previous number. ???

[AleoMagus]

[ QUOTE ]
Also any way to make the overall tab stats accurate when I am only playing one or two levels? It seems to include one buyin and loss at each level by default which drastically affects my stats obviously. Especially that $100 one!


[/ QUOTE ]

Make sure that the Entry and Vig fields of the $100 SNG sheet are set to 0.

[ QUOTE ]
Also can I enter the data manually or do I have to use the UI.

[/ QUOTE ]

You can enter it manually

...

Does everyone understand that the 10+1, 20+2, 30+3, 50+5, 100+9 defaults are not necessary? You can put whatever tourneys that you want in these sheets. 15+1 25+2, 5+1, 2+.20... whatever.

Regards
Brad S

[climber]

Ok thanks-that worked great...I'm never sure in excel what I'm allowed to change and what will screw up everything. Didn't think of that but it fixed me right up.

Now my only remaining question is why my day of the week data that the sheet calculated from the column of dates I cut and pasted in never got written to the the AJ,65 block where it would need to be to give me time of day stats.

Anyways, I can prob live without that for now.

Thnaks again for all the hard work and the awesome sheet and quick answers!

[Marcotte]

[ QUOTE ]
Still divide by zero. I'm guessing this maybe because of cut and pasting the data in that info somehow didn't get written to where it needs to be? any thoughts on how to correct this.


[/ QUOTE ]
Off the top of my head, the only thing I can think of is that you don't have any tournys for a given time period. Do all time blocks say '#DIV/0'?

[ QUOTE ]

Also can I enter the data manually or do I have to use the UI. Cause I got my sheet screwed up by accidentally closing the UI window before a tourney was over not realizing it would clean its slate--I then entered the finish and duration manually but it wouldn't compute the stats--total tourneys were still at the previous number. ???

[/ QUOTE ]

As Aleo said, you can enter it manually. What exactly do you mean by "clean its slate"? Did it erase the existing data you had for that tournament when you closed the UI? As to why the stats didn't compute it may be that calculation got set to Manual. Oftentimes I will set calculation to manual at the beginning of a macro and set it back to automatic at the end (to speed up execution). Manually exiting the UI (with the 'x' in the corner) would bypass the code turning calculation back on. (Can't remember for sure if I did this in this case, but I probably did.)

Go to Tools-->Options-->Calculation and select automatic. You can also press 'F9' to do a one time calc.

[Marcotte]

I checked the code and I did include lines to turn calculation to manual. Also, on my copy, I had temporarily taken out the lines to change it back to automatic. I will try to include code to prevent the accidental closure of the UI and forward a new copy to Aleo. In the meantime, if anyone is having trouble with the spreadsheet not calculating, follow the instructions in the above post.

edit: After looking over all the code, I see that the UI is set up to calculate only when the Exit button is clicked. On my machine (~1.1Ghtz chip, 1GB Ram), it takes about 4-7 seconds to calculate the spreadsheet, which is why I turned calculation off. I've added support to prevent accidental closure and will forward it to Aleo for posting. Please let me know if you find any other bugs.

[Turk]

Where is the spreadsheet please? I have looked through the threads and can't seem to find it.
Thanks

[boxedIn]

First off, since I'm posting in this thread, I've got to give major thanks. This is an excellent piece of work.

Onto the question: I'm running into the same problem (using it for 6-handed SNGs) and the OOTM color is vaguely annoying. I've been able to modify everything but that, so far. I wouldn't even mind doing it manually for those third places finishs that I'm pained to receive, however the "Fill Color" doesn't seem to work for these cells; I'm guessing because there's a formula?

Has anyone gotten this to work? Does anyone know how to get this to work? Perhaps the creator of the formula could post some hints on where I might look to modify the formula?

Thanks for any help anyone can post.

[climber]

Thanks for the tip on how to turn auto calculation on or off--thats great.

On the "divide by xero issue"--No they don't all say divide by zero. For instance I cut and pasted yourney data from 40-50 tourneys into it, I have since played 10-20 more using the record feature. The data from the newly imported tourneys is all in the proper places. There is no issue with the sheet itself and using it for new data. The forumals etc are all in place. Somehow though when I cut and pasted in the old tourney info the data never got written to that block of cells in the AJ,65 area. The day of the week data was written fine and those calculations are all working correctly for all the data--just the time of data stuff is not getting cipied to the right places when i cut and paste data into it. It works fine for new entries.

By "clean its slate" I meant that closing the dialog box with the "x" in the upper right made it so that when you relaunch it it doesn't know anything about a tourney you might have started before but not ended for example. Thats fine--its really not a problem i just know not to close it till i'm done with it.

Thanks again for all the great work--I love the sheet.

[Marcotte]

[ QUOTE ]

Has anyone gotten this to work? Does anyone know how to get this to work? Perhaps the creator of the formula could post some hints on where I might look to modify the formula?

Thanks for any help anyone can post.

[/ QUOTE ]

The coloring of the ITM cells is done with Conditional formating. Highlight the cells, then select Conditional Formating from the Format menu. I'm not sure exactly how Aleo set it up, but you should be able to change the '3' in the formula to a '2' and it should work.

[Marcotte]

[ QUOTE ]
Somehow though when I cut and pasted in the old tourney info the data never got written to that block of cells in the AJ,65 area. The day of the week data was written fine and those calculations are all working correctly for all the data--just the time of data stuff is not getting cipied to the right places when i cut and paste data into it. It works fine for new entries.


[/ QUOTE ]

By 'AJ,65', are you referring to a cell? I'll have to look on the ss and see what's there. OK, I see where the problem is coming from.

[Kinda went on a ramble. Skip to the end for the short answer.]

Excel represents date-time as a decimal number with the numbers left of the decimal representing days and the numbers left of the decimal representing fractions of days (so 9.5 would correspond to 12 noon on the 9th day - I think their days start somewhere in the late 1800s).

If you enter a time manually, Excel sees it as a decimal number less than 1 (one). EG: 12:00 noon is equivalent to 0.5, as far as Excel is concerned.

The UI date-time stamps the start time field and date stamps the date field. I then subtract the date field from the start time field to get the duration and also the "un-dated" start time, which is then used to look up the "Time-code". It sounds confusing, and is probably more confusing than it needs to be.

End result: Column E on the data entry pages (the Time column) requires date AND time data, not just time. It is formatted to only show time, but the date is necessary for certain calculations to work. Sorry about that. I didn't think about copying old data onto the spreadsheet. So much for backwards compatibility. /images/graemlins/blush.gif

[climber]

Thanks a lot for the info on my issue. Now I know how to fix it and I prob will since its only 40 or so tournies. i'm curious about the EV of play at diff times of day. Mornings seem a lot tougher but I 'd like to see some numbers to back that up.

I read the inital point of the new sheet was for recording new tourneys in 2005--it will do that just great so no worries about backwards compatibility.

[Scuba Chuck]

Say, is there any reason you have the ROI & ITM charts capped at 70%?

I'm getting tired of looking at str8 lines across the top ... /images/graemlins/cool.gif

[climber]

I'd guess you could likely solve this problem yourself by playing a few more tourneys.

[Scuba Chuck]

Ah, fixed the problem myself. ROI at 65% now.

/images/graemlins/blush.gif /images/graemlins/cool.gif /images/graemlins/grin.gif

[Etaipo]

[ QUOTE ]
Where is the spreadsheet please? I have looked through the threads and can't seem to find it.
Thanks

[/ QUOTE ]

Here's my copy (http://flamevault.com/~etaipo/Poker/SUPERSNGwUI.zip)

[Mobed]

Just what I was looking for thanks for al the hard work.

[RCM]

Hi All,

I've been lurking here off and on for the past year or so, but decided I'd respond to this post to thank all the posters for their insights and especially AleoMagus and the others who helped put this great spreadsheet together...all I can say is "wow" and "does your boss know how your using company time?"

Additionally, I have a quick question regarding the spreadsheet. I tried to add sheets for both $5+$1 and $200+$15 just to round it out, but ran into problems with the UI. I guess the $200+$15 can wait for a while, ;-) but I've recently started playing SnGs and would like to have the $5+$1 sheet as I figure I should walk before I should try to run. Any ideas on how to add these?

Thanks for your replies and thanks again for this tool and your advice...

RCM

[AleoMagus]

5+1 is no problem to add. Just take the first (or any) of the entries sheets and change the entry and vig values (the grey fields at the top).

That will still only give you 5 sheets, so maybe you cold go with:

5+1
10+1
20+2
30+3
50+5

or some other combination like that.

A better idea might be to just go with 5+.50 at Stars, as that makes way more sense to play than the party 5+1.

While I am back in this thread, I'll tell everyone a sad story.

About a week ago, I was bored and though I would do a little more work on that sheet. I expanded the sheet's finish columns to accomodate for up to 30 distinct finishes so that it would be totally universal again and would accomodate perfectly any SNG from 2-30 players. I expanded the number of entries rows from 750 to 2000. I did a few minor cosmetic changes, and I modified the daily and time of day breakdowns to actually give a true ITM for whatever the tourney was, and not just a Top 3 value. Also, I made the cash-ins, cash-outs, and other money changes actually appear on the year graph on the days they occur.

Then my excel crashed for some reason. All Gone. I spent the next hour trying to get it back because I figured it might be somewhere on my computer somehow...

All gone. Oh well. Next time I'll save every hour or so.

Regards
Brad S

[RCM]

Thanks for the quick reply, the fix, and the suggestion to play on Stars. I don't have an account there (yet), but half the vig sounds like a good enough reason to get one. Thanks again and sorry to hear about the crash...

RCM

[imported_mrfrisco]

I also would like to thank those involved in providing this spreadsheet thank you ever so much. It is a great tool and commodity to have.
I have a question though, I tried to rename the worksheet tabs from (i.e. sng1 to 5+1) as suggested in the readme but I then kept getting a run time error when I start to record new tourney. Did I do it wrong or will this not work?
Also I like to keep record of my stats when playing party poker (ie hands played, win%, showdowns won, flops seen%, win% when flop seen), maybe if you decide to upgrade this later you could allow for input of this data also, just a thought.

Thanks again and a job well done, am very grateful

[Scuba Chuck]

Perhaps you could help me understand some of the stats in the spreadsheet...

What does the typical SD (standard deviation) per tourney mean to me? Mine says $59.14.

And then how does the Total SD figure into things? Mine says $561.08

What is a good heads up percentage to shoot for? Mine is 58%. Could/should this be higher?

thanks for any input...

[splashpot]

My appologies if this has already been mentioned, I skimmed through all the posts, but didn't see what I was looking for.

I am an beginner when it comes to excel. Everyone here already seems to know how to use the spreadsheet. Is there a FAQ for absolute beginners? For example, importing tourny histories. I'd love to use the info from the spreadsheet, but I don't know how to get started. Thanks in advance.

[Marcotte]

Unfortunately, the way the code is written for the UI, you cannot rename the spreadsheet tabs. It also will not support more than 5 different SNGs. The easiest way around this is to have a second spreadsheet for the additional levels you are playing. I know this isn't ideal, but I don't think I will have time in the next several weeks to update the code. (Year-end closing is a b****).

[Marcotte]

Currently the spreadsheet doesn't support the importation of Tourny histories. Eastbay has asked me about this and it's definately do-able, though like I said in my other post, it will be a while until I have the time to do this.

The spreadsheet has a UI that is designed to be used while you play. Open the spreadsheet, goto the page corresponding to the level of SNG you want to play. Sign-up for your SNG like normal. Then switch back to the spreadsheet and press the "Start Tourny" button. A dialog box will open showing the current status of 8 tournies. The first line should say "Tourny 1 started at xxxxxx" You may be prompted for buy-in level before this - I can't remember off the top of my head. Anyway, there is a text box in which you can write notes/hand numbers. This will be added to the notes column on the spreadsheet. When the tourny is over, click end tourny, enter your finishing place and your done. The start and end buttons record the time for you, so you don't have to do any direct data entry on the spreadsheet.

If you don't want to use the UI, or you have old tourny data you want to enter, just enter the date and time the tourny started and ended, along with your finishing place, and everything should calculate for you. I think the general guideline is that if a cell is gray, it is a user input cell. And they should all have column headers that make it clear what goes there.

One important note/bug: If you enter your tournies by hand, you must enter a date/time for the start and end times, rather than just a time. This is because of the Now() function in VBA, which returns the current date and time. If and when I get around to adding support for importing tourny histories, I will change this.

[AleoMagus]

SD is your standard deviation per tourney. This is a measure of the average deviation from your expectation 1 standard deviation (or about 66%) of the time.

In other words, you can expect that on average ~66% of your tourneys will have an exectation of your expected profit +/- your SD. ~95% of the time you will be within 2 SD, and ~99% of the time you will be within 3 SD. Of course this isn't actually true as SNG results are have discrete outcome possibilities but it still is meaningful.

Your Total SD is just the same thing, but over your total sample. In other words, in your next sample of equivalent size, you can expect the same profit +/- 1 total SD ~66% of the time. You can expect profit +/- 2 total SD ~95% of the time, etc...

As for what a 'good' SD value is, don't worry too much at all. Almost all SNG players will have a similar SD (about 1.7 buy-ins). Practically speaking, better players will have a slightly higher SD (yours looks good and is actually quite high, though this can be due to other factors).

The main use for SD is in other calculations like confidence and Risk of Ruin.

this other post explains a bit more:

SD and confidence (http://forumserver.twoplustwo.com/favlinker.php?Cat=&Entry=54096&F_Board=singletable &Thread=1373360&partnumber=&postmarker=)

As for heads up percentage, over the long, long run, I think your percentage is very high. I'd say that in lower stakes 60-65% would be about max, and at high stakes it is probably barely over 50%. Perhaps others will disagree. (Actually, this assumes strong play throughout, because if I wanted to, I could 'manufacture' a sample with 90% HU wins simply by making stupid gambles which assured a huge heads up chiplead the few times that I did get heads up.)

Hope this helps

Regards
Brad S

[CardMinger]

Aleo & Other contributors,

Thanks again for such a valuable resource to our play. The spreadsheet is awesome!

I am having one problem which might possible be the result of me being an excel idiot but...

for example under stats2 I cant get it to accurately compute the number of hourse I have played...it keeps wanting to only use the data from the SNG1 page for this instead of info from SNG2...is there any fix for this or is it just a mistake I am making about how it is supposed to function.

Thanks again!

[AleoMagus]

[ QUOTE ]
under stats2 I cant get it to accurately compute the number of hourse I have played...it keeps wanting to only use the data from the SNG1 page for this instead of info from SNG2

[/ QUOTE ]

Nice catch Cardminger. This is an important litle error and I'm surprised we didn't see it sooner.

A quick fix is to go into the Stats2, Stats3, Stats4, and Stats5 pages and change field E15 (the # hrs) to it's proper value

it currently is:

=STATS1!F1216/60

but the STATS1 should be changed to the Appropriate STATS sheet. Actually now that I think about it, you should be able to just change that to just F1216/60 for all of those sheets as they are just supposed to be referencing themselves anyways.

I will post a 'final' version of this sheet sometime in the new year, and this will definitely be fixed.

Regards
Brad S

[CardMinger]

Aleo,

Well glad I could help out if only slightly...

Using your suggestions however I am still unable to get it to calculate the correct number of hours...I tried both suggestions including changing STATS1 to STATS2 and omitting STATSX altogether.

Any other suggestions would be appreciated.

Thanks!
Kevin

[climber]

I just ran into this problem the other day...

Aleo is right you need to change the first formula from SNG1 to SNG2 or SNG3 or whichever you are on.

However you must also change the formula in the F1216 cell as it also refers to SNG1 explicitly.

Change them both and youll be all set.

Both of these formulas need to be changed on sheets 2-5 for the hourly stats to work properly.

Good Luck.

[iMsoLucky0]

Hey guys, I've downloaded the spreadsheet, and it is absolutely great..... However, I am having a few problems with it. Every version I find already has some tournaments entered in it, and I have trouble clearing them out. I delete all the tourneys that were already in there, and then start to enter mine, but then for some reason the stats are messed up. If anyone has a version that is completely blank and could get that to me somehow, I would greatly appreciate it.

If you can respond here, or PM me, or AIM me at imsolucky0, that would be great. Thanks.

[reecelights]

I copied and pasted results from V2 and had a couple of problems.

First there were no Day split results. I solved this by going to the first day box after what I pasted, pasted up and then filled up to the top of the column. Problem solved.

However, I currently have no Time of Day splits. It seems as if the Time entry from the SNG page is not copying over to the Stats page. All the times are in, I've tried the same thing as the Day column, and even entered new results manually to no avail.

Suggestions as to how to remedy this? Sorry, I'm about a 3rd grader when it comes to Excel.

Oh, and add my Thank You to the growing list. You guys have done us all a great service.


Chuck

[climber]

Read the replies above to solve your problem...

Hint: The time field needs to contain both a date and a time. "record" a sample tourney and look at its format for help.

[reecelights]

I tried ecording a new tourney and got:

"Run-time Error '9'"

"Subscript out of range"

I think I'll start a fresh copy, and try from scratch.

[reecelights]

OK...I found your post about time stamps needing the date. Must have skipped those three somehow, becase I'd read almost all the others.

I think I'll do the same thing and just start a new fresh sheet tomorrow. My old ones have over 300 tourneys and I don't feel like manually changing all of them!!!

Happy New Year all!

[OrcaDK]

I just ended a SnG with a split 2nd place, is there any way this might be entered, so that the sheet updates with the correct won amount?

Currently i've made a cashout of the skewed amount so the total is correct.

[jedi]

I'm sure this is simple, but how do you automatically get the background color to change when you are ITM vs. OOM?

[gillo]

Could someone please forward me a copy of the spreadsheet for review. Thanks.

[binions]

Look it up.

[AleoMagus]

Actually I think it does... read this thread and you will see.

Regards
Brad S

[reecelights]

Aleo already has it set to do that and it was listed above but there are a lot of posts to go through in this thread...

Format
Conditional Formatting
Condition 1
Cell Value is, Between, 1, 3, pick a color
COndition 2
Cell Value is, Greater than, 3, pick a color
Condition 3
Cell value is, Equal to, 0, pick a color

[iMsoLucky0]

Can someone please post a copy that does not have any tournaments already entered in the database or just explain how to remove all of the tourneys already in there without messing up the spreadsheet. I have gone through and manually removed all of them to where it shows that 0 tourneys have been played on each of the entry pages, however on the stats page it still has that there have been entries in the amount of 1 sng per amount (11, 22, 33, 55, and 109). Thanks

[Tosh]

Will there be one to start 2005 a fresh? I only started importing for the new year and it doesn't seem to pick anything up.

[binions]

Many people think "penultimate" means "super-ultimate". More ultimate than ultimate.

It doesn't. It means "next to last" or "second to last."

When you describe your spreadsheet as the "new penultimate," we already have a contradiction. When you describe your spreadsheet as "finished" and a "super SNG spreadsheet," we know you are using penultimate incorrectly.

It's a cool spreadsheet, however.

[AleoMagus]

Tsk, Tsk... Still not reading the thread I think...

I'll help you out. I described the Spreadsheet as finished only to the point that I wanted others to check it out and help debug it. The spreadsheet itself has a big yellow label in the ReadMe which says it is a 'trial' version of sorts which will later be revised one more time. I have also repeatedly made reference to a final modification I have planned in which I will clear up a lot of the bugs mentioned here.

That may not convince you, so I will include a direct quote from myself to Marcotte after he added the UI

[ QUOTE ]
If my spreadsheet was penultimate, yours definitely moves things a lot closer to ultimate.

[/ QUOTE ]

Still, my hopes of this sheet being Penultimate do seem a bit naive, as it will no doubt become modified further after I am done with it. Marcotte & Eastbay's idea of populating the Spreadsheet by importing hand histories would certainly move things a lot further than I had planned.

Regards
Brad S

[jedi]

[ QUOTE ]
Aleo already has it set to do that and it was listed above but there are a lot of posts to go through in this thread...

Format
Conditional Formatting
Condition 1
Cell Value is, Between, 1, 3, pick a color
COndition 2
Cell Value is, Greater than, 3, pick a color
Condition 3
Cell value is, Equal to, 0, pick a color

[/ QUOTE ]

Thanks. I'm actually modifying the spreadsheet to include my ring game play and live game play as well as SnGs and even multi-table tournaments. I'm having to re-learn a lot about Excel.

[binions]

[ QUOTE ]
Tsk, Tsk... Still not reading the thread I think...

I'll help you out. I described the Spreadsheet as finished only to the point that I wanted others to check it out and help debug it. The spreadsheet itself has a big yellow label in the ReadMe which says it is a 'trial' version of sorts which will later be revised one more time. I have also repeatedly made reference to a final modification I have planned in which I will clear up a lot of the bugs mentioned here.

That may not convince you, so I will include a direct quote from myself to Marcotte after he added the UI

[ QUOTE ]
If my spreadsheet was penultimate, yours definitely moves things a lot closer to ultimate.

[/ QUOTE ]

Still, my hopes of this sheet being Penultimate do seem a bit naive, as it will no doubt become modified further after I am done with it. Marcotte & Eastbay's idea of populating the Spreadsheet by importing hand histories would certainly move things a lot further than I had planned.

Regards
Brad S

[/ QUOTE ]

Nice backpedal, but I'm not buying it.

[Tosh]

On the Stats sheets the E61 field needs to be changed to be equal to the SNG sheet where it lists how many players there are (Stars tourneys are 9 handed for example): E61 '=SNG1!D7'

Also it would be better to have the E62 field equal to '=SNG1!D8'

It also makes a lot of sense to make the calculation of profit for each day on this sheet a function of the payouts; so that we can modify them. You can drag it down to make all further days so each day has the same formula. I have made Q76 on the stats page '=(C76*$P$74*$E$61*SNG1!$G$4+E76*$P$74*$E$61*SNG1! $G$5+F76*$P$74*$E$61*SNG1!$G$6)-(N76*($P$74+$R$74))'

[sofere]

The spreadsheet rules...you guys rock.

That being said, in future versions, I was wondering if it would be possible to create filters so that you can see stats from date X to date Y, or monthly/Month-to-date/year-to-date type stuff.

Keep up the kick arse work.

[The once and future king]

Maybe I missed the fix, but there is an error whereby when you enter a value into the buy in field and vigourish (after having deleted the old values) it is immediatly recorded as a loss on the over all money sheet before one has actualy played any tournaments.

[JalKelly]

tired of looking for this thread

[partygirluk]

Could someone please provide a link as none of the current ones work for my friend.

[vindikation]

http://vindikation.com/misc/SUPER.zip

[bearly]

none will open in my comp. i have excel 5.0 win zip. some one said something about enableing macros. i don't get this part. any ideas why nothing will open? have win. xp. thanks for any help you comp. experts can provide. h. ps. this effort has led to a near frenzy-talk about frustration

[bearly]

this one doesn't get past the gremlins either------------howllllllllllllllllll..........h

[vindikation]

Sorry don't know what I can do for you /images/graemlins/confused.gif

[TexBigSlick]

Very good chance the problem is "Excel V5". That goes back about 8+ years. Having XP the OS won't help with that problem. You'll likely need to upgrade to Excel '97 minimum.

[MrGunner]

Can someone please tell me how to make a formula that count and update the longest losing streak. Thanks

[SirJoker]

Its saying the number of entries is false, and my ROI says #DIV/0 I know what it means but I'm really not good at Excel so I dont know how to fix it. Thanks.

[Slim Pickens]

There were many bugs in that version. There are fewer in this one (http://www.mowrmowr.com/poker/SUPER_SNG_w_UI_v2.2e.zip). The next generation (though only kinda ultimate, certainly not the most ultimate) will be out soon.

Slim