There were some big waves in the nerdy baseball world (I’m a proud card-carrying member) last week, when Baseball-Reference.com released a redesigned website. While the improvements are very nice, especially on a mobile device, the unfortunately broke the link to the Projecting X 2.0 spreadsheet.
Unfortunately, the newly designed site doesn’t seem to allow for the web querying function that was used to extract several of the pieces of information necessary to project pitcher stats. Because of this, we’ll have to make a few small edits to your spreadsheet that will allow you to link directly to the player you are projecting so that you’re taken right to the table containing the desired information (if we can’t pull it into the file, we’ll create a link that takes you right to where the information is located).
What follows are instructions that will help create these helpful hyperlinks and add them to your spreadsheet.
| Step | Description |
|---|---|
| 1. | To start with, we’ll delete some information on the top of the “Pitcher Projections” tab. We’ll remove anything we no longer expect to pull in automatically. The ranges of cells to be deleted are:
|
2. | As you might have guessed, I’m a little uptight about the appearance of my spreadsheets.
I think it’s important to convey where you are expected to type or enter data into a spreadsheet. And since we can no longer pull in the information above automatically, if you want averages calculated for you, you’ll need to input those measures. Accordingly, we should shade the cells from above in the same yellow color our other input cells are shaded. To do this, first click on cell G33 (this should be the “IP S” cell for the first pitcher in the list). Then click the “Format Painter” button on the “Home” tab of the Excel ribbon. Afterwards, click and hold your mouse button in order to select cells H5 through H9. We’ll now format the QS area. Click to select cell N33 (this should be the “SV” cell for the first pitcher in the list). Then click the “Format Painter”. Then hold your mouse button down and click to select from cell M5 all the way to cell M10. Then, click once on cell AA33 (this should be the “STR%” cell for the first pitcher). Click the “Format Painter”. Then hold the mouse down and click to select from cell AA5 all the way down to AE9 (this should cover from the top STR% cell to the bottom I/STR cell). |
| 3. | Now we’ll add hyperlinks that will take us to the selected pitcher’s “Starting Pitching” data (for IP/GS and QS) and their “Pitch Summary” data (for the STR%, L/STR, S/STR, and F/STR.
Copy the following formula, select cell H4, delete the current contents, hit F2, then paste this formula: Copy the following formula, select cell M4, delete the current contents, hit F2, then paste this: Copy the following formula, select cell AA4, delete the current contents, hit F2, then paste this: Copy the following formula, select cell AB4, delete the current contents, hit F2, then paste this: Copy the following formula, select cell AC4, delete the current contents, hit F2, then paste this: Copy the following formula, select cell AD4, delete the current contents, hit F2, then paste this: Copy the following formula, select cell AE4, delete the current contents, hit F2, then paste this: |
| 4. | That completes the setup. When you go to project a pitcher, you can click on these newly created links to be taken directly to the appropriate spot on the player’s Baseball-Reference page.
It gets manual at this point, but you can type in the last three years or all five years of data, depending on the approach you’ve decided to use for making your projection (three-year vs. looking at all five years of data). |
Any Other Suggestions to Make the Process Faster?
I do have one more thing to consider. It’s unfortunate to have to suggest this, but instead of projecting each of the individual components of Mike Podhorzer’s xK%, you could just project K%. This could save you a significant amount of time over the course of projecting 100+ pitchers.
If you have any suggestions or would like to share tips on how you’ve worked around this new issue, please let us know in the comments below.
Thanks. Stay smart.
Have you tried this with Fangraphs’ website?
Hi Steve, are you asking if there is a way to generate similar links to specific locations on Fangraphs’ website? If you let me know what kind of link you’d want, I do think it’s possible.
In the construct of the Projecting X template, they’re not needed that much because the data comes into the Excel file, you don’t need to visit Fangraphs’ site to get the information.