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

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)

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

  1. 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 ??

  2. 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?

  3. 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.

  4. 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!

  5. 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?

  6. 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 : )

  7. 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

  8. 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

  9. 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?

  10. 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?

  11. 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!

  12. " … Note: Importing web data with Power Query this way requires the tables on the web pages to be in HTML format – otherwise they're not recognized in the Navigator view. …"
    Question: How can we tell if a 'table' is in HTML format?"

    Thank you.
    PS
    Great tip … again

  13. Ola Leila. Adoro os seus videos. Estou seguindo e posso lhe dizer que ja aprendi bastante e tenho usado alguns dos ensinamentos aqui no meu trabalho. Bom trabalho. Abracos.

  14. Need to know IF formula like in a Cell if the Amount is in Green Color text, then Add or Amount is in Red color text then subtract? Do you have in that video?

  15. How can you set up a spreadsheet to automatically import data from a website that you have to log into first when you hit refresh?

  16. "or use bing" – I like your sense of humor. Nice subtle joke.

    Seriously though, great video as usual. Great content.

  17. What is the "Windows Button and The Period" mentioned at 7:56?
    Thanks, in advance, to anyone who can point me to what I missed at that moment.

  18. Great tutorial and just what I needed! However I am stumped on how to get back in to edit the Conditional Column where we just added the umbrella. I now want to add other icons depending on other key words, but I cannot see how to get back in to the same column we already created. Please lend a helping hand. Thank you!

  19. Excellent Video. A question though. I have Excel for Mac and there appears to be no DATA>GET DATA >FROM WEB. Mac version instead has GET EXTERNAL DATA and then RUN WEB QUERY. How do I get this done on Mac. Thanks.

  20. I wonder if you can use NOAA website & URL to get the weather data? There is a place on the NOAA website to get your local weather forecast. On the top right hand side click on Find Your Local Weather, then type in your City State & Zip Code.

  21. Nice Info.

    however i want to know the process of copying my Financial info from the web, which requires a Log-In procedure. Can u make a separate video on that

  22. Nice! As always, you're full of brilliance.

    Question: Can you share with us some tips on how we can select a specific date such as MTD Report from the examples you've shown us?

  23. Hi Leila, thanks for this. However, I was curious, how we can refresh data to be added to a different column at frequent intervals, an example would be for stock price movements over time and add a chart to that.

  24. Awesome.. continue the good work ! i tried this with https://www.nseindia.com/live_market/dynaContent/live_analysis/top_gainers_losers.htm but generating error – "Underlying connection was closed:" An unexpected error occurred on a receive."
    Also in the example – is "Null" considered as a keyword like any other programming language ( how is "Null" working with "Fill down"

  25. If the external data source requires a password you can add that in "Data Source Settings" – check out this page: https://support.office.com/en-us/article/data-source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380ca9 (If you don't see the option in the Ribbon, go to Power Query, File, Options and Settings, Data Source Settings – Edit Permissions – Edit – Basic – The input username and password. )

  26. Beautifully presented.
    Is there a technique for dealing with multiple pages within a page—ie those buttons you click '2', '3', 'Next'—so that Refresh will pull them all in?
    My current need has a variable number of pages, some days 10, some days 15…

  27. Dear Leila thanks for all you excel VDOs… Please help me to automate some excel work … I want to Run a macro after or whenever query auto-refreshes by Refresh Time … is it possible. PLEASE REPLY.

  28. Regarding to your tutorial I have a challenge that no body solved it.
    I want to get data from bellow site for period of time but it seems since its set in javascript it doesn't work with power query.
    https://sanarate.ir/Default.aspx

    Do you have any solution?

  29. Very informative, Leila. Well done. I've been using PQ to get data from the web in all kinds of ways. I regret to report that not all websites cooperate, owing probably to the way they're coded. This is a particularly troublesome one: https://www.nhl.com/standings/2019/league. Even when you finally get the Tables to list in websites like that one, the next time the query is refreshed, it fails.

  30. I opened the Emog (Window Key + the period) in the area where you pasted the umbrella Equipped and selected all the different symbols I wanted. Then Cut and pasted them in the other cells. Thanls for the fun weather program.

  31. Thank you, Leila! could you help me with a problem – at the very first step – getting data from the web, didn't open a dialog box with URL only, but New Web Query , and Script Error.

  32. my Excel 2016 "From Web" does not look anything like this demonstration – not even close enough to try to adapt it to my Excel features

  33. Hi Leila, video is a great help to all . I want to know is there a video with same topic where i want query to pick data from a condition.. like i a office web portal with employee details and i want query to pick details of a particular employee by a date range or any thing like that .. Thanks for your help

  34. Excellent idea and i have now created one for the weather, i am attempting to use our companies website that was created using wordpress and when i load the url it will not load the tables and gives me the following message "table highlighting is disabled because this page uses internet explorer's compatibility mode" the url i am using is https://www.bachmann.co.uk/stockists/ any help or assistance would be appreciated – thank you

  35. great job i like it .i have a question what if the URL that you getting data from is website that you need to access to it with a login and password this refreshing things will not work .

  36. Hi Leila, great video! When I import from the web, I find that some dates don't come across formatted as dates and when I try to change the date it just shows an error. However, what is odd is that this just seems to happen on some records in the table. Any ideas how I can correct this?

Leave a Reply

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