Get & Transform: An Alternative to Excel’s Web Browser

Excel has a built-in web browser that can be used to view web pages and import selected web data. This browser worked well for many years, but, recently, script errors began appearing on some pages. This makes it challenging to import data from some web pages, and frustrates users. The good news is that we can use a Get & Transform query as an alternative to opening Excel’s web browser.

Objective

Our goal is to import data from a web page. For example, we’d like to import some exchange rates from Google’s finance page. When we launch Excel’s web browser (Data > Get External Data > From Web), some pages show up just fine. This is illustrated below.

Google Home by Jeff Lenning

But, when we navigate to another page, we may get an intermittent script error. For example, if we try to navigate to Google’s Finance page shown below.

Script Error by Jeff Lenning

Often, you can refresh the page or close the browser and try again to get a clean page load, but, the error slows us down…and…we just don’t like being slowed down.

The good news is that we can retrieve web data using a Get & Transform query. This provides a clean alternative to Excel’s web browser. Let’s check it out.

Get & Transform Query

We’ll accomplish this with the following steps:

  • Enter the web page URL
  • Pick the data table
  • Load to Excel

We’ll work through each step together.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

Enter the web page URL

To retrieve web data using a Get & Transform query, you’ll need to know the web URL. You can commit it to memory if it’s short and simple, or, just browse to the page in your normal browser and copy the web address. Next, you’ll want to use the following command from the Get & Transform ribbon group:

  • Data > New Query > From Other Sources > From Web

This displays the From Web dialog, where we can enter the desired URL, as shown below.

From Web by Jeff Lenning

We click OK to open the Navigator dialog, where we can pick the desired data table.

Pick the data table

The Navigator dialog displays the importable web tables that exist at the URL. You can click through the tables and get a preview of the data. Since we want the exchange rate table, we click through the tables until we find the one we want, as shown below.

Excel Navigator by Jeff Lenning

With the data table selected, it is time to move to the final step.

Load to Excel

We use the Load button to bring the data back into Excel. If we select Load, Excel will place the data into a blank worksheet. If we select Load To, Excel allows us to specify the destination. Either way, the data is loaded into Excel, as shown below.

Excel web data by Jeff Lenning

Now, the best part is that we can easily update the data table from within Excel. Just right-click and refresh…and Excel retrieves the current data. And…no pesky script errors!

Resources

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

12 Comments

  1. Ron Yandell on July 8, 2016 at 7:17 am

    This is remarkable. Many times I have copied and past from web tables. Still learning. thanks

    • jefflenning on July 8, 2016 at 7:18 am

      Get & Transform is indeed remarkable my friend 🙂

  2. Miranda on July 11, 2016 at 10:01 am

    Very cool trick!!

  3. Hameed on May 9, 2017 at 2:29 pm

    this is awesome, thank you very much.

    is there a way to autorefresh or refresh on open document?

    many thanks.

    • Jeff Lenning on May 10, 2017 at 2:02 pm

      Yes…To do so, right-click the results table and select Table > External Data Properties. Then click the little “Connection Properties” icon. In the resulting Connection Properties dialog you can select refresh options, including refresh on open 🙂
      Thanks
      Jeff

      • Hameed on May 10, 2017 at 4:24 pm

        The you very much Jeff, you are a STAR..

        • Jeff Lenning on May 10, 2017 at 6:19 pm

          Welcome thx 🙂

  4. Peta on April 26, 2018 at 9:46 pm

    Hi Jeff
    I have Excel 2010 with Power Pivot add-in. Is there a work around for the script errors in this version.
    thanks

    • Jeff Lenning on April 27, 2018 at 2:03 pm

      Peta,
      I actually don’t have Excel 2010 here for testing, so, I’m unable to confirm. I’m hoping someone can help me out by posting a comment.
      Thanks
      Jeff

  5. Justin on July 11, 2019 at 11:14 am

    Hi

    Is there a way to hold or keep the pulled data for a certain amount of time? In your example, let’s say I want to keep 1.106 from Column 2 for a week so that i can make a chart for changes over a seven-day period. Is that possible? If i only refresh, that data is gone.

    Thank you!

  6. Fred K on December 20, 2019 at 12:56 am

    I can’t tell you how much I appreciated this. Thank you. I have been going crazy with Javascript errors on one of my computers but not the other. Both computers have Excel 2016 but with different versions of Win 10.

    So ‘Get & Transform ‘is doing what differently than a plain ‘Get External Data?’ And isn’t your technique also considered a ‘Power Query, which does what differently?

  7. Brian S on October 15, 2020 at 1:51 pm

    What if a login is required? Right now I use Excel Web query to download data from my brokerage. A login is required to access my account. Does the “Get & Transform Query” method support a login or connection string?

    Using the Data Source Settings for permissions does not work. I need a persistent login and the ability to make multiple requests, a single login.

    That is what I do with Web Query currently. I sign on in the morning, and all my Web Queries are under that sign on, which persists across the Excel instance.

    “Get & Transform Query” seems to want to send credentials with every request, instead of just once on the sign-on page, and then making requests to pages after sign-on. This method cause a rejection of the credentials, since the login page has no data, and the pages that have data do not have fields for login.

    I tried accessing the login page alone, hoping that I could sign in that way and then make follow on “anonymous” requests for the other pages (to prevent re-sending the credentials on every request). That also did not work. The initial login page fails, even with the same credentials that I use with a browser, and that I use successfully with the old style web query that I would like to retire.

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.