Semalt Expert: Downloading Data From A Website To Excel Automatically
The Internet contains tons of useful information, but sometimes that information must somehow be stored and processed. For these purposes, it is convenient to use Microsoft Excel spreadsheets. However, the statistical data from different sites can not only be collected manually but automatically as well. For example, you need to save fresh data from dozens of sites. Of course, it takes a long time to save this data manually, so you can try to use the automatic data import tool in Excel. Why "you can try"? It means that not all sites allow you to import data automatically using this tool. I am going to explain you why later. But still, it can help in most cases.
Let's Get Started
The automatic data import tool is located in "Data" tab. Here select "From Web" (in some versions of Excel you need to click on "Get External Data" first).
It opened up "New Web Query" where you need to write an address of the website page you want to download the data from and wait a few seconds for it to load in the same window.
We will extract data from website soccerstats.com as an example. I'm sure many of you enjoy soccer and would like to collect up-to-date data about soccer leagues around the world in a table. There are loads of useful statistics but let's start with scores table of Spain La Liga.
You can see lots of arrows in yellow squares showing up. They are pointing at the elements you can extract to Excel table. Click on the arrow pointing to the element you need. Note that you can select more than one table at once. What we need is the score table.
Then click on "Import" in right bottom corner.
It will ask "where do you want to put the data". You can type cell name you need or just click "OK," and it will show up starting from the first cell.
And here's the result – all the information is now in our Excel table.
This data is still static while we need it to be updated every now and then. So, what you can do is refresh data manually by clicking "Refresh all."
But that's not what we're here for, right? Clicking it every time can be quite annoying if you need constantly updating information like, for example, currency rates. Now click on "Properties" on "Connections" tab. Here you can make tables automatically refresh – it can update all data with interval or every time you'll open the file.
Why It Won't Work Sometimes
A few words about why the automatic data import tool might not work. You've opened the web page in Excel and found the data presented in the form of a table, but the arrows that allow you to mark them for import do not appear next to them. Why? About a decade ago all the tabular data on websites appeared in the form of special markup - tags TABLE. Recognizing such table was a piece of cake for a robot. But over time methods of presenting data on web pages have changed - data itself and its display were separated. So when you look at a web page and see data in tables, you can't be certain that this data has a table structure in code. In some situations, the import wizard is simply not able to recognize the presence of the table by analyzing the source code of current web page.
Conclusion
Congratulations! Now you can easily download data from website using Excel into a table and make it refresh automatically. This simple tool will help to ease your life and relieve unnecessary copy-pasting.
Post a comment