馃攳
Easily Import Data from Web to Excel (2 Practical Examples) - YouTube
Channel: Leila Gharani
[0]
Today, we're going to import
data from the web to Excel.
[4]
We're going to be using get and transform
[7]
from the data tab.
[8]
This used to be the Power Query add-in,
[11]
but since Excel 2016,
it's a part of Excel.
[15]
It's a super useful feature that connects
[17]
to different data sources,
transform and loads data for you
[22]
so that you can do further analysis on it.
[24]
If this sounds complicated
to you, don't worry.
[28]
All of this is going to
be done with a few clicks,
[30]
and the best part is that
once we have it set up,
[33]
the data can be refreshed
with just a click of a button.
[37]
Let's get to it.
[38]
(upbeat music)
[42]
Let's do two examples.
[44]
In the first case, we're
going to import spot prices
[46]
for petroleum from a web page into Excel.
[49]
In the second case, we're going
to import the weather forecast
[52]
for the next 10 days.
[54]
Now, we're going to do
it in a dynamic way,
[56]
so the data that we import
is going to be connected
[60]
to the original web page.
[61]
So whenever we want to get
the newest information,
[64]
all we have to do is refresh our table.
[68]
Let's start with spot prices.
[70]
First step is to find the website
[72]
from which you want to
import the data from.
[75]
In this case, I'm going to import the data
[77]
from the US Energy
Information Administration.
[80]
Let's go to sources,
petroleum and other liquids.
[85]
That's the information that I want.
[87]
Now, obviously, one way
of getting this into Excel
[89]
is to just copy this,
press Ctrl+C, go to Excel,
[94]
press Ctrl+V, and if you're lucky,
[97]
everything is going to be
recognized properly by Excel.
[100]
The downside of this is it's
not connected to that web page.
[104]
So if you need this information
on a continuous basis,
[107]
you have to repeat the steps.
[109]
What we're going to do instead
is to connect this information
[113]
to that page so that all we need to do
[115]
is open our Excel file
and refresh our data.
[119]
So let's just remove
this, let's go and grab
[122]
the URL of that page.
[124]
Let's go back to Excel,
go to the data tab,
[127]
and get the data from web.
[130]
Paste the URL, click on OK.
[133]
This is going to launch the query editor,
[135]
and it's going to try to
find the tables on this page.
[139]
Now, we have the ability to switch
[141]
from table view to the web page view,
[143]
just to make sure that we
are on the correct page.
[146]
Now, you can switch back by
selecting table view here.
[149]
This looks like the
table I want to import.
[153]
If I'm happy with this
and I don't want to make
[155]
any additional changes on this
or add any transformation,
[159]
I can load this directly to my page.
[162]
And if I want to load
it to my existing page,
[164]
I'm going to go with load to
and select existing worksheet,
[169]
click on OK.
[170]
This is going to create
a query to that page.
[173]
We can see the query right here.
[175]
When I hover over it, we can
see when it was last refreshed
[179]
and the data source
that it's connected to.
[182]
This all looks good,
but let's say the fact
[185]
that these are empty is
going to give me problems
[188]
in my further analysis, so
let's assume I have formulas
[191]
that are referencing this and
I need these to be filled.
[195]
I can go and add a
transformation step to this.
[198]
So let's launch Power Query again,
[200]
I'm going to double
click on the query here
[203]
and add a step to fill these down.
[207]
So let's go to transform.
[209]
Up here, we can see fill and fill down.
[212]
Nothing happened when I clicked on this,
[215]
although I can see the step here,
[217]
but I don't see these
being filled down here.
[219]
So it looks like these are not recognized
[222]
as empty by Power Query.
[225]
So I'm going to add a step
before I fill this down,
[228]
and I'm going to make sure
that these are really empty.
[231]
So with this highlighted,
I'm going to go back
[233]
to the home tab, click on replace values.
[237]
It asked me if I'm sure I want
to insert a step in between.
[240]
Yes, I do, so go with insert.
[242]
The value to find is just an empty string,
[245]
and the value I want to
replace it with is null.
[250]
Now, this is seen by Power
Query as really empty.
[254]
So when I go to the fill down step,
[256]
I can see everything being
filled down correctly.
[259]
So I'm going to update this name,
[261]
call it SpotPrices, press
Enter, close and load.
[265]
Now my query is updated.
[267]
Every time I want to
grab the new information,
[270]
all I have to do is go up here and refresh
[274]
or right mouse click
and refresh the query.
[277]
But you also have some query options.
[280]
If you click on the drop down arrow here
[282]
and go to connection properties,
[284]
you can refresh this every 60 minutes
[288]
or adjust the minutes from here.
[289]
You can also refresh data
when you open the file.
[294]
So I'm just going to remove these
in this case and go with OK.
[297]
That was really easy, right?
[299]
So now let's take a look at importing
[301]
the weather forecast for the next 10 days.
[304]
Imagine you get a new job at a hotel
[306]
and your boss asks you to print out
[309]
the weather forecast for the next 10 days.
[312]
And this is something you
need updated every day
[314]
so that the tourists that are there
[316]
can always take a look
at the weather forecast
[319]
for the next 10 days.
[320]
So this is what you do.
[322]
You come and you just
Google weather forecast
[325]
for New York City, so that's
where you're working, for,
[330]
let's do 10 days.
[331]
We get weather.com.
[334]
This looks good, that's the
information I want to get
[337]
in Excel and have it
connected to this page.
[340]
So now that I have the URL, I'm
going to copy it, go to data,
[346]
from web, and paste our
URL in here, click on OK.
[351]
It's creating a connection to weather.com,
[354]
it's recognized the different
tables we have here.
[358]
That's the one we want.
[360]
Now, there is some information in here
[362]
that I don't need, like these ones.
[365]
And you can see that the column titles,
[368]
the headers, have shifted,
because description
[371]
should belong here and high
and low should be for this one.
[375]
So I'm just going to go
and transform the data
[378]
before I load it to my Excel sheet.
[381]
Let's first off delete what we don't need
[384]
and remove the last three columns here.
[388]
The only columns that I'm
interested in is the day,
[392]
which is the first one, then
the description, and high/low.
[397]
Now, these are not seen as
numbers, but in this case,
[400]
that's fine, because
this is just going to be
[402]
a report that I'm going
to show the tourists.
[406]
I'm not going to perform any
mathematical operations on this.
[410]
But if I wanted to do that, so let's say
[412]
if I wanted to calculate
the Celsius values,
[415]
I have to make sure these are recognized
[417]
as numbers before I do
that transformation.
[420]
So in this case, I'm fine with Fahrenheit
[423]
and I'll just go with this
dataset, close and load to.
[427]
Click on existing worksheet, click on OK.
[430]
So that's the weather
forecast for the next 10 days.
[434]
Now, all I have to do every day
[435]
is just to refresh this to
get the latest information.
[439]
But since I really want
to impress my boss,
[442]
I'm going to do one additional step.
[445]
I'm going to add an emoji to this so that,
[448]
whenever we see rain or shower,
we see an umbrella emoji
[454]
so the tourists know
they got to be equipped.
[458]
Now, this is something I learned
from Frederic and from Oz.
[462]
So there is a blog post
and a video on this.
[464]
I'm going to add the link to these
[467]
in the description of this video,
[468]
so check it out, they're really fun.
[470]
First step is to get
the emoji that I want.
[474]
I'm going to use the Windows emojis
[476]
by clicking on the Windows
button and the period.
[479]
Then let's just type in
rain, that's the one I want.
[483]
So let's just click away and copy this.
[486]
Okay, so don't forget to copy this
[488]
before you go to Power Query,
so Ctrl+C and press Enter.
[492]
Let's go back to our query,
[495]
so I'm just going to double click this.
[497]
Now, I just realized I forgot
to name this table, NYWeather,
[502]
and press Enter.
[503]
Now, the next step is
to add that rain emoji.
[506]
Wherever we have the word rain or shower,
[509]
it should show the umbrella here.
[511]
So let's add a column.
[513]
I'm going to go with
conditional column in this case.
[516]
Let's call this be equipped
[518]
and just go through the steps here.
[520]
If column name is description,
if this contains the word,
[527]
I have rain here, then I
want my umbrella emoji,
[531]
so I'm going to press
Ctrl+V, because remember,
[534]
I copied this before I came here.
[536]
Next one is else if description
contains the word shower,
[543]
I want to have my umbrella emoji as well.
[546]
Otherwise, I'm just going to go
with nothing and click on OK.
[551]
That adds the umbrellas here.
[553]
Drag it and bring it beside description.
[556]
These are my steps that are going to
[557]
be applied every single
time I refresh the data.
[562]
We're done, let's go
back, close and load this.
[565]
Okay, so that's my updated report.
[568]
Now, tomorrow, when I get to work.
[570]
(ticking)
[572]
All I have to do is open my Excel file
[575]
and refresh this sheet, and I have
[576]
the updated information in here.
[579]
I hope you found something new here.
[581]
If you like this video,
give it a thumbs up.
[584]
And don't forget to subscribe
if you haven't done so already
[588]
so that you can get updates
when I put out new videos here.
[592]
(rhythmic music)
Most Recent Videos:
You can go back to the homepage right here: Homepage