AWESOME Excel trick to scrape data from web automatically

Thank you BrightData for sponsoring this video 😍

~
Ever wanted to gather some data from web and use it for analysis? You can use Excel's Power Query to setup and automate web scraping easily. In this tutorial, let's look at how we can combine US state population data with Chocolate Sales data in Excel.

📗 Sample File:
=============
Practice the steps by using the Wikipedia link:

⏱Video Topics:
=============
0:00 - Web Scraping data to Excel - The problem
1:04 - URL based data Extraction with Power Query
3:40 - Cleaning up the data (Transforming with PQ)
5:54 - Loading data to Excel
6:20 - The problems with Power Query method & solution

💡BRIGHTDATA:
=============

If you have a more complex data collection need, then I highly recommend using BrightData. Using their tools, you can automate data collection, clean-up and archival for all situations. If you use my link below, you get a FREE DEMO & $250 matching credit.

~

MORE Power Query 💻⚡:
======================

Power Query can greatly automate & simplify your data processes. If you are new to this revolutionary technology check out below videos.

🕑🕑🕑 1+hr deep videos:

⏳Short but powerful videos:

🎶Play Lists:

~

#Excel #webscraping

~
A data analyst was deathly afraid of spiders. He could never web scrape.

[jackielinde]

As someone from Arizona, I can say we definitely love chocolate here. Also, don't let kids sell boxes of chocolate, as they tend to eat more than they sell.

[arun.kumar.s]

I don't know how far will @Chandoo go to educate others, every time new things to learn. Be Awesome

[vijayarjunwadkar]

Great Idea! Thanks Chandoo for sharing this! 👍

[Azhar_Khan]

Thank you, sir, for keeping us up to date with time and skill development. I'm becoming awesome. Once again Thanks.

[NealBurkard-utoo]

For this specific example, you dont actually need to use scrapping. I forget the function name but you can classify cells, one common classification is geography. Type in the state, a drop down box appears to indicate the state. New york state, usa for example. Then you can use other cells to reference the geographical cell to list characteristics like population, total area, area by land, area by water, all differnt types of discriptors pertaining to new york state. So generate all 50 states, create the columns of criteria you want, do all the referencing for the to row. Then just drop down to fill the other 49

[Excelambda]

Great video!! Super cool tricks!!✌
For this particular case we can also use Data Types.
For example:
In H2 we write USA, with H2 selected, go to: Data tab, Data Types, Geography, select first result from side pane
In I2: =H2.Subdivisions
In J2: =H2.Subdivisions.Population
There are discrepancies though.
To visualize the differences, a formula: (Xlookup syntax technique when we deal with data types)
=LET(s, pop[State or territory], cp, pop[Census population], cp-XLOOKUP(s, H2.Subdivisions.Name, H2.Subdivisions.Population))

[jamesbautista]

Thank you for your video. May I ask if you can do video that automate data entry to chrome browser? Excel data to chrome browers like that without Third Party App? Thank you so much for your videos

[yafethtb]

AWESOME! I tried to a web that cannot be scraped via requests library in Python and I can get the table from its page!

[cynthiahoz]

I love dark chocolate as much as I love your videos❤️

[shanmugapriyayu]

Can you please show videos about educational data like mark analysis or teacher analysis in power bi..

[vgzfdnm]

Hello, merci pour la vidéo ! Il existe une méthode pour les sites qui requiert une connexion ?

[sharma]

Sir could you please guide me to create excel auto update table about bestselling books rating on specific category say personal finance. 🙏🏾

[amlevin]

Great vide! Is it possible to scrap if the number of pages is not fixed? And number of pages is available by link

[myheliography]

Hi Chandoo while i tried to scrape data from the website using power query the website first leads to Disclaimer (accept or reject), please guide how to skip it?

[gzfraud]

Hi Chandoo .... GREAT video.

QUESTION .... I scrape 10, 00+ webpages so this will really help. BUT if a URL is embedded in text on a webpage, PQ or BI won't extract the URL eg email address is embedded in the person's name. I've searched and can't find it. Any ideas?

[bhavikapawaskar]

I need help!!
I have one application which we normally used for filling information of working employees & that application generate UNIQUE ID. 2 Step :- I have to audit those case but at the same time wanna check multiple UNIQUE ID cases with different Name via excel using filtering data. So tell me how should i audit multiple data for UNIQUE ID.!!

[atlasgunther]

Anyone know of a detailed video to scrape sofascore's historical score data? I currently have to scroll manually to scrape it. TIA.

[angelvargas]

How come when I put in the URL from other websites the tables don't pop up but they do for wikipedia? Any suggestions?

[insidehead]

Hi could you help in how to connect with oracle 10g DB?

[niharraval]

Hi Chandoo, Thanks for the informative video once again. I have one question that how do big organization deal with their data for data visualization or daily analysis. Do they use any specific tools to scrap the data from their database. They are autogenerated or one person has to do it every day manually?