Site removal is the act of automatically extracting data, information or images from a website. Think of it as fully automatic copy and paste.
We write or use an application to access the websites we want and make a copy of the specific things we want from those sites. It’s much more accurate than downloading an entire web page.
Like any tool, web scraping can be used for good or bad. Some of the better reasons to remove the site are to rank it in a search engine based on its content, shopping, comparing prices or tracking stock market information. You can even use it as a research tool.
Table of Contents
How can I edit a web page using Excel?
Believe it or not, Excel has been able to extract data from web pages for a long time, at least since Excel 2003. It’s just that web scraping is something most people don’t think of, let alone using a spreadsheet program to do the job. But it’s surprisingly easy and powerful. Let’s learn how by creating a collection of Microsoft Office keyboard shortcuts.
Find the sites you want to scrape
The first thing we will do is find the specific sites we want to get information from. Go to the source and search at https://support.office.com/. We will be using the search term “frequently used keyboard shortcuts”. We can make it more specific by using the name of the specific application, like Outlook, Excel, Word, etc. You should bookmark the results page so that we can easily return to that page.
Click on the search result, “Shortcuts in Excel for Windows”. Once on that page, find the list of Excel versions and click Newer version. Now we are working on the latest and greatest.
We can go back to our search results page and open the results for all the other Office applications in their own tabs and bookmarks. It’s a good idea, even for this exercise. This is where most people would end up in collecting Office shortcuts, but not us. We’ll put them in Excel so we can do whatever we want with them, whenever we want.
Open Excel and Scrape
Open Excel and start a new workbook. Save the workbook as Office Shortcuts. If you have OneDrive, save it there to Auto save feature will work.
After the workbook is saved, click Data navigation.
In the ribbon of the Data tab, click From the web.
The From the web wizard window will open. This is where we put the web address or URL of the website we want to get data from. Switch to your web browser and Copy URL.
Paste the URL in URL
of the From Web wizard. We can choose to use this in Communication or Advanced method. Advanced Mode gives us more options on how to access data from the website. For this exercise, we only need Basic mode. Click ALRIGHT.
Now Excel will try to connect to the web page. This may take a few seconds. We should see a progress window, if available.
The Navigator
window will open and we will see the list of boards from the website on the left. When we select a board, we will see a table preview on the right. Please choose Commonly used keyboard shortcuts table.
We can click Web View to see the actual web page, if we need to find the table we want. Once we find it, we can click on it and it will be selected for import.
Now we click Tonnage
at the bottom of this window. There are other options that we can choose from, which are more complicated and beyond the scope of our first scraping. Just be aware that they are there. Excel’s web scraping capabilities are very powerful.
The web table will load in Excel in a few seconds. We will see the data on the left, where the number first is in the picture below. Numbers 2 Highlights Query
used to get data from the website. When we have multiple queries in a workbook, this is where we choose the one we need to use.
Note that the data is included in the spreadsheet as an Excel table. It is already set up so that we can filter or sort the data.
We can repeat this process for all other websites that have the Office shortcuts we want for Outlook, Word, Access, PowerPoint and any other Office application.
Keep the scraped data current in Excel
As a bonus for you, we will learn how to keep our scraped data fresh in Excel. This is a great way to illustrate how powerful Excel is at collecting data. Even with this, we’re just doing the most basic of things Excel can do.
For this example, let’s use a stock information website like https://www.cnbc.com/stocks/.
Going through what we did earlier, copy and paste the new URL from the address bar.
You will go to the Navigator window and see the available tables. Choose from Major US Stock Indices.
After the data is collected, we will see the following spreadsheet.
On the right, we see the query for the Major US Stock Indices. Select it so it’s highlighted. Make sure we’re in Table Tools tab and in Design
area. Then click the down arrow Refresh. Then click Connection properties.
inside Query properties window, under Using
, we can control how this information refreshes. We can set a specific time to refresh, or to refresh when we next open the workbook, or to refresh in the background, or any combination thereof. After we choose what we need, click ALRIGHT to close the window and continue.
That’s it! You can now track stock prices, sports scores, or any other frequently changing data from an Excel spreadsheet. If you are proficient with Excel equations and functions, you can do almost anything you want with the data.
Maybe trying to spot stock trends, run a fantasy sports pool at work, or maybe just keep an eye on the weather. Who knows? Your imagination and the data available on the Internet is the only limit.
