Excel Cheatsheet Generator (1 Viewer)

[Mike]

Sounds to me Bobcat like you didn't hit Compile Cheatsheets back in the Compiler, at least after you changed the setup tab to reflect fantasy scoring. That "Roto Pts" column on the hitters (and pitchers) tabs, should convert to "Fan Pts" for fantasy points leagues. The number should be what you expect for each player, the projected stats against the fantasy points scoring system.

Note: Go back to the Compiler and hit Compile Cheatsheets again. Then, you'll have to run Setup Draft Buddy (again), but BEFORE you hit Buddy Setup, first uncheck the option "Clear Keepers and Draft Picks". If you leave it checked, then it will wipe out the keepers that you've already input... twice now

 

[Bobcat10]

Sounds to me Bobcat like you didn't hit Compile Cheatsheets back in the Compiler, at least after you changed the setup tab to reflect fantasy scoring. That "Roto Pts" column on the hitters (and pitchers) tabs, should convert to "Fan Pts" for fantasy points leagues. The number should be what you expect for each player, the projected stats against the fantasy points scoring system.Note: Go back to the Compiler and hit Compile Cheatsheets again. Then, you'll have to run Setup Draft Buddy (again), but BEFORE you hit Buddy Setup, first uncheck the option "Clear Keepers and Draft Picks". If you leave it checked, then it will wipe out the keepers that you've already input... twice now

Yeah I figured that out. Forgot I downloaded 1.1 of the compiler also and just never hit the button.I noticed that keeper button the first go around. Thanks for the help.

 

[ianfitzy]

Whenever I try to use the draft buddy and hit setup it says there is an error and can't locate the compiler file even though it has the same name in my directory as it does on the excel sheet.

 

[Mike]

Mac or Windows?

For a Mac... and even for Windows I suppose this is one thing to try when having issues... both the Compiler and Buddy need to be open in Excel at the same time. On a Mac, if the Compiler is closed then Buddy can't find it. So, open the Compiler. While it is open, open Buddy. Double check that filename. Hit Setup.

Any luck?

 

[Tiger Fan]

what would i have to do to import my own sounds....cool option, btw

 

[Mike]

Ha. Glad you like it. I think everyone should use the "my kind of team Charlie..." sound from Major League after their baseball drafts.

All you have to do to add your own sounds is first, add a .wav file to the /sounds/ directory. Then on the summary tab in Buddy, if you want to include it in the Timer Sound Effects, put the exact name of your sound file in one of the cells under the heading "Filename". If you want to include it in the Trash Talking Sound Effects, just put the exact name of your sound file on any of those cells, overtop of one of the existing ones.

Click and play.

 

[SouthJersey]

 

[Mr. Pickles]

Players only qualifying at DH are still not worked out properly for leagues using UT as a position. For example, Ortiz is not considered a starter in the Draft Buddy.

 

[Mike]

If you use any of UT, CI, MI, P positions, and additional OF that can be any of LF/CF/RF in cases where the league uses split OF, try this:

Draft Buddy’s Roto Tab Trick

One of the fixes to Draft Buddy involved one of everyone’s favourite features, the roto tab. The roto tab was grabbing stats and projecting standings for each team’s whole roster instead of the starters, just like last year. I swear I fixed that for 1.0, but apparently everything necessary except the final formulas on the roto tab. Now, in 1.1, it does grab only starters, with an exception.

If your league uses any of Utility, Corner Infielder, Middle Infielder, Pitcher (optional starter or reliever) and even Outfield (optional OF when otherwise using split OF positions LF/CF/RF), Draft Buddy cannot figure out who the “starters” should be for these positions. You would not believe how long I spent on this creating the initial release for 2009. In a dynamic environment, it just isn’t that easy to tell the program that “these guys over here” are better than “these other guys over there”.

So, I created a way to trick/force Buddy to include players as starters that the user specifies. Remember, it will pick up starters at the “normal” positions, like 1B and 3B, but not the CI. Say you’ve drafted two players at 1B to fill one 1B starter spot and 1 CI. Both players will show up beside 1B on your by team tab. If you flip to the roto tab though, only one player’s stats show up, the player with a higher projected dollar value.

To get the other guy’s stats included in yours (or another team’s) projected stats and roto standings, go to the by team tab and click on the “+” sign above that team’s name. (You did know about the “+” sign, didn’t you? It unhides each player’s MLB team, projected points or dollar value.) Find the cell, which is empty, between the MLB team and dollar value for the player in question. In that cell, input a small “x”.

That’s it. Now Buddy considers that player a “starter”, and his stats will be included on the roto tab. This improvement to the roto tab should give a much better post-draft picture than last year’s sum of stats for the entire roster.

http://www.myofficepool.ca/blog/2009/03/13...version-update/

Check the link for screen shots to see exactly what cells/steps I'm describing in the text.

 

[Mike]

Plenty of players updated in a big projection update just posted on the website, and for the Compiler. Compiler go to the setup tab and click Update Projections.

To see exactly what players moved up or down, you can review them on the website.

Depth charts are not updated yet, but should be Friday, after which I'll add the depth chart update pages for the Compiler.

 

[Mr. Pickles]

Really impressive work with this thing. If anything, I think it's too big for Excel. Some updates and changes are absurdly slow.

Nonetheless, it's very useful.

 

[Mike]

Thanks Pickles. I don't disagree with you, really. We're stretching the limits of what Excel can do or should do because of the size/speed. It is a continual balancing act. I've been looking at non-Excel options. What finally threw me over the edge was Microsoft pulling Visual Basic support out of Mac Office 2008, rendering the Compiler/Buddy useless for those users. But, I don't have any experience programming anything except some web stuff and in Excel, so it is tough figuring out the best way to go.

 

[Mr.Pack]

How could I use this with a draft format like this:

The first 4 rounds are an auction whereby you get a cap of $100 and everyone has to acquire 4 players. After that a normal Serpentine draft kicks in.

 

[Mike]

Is that a live draft, Mr. Pack? Are you doing the auction and immediately starting the draft after?

 

[Dickie Dunn]

I've used versions of this before for football and always found it useful. Just stumbled upon it again for baseball and I like it.

However, I'm having a problem with the keeper function. I can get all of the players entered, and it gives me the drop-down menus to select the draft spot where they should be protected. However, none of the players are showing up on the team rosters, being entered into the draft positions or being removed from the player pools.

Any idea?

 

[Mr.Pack]

Is that a live draft, Mr. Pack? Are you doing the auction and immediately starting the draft after?

Yes,I set the variables in the compiler to 12 teams and 4 rounds, and it gave me a cheatsheet but the highest dollar player was only $18, I know a few players will go for over $30

 

[Mike]

Is that a live draft, Mr. Pack? Are you doing the auction and immediately starting the draft after?

Yes,I set the variables in the compiler to 12 teams and 4 rounds, and it gave me a cheatsheet but the highest dollar player was only $18, I know a few players will go for over $30

For the dollar values make sure you set the number of players per team to only 4. Otherwise it is going to spread out the dollars across the entire roster for each team, meaning lower dollar values for all players. Hmm... what starters to put in though? That will be tricky. You might have to play around with it. Idea 1: Perhaps run it as an auction for the entire roster, and then adjust the values so the top players get dollar values similar to what your league experiences historically, and set the rest to the minimum bid.Idea 2: Another idea might be set the total starters to 4... use decimals if you like, such as 0.5 1B starters (thinking that about half the teams will acquire a 1B in the auction). Then set up a separate league profile for the entire roster, non-auction, which you will switch to for your rankings after the auction is over.Using Draft Buddy would be similar to the ideas above. Use one file or two.Idea 1: Set the entire draft to an auction draft, for the entire roster. The first 4 rounds are an auction, of course, and you will input the bids and track salaries, etc. through 4 rounds as teams spend up to their $100 cap. Starting round 5, continue to use this Draft Buddy file, with the auction input tab, but just set the nomination order consistent with your serpentine draft. You won't need to change the "Winning Bidder" column. Insert just $1 for all players so the player flows to the rosters and falls off the cheatsheets.Idea 2: Use 2 Buddy files. Set up the first for a 4 round auction with $100 cap. Have the auction. Then after the auction is over, switch over to your second Buddy file which is set up for a serpentine draft for the remaining rounds, with 4 keeper slots. You'll insert the players acquired via the auction into the keeper spots.I prefer Idea 2 for Buddy, but given the draft starts right after the auction, I'm sure the guys in your league are not going to sit around waiting for you to input the keepers. Only potential problem I see with Idea 1 for Buddy is the Nominate Player buttons may be a little funky once you get into the draft part, since Buddy tracks the current pick based on if there is a winning bid amount or not.My best advice is test out one or both of these things in advance to get comfortable it will work as expected for your combo draft. It is flexible so we can make it work for you, but it wasn't explicitly designed for such a task.

 

[Mike]

I've used versions of this before for football and always found it useful. Just stumbled upon it again for baseball and I like it.However, I'm having a problem with the keeper function. I can get all of the players entered, and it gives me the drop-down menus to select the draft spot where they should be protected. However, none of the players are showing up on the team rosters, being entered into the draft positions or being removed from the player pools.Any idea?

Hey champ, thanks. Did you enable macros when you opened Buddy? When you select a draft pick from the drop-down, it triggers a macro to move the player to the draft input tab in the draft slot you selected. Try selecting, pausing a second, and then checking, as it does take a little time for it to do that. Quickly flipping tabs might disturb that process. It should work though. It sounds like you must already have the option "keepers cost a draft pick" checked off in the Compiler, otherwise that drop-down with available draft picks by team would not appear. Does that help? If it still isn't working then shoot the file over to me and I'll take a look. mike@myofficepool.ca

 

[Jetznut674]

i am trying to download this but cant even get on the website? Has this been down?

 

[Bobcat10]

i am trying to download this but cant even get on the website? Has this been down?

It was up a few days ago. Just tried getting there myself, no luck...

 

[Mike]

Back up as of around noon. Sorry about that. Exceeded my bandwidth. That's never happened before.

 

[Mr. Pickles]

Thanks Pickles. I don't disagree with you, really. We're stretching the limits of what Excel can do or should do because of the size/speed. It is a continual balancing act. I've been looking at non-Excel options. What finally threw me over the edge was Microsoft pulling Visual Basic support out of Mac Office 2008, rendering the Compiler/Buddy useless for those users. But, I don't have any experience programming anything except some web stuff and in Excel, so it is tough figuring out the best way to go.

Well, as you may or may not know, myself and others had an Excel-based app previously that was based on VisualBasic. I also produced a very large in-draft app based on the projections/ranking in the VB app that only used formulas (liberal use of VLOOKUP commands). As the size of the thing got bigger, it was really slow, and I can see that your sheets are suffering similar difficulties. We kind of gave up on it (permanent frozen storage) before we could get it ported to something else. The energy barrier was too high for a bunch of guys with full-time jobs and lives to put it together, and getting a programmer to do it proved to be much more challenging than anticipated.

 

[Tiger Fan]

Not up yet, but coming...

http://www.myofficepool.ca/mlb/

To give an update on this one, we are targetting Mar. 1st to release the '09 Cheatsheet Compiler and Draft Buddy. We've had Mar. 1st circled from the beginning, so here is hoping for no hiccups in the final week putting it together.

Just wanted to give a "public recognition" to you guys.....Ended up coming in 2nd in my league....10th year of a keeper league, so pretty competitive.....missed 1st by 1 run or 1 SB.....and I woulda been lost in the draft w/o this.

 

[Lehigh98]

Looks like 2010 is up.

Can you change a players position on here?

Need to all batters together into one category.

(In a "simple" league with no positions requirements, just draft hitters)

 

[Mike]

Not up yet, but coming...

http://www.myofficepool.ca/mlb/

To give an update on this one, we are targetting Mar. 1st to release the '09 Cheatsheet Compiler and Draft Buddy. We've had Mar. 1st circled from the beginning, so here is hoping for no hiccups in the final week putting it together.

Just wanted to give a "public recognition" to you guys.....Ended up coming in 2nd in my league....10th year of a keeper league, so pretty competitive.....missed 1st by 1 run or 1 SB.....and I woulda been lost in the draft w/o this.

First I've seen this Tiger Fan, thanks! Glad to hear it worked so well for you last year.

 

[Mike]

Looks like 2010 is up.Can you change a players position on here?Need to all batters together into one category.(In a "simple" league with no positions requirements, just draft hitters)

Hi Lehigh98. I just ran a league the same as my default settings - 5x5 roto - but switched all of the positions to zero except UT (any hitter) to 9 and SP/RP each to zero and P (any pitcher) to 6. Hit Compile Cheatsheets and it looks good. I believe it will work fine for you. If you're in a points league that should be okay too.The positional cheatsheet is still going to arrange the players by position, but the overall cheatsheet will rank all the positions together. Since there is no position scarcity except between hitters and pitchers, it should pretty much have them ranked in order by points or roto value from highest to lowest.Note for those interested, the new web address this year is www.draftbuddy.com

 

[Bobcat10]

Note for those interested, the new web address this year is www.draftbuddy.com

Looks like $10 this year? Just making sure I'm at the right place...

 

[Tiger Fan]

Note for those interested, the new web address this year is www.draftbuddy.com

Looks like $10 this year? Just making sure I'm at the right place...

want to make sure before I pay as well

 

[harryhood]

 

[Mike]

Thanks harryhood. Yes Bobcat and T.F., it is the right place and yes, it is $9.95 this year.

I have given it away for free for a bunch of years, I figured it was finally time to see if anyone would buy it. Hopefully the price doesn't dent people's wallets too much.

 

[DynOmite]

Can it be customized for an AL Only auction?

 

[Mike]

Yes it can.

There are a couple things people have pointed out for AL- or NL-only that could be cleaned up a bit. For example, the ADP tab continues to show players from both leagues. This is something I'm working on, but the cheatsheets can be generated AL- or NL-only with no trouble. Draft Buddy can handle the auction draft.

 

[Tiger Fan]

Although this isn't terribly detailed, it is the PVM method described here:

http://www2.mockdraftcentral.com/articles/460

I had a more detailed article but it has since been taken off the web. I plan to write my own describing the methodology in the Compiler in the near future.

I think you asked last year about being able to see the marginal contribution percentages rather than dollar values in the Compiler. I think we can do that. Unhide the sheet compile_hitters, and scroll across to cell DC9. It has this formula:

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,MAX(FLOOR(CZ9/CZ$7*AR$39+AP$31,AR$31),AP$31),AV9)

Try changing it to this:

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,CZ9,AV9)

which basically deletes the part that calculates the dollar value and inserts the marginal contribution percentage for that player. Copy the formula down for all players, and repeat on the compile_pitchers tabs. Hit Compile Cheatsheets.

Might want to keep a backup copy first in case something happens I'm not expecting. You probably have to change the formatting of the dollar columns on the cheatsheets. Does that help?

Actually, after opening the hidden sheets, it looks like if there was an option to view %s instead of $, all you would need to do is to ask the cheatsheet to read column CZ (Adjusted) on the compile_hitters tab and CE (Adjusted) on the compile_pitchers tab and then you'd have it. Do you agree?

Mike - if I wanted to do this again....do I follow these same instructions as last year? It was very valuable!

 

[Mike]

You know, someone else asked me about this recently, and I sort of assumed it was you

Yes, it should be the same as the prior year. Nothing on the compile_hitters or compile_pitchers tabs changed.

 

[Tiger Fan]

You know, someone else asked me about this recently, and I sort of assumed it was you

Yes, it should be the same as the prior year. Nothing on the compile_hitters or compile_pitchers tabs changed.

Thanks....it wasn't me, but might as well been meFew things for anyone else interested:

1. The formula to get the marginal %'s for hitters is [enter this in cell DC9 of the compile hitters tab (once you unhide)]

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,CZ9,AV9)

2. The formula to get the marginal %'s for pitchers is [enter this in cell CH9 of the compile pitchers tab (once you unhide)]

=IF(OR(AF$26=1,AR$27=TRUE)=TRUE,CE9,AJ9)

3. You're correct in that some reformatting needs to be done on the cheatsheets tab, however it looks like something else needs to happen as well. Once I make the changes outlined in steps 1 and 2 above>compile cheatsheets>it resorts from Z-A, which puts those who show up as a 100% first. For example, Derreck Lee shows up as the #1 first baseman....Pujols doesn't show up until #21, but his marginal % is correct and everyone under him is in the correct order with the correct % shown. It's like this for each position

ETA: The 100% shows up as a result of the vlookup portion of the formula in column DJ. In the example I used above, since Derrek Lee doesn't exist in column DA, the vlookup formula returns "N/A" so by default it goes to 100%. I tried to find a way to automate this, but I'm having trouble figuring out where the names in column DA are generated from. The workaround I've found is just entering a 0 in column DJ for any "N/A" that shows up......but not sure how that will be effected once new projections come out.

Understand you're probably swamped right now.....and I could likely put together a manual cheatsheet from this info, but do you know if there is a quick fix to the spreadsheet so once projections are updated, I don't need to do this manually each time?

Thanks - appreciate the patience

 

[Mike]

Hey, no problem at all on the questions. Glad to help.I believe the NAs get converted by the macro to the min bid (likely $1) after copying the info to the cheatsheet. $1 = 100%, bumping them to the top after a re-sort on the cheatsheets.How about you change your formulas to:

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,CZ9+1,AV9)

and

=IF(OR(AF$26=1,AR$27=TRUE)=TRUE,CE9+1,AJ9)

OR, maybe change the min bid setting on the setup tab to 0. That might do it toR, convert the NA to 0 in the formula:

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,IF(ISNA(CZ9),0,CZ9),AV9)

and

=IF(OR(AF$26=1,AR$27=TRUE)=TRUE,IF(ISNA(CE9),0,CE9),AJ9)

All three of those options might work.

 

[Tiger Fan]

Hey, no problem at all on the questions. Glad to help.I believe the NAs get converted by the macro to the min bid (likely $1) after copying the info to the cheatsheet. $1 = 100%, bumping them to the top after a re-sort on the cheatsheets.How about you change your formulas to:

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,CZ9+1,AV9)

and

=IF(OR(AF$26=1,AR$27=TRUE)=TRUE,CE9+1,AJ9)

OR, maybe change the min bid setting on the setup tab to 0. That might do it toR, convert the NA to 0 in the formula:

=IF(OR(AR$26=1,AR$27=TRUE)=TRUE,IF(ISNA(CZ9),0,CZ9),AV9)

and

=IF(OR(AF$26=1,AR$27=TRUE)=TRUE,IF(ISNA(CE9),0,CE9),AJ9)

All three of those options might work.

Thx - setting the min bid to $0 is the easiest! Didn't even think of that.Now, about those Zobrist projections

 

[ditka...mike ditka]

Does this compiler work well for head to head leagues?

 

[Mike]

Hi d.m.d. My take on head to head vs. total points or season total leagues, is that the rankings are not really any different. You want players who are most likely to produce big week-to-week, and those players are the ones with the highest season projections. I take the same approach with football. I wrote about this in some more detail (and more eloquently I think - I'm a little tired tonight), here:

http://www.draftbuddy.com/forums/viewtopic.php?f=2&t=204

So, to answer your question, there is no explicit option in the Compiler for a head to head league vs. a total points league.

There is roto vs. fantasy points, which is the type of scoring. You could have head to head roto or head to head fantasy points. The Compiler will handle both.

Cheers.

 

[ditka...mike ditka]

Hi d.m.d. My take on head to head vs. total points or season total leagues, is that the rankings are not really any different. You want players who are most likely to produce big week-to-week, and those players are the ones with the highest season projections. I take the same approach with football. I wrote about this in some more detail (and more eloquently I think - I'm a little tired tonight), here:

http://www.draftbuddy.com/forums/viewtopic.php?f=2&t=204

So, to answer your question, there is no explicit option in the Compiler for a head to head league vs. a total points league.

There is roto vs. fantasy points, which is the type of scoring. You could have head to head roto or head to head fantasy points. The Compiler will handle both.

Cheers.

You sold me with your quick response! I just bought, username sloth54. Thanks for your help!

 

[Bobcat10]

Is there an easy way to print the compiler cheatsheet in a good format? Last night I copy/pasted chunks of it onto excel worksheets, formatted, then printed. Came out ok I guess...just takes time.

 

[Mike]

Hi d.m.d. My take on head to head vs. total points or season total leagues, is that the rankings are not really any different. You want players who are most likely to produce big week-to-week, and those players are the ones with the highest season projections. I take the same approach with football. I wrote about this in some more detail (and more eloquently I think - I'm a little tired tonight), here:

http://www.draftbuddy.com/forums/viewtopic.php?f=2&t=204

So, to answer your question, there is no explicit option in the Compiler for a head to head league vs. a total points league.

There is roto vs. fantasy points, which is the type of scoring. You could have head to head roto or head to head fantasy points. The Compiler will handle both.

Cheers.

You sold me with your quick response! I just bought, username sloth54. Thanks for your help!

Thanks. I just swung by the forum by chance and noticed this post bumped to the top.

 

[Mike]

Is there an easy way to print the compiler cheatsheet in a good format? Last night I copy/pasted chunks of it onto excel worksheets, formatted, then printed. Came out ok I guess...just takes time.

Hey Bobcat. If you go to the cheatsheets tab in the Compiler, and hit Print Preview, does it not show the cheatsheets printed nicely across 4 pages? Hitters positional cheatsheet on 2 pages (it was too wide for 1 page), and the pitchers positional on 1, and overall on 1. That is the way I set it up so the printing should be fairly easy. No copying/pasting into different worksheets, etc. Are you not finding that to be the case?

 

[Bobcat10]

Is there an easy way to print the compiler cheatsheet in a good format? Last night I copy/pasted chunks of it onto excel worksheets, formatted, then printed. Came out ok I guess...just takes time.

Hey Bobcat. If you go to the cheatsheets tab in the Compiler, and hit Print Preview, does it not show the cheatsheets printed nicely across 4 pages? Hitters positional cheatsheet on 2 pages (it was too wide for 1 page), and the pitchers positional on 1, and overall on 1. That is the way I set it up so the printing should be fairly easy. No copying/pasting into different worksheets, etc. Are you not finding that to be the case?

I got it now. I was workin' from the web version yesterday (didn't save it as excel file) so I think it was trying to use the IE printing features. Thanks Mike.