My Ratings Calculator (with download)

[jimbo_1884]

This has been my first year playing MLB The Show and I have enjoyed it that much, I decided to have a bash at creating an Excel ratings calculator based on MLB regular season statistics.

I thought I would make it available for people to try and also because I am looking for any feedback or alternative formulas etc.

I am especially interested in any suggestions for formulas for Arm Strength and Accuracy, as I have been unable to think of any, based on the current set of stats that I use.

The calculator is worked by simply selecting the player from the drop down menu on the HOME tab.

If you want to see the formulas I have used, they are listed on the Calcs tab and down the right hand side, I have also listed alternative formulas that could be used instead.

As I am a total newbie to roster making, I can't promise it will be 100% accurate but it has been completed to the best of my abilities.

03/11/12 --- v3 fixes an error with the pitcher k/9 rating ---
05/11/12 --- changed drag bunt formula - doesn't affect rating, just inputs "-" when appropriate ---
17/11/12 --- v4 This fixes the 2012 Tampa Bay Rays pitcher stats with RISP omission ---
17/11/12 --- pitcher H/9 and HR/9 formula changes ---
23/11/12 --- v5 new fielding ability formula and changes to H/9 AND HR/9 ---
02/12/12 --- Have updated the file to include missing players and all players now have detailed personal information ---
21/02/13 --- New version of calculator that is compatible with older versions of Word and Open Office now available ---
03/03/13 --- Bahnzo's formulas added and a formula fix ---
06/03/13 --- v6 new formulas for Base Running Ability and Aggressiveness, Discipline, Bunting and modifications to Catcher Blocking and Reaction ---
17/03/13 --- "user input" tab added ---
06/08/13 --- New version of the calculator available for download but unfortunately, due to its size (14mb) I am unable to upload it.
30/08/13 --- Comapatabilty version for use with older versions of Excel and Open Office now available ---
03/09/13 --- create a year calculator available for download ---
08/10/13 --- Updated to include all the 2013 regular season stats, bios etc. ---


For those with Excel 2010 and later
======================

Calculator:
http://www.jamesagrant.webspace.virg...ator_2013.xlsx

Create a year calculator:
http://www.jamesagrant.webspace.virg...stom_year.xlsx


For those with OpenOffice or older versions of Excel etc
===================================

Calculator:
http://www.jamesagrant.webspace.virg...atability.xlsx

Create a year calculator:
http://www.jamesagrant.webspace.virg...atability.xlsx



The calculator is designed for use with Excel 2010 and later, so there may be some anomalies with the "compatability version" due to trying to work out averages for example, when there are errors etc.

[jimbo_1884]

Re: My Ratings Calculator (with download)

I have fixed an error in the calculator that meant some names (U-Z) were missing from the pitcher list in the drop down menu.

Also about to collate pitcher stats and try to incorporate basic pitcher ratings within the calculator.

So, if anybody has any formulas etc. they would like me to include for calculating the basic pitcher ratings, then let me know.

[doominic77]

Re: My Ratings Calculator (with download)

This calculator looks and works really nicely. Good job!

[Rwings832]

Re: My Ratings Calculator (with download)

Yes this looks really good, a good calculator if you want to kind of do an overall 2013 roster. Good Job with these.

[jimbo_1884]

Re: My Ratings Calculator (with download)

I have fixed an error in the calculator that meant some names (U-Z) were missing from the pitcher list in the drop down menu.

Also about to collate pitcher stats and try to incorporate basic pitcher ratings within the calculator.

So, if anybody has any formulas etc. they would like me to include for calculating the basic pitcher ratings, then let me know.

I've now added 3 years worth of pitching stats but unfortunately, I have ZERO ideas for formulas.... ok I have a couple but none I'm happy with!!

So, if anybody can help with formulas for:

Stamina
Pitching Clutch
H/9
HR/9
k/9
BB/9

I can/will upload an updated version of my calculator with pitcher ratings.

[seanjeezy]

Re: My Ratings Calculator (with download)

Stamina: ((IP/G) / 7.34) * 100 for starters, (((IP/G) / 7.34) * 100) *2 for relievers

Clutch: use the clutch ratings on fangraphs and this scale (also from fangraphs) -

99 = 2
88 = 1.5
77 = 1
66 = 0.5
55 = 0
44 = -0.5
33 = -1
22 = -1.5
11 = -2

Position players: ((((C / (G/162)) - C1) / 0.5) *11) + R1
Starters: ((((C / (G/33)) - C1) / 0.5) *11) + R1
Relievers: ((((C / (G/61)) - C1) / 0.5) *11) + R1

* C1 = closest clutch rating on the scale rounded down (so 1.48 would be 1)
* R1 = the rating that corresponds with C1 on the chart (so 1 would be 77)

I came up with these by myself, I think at the time, 7.34 IP was the highest average in the league, and the clutch rating is simply the difference quotient set on the 0 - 99 scale. The other stuff I just use Bahnzo's calculator for, the formulas might be floating around somewhere in the forum.

[Richie12]

I've now added 3 years worth of pitching stats but unfortunately, I have ZERO ideas for formulas.... ok I have a couple but none I'm happy with!!

So, if anybody can help with formulas for:

Stamina
Pitching Clutch
H/9
HR/9
k/9
BB/9

I can/will upload an updated version of my calculator with pitcher ratings.

I think you're putting in place right now foundations for something great for roster makers.

Just curious, what tool and what data source do you use for mass import data?

[jimbo_1884]

Re: My Ratings Calculator (with download)

Stamina: ((IP/G) / 7.34) * 100 for starters, (((IP/G) / 7.34) * 100) *2 for relievers

Clutch: use the clutch ratings on fangraphs and this scale (also from fangraphs) -

99 = 2
88 = 1.5
77 = 1
66 = 0.5
55 = 0
44 = -0.5
33 = -1
22 = -1.5
11 = -2

Position players: ((((C / (G/162)) - C1) / 0.5) *11) + R1
Starters: ((((C / (G/33)) - C1) / 0.5) *11) + R1
Relievers: ((((C / (G/61)) - C1) / 0.5) *11) + R1

* C1 = closest clutch rating on the scale rounded down (so 1.48 would be 1)
* R1 = the rating that corresponds with C1 on the chart (so 1 would be 77)

I came up with these by myself, I think at the time, 7.34 IP was the highest average in the league, and the clutch rating is simply the difference quotient set on the 0 - 99 scale. The other stuff I just use Bahnzo's calculator for, the formulas might be floating around somewhere in the forum.

Thanks for the formula suggestion.....

Initially I was going to go with 9 (total innings), then I changed it to 6 (mlb average) but they gave either too low or high ratings, so the idea of working from the highest average makes perfect sense!!

As for clutch, I was simply going to base my figure around the OBP with RISP.

[jimbo_1884]

Re: My Ratings Calculator (with download)

I think you're putting in place right now foundations for something great for roster makers.

Just curious, what tool and what data source do you use for mass import data?

The data is collected from the MLB website and the tool is me.... pun intended!!

[jimbo_1884]

Re: My Ratings Calculator (with download)

Have uploaded version 2 of the calculator that now contains pitcher ratings.

(available from link in first post)

Still a bit rough around the edges with regards to formulas and other things but it's a start!!

Oh and it's now over 6mb long.

[jimbo_1884]

Re: My Ratings Calculator (with download)

Have now added 2009 pitching stats to the calculator and modified the durability formula to take into account pitchers.

(available from the link in the opening post)

[StormsWarning]

Re: My Ratings Calculator (with download)

haven't had a chance to look at this, but the abbreviations in the game for the pitchers have always thrown me off, i know some, but not all of them, have you included the clutch stats etc for the pitcher edits, i hope so, because that will be huge not only for current rosters but classic rosters as well?

[hoser]

Re: My Ratings Calculator (with download)

Nice spreadsheet, very well done.

I do notice that the K/9 number seems pretty high for many, if not all, pitchers however. Jeff Francis for example is rated at 95 in the spreadsheet for K/9. He's not a strikeout pitcher at all so it doesn't seem right that he'd be rated at 95 in this category.

[jimbo_1884]

Re: My Ratings Calculator (with download)

Nice spreadsheet, very well done.

I do notice that the K/9 number seems pretty high for many, if not all, pitchers however. Jeff Francis for example is rated at 95 in the spreadsheet for K/9. He's not a strikeout pitcher at all so it doesn't seem right that he'd be rated at 95 in this category.

Thanks for the spot.... that's a mistake on my part due to a dodgy formula!!

I have the formula back to front, so at present it means someone with a lower K/9 figure will in fact have a higher K/9 rating in the game!!

Have changed the formula so that it now makes more sense:

Jeff Francis would now be rated 55 compared to Verlander's 90

[jimbo_1884]

Re: My Ratings Calculator (with download)

v3 of the calculator is available to download from the link in the opening post.

This fixes an error with the pitcher k/9 rating formula that was spotted by "hoser".