How to Add Player Page Links to Your Draft Spreadsheet

A couple weeks back I wrote my first Excel-focused piece here at Rotographs. I was testing the water to see how it would go over and the results were pretty unanimous… More Excel please!

So I’m back today with a very practical tip to help you during the draft or while you’re conducting player research. In the instructions that follow I will show you how to add clickable player page hyperlinks to your draft spreadsheet.

Running out of time on the draft clock? Want to look up Dexter Fowler’s walk rate? Or Chris Davis‘ home run per fly ball rate for 2015? You’ll quickly be able to get to any player’s Fangraphs profile page after adding this to your spreadsheet.

ClickableLink

Getting Started

Before we get into the step-by-step instructions, I first need to lay some groundwork. Click this link to go to Mike Trout’s Fangraphs player page.

After the page loads, check out the URL (web address) at the top of the page:

TROUT_URL

Now click on this link: http://www.fangraphs.com/statss.aspx?playerid=10155

It still works! It’s the same address, just without the “&position=OF” piece at the end. You might even notice the “&position=OF” just shows up even though it wasn’t in the link.

That’s important news. Each player at Fangraphs has a unique player ID and we can use these IDs to create links directly to each player’s page. How can we get a list of these IDs? Perhaps the easiest way is to download the Steamer player projections (or any player stat download on Fangraphs) using the “Export Data” link on that page.

EXPORT_DATA_STEAMER

The projection reports and stat leaderboards you can download from Fangraphs list the Player ID in the rightmost column (NOTE: the IDs don’t show on the web page but are present in the download file).

PLAYERID_FANGRAPHS

Now that we’re armed with that information… Let’s add this to our draft spreadsheet.

Prerequisites

I am assuming you already have some kind of draft preparation spreadsheet started. You can attempt the instructions that follow in your own spreadsheets, but for the sake of consistency, I’ll be starting from where we left off in the “How to Create a Position Cheat Sheet in Excel” post from a couple weeks back. If you don’t have anything started yet, that’s a great starting point. Do that first, then complete the instructions that follow.

I’ll specifically be using Excel 2013, but I’m fairly confident (famous last words) the process and formulas below will work in just about any recent version of the program and should also work in your main Excel alternatives (Google Sheets, etc.). I’ll also only be showing this process for hitters, but the same rules should apply for pitchers.

 Step-by-Step Instructions
1. Visit the projections page and download your projection system of choice (Zips, Fans, Steamer, Depth Charts). Do this by clicking the “Export Data” link toward the top right of the projection information.

EXPORT_DATA_STEAMER
2. Locate the downloaded report on your computer and open the “Fangraphs Leaderboard.csv” file in Excel.

If double-clicking on the CSV file doesn’t cause it to open in Excel, you may need to right-click on the file and choose to “Open with>Excel”.

OPEN_WITH
3. Open the spreadsheet you’ve previously started (again, if you have nothing started, try this first). At this point you should have both the projection download and the cheat sheet file open.

In the projections file, right-click on the “Fangraphs Leaderboard” tab and choose the “Move or Copy…” menu item.

MOVE_OR_COPY

Then choose your previous (cheat sheet) spreadsheet from the drop down menu, choose to move the projections to the end of that workbook, and click “OK”.

COPY_SHEET
4. Assuming you are using the same sheet we created a couple weeks back, click the “Rankings” tab.

In the first cell of the first empty column (in my example image below this is cell H1), enter the title “PLAYERID”.

PLAYERID2

Then in cell H2 (adjust accordingly for your sheet), enter the following formula:

=MATCH(A2,'FanGraphs Leaderboard'!A:A,0)

Recall from that earlier cheat sheet piece that the Match function is typically used to search in a column to find a match. In my example image above, this formula would be looking for Mike Trout’s name within the projection data. The formula will return the row it finds Trout’s name in.

TROUT_ID_MATCH2

NOTE: This assumes your projection download was named “FanGraphs Leaderboard” and not “Fangraphs Leaderboard (12)”, like mine sometimes are.

5. Our goal in this PLAYERID column is to find Trout’s Fangraphs ID. This is in column AD of my FanGraphs Leaderboard sheet, which translates into the 30th column.

Combining that knowledge with the results of the Match function (2, for Trout), I now know exactly where Trout’s Player ID is (the second row, 30th column).

Knowing that, we can use the INDEX function to get exactly to that location. Edit your existing formula to be (brown font is new formula pieces, gray is already created from previous steps):

=INDEX('FanGraphs Leaderboard'!A:AD,MATCH(A2,'FanGraphs Leaderboard'!A:A,0),30)

NOTE: You may have a different number of columns, depending on the projection download you used. My “FanGraphs Leaderboard” goes from column A to column AD. Yours may encompass fewer or more columns.

You’ll need to adjust the “A:AD” and 30 arguments in your formula for the specific projection download you used.

6. After completing that formula, you should see Trout’s ID of “10155”. Copy this formula to the remaining cells in the column.

A very quick way to do this is to click the small square in the lower right-hand corner of the cell containing Trout’s ID.

BAD_BOY

At this point you should have a player ID listed for all players on the “Rankings” sheet.

7. Next, right-click on the first column on this ‘Rankings” sheet and choose the option to “Insert…”. This should place a blank column to the left of the “PlayerName” column.

INSERT
8. Type “PlayerName” in cell A1 (what you label this is not important).

Then enter the following formula in cell A2:

=HYPERLINK("http://www.fangraphs.com/statss.aspx?playerid="&I2,B2)

After completing the formula, the player’s name should turn into a clickable link.

TROUT_BLUE_LINK

NOTE: You may need to adjust the “I2” and “B2” cell references for your own spreadsheet.

The “I2” piece should be the cell number containing the “PLAYERID” we just added for the first player.

The “B2” piece should be the player’s name.

Excel’s HYPERLINK function is looking for two inputs.

The first is the web address you want the link to go to. In the formula above we’re using that root Fangraphs player page web address and appending (or concatenating) the individual player ID for that player. If you hover over the link, you can see the player ID has attached to the Fangraphs address.

HYPERLINK_TROUT

The second input is the text to appear in the link. In this case we just want the player’s name to appear as the link.

9. Once again, double-click the square in the lower right hand corner of cell A2 to copy that formula down to all remaining players.

Then right-click on column B to hide it.

HIDE_COLUMN

Wrap Up

What do you think? Still helpful? What neat feature have you added to your draft preparation spreadsheet? Or what do you wish for? No promises, but I may be able to pluck some ideas from your comments and show them in future pieces.

If we don’t talk again before your draft… Good luck!





Tanner writes for Fangraphs as well as his own site, Smart Fantasy Baseball . He's the co-auther of The Process with Jeff Zimmerman, and has written two e-books, Using SGP to Rank and Value Fantasy Baseball Players and How to Rank and Value Players for Points Leagues, and worked with Mike Podhorzer developing a spreadsheet to accompany Projecting X 2.0. Much of his writings focus on instructional "how to" topics, Excel, and strategy. Follow him on Twitter @smartfantasybb.

14 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
HappyFunBallmember
8 years ago

Good stuff!

I have a fairly specific request, if you think that you’re able to help?

In my spreadsheet I’ve got a tab into which I import playing time projections. For now it’s the FG depth charts download, but as the season progresses it’s going to be the depth charts (ROS) download. This tab will update every time I download new source data.

Then I’ve got a second tab that builds my rankings. Essentially it’s just a listing of all the playerIds in column A, then I use INDEX(MATCH()) to read data from my first, source data, tab and compute zScores for my league categories.

What I’d like to do, is have my rankings tab be able to intelligently read the source data for the playerIds that are actually in it. As new players are added or injured/benched dropped from the depth charts data, I would like that playerId list in my rankings page to automatically change.

Is there a way to do this that isn’t (a) setting column A cells to “=DepthCharts!A#” for 10,000 rows and handling the “#N/A” errors, or (b) wading into the wonderful world of macros and VB programming?

For a single tab just doing a copy&paste is simple enough. But I’ve got separate breakdowns by position, etc … across many tabs and what I’m looking for is a programmatic solution that won’t force me to rebuild ALL of those lists every time I update the source data.

HappyFunBallmember
8 years ago
Reply to  Tanner Bell

BTW, I’m having a nerdgasm over the PlayerIDMap on your site. Been looking for a solid way to reconcile FG player IDs with the stuff put out by the CBS leagues I play in, and this should do it. Many thanks! Does that update automatically or do you have to manually change names and teams when players move around?

mluceymember
8 years ago
Reply to  Tanner Bell

Incorporating macro’s into your spreadsheets is like learning vlookup for the first time.

Alex Chamberlainmember
8 years ago
Reply to  HappyFunBall

If I’m understanding this correctly, I think the easiest solution that avoids macros is to simply insert ranks. Hard-core ordinal ranks into your “rankings” page (the good ol’ 1, 2, 3, 4, etc. in column A). After your second tab calculates the Z-scores, use =rank() to order your players. Then fill our rankings page with index/match functions to populate it, irrespective of playerIDs.

Alex Chamberlainmember
8 years ago

your* rankings page

Alex Chamberlainmember
8 years ago

Hard-code* ugh, don’t hard-core it

vennardk
8 years ago

Not sure if this will help you but it was huge for me.

=(SUMPRODUCT(($D$2:$D$441=D2)*(E2>=$E$2:$E$441)))/COUNTIF($D$2:$D$441,D2)

Colume D = player position
Column E = Dollar value (based on sum of standard deviations times a dollar amount)

Result is percentile for the player. i.e. Harper is 100% and Jayson Werth is 57th percentile.

billcmember
8 years ago
Reply to  HappyFunBall

@HappyFunBall

I can help you if you send me your sheet. Find me on twitter @bill_c_