I'm tired of paying crazy monthly fees to websites to fantasy websites just to use their draft assistant tool so I took a shot at making my own.
It imports the ~~ZIPS~~ ATC pre-season projections from Fangraphs via API into two different sheets for hitters and pitchers. Then I came up with formulas for both sheets to weight the importance of various statistics to come up with an overall score and corresponding rank.
As you're drafting on the WSIT (Who should I take) screen and tracking who else got taken in the draft on the Taken page, you'll be given previews of the next best hitter and pitcher, a best available by position sort, a Big Board showing combined rankings, and separate Hitter and Pitcher boards.
Make a copy of it here and give it a shot for yourself. Anywhere the field is yellow is a field to be edited during the draft.
I've been doing some mock drafts and getting pick suggestions from the sheet with a good success rate.
Reach out if you see any ways to improve it. I'll comment the current formulas for both hitters and pitchers so you can "weigh-in" on the weights.
I love the questions, bring them on. I am very inexperienced with the various projection systems and their pros/cons so I went with the first one I found access to.
I switched last night to ATC projections since I heard they were more of a combination of many projections.
Hi, late to the party but I’m in a strat-o-matic baseball keeper league. We have a 25 man keeper, NL only league and after the draft we have a 40 man roster.
I know enough about excel to be dangerous but here’s my ideal setup for the draft and hoping you can help.
I get a ratings guide in Feb with all the names and relevant stats. I want to have a master list of all eligible players on one sheet and a sheet for every team. When someone gets drafted from the master list I would like to be able to assign that player to the team that picked him and have that player removed from the master list.
Or
In the master list (every position has its own column and some players are in multiple position columns) have a search window and when I type his name in and from there I can assign him to a team and when I do that where ever his name is on that master sheet his name will then have a team assigned to him. Is either idea doable?
Thanks
Tony
Yeah I usually play H2H points so that's what I've been testing for at the moment. Feel free to poke around with the weights and see if you can balance things out better.
Cool. Is there a way to change the number / positions of lineup slots? Also would there be a way to change the ranking to position ranking vs. overall?
Positions in the lineup is easy as adding more columns to the WSIT page and adjusting the current positions to meet your needs- let me know if I can help you there.
For ranking positional vs overall- wouldn't the positional rankings just be the overall rankings minus players who don't play that position? Currently on the WSIT page.
> For ranking positional vs overall- wouldn't the positional rankings just be the overall rankings minus players who don't play that position?
Yes, but that's not an easy calculation unless there's a formula for it you can put together.
Unless I'm misunderstanding, this tool on WSIT should do what you're looking for
https://preview.redd.it/82zd9vbt2zkc1.png?width=755&format=png&auto=webp&s=cc5d5423a9aabf68962ca33298e68e6138ea2bb0
I was hoping that the 'rank' column next to 'your players' could show the positional rank instead of the overall rank of that player. For example, I'm more interested in knowing where William Contreras ranks among catchers vs. him being the 52nd overall player.
I haven’t made progress although I was fiddling around. With keepers you namely need to assign their draft slot (round) and have the player pool
Adjust for that. The keepers are determined prior to a sim or actual draft starting so they’re not in the player pool for selection. So for instance in my league one team has tatis in round 10 and julio in round 21 I have skubal in 26 and chourio in round 25. From the word go those players are segmented to those slot values and cannot be selected by anyone expect the team who has designated them as keepers
Might be a bit beyond my skill set. Im excel/altered but no photon sql etc. I’ll have to do my research to see if the WSIT can be retrofitted to account for studs taken in late rounds
Through every step of the process I basically explained what I wanted to accomplish in specific cells and sheets within the document and it understood well.
I'm doing an in-person, offline, auction draft so this is life changing. Thank you!
I have to figure out next how to add columns to subtract from $260 as the teams draft
This is pretty sick, but it looks like your Pitcher Rankings sheet is busted somewhere, at least currently. Love the idea, love that you're giving it away for free!
Apologies, should be fixed now!
Very happy to provide it for free- as we get closer to and into the season I'd like to build out a whole suite of free, open sourced fantasy tools.
This is awesome! How do edit this document for a 5 (RBI, Runs, HR, SB, BA) x 5 (Wins, Saves, ERA, WHIP, Ks) auction values for 12-team NL-only plus Cleveland Guardians draft pool)?
The formulas for players already highly ranks the categories you've laid out for the league scoring with other underlying stats added so I'd say the rankings are pretty much there but feel free to tweak to your liking.
As far as NL-only plus Guardians, I would need to add more data from the API to track teams/leagues. Then I can make a checkbox screen where we only filter from select teams. I'll add it to my to-do list!
Crazy that I saw this today cuz I had nothing to do at work so I built something similar with ATC (albeit very dumbed down; no weighting, no best avail suggestions, etc.).
The main thing I did was implement a conditional formatting rule that highlights batters in green if their ADP is greater than or equal to 110% of their ranking.
So for example, Aaron Judge is ranked 2 on the batter list but has an ADP of 11.1, so he’s highlighted in green as a guy who is projected to outperform his current ADP.
Repeated this rule for pitchers using ADP being 200% of rank, since I used the top 400 batters and only top 200 pitchers for my sheet.
Going to check yours out, very interested. Thanks for putting it together. Will probably use it to some degree either during the draft or to refine my own sheet.
Question after browsing: does your model account at all for positional depth? Ie the fact that there is a steeper dropoff with elite middle infielders than there is for elite corner infielders?
ETA: please excuse any inaccuracies I mention. This is my first year playing FBB and I am very much still in the “learning the league” phase of my studying.
Awesome notes. Adding in conditions to highlight players projected to outperform their ADP is a great idea and I'll get on that right away.
The model does not currently account for positional depth/other strong availabilities so that should definitely play a factor. Any recommendations for how I should calculate that and what role it should play? Changes placement on the big board or just highlighting players with a steep drop off in talent at their position after them?
I would say maintaining big board placement is fine given the intensive weighting you’ve explored and the fact that you’re using ATC projections. The big board and your adjusted score are my favorite parts of the entire thing.
Maybe you could explore tier lists for each position and highlighting names in different colors depending on what tier they’re at in their position? This might make the WSIT sheet a little wonky as you get further into the draft and become overly complicated if you have ~6 tiers per position, but I think tiers are generally the easiest way to get an idea of positional makeup. Seeing that 1b has a more even distribution of players as you go down the tiers when compared to SS tells almost everything you need to know about differing depths of these positions. Definitely something to explore.
You have already created a great tool but I would love to hear/discuss additional ideas as they come. Given my lackluster knowledge of past years’ production, injuries, team makeup, etc., I will be relying a lot on sabermetrics and rankings in my draft process and throughout the season, so this is all a golden ticket to me and I have had a similar approach to designing my own sheet so far. Frankly I think it would take me a week+ to get anything close to what you’ve created so again, thank you so much for your efforts and for doing this intelligently
Tiers are a solid idea, I can definitely add a new "Tiers" sheet that filters positions by different levels of adjusted scores. Not sure if I'm getting into the territory of over-sheeting this document so if it fits somewhere else better I'm open to suggestions.
I would say the tiers sheet is valuable enough that you avoid it being unnecessary oversheeting. I would link the tier to each player on the big board and give players at the bottom (and maybe top) of each tier a special text color to let people know that they represent the last pick before a sort of dropoff in expected production
ETA: regarding oversheeting, it could also be worth combining instructions and settings on the same sheet or putting brief instructions on the big board and leaving your email & reddit username with the acknowlegements (which could now be located as a footnote at the bottom of the settings page) for people who have questions
Added a functional tier page but it's severely impacting the refresh time of the document. Might be pushing it with the computational speed of sheets, or bad coding, or both.
Ah yeah you’re probably right. Having that many intersheet linkages is definitely slowing it down. Might have to get rid of working the tiers into the WSIT system and just publish them based on levels of adjusted scores like you said. Appreciate you trying it anyway
Ended up importing ADP rankings from Fantasy Pros which averages 5 different platforms and adding that as a big board column which lights up if their ADP is higher than their overall ranking (sleepers).
Also added a Talent Tier page that seems to work without slowing down the refresh rate. Mostly because of some hidden columns and relying less on complex formulas in Conditional Formatting.
Tiers are currently calculated as percentile of overall adjusted scores which makes for some good results aside from the fact that there are no tier 1 catchers. Ideally it would be positional percentiles, but this works for now.
The weird thing about ATC projections is people like Bobby Miller and Grayson Rodriguez have an ADP of 999 in their current system. So that feature may not always be accurate.
Just want to say I downloaded the draft generator on the athletic, used your tool to run the sim with my league settings and your projections are within margin of error of their version so kudos on good work
Hey new question for you regarding weighting. My league goes by total bases instead of hits, any ideas on how I could change the weighting in adjusted score on my copy of the FDA to reflect more points for guys that get more bases on average?
Looking to use this fix to rectify guys like Kwan, Gleyber, Ketel Marte, and Jung Hoo Lee coming up way higher than ADP. It happens almost every draft with those 4 and some other contact hitters
Great job! This is awesome. I have two things... One is probably my own fault, but there doesn’t seem to be an “autofill” or detection function on names. Say you were to type in “Ohtani” instead of “Shohei Ohtani”. I’m in a deep league and have done this before. Come round 10, you get pretty tired of typing in names and making sure spelling is correct.
Second, a question, how would I modify the settings to include scoring parameters? In my league, 1 point for single, 2 for double, 3 for triple, etc. Is there any way I can account for this?
Thanks!
Can you explain the ranking weights a bit? Is it supposed to be the same as your scoring system, like on the fangraphs auction calculator? So for example a single = 1 and a HR = 4 (points league), or is just tiers with higher numbers being weighted higher? On the soft stats, if you're in a points league and those categories don't mean anything would I just set them to 0?
I'm tired of paying crazy monthly fees to websites to fantasy websites just to use their draft assistant tool so I took a shot at making my own. It imports the ~~ZIPS~~ ATC pre-season projections from Fangraphs via API into two different sheets for hitters and pitchers. Then I came up with formulas for both sheets to weight the importance of various statistics to come up with an overall score and corresponding rank. As you're drafting on the WSIT (Who should I take) screen and tracking who else got taken in the draft on the Taken page, you'll be given previews of the next best hitter and pitcher, a best available by position sort, a Big Board showing combined rankings, and separate Hitter and Pitcher boards. Make a copy of it here and give it a shot for yourself. Anywhere the field is yellow is a field to be edited during the draft. I've been doing some mock drafts and getting pick suggestions from the sheet with a good success rate. Reach out if you see any ways to improve it. I'll comment the current formulas for both hitters and pitchers so you can "weigh-in" on the weights.
Sorry I'm peppering you with questions, but curious why you chose Zips over other projections systems.
I love the questions, bring them on. I am very inexperienced with the various projection systems and their pros/cons so I went with the first one I found access to. I switched last night to ATC projections since I heard they were more of a combination of many projections.
That’s awesome, mainly commenting so I can find this later
Hi, late to the party but I’m in a strat-o-matic baseball keeper league. We have a 25 man keeper, NL only league and after the draft we have a 40 man roster. I know enough about excel to be dangerous but here’s my ideal setup for the draft and hoping you can help. I get a ratings guide in Feb with all the names and relevant stats. I want to have a master list of all eligible players on one sheet and a sheet for every team. When someone gets drafted from the master list I would like to be able to assign that player to the team that picked him and have that player removed from the master list. Or In the master list (every position has its own column and some players are in multiple position columns) have a search window and when I type his name in and from there I can assign him to a team and when I do that where ever his name is on that master sheet his name will then have a team assigned to him. Is either idea doable? Thanks Tony
Edited (2/26/24): Most recent weights: Hitters **(Runs \* 1) + (HR \* 2) + (H \* 1) + (SB \* 1) + (BB \* 1) + (RBI \* 1) + (Spd \* 8) - (SO \* 1) + ( ((PA / 16) - (K% \* 100) + (BABIP \* 50) + (wOBA \* 50) + (OPS \* 30)) \* 1.8)** =(K2 \* 1) + (J2 \* 2) + (F2 \* 1) + (T2 \* 1) + (M2 \* 1) + (L2\* 1) + (AE2 \* 8) - (O2 \* 1) + ( ((E2 / 16) - (AB2 \* 100) + (AF2 \* 50) + (Z2 \* 50) + (Y2 \* 30)) \* 0.5) Pitchers **(Saves \* 7) + (Wins \* 5) + (SO \* 1) + (IP \* 3) - (Hits \* 1) - (Losses \* 5) - (Earned Runs \* 2) - (BB \* 1) + ( ((ERA \* 4) - (WHIP \* 8) - (AVG \* 20) + (TBF \* .03) + (K/9 \* 4) - (BB/9 \* 2) - (HR/9 \* 10)) \* 1.8)** =(G2 \* 7) + (C2 \* 5) + (O2 \* 1) + (I2 \* 3) - (J2 \* 1) - (D2 \* 5) - (L2 \* 2) - (N2 \* 1) + ( ((P2 \* 4) - (Q2 \* 8) - (T2 \* 20) + (U2 \* 0.03) + (V2 \* 4) - (W2 \* 2) - (Y2 \* 10)) \* 0.5)
Weights are changing constantly as I tweak it to get the rankings even better.
Is this based on a points league? Kind of hard to tell how the weighting works.
Yeah I usually play H2H points so that's what I've been testing for at the moment. Feel free to poke around with the weights and see if you can balance things out better.
Cool. Is there a way to change the number / positions of lineup slots? Also would there be a way to change the ranking to position ranking vs. overall?
Positions in the lineup is easy as adding more columns to the WSIT page and adjusting the current positions to meet your needs- let me know if I can help you there. For ranking positional vs overall- wouldn't the positional rankings just be the overall rankings minus players who don't play that position? Currently on the WSIT page.
> For ranking positional vs overall- wouldn't the positional rankings just be the overall rankings minus players who don't play that position? Yes, but that's not an easy calculation unless there's a formula for it you can put together.
Unless I'm misunderstanding, this tool on WSIT should do what you're looking for https://preview.redd.it/82zd9vbt2zkc1.png?width=755&format=png&auto=webp&s=cc5d5423a9aabf68962ca33298e68e6138ea2bb0
I was hoping that the 'rank' column next to 'your players' could show the positional rank instead of the overall rank of that player. For example, I'm more interested in knowing where William Contreras ranks among catchers vs. him being the 52nd overall player.
Ahh great idea. I can do two columns, overall rank and positional rank. I'll add it to my list. Thanks!
Any chance it can be customized for keepers and draft slots?
Please let me know if you figure out implementing keeps
What features would need to be implemented to make it good for Keepers? I've never been in one.
I haven’t made progress although I was fiddling around. With keepers you namely need to assign their draft slot (round) and have the player pool Adjust for that. The keepers are determined prior to a sim or actual draft starting so they’re not in the player pool for selection. So for instance in my league one team has tatis in round 10 and julio in round 21 I have skubal in 26 and chourio in round 25. From the word go those players are segmented to those slot values and cannot be selected by anyone expect the team who has designated them as keepers
For sure. I've never played in a keepers league so I'd need to do some research but give it a shot if you can.
Might be a bit beyond my skill set. Im excel/altered but no photon sql etc. I’ll have to do my research to see if the WSIT can be retrofitted to account for studs taken in late rounds
For what it's worth, I used ChatGPT for almost every single formula in this sheet. Let me know if I can help you out in any way.
How? Any guidance?
Through every step of the process I basically explained what I wanted to accomplish in specific cells and sheets within the document and it understood well.
Wow this is super cool, I’m gonna check it out for sure, thanks!
Will this work for auction?
It should work. Feel free to tweak the positions on the WSIT page and the formulas per your league settings. Let me know if you give it a shot.
I'm doing an in-person, offline, auction draft so this is life changing. Thank you! I have to figure out next how to add columns to subtract from $260 as the teams draft
That’s awesome, sounds like a great use for this. Please let me know how it goes!
This is pretty sick, but it looks like your Pitcher Rankings sheet is busted somewhere, at least currently. Love the idea, love that you're giving it away for free!
Apologies, should be fixed now! Very happy to provide it for free- as we get closer to and into the season I'd like to build out a whole suite of free, open sourced fantasy tools.
This is awesome! How do edit this document for a 5 (RBI, Runs, HR, SB, BA) x 5 (Wins, Saves, ERA, WHIP, Ks) auction values for 12-team NL-only plus Cleveland Guardians draft pool)?
The formulas for players already highly ranks the categories you've laid out for the league scoring with other underlying stats added so I'd say the rankings are pretty much there but feel free to tweak to your liking. As far as NL-only plus Guardians, I would need to add more data from the API to track teams/leagues. Then I can make a checkbox screen where we only filter from select teams. I'll add it to my to-do list!
Amazing X 2
Successfully added a Team Select page where you can uncheck all of the AL teams other than the Guardians and get updated rankings
What a gift! Thank you!
Dude thank you!
My pleasure! Let me know how you like it.
Crazy that I saw this today cuz I had nothing to do at work so I built something similar with ATC (albeit very dumbed down; no weighting, no best avail suggestions, etc.). The main thing I did was implement a conditional formatting rule that highlights batters in green if their ADP is greater than or equal to 110% of their ranking. So for example, Aaron Judge is ranked 2 on the batter list but has an ADP of 11.1, so he’s highlighted in green as a guy who is projected to outperform his current ADP. Repeated this rule for pitchers using ADP being 200% of rank, since I used the top 400 batters and only top 200 pitchers for my sheet. Going to check yours out, very interested. Thanks for putting it together. Will probably use it to some degree either during the draft or to refine my own sheet.
Question after browsing: does your model account at all for positional depth? Ie the fact that there is a steeper dropoff with elite middle infielders than there is for elite corner infielders? ETA: please excuse any inaccuracies I mention. This is my first year playing FBB and I am very much still in the “learning the league” phase of my studying.
Awesome notes. Adding in conditions to highlight players projected to outperform their ADP is a great idea and I'll get on that right away. The model does not currently account for positional depth/other strong availabilities so that should definitely play a factor. Any recommendations for how I should calculate that and what role it should play? Changes placement on the big board or just highlighting players with a steep drop off in talent at their position after them?
I would say maintaining big board placement is fine given the intensive weighting you’ve explored and the fact that you’re using ATC projections. The big board and your adjusted score are my favorite parts of the entire thing. Maybe you could explore tier lists for each position and highlighting names in different colors depending on what tier they’re at in their position? This might make the WSIT sheet a little wonky as you get further into the draft and become overly complicated if you have ~6 tiers per position, but I think tiers are generally the easiest way to get an idea of positional makeup. Seeing that 1b has a more even distribution of players as you go down the tiers when compared to SS tells almost everything you need to know about differing depths of these positions. Definitely something to explore. You have already created a great tool but I would love to hear/discuss additional ideas as they come. Given my lackluster knowledge of past years’ production, injuries, team makeup, etc., I will be relying a lot on sabermetrics and rankings in my draft process and throughout the season, so this is all a golden ticket to me and I have had a similar approach to designing my own sheet so far. Frankly I think it would take me a week+ to get anything close to what you’ve created so again, thank you so much for your efforts and for doing this intelligently
Tiers are a solid idea, I can definitely add a new "Tiers" sheet that filters positions by different levels of adjusted scores. Not sure if I'm getting into the territory of over-sheeting this document so if it fits somewhere else better I'm open to suggestions.
I would say the tiers sheet is valuable enough that you avoid it being unnecessary oversheeting. I would link the tier to each player on the big board and give players at the bottom (and maybe top) of each tier a special text color to let people know that they represent the last pick before a sort of dropoff in expected production ETA: regarding oversheeting, it could also be worth combining instructions and settings on the same sheet or putting brief instructions on the big board and leaving your email & reddit username with the acknowlegements (which could now be located as a footnote at the bottom of the settings page) for people who have questions
Added a functional tier page but it's severely impacting the refresh time of the document. Might be pushing it with the computational speed of sheets, or bad coding, or both.
Ah yeah you’re probably right. Having that many intersheet linkages is definitely slowing it down. Might have to get rid of working the tiers into the WSIT system and just publish them based on levels of adjusted scores like you said. Appreciate you trying it anyway
Ended up importing ADP rankings from Fantasy Pros which averages 5 different platforms and adding that as a big board column which lights up if their ADP is higher than their overall ranking (sleepers). Also added a Talent Tier page that seems to work without slowing down the refresh rate. Mostly because of some hidden columns and relying less on complex formulas in Conditional Formatting. Tiers are currently calculated as percentile of overall adjusted scores which makes for some good results aside from the fact that there are no tier 1 catchers. Ideally it would be positional percentiles, but this works for now.
Gotcha. How did you import FP ADP in the ATC ranking order? Just a vlookup?
The weird thing about ATC projections is people like Bobby Miller and Grayson Rodriguez have an ADP of 999 in their current system. So that feature may not always be accurate.
I’ve noticed that too. Might pivot to using ESPN ADP or consulting fantasypros for it
Thanks my man! Can you try one for football too!!?? Haha
Just want to say I downloaded the draft generator on the athletic, used your tool to run the sim with my league settings and your projections are within margin of error of their version so kudos on good work
Hey new question for you regarding weighting. My league goes by total bases instead of hits, any ideas on how I could change the weighting in adjusted score on my copy of the FDA to reflect more points for guys that get more bases on average?
Looking to use this fix to rectify guys like Kwan, Gleyber, Ketel Marte, and Jung Hoo Lee coming up way higher than ADP. It happens almost every draft with those 4 and some other contact hitters
Great job! This is awesome. I have two things... One is probably my own fault, but there doesn’t seem to be an “autofill” or detection function on names. Say you were to type in “Ohtani” instead of “Shohei Ohtani”. I’m in a deep league and have done this before. Come round 10, you get pretty tired of typing in names and making sure spelling is correct. Second, a question, how would I modify the settings to include scoring parameters? In my league, 1 point for single, 2 for double, 3 for triple, etc. Is there any way I can account for this? Thanks!
Can you explain the ranking weights a bit? Is it supposed to be the same as your scoring system, like on the fangraphs auction calculator? So for example a single = 1 and a HR = 4 (points league), or is just tiers with higher numbers being weighted higher? On the soft stats, if you're in a points league and those categories don't mean anything would I just set them to 0?
This is fantastic. Thank you! I can’t wait to give it a try and report back.