Say goodbye to copy and pasting. Connecting Excel to website data
There are times when having live data in Excel can be really useful. You may need the latest currency exchange rates, monitor your stocks, track house prices, the possibilities are endless.
Once you have established the data links you can refresh the data, also Excel will check to see if the data on the website has been updated, if so it will refresh to ensure you have the latest data.
Here we will look at how we can set up Excel to connect to a live website and it’s data. As the UK general election is just around the corner we have decided this will make a good example.
The worksheet we will create will be linked to the following website which is tracking the “most seats betting odds” for the 2015 UK general election.
http://www.oddschecker.com/politics/british-politics/next-uk-general-election/most-seats
To create a link to a website from Excel click the Data tab and in the Get External Data group click From Web.
The Excel web browser will open. In Address enter the website address and press GO.
In the Excel web browser you have to find the table you want to link to Excel. Each table is represented by a yellow box with a black arrow inside. If you hover over the arrow Excel will put a frame around the table to show which data the arrow represents. Click on the arrow and the arrow will change to a tick. Click Import in the lower right corner.
The below dialogue box will open. Tell Excel where you want the data and click Properties.
You can now tell Excel how often you want to refresh the data under Refresh Control. You can change more properties if you so wish. When you have finished setting properties click OK and Excel will import the data.
The example below is the final result. We have worked with some formatting and created a chart. Now every time the odds change our chart and worksheet will update on refresh.
As we mentioned earlier, this linking web data to Excel has a wide range of applications and should hopefully make copying and pasting website data a thing of the past!