Extrapolating advanced baseball data from Excel spreadsheet

[sborah99]

Hi all,

It's been a while since I've posted. But, with the whole pandemic thing going on, I've decided to take the time to learn more about using Excel to extract and extrapolate data. I'm a huge baseball fan and grew up playing the Strat-O-Matic board game. So, I decided to do a historical replay of MLB in the 80s using that. I have tons of spreadsheets full of game data for the St. Louis Cardinals that I kept by hand, starting in 1980 and ending in 1989. Now, I want to expand the knowledge database by extrapolating new data such as batting average with runners in scoring position, BA with 2 outs and two strikes, etc. I have attached one of the spreadsheets showing one batter's performance for 1989 on one sheet and the situational data I want to extrapolate on the second sheet. Is there a macro or program available that could help me do this?

Thanks in advance.

Stacy

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[AliGW]

Much better - thanks.

[MrShorty]

I am not aware of any built in or simple plug and play macros/programs that will do this kind of analysis of baseball statistics.

Is this an Excel specific question, or is this a broader statistics/prediction/extrapolation question? We are usually pretty good at Excel questions, but I don't know if any of us has the statistical expertise to recommend or develop a statistical algorithm from scratch for this. If you help us understand the algorithm you want to use, we should be able to help you program that algorithm into Excel.

I tried an internet search and found a couple of interesting hits (if this is more about statistics than Excel):

http://cs229.stanford.edu/proj2019au...w/26382632.pdf This looks like a student project?? for a class at Stanford where they tried multiple different methods to predict a player's OPS+ metric (whatever that is). The page summarizes their results, which appear to be marginal at best.

https://solomonkurz.netlify.app/post...an-do-for-you/ This is a fairly extensive discussion focused on predicting batting average at the end of the season based on early season data. Seems somewhat successful, but I'm not sure how one would adapt the algorithms presented for other metrics or for year over year metrics. It looks like code is provided, I think for R rather than Excel (not surprising since, according to my statistician colleague, serious statisticians tend to shy away from Excel).

It sounds like an intriguing project. I expect it will get fairly involved, as the statistics probably gets fairly deep for a project like this. We can probably be most helpful if you choose an algorithm that you want to use, program what you can into the spreadsheet, then pose specific questions about the parts of the algorithm that you have difficulty implementing on your own.

[sborah99]

I am not aware of any built in or simple plug and play macros/programs that will do this kind of analysis of baseball statistics.

Is this an Excel specific question, or is this a broader statistics/prediction/extrapolation question? We are usually pretty good at Excel questions, but I don't know if any of us has the statistical expertise to recommend or develop a statistical algorithm from scratch for this. If you help us understand the algorithm you want to use, we should be able to help you program that algorithm into Excel.

I tried an internet search and found a couple of interesting hits (if this is more about statistics than Excel):

http://cs229.stanford.edu/proj2019au...w/26382632.pdf This looks like a student project?? for a class at Stanford where they tried multiple different methods to predict a player's OPS+ metric (whatever that is). The page summarizes their results, which appear to be marginal at best.

https://solomonkurz.netlify.app/post...an-do-for-you/ This is a fairly extensive discussion focused on predicting batting average at the end of the season based on early season data. Seems somewhat successful, but I'm not sure how one would adapt the algorithms presented for other metrics or for year over year metrics. It looks like code is provided, I think for R rather than Excel (not surprising since, according to my statistician colleague, serious statisticians tend to shy away from Excel).

It sounds like an intriguing project. I expect it will get fairly involved, as the statistics probably gets fairly deep for a project like this. We can probably be most helpful if you choose an algorithm that you want to use, program what you can into the spreadsheet, then pose specific questions about the parts of the algorithm that you have difficulty implementing on your own.

Thanks for the reply. I've also seen the programs coded in R and I don't have the patience to learn a new language. I'm fairly familiar with Excel and was thinking of using VLookup for this project since all of the relevant is presented in rows. What do you think?

[MrShorty]

VLOOKUP() is a good lookup function for finding a specific entry in a table and extracting information related to that entry. If your analysis involves a lookup step, the VLOOKUP() function (or any of Excel's other lookup functions) may be useful for the lookup step. VLOOKUP(), however, has no additional abilities beyond finding and retrieving a datum from a table. It could be a part of the process, but I doubt it will be entirety of the algorithm (unless I am overthinking your problem, and all you need to do is extract data from the table).

[sborah99]

Basically, I want to pull the data from the simmed stats according to situations, hitting counts, runners on base, etc., and compile it in the correct columns in the situations sheet. I want to see how many hits each batter got in 0-2 counts with a runner on 2nd base, or full counts with bases loaded and two outs. That sort of thing.

[MrShorty]

That sounds more like something a filter would do well. Excel, being a spreadsheet and not a full featured database tool, has some filter/query capabilities, but not the best.

Excel's Autofilter tool works well for filtering a list in place: https://www.wikihow.com/Use-AutoFilter-in-MS-Excel You can then copy and paste values into another sheet, if desired.
Excel's Advanced Filter tool can filter a list into another location within the same sheet: https://www.excel-easy.com/examples/...ed-filter.html

Your profile says you are using 2013, so you should be able to download and use Power Pivot, Power Query, and/or Power BI, which provide more robust data analysis and shaping tools: https://support.office.com/en-us/art...5-89F6269CD605 If you find the built in Autofilter or Advanced Filter to be insufficient or tedious to use, you may consider these tools for your data extraction needs.

Since I don't do much database work, I don't use these tools very much and am not very familiar with them. Hopefully someone with more experience with these tools will join the discussion when you need help with specific uses of these tools.

[MrShorty]

Or maybe a pivot table sort of thing: https://www.excel-easy.com/data-****...ot-tables.html

[sborah99]

Thanks for those links. I will give them a shot. And I changed my profile to list Excel 365 since I am now using Office 365.

[JeteMc]

As you are talking MLB statistics I am guessing that you may be familiar with Sabermetrics.
A search for "Sabermetrics Excel" yields some 28,000 results, and might include some of the stats that you are seeking. Especially I would expect batting average with runners in scoring position to be in there somewhere as well as BA with two outs, although 2 outs and 2 strikes may be a bit of a reach.
As you are in Alabama and we are talking baseball, I will lament the necessary cancelation of the college baseball tournament in Hoover which is always scheduled for this week.
Best of luck and I hope that you have a blessed day.