Easily Import Data from Web to Excel (2 Practical Examples)
Easily Import Data from Web to Excel (2 Practical Examples)
Complete Excel Power Query course: https://courses.xelplus.com/p/excel-power-query
*** Note: On XelPlus you will get additional bonuses that are not available on other platforms. Check the course landing page for more info.
You’d like to bring data from a web page into Excel? Instead of copying and pasting, use “Get and Transform” (Power Query) from the “Data” tab. This way you can import website data properly into Excel. Once you create a web query you can automatically get the latest data in Excel. You just have to refresh your query and update the query settings so it runs a refresh at specific intervals defined by you.
Getting data from a web page has never been easier. Just Open Excel, go to the “Data” tab and click on “From Web”. Enter the web address of the external page you’d like to import. Power Query opens and tries to find the html tables on that web page. Select the correct table and transform your data. This step gives you the ability to customize your output. You can for example, fill in the blanks by automatically copying down the top values. You can remove the columns you don’t need and clean up the data. Once done, you can view the data in an Excel table, as an Excel Pivot Table or create a connection to the query.
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.
00:00 How to Import Data from Websites to Excel
01:15 Importing Crude Oil Spot Prices to Excel
04:59 Getting the Latest Weather Data into Excel
07:25 Bonus – Using Emojis in Power Query
______ Check out _______
Frédéric Le Guen’s blog post on adding emojis to your reports: https://www.excel-exercise.com/add-emojis-to-your-charts-formulas/
Oz’s video – Emojis, Excel, Power Query & Dynamic Arrays: https://youtu.be/c-tnA1zfrzs
⯆ DOWNLOAD the workbook (scroll to the bottom of the blog post): https://www.xelplus.com/import-data-from-web-to-excel/
Check out the Power Query Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
Thanks!
Thanks!
Hey I have a question If I data scrape from a site that is a paid subscription when the subscription ended does the data still save in your excel or will it say #ref
Thank you Leila!
Thanks for the tip, Leila. It’s quite helpful indeed. Quick question, I tried copying data from a website and pasting them in excel but it didn’t paste in the right order (i.e, the data on the website were organized in a 4-column table but when I copied and pasted in excel, it pasted everything in one column) how can I organize the data and sort them in the right format/ columns given the large set of data? Would appreciate your input.
How can I export data from website by using MS excel 2007?
I’ve tried this but some websites doesn’t show the table on excel. How can I import a table from those websites that doesn’t show the table in excel?
Thanks!
Mind = blown
I used to use the currently known as legacy and it is not loading the page correctly also the now query table is not loading the table correctly. the only option left is TEXT or CSV view for HTML page and it is nightmare to extract the data from
Excatly what I needed, thank you.
Maam ur presentation was awesome 👌 really a useful one
hi i am facing issue data fetch in yahoo finance page to get data con you guide me
Hi Leila Thanks for this amazing video its really very easy to learn things from you, your explanations are simple to learn , can you help with the query, I want not to replace the previous data but new data to be added below the previous data, as I want to create a repository of daily price movement which further I want to connect to Power bi.
Hi Leila I am trying to get data from Yahoo finance but unable to get table
When i want to import popup menu doesn’t’ show. What should i do. Direct import. Please help
You are realy great. You saved my day and job. Thanks a lot.
Hi Leila, You videos are quite useful. I wanted to know is there any way of scapping data from websites which have image verification process.
But what if the data or value I need from a website isn’t in a table?? 😕
I’m trying to important the stock price of a stock in yahoo finance, but since it’s only shown at the top outside of a table, excel doesn’t recognize it. That’s really disappointing to be honest…
Amazing….
I was given such task before which I couldn’t… You just made my day
Thank you Leila
Can we change url dynamically like for 2021 and 2022 there are tow diff links. When i open table it replace with latest year data not old year data. What is the tips.
Good feature to know. Nice video. When I tried to use am getting some Javascipt error. Is there any pre-requite to take care before using this feature to extract data from web.
awesome! love it!
I really like this solution, what happen if the web need credentials to access it?
The problem is sites nowadays have those cookies popups and I just get that. how to bypass it
Excellent 👍
Can you do this on iPhone?
This really helpful 👏
‘Or use Bing’
Hi Leila thank you for your videos, it has become a go to ressource for me. I work in an organisation where most are bilangual. One of theese languages are kaalalisut (greenlandic) spoken by very few people (worldwide). There is no google translate (for obviois reasons) but there are online ressources. Would it be possible to "livetranslating" a spreadsheet, by scraping the dictionary, but as the search result?
THIS IS ONE OF THE BEST VIDEOS I´VE EVER SEEN ABOUT EXCEL POWER QUERY. THANKS A LOT.
Very useful…Thanks a lot.
Good day Leila. Thanks for this wonderful video. Can I ask if it’s possible to import single data thru web query to Excel? and How?
Awesome!
Hai Leila can you make tutorial Excel with database MySQL so user can take data by Primary key?, thanks
How wold you create an order processing status spreadsheet ?
Great! But can I do something similar on sites that need to log in and navigate to the desired location, besides inserting parameters such as date?
#fromBrazil
Thanks Leila, How can import the entire data from database table with web URL link. The web page usually load few rows at a time and with your way, it only import the 1st page.
this is Brilliant!! I had no clue that so much power was avail in Excel for web interfacing.
*THANK YOU* *THANK YOU* *(NEW SUB)*
In Excel – Page Layout, – Page Setup – Sheet -Print Titel – Rows to repeat at top – I Want Row No. 1 & 7 & 8 witll Page header too alll Pages How to Set up. ???
Hello Leila… I am working to get webpage data in excel; however, do not see the option "Get Data from Web" in my Excel (working on macOS).
Request your help running same thing on macOS please.
Very enjoyable and user friendly
Hi I needed to give an array which is dependent on another Sheet- lets say Sheet1 B10 & C10 Value. Example- Instead using =Sum(A1:A300) formulae I want to use =Sum(A&’Sheet1!’B10:A&’Sheet1!’C10) kind of formulae but it’s not working!! any help regarding the same will be appreciated
Hi Leila, thank you for the video. I am wondering if you have any video on hot to connect a webpage with login and then pass parameter to get the data.
Asalam’alaykum! as always helpful
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. )
Hello Leila,
Thank you for your video, but i have a small question. I did all of the above and everything was working perfectly fine for a few weeks up until now.
The data is not getting updated anymore and i’m receiving this message: "The internet site reports that a connection was established but the data is not available".
Could this problem be from the website ? I did a lot of research on this issue but i couldn’t find a solution. Please help!! 🙂
Leila, I admire you! You are an outstanding & beautiful person. Very impressive!
Can we do the same with twitter?
I need to import the stats of some accounts, how can I do that?
QUESTION: You like a Power Query but are asked for an additional item by boss….. Can we go from current form to adding fields?