Power Pros Forum

A community-run forum for Power Pros
It is currently Thu Mar 28, 2024 7:43 am

All times are UTC - 5 hours [ DST ]




Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: Spreadsheet with MLBPP ratings and some abilities formulas
PostPosted: Sat Dec 10, 2016 5:51 pm 
AAA Starter
AAA Starter
User avatar

Joined: Sun Apr 19, 2015 9:57 am
Posts: 60
Location: NY
Favorite Team: Astros
Console '07: Nintendo Wii
Console '08: Nintendo Wii
Favorite Japanese title: (Vita) Jikkyou Powerful Pro Yakyuu 2012
You can view my spreadsheet: https://docs.google.com/spreadsheets/d/ ... sp=sharing

You can get your own hard copy here: http://www.mediafire.com/file/7d6656205 ... pdate.xlsx
The file above that can be downloaded can't actually be used in Microsoft Excel, so open it up in Google Sheets instead

***If you want to help enter in data for 2017 updates then let me know and I can share the spreadsheet with you***

What is the purpose of this spreadsheet?

Every year people want to update their rosters, but it seems to take forever. It seems the cycle is, 1) find Jag's post, 2) Find which stat I need, 3) find stat on fangraphs, 4) Find what stat comes out to for MLBPP, 5) Enter in MLBPP, 6) Repeat 1-5.

This all just takes so much time to do. My spreadsheet has columns for what stat you need. Right below that is a spot for you to enter the stat. In a blink of an eye the MLBPP rating is in the adjacent box. As you can see my process isn't that much different to the one that I ripped on above, but mine will save you anywhere from 10 seconds to 45 seconds depending on the stat you are looking for. Think about it, this would save you about 10 seconds for each time you need a power rating. Say there are 12 hitters per team. This will save you 2 minutes per team. Now that deosn't seem like a lot, but it saves you 1 HOUR if you update every team!!!

Also if you are like me and have study hall as your last class of the day sometimes then you can enter in the data and have it all there for whenever you want to edit in MLBPP.


How to use:


Hitters:


1) Enter in data for team, player, position, number, bat, throw
2) in column “G” enter in their HRs from last year (can be found on fangraphs Dashboard section)
3) in column “I” enter in their batting average (can be found on fangraphs Dashboard section)
4) in column “K” enter in their .ISO (can be found on fangraphs Advanced section)
5) in column “M” enter in their speed rating (can be found on fangraphs Advanced section)
6) in column “O” enter in their Arm Strength rating (can be found on fangraphs Fans Scouting Report section)
7) in column “Q” enter in their Defense Rating (can be found on fangraphs Value section)
8 ) in column “S” enter in their Fielding Rating (can be found on fangraphs Value section)
9) in column “V” enter his hits from last year (can be found on fangraphs Standard section)
10) in column “X” enter in their HRs from last year (can be found on fangraphs Dashboard section)
11) in column “Z” enter in their K% (can be found on fangraphs Dashboard section)
12) in column “AB” enter in their batting average (can be found on fangraphs Dashboard section)
13) in column “AD” enter in the difference between his BA for L vs R (can be found on fangraphs Splits, Standard section)
Ex. 1) Player bats .279 vs R
Player bats .258 vs L
.258 - .279 (always L AVG - R AVG) = -.021


Ex. 2) Player bats .257 vs R
Player bats .289 vs L
.289 - .257 (always L AVG - R AVG) = .032

14) in column “AF” enter in the difference between AVG W/RISP vs Regular AVG (can be found on fangraphs Splits, Standard section)
Ex. 1) Player bats .289
Player bats .269 W/RISP
.269 - .289 (always AVG W/RISP - Reg AVG) = -.020


Ex. 2) Player bats .274
Player bats .291 W/RISP
.291 - .274 (always AVG W/RISP - Reg AVG) = .017


Pitchers:


1) Enter in data for team, player, position, number, bat, throw
2) in column “G” enter in their Max Velocity, round to nearest whole number (can be found on fangraphs PITCHf/x, Standard section)
3) in column “H” FOR STARTERS ONLY, find innings pitched and divide by games started, enter it in (can be found on fangraphs Dashboard section)
3.5) in column “J” there is info for stamina for swingmen, MRP and CP. Use that for stamina
4) in column “K” enter in BB/9 (can be found on fangraphs Dashboard section)
5) in column “M” enter in 2SFB avg IF the player pitches it (can be found on fangraphs PITCHf/x, Standard section)
6) In columns “O”, “R”, “U”, “X” and “AA” enter in name of each pitch (you do not need to use every column because some pitchers don’t throw 5 diff pitches)
7) in columns “P”, “S”, “V”, “Y” and “AB” enter in the AVG of each pitch (can be found on fangraphs PITCHf/x, Standard section)
8 ) in column “AE” enter in their LOB% (can be found on fangraphs Dashboard section)
9) in column “AG” enter in their K/9 (can be found on fangraphs Dashboard section)
10) in column “AI” enter in their Fastball AVG (can be found on fangraphs PITCHf/x, Standard section)
11) in column “AL” type in Spin 4, Spin 3, Spin 2. You will know which based on the notes in column “AJ”
12) in column “AM” enter in the difference between his BA for L vs R (can be found on fangraphs Splits, Standard section)
Ex. 1) R players bat .279 vs player
L players bat .257 vs player
.258 - .279 (always L AVG - R AVG) = -.021


Ex. 2) R players bat .257 vs player
L players bat .289 vs player
.289 - .257 (always L AVG - R AVG) = .032

_________________
jag123jg wrote:
I'll help, and partially cover for AOW:

Horns, that rapid drum tapping thing, my n-word, n-word, drugs, weed, guns, money, bitches, pussy, pussy ass bitches, bitch ass n-word, shawty, saying the same thing over and over to fill up space, simple sentences that have no context, saying the same thing over and over to fill up space, blat blat, pussy shit, porsche


jag123jg wrote:
Are you guys actually *(censored)* stupid? Learn to wait for a 3-5 game sample size and then read stats before making players, dumbasses


Last edited by PEDs on Sat Dec 10, 2016 6:21 pm, edited 2 times in total.

Top
 Profile  
 
 Post subject: Re: Spreadsheet with MLBPP ratings and some abilities formul
PostPosted: Sat Dec 10, 2016 5:59 pm 
Community Manager
Community Manager
User avatar

Joined: Sat Dec 20, 2008 12:01 pm
Posts: 9585
Location: Michigan
Favorite Team: Tigers
Console '07: Nintendo Wii
Console '08: Nintendo Wii
Favorite Japanese title: (Wii) Jikkyou Powerful Major League 2009
I'd like to have access to edit the spreadsheet. I'd like to do these for the Tigers updates I promised dusty I'd do.

I'm also curious to see what formulas you used for the spreadsheet. Are they ones we've known about, or are these ones you created yourself? Just genuinely curious.

EDIT: Just grabbed the hardcopy; can't wait to use it!

_________________
My YouTube

"Another year older, another year better at Power Pros!" -Me
"Best way to get laid: Tell her you're the community manager of a baseball video game forum." -SkittleMonster
"The Chronicles of Dish's Love Life. I'd read it." -BrewersFuzz
"Find a way to be alone in a baseball stadium at sunrise, when the only sound you hear are about nine birds that got lost, and found themselves in a stadium, and they’re chirping across the grandstand trying to figure out where the *(censored)* they ended up." -Dan Besbris

Twitter: @robtoml_statman


Top
 Profile  
 
 Post subject: Re: Spreadsheet with MLBPP ratings and some abilities formul
PostPosted: Sat Dec 10, 2016 6:11 pm 
AAA Starter
AAA Starter
User avatar

Joined: Sun Apr 19, 2015 9:57 am
Posts: 60
Location: NY
Favorite Team: Astros
Console '07: Nintendo Wii
Console '08: Nintendo Wii
Favorite Japanese title: (Vita) Jikkyou Powerful Pro Yakyuu 2012
Most of the formulas were the ones Jag had in his post about how he makes his players. A few came from Dusty and one I made for ERes based on Dusty's Arm/FLD/ERes formula

_________________
jag123jg wrote:
I'll help, and partially cover for AOW:

Horns, that rapid drum tapping thing, my n-word, n-word, drugs, weed, guns, money, bitches, pussy, pussy ass bitches, bitch ass n-word, shawty, saying the same thing over and over to fill up space, simple sentences that have no context, saying the same thing over and over to fill up space, blat blat, pussy shit, porsche


jag123jg wrote:
Are you guys actually *(censored)* stupid? Learn to wait for a 3-5 game sample size and then read stats before making players, dumbasses


Top
 Profile  
 
 Post subject: Re: Spreadsheet with MLBPP ratings and some abilities formul
PostPosted: Thu Dec 22, 2016 5:53 pm 
Power Pro Legend
Power Pro Legend
User avatar

Joined: Fri Aug 07, 2015 7:15 pm
Posts: 2367
Location: 南ジャージー
Favorite Team: Angels
Console '07: Nintendo Wii
Console '08: Nintendo Wii
Favorite Japanese title: (Wii) Jikkyou Powerful Major League 2009
Hey if you have time PEDs could you explain me about what FLD and stuff I am putting in or
Just help me do this.
Eric Davis 44 wrote:
OK, let’s do one together.

1) Open a clean Excel spread sheet.

2) Click the following link (http://www.fangraphs.com/leaders.aspx?p ... 09&month=0), click ‘Export to Excel,’ click ‘Open,’ and copy/paste it to your clean Excel sheet.

3) Close the fangrpahs.com window, go back to your excel sheet and delete every column except: Name, RF/9, RngR and UZR/150. You might want to keep the other columns in the future, but let’s keep it clean and simple.

4) Insert two new columns after RF/9, RngR and UZR/150 and name them RF/9z and RF/9x, RngRz and RngRx, 150z and 150x.

So, your excel spread sheet should currently have columns A (Name) through J (150x) and rows 1 (Name) through 20 (Vernon Wells).

5) Highlight cell B21 (under the last RF/9 score), click the arrow next to the AutoSum button (the Sigma symbol), click ‘Average’ and hit Enter.

When you highlight the cell you should see: =AVERAGE(B2:B20). You can also enter the functions manually.

6) Now highlight cell B22 (under RF/9 average), click the arrow next to the AutoSum button, click ‘More Functions,’ find and click STDV, make sure the range is B2:B20, and then click Enter. I enter STDEV manually to avoid the mouse clicking

You should see this in the function window: =STDEV(B2:B20).

7) Copy/paste cells B21 and B22 under the other two columns of data, RngR and UZR/150. Now we have averages and stdevs for all three columns.

8] Highlight cell C2 and enter the following function in the fx window: =SUM(B2-$B$21)/$B$22. In other words, subtract Franklin Gutierrez’s RF/9 score from the RF/9 average, and then divide by the stdev. The $ symbol locks that specific part of the function so we can apply to the other players without having to re-enter the function. So highlight cell C2 again, hold/drag it down to cell C20 and click Enter. Now we have RF/9z scores for all of the players.

9) Highlight all of the data in rows 1 through 20 (not 21 and 22) and click: Data / Sort / Sort by RF/9z / Descending / OK. The players should now be ranked in descending order.

10) Highlight cell D2 and enter the following function in the fx window: =SUM(C2-$C$20). In other words, subtract Adam Jones’ RF/9z score from the lowest RF/9z score. Again, we used the $ symbol so we can subtract everyone’s RF/9z score from the lowest RF/9z score. So highlight cell D2, hold/drag it down to cell D20 and click Enter.

11) There is one last step before we move to RngR. Because we will be sorting again, our previous functions will get screwed up as things move out of order. The easiest way to avoid this is to copy our created data (C2 through D20), paste it to a message board window (seriously – a reply window right here at MLBPP or at fangraphs will do the trick), copy it from the message board window, and paste it back over the top on our excel sheet. Now our numbers are hard instead of the being reliant on the functions which created them.

12) Repeat steps 8 through 11 for RngR and UZR/150.

13) Add two more columns after 150x, and name them Score and FLD. Use 0 decimal places for the FLD column.

14) Highlight cell K2 (Score) and enter the following function: =AVERAGE(D2,G2,J2). Highlight cell K2 and hold/drag it down to K20. In other words, average the three x-scores for each player.

15) Highlight all of the data in rows 1 through 20 (not 21 and 22) and click: Data / Sort / Sort by Score / Descending / OK.

16) Now it’s time to turn these numbers into our FLD ratings. As you can see, Franklin Gutierrez is the highest rated defensive CF by a sizeable margin. He is currently one of the premier defenders in baseball, regardless of position, and probably deserves a 15 rating for his glove, range and reaction time. As the highest rated player, he is out benchmark.

NOTE: The highest rated player will usually be the benchmark when calculating this way. For roto baseball, the benchmark is actually the lowest ranked draftable player at each position, but we aren’t using dollars here, we are using a 15-point scale.

So . . . highlight cell L21 and enter: =SUM(15/K2). K2 is Franklin Gutierrez’s Score and the number 15 represents his FLD rating.

17) Highlight cell L2 and enter the following function: =SUM(K2*$L$21). Highlight cell L2 and hold/drag it down to L20. Bingo!

That’s how you do it. Now, in this example you’ll notice Vernon Wells has a FLD rating of 0. That’s because he was the worst in all three categories in our small sample of players. That won’t happen when you increase your sample to include more players.

The key is deciding who your benchmark is and what his rating should be. For example, maybe you believe Gutierrez has been so good, he is tipping the scales and everyone not named Gutierrez should have higher FLD ratings. No problem. Same process as step 17:

Highlight cell L21 and enter =SUM(14/K3). In other words, K3 is Carlos Gomez’s Score and the number 14 represents his FLD rating. Then in cell L3, enter: =SUM(K3*$L$21). Highlight cell L2 and hold/drag it down to L20. As you can see, most of the CF’s received a 1-point bump to their FLD ratings.

Again, because our sample size was small, guys like Shane Victorino and Jacoby Ellsbury got the shaft. They move up the scale once you increase the sample size to include all OF’s, so don’t use these as your actual ratings. You can go down the list and manually increase/decrease the ratings as you see fit, but with enough innings, this is a pretty accurate indicator of a player’s true defensive ability. Guys like Victorino and Ellsbury are deceiving. They appear to be excellent defenders due to their blazing speed, but their speed is often nullified by below average reaction time, misreads, and poor route running.

If you feel there guys are ranked too low, go ahead and do away with RF/9 and just use the other two. Also, pay attention to the Innings. A crappy 3B may have had a great 150 innings at SS but that doesn't make him a great SS. I suggest you break your players up into 2 or 3 groups based on innings played, and use more conservative ratings for players with fewer innings.

That’s about it. The step-by-step guide makes it appear hard and time consuming, but once you get the hang of it, you can hammer these out in just a few minutes.

_________________
Dynasties That Never Were
Dynasties That Never Were MLBPP Stats
2011 San Francisco Giants Log
Cleveland Browns : The Race For Last Place
2018 MLBPP Update


Top
 Profile  
 
 Post subject: Re: Spreadsheet with MLBPP ratings and some abilities formul
PostPosted: Fri Dec 23, 2016 9:39 pm 
AAA Starter
AAA Starter
User avatar

Joined: Sun Apr 19, 2015 9:57 am
Posts: 60
Location: NY
Favorite Team: Astros
Console '07: Nintendo Wii
Console '08: Nintendo Wii
Favorite Japanese title: (Vita) Jikkyou Powerful Pro Yakyuu 2012
tbh I have no clue what Eric Davis did for his fielding. Seems like he had a little too much time on his hands lol. If you want to find the stats to use for my formulas here are how to find the defensive stuff...

http://i.imgur.com/K3bCJbw.jpg

_________________
jag123jg wrote:
I'll help, and partially cover for AOW:

Horns, that rapid drum tapping thing, my n-word, n-word, drugs, weed, guns, money, bitches, pussy, pussy ass bitches, bitch ass n-word, shawty, saying the same thing over and over to fill up space, simple sentences that have no context, saying the same thing over and over to fill up space, blat blat, pussy shit, porsche


jag123jg wrote:
Are you guys actually *(censored)* stupid? Learn to wait for a 3-5 game sample size and then read stats before making players, dumbasses


Top
 Profile  
 
 Post subject: Re: Spreadsheet with MLBPP ratings and some abilities formul
PostPosted: Mon Mar 20, 2017 4:21 am 
Rookie
Rookie
User avatar

Joined: Mon Mar 20, 2017 4:05 am
Posts: 24
Favorite Team: Giants
Console '07: Nintendo Wii
Console '08: Sony PS2
Favorite Japanese title: Don't Own
registered just to say thanks for making this spreadsheet, it's really useful. I'd love to have a copy with new players/stats data merged, if you do have that. I appreciate useful tools like this because I just got into the game again after years and am into customizing/modernizing. Trying to edit players and plug-in player codes from these forums. Thanks.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 27 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group