So I've created an excel spreadsheet that allows users to dump their league's data into it and calculate WAR for position players. I've done this for pitchers as well, but I need to refine it a little more to make it user friendly before I release it.

To do a complete season, it takes about  20-30 min to dump everything into it.  Offensively it uses wOBA and wRAA to compute runs. Defensively it's a method that MikeT23 used to evaluate RFs, and modified a little by me. I've included instructions for inputting data on each sheet that requires it.

Any questions or tips on improving it are surely welcome. 

Version two of the WAR worksheet for hitters can be downloaded here.

The first version of the pitcher WAR worksheet can be downloaded here.

Batter WAR Version history

V 2
Revamped the defensive runs formula based on mFP developed with jtrinsey.
All defensive runs are based on season positional average, allowing for comparison across multiple years.
Added baserunning component.
Value of a catcher throwing out stolen base runner will automatically update from year to year.
Corrected Omaha's three letter abbreviation.

I still need to verify El Paso's three letter abbreviation. If it's something other than ELP, please let me know and I'll correct it.

This worksheets looks great. I am not excel savvy. How would I input my leagues data into the worksheet?

I think your fielding #s are good but could be improved a bit. I think your "average" baseline is a bit flawed because it only considers average outs per inning.

For example, Pat Woo is credited with -15 FRAA.

However, his fielding% is .978, which is somewhere around average or slightly above average. I used .972 as average for a SS. In my world currently, .978 would be tied for 12th among SS and .972 would be 17th. So .978 is somewhere around average. And he has -5 plays, which is below average for sure; I think an average SS would be about +5 to +10. But that's 5 runs at most. However, he had significantly fewer chances than he would have expected. Good pitching perhaps.

So I think an adjustment there could make an outstanding calculator even better.

Posted by runnrun on 3/19/2013 6:55:00 PM (view original):
This worksheets looks great. I am not excel savvy. How would I input my leagues data into the worksheet?

Just follow the instructions on the yellow tabbed sheets.

I list what statistical menus to use, and the splits you need to have up for each sheet.

Then it's just a matter of copy/pasting the data into the sheet. Hyperlinks and the three letter team abbreviations are important because the hidden cells use it to match up players throughout the workbook.

Posted by jtrinsey on 3/19/2013 7:17:00 PM (view original):
I think your fielding #s are good but could be improved a bit. I think your "average" baseline is a bit flawed because it only considers average outs per inning.

For example, Pat Woo is credited with -15 FRAA.

However, his fielding% is .978, which is somewhere around average or slightly above average. I used .972 as average for a SS. In my world currently, .978 would be tied for 12th among SS and .972 would be 17th. So .978 is somewhere around average. And he has -5 plays, which is below average for sure; I think an average SS would be about +5 to +10. But that's 5 runs at most. However, he had significantly fewer chances than he would have expected. Good pitching perhaps.

So I think an adjustment there could make an outstanding calculator even better.

Yes, the FRAA numbers are somewhat arbitrary. Let me try to explain the thinking behind it.

I started out with MikeT23's Positive Plays per Inning that he used in the catcher in rightfield thread. Essentially it's just adding up put outs, assists, and plus plays, while subtracting errors and negative plays, and dividing by how many innings played.

Well with the modified formula, I've made plus plays worth four, and negative plays worth two. For outfielders, assists are also worth four.

So each of these rate statistics is summed for every position on the field, and divided by total innings played to develop a league average shortstop, for example. For the season I uploaded for people to check out, an average shortstop had a PPI of .571. For Pat Woo, his PPI was .505. So over the course of his 1211 innings, PPI says he's been involved in about 106 plays less than the average SS would have been. 

The reason behind using strictly rate statistics instead of fielding percentage is because, honestly, it's all we've got. Without actual batted ball data from WIS, and good luck getting that, there really is no good way to determine a fielder's relative worth. Also, I think there is a grey area in between + and - plays, that WIS doesn't show us. So if a player makes 5 - plays during the season, I'm thinking there were some balls that he could've got to, but didn't, and weren't marked as a - play by the engine.

But, yes, a good pitching team could theoretically make a fielder look worse than he actually is just because they limit the amount of balls in play. 

Knowing what team Pat Yoo plays for, it could very well be possible that the pretty good pitching on that team is "hurting" him here, but looking at multiple seasons worth of data shows that he's definitely a below average fielder 

The next step of the process is essentially trying to figure out how many runs those 106 plays are worth. In an attempt to scale this statistic to UZR, I figured every 10 plays was worth about 2 runs. Just like UZR, it's a good idea to take a look at fielding data from multiple seasons to figure out a player's defensive capabilities.


Side Note: I forgot to include baserunning data in this. I'm working on that fix right now, and will have an updated sheet probably tomorrow.

I got the PPI thing, however, if you look at Woo again as an example, if you add up his total "chances" (A + PO + E + "+" + "-"), it is significantly less than 0.57 per inning, so even if he had a 1.000 FP and 0 "-" plays, he'd still rate as a negative fielder, which doesn't really make sense. I think it makes more sense to add up all of the chances that a player had (A + PO + E + "-"... I'd leave out + plays as they are essentially "bonus" plays) and take what an average fielder (using historical fielding data from the league) at that position would have done. For example, in the world data I tracked, it seemed an average shortstop was about 0.972 FP and about +8 in +/- per 1200 innings.

So for all the chances Woo got (655), I would have expected an average shortstop to make 655*.972 + 8*(1211/1200) = 644.7 outs, whereas Woo actually made 636 outs. This makes him about -8.7 outs worse than average, and, estimating an out as worth about 0.3 runs, he was about -2.6 in FRAA, by my calculation. Obviously this is significantly different than the -16 you have him credited with.

Of course, I deliberately picked the one where we differed the most that I could find in few minutes of scanning. Most of them are pretty close and often within 1 run of each other. I don't know for sure that my way is better, but I suspect that it is less prone to noise because you are essentially forming your baseline comparison off average performance x average chances whereas I am forming from average performance x actual chances. Something to think about.

The "grey area" thing is interesting. I've never thought about that before, but it certainly seems like it could be the case. The way I see it, you're overestimating + and - plays. For example, a + is listed as 4 plays, which would be 0.8 runs, however, an fly out is (in MLB) only worth about 0.3 runs, and not all + plays are outs, some just save the runner from advancing an extra base. However, if the "grey area" exists and every + play is a proxy for a few more good plays that don't show up, then maybe it's on point.

In any case, it's more of a theoretical discussion. Your spreadsheet is really good and I look forward to parsing it a little bit more. Thanks for sharing.

This is a very cool tool, but damn is it tedious loading all of the data.
A couple of notes:
- Omaha is OMA by WIS... you have it as OMH.  That was a relatively easy fix, but it took a bit to figure out why no one from Omaha was showing up.
- I think something is wrong with the Catcher defensive lookup... it is not pulling the data from the entry sheets for PB, CS, etc.  I can't see anything that I didn't do correct in entering the data, so I think it may be something with the spreadsheet.  I don't know advanced excel well enough to trouble-shoot the "vlookup" command, so I can't figure out what is wrong.

Posted by kcden on 3/20/2013 10:58:00 AM (view original):
This is a very cool tool, but damn is it tedious loading all of the data.
A couple of notes:
- Omaha is OMA by WIS... you have it as OMH.  That was a relatively easy fix, but it took a bit to figure out why no one from Omaha was showing up.
- I think something is wrong with the Catcher defensive lookup... it is not pulling the data from the entry sheets for PB, CS, etc.  I can't see anything that I didn't do correct in entering the data, so I think it may be something with the spreadsheet.  I don't know advanced excel well enough to trouble-shoot the "vlookup" command, so I can't figure out what is wrong.

Yes, Omaha was one of the cities I didn't have the exact three letter abbreviation to, because in all the seasons worth of data I pulled to determine park factors, I was only able to find three seasons worth of Omaha data. So thanks for providing that info. I'll make that change.

As to the extended catching stats, unhide columns A through, I believe, C, and check the entries there. Sometimes when I copy/paste the team abbreviation, it messes those formulas up, which will ruin the rest of the formulas. Otherwise, just make sure that hyperlinks and team names are correct when entering.

What happens is the hyperlink has the unique player ID embedded in it. I remove the rest of the link to just get the ID, and then add the team name onto the end of it. That allows the player to be matched up further in the spreadsheet with the rest of the data to determine WAR. If it was just his unique ID, then if he played on two ML teams in a year, it would only grab the first team's data, missing the second team's data.

So that's where I would check in regards to the expanded catcher stats. The first couple of columns that are hidden on that sheet.

Posted by jtrinsey on 3/19/2013 11:35:00 PM (view original):
I got the PPI thing, however, if you look at Woo again as an example, if you add up his total "chances" (A + PO + E + "+" + "-"), it is significantly less than 0.57 per inning, so even if he had a 1.000 FP and 0 "-" plays, he'd still rate as a negative fielder, which doesn't really make sense. I think it makes more sense to add up all of the chances that a player had (A + PO + E + "-"... I'd leave out + plays as they are essentially "bonus" plays) and take what an average fielder (using historical fielding data from the league) at that position would have done. For example, in the world data I tracked, it seemed an average shortstop was about 0.972 FP and about +8 in +/- per 1200 innings.

So for all the chances Woo got (655), I would have expected an average shortstop to make 655*.972 + 8*(1211/1200) = 644.7 outs, whereas Woo actually made 636 outs. This makes him about -8.7 outs worse than average, and, estimating an out as worth about 0.3 runs, he was about -2.6 in FRAA, by my calculation. Obviously this is significantly different than the -16 you have him credited with.

Of course, I deliberately picked the one where we differed the most that I could find in few minutes of scanning. Most of them are pretty close and often within 1 run of each other. I don't know for sure that my way is better, but I suspect that it is less prone to noise because you are essentially forming your baseline comparison off average performance x average chances whereas I am forming from average performance x actual chances. Something to think about.

The "grey area" thing is interesting. I've never thought about that before, but it certainly seems like it could be the case. The way I see it, you're overestimating + and - plays. For example, a + is listed as 4 plays, which would be 0.8 runs, however, an fly out is (in MLB) only worth about 0.3 runs, and not all + plays are outs, some just save the runner from advancing an extra base. However, if the "grey area" exists and every + play is a proxy for a few more good plays that don't show up, then maybe it's on point.

In any case, it's more of a theoretical discussion. Your spreadsheet is really good and I look forward to parsing it a little bit more. Thanks for sharing.

Alright, I promised this for jtrinsey earlier today when I got home.

I took a look at historical  Hardball Dynasty – Fantasy Baseball Sim Games - Player Profile: Pat Woo stats. First number his Yoo's PPI, the second his the season average PPI for SS.

Season 8 - .581, .612
Season 9 - .534, .600
Season 10 - .544, .557
Season 11 - .548, .549
Season 12 - .577, .551
Season 13 - .591. .562
Season 14 - .550, .558 
Season 15 - .528, .557
Season 16 - .551, .565
Season 17 - .571, .552
Season 18 - .500, .558
Season 19 - .505, .571
Season 20 - .504, .563
Season 21 - .486, .561

I'm going to group these seasons into two distinct groups, seasons 8 through 17, and 18 through 21, because there is a stark contrast after 17. From 8-17, he averaged a PPI of .558, which is just under the league average of .566 for that time period, or about 2 runs below average over 1000 innings. From 18-21 he averaged .499, well below the average of .563 for the time period, or about 13 runs by my estimation over 1,000 innings.

When looking at his ratings it's easy to see why they dropped off. After season 17 his range dropped 4 points down to 81, and he lost a point in arm strength. But lets look at recommended ratings first before discussing Woo specifically: 80, 85, 85, 85 is what WIS gives us for Shortstops to be league average. From 8-17, Woo was 85,88,82,76, give or take a couple points while he was developing in the earlier seasons. So he's got above average range and glove, but below average arm strength, and way below average arm accuracy.

My theory is that his range was compensating for his below average arm during his career, making it appear he was league average, but when that started to drop off then it started to reveal his faults. By the time we get to season 19, his range is down to 80, exactly average, and his arm strength is down to 78, seven points below average.

This is where I'm going to get into the "grey area" I mentioned. It is my belief that there is a "grey area" in between + and - plays, where a player might make a play that might require above average ratings, but might not necessarily be worthy of being distinguished as great or poor by the engine. 

Think of how Zone Rating breaks the field up into different zones for each position. If a player makes a play on a ball outside his zone, he is credited with it , but if he doesn't, it doesn't get count against him. I figure these are + plays in the engine. Now I think there might be another zone, within the larger one, that makes up plays a player should absolutely be making, and if he doesn't, then he is credited with a - play. 

Now this is where the grey area comes in. If a player makes the stop on a ball, good for him, he gets credited with the PO or A, if not, no biggy. I thought over the course of a season this might make itself evident by getting more PO and A, but after running numbers for two years, I found this not to be the case in some instances.

I present to you these three individuals

Here are their season lines, along with their fielding ratings that season:

Player A:  297 PO, 573 A, 44 E, 0 +, 14 -      75, 80, 83, 82

Player B:  267 PO, 521 A, 29 E, 0 +, 6 -     79, 82, 87, 84

 

Player C:  272 PO, 512 A, 17 E, 31 +, 0 -     91, 86, 96, 91 

I left innings out for a reason, but I will say A and C played about the same amount of innings, while player B played about 200 less than A and C.

Who is better? Who would you want playing shortstop given equal hitting ratings?

Obviously Player C, right? 31+ plays, which broke a record in the world he plays in, while only 17 errors over the course of a season?  The guy is a perennial GG candidate. Not so fast says an un-weighted PPI, which says Player B is the best, at about 10 runs, while Player C is second in at around 4. Player B, with his 14 - plays and 44 errors comes in at 2 runs.

A weighted PPI, with + plays worth four, and - plays worth negative two, changes this. Player C becomes the clear front runner, with 19 runs, Player B is second at 6.3, while Player A is -4. 

I will admit that the weights I chose are entirely arbitrary, and I did no statistical work to come up with them, but I figured they are better than running with nothing. I'm willing to work with people to try and come up with a better way of finding out how to weight + and - plays. Maybe it will require taking park effects, and pitching staff into account? It might be worth looking at league average GB/FB data, making adjustments for a the player's pitching staff and park factors.  From what I understand of how the engine works, it first decides if a PA is a walk, hit, or an out, and then decides what kind of hit or out, with park effects first playing a role in outs/hits, and then on what type of hit. On this point, If anyone wants to have some theoretical discussions on how to go about this, please sitemail me.

 

What I don't like about going with just straight up historical field percentage is we know that errors are caused by either a player's glove or arm accuracy rating, while + or - are from range and arm strength. FP doesn't incorporate those two ratings into it because it's solely based on errors. Maybe PPI adjusted for pitching staff/park effects wouldn't be as bad as -16 runs, but in my mind he's definitely not -2 in a year where he's 7 points below rec arm strength and 9 points below rec accuracy.

Player A: Hardball Dynasty – Fantasy Baseball Sim Games - Player Profile: Chick Conroy

Player B: Hardball Dynasty – Fantasy Baseball Sim Games - Player Profile: Orber Pescado

Player C: Hardball Dynasty – Fantasy Baseball Sim Games - Player Profile: Ed Stockton - Yes, he is my player.
 

Interesting analysis. I definitely think the "grey area" thing is interesting, I'm just not sure how to actually justify it.

It's my understanding that range and arm strength create +/- in basically the same way that glove and arm accuracy create errors. Just looking at the sample text from a recent game was: "B. Randall hits a bloop single to RF." And on that play my RF was credited with a "-", and since he's essentially a C playing RF, I know that minus was almost certainly due to range and not arm. So basically, my assumption there is that a fielder with a higher range would most likely have made that out.

I don't really see how that's any different from a player making an error because he has a low glove?

I think it makes a lot more sense to build a modified fielding% that factors in how frequently chances are converted to outs. For example:

A player with 70 PO, 20 A, 8 E, 5 +, and 2 - would have a 0.950 mFP, because he made 95 outs on 100 chances (the + plays are considered "bonus" chances)

A player with 70 PO, 25 A, 5 E, and 10+ would have a 1.050 mFP, because he made 105 outs on 100 chances since his extra range created more chances than he would have had with average range.

Posted by jtrinsey on 3/21/2013 12:08:00 AM (view original):
Interesting analysis. I definitely think the "grey area" thing is interesting, I'm just not sure how to actually justify it.

It's my understanding that range and arm strength create +/- in basically the same way that glove and arm accuracy create errors. Just looking at the sample text from a recent game was: "B. Randall hits a bloop single to RF." And on that play my RF was credited with a "-", and since he's essentially a C playing RF, I know that minus was almost certainly due to range and not arm. So basically, my assumption there is that a fielder with a higher range would most likely have made that out.

I don't really see how that's any different from a player making an error because he has a low glove?

I think it makes a lot more sense to build a modified fielding% that factors in how frequently chances are converted to outs. For example:

A player with 70 PO, 20 A, 8 E, 5 +, and 2 - would have a 0.950 mFP, because he made 95 outs on 100 chances (the + plays are considered "bonus" chances)

A player with 70 PO, 25 A, 5 E, and 10+ would have a 1.050 mFP, because he made 105 outs on 100 chances since his extra range created more chances than he would have had with average range.

I like your thinking. Just created a couple quick formulas into the spreadsheet. This is what I've got

Stockton: 815 outs in 801 chances for mFP 1.017

Orber Pescado: 788 outs in 823 chances for mFP .957

Chick Conroy: 870 outs in 928 chances for mFP .938

League average mFP was .961 for the season. So taking your .3 value for each extra out, Stockton is +13.6, Pescado is -.86 and Conroy is -6.5

Where I think you ultimately need to go with this is that, when you add up all your individual players, their contributions should sum to what the total team contribution was.

This might not be the case for every league (although I suspect it is for the most part), but the league-wide fielding numbers have been remarkably consistent in No Quitters. Each of the past 5 years, the league-average fielding% has been .984, the league-average + plays (per team) has been between 55 and 61 (average of 57.6) and the league-average - plays (per team) has been between 32 and 36, with an average of 33.4.

So, I think it's reasonable to assume that an average fielding team will have a fielding% of .984, with a +/- ratio of about 24. The league-average number of PO + A + E is 6047, so it's more accurate to say that the average team produced 5950 outs on 6023 (6047-24) chances, for a mFP of 0.988.

For an individual player, I just look at: (PO + A)/(PO + A + E + minus plays - plus plays) ... that's with the assumption that minus plays are plays that "should" be made, thus are counted as chances, and plus plays are plays that "shouldn't" be made, and thus are subtracted from the chances and counted as bonus.

From there, it's not too hard to get data to get mFP for different positions to do this on an individual basis. That way, the sum of your individual pieces of defense coincide exactly with your team defense rating. This will not be the case if you use the PPI method and your pitching creates an above-average or below-average number of chances.

Stockton created 784 outs (272 PO + 512 A) in 770 chances (272 PO + 512 A + 17 A - 31 plus plays) for a mFP of 1.018.

Basically the same as what you had, but you don't want to add the plus plays to the outs created (they are already scored as a PO or A), you want to subtract them from the chances. Likewise minus plays shouldn't be subtracted from the outs you made, but instead just added to the chances (ie, treated like an error).

Let me apply this calculation to the entire sheet, and see what it spits out in terms of whole team numbers.

Instead of using .2 or .3, why don't we just use the "out value" embedded in the wOBA calculator on the base info sheet? That way it scales it to whatever an out was determined to be worth in the league that year? So for example, an out was determined to be worth -.281 runs in this season. 

That would certainly be the most accurate way to do it. Good idea.