Get off the Web!

Did you know that you can get data from a website directly into your spreadsheet?

Excel has the ability to pull in data from many sources. Other Excel spreadsheets of course, but also Access databases and Word documents. Outside of the Office family Excel can connect for example, to Sage 50 or Sage MMS and get real time data from these popular accounts packages.

EOXL Quick Tip: If you do not know how to connect your Excel to a Sage product, or you are still struggling with Crystal reports, give us a quick call. We could save you a lot of time!

SP32-31052013165300Another great way to input data to Excel is direct from a page on a website. Not only can you get Excel to download web data, but it can regularly check the website and automatically update your spreadsheet to show the very latest figures. Opens loads of possibilities and a great tool for creating a company Dashboard.

Here’s how:

1) Ensure you are connected to the Web. Open a new Excel spreadsheet. Go to the Data tab and Click on the From Web icon in the Get External Data section. Shown n the above image.

SP32-130620131535002) A window entitled New Web Query opens in your spreadsheet showing your internet browser — for example Internet Explorer —  displaying the home page. See above image. In this window you need to get to the web page containing the information. In the Address bar of this window type in, or copy and paste the address of the web page where the information to download is held. Click on the Go button to load the new web page. Or just Google the website required and load the site. Try out this page as an example: http://www.teletrader.com

SP32-13062013165900

3) Hover over one of the small yellow boxes in the window—it will show it’s related data table. See above. Click the yellow box to select the data table you require, then click the Import button.  Decide where you want the data and click OK. See below. There is now a direct link between the website and your spreadsheet. To break the link go to Connections, Properties and uncheck the Save Query definition box.

There are options to play about with in the New Web Query window on the format of the download link and the import settings. Alter the refresh times in the Connections, Properties to keep the data up to date. Some data that you obtain will look pretty basic, but you can always link the raw data from the web to a snazzy looking report or dashboard.

There is also no restriction on how many links to web pages you can have in one spreadsheet – within reason. In theory you could compare the online price of cheese from all the major retail outlets, and create your own whatcheese.com comparison site!

 

SP32-31052013170000

Not quite the data you were looking for? 

Some websites restrict the download availability, and even ask for passwords.

However it’s a big Internet universe, and somewhere the data you require will be freely available.

Contact us for even more information on using data links in Excel.

 

 

About Derek