Easily Import Data from Web to Excel (2 Practical Examples)

Today we are going to

import

data

from the web to Excel. We will use get and transform from the

data

tab. It used to be the Power Query add-in, but since Excel 2016, it is part of Excel. It is a super useful feature that connects to different data sources, transforms and loads data so you can perform further analysis on it. If this seems complicated to you, don't worry. All of this will be done with a few clicks, and the best part is that once we have it set up, the data can be updated with the click of a button.

Let's do it. (upbeat music) Let's do two

examples

. In the first case, we will

import

spot oil prices from a web page to Excel. In the second case we are going to import the weather forecast for the next 10 days. Now we will do it dynamically, so the data we import will be connected to the original web page. So whenever we want to get the latest information, all we have to do is update our table. Let's start with spot prices. The first step is to find the website from which you want to import the data. In this case, I'm going to import the data from the US Energy Information Administration.

Let's go to the sources, oil and other liquids. That's the information I want. Now, obviously, one way to get this into Excel is to just copy it, press Ctrl+C, go to Excel, press Ctrl+V, and if you're lucky, Excel will recognize everything correctly. The downside to this is that you are not connected to that web page. So if you need this information continuously, you must repeat the steps. What we'll do instead is connect this information to that page so that all we have to do is open our Excel file and update our data. So let's remove this and grab the URL of that page.

Let's go back to Excel, go to the data tab and get the data from the web. Paste the URL, click OK. This will start the query editor and try to find the tables on this page. Now, we have the ability to switch from table view to web page view, just to make sure we're on the right page. Now you can change back by selecting the table view here. This looks like the table I want to import. If I'm happy with this and don't want to make any additional changes or add any transformations, I can upload it directly to my page.

And if I want to load it on my existing page, I'll go to load and select the existing worksheet, click OK. This will create a query to that page. We can see the query right here. When I hover over it, we can see when it was last updated and the data source it is connected to. This all looks good, but let's say the fact that they are empty will give me problems in my later analysis, so let's say I have formulas that reference this and I need them to be filled. I can go and add a transform step to this.

So, let's start Power Query again. I'm going to double click on the query here and add a step to complete them. So let's transform. Up here we can see it filling up and filling up. Nothing happened when I clicked on this, although I can see the step here, but I don't see them completing here. Therefore, it seems that Power Query does not recognize them as empty. So I'm going to add a step before I complete this and make sure that they are actually empty. So with this highlighted, I'll go back to the home tab and click on replace values.

He asked me if I'm sure I want to insert an intermediate step. Yes, I do, so I'm going with the insert. The value to look for is just an empty string and the value I want to replace it with is null. Now, Power Query considers this to be actually empty. So when I go to the complete step, I can see that everything is completed correctly. So I'm going to update this name, call it SpotPrices, hit Enter, close and load. Now my query is updated. Every time I want to get new information, all I have to do is come up here and refresh or right click and refresh the query.

But you also have some consultation options. If you click the drop down arrow here and go to connection properties, you can update this every 60 minutes or adjust the minutes from here. You can also refresh the data by opening the file. So in this case I'll delete them and accept. That was very easy, right? Now let's take a look at importing the weather forecast for the next 10 days. Imagine you get a new job at a hotel and your boss asks you to print the weather forecast for the next 10 days. And this is something that you should update every day so that tourists who are there can always check the weather forecast for the next 10 days.

So this is what you do. You come and Google the weather forecast for New York City, so that's where you're working, for 10 days. We get clima.com. This looks good, that's the information I want to get into Excel and connect it to this page. Now that I have the URL, I'll copy it, go to data, from web, paste our URL here and click OK. It's creating a connection with Weather.com, recognize the different tables we have here. That's the one we want. Now, there is information here that I don't need, like these. And you can see that the column titles, the headings, have changed, because the description should belong here and high and low should be for this one.

So I'm going to transform the data before loading it into my Excel sheet. First, let's delete what we don't need and delete the last three columns here. The only columns that interest me are the day, which is the first, then the description and high/low. Now, these don't look like numbers, but in this case, that's okay, because this will simply be a report that I show to the tourists. I'm not going to do any math on this. But if I wanted to do that, let's say if I wanted to calculate Celsius values, I have to make sure that they are recognized as numbers before I do that transformation.

So in this case, I'm fine with Fahrenheit and I'm just going to use this data set and close it and load it. Click on the existing worksheet, click OK. This is the weather forecast for the next 10 days. Now all I have to do every day is update this for the latest information. But since I really want to impress my boss, I'm going to take it an extra step. I'm going to add an emoji to this so that every time we see rain or showers, we see an umbrella emoji so tourists know to be equipped. This is something I learned from Frederic and Oz.

So there is a blog post and video about this. I'm going to add the link to these in the description of this video, so check them out, they're really fun. The first step is to get the emoji I want. I'm going to use the Windows emojis by clicking the Windows button and dot. So let's write rain, that's the one I want. So let's just click and copy this. Well, don't forget to copy this before you go to Power Query, so Ctrl+C and press Enter. Let's go back to our query, so I'll double click on it.

Now, I just realized that I forgot to name this table, NYWeather, and press Enter. Now, the next step is to add that rain emoji. Wherever we have the word rain or shower, the umbrella should be shown here. So let's add a column. In this case, I'm going to use the conditional column. Let's call this being equipped and just follow the steps here. If the column name is description, if it contains the word, I have rain here, then I want my umbrella emoji, so I'm going to press Ctrl+V, because remember, I copied this before I came here.

Next up is: If the description contains the word shower, I also want to have my umbrella emoji. Otherwise I'll be left with nothing and click OK. That adds up to the umbrellas here. Drag and drop it next to the description. These are my steps that will be applied every time I update the data. We're done, let's go back and close and load this. Well, that's my updated report. Now, tomorrow, when I get to work. (checking) All I have to do is open my Excel file and update this sheet, and I have the updated information here. I hope you found something new here.

If you like this video, give it a like. And don't forget to subscribe if you haven't already so you can receive updates when I post new videos here. (rhythmic music)