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


Today, we’re going to import
data from the web to Excel. We’re going to be using get and transform from the data tab. This used to be the Power Query add-in, but since Excel 2016,
it’s a part of Excel. It’s a super useful feature that connects to different data sources,
transform and loads data for you so that you can do further analysis on it. If this sounds complicated
to you, don’t worry. All of this is going to
be done with a few clicks, and the best part is that
once we have it set up, the data can be refreshed
with just a click of a button. Let’s get to it. (upbeat music) Let’s do two examples. In the first case, we’re
going to import spot prices for petroleum from a web page into Excel. In the second case, we’re going
to import the weather forecast for the next 10 days. Now, we’re going to do
it in a dynamic way, so the data that we import
is going to be connected to the original web page. So whenever we want to get
the newest information, all we have to do is refresh our table. Let’s start with spot prices. First step is to find the website from which you want to
import the data from. In this case, I’m going to import the data from the US Energy
Information Administration. Let’s go to sources,
petroleum and other liquids. That’s the information that I want. Now, obviously, one way
of getting this into Excel is to just copy this,
press Ctrl+C, go to Excel, press Ctrl+V, and if you’re lucky, everything is going to be
recognized properly by Excel. The downside of this is it’s
not connected to that web page. So if you need this information
on a continuous basis, you have to repeat the steps. What we’re going to do instead
is to connect this information to that page so that all we need to do is open our Excel file
and refresh our data. So let’s just remove
this, let’s go and grab the URL of that page. Let’s go back to Excel,
go to the data tab, and get the data from web. Paste the URL, click on OK. This is going to launch the query editor, and it’s going to try to
find the tables on this page. Now, we have the ability to switch from table view to the web page view, just to make sure that we
are on the correct page. Now, you can switch back by
selecting table view here. This looks like the
table I want to import. If I’m happy with this
and I don’t want to make any additional changes on this
or add any transformation, I can load this directly to my page. And if I want to load
it to my existing page, I’m going to go with load to
and select existing worksheet, click on OK. This is going to 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 refreshed and the data source
that it’s connected to. This all looks good,
but let’s say the fact that these are empty is
going to give me problems in my further analysis, so
let’s assume I have formulas that are referencing this and
I need these to be filled. I can go and add a
transformation step to this. So let’s launch Power Query again, I’m going to double
click on the query here and add a step to fill these down. So let’s go to transform. Up here, we can see fill and fill down. Nothing happened when I clicked on this, although I can see the step here, but I don’t see these
being filled down here. So it looks like these are not recognized as empty by Power Query. So I’m going to add a step
before I fill this down, and I’m going to make sure
that these are really empty. So with this highlighted,
I’m going to go back to the home tab, click on replace values. It asked me if I’m sure I want
to insert a step in between. Yes, I do, so go with insert. The value to find is just an empty string, and the value I want to
replace it with is null. Now, this is seen by Power
Query as really empty. So when I go to the fill down step, I can see everything being
filled down correctly. So I’m going to update this name, call it SpotPrices, press
Enter, close and load. Now my query is updated. Every time I want to
grab the new information, all I have to do is go up here and refresh or right mouse click
and refresh the query. But you also have some query options. If you click on the drop down arrow here and go to connection properties, you can refresh this every 60 minutes or adjust the minutes from here. You can also refresh data
when you open the file. So I’m just going to remove these
in this case and go with OK. That was really easy, right? So 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 out the weather forecast for the next 10 days. And this is something you
need updated every day so that the tourists that are there can always take a look
at the weather forecast for the next 10 days. So this is what you do. You come and you just
Google weather forecast for New York City, so that’s
where you’re working, for, let’s do 10 days. We get weather.com. This looks good, that’s the
information I want to get in Excel and have it
connected to this page. So now that I have the URL, I’m
going to copy it, go to data, from web, and paste our
URL in here, click on OK. It’s creating a connection to weather.com, it’s recognized the different
tables we have here. That’s the one we want. Now, there is some information in here that I don’t need, like these ones. And you can see that the column titles, the headers, have shifted,
because description should belong here and high
and low should be for this one. So I’m just going to go
and transform the data before I load it to my Excel sheet. Let’s first off delete what we don’t need and remove the last three columns here. The only columns that I’m
interested in is the day, which is the first one, then
the description, and high/low. Now, these are not seen as
numbers, but in this case, that’s fine, because
this is just going to be a report that I’m going
to show the tourists. I’m not going to perform any
mathematical operations on this. But if I wanted to do that, so let’s say if I wanted to calculate
the Celsius values, I have to make sure these are recognized as numbers before I do
that transformation. So in this case, I’m fine with Fahrenheit and I’ll just go with this
dataset, close and load to. Click on existing worksheet, click on OK. So that’s the weather
forecast for the next 10 days. Now, all I have to do every day is just to refresh this to
get the latest information. But since I really want
to impress my boss, I’m going to do one additional step. I’m going to add an emoji to this so that, whenever we see rain or shower,
we see an umbrella emoji so the tourists know
they got to be equipped. Now, this is something I learned
from Frederic and from Oz. So there is a blog post
and a video on this. I’m going to add the link to these in the description of this video, so check it out, they’re really fun. First step is to get
the emoji that I want. I’m going to use the Windows emojis by clicking on the Windows
button and the period. Then let’s just type in
rain, that’s the one I want. So let’s just click away and copy this. Okay, so 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’m just going to double click this. Now, I just realized 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, it should show the umbrella here. So let’s add a column. I’m going to go with
conditional column in this case. Let’s call this be equipped and just go through the steps here. If column name is description,
if this 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 one is else if description
contains the word shower, I want to have my umbrella emoji as well. Otherwise, I’m just going to go
with nothing and click on OK. That adds the umbrellas here. Drag it and bring it beside description. These are my steps that are going to be applied every single
time I refresh the data. We’re done, let’s go
back, close and load this. Okay, so that’s my updated report. Now, tomorrow, when I get to work. (ticking) All I have to do is open my Excel file and refresh this sheet, and I have the updated information in here. I hope you found something new here. If you like this video,
give it a thumbs up. And don’t forget to subscribe
if you haven’t done so already so that you can get updates
when I put out new videos here. (rhythmic music)

51 thoughts on “Easily Import Data from Web to Excel (2 Practical Examples)”

  1. Happy Thursday your highness
    I'm just reserving first comment 🤗🤗
    I can do that . But i will c ur way first. You always surprising me

  2. I started to get used on your vedios, thanks thanks thanks a lot.
    You are really great inspire for all content creators.

  3. One of my most important aspirations in 2019 is that you have a course in business intelligence tools that are as fat and complete as the VBA

  4. Great to see this feature used in a video Leila. So much more flexible than a simple copy and paste from the web….and all the reformatting that normally requires 👍💚

  5. Thanks for your wonderful and simple videos you make

    I have two questions:
    1) After you finished all your amendments in the Power query and before loading the data. Their where four options to load data, two of them where (Table) & (Create connection only), what is the difference between both?

    2) Is there a way to load data that is changing continuously second by second such as stock prices, in other words creating live connection?

  6. I like the emoji part. Would you be able to do a tutorial in the future explaining the 'Advanced' option? After you clicked on Get Data from Web, copied the url there are two options, basic and advanced. Great video as always. Thank you.

  7. Very nice & thank you! I think Microsoft should hire you to announce new Excel features, instead of their lengthy & boring Release Notes that nobody reads!

  8. This is really cool. Is the only way to know if a website can be used for this method is that the tables are in html format? Is there a repository of websites with different types of data like this to play around with?

  9. Best part of the video is when you said; "it is just part of Excel"! That seems to be our main job as Excel Trainers, to convince Excel users that Power Query is the greatest Excel Invention since the PivotTable : )

  10. Hi Leila, Great videos.

    I have a question pulling data from Yahoo Finance and hope you know the answer:

    I build the below link in a parameter table to pull data from Yahoo Finance from 01/01/2018 to 12/12/2019. The problem is the power query only pulls 6 months of data from yahoo finance. Hope you know why just 6 months of data instead of data from 01/01/2018 to 12/12/2019.

    https://finance.yahoo.com/quote/BAC/history?period1=1514793600&period2=1576137600&interval=1d&filter=history&frequency=1d

    Below is the parameter table.I use Macro to change the end date. For Yahoo, 86400 = 1 day. The below parameter table changes URL in power query so that the data is up to date.

    Index Description Period Values
    1 Start Day 1514793600 1/1/2018

    2 Increment 86400 1

    3 End Date 1576051200 12/11/2019

    6 Symbols BAC

    7 URL https://finance.yahoo.com/quote/BAC/history?period1=1514793600&period2=1576051200&interval=1d&filter=history&frequency=1d

  11. Leila Gharani, it is amazingly awesome to see the power of your presentation in the power query with emojis 🙂😀😊
    Thanks a bunch for this awesome tutorial

  12. Great Job, Leila! Another very helpful video!

    I think that – get data from web works with data that is put in an HTML table tag or it is passed in delimited format thru some API. Is there a way to make queries if the table is not automatically recognized as an HTML table. When the data is put in some sort of table which is put on div blocks for example?

  13. Thanks for the great video. The umbrella idea was great indeed. However, it doesn't look nice in Excel. Maybe there is a workaround to display emojis in a better way?

  14. Hi Leila!, thanks for this video, very amazing, and I have a question. What if I have to log in a website every day to download some data, for instance a bank account, How do I apply the web query there? Thanks a lot, this will help me a lot!

Leave a Reply

Your email address will not be published. Required fields are marked *