Tag: Excel

Adding Excel’s “Power Query” to Your Arsenal

About a week ago I got an e-mail from a reader of the site asking me for help using “Power Query” to pull some Fangraphs data into Excel. Power Query is an add-in for Microsoft Excel that offers more advanced data importing options and ability to combine data from different resources.

Power Query is an add-in that will appear on the Excel ribbon.
Power Query is an add-in that will appear on the Excel ribbon.

I knew Power Query existed. But as I was reading the e-mail, my palms began to sweat and an overwhelming sense of guilt washed over me.

“I don’t know anything about Power Query!!!”

Coincidentally, my wife was out of town for the weekend and with the girls in bed early, I had a handful of hours on Saturday night to give myself a crash course in how to use Power Query (ah, the exciting and glamorous nightlife of a baseball nerd!).

And now I might be hooked.

Tables, Tables, Tables!!!

I recently wrote a post (about importing a CSV file into Excel) that included a list of benefit to using Excel tables.

But I missed a really important one… If you import your data into Excel as a table, you create a connection to the data that is linked and can be updated automatically.

Let that sink in for a minute.

I’ve shown you how to make a lot of the Excel file’s that are isolated, dead, and not directly linked to any outside information.

I might have you download some data. Then copy and paste it into Excel. And then convert it into a table. But this is not ideal. The only way to update that information is to manually download it, open the file you downloaded, copy the data, paste it into your file, and cross your fingers that none of your formulas break when you paste over the top of everything.

If we can start importing data directly into Excel as tables, rather than copying and pasting data manually, we can maintain the link to the original data and then very easily update it in the future. And Power Query is capable of helping us do that. It gives more options to create live links to data sources and better options to manage those connections.

Power Query gives us more options to link to live data sources and better options to manage those connections.
Click the link to see a larger version of this image.

Imagine not having to rebuild a new rankings and dollar value file from scratch EVERY season. If you set the file up intelligently, you can use the same file to quickly get in-season values or to update the file for the next season in only a couple of minutes.

Power Query and the Power of Tables

When using Excel's standard web query to import data, you don't have the option to import the information as a table.
When using Excel’s standard web query to import data, you don’t have the option to import the information as a table.

In my limited use of Power Query so far, the thing that has me most excited is that it gives you the ability to import more data sources as tables. We have previously looked at how to use web queries to get information into Excel, but if you use a basic web query, the information does not come in as a table.

Granted, a web query does still leave a live link to the original data. But I want the best of both worlds. I want a live link to the original data AND to import it as a table!

There is a Catch

When using a standard web query (outside of Power Query), you do have the option to import the entire web page. This is messy and loaded with complications, but it’s helpful to have the option.

To view the actual HTML code of a site, right click on a web page and choose the option to "Inspect Element". If you don't see < TABLE > references, you may not be able to import the page with Power Query.
To view the actual HTML code of a site, right click on a web page and choose the option to “Inspect Element”. If you don’t see “<TABLE>” references, you may not be able to import the page with Power Query.

There is no such option in Power Query. You can only use Power Query to web query actual HTML tables from a web site. My educated guess is that to set something up as a table in Excel requires a neat and structured block of data, which querying an entire web page is not.

This is unfortunate, because some really great sites like Baseball Press don’t use tables to present their data. Instead, they use the division (< DIV >) HTML tag.

Downloading Power Query

Despite Power Query not being the silver bullet we need to resolve all our data needs, it’s definitely a tool worth having in the arsenal. And it’s free!

You can download the add in from this page. There are some restrictions you should know about. The program requires at least Windows 7 (sorry again Mac users… you should really look into partitioning your Mac to run Windows).

You also need to be running Excel 2013 (any version) or Excel 2010 “Professional Plus”. After you’ve downloaded the installation, close out of Excel and proceed through the installation. The new toolbar on the ribbon should appear the next time you open Excel. If you’re not seeing a “Power Query” tab, you may need to activate the add-in. Check out the instructions here on how to turn on the add-in (look for the section labelled “My Power Query Tab Disappeared”). POWER_QUERY_ADD_IN

Making Your First Web Query With Power Query

If you’ve read any of my previous pieces on web queries, this really isn’t that much different. The improvement is with the data being in a table and some additional capabilities to fine tune the data that is imported. But let’s take a closer look at how the basic functionality changes.

Step Description
1. The first task is to identify a web page you want to query AND to determine that it does contain HTML tables. My excitement over Power Query is tempered some by that fact that it is difficult to locate useful resources that put their data into tables. Many valuable sites and specific pages don’t!

Remember, to determine if data is in a table, right click somewhere on the web data you would like to capture and choose the menu option to “Inspect Element”.Inspect element

This will load the HTML “code” used to create the web page. If you see references to table, tr (means table row), or td (means table cell), this is a table and a web query should be successful.TABLE_ELEMENTS

A few examples of potentially helpful tables that I’ve found:

  1. FantasyPros.com ROS Projections
  2. Individual Fangraphs Player Pages
  3. Razzball.com ROS Projections
  4. Fangraphs Probable Pitchers List

After you have located a table to import, copy the web page address. For my example, I’ll use the Fantasy Pros rest of season projections (link is http://www.fantasypros.com/mlb/projections/ros-hitters.php). I realize this is not useful for DFS, but I just want to demonstrate the basics of Power Query now.Copy web page URL
2. Open a blank Excel file. Click the newly added “Power Query” tab. Then click the “From Web” icon on the left of the ribbon.Import from Web

Then paste the copied URL into the dialog box and click “OK”.Paste URL
3. The “Navigator” dialog will appear. It may take a minute or two to load as Excel processes each of the tables on the page.

Once the loading process completes, you will see a list of all the tables available for import. Click your mouse to locate the data you want. If you wish to import more than one table, check the “Select multiple items” box.

As you click on the various tables, watch the preview pane on the right in order to locate the exact table you want.Click tables and preview
4. Stop! This step is informational only. Don’t do anything!

At this point, you could click the “Load” or “Load To…” button. LOAD_TO

The “Load” button will import the data exactly as you see it into a newly created worksheet tab.

The “Load To…” option gives you a few more control over how the data loads. In the ensuing menu you have the option to import the table (recommended) or only add the connection to your file (not sure why you would want to do this unless you were unsure of where to place the table now). You can also choose to create a new worksheet or to place the table in an existing spot. Working with data models is something I may explore in the future. If you want to look ahead, you can start hereLOAD_TO_SETTINGS.

Loading from here bypasses some of the real value that Power Query offers. These features are available when you click the “Edit” button.Edit query settings

5. Start! You can start following along again.

Click the “Edit” button and the “Query Editor” will load. In this screen we not only see the preview of the data that will be imported, we can also clean things up.

For example, the first column is labelled “VBR”. This looks like some kind of a ranking, but I don’t want to import this. Additionally, the second column has a lot of information in it. Instead of seeing “Mike Trout(LAA – CF)” all in one column, I want to try breaking that into separate columns.QUERY_EDITOR_SCREEN

Rather than bring it in and have it clog up my screen, we can tell Power Query not to import this column. To do this, click on the “VBR” column to select it. Then click the “Remove Columns” button.CLICK_TO_REMOVE_COLUMN
6. Now let’s move on to splitting apart the player name, team, and position.

Click once on the “Player” column. Then click the “Split Columns” button.SPLIT_COLUMNS

And then click the “By Delimiter” option.BY_DELIMITER

A delimiter is a unique character that represents a change in the field or information. Looking at the data we have, the opening parenthesis is a delimiter between player name and team. There is no option to choose that from the drop down menu, so instead select the “–Custom–” option.CUSTOM_DELIMITER

Then type in the open parenthesis, “(“, and select the option for “At the left-most delimiter”.PARENTHESIS_LEFT_MOST

You should now see that the columns automatically get split!SPLIT_PLAYER
7. Let’s keep going and try to separate out each player’s position. Click the new “Player.2” column and then click the “Split Columns>By Delimiter” menu button again. This time use the settings in the image below to split the column at the hyphen.HYPHEN_DELIMITER

Power Query is really looking useful.PLAYER_TEAM_POS
8. The last thing bothering me is the closing parenthesis after each player’s position. To get rid of this, click to select the “Player.2.2” column and then click the “Replace Values” icon.REPLACE_VALUES

Once the “Replace Values” dialog loads, enter the closing parenthesis in the “Value To Find” field AND LEAVE THE “REPLACE WITH” FIELD BLANK! Then click “OK”.REPLACE_SETTINGS

Check this out…CLEANED_DATA
10. Now that the data is cleaned up, click the “Close & Load To…” button on the “Home” tab of the ribbon.CLOSE_AND_LOAD_TO

This will load the same “Load To” box discussed earlier. Adjust the settings as you see fit and click the “Load” button when you’re done.LOAD_TO_SETTINGS

The data loads exactly as you cleaned it up!CLEANED_TABLE

This Is Not the Best Example

Because I’m in the middle of a series of DFS-related blog posts, I wish I had a more concrete example that specifically tied in DFS information. But I did want to demonstrate the power of cleaning and tweaking data with Power Query. Hopefully you can recognize there is a great deal of value in knowing these tools exist so you can use them to solve issues as you build your own DFS spreadsheet with the information you like using.

I’ve included a couple more links below that may help you down the Power Query path.

As always… stay smart.

Are You Using Power Query? Other Add-Ins?

Is anyone using Power Query already? What kinds of things are you using it for? What sites are you loading the data from?

I’d love to hear it if you are. Please e-mail me or leave a comment below.

Other Resources

I’ve only given a brief overview of the full capabilities. If you’re intrigued and looking for more examples, check out these additional resources below.

Name Description
Download Page Free download page. Power Query is free, but it does require you to have Excel 2013 or Excel 2010 “Professional Plus” (I don’t know exactly what that means). It also requires you be using at least Windows 7.

If you’ve been on the fence about upgrading to the newest version of Excel, I list out a few of the purchasing options here.

Interactive Online TutorialIf you choose only one of these items to click on. Choose this one. The demo is only a few minutes long, but it does a great job of demonstrating how you can really fine tune and clean up the data you import through Power Query.
Interesting Examples that Might Apply to Baseball DataThis is a fairly lengthy post, but look specifically for the sections labelled “Append (Combine) Tables with Power Query”, (I could see this being a way to import a player’s last three seasons of data, or to import multiple projection systems) and “Merge Tables – A VLOOKUP Alternative” (a way to combine DFS salaries with info from other sites).
Introduction to Power QueryThis is simply a written explanation of Power Query and its features and benefits. More detailed than I’ve explained above.
Advanced Example, Dynamic and Multi-Layered QueriesI started this post off by referring to a question I received from a reader of the site. He wanted to provide Excel with a list of player IDs and then have it systematically go out to the player pages for each of those IDs and pull back data.

I wasn’t sure this was possible in Excel, but turns out that it is! This resource demonstrates how to make advanced edits to your query to make it dynamic (to ask for a player ID) and to make it multi-layered. Said another way, you can have one query go and fetch a list of player IDs and you can have a subsequent query run off each of those IDs. “Hey Excel, go get this list of players. Then go through each player on that list and go get me the standard data from their Fangraphs page.”

It’s slow… But it works.

How to Add Batter and Pitcher Handedness to your Daily Fantasy Baseball Spreadsheets

In this post I’ll show you how to add batter and pitcher handedness to your spreadsheets. To do this, we’ll have to learn two new Excel formulas we have not tackled yet.

I’ve avoided doing this for a long time… But there’s just no way around it now. It’s time to say goodbye to relying exclusively on VLOOKUP. Let’s put on our big boy pants and tackle VLOOKUP’s more flexible and powerful counterpart… INDEX and MATCH.

Sometimes VLOOKUP Can’t Get the Job Done

Take this scenario. You’ve started to build a DFS spreadsheet and you’ve imported FanDuel player salaries from a CSV file. Now you want to add player handedness (Lefty/Righty) as a column to your spreadsheet.

FanDuel Player Salaries Excel CSV
FanDuel Salary Information

You are also aware of the Player ID Map and know that it’s an easy way to get handedness information on players.

Batter_Pitcher_Handedness-e1438976169811
The Player ID Map contains information for batter and pitcher handedness, date of birth, team, position, and many player ID and naming systems.

You look at this data above and you think, “No problem!”. FanDuel ID is in both sets of data. How hard could this be? A simple VLOOKUP and we’re done.

But you quickly realize things are not that easy. You see, the VLOOKUP has a very restrictive assumption. If you are doing a VLOOKUP from the salary information into the Player ID Map, the Excel function assumes that “FanDuelID” will be the FIRST column in the Player ID Map.

And that’s NOT the case.

Let’s look at an example VLOOKUP formula:

=VLOOKUP([@FanDuelID],PLAYERIDMAP,10,FALSE)

In this formula we’ve told Excel to go look for the “FanDuelID” column in the “PLAYERIDMAP” table and give us back the value in the 10th column.

But “FanDuelID” is not the first column of the PLAYERIDMAP. It’s the 33rd (wow… the Player ID Map is getting to be quite large). So VLOOKUP will not work.

Other Weaknesses of VLOOKUP

Not only is assuming the data you want to match is in the first column awfully restrictive, if you think about it, VLOOKUP also ties you to a left-to-right lookup. For example, if you’re trying to use Excel to VLOOKUP which team Michael Brantley plays for, his player ID must be in the first column of your data set and you are then forced in to looking only to the right.VLOOKUP_LEFT_TO_RIGHT

We want a formula that will allow our lookup to be in any column and then to look to the left! For example, go find Nelson Cruz’s FanDuelID and then look to the left a few columns and give me the side of the plate he bats from.INDEX_MATCH_LOOK_LEFT

=VLOOKUP([@FanDuelID],PLAYERIDMAP,10,FALSE)

Going back to this example formula right above, the hard coding of a “10” in the formula to return the information in the 10th column is a flimsy approach, but that’s how many people are taught to write VLOOKUP formulas.

The flimsiness comes in if you decide to insert a column somewhere in the PLAYERIDMAP. If column 10 becomes column 11, Excel will not adjust its formula accordingly. Because you are likely building a spreadsheet that you’ll use all throughout the season, it seems highly likely you’ll want to add a new piece of information to your analysis. That inevitably means adding columns to bring that new information in. You don’t want to have to hunt through your formulas to figure out what the new column number in your VLOOKUP needs to be.

Enter “INDEX” and “MATCH”

That’s right. To defeat the almighty VLOOKUP formula, we need to combine the powers of two functions (that last sentence just made me think of this). Let’s take a look.

INDEX

When I’m using a function I’m unfamiliar with, I will add it through the “Insert Function” button. I like doing this because Excel will then give you a search menu to find a formula. And after locating your function you’ll get a helpful wizard that breaks down all the inputs it needs. INSERT_FUNCTION

If you follow that approach to add the INDEX function, you’ll soon realize there are two versions of it.

Excel Index formula has two versions.

I always use the first version, which allows us to locate a cell anywhere within a block of data and return the value from that cell. This function uses the following inputs:

INDEX(Array, Row_num, Column_num)
  1. Array – The range of cells you are searching for a value in. This could be a table or a block of cells.
  2. Row_num – The row within that array that the value is in. This should be a number representing the row.
  3. Column_num – The column within the array that the value is in. Again, this should be a number representing the column (not the letter representation of the column).

It may help to see a visual representation of the function. Assume we’re trying to find Nelson Cruz’s batting handedness. If we tell the index function to look in the PLAYERIDMAP (the “array”), in row 309 (“row_num”), and column 26 (column_num), it would return “R”.

Index Excel formula image example.

We know the array to look in. And we can easily determine the column we want to look in. The challenge we now face is how to determine the row to look in… How do we easily determine that Nelson Cruz is listed on row 309. That’s where the “MATCH” function can help us.

MATCH

The MATCH function will look for a specific value in a range of cells. The function will return a number representing where the matched item falls in the list.

Translating that into English, a realistic use for the function is to look in an entire column for a match. The function will start at the top of the column and proceed down until it locates the desired value. The function then returns where the item falls in the list, which happens to be the row the item is in.

The function uses these inputs:

MATCH(lookup_value, lookup_array, [match_type])
  1. Lookup_value – This is the value you are hoping to match in the array (or column). For us, this will usually be a Player ID of some sort.
  2. Lookup_array – This is the area you are searching for the match within. When using the MATCH function with the INDEX function, this will usually be a column of data.
  3. Match_type – This is an optional input telling Excel some more details about the kind of match you are looking for. You can enter a 1, 0, or -1. Entering a 1 or -1 are forms of approximate matches and are useful if you are looking up numeric values. But we are typically looking to match strings (I consider a Player ID made up of all numbers to still be a string) of text.

    This means we want exact matches only. Accordingly, I always use a 0 for this argument (even though it’s optional, leaving it blank tells Excel an approximate match is acceptable).

MATCH_FUNCTION

Combining INDEX and MATCH

As I alluded to before, the power of these two formulas comes when you combine (or nest) them together. Recall that the INDEX function looks like this:

INDEX(Array, Row_num, Column_num)

If we drop the MATCH function in place of the “Row_num” argument:

INDEX(Array, MATCH(lookup_value, lookup_array, [match_type]), Column_num)

We now have a formula that is more flexible and powerful than a VLOOKUP! The combination of INDEX and MATCH can look for a value anywhere in a table of data and we are no longer tied to the first column and a right-only lookup.

Step-by-Step Instructions

Continue reading “How to Add Batter and Pitcher Handedness to your Daily Fantasy Baseball Spreadsheets”

Importing a CSV File as an Excel Table

In this post I’m going to put a slight twist on the Draft Kings CSV salary file import we discussed recently and show you how to import the data into an “Excel table” (or sometimes referred to as an Excel structured reference).

If you’ve followed any of my previous “how to” sets, you know I’m a big proponent of using these Excel tables. They give a number of benefits:


  1. Efficiency – When you add a formula to a cell, Excel automatically copies that same formula to all other rows in the table. No more copying and pasting and scrolling around to copy your formulas.

  2. Consistency – Reduces the likelihood of an error in your spreadsheet by making sure formulas in a column are identical.

  3. Easier to Build Formulas – Your table becomes part of Excel’s reference system giving you useful type ahead featuresTable_Type_Ahead

  4. More Reliable Formulas – Because of the naming and reference system, formulas can better adjust when rows/columns are added or deleted. Ever had a VLOOKUP formula fall apart after you added a column to your spreadsheet? Or how do you think your spreadsheet will respond when you import a large list of players for a big slate of games and the next day you play a small slate of afternoon games? Setting the range of salaries as an Excel table allow formulas to adjust automatically.

  5. Meaningful Formulas– Structured reference formulas inherently have more meaning to them than standard formulas not using structured references. Look at these two examples…

    Traditional Excel Formula

    =VLOOKUP(A3,PLAYERIDMAP!$A$1:$AH$1502,4,FALSE)

    Structured Reference Excel Formula

    =VLOOKUP([@PLAYERID],PLAYERIDMAP,COLUMN(PLAYERIDMAP[FIRSTNAME]),FALSE)

    While the first formula is shorter in length, it’s also shorter on meaning. You can’t look at the formula and easily determine its purpose. You can easily look at the second formula to see it’s trying to locate the PLAYERID in the PLAYERIDMAP table and return the FIRSTNAME of the player.

    I think this is a HUGE benefit…

Is This Really Necessary?

Necessary? Probably not. An improvement? I think so. Since discovering Excel’s structured reference system, I’ve been using it on all my spreadsheets. I was excited to find a way to do this while importing data from an outside source. I didn’t know it was possible… I just wish we could get it to work with a web query too!

Step-By-Step Instructions

Here are instructions to bring in a Draft Kings or FanDuel CSV salary listing into an Excel file as a table. I use FanDuel in the example, but this can just as easily be performed with a Draft Kings CSV export.

Step Description
1. Log into your FanDuel account and download the CSV relating to the contest you wish to enter.

To do this, choose the contest you wish to enter. After identifying the contest, click the “Enter” button.FanDuel_Enter_Button

Once the contest loads, scroll to the bottom of the player salary list and click the link to “Download player list”.FanDuel_Download_Players_List

The file you download will have a unique name. Recall from our previous post on using CSV files that we will be better off to change this to a more generic name, like “FanDuel.csv”. We can then use that generic name going forward and instruct Excel to import “FanDuel.csv” each time it opens.

FanDuel CSV File Name
Instead of using the unique name provided by FanDuel, rename the file to a more generic name like “FanDuel.csv”.

By saving the file in the same spot and using the same name each time, Excel can seamlessly open and update the salary information with us not having to perform the CSV import each time.

So, after you have renamed it, save your “FanDuel.csv” file somewhere you don’t mind it residing in the future.

2. Start a new Excel file or open the file you wish to add the FanDuel salary list to.

On Excel’s Data tab, click the “Get External Data From Text” button.Import CSV file into Excel

Then browse to and select your “FanDuel.csv” file. Then hit “Import”.FanDuel CSV File
3. At this point, Excel’s “Text Import Wizard” will open, asking you which type of file you’re importing. Choose the “Delimited” option and be sure to check the “My data has headers” box.IMPORT DELIMITED

At step 2 of the import wizard, check the “Comma” delimiter box and uncheck any others. Click “Next”.Text Import Wizard Comma Delimiter

No changes are necessary at step 3. Just click “Finish”.TEXT IMPORT WIZARD STEP 3
4. This is where we diverge from the previous instructions. To format the imported data as an Excel table, check the “Add this data to the Data Model” box (other options on this screen will be grayed out until you check that option). Then ensure the “Table” radio button is selected under the “Select how you want to view this data in your workbook” area.

Click the “Properties…” button. Add_This_Data_to_the_Data_Model

5. In the past, I’ve suggested you check the “Refresh data when opening the file box” at this point. But don’t do that yet!

When I check that box at this point, my Excel file gets an error each time I open it. I get the sense the connection is still working, but the error bothers me. In researching the error, it seems others have experienced the same thing. The simple workaround to the error is to not check that box now, and just come back and check it later in the process.

I would recommend unchecking the “Prompt for file name on refresh box”.
Refresh_Data_When_Opening_the_File

Click “OK” to close the “Connection Properties” settings. Then click “OK” to close the “Import Data” settings.

6. Your import should be complete! You’ll know that Excel formatted your data as a table if it’s somehow shaded with alternating colors.

FanDuel_CSV_Table

You can change the colors under the “Table Tools Design” tab that should appear on the Excel ribbon when you click within the table.Change Excel table color.
7. After the import is done and you’re satisfied with the color, go to Excel’s “Data” tab and click on the “Connections” button.Data_Connections

In the list of “Workbook Connections” that loads, choose your FanDuel connection and hit the “Properties…” button.
Workbook_Connections

Now check the “Refresh data when opening the file” option that we purposely held off on earlier.
Connection_Properties
8. To test the connection, close your Excel file. Then browse to and open the “FanDuel.csv” file.

Make an obvious edit to a player’s salary (for example, change Clayton Kerhsaw’s salary to “99999”), save your changes, and close the CSV file.
Clayton_Kershaw_FanDuel_Salary

Note, your CSV file may open in Excel. You can still edit a player’s salary. When you go to save the file, you’ll get a series of annoying messages about “Do you want to keep using the CSV format?”. Just be sure you’ve saved the file and say “Yes” to those questions. You’ll get prompted with the same set of questions when you close the CSV file.

9. Now open your Excel file and verify that the salary change you made flows through. You might first get a security warning from Excel that data connections have been disabled. Click the “Enable Content” button and watch the data below update.

My data files sometime import in a strange order. You may have to use the “Salary” drop down menu to sort by salary in descending order (to see Kershaw at the top).Sort_Salaries_in_Descending_Order

Clayton_Kershaw_FanDuel_Salary_Excel

Changing the Name of a Table

Now that you’ve created an Excel table, you an edit its name and other properties on the “Table Tools” tab. This tab is not always visible but should appear when you select a cell within the table.

Excel's Table Design Tools

Once you’ve selected the “Table Tools” tab of the ribbon, you can change the name of the table under the “Properties” icon set all the way to the left of the tab. Remember, a great deal of the value from using structured references is the ability to use type ahead formula building and to have meaning in your formulas. So avoid names like “Table1” and go for things like “FanDuel_Salaries” or “Table_FanDuel”.

TABLE_NAME

Using Type Ahead in Formulas

Once you’ve set up a table, the type ahead features immediately activate with no effort needed on your part. To see the type ahead in action, just start by typing the name of your table in a formula.

You can see in the image below that just typing “Ta” (for “Table_FanDuel”) pulls up the table. You can then use your mouse to double-click on one of the items in the list or use your arrow keys to select one and then hit the “Tab” key to select the highlighted item.

TYPE_AHEAD

Once you have completed adding the table name, an open bracket (“[“) will then present you with a list of field names, all of which you can cycle through with the up and down arrrow keys and then hit Tab to select a column/field.

Excel Type Ahead Field Names

Wrapping Up

A little bit of a personal story here. I occasionally give Excel trainings at work to people that know their way around a spreadsheet pretty well. Most could even rattle off a VLOOKUP formula without much thought. I show them Excel tables and structured references and everyone’s eyes glaze over and they look like a bunch of deer in headlights.

I get it. The structured reference thing is new. If you already know how to do a VLOOKUP it’s hard to see the value in changing. Why take the time to do this?

Keep in mind that I try to design spreadsheets that will last for a long time and be reusable. At least for one season and possibly into future seasons. You could easily just get caught in a cycle of whipping up an inferior spreadsheet each time you want to create a lineup. But I would rather invest the time to build a long-term tool on a strong foundation. This way I can save time each night by having a prebuilt tool and because it’s built on a strong foundation I can continue to add new data, projections, and features over time.

And don’t you just love the pretty alternating row colors????

The SFBB way is all about doing things yourself, building things the right way, and continually improving and learning new things. So take the time to play around with structured references and learn the language.

Stay smart.

FanDuel Name, FanDuel ID, and Draft Kings Name Added to Player ID Map

Short and sweet update here… Columns for “FanDuelName”, “FanDuelID”, and “DraftKingsName” have been added to the Player ID Map (click to download the Excel file).

FanDuel and Draft Kings names for Paul Goldschmidt, Carlos Gomez, Yan Gomes, and Adrian Gonzalez.
FANDUELNAME, FANDUELID, and DRAFTKINGSNAME columns have been added to the Player ID Map.

New to the site? Here are some past articles about how to use the Player ID Map in developing your spreadsheets (they focus on the season-long game, but the principles of using Player IDs or the map to account for differences in player names across sites still apply).

Please keep in mind that the Player ID Map is not intended to be all encompassing. I aim to keep all “fantasy relevant” players on the list. On choice days when a bench player or swing-man starting pitcher get the call, one might argue they become “fantasy relevant” to the daily game, but such players may not be included in the listing. I have to draw the line somewhere!

Thanks for following the site and stay smart.

How to Import Draft Kings Salaries into Excel

I’ve been exclusively focusing on web querying Fan Duel to this point. But what about Draft Kings? Is there a way to web query their salary information? Here’s an e-mail I recently got from a reader of the site…

Hey Tanner, I just got back from vacation and saw some of your recent posts. That looks great… but I play on Draft Kings. When I try to do those steps on that site, it doesn’t work. No player names. No salaries. Any ideas?

Draft Kings salary info.
There is no <Table> element on the Draft Kings salary info.

I followed the same web querying steps we went through in this post, “How to Use Excel to Web Query DFS and Other Fantasy Baseball Data”, using FanDuel’s site this time. And sure enough, no matter what web querying option you use… No dice. If you do an investigation using the “Inspect element” option for your browser, I believe the technical reason the web query is unsuccessful is because the salary information is not coded as an HTML table.

So things aren’t going to be as easy as the web query. But due to a nice feature on Draft Kings, we can get very close. It’ll take just a few extra mouse clicks to get to the same place.

You see, even running a FanDuel web query isn’t seamless. You still have to log into your FanDuel account in your browser, locate a contest ID (URL), and type it into your Excel file to kick off the web query.

One Extra Step for Draft Kings

While Draft Kings salary info can’t be web queried, they do offer the ability to export all player information from a given contest into a nicely packaged CSV file. The link for a contest appears just below the player list you sort through to select players for your lineup.Export DraftKings salary information to CSV.

“CSV” stands for “comma separated values”. Many computer generated data export files have some kind of delimiter, or character, that breaks the data into columns. The CSV format specifically uses a comma to separate the values. Here’s what a CSV file looks like when opened outside of Excel:

Draft Kings comma separated values export file.

That Looks Awful, How Do I Work With That File?

The CSV format does not look user-friendly when viewed outside of Excel. But fortunately, once you install Excel, it becomes the default program to open CSV files… And this format is so standardized, Excel is trained to clean up the data and put it in our friendly Excel format:

Draft Kings CSV export opened in Excel.

This Doesn’t Look as Efficient as a Web Query

Right about now you’re probably thinking something like this…

You mean to tell me each time I want to update my DFS baseball spreadsheet, I need to…

  1. go Draft Kings,
  2. find a contest to enter,
  3. export the salary information,
  4. open that CSV file,
  5. copy the information,
  6. and then paste it into my spreadsheet?

Not quite. Some of those things we have to do regardless. We can’t really automate the selection of the contest you want to enter. But we can have Excel automate steps four, five, and six (the process of getting the CSV data into a preexisting DFS spreadsheet.

Excel Data From External Sources

DATA_FROM_TEXTYou might remember in the previous web querying article that we used the “From Web” option to “Get External Data” into our Excel files.

Fortunately, Excel also has an external data option for “From Text” (and a CSV file is a text file) that works on the exact same principles of a web query.

Just like how the web query is set up to pull information from a very specific web URL, this text file link can be set up to pull information from a very specific file path.

So as long as we save and name our Draft Kings CSV file in the same place each time, the data can automatically pull in to our DFS Excel files each time we open it.

Step-By-Step Instructions

Step Description
1. Log into your Draft Kings account (unlike for a web query, you can use any browser you wish for this).

Once you’ve logged in, locate a contest you’d like to participate in and click the “ENTER>>” button.ENTER_DK_CONTEST
2. After the contest loads, click the “EXPORT TO CSV” button below the player salaries (located on the left half of the contest screen).

NOTE: It does not matter if you check or uncheck the “ONLY PROBABLE PITCHERS” box. The export always contains all pitchers.EXPORT_TO_CSV_DRAFT_KINGS
3. Depending on the browser you’re using and its settings, one of three things will likely happen at this point:

  1. The CSV file will automatically download to your pre-specified download location
  2. You’ll be prompted to name the CSV file and choose where to save the file
  3. Or Excel may open and display the CSV file immediately

Regardless of what scenario you find yourself in, the main goal here is to save the CSV in a location where you want it to stay going forward. Maybe that’s your desktop or maybe you have a “Fantasy Baseball” or “DFS” folder on your computer. Save the CSV file in that long-term location.

So if it automatically downloaded to your “Downloads” folder, move it. If you’re being asked where to save the file, point it to the desired location. If Excel launched, perform a “Save As” to the desired folder. If you have the CSV file open, close it now.

4. Open your DFS spreadsheet (you may want to make a backup before proceeding) or start a blank Excel file if you’re just beginning (don’t start in the CSV file!).
5. Go to the “Data” tab and click the “From Text” button.
DATA_FROM_TEXT
6. At this point Excel will prompt you to browse for the text file you want to import. Browse to and locate your CSV file.
DKSalaries.csv

Once you’ve located the file, select it and click the “Import” button.
Import DKSalaries.csv
7. Excel’s “Text Import Wizard” will launch and ask which type of text file you’re importing. Remember that a CSV file is a special type of delimited text file that uses a comma to separate the values (a fixed width file doesn’t have a character (like a comma or semi-colon) separating the data, it would just have clearly visible spaces separating the data into columns). So choose the “Delimited” option.

Also check the “My data has headers” box and click “Next”.

IMPORT DELIMITED

On the next screen, choose the “Comma” delimiter option and uncheck any other options. You’ll get a preview of how the columns will be identified. Click “Next”.
COMMA_DELIMITED

On the final screen of the Text Import Wizard you can likely just hit “Finish”. For future imports, if you have a column containing something you only want treated as text or a date, you can choose those options. But “General” is fine for our purposes.

FINISH_TEXT_IMPORT_WIZARD
8. Once you click “Finish”, you’ll be prompted about where you want to import the CSV data to in the Excel file. If you are working in your pre-existing DFS spreadsheet, click on the tab you want the text to import to and use the “Existing Worksheet” option, or just choose the “New worksheet” radio button.

Don’t click OK yet! Click the “Properties” button.

IMPORT_DATA_OPTIONS

9. There are many available properties to adjust, but I’ll call out a few I feel the most strongly about changing:

Prompt for file name on refresh – If you plan to save the CSV file in the same location and with the same name each time, you should uncheck this option. If left checked, each time you refresh the connection to the CSV file you will need to browse to and select the file. Conversely, if you don’t want the restriction of having to name the file consistently and in the same spot, you may want this checked. But you’ll then need to browse and point to the new file each time you want to refresh the link.

Refresh data when opening the file – As you can probably guess, if you check this box, each time you open your DFS spreadsheet it will link to and import the text file information.

Fill down formulas in columns adjacent to data – We may not need this, but if we end up needing formulas next to the data being imported, I would recommend checking this box.

IMPORT_PROPERTIES

Click “Ok” to accept the settings changes.

Then click the next “OK” button on the “Import Data” window.

10. That’s it! The next day, when you go to prepare for a new contest, simply repeat steps 1 through 3.

In doing so, be careful to name or save the CSV with the exact same name and in the exact same location as your Excel file is looking to.

For instance, if you saved your first Draft Kings export as C:\Users\tanner.bell\Downloads\DKSalaries.csv, then make sure you save it there again the next day. If you have an older CSV already sitting in that location, the export will likely save as “DKSalaries (1).csv”. Not a problem. Just delete the older one and rename the new download to be just “DKSalaries.csv” (right-click on the file and choose “Rename”).
Draft Kings Salary CSV Export

Web Query vs. CSV Import

I’m not sure which is superior or if there is even a clear winner. There is something elegant about the web query. And I really like how you can create a dynamic web query (I wish there were a way to do a dynamic text file link, but I can’t see a way to do that). But the web query is slow.

The text file import does require a couple of extra steps, in terms of naming the CSV file, but once that part is complete it’s essentially the same. The actual import of the data into Excel is much faster for the text file option (at times my web queries can take 30 seconds or more to complete).

For both options, Excel will seamlessly import the data and the rest of your Excel file can be built around this to automatically update based on the salary list.

Second Look at FanDuel

Having noticed this for Draft Kings, I took another look at FanDuel… Lo and behold, FanDuel has the same export option.

Export FanDuel player list and salaries.

One key difference in the FanDuel CSV is that it has a unique file name associated with the contest ID. Unique FanDuel CSV file name.

So if you decide to go the CSV route for FanDuel, instead of using this unique name, rename/save the file with a generic name like “FDSalaries.csv”. If you also save the file in the same location each time, you would be able to link an Excel file to it repeatedly. At that point, just follow the setup instructions above and there is no difference in the setup of a FanDuel export and a Draft Kings export.

But Wait… What is that on the FanDuel Salaries List???

Oh my… This might be enough to convert me off the web query and to an exported CSV approach even for FanDuel (damn you Chris Youngs and Jose Ramirezes!).

FanDuel Player ID Numbers

Player IDs (don’t know what a Player ID is?)… FanDuel player IDs, coming soon to a Player ID Map near you.

Doing This in Excel For Mac 2011

If you happen to be using Excel on a Mac, the menu location to start the import process is slightly different. You can find it on the “Data” tab. Excel for Mac Import

Or you can also find it under the Data menu.Data_Get_External_Data_Import_Text_Mac

Once you find these menu options, the settings are the same as above (for the most part). Make sure to click on the “Properties” button to get options about when and how to refresh the import upon opening the Excel file. Text file import properties.

How Is Your DFS Spreadsheet Coming Along

Do you have any neat features you’ve added? Struggling to get some kind of information added? Feel free to post a message below to share what you’ve done or let me know your struggles and maybe I can help.

Stay smart.

How to Use Excel Text Manipulation Formulas with Fantasy Baseball Data

In our last post we took a close look at a handful of different web query options available in Microsoft Excel.

By the end of that post we had an Excel file that was able to automatically go out and pull in a raw listing of player names and salaries. All you had to do was figure out the ID for the slate of games you’re entering on FanDuel and type in that five digit number.

FanDuelContestID

Then voila… Current salary information (or you can even run it for tomorrow to start planning the night before)!

FanDuel_Salaries

The Next Step

So now we have player names and salaries. And we know from doing research at how to succeed playing DFS that we also need to bring in other information like batting and pitching splits, Vegas over/unders, and weather data.

We also know that using Player IDs is a more reliable way of matching players up with all of this data.

New Column On The Player ID Map

To bring all this data together, I recognized that we need a way to match a player’s name according to FanDuel to the player ID systems shown on the Player ID Map. So I made an update to the map over the weekend and added “FANDUELNAME” in column AF (I also added over 30 new players that have been called up during this season and/or are starting to have a “fantasy impact”. Guys like Carson Smith, Nate Karns, Carlos Frias, Lance McCullers).

FanDuelName

If you’re using the Player ID Map and you want instructions on how to drop in an updated version, check this out.

But We Had a Problem

Look closely at the image below.ClaytonKershawFanDuel

Who the heck is “Clayton KershawP”?

If you look through the list a little more you will see “Jose FernandezDL”, “Yordano VenturaDTD”, and “Ervin SantanaNA”.

You can probably see that some player names are reflecting health or availability information. This is great, but it poses a challenge for our ability to match to the new column in the Player ID Map. Kershaw is going to be listed as “Clayton Kershaw” on the list. Not “KershawP”.

Enter Excel Formulas “RIGHT”, “LEFT”, “FIND”, “LEN”, and “IF”

We are going to use a series of formulas to do the following things:

  1. Identify if a player name has “DL”, “DTD”, “NA”, or “P”. I’ve scanned the list of player names and those are the only injury/availability classifications I see.
  2. If a player has one of those health indicators on their name we will strip it off
  3. If a player does not have health indicators, we’ll just use their name as it is shown

RIGHT

The RIGHT function will give you the rightmost characters in a text string. For example, we could use the RIGHT function to look at the string “Yu DarvishDL” to determine if the two rightmost characters are “DL”.

This formula uses two inputs:

  1. Text – The main string of text you want to pull the rightmost characters from (this can be a cell reference)
  2. Num_chars – The number of characters to pull out

RIGHT_FORMULA

We will use the RIGHT function to look at only the last three characters of each player’s name. We only need the last three because our longest health indicator is “DTD” (three characters in length). And we don’t want to look at full names, because one of our health indicators is just “P”. We can’t just tell Excel to look for a capital P in an entire player name because player’s like Michael Pineda or David Price might cause us trouble. So by limiting our search to just the last three letters in a player name, we should be able to located “DL”, “DTD”, “NA”, and “P” without issue.

FIND

The FIND function searches for a specific string of text within another string of text. If the string you are searching for is located, it returns a number that indicates the location where the string starts. FIND is also case-sensitive (thank goodness, otherwise looking for “NA” at the end of player names could be a problem with guys named “Santana”).

This formula requires two inputs and has one optional input:

  1. Find_text – The string of text you are searching for. You can enter your text in double quotes or use a cell reference.
  2. Within_text – The string of text you want to search within. This can be a cell reference.
  3. Start_num (optional) – What character within the string you want to start searching at. For example, if you wanted to start by searching only after the fifth character in a name, you could enter a five for this argument.

FIND_FORMULA

We will use the FIND function in conjunction with the RIGHT formula mentioned above. We will use RIGHT to first pull out only the last three characters from a player’s name. We will then run a FIND on those three characters to look for “P”, “DTD”, “DL”, or “N/A”.

LEFT

Similar to the RIGHT function, LEFT will give you the leftmost characters in a text string.

This formula uses two inputs:

  1. Text – The main string of text you want to pull the leftmost characters from (this can be a cell reference)
  2. Num_chars – The number of characters to pull out

LEFT_FORMULA

We will use LEFT on players that have health information in their name. For example, for “Clayton KershawP”, we will want to pull out the first 15 characters (out of the total 16 characters in the full string of text).

LEN

The LEN function will tell you the number of characters in a string of text.

This formula has just one input, Text, which represents the text to want to count the characters from. This can be a cell reference.

LEN_FORMULA

We will use LEN with the LEFT function mentioned above. Remember the “Clayton KershawP” example where I mentioned that string of text has 16 characters and we want to take the leftmost 15 characters? We will use LEN to easily get that 16. We’ll use LEN to evaluate how many characters are in every player’s name.

IF

The IF function allows you to evaluate a cell to see if a condition is true or false. If the condition is true, we can give one result or a specific formula. If the condition is false, we can give a second result or an alternative formula.

This formula uses three inputs:

  1. Logical_test – The condition you want to evaluate for being true or false.
  2. Value_if_true – The value or formula you want to run if the Logical_test is true.
  3. Value_if_false – The value or formula you want to run if the Logical_test is false.

IF_FORMULA

Nesting Formulas

By default, the IF function only allows you two options. One if true. One if false.

This might be a problem for us. Here are some of the tests we need to run…

If we find “P” in the last three characters of the player name, cut off the last character from the name (or if the player’s name is 16 characters in length, give me the leftmost 15). If we find a “DL” in the last three characters of the player name, cut off the last two characters from the name (or give me the leftmost 14 from a 16 character name). If we find a “DTD” in the last three characters, cut off those three letters from the name (or give me the leftmost 13 from a 16 character name). If we find an “NA”, cut off the last two characters (14 from 16). And if none of those are true, then just give me the full length of the player’s name and remove nothing.

Thankfully you can also “nest” IF functions to give you more options. In a weird combination of Excel language and plain English, our formula will look something like this:

=IF(player is status "P", give me LEFT(player_name, LEN(player_name)-1), if their status is not "P" then IF(player status is "DL", give me LEFT(player_name, LEN(player_name)-2),if their status is not "DL" then IF(player status is "DTD", give me LEFT(player_name, LEN(player_name)-3, if their status is not "DL" then IF(player is "NA", give me LEFT(player_name, LEN(player_name)-2), if their status is not "NA" just give me player_name))))

This is basically saying, if a player is marked as “P”, cut off one character from their name (that’s what the “-1” is doing in the LEFT(player_name, LEN(player_name)-1). If the player is not marked as “P”, go to the next IF statement. If the player is marked as “DL”, cut off two characters from their name LEFT(player_name, LEN(player_name)-2). If the player is not marked as “DL, go to the next IF statement.

And so on. Until we get to the very end of all the IF statements. The final player_name just indicates that if all the previous IF conditions are false, this is what will show.

One More Web Query Option I Didn’t Mention

We looked at quite a few web query options in the last post, but I neglected to mention one that will now be helpful to use. We are going to be placing the formulas from above next to the FanDuel web query results (you can see my vision of how this will look below, they’re shaded a yellowish color).

FormulasNextToData

The potential problem with doing this is that the web query results and FanDuel’s list of player names will be changing each day and for each contest. The length of the player list is going to be a lot longer for a full slate of games on a Wednesday night than it will be for the afternoon slate on a Thursday.

Fortunately, Excel offers a setting that will attempt to fill the formulas down as far as the data in the web query reaches. To activate this setting, first select a cell in the middle of the web query results (for example, select the cell with Clayton Kerhsaw’s name).

Clayton_Kershaw_FanDuel

Then click on the “Data” tab. And then click the “Properties” button in the “Connection” section of the Data tab.

Data_Connection_Properties

This will bring up the “External Data Range Properties” dialog. Check the bottom check box for “Fill down formulas in columns adjacent to data”. This will attempt to fill our formulas to the exact size of the web query results. I also like to uncheck the “Adjust column width” box because each time the web query runs, it resizes my columns and I find it annoying.

External_Data_Range_Properties

Step-By-Step Instructions

If you want to make sure you’re at the same starting point as me, here’s an Excel file Continue reading “How to Use Excel Text Manipulation Formulas with Fantasy Baseball Data”

How to Use Excel to Web Query DFS and Other Fantasy Baseball Data

NOTE: It appears the information below is no longer relevant. You are welcome to read the article because it still contains a valuable approach that can be used in other scenarios. But the specific act of web querying FanDuel for salary information as shown below no longer seems to work.

With a web query there will be no more manual copying and pasting of data.
With a web query there will be no more manual copy and pasting of data.
Does this sound familiar?

You’re doing prep work for setting a daily lineup or even preparing for a season-long rotiserrie league. You’re trying to set up a spreadsheet to help you prepare, but you are sick and tired of having to copy information from the web and paste it into Excel.

There has to be a better way!

In this post I’ll show you an efficient way of grabbing player salaries directly from FanDuel’s website using Excel’s web query function. In fact, I’ll show you three different variations of Excel web queries:

  1. Simple web query
  2. Dynamic web query
  3. Table-specific web query

Before I begin, I need to be honest with you about something.

I Don’t Know Where We’re Going

This is the first post of what I hope will be a series documenting how to build a spreadsheet for DFS. But the thing is, I don’t know exactly where I’m headed on this journey. I can’t promise you a panacea to cure all your DFS aches and pains. I don’t have a master plan that will lead us to a perfect functioning spreadsheet that will fit everyone’s exact desires. But my plan is to just start moving the ball in the right direction.

I don’t have much DFS experience. I don’t know exactly what you want. If you are looking for DFS lineup advice, I can tell you I won’t be giving that. But what I do have are a very particular set of skills. Skills I have acquired over a very long career… OK, I’ll end my Liam Neeson joke.

I hope that by just starting to build something, starting to share techniques you can use on your own, and by seeking feedback, we will eventually end up with something special.

Some of the techniques I’ll show you may seem silly. Or pointless. Or way too involved.

But I have a purpose in mind. You might wonder why I pull data from Site Y when Site X has the same information in an easier to use format. Or you may want to incorporate additional data that I don’t want to pursue.

That is why I want to stress it is not the “WHAT” in the instructions that is the important part. It is the “HOW”. I’m going for that whole “teach a man to fish” proverb. So let’s start learning…

Not All Versions of Excel are Created Equal

I’ll be using Office 2013 installed on a Windows 7 machine. I believe the web querying experience will be similar for Office 2007 and Office 2010 (while using Windows). Unfortunately, I have had poor results with web querying in Excel for Mac, but it can be done.

What Is a Web Query?

A web query is an automated way to copy information from a table on a web page into Excel. It removes the need for you to manually copy and paste data from websites into Excel. On top of that, you can set the query up to run each time you open an Excel file or change a particular value, so you can escape the cycle of continuously needing to go to a web site, copy the data, and paste it into Excel each day you want to set lineups. It can refresh automatically!

As you’ll soon see, setting up a basic web query is fairly easy. But there are a couple of more advanced settings that should make your life a little bit easier.

Step-By-Step Instructions, Simple Web Query

For this example, let’s just go with a simple and straight-forward web query. Later in this post we will build on this.

Continue reading “How to Use Excel to Web Query DFS and Other Fantasy Baseball Data”

How to Automatically Shade Drafted Players In Excel

I know, I know. It’s mid-March, college basketball is taking over your time, and you’re in the final push of draft preparation for your baseball leagues. The last thing you want to do right now is start tinkering with your draft spreadsheet and screw something up.

Then here I come with another monster Excel post…

NOT THIS TIME! I swear. This is an easy one and you might find it helpful.

In this post I’ll show you how to use Excel’s conditional formatting to gray out players that have been chosen during your draft.

Excel’s Conditional Formatting

Conditional Formatting is a way to adjust the format of individual (or many) cells based upon the information within that cell. If you were using this in a business spreadsheet you might want really high sales months to show up in bolded font or below average sales people to show up shaded in red.

For fantasy baseball you might want all players projected to hit 30 HR to have their HR projection in bold red font. Who knows.

The point is you can tell Excel “what to format” and “how to format it”.

Here’s an example of what I’ll show you in this specific post:EXAMPLE_DRAFTED_PLAYERS

Assumptions

I’m using Excel 2013 in the screenshots below. I think this will work on Excel 2007 or 2010 without any issues. I’m also assuming you’re starting with a spreadsheet you created following either this standings gain points series or this points league series.

Step-by-Step Instructions

Continue reading “How to Automatically Shade Drafted Players In Excel”

How to Add Average Draft Position Info to Your Spreadsheet

I think you’re gonna like this one.

In this post I’ll show you how to link live average draft position information from the web into your draft spreadsheet. Every time you open your rankings file it will pull down updated ADP information. Bam!

How We’re Going To Do This

We will use a powerful feature of Excel called web querying to pull in the ADP information aggregated by FantasyPros.com (please note, I’m an affiliate of Fantasy Pros). The web query will suck up that table of ADP information and bring it directly into Excel for us to then VLOOKUP into our existing “Hitter Ranks” and “Pitcher Ranks” tabs.

Fantasy Pros has the best ADP information I've found on the web. They have the most sources in one place and it appears to be updated regularly.
Fantasy Pros has the best ADP information I’ve found on the web. They have the most sources in one place and it appears to be updated regularly.

Assumptions

I am assuming that you’ve followed my Standings Gain Points or Points-League Ranking series and are already starting with a spreadsheet that is based off one of those (you don’t have to have those exact spreadsheets, but something similar).

Excel Functions and Concepts Used in this Post

Web Query

As you can probably imagine, the power of the web query is that it automatically updates the data in your Excel file without you having to do ANYTHING after the initial setup.

Web queries are created from the “Data” tab on the Excel Ribbon, under the “Get External Data” icon grouping. There are several ways to get data from outside sources into Excel, we will be using the “From Web” button.

GET_EXTERNAL_DATA

One “weakness” I have found in web queries is that they cannot work with “tables” in Excel. You cannot pull a web query in as the data of a table. If you’re a big follower of this site you know that’s a bit of an issue for me because I use tables all the time. Thankfully this doesn’t prevent us from doing things, I just point it out because you might wonder why I set things up the way I do in the instructions below.

Find

The FIND function searches within a specified cell for a string of text that you provide. If the FIND function locates the string, it will return the character position where the string starts at.

I know, you’re thinking “What the heck does that even mean?”. Here’s an example. Let’s say we have the text “Mike Trout (LAA)” in a cell and every player in our whole spreadsheet follows that format. If we want to pull out each player’s team we will need to start by figuring out where the team name starts in that cell. And we can’t just say it will always start at the 13th character each time when we have players like this hanging around MLB.

Instead we can use the FIND formula to intelligently determine where that opening parenthesis is for each player (it starts at 12 for Trout and 23 for Salty).

This formula requires two inputs:

FIND(Find_text, Within_text)

FIND

  1. Find_text – This is the string of text you are searching for and keep in mind it is case-sensitive. You would wrap the string you are searching for in quotation marks. So in our example above, to look for the opening parenthesis you would enter “(” here. Or if you’re trying to be slightly more precise, you could enter ” (“, a space before the parenthesis.

  2. Within_text – This is the text you want to search WITHIN. This can be a cell number.

Left

The LEFT function gives you the leftmost number of characters in a text string. You also get to specify the number of characters to specify.

For example, if you have a text string of “Mike Trout (LAA)” and you ask for the 10 leftmost characters in that string, you would get “Mike Trout” back.

This formula requires two inputs:

LEFT(Text, Num_chars)

LEFT_FUNCTION

  1. Text – This is the text string you want the leftmost characters from.

  2. Num_chars – This is the number of characters you want from the string. This can be a hard entered number (e.g. 10) or it can be a formula itself that results in a number.

Combining Functions Together

We can do something pretty powerful by combining the FIND and LEFT functions together. I’ve been hinting at it with this “Mike Trout (LAA)” example. Recall from above that the LEFT function wants to know our text string (“Mike Trout (LAA)”) and the number of characters on the left to pull from that string.

Assume that cell B2 has a value of “Mike Trout (LAA)”. Instead of using this formula:

LEFT(B2, 10)

We can use this:

LEFT(B2, FIND(" (",B2)-1)

The FIND(" (",B2)-1 part of the formula returns a 10 (if you don’t subtract the one it returns an 11), and “Mike Trout” has 10 characters in it (including the space). By using this combination of functions we don’t have to type in a “10” for Mike Trout and a “21” for Saltalamacchia.

Important Prerequisite

Before you’re able to proceed with the instructions below you must make sure the PLAYERIDMAP in the file you’re working with was updated after February 21st, 2015. I added a column to the Player ID Map called “FANTPROSNAME” that is necessary for the steps below to work.

Instructions for updating your PLAYERIDMAP can be found here. Completing the update should only take five minutes or so.

Step-by-Step Instructions

Continue reading “How to Add Average Draft Position Info to Your Spreadsheet”

How to Update the Player ID Map in your Spreadsheets

Warning – The instructions below are likely only relevant if you are following some of my much older work. The Player ID Map has since been updated to allow much easier updating. If you’re looking for guidance relating to a spreadsheet you’ve built or purchased since 2015, you likely want to be looking here for guidance relating to the Player ID Map.

You’ve been following the site for a while. You’ve even created a spreadsheet to develop your own points league or SGP rankings. You’ve spent all this time building this spreadsheet but it’s getting to be a bit out of date. Players have been traded, rookies have been called up from the minors…

How do you update things? Do you have to rebuild your spreadsheets from scratch each season?

No way, give me some credit! I’m smarter than that. I design things to be reusable.

In this post I’ll show you how to quickly and easily update the Player ID Map in your spreadsheet so you can get updated MLB teams and have new players available to tie in to your projections.

Warning!

All we’re really doing here is downloading the new version of the Player ID Map and pasting it on top of our existing Player ID Map already in your ranking file. The key is that you have to be very particular about how you paste the new version in. If you’re not careful you will break all the existing formulas in your spreadsheet that reference the PLAYERIDMAP named table.

Read carefully!

Step-by-Step Instructions

Step Description
1. Open your existing rankings spreadsheet, the one in which you want the new Player ID Map information. Save a backup copy of the file, just in case something were to go wrong during this process.

Go to the PLAYERIDMAP tab.PLAYER_ID_MAP
2. We will soon be pasting information onto this sheet so it is important to make sure all the data is currently showing.

Click on Excel’s “Data” tab and then click the “Clear” button of the “Sort & Filter” icon grouping. CLEAR_FILTERS
3. Click this link to download an updated copy of the Player ID Map.

Once the download completes, open the file. If Excel is displaying any kind of warning message, enable your ability to edit the file (provided you trust this site).ENABLE_EDITING

Now refresh the content to pull in any recently added players. Do this by right-clicking on a cell within the table (somewhere within the blue and white rows of data). Then choose the option to “Refresh.”
4. Place your mouse in cell A1 of the newly downloaded Player ID Map. Then hit the CTRL + SHIFT + End keys all at once. After you’ve done this release the keys. Then hit SHIFT + the up arrow key.LAST_PLAYER
This set of key strokes should select the entire Player ID Map table and then deselect the “Last Player”.

Now hit CTRL + C to copy the selected data.
5. Return to your customized rankings spreadsheet. Select cell A1 with your mouse and then paste the data you just copied over cell A1.

The reasoning behind this specific set of copying and pasting instruction is so that the existing table named “PLAYERIDMAP” in your rankings spreadsheet will not be renamed during this process. If you don’t deselect the “Last Player” before copying, the entire Player ID Map table will be renamed and it will break all existing VLOOKUP formulas you have looking for this information.
6. That’s it!

Well, kind of. Any new players added to the PLAYERIDMAP will not yet be listed on your “Hitter Ranks” or “Pitcher Ranks” worksheets.

This is where you have a decision to make.

If you have taken notes next to players, entered keeper dollar values, or otherwise “hard entered” information that relates to a specific player, then you manually add the player IDs of “new” players to your “Hitter Ranks” or “Pitcher Ranks” tabs.

For example, simply go to the “Hitter Ranks” tab and type the player’s ID at the very bottom of the first column. When you hit enter the Excel table should grow to add your new player and all the other formulas should automatically copy down (another benefit of using Excel tables!). FRANCISCO_LINDOR

If you’re not sure what players were added to the PLAYERIDMAP, you can look on the “CHANGE LOG” tab on the newly downloaded Player ID file to see a brief note of all the players added or updated recently. CHANGE_LOG

I try to put brief descriptions of the players that have been added so you can manually add to your “Hitter Ranks” or “Pitcher Ranks” sheets, if necessary.CHANGE_LOG_INFO

7. If you have not edited dollar values or added player notes, you can copy and paste the hitter IDs onto the “Hitter Ranks” sheet and the pitcher IDs on to the “Pitcher Ranks” sheet.

To do this, go to the PLAYERIDMAP tab in your spreadsheet and apply a filter to only show hitters. On the “POS” column filter, uncheck the “N/A” (if there are any) and “P” check boxes. This will only display the hitters.POSITION_FILTER

Then select cell the first cell below the header in column A and hit the SHIFT + CTRL + Down Arrow Key. SHIFT_CTRL_DOWNCopy this information and go to your “Hitter Ranks” tab and paste it into the first cell below the header in column A there.PASTE_HITTER_LIST

After you do this all the other information on the tab should update immediately.

No go back to the PLAYERIDMAP tab and adjust the filter to only show pitchers and repeat the process by pasting those players onto the “Pitcher Ranks” tab.

Now you’re done!

Have Any Questions?

Please leave a comment on this post.

I have to do this quite frequently to keep all the spreadsheets I maintain for the site up-to-date, but this is probably something you’ll only need to do a few times a year. Maybe after the season ends, to get all the new players I’ve added during the season, late February, to get all the players that have changed teams, and once during the season, if you’re doing in-season rankings.

Want More Tips Like This

Make sure to follow me on Twitter, that’s the best place to hear about new posts and updates at the site.