Importing data from the web into Microsoft Excel can be used for a range of reasons. One of the reasons why I do this is for Fantasy Football data analysis. I can create a connection from a website that updates player stats every week and establishes a relationship with my excel document. By doing that, I can update the data into a pivot table and start to project out players weekly. By having this connection, I will save time in the long run as I will have the data already updated once I go into the spreadsheet. This is the website I will use in this tutorial below. Let’s get started.
Create the Connection Between Excel and the Website
Within Excel, you will need to head over to the data tab and click on from the web. From there, you will see a screenshot that will allow you to put in the website URL you want to connect to. Once you put in the URL you want to connect, you will need to click on the import button. For this example, I am going to use this website to connect my spreadsheet to.
As a note, you will get a few error script messages within Excel. Just keep hitting OK, and you should be good to go. If you follow getting a never-ending error script message, you can merely close excel and repeat the steps above. That should solve the problem if it does not go away on the first pass.
Once you are done with the script error messages, you will see a picture like this below. There will be a yellow/orange arrow boxes to all the aspects of the website that you are trying to connect to. Just click on the table that you want to import. You will see the box will turn into a check mark and turn into a blue box. See below to see how I only want to introduce the player information.
Check the Connection
Once you clicked on the import button, Excel will then ask you what cell you want to import the data in. Once you choose the cell, the connection will drop in all the data that you wanted to reference into excel. Here is a picture below showing all the data from the website.
I included a screenshot of how to test the connection. To do this, you need to click on data and links. From there, you will be brought to the connection properties tab which will give you many options on how you want the connection to work. If you click on the definition tab, you will be brought to a screen that shows where the relationship comes from. You will see the website that is connected to your spreadsheet.
How to Update Your Spreadsheet Dynamically
You can have the data refresh every 60 seconds (or another given time) if this is a dynamic website that is getting updated in real-time. This can be great if you are trying to get real-time data updated into excel as it comes in. You can also choose the settings to upgrade every time you click the refresh all button. One example of why you would want to do this is if you are tracking fantasy football in real-time. Perhaps you want players stats to be updating in real-time (or when the website is updated), so you can choose this option.
Conclusion
Creating a connection from a website into excel will save you time. Instead of going to a site and copying and pasting the data into excel, you can have the connection do that automatically for you. You can even take this a step further and create a pivot table or have IF statements run to get fast analysis on whatever you want to track. Depending on your connection and how often you want the results to come in, you can have a lot of saved data. Here are the steps on how to make excel document smaller and run faster.