How to import data from a website into Excel (Scraping in Excel)

On this occasion we want to illustrate how import data from a website into Excelthanks to the tools for connecting to external data and the integration of power query, as a native option of excel, we can do our web scraping in excel.

Through the tools of external data connections, we can link and import any web content in table format, thus creating a direct link to the website. Which allows you to update information in real time (in the background) and be able to work with it in your Excel spreadsheet.

All this can be done thanks to the built-in plugin from the 2016 version of Excel PowerQuery, which allows you to query multiple sources. In this case we will import data from a website into Excel so that it will allow us to transform the information if needed or simply load it into Excel in table format.

This process is known in data processing as ETL (Extract: extract. – Transform: transform – Load: load).

You may also be interested in: Gantt chart in Excel

Import the data into Excel

To import data, Excel since its 2016 version puts a tab called get and transform in the data tab, this is what in previous versions was very similar to a PowerQuery add-on.

For this case, we want to show you how Import data from a website into Excelthat is, create a connection to a table with records from a web page. What the Excel spreadsheet will do is create a connection to that website and update that table whenever we want with the information that is published online every day or every X time.

We will refer to a website that provides us with the price of European Brent oil and the respective daily closes.

So, assuming we are working with this data and we need to know the value of the close on a daily basis, so that I don't have to go to the web every day and look up the value and manually enter it into my Excel file, we can create a connection to bring us the table and update it daily or periodically as appropriate.

Automate and create a connection to import data

The first thing we will do is copy the website url and go in Excel to the Data tab - get and transform - new query - from other sources - from the web

We are waiting for the Framework to load!

We get two options in a dialog that tells us whether we want to connect in basic or advanced use. For the example we will leave it basic and paste the address into the URL address field and accept, wait for the web page to connect to our Excel file.

Here we open a dialog called browser which offers us two views, table view and web view. We go to table view and look for the table we want to integrate into Excel, click on load and load.

We select to be loaded as a table in the existing spreadsheet and uncheck the box (Add this data to the data model) and click load.

We have now established the connection and uploaded the information

Import data from a website into Excel and refresh it

When we stop inside the loaded records, a query tool is enabled at the top, if we click on query, we have a number of options to do for that query, for example we can update it again, we can edit, delete and execute a other set of options within this option.

The connection that loads the web into the table or Excel file has already been made. Hopefully this little Excel tutorial be very helpful to you. If you have friends or colleagues that you think of the illustration of import data from a website into Excel Share them or leave us your comments or observations.