15 Responses

  1. Sean
    Sean at | | Reply

    Hi, Thanks for the note on using PowerQuery. I actually use it for work a lot so I am quite familiar with it, but I haven’t quite gotten there on the fantasy baseball project I’m working on and was wondering if you could help. I’m primarily interested in tracking my league’s daily game activity / game logs. So 10 teams, 23 players, so give or take 230+ players throughout the 162 game season. I’m trying to figure a way to easily pull in all these players’ game logs – I know Fangraphs and B-R offer these, but power query seems to not be able to pull them through the table method you have explained here, so I’ve basically resorted to downloaded to excel and combining, which obviously is a big task. Basically, I’d like a way to 1) streamline 230+ players to be able to write a macro or code to download them all from their respective fangraphs or B-R game log page, and 2) constantly update throughout the season. Would love to hear your thoughts on this now that you’ve gotten to PQ demonstrations and may know something I don’t.

    1. Paul
      Paul at | | Reply

      Hey! I’ve been looking to do exactly what you’re doing. Were you able to come up with any macros or get anywhere with this?

      1. Tanner Bell
        Tanner Bell at | | Reply

        Hey Paul,

        Sorry, I haven’t made much progress on this and automating a daily spreadsheet. Maybe this year. Who knows. I think I’m going to make a good faith effort to start playing some DFS baseball this summer.

  2. Ralph
    Ralph at | | Reply

    Tanner,
    So, for instance, if I have created a conditionally-formatted spreadsheet using my own Fangraphs charts, using Power Query is the only way to update the spreadsheet automatically without exporting the data from Fangraphs and pasting it manually?

    1. Tanner
      Tanner at | | Reply

      Hi Ralph,

      No, that’s not necessarily true. Apologies if I said something misleading. There is a setting you can use to have data from a regular (non-Power Query) web query refresh when you open an Excel file. Or you can right-click within a set of connected data (from a regular web query) and choose to “Refresh” it manually.

      To have it refresh upon opening the file, the very last step of creating a web query, on the screen where Excel is asking where you want to data to be placed, should be a “Properties” button. In that properties screen is a check box to refresh data upon opening the Excel file.

      You definitely don’t need to copy and paste it.

      The main advantage I see in Power Query is it brings data in as an Excel table, which a regular web query does not do. But in the grand scheme of things, that’s not a significant advantage. Power Query also seems to have some very high-end powerful features that I don’t fully understand yet. Maybe one day!

      1. Ralph
        Ralph at | | Reply

        Tanner,
        No fault of yours. I have never been able to get web queries to work in any Excel files I create. I’m hoping that after my machine is upgraded to Windows 10 the problems will disappear. Also, I believe a new version of Excel is going to be released in September, so maybe that will help.
        Keep up the great work!

  3. Jon Acampora
    Jon Acampora at | | Reply

    Hi Tanner,
    Great article! This is Jon from ExcelCampus.com. Thanks for linking to a few of my articles in your post.

    It’s really cool to see how others are using Power Query, and your example is just awesome! It’s a perfect example of the power of Power Query, and what it can accomplish without any programming knowledge needed.

    In one of you comments above you mentioned that creating a SQL script and web scraping tool would be another alternative. Power Query is actually doing just that. All those steps you create in Power Query to modify the data actually create a SQL script in the background and run it on the database when you refresh the query. That SQL statement also depends on the source data. In this case the source is a web table, so most of the SQL work is done in Excel. If you are querying an actual database then the SQL work can be done on the database side before retrieving the data. This helps make Power Query fast and efficient.

    If you really want a mind blowing experience with Power Query, checkout PowerBI.com. This is a new FREE service from Microsoft that uses Power Query and Power Pivot to create interactive dashboards on the web. I’m sure you might be able to find uses for it with fantasy baseball. I have an overview video on the following page where I connected it to three different web page sources to create charts and dashboards about one of my favorite ski resorts.

    http://www.excelcampus.com/powerbi/powerbi-designer-overview/

    Let me know if you ever have any questions about PQ or any of these tools. Thanks again and have a good one Tanner!

    1. Tanner
      Tanner at | | Reply

      Hi Jon,

      Thanks for taking the time to reach out. I might take you up on the offer to ask questions about PQ! I won’t bore everyone with the details here, but I’ve been struggling trying to get a web query to read a value from a cell in the workbook.

      We’re a bunch of fantasy baseball fanatics trying to gain advantages by using data analysis, mostly in Excel. The challenge is to pull all different types of information and put it into an easy-to-digest format. We’re trying to build an efficient/easy way to pull player information, historic stats, opponent information, ball park information, lineup information, and more.

      It’s a blast!

  4. Ralph
    Ralph at | | Reply

    Tanner,
    Well, I got Power Query to work….sort of. I am trying to create a spreadsheet of my own fangraphs data that will update either automatically or when I refresh it. When I link one of my tables to the fangraphs URL, it only imports whatever data is showing on the webpage(i.e. the first 30 players), not the entire table(all the players in the table). If I try linking the table to the fangraphs “export data” shortcut, I get this error:

    Expression.Error: The scheme ‘javascript’ of the input URL isn’t supported

    Any ideas?

    1. Tanner
      Tanner at | | Reply

      Hi Ralph, unfortunately, I don’t see any way to automate this. The main reason being that the data does not actually load on the page. If you look at the underlying HTML for the page (right click somewhere on the page and choose “Inspect Element”), you will see that only thirty players load on a page. And you can’t just adjust the URL to get the next 30 (some sites you’ll see the web address changes for the next 30, meaning you could set up a bunch of different queries to get 30 at a time).

      The best suggestion I can offer is that you follow these instructions and use the CSV approach to update the data. It’s still a bit manual because you have to download the CSV each time, but those instructions will help you streamline it as much as possible.

  5. sanga collins
    sanga collins at | | Reply

    On fangraphs if you change the page to load 50 players it adds “&page=1_50” to the URL. You can then change that to &page=1_1000 to load 1000 players on one page and import that entire page into Excel :)

    1. Tanner
      Tanner at | | Reply

      Thanks for sharing this, Sanga. I only wish this worked on ALL pages. It doesn’t work on projection pages. But this is definitely helpful.

Leave a Reply