Introducing actionjackson's Simulation Settings widget/gadget/calculator... thingy

As anyone who is familiar with my posts here knows I have a thing for simulation settings and a general aversion to playing dynasties that feature 5.5 to 6 runs per team game (particularly in the dead-ball era), inflated league batting, on base and slugging percentages and the crappy ERAs that come with them. My first little attempt to make a dent in the "juiced" offensive numbers was a little over 9 months ago and only covered 8 seasons. Me and Excel didn't get along too well then, but since then I've learned how to bend it to do my bidding. That has enabled me to come up with this bad boy. 1740 lines of spreadsheety goodness.

Got a particularly favourite year that you want to historically simulate? Simply find that year in the spreadsheet, for which you will see the Real Life (RL) raw and rounded numbers. Run a default sim (all default sim settings) of that year in BBMogul 2k9, output it to the encyclopedia, output the league index and individual team indexes to Excel, do some cut and paste, get the required totals and plug them into the Simulation (SIM) section of your favourite year.

The second you plug in the raw data, the widget/gadget/calculator/slide rule thingy roars into action, calculating rounded numbers (to a full season's worth of games) for each category and divides the RL rounded numbers by the SIM rounded numbers in order to come up with "factors" (RL # / SIM # * 100), which you would then use for Sim Settings in categories like Ground/Fly/Lineouts (or in the widget IPO = In Play Outs), Singles, Doubles, Triples, Homeruns, BB, IBB, K, SB, CS, SH, HBP, and GDP (as well as FDP = Fielding Double Plays, which can't be manipulated in the sim settings but were used to fudge early season GDP #s). It will also calculate AVG, OBP, SLG, R/G and K/9 for your default sim. You will notice some zeroes (under 1B or singles and IPO) and lots of #DIV/0! characters, disregard them, but don't erase them. Those boxes contain the equations which will calculate the rounded numbers and "factors" for you as well as the singles and IPO from your sim. All you have to do is fill in the raw data for GP (use GS or Games Started from the team pitching stat indexes), IP, AB, R, H, 2B, 3B, HR, BB, IBB, K, SB, CS, SH, HBP, GDP, and FDP and voila simulation settings for you to use for that year.

Maybe you want to run a 4 or 5 year dynasty, do the same thing only for the 4 to 5 years. Maybe you want all of your seasons to output league-wide stats similar to your favourite year. Take the RL stats of your favourite year and paste them in every year or whatever years you wish to sim. Maybe you're not a historical player, but you're still finicky about realistic league-wide stats. Pick one of the 108 years (and counting) of RL data, or better yet go to the bottom of the spreadsheet, where you will find the coup de grace, the piece de resistance: all 108 years totalled (the original SUM equation took up six lines in the formula box before it was cleaned up) and rounded for schedules of 2240 (1901-1903), 2464 (1904-1960), 2772 (1961), 3240 (1962-1968), 3888 (1969-1976), 4212 (1977-1992), 4536 (1993-1997) and 4860 (1998-today) games in length. Courtesy these totals I learned that in the 108 years of MLB, the league has a .262/.330/.386 line with 4.41 R/G and 4.89 K/9. Pretty palatable for most Mogulers. I use my own formula for OBP ((H+BB+HBP)/(AB+BB+HBP)) due to the fact that there have been quite a few changes to its equation throughout history and due to the fact that Mogul doesn't seem to track Sac Flies (neither did MLB until 1954).

With this All-Time #s rounded tool metsguy234 could find out how utterly dominant Pedro Martinez was (before he became a Met unfortunately ;) ). Players in custom universes could play their leagues to output stats adjusted for all-time, so they can truly compare the greats from every era on a level, stable statistical playing field. Leagues could also use this feature or any of the other years to dampen down the offense in their league. The possibilities are endless.

There are some notes off to the right of the stats at the top of the spreadsheet to fill you in on why I used the formulas or fudge factors that I used. Check them out so you know where I'm coming from. I also included a setup for 2009 and 2010 a) so you can see it completely blank and maybe use it for trial and error and b) so you can plug in '09 and '10 numbers if you so choose. Remember if you fubar your original download, you can always come back and dowload it again and overwrite the old one.

This version of the widget...thingy only has the RL data. I will be updating it with SIM data after I run simulations of the seasons and have data for all 108, and I'll probably include examples of what the League Index page and what a Team Index page should look like in Excel when you export from the Encyclopedia. Basically you want to put the categories on the League Index page in the same columns as they're in on the widget spreadsheet. Leave a gap in column A and column G (Year and 1B) and run the same categories (GP (from GS), IP, AB, R, H, (blank), 2B, 3B, HR, BB, IBB, K, SB, CS, SH, HBP, GDP and FDP) from columns B to S. Then paste it in the "SIM" rows of the year you're working on. The widget does the rest. The reason 1B get left blank is that they are calculated based on H, 2B, 3B, and HR #s from the sim. IPO, AVG, OBP, SLG, R/G and K/9 will also be calculated once the appropriate columns are filled in. On the Team Index page all you want to do is remove any impediment to getting the totals for AB, R, H, (blank), 2B, 3B, HR, BB, IBB, K, SB, CS, SH, HBP, and GDP from the hitting totals and GS and IP from the pitching totals and paste them into the stats of the team in question on the League Index page.

You know what, I'm starting to get confused myself. :confused: I'll try to post examples later today of what the Encyclopedia League Index and Team Index pages should look like when they've been exported to Excel (or equivalent) and you've manipulated the categories so that everything lines up with the widget's categories, but basically if the category isn't something you need for the widget (like RBI) - move it out of the way and get what you need and get that clipboard out and start pasting.

So, there you have it. Sorry for the Tolstoyish post, but I hope you have fun with this thing. Just don't get lost in there. And it all comes for the incredibly low price of...(Sorry, ginsu knives are not available with this offer)...What is my price? Oh yeah, to paraphrase "Crash" Davis: Mogulers, here comes the Mother of all Sim Settings widget/gadget/calculator/slide rule thingies, and when you speak of me, speak well. :D

Wow. This looks like good stuff, AJ.

A preliminary "nice work".

actionjackson=god

Thank you. No, not god, mere mortal who has found his Baseball Mogul niche.

cant open, it says the description is too long.

Can you save it as a 1997-2003 Excel file and upload it?

Shortened description. Hopefully you can upload it now. Sorry about that.

This time four little spreadsheets in a zip file to give you a sense of what the league index and team index will look like (Original League and Team Index) when you export to Excel from the Encyclopedia and what it should look like (Altered League and Team Index) in order to plug into the widget smoothly. These numbers are the real life 2007 numbers and all of them added up right in the Altered League Index, except Sac Hits, which came up short by 8 (weird).

Sometimes the Encyclopedia maddeningly does not export every player (at least with the real life numbers - hopefully it's better with the sim numbers, but we'll see). Relievers appear to be the chief victims, but you never know. The missing players can easily be spotted by checking the last player amongst the starters, the bench players, or the pitchers as hitters or the two categories of pitcher. The missing players will be located below the last player of the section that is listed in Excel. I found one team that had about 150 IP by relievers listed in the encyclopedia below its last reliever listed in the spreadsheet. Like I said hopefully the sim numbers are done better. I'll let you know.

I apologize for bumping my own thread, but I need some info from those that have downloaded the widget so far. Is it coming up as a read-only file? If so, can you convert it to a read-write file? Otherwise it's going to be pretty pi$$ useless as a widget. I need to know so I can convert it to a shared file and re-issue it if necessary. The whole idea of the thing was to give y'all something you could use to get your own sim settings, but it kind of falls apart if you can't a) plug in data and b) get sim settings based on said data. I'm kind of a newb in this particular area of Excel, so any assistance from the spreadsheet gurus that I am sure inhabit this forum would be greatly appreciated. Thankyou, and we now return you to your regular programming already in progress...

I'm having a problem with finding the FDP value. I see there is a DP value in the leauge index, but it is way above that of the real life numbers. Is this normal? Am I looking in the right place?

Quote:

---

Originally Posted by gosensgo101

I'm having a problem with finding the FDP value. I see there is a DP value in the leauge index, but it is way above that of the real life numbers. Is this normal? Am I looking in the right place?

---

Yes, the DP from the league index is my FDP and yes it is a-way too high. I called it FDP because I couldn't figure out where Clay was getting his DP numbers and I wanted to distinguish it from GDP. The ratio between FDP (or league index DP if you prefer) is about 3.1ish to 1, which is far higher than the RL FDP/GDP ratio which is about 1.2 or 1.3 to 1. Because of this discrepancy I tend to completely ignore FDP (DP) except to check the ratio between it and GDP and if it falls somewhere in the general area of 3.1 to 1, I know the GDP is right. Your point does raise an interesting question though: just what the heck do the DP numbers in the league index represent? It is certainly one of the great mysteries of our time and one that I think will still stand long after all the other great mysteries are solved. ;)

The only reason I can see is that there are twice as many other double plays than there are GDP. (i.e. strike him out, throw him out/doubling guys off/throwing out a guy advancing) I agree it should not be that high.

Quote:

---

Originally Posted by gosensgo101

The only reason I can see is that there are twice as many other double plays than there are GDP. (i.e. strike him out, throw him out/doubling guys off/throwing out a guy advancing) I agree it should not be that high.

---

It isn't anywhere near that high. You can get RL FDP numbers at baseball-reference and RL GDP numbers at retrosheet all the way back to somewhere in the '50's and the ratio between the two is about 1.2 or 1.3 to 1, whereas with Clay's it's about 3.1 to 1. I have no idea where Clay gets his DP numbers from.

Quote:

---

Originally Posted by actionjackson

It isn't anywhere near that high. You can get RL FDP numbers at baseball-reference and RL GDP numbers at retrosheet all the way back to somewhere in the '50's and the ratio between the two is about 1.2 or 1.3 to 1, whereas with Clay's it's about 3.1 to 1. I have no idea where Clay gets his DP numbers from.

---

I'm saying that the ratio at which they happen may be flawed in the game.

Ok, how about saying that we're saying the same thing, just in a different way. ;) There's a possibility Clay's numbers come from giving credit to every single fielder that touches the ball with a DP and adding all those numbers together, so that an 8-6-2-5-1 DP gets recorded as 5 DP, although I'm not sure if I've ever seen one of those in Mogul or not. :D He has to be getting them from somewhere though and his number is pretty much always a little over 3 times the size of the number of GDP in any given simulated season.